Schedule restore

Question

Post reply

Schedule restore

surma.sql

SSCommitted

Points: 1552
More actions
April 3, 2012 at 7:37 am

#266493

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.

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

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

Geoff A SSChampion Points: 11511 More actions · Answer 1

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

Anders Pedersen SSChampion Points: 11410 More actions · Answer 2

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.

Jared SSC Guru Points: 61793 More actions · Answer 3

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

CapnHector SSCoach Points: 16143 More actions · Answer 4

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]

Anders Pedersen SSChampion Points: 11410 More actions · Answer 5

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).

surma.sql SSCommitted Points: 1552 More actions · Answer 6

Superb

Thank You so much for your quick reply

I appreciate