Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Database backups best practice Expand / Collapse
Author
Message
Posted Thursday, December 27, 2007 3:18 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, December 7, 2014 9:00 AM
Points: 264, Visits: 1,513
We are planning to implement the following backup plan, please let me know your ideas:
1. Full database backup - weekly once with Truncate
2. Differential db backup - once every day with Truncate
3. Transaction log backups every 30 minutes.
For the full and differential db backup's - Truncate is it Okay??
If so, can you please refer me to the scripts which also includes truncate. Thanks!!
Post #436850
Posted Thursday, December 27, 2007 8:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:38 PM
Points: 31,368, Visits: 15,834
Full and diff backups don't use truncate.

The backup scheme depends on what works for your business. If they can tolerate finding a full, then a diff, then a ton of logs if the db fails at 7pm, then that's fine. If not, you might want to add other diff backups in there as well.

Be sure you keep everything you need since the last full.








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #436875
Posted Tuesday, January 1, 2008 12:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 26, 2013 3:12 AM
Points: 233, Visits: 246
Hi,

What is the size of the database?
What is the data loss can the your company afford?

let us know about it, then we can goa head about the suggestion for the backup Strategy.

Regards
Kumar
Post #437703
Posted Tuesday, January 1, 2008 1:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
Hi

A backup strategy mainly depends on the amount of data loss that you can take and also the maximum time you can spend on restoring the database in case anything happens.

Less frequent the backups more is the data loss and more the number of backups to be restored, more time taken to restore the database.

Need more details to come up with valuable suggestions but i think you may need more diff backups.




"Keep Trying"
Post #437704
Posted Friday, February 8, 2008 12:54 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, December 7, 2014 9:00 AM
Points: 264, Visits: 1,513
As the backup file size is big, we are thinking of truncating them, is this okay?
The database size we have is about 60 GB. Thanks.
Post #453419
Posted Monday, February 11, 2008 2:22 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 7:13 PM
Points: 2,665, Visits: 840
Are you talking about truncating or shrinking?

Truncating is only applicable to log backups.

Shrinking can apply to data and log files but not backup files.

If the backup file is 60GB, then that is how much data is in the database. The only way to reduce that is to clean out data from the database, which may or may not be appropriate. If the size of the backup is an issue, there are 3rd party tools which can compress them on the fly (e.g. RedGate SQL Backup or Quest LiteSpeed)

If your database is still growing, then shrinking is not a good idea - it will just grow again anyway.

If the log file got blown out for some reason, preferably one that is not going to reoccur, you could shrink it. Lookup DBCC SHRINKFILE in BOL.





Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
Post #454078
Posted Tuesday, February 12, 2008 7:08 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 8:37 AM
Points: 621, Visits: 877
We have a similar plan to what you propose, as our standard for about 60 servers, varying in size from 1/2 GB to 128 GB.

Sun 7:00 pm - Full backup WITH INIT
Mon-Sat 7 pm - Diff backup (append) to same backup device file
Midnite - 6:59, and 8:00 pm - 11:59, Sun - Sat : tran log backup every 15 mins. These are to a different backup device file (i.e, "DBname_Log.bak"), which gets an INIT every eve with either the Diff or the Full.

We can restore back to point-in-time TODAY, or 7 pm THIS WEEK. This has happened in practice a few times in the last two years.

If we need to go previous to Sun. Eve, we will pull needed files from a disk image on tape.

I think the INIT is what you're referring to by Truncate - it clears the old backup file on Sunday night, and the old Log backup file every evening.

For two of the larger applications we use LiteSpeed backup to save drive space and time, but otherwise the same strategy.


Regards, Mike
Post #454402
Posted Tuesday, February 12, 2008 7:21 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 8:37 AM
Points: 621, Visits: 877
I should have said "7 pm ANY DAY this week".

Regards, Mike
Post #454412
Posted Thursday, February 14, 2008 10:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, December 7, 2014 9:00 AM
Points: 264, Visits: 1,513
Mike Hinds (2/12/2008)
We have a similar plan to what you propose, as our standard for about 60 servers, varying in size from 1/2 GB to 128 GB.

Sun 7:00 pm - Full backup WITH INIT
Mon-Sat 7 pm - Diff backup (append) to same backup device file
Midnite - 6:59, and 8:00 pm - 11:59, Sun - Sat : tran log backup every 15 mins. These are to a different backup device file (i.e, "DBname_Log.bak"), which gets an INIT every eve with either the Diff or the Full.

We can restore back to point-in-time TODAY, or 7 pm THIS WEEK. This has happened in practice a few times in the last two years.

If we need to go previous to Sun. Eve, we will pull needed files from a disk image on tape.

I think the INIT is what you're referring to by Truncate - it clears the old backup file on Sunday night, and the old Log backup file every evening.

For two of the larger applications we use LiteSpeed backup to save drive space and time, but otherwise the same strategy.


Mike: Thanks for your response,
With the below:
"These are to a different backup device file (i.e, "DBname_Log.bak"), which gets an INIT every eve with either the Diff or the Full"
Did you mean that for the full/diff db backup files you are opting to have 1 days worth of files?
Actually with truncate I meant truncating the db bak files if possible, as the bak files are big I was thinking of truncating the size if possible, not sure if this is possible. Please advice. Thanks!
Post #455838
Posted Thursday, February 14, 2008 11:05 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 8:37 AM
Points: 621, Visits: 877
Mh, I have two separate backup files, "DbName.Bak" and "DbName_Log.bak". Each Sunday evening, when I run the Full backup, I add the WITH INIT command, which completely empties the file. You are correct that this can be thought of as truncating, but usually that word is used for the logfile, which is truncated whenever you do a Full or Differential backup of the database itself (not the log).

Mon - Sat I do a differential (which truncates the log), but this one does not INIT or empty the "DbName.Bak" file - instead the Differential backups append, so the "DbName.Bak" file grows each night until Sunday, when it is emptied with another INIT. I can look at the contents of the backup device and see a backup for each day starting at Sunday evening, through last night.

The log backups go into "DbName_Log.Bak" every 15 minutes between midnight and 7 pm. The job that runs the Full or Diff at 7 pm also runs an INIT on the "DbName.Bak" file, so it starts out empty at midnight, all 7 days. This means that during the day I can restore to any point-in-time, but if I go to a previous day I can only choose the 7 pm marks, which seems like a resonable compromise.

I hope this helps. Let me know if you'd like some example code to set up your jobs.


Regards, Mike
Post #455878
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse