September 19, 2011 at 9:47 am
Here's what I am trying to do. My boss wants me to create a script that will allow the techs to (if needed, and hopefully it won't be) restore all the Transaction Log backups automagically. Basically, they would copy the Log backup files to the local HD of the SQL Server, load this SQL Query, tell it where to find the files, and execute it.
I want this script to check that each TLog restore went OK, and if it fails for some reason, to immediately STOP and put up a message indicating there was a problem. I've got a script that will retrieve a list of files, store them in a temp table, and flag each as restored or not (to prevent double restoring of a file.) The TLog backup file names include the date and time they were created, and so should be in the proper order (maybe except for certain situations, such as going from MMDDYYY of 09302011 to 10012011)
The script I've got so far, is (yes, it's kind of ugly):
create table #backupfileloc
(col1 varchar(max))
go
declare @BackupFilesLocation nvarchar(4000)
set @BackupFilesLocation = 'c:\temp\'
insert into #backupfileloc (col1)
values (@BackupFilesLocation)
go
create table #filelisting
(col1 varchar(max),
col2 varchar(max),
col3 varchar(max))
go
declare @fileloc varchar(4000)
set @fileloc = (select top 1 col1 from #backupfileloc)
insert into #filelisting exec xp_dirtree @fileloc, 1, 1
go
alter table #filelisting
add restored int
GO
update #filelisting
set restored = 0;
go
declare @filetorestore varchar(max)
declare @exitcode int
set @filetorestore = (select top 1 col1 from #filelisting where RIGHT(col1, 3)='TRN' and restored = 0)
set @filetorestore = (select top 1 col1 from #backupfileloc) + @filetorestore
restore log ehrTHOMAS from disk=@filetorestore with norecovery
if @exitcode = 0
Begin
print 'Everything restored OK, and we will move on to the next file'
print 'someday...'
End
Else if @exitcode != 0
begin
print 'Oh dear, something failed!'
end
update top(1) #filelisting
set restored = 1
where RIGHT(col1, 3)='TRN' and restored = 0;
drop table #filelisting
drop table #backupfileloc
I know the section for testing the exit code is at least partly wrong, as I don't know how to "catch" the exit code / error level / return code of a restore.
Any help would be appreciated!
Thanks,
Jason A.
PS Obviously, the messages will be "profesionalized" before this goes out into the world...;-)
September 19, 2011 at 9:55 am
Never mind, I figured out I could (and how) wrap the restore in a Try / Catch block. Now I just need to set about getting it to loop.
Thanks!
Jason A.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply