SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database backups best practice


Database backups best practice

Author
Message
Mh-397891
Mh-397891
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1496 Visits: 1514
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!!
Steve Jones
Steve Jones
SSC Guru
SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)

Group: Administrators
Points: 139550 Visits: 19413
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
My Blog: www.voiceofthedba.com
Kumar-386306
Kumar-386306
SSC-Addicted
SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)

Group: General Forum Members
Points: 417 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
ChiragNS
ChiragNS
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6359 Visits: 1865
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"
Mh-397891
Mh-397891
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1496 Visits: 1514
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.
thecosmictrickster@gmail.com
thecosmictrickster@gmail.com
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4646 Visits: 940
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

Mike Hinds
Mike Hinds
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1335 Visits: 1132
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 Hinds
Senior Database Administrator
1st Source Bank
MCP, MCTS
Mike Hinds
Mike Hinds
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1335 Visits: 1132
I should have said "7 pm ANY DAY this week".

Mike Hinds
Senior Database Administrator
1st Source Bank
MCP, MCTS
Mh-397891
Mh-397891
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1496 Visits: 1514
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!
Mike Hinds
Mike Hinds
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1335 Visits: 1132
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.

Mike Hinds
Senior Database Administrator
1st Source Bank
MCP, MCTS
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