February Question: What is one of your best practices for making full backups and transactions log backups of your production databases?
Question of the Month | February 4, 2010 | 1:00 amPost your responses to the above SQL Aloha Question of the Month in the comments section below (at www.bradmcgehee.com if you are viewing this from a syndicated newsfeed). If you have more than one best practice you would like to offer, please enter each one as a separate comment. And don’t forget to enter your e-mail address when you post your response, so I can contact you if you win.
I’ll pick the winner that offers what I think is the most original, useful and complete answer to the question. If some of the responses are very similar, then I will select the one that was posted first. The winner will be announced on this page the first week of March. Good luck!
For more information, click on the Question of the Month tab above.
| This Month’s Prizes
|
The prizes this month are an Amazon.com voucher worth US$50.00 and a license for SQL Backup Pro with one year of support and upgrades worth US$994.00. Not familiar with SQL Backup Pro? There’s lots of info on the Red Gate website, and here’s what one customer has to say:
“It achieved almost 90% data compression, and the reduction in per-GB data costs resulted in 100% ROI in just one month!” Andy Hughes, Senior SQL Server DBA |
- March Question: What is the biggest mistake/problem you have ever found on a SQL Server instance, and how did you fix it?
- Winner of the February SQL Aloha Contest
- Questions About SQL Server Backups
- Do You Verify Your Database Backups?
- Play the T-SQL Alphabet Game
RSS
Twitter
Linked In
We do nightly fulls and logs every 15 minutes
We copy the files to different destinations for backup to tape, DBCC checks and test restorations.
To ensure we always have all the files needed for a restore we run a procedure that navigates the LSN chain from the earliest full backup in the directory to the most current log backup. This has alerted us to copies that have failed or not been readable, files that have been missed by the copy routines and the truncation of the transaction log during the day.
Lance, are you able to share the code you use to navigate the LSN chain? If so, post it here. If you can’t share it, we understand.
I have it a bit easy compared to most of your audience I think. And although our total mdf and ldf storage is well over 5TB’s covering over 300 separate databases, every single one is in the simple recovery mode. Nice, huh? Our policies…
Production is backed up nightly using Quest LiteSpeed at a compression ratio of three. We’ve found ‘three’ offers us the best tradeoff between speed and compression percentage (self plug). Prod database backups are kept off-site for three weeks minimum. Dev and staging databases are also backed up nightly using LiteSpeed but only kept one week. Nothing horribly original or exciting; just a good, solid plan for an organization using the simple recovery model.
Thanks and have a Grateful day…Troy
We schedule full backup nightly, transactions log hourly using Redgate SQLBackup. (The backups are encrypted) The full backups are written off to tape and the T-Log backups are mirrored to a second server.
On most DB that does not have night application routines, I schedulle a full backup at night and make transaction logs backup hourly.
All backups are done on disk and after that, a backup software is used to make a bkp files tape backup. In this way I can restore them on any production ou test server.
I make monthly restores from DB production backup on a test environment to check backup integrity.
We have a unique setup due to several security restrictions. We are running Red-Gate’s SQL Backup for backups: Tlogs every fifteen minutes, differential backups every night and full backups every Saturday. We have to move data securely to two different disaster recovery (DR) data centers (none of them trusted with another–long story). So we wrote a custom job and scripts for each type of backup that does the following:
1. It loops through all the databases on a server (about 300 databases on each server and we have 10 servers) and creates/executes the T-SQL code for the RG backup with error logging, notification, verification, compression, and encryption and stores the results along with filename, size, RG Status, and other information in a table.
2. When each database backup is complete, the job moves the SQB (RG backup file) to a central share on the NAS so that the operations backup can commit the backups to tape nightly and store them off site.
3. At the end of the backup job, the job creates a specially formatted text file that is moved to another share which is polled by a cron job and when it sees the file, it imports the specially formatted file and SFTP’s the backup files to the other two DR data centers.
4. At each of the DR data centers, there is a SQL job that runs every hour that grabs the SQB files and uses RG SQL Backup to restore the files accordingly with notifications. This effectively tests each backup to make sure it was valid and puts the DR centers in a state where we could switch over fairly quickly with a maximum of 30 minutes of data loss.
5. At the end of each day, a SQL job is run that pulls data from the MSDB database comparing the backups, our logging table and the sys.databases catalog so we can verify backups were successfully ran and have a nice report for management.
Finally, during our monthly maintenance window, we run various tests to ensure that the three environments are in sync and test out our DR conversion scripts/processes.
Whew!
We do full backups nightly as well as transaction logs hourly. Backups are done to disk and then backed up to tape. I keep 3 days woth of backups on disk as it is quicker to restore from disk than tape.
Biggest tip: backups are nice, but they’re worthless without the ability to restore. I test restore to test server twice a month. Don’t rely on backups to be good; test them frequently.
One of my best practices is the use of filegroup backups. Filegroup backups do full backups on individual filegroups within a database. Two useful scenario’s for a filegroup backup are:
1. For when you have partitioned data that will not be changed any longer.
2. If you’re dealing with a VLDB.
In my current scenario I use SQL Backup with a 15TB database that has meets scenario’s. By backing up filegroup’s as they “top-off” my differential’s are considerably smaller, thus finish faster.
While they are not perfect for every situation they’re invaluable when your databases start getting large and unwieldy. If you have Enterprise edition you can even restore filegroups individually while the database is online. This has the added advantage that you can bring up your database and then bring back your data, which is great if a full out restore would take weeks rather than hours. Some data sooner can be better than no data till later.
My thought of implmentation was weekly full backup , Daily differential Backup, Log back up every 10 minutes. (I feel this is suitable for all type of industries).
But due to our environment restrictions…..we do Daily 3 Am full Backup and after 1 hour we start log backup every 15 minutes till prior to full backup.
And we do use a third party tool TSM to backup all SQL server data to the tape drive every day.
Thanks
Sreekanth
One simple practive that I followis that system databases are backed up after users DB’s.
We test our backups daily by restoring them and compare schema and data.
This is done automatically and only when finding a problem SQL Agent sends an email.
Well, the answer to this will obviously depend heavily on the environment. To date, I’ve usually worked in environments that are somewhat smaller so we can afford to do Nightly Full backups to disk, Transaction log backups every 15 minutes to disk, and then back those up to tape for storage off-site. We often have enough space to keep several local copies of those backups.
One environment I worked in used log shipping so we had a test plan for our backups built-in to the process. We also had regular off-site DR practice where we were given a fresh set of servers, a copy of our backups, and told to restore everything to a working state. Because of the expense, that was usually an annual event. It was very useful to know whether or not our backups worked.
If I were working in a larger environment, I would very carefully consider either SQL 2008 native compression or using a 3rd party backup tool that enabled compression as well as splitting out data into filegroups to allow for backing up specific portions of the data at different times. It’s highly unlikely that trying to capture nightly full backups of VLDBs would work well in most situations. That would require splitting up the backups, partitioning the tables, logical planning of filegroups, archiving older/static data into certain filegroups, and a good test machine or at least a test machine that can handle restoring those backups.
The only thing that I’d be really concerned about that wasn’t mentioned much would be corruption. A backup of a corrupt database doesn’t help too much, but if you can’t catch that corruption until the backup has been restored, you have other issues. A good practice would be to restore the database, then run DBCCs against the restored copy, but if your environment allows enough time to run regular DBCCs before taking a full backup, that would be ideal. That way you don’t end up with several backups of a corrupt database.
Of course, the best practice that I can think of regardless of all of this is to test the restores. If you don’t test the ability to restore regularly, you are just setting yourself up for trouble down the line.
Well as far as backups are concerned the best practices will depend primarily on the business criticality of the databases and secondarily on the available infrastructure. Considering a general level of business criticality and morderate level of available infrastructure, the best practice for the full backups and the transaction log backups would be:
1) Nighly once full backup.
2) Transaction log backup at an interval of every 30 mins.
3) The scheduling needs to be done in such a way that the transaction log backups should not clash with the
full backs.
4) The sytem databases i.e the Master database, Model database and MSDB databases should be backed
up atleast once daily. These backups should be full backups. In case of changes happening in these
databases like creating of any new database or creating and scheduling a new job etc, the backup of the
system databases should be taken immediately.
5) The backup retention period will again depend upon the business criticality and available infrastructures. In
general cases business critical databases should have a retention period of atleast 30 days and for less
critical databases 7 days retention period of backups will be ok.
6) Last but not the least, as a practice, it should be must to regularly check the ability of restoration using the
available backups.
The environment at the last company I worked was quite simple but 24 x 7 (5 servers, 180 Gb data, no replication, 37 user dbs Full/Simple recovery mode). Transaction Log every hour was enough.
We took a daily basis backup job at night for all dbs and a weekly maintenance job (checkdb, reindex, update statistics, Updateusage for Sql Server 2k, Schrink and Full Backup, in that order) for user databases.
Bak and trn files were created in disk and copied to tape later. Retention period could be 5 years in some cases.
For the sake of security, restore verifyonly command was executed after each backup processed.
Each job step sent both an email and a message in the case of any error.
Due to business constraints, jobs were triggered by team of operators.
A point to note, before starting a maintenance job, a new transaction log job was executed for every user database (what if we need a point in time restore between last trn log and new full backup).
After that, Transaction Log job was scheduled to start after maintenance plan had finished, so we didn’t log any maintenance stuff. In our case, it was possible to do that.
For simplicity, we’ve created a maintenance plan job script which uses a temp table with db names to change selected databases in the whole job. You don’t need to worry about maintenance plan for a new db and we have a template depending on a kind of server (Prod, QA, Dev).
Thx.
Leo
I wrote:
“After that, Transaction Log job was scheduled to start after maintenance plan had finished, so we didn’t log any maintenance stuff. In our case, it was possible to do that.”
About transaction log schedule, our issue was a bit more complex.
We needed to get rid of maintenance stuff log. At that time we had no much space in Prod server.
So, we save ‘restore mode’ for all dbs in a temp table, change restore mode to simple in the beginning of maintenance plan, and restore to old restore mode in the end of maintenance plan.
Sorry for the inconvinience. I guess it’s not the goal of the question.
Leo
Frequent restoring is one of my best practices for making full backups and transaction log backups of my production database. Business needs require me to always have a copy of my production database with current data. Having this need forces me to do daily restores of my production database to another system. This helps in two ways; one, I validate daily that the backups I’m taking are good and not corrupt or failing. Second, this practice gives me some level of comfort that if the need for a production restore ever occurred, I will be prepared.
The February SQL Aloha Question of the Month is now over, and you can find out the winner here: http://www.bradmcgehee.com/2010/03/winner-of-the-february-sql-aloha-contest/. Thanks for taking the time to enter the February contest, and you can enter the March contest here: http://www.bradmcgehee.com/2010/03/march-question-what-is-the-biggest-mistakeproblem-you-have-ever-found-on-a-sql-server-instance-and-how-did-you-fix-it/.
Brad,
Thanks! The process that I wrote about above was fully tested as one company that I have helped for more than 20 years had an office in Santiago Chile that was for the most part destroyed in the recent earthquake. As the quake started, the Business Continuity Plan (BCP) was initiated and the backup and disaster recovery processes that I had helped setup worked. We only lost about 15 minutes of data (because it happened early in the morning and just around 15 minutes after the last log backup and log shipping). There were other snafu’s in the BCP from an organizational and communication perspective that we will fix but at least the data was safe. We have been fortunate that all of our staff has been accounted for and they are being helped getting their life back in order.
So, I guess the boy scout motto of “Be Prepared” is really worthwhile as you never know when your processes will be put to the test and how important of a test it will be.
Thanks much!
Shawn
Shawn, I can’t agree with you more, a proactive DBA always has to always be prepared for the worst.
We’re implementing a sql 2008 DB for a client very soon. It’s <1gb db to run on a single server.. but still not bad for my first production effort
The plan is to full backup weekly w/ nightly diffs. The tlog will be 15 minutes. They get copied off to the network and usb drive, which is taken offsite. I like the convenience of disk, especially for remote sites with non-IT personell.
I'll have a vmware guest to run on the production server, which will (hopefully) retain an identical configuration setup to the production machine. test restores etc can be done on the guest. It shouldn't run 24/7 lest it interfere with the production.
db scripts are from sqlservercentral.com