Easily restore SQL Server Database with T-SQL script

Doing test driven development often requires you to reset your database to a well-defined state. For this purpose  I’ve come up with the following T-SQL that first clears all outstanding connections to the database, enabling the restore, then restores it from its backup set. Note that ‘WITH FILE=2’ points to the second backup set in my backup file, allowing me to choose from different bases (sets) in the same backup file.

——————————————————————————
— Kill all current connections
——————————————————————————
DECLARE @sqlcmd VARCHAR(8000);
SELECT @sqlcmd=COALESCE(@sqlcmd,)+‘KILL ‘+CAST(spid AS VARCHAR(10))+‘; ‘
  
FROM sys.sysprocesses
  
WHERE DBID=DB_ID(‘OEDB’) AND HOSTNAME <>
PRINT @sqlcmd
EXEC(@sqlcmd)
—————————————————————————-
— Restore test-data
—————————————————————————-
RESTORE DATABASE[OEDB]
    FROM  DISK=‘C:\OEDB.bak’
  
WITH  FILE=2
—————————————————————————-

Of course you can execute this automatically from your test code – using SMO (Systems Management Objects) is another option for doing a programmatic restore. I’ve experimented with SMO, but the scripting seems so much easier.

Advertisements
Posted in SQL