April 28, 2011 at 12:41 pm
I've got a sql 2008 r2 enterprise cluster with several dozen databases. I have a daily and a weekly maintenance plan that among other things runs backups. The weekly runs a full and the daily takes differentials. The backup steps of the maintenance plans drop the bak's and diff's into a network share. I would like to include a step in the maintenance plans that generate the restore script needed to restore the databases to that date.
Any recommendations? What do other folks do to try to ease the burden of scripting the restores if you have to often restore a lot of databases to various dev and test servers?
April 28, 2011 at 1:34 pm
Assuming you're restoring back to the same datafile location(s), it's fairly straightforward. Below is the code to script the restore for the current database. You could easily modify it to run through all the databases.
DECLARE @dbname VARCHAR(250),
@bkup_type CHAR(1),
@sqlstmt NVARCHAR(1000)
SET @dbname = DB_NAME()
--most recent full backup
SET @bkup_type = 'D'
select top 1 @sqlstmt = 'RESTORE DATABASE ['+b.database_name+'] FROM DISK = N'''+ m.physical_device_name +''' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10'
from msdb..backupset b
join msdb..backupmediafamily m on b.media_set_id = m.media_set_id
where database_name = @dbname
AND type = @bkup_type
ORDER BY backup_start_date desc
PRINT @sqlstmt
--most recent differential
SET @bkup_type = 'I'
select top 1 @sqlstmt = 'RESTORE DATABASE ['+b.database_name+'] FROM DISK = N'''+ m.physical_device_name +''' WITH FILE = 1, NOUNLOAD, STATS = 10'
from msdb..backupset b
join msdb..backupmediafamily m on b.media_set_id = m.media_set_id
where database_name = @dbname
AND type = @bkup_type
ORDER BY backup_start_date desc
PRINT @sqlstmt
GO
Hope this helps.
Colleen
April 28, 2011 at 1:44 pm
Oh, that's nice, thank you. I was working off of some script from this post:
http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/66372/
However, I think yours looks better.
So, the only last piece of this is, how can I drop the restore scripts into a .sql file in the same directories as the backup and diff files? Is that what you would recommend or is this perhaps the wrong approach?
April 28, 2011 at 1:49 pm
One idea I had was to write a C# CLR stored proc that would take the print from your code and drop that into a .sql file and write it to the network share.
April 28, 2011 at 1:53 pm
There might be a better way, but what initially comes to mind is to put this script into a separate step in your backup/maintenance job. In the Advanced tab of the Job Step properties you can specify an output file. All the print statements will be written to that file. That's worked for me in the past.
April 28, 2011 at 2:04 pm
I'm using an Execute T-SQL Statement Task within a maintenance plan, is that what you are referring to or something else?
April 28, 2011 at 2:10 pm
No. I'm not sure how you get output from a task in the maintenance plan itself to go to a file.
What I was referring to is the job that runs that plan/subplan. You can add a T-SQL step to that job to run your script. And on the Advanced Tab of the step properties, you set the output.
April 28, 2011 at 2:18 pm
Clever idea, thanks for your advice today.
April 28, 2011 at 2:19 pm
You're welcome. 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply