Backup / Restore plan

Question

Post reply

Backup / Restore plan

bryan99y

SSCarpal Tunnel

Points: 4672
More actions
November 5, 2002 at 12:45 pm

#65078

SQL Server 2000 / Win2K A/S

For a one week backup plan, if use one full backup on Day 1 then only transaction log backups for the rest of the week.

Assuming have to rollback on day 5 to 3 hrs earlier that day. The solution would be to restore last full, then apply log backups

until point in time needed.

1. How long will this take?

2. Can this be done if the log backups are moved to tape daily and then moved back to a specific directory?

I am also wondering if, in ONE step, you can select say 25 transaction logs backups and stop after the 25th log is applied and bring the databse?

(When I get time I will test this myself. Just figure someone would know.)

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

zach_john SSCertifiable Points: 5922 More actions · Answer 1

Question one is impossible to tell, How big is the backup and how big are the transactions, how fast is the server, where are the backups stored, yak yak yak

Question two - yes.

Question 3 - Possibly

I don't like the plan at all, assuming you have a VLDB(very large database.

Add more database files on devices or more disk to your Raid.

Have you looked into differential backups.

Use multiple backup devices, this allows backups to be written to all devices in parallel.

You need to do more reading, but that should get you started. There's alot of good strategies here, do some searching.

John Zacharkan

SQLBill SSC Guru Points: 51440 More actions · Answer 2

I'd like to know how long that would take also, but I'm wondering why you aren't doing Differential backups. (I'm trying to get a system to test restores, but....). I do one full backup every Friday night, a differential every night but Friday and transaction logs every eight hours/every day.

Then for a restore I don't have to restore so many transaction logs.....just the full backup, whatever differential is the most recent and then whatever transaction backups that were made since the latest differential.

That's a LOT less to restore.

-Bill

bryan99y SSCarpal Tunnel Points: 4672 More actions · Answer 3

bryan99y

SSCarpal Tunnel

Points: 4672

November 5, 2002 at 1:33 pm

#440389

Ya I am more concerned about the time involved here. We don't have the space for alot of full's or differentials. So I was wondering about applying alot ot t-logs backups. Is this a quick process to apply alot lot t-logs?(specific setup needed here?)

I assume if they are all in a backup "set" it should be a fast process.

Greg Larsen SSC-Insane Points: 20891 More actions · Answer 4

If you manually do one restore at a time it might take a while. This is why I have added onto our backup process to build the restore script for me each time the backups are taken. Here is an article I wrote about this process, scripts included:

http://qa.sqlservercentral.com/columnists/glarsen/streamliningtherecoveryprocess.asp

Maybe you can use something here to minimize the setup time to build your restore scripts.

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Antares686 SSC Guru Points: 125444 More actions · Answer 5

Restores are all sequentially. You can build a script to run to do the restore and recover on the last restore but you will not be able to do better than that (EM can let you choose the set and it do the restores in sequence for you).

Now you will never be able to know the retore time as it all depends on the speed and location you store in.

I use various mixes.

Small databases I do full backups every night to a file on another drive array (takes a minute or two to restore depending on size).

Medium (over 2GB for me) I do Full backup once a week and a differential every night, then on a few were as current as possible data is required I throw in TL backups. The FUll and Diffs are to tape and the TL to another server for speed. Takes anywhere from 45 minutes to 2 hours to restore.

Large database (for me over 10GB) are full backup and fortunately differntial (no TL as current is not required. These all go to tape and I make no promises for same day restore (6hrs on one of them to complete restore).

Then I have a 30GB DB which is backed up full to disk once a week. This does not require any other as data can be recovered from other sources. As of yet I have not attempted a restore to get a time.

Best way for you to know your situations speed of recovery is to try a few test restores from various locations to a copy of the db.

Also, if at all possible, when availability is required make sure no access can do damage (delets, truncates, drops), have hardware to support failure tolerance, and use replication or log shipping to another server.

bryan99y SSCarpal Tunnel Points: 4672 More actions · Answer 6

Thanks for the info.!!!

One more quick question:

In SQL Server 2000 E.M., if the Full Recovery model is selected and there are hourly t-logs backups being taken for example. If go to the Restore Screen, it will show the Backup Set which includes the last Full + all the t-logs since the last full. Now, there is a box there for "Point in time restore."

Can any time be selected here that falls within any t-log backups since the last full?

If so, the database should do a 'recovery' to that point correct?

If this is correct then restoring one full + a week of T-Logs backups for example in a restore situation will not be much from an administration point of view.

Antares686 SSC Guru Points: 125444 More actions · Answer 7

Yes, select a valid point in time covered between the last Full backup and last TL backup will do exactly as you stated.

EM is a really nice and simple interface when doing restores. The only thing is if you move to tape avoid the "View COntents" button if you know the number of the file to restore, takes forever to read the actual tape drive itself in many cases.

bryan99y SSCarpal Tunnel Points: 4672 More actions · Answer 8

Since we are on the topic,

Backups:

Day 1: Full

Day 2: T-Logs Only

Day 3: Full

Day 4: T-Logs only

Will this restore process work:

1. Apply Day 1 full

2. Then Apply Day 2 T-Logs

3. Then Apply Day 4 T-Logs

- assume Day 3 full is unavailable. Bad tape for example.

I just want to know technically will this work. Can Day 3 full be skipped?

Antares686 SSC Guru Points: 125444 More actions · Answer 9

No, I believe you asked this before. The reason is when a Full backup runs it marks the commited sections of the TL as inactive, and therefor they should be overwritten as new transactions take place or they are flushed after a period of time (this is the supposed to portion of our programming, but sometimes you have to give it a hand to flush).

This is also the reason a missing TL backup between two will not allow restores that occurr beyond the last one before the missing one.

It is Differentials that confuse me. Since, as I recall, a differential requires the TLs to remain available, but if that is you do TL backups then not all will be there???

Edited by - antares686 on 11/05/2002 5:20:04 PM

bryan99y SSCarpal Tunnel Points: 4672 More actions · Answer 10

bryan99y

SSCarpal Tunnel

Points: 4672

November 5, 2002 at 6:14 pm

#440443

ya I asked this before but I did not get full agreement in responses.

So when a Full backup runs it marks the commited sections of the TL as inactive which causes it to be overwritten.

Is there literature on this?

I would like to see it as it is an important point.

Antares686 SSC Guru Points: 125444 More actions · Answer 11

Huh, I may be wrong, was just going back over the SQL BOL documentation and this is what I found

quote:

Important Microsoft® SQL Server™ does not truncate the transaction log when backing up the database. Therefore, when creating database backups only, it is recommended that the transaction log is set to be truncated automatically every time a checkpoint occurs in the database by setting the trunc. log on chkpt. database option to TRUE. This prevents that transaction log from becoming full, requiring the transaction log to be truncated manually.

Which I found in SQL7 BOL under "Creating and Restoring a Database Backup"

So based on that as long as you did not kill it, then the Full backup from before the lost one and all the TL backups should restore just fine. I will test this tomorrow and may do a write up on it after I re-review all the documentation and cross reference with MS training materials.