March 29, 2011 at 5:52 am
Hi,
I'm trying to automise the task of refresh between a Prod to Test Database.
I am stuck at a point at the time of the Restore Job Step.
Well, to begin with, I had included the following job steps in the entire automate process.
1. Validating Backup;
2. Kill All user session;
3. Take into Single User Mode;
4. Restore;
5. CheckDb;
6. UpdateUsage;
While at the Restore Step:
I am using the below script:
restore database test
from disk = 'K:\bkp\test_backup_201103290500.bak'
with move 'test' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test.mdf',
move 'test_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test_log.ldf',
replace,
stats = 5
But, I need to actually pick the backup from a share location, which contain all the database backups (Full and T-log) and the job need to pick up the latest production full database backup.
PLEASE SUggest me a suitable "script" which take the latest backup. The file name follows a specific format of name: [DatabaseName_backup_YYYYMMDDHHRR.bak]
Thanks.
March 29, 2011 at 6:36 am
The share has to be accessible, but then you just use the UNC path for it:
RESTORE ... FROM DISK = '\\servername\sharename\filename'
Since the filename is likely to be different each and every time, you need a mechanism to get it. Either you build the string using the date & time fields in the same way as they are put together in the backup routine, or you check the disk for the latest file and get the file name first. If you're going to start doing that, I'd suggest moving your process out of TSQL and into a programming/scripting language such as PowerShell.
"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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply