Restore job: how to bypass date part of bak file in job command?

Question

Restore job: how to bypass date part of bak file in job command?

EllieDBA

SSCommitted

Points: 1786
More actions
April 17, 2009 at 8:49 am

#203039

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

EllieDBA SSCommitted Points: 1786 More actions · Answer 1

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

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 2

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

Steve Jones - SSC Editor SSC Guru Points: 736193 More actions · Answer 3

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.