Easy way to generate a restore script.

Leave a comment

June 5, 2013 by Kenneth Fisher

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.

EasyRestoreScript1

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

EasyRestoreScript2

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.

EasyRestoreScript3

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

EasyRestoreScript4

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.

EasyRestoreScript5

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.

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

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,664 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: