Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Easy way to generate a restore script.

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.


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

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...