:::: MENU ::::

Restoring many databases

Hi!

 

Had a release the other day where we had to move a couple of thousand databases from one machine to another, we could not shutdown the source instance so no detach/attach, had to go with backup restore.

So, I created a simple script that I ran on the source instance, it reads the backup information in msdb and creates restore commands which I then ran on the target instance to restore all these thousands of databases. I only did full restores, but with a bit of tweaking you could do a full restore with no recovery and then eg. diff och log with recovery.

When you run it in SSMS, set results to text, not rows, and be wary that you don’t exceed the “Maximum numbers of characters displayed in each column”, found under Options -> Query Results -> SQL Server -> Results to Text

Make sure the path pointing to the backups on the target machine are the same as on the source, goes without saying I know, but I just thought I’d mention it.

The script below will create restore commands for all full backups that were taken in the last 17 hours and for all databases with DATABASE_NAME in it’s name, change it to match your case.

SELECT  'RESTORE DATABASE [' + bs.database_name + ']
FROM DISK = N''' + bmf.physical_device_name + '''' +
' WITH  FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10'
+ CHAR(10) + 'GO' + CHAR(10)
FROM    msdb.dbo.backupmediafamily bmf with(nolock),
msdb.dbo.backupset bs with(nolock)
WHERE   bs.media_set_id = bmf.media_set_id
AND    (CONVERT(datetime, bs.backup_start_date, 102) >= dateadd(hour, -17, getdate()))
AND    bs.database_name like '%DATABASE_NAME%'
AND     bs.type = 'D'

 

If you want to check only diff backups, set bs.type to ‘I’ and for logs ‘L’

If you want to restore with NORECOVERY just add NORECOVERY like below:

SELECT  'RESTORE DATABASE [' + bs.database_name + ']
FROM DISK = N''' + bmf.physical_device_name + '''' +
' WITH  FILE = 1,  ,  NORECOVERY,
NOUNLOAD,
REPLACE,
STATS = 10'
+ CHAR(10) + 'GO' + CHAR(10)
FROM    msdb.dbo.backupmediafamily bmf with(nolock),
msdb.dbo.backupset bs with(nolock)
WHERE   bs.media_set_id = bmf.media_set_id
AND    (CONVERT(datetime, bs.backup_start_date, 102) >= dateadd(hour, -17, getdate()))
AND    bs.database_name like '%DATABASE_NAME%'
AND     bs.type = 'D'

 

STATS=10 gives progress output every 10%.

 

And as always, try in test before running it in prod!

 

//R

 

 


So, what do you think ?