Restoring multiple transaction logs on another server

Dear knowledgeable people,
Is there a simple way to restore the latest full backup, plus all the transaction
log backups on to another server. I know you can on the same server, 'cause
all the backup history is there in MSDB. But how many people back msdb up
every 15 minutes?
Consider the case:
Full Backup of Database @. 0300 each day
T.log backups every 15 minutes
Crash at 19:00 takes out entire server, but not the backup files 'cause
you cunningly configured File Replication Service to copy them elsewhere.
When restoring, do you really have to type 60+ "restore log ... with no recovery"
commands (without typos) to get the database back?
i.e Should I polish up the following script ( error handling / differential backups ...)
and/or write my own .exe to intelligently parse a backup directory, or am I
totally missing the point? ( Me programmer not normally sysadmin ).
Regards
AJ
CREATE procedure spAJGRestoreAsFarAsYouCan
(
@.Path varchar(200) = 'c:\mssql\backup\',
@.DBName varchar(30) = 'ALC',
@.DoItOrJustPrint char(1) = 'Y'
)
as
set nocount on
-- Get list of files in directory
declare @.SqlCmd varchar(300)
set @.SqlCmd = 'dir ' + @.Path + '*.* /a-d /o:d /t:c /n /B'
create table #DirList
(
RowID int identity,
FileOnly varchar(400)
)
insert #DirList ( FileOnly )
exec master..xp_cmdshell @.SqlCmd
-- Last full backup ( .bak )
declare @.FullBackup varchar(300)
declare @.CurrentID int
Select top 1
@.CurrentID = RowID,
@.FullBackup = @.Path + FileOnly
from #DirList
where patindex( '%.bak%', FileOnly) > 0
order by RowID desc
-- if @.FullBackup is NULL return -1
Set @.SqlCmd = 'restore database [' + @.DBName + '] from disk = ''' + @.FullBackup + ''' with norecovery'
print @.SQlCmd
if @.DoItOrJustPrint = 'Y'
exec(@.SQLCmd)
Declare @.TransLog varchar(300)
Set @.TransLog = 'dummy'
While @.TransLog is Not NULL
Begin
Set @.TransLog = NULL
Select top 1
@.TransLog = @.Path + FileOnly,
@.CurrentID = RowID
from #DirList
where patindex( '%.trn%', FileOnly) > 0
and RowID > @.CurrentID
order by RowID
if @.TransLog is not NULL
Begin
Set @.SqlCmd = 'restore log [' + @.DBName + '] from disk = ''' + @.TransLog + ''' with norecovery'
print @.SQlCmd
if @.DoItOrJustPrint = 'Y'
exec(@.SQLCmd)
End
End
Set @.SqlCmd = 'restore database [' + @.DBName + '] with recovery'
print @.SQlCmd
if @.DoItOrJustPrint = 'Y'
exec(@.SQLCmd)
drop table #DirList
return (0)I'd just polish the script. I'd like to point out the following, though:
* You may want to change dir /t:c to dir /t:w
* If your directory may have files other than the database/log backup files,
you may want to check
each file with RESTORE HEADERONLY to make sure that it's a backup file
* Instead of relying on the file date/time to determine the order of the
restore, you can also use
RESTORE HEADERONLY to make sure that the order is correct.
* Using RESTORE HEADERONLY, you can even determine whether you have gaps in
your log
backups by inspecting the LSN's (log sequence numbers)
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Andrew John" <aj@.DELETEmistrose.com> wrote in message
news:O5yClz$fDHA.616@.TK2MSFTNGP11.phx.gbl...
> Dear knowledgeable people,
> Is there a simple way to restore the latest full backup, plus all the
transaction
> log backups on to another server. I know you can on the same server,
'cause
> all the backup history is there in MSDB. But how many people back msdb up
> every 15 minutes?
> Consider the case:
> Full Backup of Database @. 0300 each day
> T.log backups every 15 minutes
> Crash at 19:00 takes out entire server, but not the backup files 'cause
> you cunningly configured File Replication Service to copy them elsewhere.
> When restoring, do you really have to type 60+ "restore log ... with no
recovery"
> commands (without typos) to get the database back?
> i.e Should I polish up the following script ( error handling /
differential backups ...)
> and/or write my own .exe to intelligently parse a backup directory, or am
I
> totally missing the point? ( Me programmer not normally sysadmin ).
> Regards
> AJ
> CREATE procedure spAJGRestoreAsFarAsYouCan
> (
> @.Path varchar(200) = 'c:\mssql\backup\',
> @.DBName varchar(30) = 'ALC',
> @.DoItOrJustPrint char(1) = 'Y'
> )
> as
> set nocount on
> -- Get list of files in directory
> declare @.SqlCmd varchar(300)
> set @.SqlCmd = 'dir ' + @.Path + '*.* /a-d /o:d /t:c /n /B'
> create table #DirList
> (
> RowID int identity,
> FileOnly varchar(400)
> )
> insert #DirList ( FileOnly )
> exec master..xp_cmdshell @.SqlCmd
> -- Last full backup ( .bak )
> declare @.FullBackup varchar(300)
> declare @.CurrentID int
> Select top 1
> @.CurrentID = RowID,
> @.FullBackup = @.Path + FileOnly
> from #DirList
> where patindex( '%.bak%', FileOnly) > 0
> order by RowID desc
> -- if @.FullBackup is NULL return -1
> Set @.SqlCmd = 'restore database [' + @.DBName + '] from disk = ''' +
@.FullBackup + ''' with norecovery'
> print @.SQlCmd
> if @.DoItOrJustPrint = 'Y'
> exec(@.SQLCmd)
> Declare @.TransLog varchar(300)
> Set @.TransLog = 'dummy'
> While @.TransLog is Not NULL
> Begin
> Set @.TransLog = NULL
> Select top 1
> @.TransLog = @.Path + FileOnly,
> @.CurrentID = RowID
> from #DirList
> where patindex( '%.trn%', FileOnly) > 0
> and RowID > @.CurrentID
> order by RowID
> if @.TransLog is not NULL
> Begin
> Set @.SqlCmd = 'restore log [' + @.DBName + '] from disk = ''' +
@.TransLog + ''' with norecovery'
> print @.SQlCmd
> if @.DoItOrJustPrint = 'Y'
> exec(@.SQLCmd)
> End
> End
>
> Set @.SqlCmd = 'restore database [' + @.DBName + '] with recovery'
> print @.SQlCmd
> if @.DoItOrJustPrint = 'Y'
> exec(@.SQLCmd)
> drop table #DirList
> return (0)
>|||Andrew
Yeh, you need to be prepared for such situations.
I would write stored procedure that accept tree parameters
(dbname,from_hour,to_hour)
You need to keep transaction backups with appropriatte file name like
my_log1200 ,next my_log1215.
"Andrew John" <aj@.DELETEmistrose.com> wrote in message
news:O5yClz$fDHA.616@.TK2MSFTNGP11.phx.gbl...
> Dear knowledgeable people,
> Is there a simple way to restore the latest full backup, plus all the
transaction
> log backups on to another server. I know you can on the same server,
'cause
> all the backup history is there in MSDB. But how many people back msdb up
> every 15 minutes?
> Consider the case:
> Full Backup of Database @. 0300 each day
> T.log backups every 15 minutes
> Crash at 19:00 takes out entire server, but not the backup files 'cause
> you cunningly configured File Replication Service to copy them elsewhere.
> When restoring, do you really have to type 60+ "restore log ... with no
recovery"
> commands (without typos) to get the database back?
> i.e Should I polish up the following script ( error handling /
differential backups ...)
> and/or write my own .exe to intelligently parse a backup directory, or am
I
> totally missing the point? ( Me programmer not normally sysadmin ).
> Regards
> AJ
> CREATE procedure spAJGRestoreAsFarAsYouCan
> (
> @.Path varchar(200) = 'c:\mssql\backup\',
> @.DBName varchar(30) = 'ALC',
> @.DoItOrJustPrint char(1) = 'Y'
> )
> as
> set nocount on
> -- Get list of files in directory
> declare @.SqlCmd varchar(300)
> set @.SqlCmd = 'dir ' + @.Path + '*.* /a-d /o:d /t:c /n /B'
> create table #DirList
> (
> RowID int identity,
> FileOnly varchar(400)
> )
> insert #DirList ( FileOnly )
> exec master..xp_cmdshell @.SqlCmd
> -- Last full backup ( .bak )
> declare @.FullBackup varchar(300)
> declare @.CurrentID int
> Select top 1
> @.CurrentID = RowID,
> @.FullBackup = @.Path + FileOnly
> from #DirList
> where patindex( '%.bak%', FileOnly) > 0
> order by RowID desc
> -- if @.FullBackup is NULL return -1
> Set @.SqlCmd = 'restore database [' + @.DBName + '] from disk = ''' +
@.FullBackup + ''' with norecovery'
> print @.SQlCmd
> if @.DoItOrJustPrint = 'Y'
> exec(@.SQLCmd)
> Declare @.TransLog varchar(300)
> Set @.TransLog = 'dummy'
> While @.TransLog is Not NULL
> Begin
> Set @.TransLog = NULL
> Select top 1
> @.TransLog = @.Path + FileOnly,
> @.CurrentID = RowID
> from #DirList
> where patindex( '%.trn%', FileOnly) > 0
> and RowID > @.CurrentID
> order by RowID
> if @.TransLog is not NULL
> Begin
> Set @.SqlCmd = 'restore log [' + @.DBName + '] from disk = ''' +
@.TransLog + ''' with norecovery'
> print @.SQlCmd
> if @.DoItOrJustPrint = 'Y'
> exec(@.SQLCmd)
> End
> End
>
> Set @.SqlCmd = 'restore database [' + @.DBName + '] with recovery'
> print @.SQlCmd
> if @.DoItOrJustPrint = 'Y'
> exec(@.SQLCmd)
> drop table #DirList
> return (0)
>|||Thank you all for your comments.
I'm glad to see I wasn't completely off track.
I am a bit surprised with t:c vs t:w. Wouldn't that
cause issues if the full backup took more than 15minutes ?
( It wont in my case - but I did think of that ).
What I am trying to write is something that can be run
in the wee hours of the morning, by some overstressed
service technician, without a microscopic examination of
file names and a touch typing course.
Pretty please Mr Microsoft - It wouldn't be too hard to put
some MSDB data into the T.Log backups would it?
Thanks Again
AJ
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message news:uygo6aAgDHA.1648@.TK2MSFTNGP09.phx.gbl...
> I'd just polish the script. I'd like to point out the following, though:
> * You may want to change dir /t:c to dir /t:w
> * If your directory may have files other than the database/log backup files,
> you may want to check
> each file with RESTORE HEADERONLY to make sure that it's a backup file
> * Instead of relying on the file date/time to determine the order of the
> restore, you can also use
> RESTORE HEADERONLY to make sure that the order is correct.
> * Using RESTORE HEADERONLY, you can even determine whether you have gaps in
> your log
> backups by inspecting the LSN's (log sequence numbers)
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "Andrew John" <aj@.DELETEmistrose.com> wrote in message
> news:O5yClz$fDHA.616@.TK2MSFTNGP11.phx.gbl...
> > Dear knowledgeable people,
> >
> > Is there a simple way to restore the latest full backup, plus all the
> transaction
> > log backups on to another server. I know you can on the same server,
> 'cause
> > all the backup history is there in MSDB. But how many people back msdb up
> > every 15 minutes?
> >
> > Consider the case:
> >
> > Full Backup of Database @. 0300 each day
> > T.log backups every 15 minutes
> > Crash at 19:00 takes out entire server, but not the backup files 'cause
> > you cunningly configured File Replication Service to copy them elsewhere.
> >
> > When restoring, do you really have to type 60+ "restore log ... with no
> recovery"
> > commands (without typos) to get the database back?
> >
> > i.e Should I polish up the following script ( error handling /
> differential backups ...)
> > and/or write my own .exe to intelligently parse a backup directory, or am
> I
> > totally missing the point? ( Me programmer not normally sysadmin ).
> >
> > Regards
> > AJ
> >
> > CREATE procedure spAJGRestoreAsFarAsYouCan
> > (
> > @.Path varchar(200) = 'c:\mssql\backup\',
> > @.DBName varchar(30) = 'ALC',
> > @.DoItOrJustPrint char(1) = 'Y'
> > )
> > as
> >
> > set nocount on
> > -- Get list of files in directory
> > declare @.SqlCmd varchar(300)
> > set @.SqlCmd = 'dir ' + @.Path + '*.* /a-d /o:d /t:c /n /B'
> >
> > create table #DirList
> > (
> > RowID int identity,
> > FileOnly varchar(400)
> > )
> >
> > insert #DirList ( FileOnly )
> > exec master..xp_cmdshell @.SqlCmd
> >
> > -- Last full backup ( .bak )
> > declare @.FullBackup varchar(300)
> > declare @.CurrentID int
> >
> > Select top 1
> > @.CurrentID = RowID,
> > @.FullBackup = @.Path + FileOnly
> > from #DirList
> > where patindex( '%.bak%', FileOnly) > 0
> > order by RowID desc
> >
> > -- if @.FullBackup is NULL return -1
> >
> > Set @.SqlCmd = 'restore database [' + @.DBName + '] from disk = ''' +
> @.FullBackup + ''' with norecovery'
> > print @.SQlCmd
> > if @.DoItOrJustPrint = 'Y'
> > exec(@.SQLCmd)
> >
> > Declare @.TransLog varchar(300)
> > Set @.TransLog = 'dummy'
> > While @.TransLog is Not NULL
> > Begin
> >
> > Set @.TransLog = NULL
> > Select top 1
> > @.TransLog = @.Path + FileOnly,
> > @.CurrentID = RowID
> > from #DirList
> > where patindex( '%.trn%', FileOnly) > 0
> > and RowID > @.CurrentID
> > order by RowID
> >
> > if @.TransLog is not NULL
> > Begin
> > Set @.SqlCmd = 'restore log [' + @.DBName + '] from disk = ''' +
> @.TransLog + ''' with norecovery'
> > print @.SQlCmd
> > if @.DoItOrJustPrint = 'Y'
> > exec(@.SQLCmd)
> > End
> > End
> >
> >
> > Set @.SqlCmd = 'restore database [' + @.DBName + '] with recovery'
> > print @.SQlCmd
> > if @.DoItOrJustPrint = 'Y'
> > exec(@.SQLCmd)
> >
> > drop table #DirList
> >
> > return (0)
> >
> >
>|||> I always like using backup / restore for version control as it
> controls dev data versions along with schema objects etc.
IMHO, DDL (and selected DML) belongs in a version control system. This
facilitates new application installs as well as upgrades. Test data can
also be stored but its not as important to keep that under version
control.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:eWKx7SAgDHA.1748@.TK2MSFTNGP10.phx.gbl...
> Hi Andrew.
> I regularly need to do this on software dev projects for testing,
release
> etc.. I always like using backup / restore for version control as it
> controls dev data versions along with schema objects etc. So, it
sounds like
> my needs are similar to yours. (c:
> My approach is to use similar scripts / app logic, but there's a fair
bit of
> complexity as there's a full app with additional features not relevant
to
> your question, so I can't easily post scripts as such.
> The feature that I use though is to restore file headers, extract LSNs
&
> apply in order, which works whether the files are .bak, .diff or .trn.
I've
> found that use of .diffs mitigate your "60+ retore log statements"
problem
> significantly. Perhaps you could adjust your script to include .diff,
> restore headers & retore log / diff / db in LSN order?
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "Andrew John" <aj@.DELETEmistrose.com> wrote in message
> news:O5yClz$fDHA.616@.TK2MSFTNGP11.phx.gbl...
> > Dear knowledgeable people,
> >
> > Is there a simple way to restore the latest full backup, plus all
the
> transaction
> > log backups on to another server. I know you can on the same
server,
> 'cause
> > all the backup history is there in MSDB. But how many people back
msdb up
> > every 15 minutes?
> >
> > Consider the case:
> >
> > Full Backup of Database @. 0300 each day
> > T.log backups every 15 minutes
> > Crash at 19:00 takes out entire server, but not the backup files
'cause
> > you cunningly configured File Replication Service to copy them
elsewhere.
> >
> > When restoring, do you really have to type 60+ "restore log ...
with no
> recovery"
> > commands (without typos) to get the database back?
> >
> > i.e Should I polish up the following script ( error handling /
> differential backups ...)
> > and/or write my own .exe to intelligently parse a backup directory,
or am
> I
> > totally missing the point? ( Me programmer not normally sysadmin ).
> >
> > Regards
> > AJ
> >
> > CREATE procedure spAJGRestoreAsFarAsYouCan
> > (
> > @.Path varchar(200) = 'c:\mssql\backup\',
> > @.DBName varchar(30) = 'ALC',
> > @.DoItOrJustPrint char(1) = 'Y'
> > )
> > as
> >
> > set nocount on
> > -- Get list of files in directory
> > declare @.SqlCmd varchar(300)
> > set @.SqlCmd = 'dir ' + @.Path + '*.* /a-d /o:d /t:c /n /B'
> >
> > create table #DirList
> > (
> > RowID int identity,
> > FileOnly varchar(400)
> > )
> >
> > insert #DirList ( FileOnly )
> > exec master..xp_cmdshell @.SqlCmd
> >
> > -- Last full backup ( .bak )
> > declare @.FullBackup varchar(300)
> > declare @.CurrentID int
> >
> > Select top 1
> > @.CurrentID = RowID,
> > @.FullBackup = @.Path + FileOnly
> > from #DirList
> > where patindex( '%.bak%', FileOnly) > 0
> > order by RowID desc
> >
> > -- if @.FullBackup is NULL return -1
> >
> > Set @.SqlCmd = 'restore database [' + @.DBName + '] from disk = ''' +
> @.FullBackup + ''' with norecovery'
> > print @.SQlCmd
> > if @.DoItOrJustPrint = 'Y'
> > exec(@.SQLCmd)
> >
> > Declare @.TransLog varchar(300)
> > Set @.TransLog = 'dummy'
> > While @.TransLog is Not NULL
> > Begin
> >
> > Set @.TransLog = NULL
> > Select top 1
> > @.TransLog = @.Path + FileOnly,
> > @.CurrentID = RowID
> > from #DirList
> > where patindex( '%.trn%', FileOnly) > 0
> > and RowID > @.CurrentID
> > order by RowID
> >
> > if @.TransLog is not NULL
> > Begin
> > Set @.SqlCmd = 'restore log [' + @.DBName + '] from disk = ''' +
> @.TransLog + ''' with norecovery'
> > print @.SQlCmd
> > if @.DoItOrJustPrint = 'Y'
> > exec(@.SQLCmd)
> > End
> > End
> >
> >
> > Set @.SqlCmd = 'restore database [' + @.DBName + '] with recovery'
> > print @.SQlCmd
> > if @.DoItOrJustPrint = 'Y'
> > exec(@.SQLCmd)
> >
> > drop table #DirList
> >
> > return (0)
> >
> >
>|||> I also advocate using VSS, this is really just supplemental dev team
> procedure.
I see what you mean now. Backup / restore certainly has its place for
taking snapshots to aid iterative testing. I'm not a big fan of VSS but
there are other better, albeit much more expensive, source control
products out there.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:e3v3LvEgDHA.4024@.TK2MSFTNGP11.phx.gbl...
> Experience has made me (slightly) weary of VSS which is enough to
warrant
> extra protection! So, on my projects, I have been supplementing
version
> control of database objects by implementing fully logged backups (with
> transaction log marks in sql2k or timestamps in earlier versions).
With this
> approach you get bullet-proof backup & recoverability of all db source
/
> objects and data thrown in as well. This approach works on any
platform.
> Assuming the project doesn't have massive test / dev data
requirements, it's
> easy to store these on tape or ide media or just mix into a business's
> normal backup procedures.
> Being able to easily restore dev / test environments to marks or
specific
> points in time is a powerful technique for regression testing. As a
side
> benefit you also get early indications of capacity requirements for
backup /
> restore which a data tier architect will find useful when documenting
> capacity expectations.
> I also advocate using VSS, this is really just supplemental dev team
> procedure.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:ez8u7kEgDHA.3896@.tk2msftngp13.phx.gbl...
> > > I always like using backup / restore for version control as it
> > > controls dev data versions along with schema objects etc.
> >
> > IMHO, DDL (and selected DML) belongs in a version control system.
This
> > facilitates new application installs as well as upgrades. Test data
can
> > also be stored but its not as important to keep that under version
> > control.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > SQL FAQ links (courtesy Neil Pike):
> >
> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > --
> >
> > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> > news:eWKx7SAgDHA.1748@.TK2MSFTNGP10.phx.gbl...
> > > Hi Andrew.
> > >
> > > I regularly need to do this on software dev projects for testing,
> > release
> > > etc.. I always like using backup / restore for version control as
it
> > > controls dev data versions along with schema objects etc. So, it
> > sounds like
> > > my needs are similar to yours. (c:
> > >
> > > My approach is to use similar scripts / app logic, but there's a
fair
> > bit of
> > > complexity as there's a full app with additional features not
relevant
> > to
> > > your question, so I can't easily post scripts as such.
> > >
> > > The feature that I use though is to restore file headers, extract
LSNs
> > &
> > > apply in order, which works whether the files are .bak, .diff or
.trn.
> > I've
> > > found that use of .diffs mitigate your "60+ retore log statements"
> > problem
> > > significantly. Perhaps you could adjust your script to include
.diff,
> > > restore headers & retore log / diff / db in LSN order?
> > >
> > > HTH
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "Andrew John" <aj@.DELETEmistrose.com> wrote in message
> > > news:O5yClz$fDHA.616@.TK2MSFTNGP11.phx.gbl...
> > > > Dear knowledgeable people,
> > > >
> > > > Is there a simple way to restore the latest full backup, plus
all
> > the
> > > transaction
> > > > log backups on to another server. I know you can on the same
> > server,
> > > 'cause
> > > > all the backup history is there in MSDB. But how many people
back
> > msdb up
> > > > every 15 minutes?
> > > >
> > > > Consider the case:
> > > >
> > > > Full Backup of Database @. 0300 each day
> > > > T.log backups every 15 minutes
> > > > Crash at 19:00 takes out entire server, but not the backup files
> > 'cause
> > > > you cunningly configured File Replication Service to copy them
> > elsewhere.
> > > >
> > > > When restoring, do you really have to type 60+ "restore log ...
> > with no
> > > recovery"
> > > > commands (without typos) to get the database back?
> > > >
> > > > i.e Should I polish up the following script ( error handling /
> > > differential backups ...)
> > > > and/or write my own .exe to intelligently parse a backup
directory,
> > or am
> > > I
> > > > totally missing the point? ( Me programmer not normally
sysadmin ).
> > > >
> > > > Regards
> > > > AJ
> > > >
> > > > CREATE procedure spAJGRestoreAsFarAsYouCan
> > > > (
> > > > @.Path varchar(200) = 'c:\mssql\backup\',
> > > > @.DBName varchar(30) = 'ALC',
> > > > @.DoItOrJustPrint char(1) = 'Y'
> > > > )
> > > > as
> > > >
> > > > set nocount on
> > > > -- Get list of files in directory
> > > > declare @.SqlCmd varchar(300)
> > > > set @.SqlCmd = 'dir ' + @.Path + '*.* /a-d /o:d /t:c /n /B'
> > > >
> > > > create table #DirList
> > > > (
> > > > RowID int identity,
> > > > FileOnly varchar(400)
> > > > )
> > > >
> > > > insert #DirList ( FileOnly )
> > > > exec master..xp_cmdshell @.SqlCmd
> > > >
> > > > -- Last full backup ( .bak )
> > > > declare @.FullBackup varchar(300)
> > > > declare @.CurrentID int
> > > >
> > > > Select top 1
> > > > @.CurrentID = RowID,
> > > > @.FullBackup = @.Path + FileOnly
> > > > from #DirList
> > > > where patindex( '%.bak%', FileOnly) > 0
> > > > order by RowID desc
> > > >
> > > > -- if @.FullBackup is NULL return -1
> > > >
> > > > Set @.SqlCmd = 'restore database [' + @.DBName + '] from disk =''' +
> > > @.FullBackup + ''' with norecovery'
> > > > print @.SQlCmd
> > > > if @.DoItOrJustPrint = 'Y'
> > > > exec(@.SQLCmd)
> > > >
> > > > Declare @.TransLog varchar(300)
> > > > Set @.TransLog = 'dummy'
> > > > While @.TransLog is Not NULL
> > > > Begin
> > > >
> > > > Set @.TransLog = NULL
> > > > Select top 1
> > > > @.TransLog = @.Path + FileOnly,
> > > > @.CurrentID = RowID
> > > > from #DirList
> > > > where patindex( '%.trn%', FileOnly) > 0
> > > > and RowID > @.CurrentID
> > > > order by RowID
> > > >
> > > > if @.TransLog is not NULL
> > > > Begin
> > > > Set @.SqlCmd = 'restore log [' + @.DBName + '] from disk = '''
+
> > > @.TransLog + ''' with norecovery'
> > > > print @.SQlCmd
> > > > if @.DoItOrJustPrint = 'Y'
> > > > exec(@.SQLCmd)
> > > > End
> > > > End
> > > >
> > > >
> > > > Set @.SqlCmd = 'restore database [' + @.DBName + '] with recovery'
> > > > print @.SQlCmd
> > > > if @.DoItOrJustPrint = 'Y'
> > > > exec(@.SQLCmd)
> > > >
> > > > drop table #DirList
> > > >
> > > > return (0)
> > > >
> > > >
> > >
> > >
> >
> >
>