Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Proper way to backup databases


Proper way to backup databases

Author
Message
mo-585598
mo-585598
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 51
hi, i have a quick question about the proper way to backup databases. i have a maintenance plan to backup 2 main databases daily at 2 am - i have trans logs to backup every 3 hours daily. also i have the optimization and integrity to run weekly on these 2 databases
i have another maint plan for the master, model, and msdb to backup weekly only - nothing for the trans logs for these databases or optimization and integrity.
is this a correct way to do the backups? i have sql 2000sp4
thanks in advanced
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
There is no way to tell you what is the best because it varies by the situation. Certainly backing up daily and having transaction log backups are good. The decision comes down to, "How much data are you able to lose?". If 3 hours is good then you are all set, if not, then you need to increase transaction log frequency and might want to add differential backups during the day to reduce the # of steps required for a restore.

One other thing you don't mention is where are your backups going to? Are they on a different drive than the log and database files?



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
mo-585598
mo-585598
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 51
thank you for the response.
the backup is going to the local c drive and the trans log is going to the local d drive - the server gets backedup daily so i have the sql backups on tape from the nightly backups.
3 hours is ok for the trans logs -we can lose 3 hours -
i guess there are different ways to do backups - i am just wondering if using the maintenance option in sql is the most effective - i ask this becuase, from my experience, (which is very limited) i cant seem to get the backup and trans logs scheduled in the same maint job. i guess i could do one for each job - havent tried it yet -
thanks
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: Administrators
Points: 35973 Visits: 18725
The only thing I might add is be sure the backups (data and log) are going to drives separate from where you data (mdf/ndf) files are. If something happens to that physical drive, you want backups separate.

Follow me on Twitter: @way0utwestForum Etiquette: How to post data/code on a forum to get the best help
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47143 Visits: 44346
Add integrity checks for the system databases. Finding that master is corrupt and there's no clean backup (because it's been corrupt so long that the last good backup was deleted weeks back) it not a good thing.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Silverfox
Silverfox
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2740 Visits: 1161
Only think I would add, is that you if you are doing a full backup every day and tran log backups every 3 hours, you could be looking at more than 3 hours outage if you lose the drive the tran log backups are on.

potentially you could lose all the tran log backups and have to restore from last nights backup. it might be overkill, but i would periodically copy some of your backups on to another drive and then at the end of the day if you want to, remove them from the other drive after the full backup has been done.

--------------------------------------------------------------------------------------
Recommended Articles on How to help us help you and
solve commonly asked questions

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden
Managing Transaction Logs by Gail Shaw
How to post Performance problems by Gail Shaw
Help, my database is corrupt. Now what? by Gail Shaw
mo-585598
mo-585598
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 51
thanks for all the good suggestions --
one thing i found out was that the trans logs were failing - couldnt figure out why --finally i remeber to check the backup mode of the databases and of course they were in simple mode hence trans logs were not backingup. once i changed the mode to full the trans logs started to backup ok -
so i guess it is a good idea to check the backup mode right from the begining -
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: Administrators
Points: 35973 Visits: 18725
You can run in simple mode, but you cannot recover to a point in time other than your full backups. If that's important, then you do need to be sure you are in full or bulk logged mode.

Follow me on Twitter: @way0utwestForum Etiquette: How to post data/code on a forum to get the best help
JacekO
JacekO
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 606
I would strongly recommend you read 'Pro SQL Server 2005 Disaster Recovery" by James Luetkehoelter. The book pretty much explains all aspects of the 'data preservation' - backup being just one small step of the entire process. Most of the concepts do apply to SQL2K.

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search