Backup Debate...
-
We are trying to figure out the best way to backup our SQl 2k Databases.
First: Our primary database is about 90gigs, and then we have another 10 gigs of minor databases.
We have a 6 hour down time every night, midnight to 6am to do anything we want, so time isn't an issue.
Our Backup is an IBM LTO 3581 Tape Drive.
First way:
Midnight: Shut Down SQL
Arvserve with open file agent the entire sql drive, the logs, etc.
3am bring SQL back up online and purge the logs.
2:
Run a SQl backup of the databases and logs to a spare drives.
Backup the spare drives with the LTO
There is concern that someone could possible do something between the log backup and the purge, if the system isn't offline
Any hints appreciated..
Brad
Edited by - bly on 06/26/2003 06:51:10 AM
-
#2. No way I'd want to take my service down nightly, and no reason to as far as Im concerned. Definitely a good idea to back up to disk and from there to tape.
Andy
-
Hi,
I have been using the second method for a few years for the following reasons:
1.Our tape drive is not big enough to hold the DB backup created by ArcServe; it (our version, at least) does no compression using
their SQL backup engine. When I backup the .BAK file to tape, it shrinks down quite a bit.
2.Occasionally the tape backup fails for some reason or other. When I get in in the morning it would leave me with a problem, since I can't take the system away from my users. Using the disk backup, I can restart the tape job any time and have no affect on my users.
3.The disk backup runs very much faster than the tape job. Therefore, the chance of changes during that time are rather minimal.
4.Doing the backup to disk gives me an extra copy of the backup. I would certainly use the disk copy before trying to restore from the tape!
-
Am in agreement with the previous two. Backup to disks first. Backup those backups to tape.
If you use transaction logs, one quirk I have found is that you need to backup the transaction log prior to backing up the database. Not doing so made subsequent log restores after the db restore(in test) iffy. By doing it before the start of the backup, I have never had any problems restoring the logs to a restored db.
-
You might even want to consider performing a BCP out job on the tables in the database. BCP is very fast and the output is data only. Of course make sure you have a rebuild script to restore the database structure.
-
Yesterday,
All those backups seemed a waste of pay.
Now my database has gone away.
Oh I believe in yesterday.
Suddenly,
There's not half the files there used to be,
And there's a milestone
hanging over me
The system crashed so suddenly.
I pushed something wrong
What it was I could not say.
Now all my data's gone
and I long for yesterday-ay-ay-ay
Yesterday,
The need for back-ups seemed so far away.
I knew my data was all here to stay,
Now I believe in yesterday.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
We have a db that was 50GB of data. Went to Litespeed, shrank to 8GB. Time went from over an hour to under. I'd get Litespeed and run it to disk, then to tape.
Steve Jones
-
We use a variation of #2. We backup to disk, then copy to disk on another server. Then these are both backed up to tape. The reason we copy to the second server is our tapes are off-site and if the original server dies, we can pull the backups off the second server we copied them to!!
-
We have a 60+GB database with near 24x7 availability. We have a very short 5 minute window between 9:25 and 9:30 pm where we want to have a "snapshot" backup of the database that represents the start of nightly batch processing. We accomplish that with the following approach:
At 8:00 :
Backup Log <database> WITH TRUNCATE_ONLY
Backup Database <datebase> TO <datadisk> WITH INIT
Backup Log <database> to <logdisk> WITH INIT
Then at 9:25:
Backup Log <database> to <logdisk> with NOINIT
The log dump finishes in just a matter of seconds.
-
I want to know where the script that take the Db and copy it to another partition ??
and is it possible to copy DB get out of Database maintainace planner (that output data with specific names formats, including time and date)??
Alamir Mohamed
Alamir Mohamed
Alamir_mohamed@yahoo.com -
We use SQL Litespeed for all our SQL servers, then we back these backups to disk. It's cheap and heaps faster than native SQL backups. It shrunk our 30 gig database down to 3 gig and it's encrypted. Not to mention it's another great Australian product! 🙂
-
My vote is first goto disk then tape. tape is slow and you'll never make your 6 hour window.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply