February 16, 2010 at 10:18 am
jsb12 (2/15/2010)
There are 3 days of full backups and TLog backups in backup and trn folders respectively. I need to identify previous days full backup (8pm) and successive hourly transaction log backups (from 9pm) and restore them automatically(job) everyday. TLog backup format is '..backup_2010121800.trn'. the files are copied every hour and restore has to be an hour after they are copied.The problem is that if the copy job that copies from other server fails, older backup might be applied. So I wanted to create a mechanism to prevent it. ex. - a table that stores previous restore info and maybe read from it before applying restore. Its fine if the script works without requiring the table.
Please help
Here is what we do in our environment, We don't copy files across Prod and Dev environment, we tell network admins to open a one way port in firewall to see Bak files from Dev. so that we don't have to copy files, restore directly from Prod bakfiles it saves lot of time and band width. For restore all we do is set up a job in Dev environment replace file names anytime time you want to perform restore, If it DB size is less than 100GB and it is on SQL 2008 use compression and schedule full back up just 1 an hour before you want to perform restore, that way you can avoid t-logs restores, keep life simple.
Hope this helps.
EnjoY!
February 16, 2010 at 10:50 am
in that case I would like to try creating warm standby database. Please help with the script to restore logs in sequence.
Just fyi - there are breaks for the log backups at the time of full backup and during the maintenance window and there maybe problems like backup copy job to the server failing etc. the log backups are of the format '_backup_201002161100.trn'
I was thinking of creating a table and have the ouput of last restored backup name, time etc. I dont know if the script should look at the table for last restore info before restoring next log.
Is this correct or is it not required. in any case pls help with the script to identify next log backup to restore.
February 16, 2010 at 11:05 pm
jsb12
can you please tell me exactly , at what time full backup is taken and what time log backup
and also at what time you are planning to restore it ? give me exact time and i will try to give you code for that
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 23, 2010 at 9:01 am
actually, i was using this script to restore a full backup and getting the results to a table. however it restores only latest full backup from a folder. so if an older backup exists, it will still restore it again. is it possible to make the query read from the table 'lastrestored' to see if the full backup has already been restored? i wanted to modify it such that a full backup already restored wouldnt be restored.
this is still a part of the problem but i really need to complete atleast this much. Please help.
USE Master;
GO
SET NOCOUNT ON
-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)
-- 2 - Initialize variables
SET @dbName = 'abc'
SET @backupPath = 'D:\Backups\restoreabc\'
-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath
INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd
-- 4 - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.BAK'
AND backupFile LIKE @dbName + '%'
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
+ @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE,' +
'move ''abcdata'' to ''d:\SQLData\abc.mdf'',
move ''abclog'' to ''d:\SQLData\abclog.ldf'''
PRINT @cmd
insert into test.dbo.lastrestored values(@lastFullBackup)
EXECUTE sp_executesql @cmd
February 23, 2010 at 9:36 am
pick the bones from thsi there may be some useful bits for you. I would look to xhange the exec (@myquety to an sp_executesql for security reasons.
/******************************************************************************************************************/
/* looks for the latest backup in passed directory and restores that .bak file */
/* to specified database */
/******************************************************************************************************************/
CREATE procedure usp_restorelatestbackups @path varchar(200), @dbname varchar(100)
as
declare @myquery varchar(1000)
declare @query varchar(1000)
declare @name varchar(100)
set @myquery = 'exec master.dbo.xp_cmdshell ''dir ' + ltrim(rtrim(@path)) + '\*.* /a/od'''
create table #Filenames (id int identity(1,1) ,name varchar(400), bkpdate datetime default null)
insert #Filenames(name)
exec (@Myquery)
delete from #Filenames where substring(name,3,1) <> '/' or name is null or
substring(name,22,1) ='<'
set dateformat dmy
update #Filenames set bkpdate = convert(datetime,left(name,10)), name = ltrim(rtrim(substring(name,37,59)))
delete from #Filenames where name like '%TRN%'
--select * from #Filenames
--get latest backup
set @name = (select top 1 name from #filenames order by bkpdate desc)
--select @name
declare @statement nvarchar(255)
select @statement = 'restore database ' + @dbname + ' from disk = '+ ''''+ @path + @name +''''+' with replace'
--select @statement
exec sp_executesql @statement
drop table #Filenames
---------------------------------------------------------------------
February 23, 2010 at 9:53 am
Thank You George, but I was using this because it also restores logs that follow the full backups. but the problem was that if the folder had older full backups which would have already been restored, it still restores the older full backup over again. this was the whole script i used
USE Master;
GO
SET NOCOUNT ON
-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)
-- 2 - Initialize variables
SET @dbName = 'abc'
SET @backupPath = 'D:\Backups\restoreabc\'
-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath
INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd
-- 4 - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.BAK'
AND backupFile LIKE @dbName + '%'
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
+ @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE,' +
'move ''abcdata'' to ''d:\SQLData\abc.mdf'',
move ''abclog'' to ''d:\SQLData\abclog.ldf'''
PRINT @cmd
insert into test.dbo.lastrestored values(@lastFullBackup)
EXECUTE sp_executesql @cmd
-- 5 - check for log backups
DECLARE backupFiles CURSOR FOR
SELECT backupFile
FROM @fileList
WHERE backupFile LIKE '%.TRN'
AND backupFile LIKE @dbName + '%'
AND backupFile > @lastFullBackup
OPEN backupFiles
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''
+ @backupPath + @backupFile + ''' WITH NORECOVERY'
PRINT @cmd
insert into test.dbo.lastrestored values(@backupfile)
EXECUTE sp_executesql @cmd
FETCH NEXT FROM backupFiles INTO @backupFile
END
CLOSE backupFiles
DEALLOCATE backupFiles
/*
-- 6 - put database in a useable state
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY'
PRINT @cmd
*/
February 23, 2010 at 10:08 am
sorry, I see more than 10 lines of code I go into a coma and I only have so much time. This line strips out the newest full backup for me
set @name = (select top 1 name from #filenames order by bkpdate desc)
I have already stripped out trn log backups so you need a where clause above to do that for you.
so you can hopefully incorporate that to do your full backup restore than go onto your log restore section.
---------------------------------------------------------------------
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply