I recently had a need to test a migration script which converted product IDs from one ERP to those of another in a .net system local DB and needed a way to run the script and continuously and very quickly restore the original state of the DB.
I used the following TSQL to quickly restore:
—-restore backup
ALTER DATABASE DBNAME SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE DBNAME SET ONLINE
RESTORE DATABASE DBNAME FROM DISK = ‘c:\dbbackup.bak‘ WITH REPLACE
GO
The juicy bit is obviously the restore database command, however you will likely need to kill all processes to avoid getting an ‘Exclusive access could not be obtained because the database is in use‘ type of message. There are a couple of ways of doing this and one of these is to set the DB offline and then immediately bring it back online again just before we attempt the restore.
