restore database and automatically drop database snapshot transact sql

To facilitate quick testing of changes on my development and test environments I leverage database snapshots greatly.  The benefit of snapshots is that I can revert data to its previous state without having testers wait 30 – 40 minutes to perform another database restore, only to repeat tests again. Also, I can do a join on the data in the snapshot database, to compare data.

Snapshots are a great help. However, I still need to refresh the source database every now and then from the production source to get up to date data. The following methodology allows me to drop all existing snapshots and restore database in one step, without having to do a lot of work manually checking for snapshots, then dropping each one individually, then performing my restore.

Once I have the script define for a database I put in svn so that my entire team has access to the script and can restore a database unattended.

e.g. http://svn/database/servername/database_restore_scripts/mydatabase.sql

Please note however that

  1. If you run this script you drop and lose all your snapshots on a database permanently.
  2. If you do not have backups of your test databases, make sure that all your users are ok with you refreshing the environment before doing your data refresh from production.

My process is to

  1. drop any existing database snapshots
  2. offline the database
  3. online the database
  4. restore the database
  5. fix users


-- DROP DATABASE SNAPSHOTS IF THEY EXIST
DECLARE @emailMessage NVARCHAR(4000)
DECLARE @database VARCHAR(100)
DECLARE @sql NVARCHAR(4000)
DECLARE cur_db CURSOR
FOR
select name
from sys.databases
where source_database_id IN (select database_id from sys.databases where name LIKE 'MyDatabase')
ORDER BY create_date DESC
FOR READ ONLY
OPEN cur_db
FETCH NEXT FROM cur_db INTO @database
WHILE (@@FETCH_STATUS = 0 )
BEGIN

SET @sql = 'ALTER DATABASE ['+@database+'] SET OFFLINE WITH ROLLBACK IMMEDIATE;'
EXEC sp_executesql @sql
SET @sql = 'ALTER DATABASE ['+@database+'] SET ONLINE;'
EXEC sp_executesql @sql
SET @sql = 'DROP DATABASE ['+@database+'];'
-- PRINT @sql
EXEC sp_executesql @sql
FETCH NEXT FROM cur_db INTO @database
END
CLOSE cur_db
DEALLOCATE cur_db
GO
-- OFFLINE THE MAIN DATABASE TO FORCE USERS OFF SYSTEM
ALTER DATABASE  [MyDatabase] SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
-- ONLINE THE DATABASE
ALTER DATABASE  [MyDatabase] SET ONLINE;
GO

-- BEGIN RESTORING DATABASE.
DECLARE @exitcode int, @sqlerrorcode int;
EXEC MASTER..sqlbackup N'-SQL "RESTORE DATABASE [MyDatabase]
FROM DISK=''\\BACKUP\MyDatabase.sqb''
WITH REPLACE
, DISCONNECT_EXISTING
, RECOVERY"', @exitcode OUTPUT, @sqlerrorcode OUTPUT;
IF (@exitcode <> 0) OR (@sqlerrorcode <> 0)
BEGIN
SET @emailMessage = 'FAILED DATABASE RESTORE | ' + @@SERVERNAME + ' | ' + SUSER_SNAME() + ' | '+ 'MyDatabase' + ' | ' + CONVERT(VARCHAR(20),getdate(),120)
EXEC msdb.dbo.sp_send_dbmail
@recipients='dbas@mydomain.com;',
@subject= @emailMessage,
@body= @emailMessage
END
ELSE
BEGIN
SET @emailMessage = 'SUCCESSFUL DATABASE RESTORE | ' + @@SERVERNAME + ' | ' + SUSER_SNAME() + ' | '+ 'MyDatabase' + ' | ' + CONVERT(VARCHAR(20),getdate(),120)
EXEC msdb.dbo.sp_send_dbmail
@recipients='dbas@mydomain.com;',
@subject= @emailMessage,
@body= @emailMessage
END
GO
-- RUN CODE TO RESOLVE MISMATCHED SIDS and USER ACCOUNTS WITH LOGINS
EXEC [MyDatabase].dbo.sp_fixusers
GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s