February 19, 2008 at 2:55 pm
Hello. 'first time caller here...'
I've set up a backup schedule for all my SQL 2005 DBs under 'Maintenance Plans'.
The problem is that the .BAK files end up with the timestamp automatically appended to them. example: AdventureWorks_backup_200802190101.bak
I want to automate a nightly refresh to our corresponding DBs on our test server. In order to do this I need the backup's names to be the static (no time stamp) ex: AdventureWorks_backup.bak
Can anyone tell me how to remove the timestamp from a maintenance plan?
Thank you very much in advance for any help.
February 19, 2008 at 3:04 pm
You can't change the maint plan file name.
It's fairly easy to have your restore procedure do a directory comand from xp_cmdshell to find the latest file name to use.
February 19, 2008 at 6:16 pm
Since you know what the filename is going to be, you could construct the restore statement with that in mind & use EXEC or sp_executesql:
[font="Courier New"]DECLARE @BackupFile varchar(100),
@sql varchar(200)
SET @BackupFile = 'C:\wherever\Adventureworks_backup_' + CONVERT(char(8), CONVERT(datetime, GETDATE(), 103), 112) + LEFT(REPLACE(CONVERT(char(8), CONVERT(datetime, GETDATE(), 103), 108), ':', ''), 4) + '.bak'
SET @sql = 'RESTORE DATABASE AdventureWorks FROM DISK = ''' + @backupfile + ''' WITH REPLACE'
EXEC (@sql)
[/font]
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
February 20, 2008 at 9:33 am
Scott Duncan (2/19/2008)
Since you know what the filename is going to be, you could construct the restore statement with that in mind & use EXEC or sp_executesql:
Unfortunately I don't know what the full file name is going to be. Only the begining of it.
The restore is being run on a completely different server than the original DB.
is this script grabbing any backup file that begins with 'Adventureworks_backup_********' ?
If this is the case you've hooked me up with a perfect solution.
If not, I need a way to either rename 'Adventureworks_backup_********' or refer to it without knowing what ******** is.
Thanks!
February 20, 2008 at 1:10 pm
The above script needs a slight revision.
If you are running the backups at the same time every night/morning, then all that will change is the date. So running the script below on the same date as the backup was started should return the name of the backup file (note - the backup time has been manually set to 0100, i.e. 1 am, which is what the script above should've had to start with).
[font="Courier New"]DECLARE @BackupFile varchar(100),
@sql varchar(200)
SET @BackupFile = 'C:\wherever\Adventureworks_backup_' + CONVERT(char(8), CONVERT(datetime, GETDATE(), 103), 112) + '0100.bak'
SET @sql = 'RESTORE DATABASE AdventureWorks FROM DISK = ''' + @backupfile + ''' WITH REPLACE'
EXEC (@sql)
[/font]
If you are running the script the following day, change the GETDATE() to DATEADD(d, -1, GETDATE()) to return the previous day's date. Or assign it to a variable first, then use the variable in the CONVERT statement.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
February 21, 2008 at 7:21 am
This is fantastic. Thanks for all your help!:D
February 27, 2008 at 11:59 am
We do have our own CMD script to truncate the timestamp in it and send us the log file. You can create one for your self.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply