May 16, 2011 at 12:37 pm
Can anyone share a script that can be used to create a job that will restore a db from a backup, when the backup file is created by a maintenance plan adding a changing time stamp? For example :
RESTORE DATABASE RestoreTest FROM DISK 'G:\RestoreTestBak\RestoreTest_Backup_201105161325.bak'
WITH REPLACE
The time stamp (...201105161325) changes every day. I've tried FROM, WHERE, and LIKE clauses with wild cards that seem to work with retrieving character values from db tables but not with backup files on disk locations.
Thanks for everyone for being there to help.
May 16, 2011 at 1:13 pm
Problem is, that includes the time too. No way to calculate that, you have to know it.
Do you have access to the server where the backups are being taken? You can query the backup file name from the msdb database on that server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 16, 2011 at 1:25 pm
Thanks Grant. Great idea. Didn't think of accessing the backup file name from msdb. If I get your drift I should be able to insert it into the restore statement?
May 16, 2011 at 1:44 pm
Yeah. That way you don't have to guess what the backup name is.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 16, 2011 at 1:51 pm
Grant. If I may burden you again. What table in the msdb would contain the backup file name created by the maintenance job?
May 16, 2011 at 2:10 pm
I don't know, we'll have to renogiate the rate now...
Here's a little query to just get you started:
SELECT bf.logical_name,bs.backup_finish_date
FROM dbo.backupfile AS bf
JOIN dbo.backupset AS bs
ON bf.backup_set_id = bs.backup_set_id
There's lots more you can do with this stuff, obviously.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 16, 2011 at 2:42 pm
Thanks Grant.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply