April 17, 2009 at 8:49 am
This might be a very simple question for you gurus but help me out here. I need my restore command to bypass the date part in backup file.
I had a backup and restore job for a database in SQl that worked well
(backup N'\\HOSTname\sqlbackup\WSS_database_name_dump.bak'
Restore [db_name] from N'\\HOSTname\sqlbackup\WSS_database_name_dump.bak')
but since I am trying to do one less full backup of this db on a daily basis, I want to set the job to automatically restore database from a file in another backup source ( share backup copy).
Manually when I restore database with the following command, it restores successfully. howwever as you notice, it has the exact name of backup file:
ALTER DATABASE [WSS_Database_name]
SET OFFLINE
WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [WSS_Database_name]
FROM DISK = N'\\HOSTname\sqlbackup\WSS_database_name_backup_200904170020.bak'
WITH FILE = 1, KEEP_REPLICATION,
NOUNLOAD, REPLACE, STATS = 10
GO
Now the question is, you know how in SQL 2005 date gets attached at the end of file name? backup_200904170020.bak'? Is there anyway that I can restore the file with bypassing the date part on the file name like we do LIKE statement in select in sql?
can I say FROM DISK LIKE N'\\HOSTPS01\sqlbackup\WSS_database_name%'
If not what is the simplest way to restore this backup copy from this location? By the way, this location usually has 2 days copies of this DB backup, don’t know if that would cause a problem too.
Thank you for any informaiton
Ellie
April 17, 2009 at 9:13 am
I forgot to add after researching on this same forum i had find out that maybe both RTrim and database_name_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(), 20),'-',''),':',''),' ','') + '.BAK' commands might work, but don't know if I can use them directly in the rstore command, if so can you tell me how I can incorporate either of them?
thank you
Ellie
April 17, 2009 at 10:07 am
It seems like you would need to get the proper file name and then build that into your restore script dynamically. So, you could use the following script as a starter to get a final query that will give you the physical file name that you want to use in your restore and then build that string and execute the restore.
selectbs.backup_start_date,
bs.backup_finish_date,
bmf.physical_device_name
frombackupset bs join backupmediafamily bmf
on bs.media_set_id = bmf.media_set_id
wherebs.backup_start_date > getdate()-2
and bs.type = 'D' --Full Backups Only
and logical_device_name is not null --Eliminates dupes
I would recommend building in a backup prior to your restore just in case you need to rollback for any reason. I am often called "Mr. Conservative" by my fellow co-workers... 🙂
If I am off in my understanding of what you are looking for let me know and I will try to respond accordingly.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 17, 2009 at 11:10 am
I might be tempted to copy the file, rename it to the same name every day and let the restore then run. Works well if you move this to another server. Copy there as a standard name.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply