April 3, 2012 at 7:37 am
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.
April 3, 2012 at 7:47 am
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
April 3, 2012 at 8:18 am
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.
April 3, 2012 at 8:35 am
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
April 3, 2012 at 8:40 am
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 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]
April 3, 2012 at 9:38 am
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).
April 3, 2012 at 12:08 pm
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