Post reply

Sql Remote restore Help

  • November 17, 2009 at 12:23 pm

    #142995

    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

    #1080905

    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