Backup Help

[Ty.Incog]

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]

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.sqlmonster.com

[Ty.Incog]

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]

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/techinf...ailability.asp

Simon

[Ty.Incog]

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]

(Ty******@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/uti...ll_in_file.asp
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[Greg D. Moore \(Strider\)]

<Ty******@gmail.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.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\share1 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_name'
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+'\'+backupfile +''' 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