Schedule restore

  • Right now i schedule a back up on one server.

    and restore on a different server. But problem is schedule backup has some bak file like filename_dateformate_some no means (backup_201202041211.bak)

    so every time, This backup file name is changing.

    so i need to schedule restore on another server.

    I create task on another server and wrote T-SQL as

    RESTORE DATABASE Yotest1

    FROM DISK = N'D:\Backups\bkewtest.bak'

    WITH MOVE 'test' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Yotest1_201202041211.mdf',

    MOVE 'test_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Yotest1_log_201202041211.LDF'

    , REPLACE

    but problem is if it run once than its fine, but i need to schedule restore task and the name of backup file will keep changing.

    so please help me or give me better direction to handle this problem.

  • get the last backup file name like this;

    declare @filenametbl1 varchar(60)

    set @filenametbl1 = (

    select top 1 rtrim(ltrim(REVERSE(SUBSTRING(REVERSE(T2.physical_device_name),0,(charindex('\',REVERSE(T2.physical_device_name))))))) [filename]

    from [YOUR_SERVERNAME_HERE].msdb.dbo.backupset T1 JOIN [YOUR_SERVERNAME_HERE].msdb.dbo.backupmediafamily T2 ON

    T1.media_set_id = T2.media_set_id

    where T1.[type] = 'D' and T1.database_name = 'YOUR_DATABASE_NAME_HERE'

    order by T1.backup_finish_date desc

    )

    Then pass that variable into your path.

    My complete script looks like this;

    declare @filenametbl1 varchar(60), @thepath1 nvarchar(150)

    set @filenametbl1 = (

    select top 1 rtrim(ltrim(REVERSE(SUBSTRING(REVERSE(T2.physical_device_name),0,(charindex('\',REVERSE(T2.physical_device_name))))))) [filename]

    from [MY_SERVER].msdb.dbo.backupset T1 JOIN [MY_SERVER].msdb.dbo.backupmediafamily T2 ON

    T1.media_set_id = T2.media_set_id

    where T1.[type] = 'D' and T1.database_name = 'MY_DATABASE'

    order by T1.backup_finish_date desc

    )

    set @thepath1 = '\\my\UNC\path\'+@filenametbl1+''

    RESTORE DATABASE MY_DATABASE

    FROM DISK = @thepath1

    WITH MOVE 'MY_DATABASE_NEW'

    TO 'H:\SQL_DATA\DATA\MY_DATABASE_NEW.mdf',

    MOVE 'MY_DATABASE_NEW_log'

    TO 'H:\SQL_DATA\DATA\MY_DATABASE_NEW_log.ldf'

    ,REPLACE

  • Or don't use the maintenance plans to backup SQL and only save the date in the file name. That way don't need linked servers as long as the restore runs on the same date as the backup.

  • Anders Pedersen (4/3/2012)


    Or don't use the maintenance plans to backup SQL and only save the date in the file name. That way don't need linked servers as long as the restore runs on the same date as the backup.

    +1

    Jared
    CE - Microsoft

  • SQLKnowItAll (4/3/2012)


    Anders Pedersen (4/3/2012)


    Or don't use the maintenance plans to backup SQL and only save the date in the file name. That way don't need linked servers as long as the restore runs on the same date as the backup.

    +1

    +2

    on the server i set up recently i used the maintnence plan wizard to make sure i had every thing then set up tsql and batch files to accomplish all the tasks. the tsql backups ran and restored to the backup server then batch files moved the .bak files and appended the date. get the same result with a couple different steps for the restore and final backup file name.


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • I actually use a TSQL script that picks up any new database added to the server (and creates the subfolder needed). Backs them up to a share on a backup server. The restore script simply uses the same logic to come up with the file name. Only addition on the server they are restored to is a table maintained by me for which databases to restore. The only manual restores I do to the restore server is the first time a database needs restored. I have to do this since the script to restore picks up the current location of the files for that database from the database itself (i.e. if I need to move a database no need to modify my restore script).

  • Superb

    Thank You so much for your quick reply

    I appreciate

Viewing 7 posts - 1 through 6 (of 6 total)

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