Easy way to generate a restore script.

I was asked today if I had a canned restore script handy. I don’t. I’ve never really had a reason to. Generating a restore script, even a complicated one is really very easy if you know how.

For those of you wondering, there are frequently very good reasons to generate a script rather than run it through the restore GUI. You may be planning on running the restore through a job, you may be working on learning how to write restore scripts etc..

First go to the GUI.

Select what type of restore you plan on doing. In my case I’ll be doing a database restore.

Next fill in the information for your restore. You can set the restore options, where you want to restore to, the restore point in time etc. I’m going to set the restore time. The default is the most recent state possible, but for the purposes of the demonstration I’m going to restore to May 27 2013 10PM.

Note that now a number of restore files are checked to bring us to the date requested.

And last but not least we hit the handy dandy script button. There are several options if you hit the down arrow next to the word script including scripting to a new query window, file, clipboard or job. The default is new query window which is what I want, so all I have to do is hit the script button.

And we get the restore script as such:

RESTORE DATABASE [DBA] FROM  DISK = N'Y:\BackupFiles\DBA_backup_201305251801.bak' 
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE DATABASE [DBA] FROM  DISK = N'Y:\BackupFiles\DBA_backup_201305260201.dif' 
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10,  STOPAT = N'2013-05-27T22:00:00'
GO
RESTORE LOG [DBA] FROM  DISK = N'Y:\BackupFiles\DBA_backup_201305260320.trn' 
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10,  STOPAT = N'2013-05-27T22:00:00'
GO
RESTORE LOG [DBA] FROM  DISK = N'Y:\BackupFiles\DBA_backup_201305260917.trn' 
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10,  STOPAT = N'2013-05-27T22:00:00'
GO
RESTORE LOG [DBA] FROM  DISK = N'Y:\BackupFiles\DBA_backup_201305261517.trn' 
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10,  STOPAT = N'2013-05-27T22:00:00'
GO
RESTORE LOG [DBA] FROM  DISK = N'Y:\BackupFiles\DBA_backup_201305262117.trn' 
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10,  STOPAT = N'2013-05-27T22:00:00'
GO
RESTORE LOG [DBA] FROM  DISK = N'Y:\BackupFiles\DBA_backup_201305270316.trn' 
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10,  STOPAT = N'2013-05-27T22:00:00'
GO
RESTORE LOG [DBA] FROM  DISK = N'Y:\BackupFiles\DBA_backup_201305270917.trn' 
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10,  STOPAT = N'2013-05-27T22:00:00'
GO
RESTORE LOG [DBA] FROM  DISK = N'Y:\BackupFiles\DBA_backup_201305271516.trn' 
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10,  STOPAT = N'2013-05-27T22:00:00'
GO
RESTORE LOG [DBA] FROM  DISK = N'Y:\BackupFiles\DBA_backup_201305272116.trn' 
WITH  FILE = 1,  NOUNLOAD,  STATS = 10,  STOPAT = N'2013-05-27T22:00:00'
GO

Certainly a lot easier than trying to type it out by hand!

And of course a similar method can be used to generate backup scripts, login scripts etc.

Filed under: Backups, Microsoft SQL Server, SQLServerPedia Syndication, SSMS, T-SQL Tagged: backups, code language, language sql, Restore, script button, SSMS, T-SQL