November 17, 2009 at 12:23 pm
HI,
I am trying to setup a job to restore a remote database backups.
these backups run every alternative day.
Sql attaches year day and time to the bakup file. How do I use it?
currently I am running TSQL to do a restore
restore database from
disk=N'\\Comp_nam\Backup_Folder\DBase_200911170000.bak'
with move 'DBase' to 'c:\Program files\mssql\data\Dbase.mdf',
with move 'DBase_log' to 'c:\program files\mssql\log\dbase_log.ldf'
the filename changes everyday. How can I use wildcards to pass the value in this case?
your help would be much appreciated.
Thanks.
November 18, 2009 at 7:39 am
You could query it out of msdb using a linked server. Something like:
SELECT
B3.physical_device_name
FROM
linked_server.msdb.dbo.backupset AS B JOIN
linked_server.msdb.dbo.backupmediafamily AS B3
ON B.media_set_id = B3.media_set_id
WHERE
B.database_name = 'DB_NAME' AND
B.[type] = 'D' AND
-- Full
B.backup_finish_date = (
SELECT
MAX(B1.backup_finish_date)
FROM
msdb.dbo.backupset AS B1
WHERE
B.database_name = B1.database_name AND
B.[type] = B1.[type]
)
You'll need to parse the string, but this gets you started.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply