Microsoft SQL Server forum

Author

Backup Help

Ty.Incog@gmail.com

2005-05-24, 8:23 pm

I am implementing the following backup strategy

Sunday 2AM: Full Backup
Monday-Saturday 2AM: Differential Backup
Every 10 minutes: Transaction Log Backup

I have set this up on my production database and am now trying to
prepare my self for disaster when it strikes. I am copying the log and
database backups to a test server and attempting to make the the test
server just like the production server was at the time of the last
transaction log backup. I restore the full backup, then the
differential backups with no problems, since there are only up to 6
differential backups to restore.

The problem comes when I want to restore my transaction logs. I could
have around 100 logs to restore to get back to the point of the last
transaction log backup. Is there an interface in Enterprise Manager
that I can say restore Transaction Log x thru z?

Jeffrey Sheldon via SQLMonster.com

2005-05-24, 8:24 pm

Ty,

If you are trying to mimic production as test, you may want to look into
log shipping (if you have SQL 2000 Enterprise) or replication.

There is no interface that I know of to apply all those transaction log
backups.

You would have to generate a sql script to apply them and with the file
number changing this could be more time consuming.

Can you just restore test to the last full/differential backup?

Jeff

--
Message posted via http://www.webservertalk.com

Ty.Incog@gmail.com

2005-05-25, 9:23 am

This is an accounting system so I only can lose 10 minutes of data. I
am trying to prepare for worst case scenario, in which everything is
toasted and I need to get back to that state the system was in during
the last 10 minutes before the disaster. I'd rather figure this out now
when all is peachy.

Thanks for the suggestion on log shipping, I will research it more.
Other than log shipping, does anyone out there have any suggestions for
automating the application of these transaction logs?

Ty

Simon Hayes

2005-05-25, 11:23 am

There probably aren't any radically different ways to do it - by
definition, "log shipping" means the process of copying and restoring
transaction log backups in sequence. There are a number of articles on
the web which describe log shipping and how to implement it, or as
Jeffrey said, if you have Enterprise Edition then it's already built
in.

Depending on how big your company is, and how expensive downtime/lost
data in the accounting system would be, there are other options you
could look at, such as replication and clustering. But they are rather
more complex to plan and implement, so if log shipping will cover your
requirements (at least for now), then it's probably the best option.
There's some useful related information here:

http://www.microsoft.com/sql/techin...vailability.asp

Simon

Ty.Incog@gmail.com

2005-05-25, 1:23 pm

Let's throw log shipping and clustering out of the conversation. Is
there no automated way for me to restore a transaction log. Lets say a
company only does one full backup a week on Sunday at midnight, but
backs up the transaction log every 10 minutes. The database farts and
dies on Saturday. That means there are approximately 6 logs/hr x 24
hr/day x 6 days =864 logs to restore. Microsoft provided no way to
apply these logs? The only way I can see to accomplish this is using
RESTORE HEADERONLY to find the base LSN from the db backup, then using
RESTORE HEADERONLY on the log backup and iterating through each record
in the result set that has an LSN usable based upon the backup LSN.

Sounds like multiple days of coding and tinkering to get that script
working. Does anyone have a script already written? Does good 'ol
Mircosoft provide one?

Regards,

Ty

Erland Sommarskog

2005-05-25, 8:24 pm

(Ty.Incog@gmail.com) writes:
> Let's throw log shipping and clustering out of the conversation. Is
> there no automated way for me to restore a transaction log. Lets say a
> company only does one full backup a week on Sunday at midnight, but
> backs up the transaction log every 10 minutes. The database farts and
> dies on Saturday. That means there are approximately 6 logs/hr x 24
> hr/day x 6 days =864 logs to restore. Microsoft provided no way to
> apply these logs? The only way I can see to accomplish this is using
> RESTORE HEADERONLY to find the base LSN from the db backup, then using
> RESTORE HEADERONLY on the log backup and iterating through each record
> in the result set that has an LSN usable based upon the backup LSN.
>
> Sounds like multiple days of coding and tinkering to get that script
> working. Does anyone have a script already written? Does good 'ol
> Mircosoft provide one?

Since I rarely have to restore transaction logs, I might be missing
something, but there is a screen in Enterprise Manager which looks
spot on target. Right-click databases, select All tasks, and then
Restore. In the dropdown "Show all backups for all databases". Select
the database you want to restore backups for. In the box below, the
full backups and the log backups will appear (and I assume the
differential.)

I will have to admit never having tried it, but I'm pretty sure that
I did something like this in 6.5 days, and I can't see why MS would
drop such a feature.

If you want to look at scripts, this could be a starter:
http://www.karaszi.com/SQLServer/ u...
file.asp

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Greg D. Moore \(Strider\)

2005-05-25, 8:24 pm

<Ty.Incog@gmail.com> wrote in message
news:1117045734.520071.73720@g43g2000cwa.googlegroups.com...
> Let's throw log shipping and clustering out of the conversation. Is
> there no automated way for me to restore a transaction log. Lets say a
> company only does one full backup a week on Sunday at midnight, but
> backs up the transaction log every 10 minutes. The database farts and
> dies on Saturday. That means there are approximately 6 logs/hr x 24
> hr/day x 6 days =864 logs to restore. Microsoft provided no way to
> apply these logs? The only way I can see to accomplish this is using
> RESTORE HEADERONLY to find the base LSN from the db backup, then using
> RESTORE HEADERONLY on the log backup and iterating through each record
> in the result set that has an LSN usable based upon the backup LSN.

I don't know why you'd care about the LSNs at this point. Simply grab the
first log file after the DB backup has completed.

>
> Sounds like multiple days of coding and tinkering to get that script
> working. Does anyone have a script already written? Does good 'ol
> Mircosoft provide one?

Replace the database_name and \\remote_drive\share
1 with your own database
name and the disk where transaction log backups are stored.

This will generate a table with them listed in order with the restore syntax
added in, etc.

Simply cut and paste from the point you need forward.

Works great.

Bill is in the mail :-)

-- update dbname and directory and this will generate a quick list of
commands to restore backups.

create table #log_table
(
backupfile varchar(512)
)

create table #command_table
(
restore_commands varchar(1024)
)
go

declare @dbname sysname
declare @directory sysname
declare @cmdshellstring varchar(512)

select @dbname='Database_na
me'
select @directory='\\remote
_drive\share1'
select @cmdshellstring = 'dir ' + @directory + @dbname + '\*.trn /od /b'
insert #log_table execute master..xp_cmdshell @cmdshellstring

select 'restore log '+ @dbname + ' from
disk='''+@directory+
@dbname+''+backupfi
le +''' with norecovery' from
#log_table

select * from #command_table

select * from #log_table where backupfile like '%.bak' order by backupfile

drop table #log_table
drop table #command_table

>
> Regards,
>
> Ty
>

Sponsored Links