Automating multiple RedGate restores
I recently had the task to do a DR restore several SQL databases using RedGate backup software. The software is incredible and does things very well, but I had to restore 3 servers each with dozens of databases – not something to be done by hand. Here are a few tricks I learned during the process.
Getting the server ready:
This was a DR restore, so I had the server, file system, and system state, but no active SQL databases. The first step was to get SQL running and restore the MASTER database. To get SQL running I built out a VM and setup a vanilla install of SQL. I then copied the master, model, dist, msdb, mssqlsystemresource, and temp databases. SQL then started successfully.
Once SQL was running, I restored MASTER. Start SQL in single user mode (sqlservr -m -c) and use RedGate’s sqlbackupc.exe command as shown below:
sqlbackupc -sql “restore database master from disk=’path‘
Taking databases offline:
Because the databases dont already exist, we need to take them offline prior to being able to restore. This can be done manually, right-click database | Tasks | Take Offline, but for dozens of DBs, there has to be a better option.
I ended up creating the TSQL script below, then cut and paste the results into another query.
select ‘alter database [‘ + name + ‘] set offline with rollback immediate’ from sys.databases
Restoring databases:
Now the fun of restoring the databases. All the files were stored on a remote share, and using a similar process to taking the DBs offline, I was able to automate the restores. This time copy and paste the results into a command window with pathing to the RedGate tools
select ‘sqlbackups -sql “RESTORE DATABASE ‘ + name + ‘ FROM DISK=’ +””+ ‘\serverdirectory*.sqb’ +””+ ‘ WITH REPLACE”‘ from sys.databases
Once restored, the databases come back online automatically