SQL Server Backups - Frequently Asked Questions and Best Practices

Backing up SQL Server û Frequently Asked Questions

1) Where can I find information on backing up SQL Server?

Answer: Most information about SQL Server can be found in Books Online, a SQL Server reference. To look up specific terms, choose the Index tab and type in the word ôBackupö without quotes. Books Online is usually installed on any computer that has had a typical install done of either SQL Server or the SQL Server Client Tools. If you donÆt see it under Start -> Programs -> Microsoft SQL Server -> Books Online, then you can install it from the SQL Server CD. Or, you can use the following link to access it: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp. Additionally, there are several books that can be gotten online or from a bookstore which enumerate the different ways of backing up SQL Server.

2) What is the best way to backup SQL Server?

ANSWER: There are at least four different methods of backing up SQL Server, two of them involve scheduling regular backups and two of them are manual methods. Which one you choose depends on how your SQL Server is set up and what your company requirements are. The methods are as follows:

Answer: Manually running the T-SQL command ôBackup Database <dbname>ö from Query
Analyzer.
b) Manually right clicking the database in Enterprise Manager and navigating to All
Tasks -> Backup Database.
c) Creating a scheduled backup in Enterprise Manager with the Database Maintenance
Planner located under the Tools menu.
d) Creating a scheduled backup with a saved query or a saved batch file containing the
T-SQL ôBackup Database <dbname>ö command.

Be sure to read Books Online for further details on the T-SQL command as I have only listed the first three words of the statement.

3) Why doesnÆt the manual backup process in Enterprise Manager show all my attached network drives?

ANSWER: Microsoft designed SQL Server so that it will not back up to any device that is not directly connected to the machine that the full installation of SQL Server is on. A device includes any and all hard drives on the machine (also called Disk Devices) and backup tape drives. It may be possible to backup to a zip drive, but I have not attempted this myself. Thumb drives (or USB removable drives) are usually not considered a local, or directly connected, device.

4) I need to make my SQL Server backup available on the network. How do I accomplish this?

ANSWER: The most obvious way, but not the most secure way, is to share the drive path that the SQL Server backups are made to. The default path for SQL 2000 backups is <driveletter>:\Program files\Microsoft SQL Server\MSSQL\Backup in a default instance of SQL Server or <driveletter>:\Program files\Microsoft SQL Server\MSSQL$<instancename>\Backup for a named instance. The default path for SQL 7 is <driveletter>:\MSSQL7\Backup.
The more secure method of making backups available is to allow the backup process to save to the local device, then to manually transfer a copy of it or schedule a Windows process to transfer a copy of it to another drive which is shared.

5) How do I tell the difference between Transaction Log backup files and regular Database backup files?

Answer: Transaction Log backups always end in the extension .TRN while regular Database Backups always end in the extension .BAK if you use Enterprise Manager to create your backups. If you use T-SQL or script your backups, you can almost use any extension you prefer, but I recommend sticking with .BAK and/or .TRN to avoid possible corruption or unfindable file problems.

6) My backups all have funny number sequences listed after the database name. How do I tell which one is the backup most recently made?

Answer: It sounds like you used the Database Maintenance Planner to create a scheduled backup. The DBMP always appends the datetime to the end of its backups so that it doesnÆt accidentally overwrite the previous backup devices. The way the numbers are read are YEARMMDDHHMM or YearMonthDayHourMinute. So if you have a database called Test and the backup was done on March 10, 2005 at 8:00 p.m., your backup name would look like Test_200503102000. The time is always listed in military format, so any p.m. dates have the hour added to 12, hence 8 p.m. is 2000 and 8 a.m. would be 0800.

7) IÆve scheduled my backups but only see one backup device and it doesnÆt have a datetime appended to its name. What is the problem?

Answer: There is no problem. It sounds like you scheduled your backup jobs with a batch file or a saved query. The T-SQL Backup statement doesnÆt allow for appending the datetime at the end of a backup device name. And the scheduled job always overwrites the backup from the last time if you donÆt manually change the backup device name in the batch file/saved query. The only way to avoid the overwrite is to move a copy of the backup to another place before itÆs overwritten or to use the DBMP to schedule your backups.

8) Are there any significant differences in using the DBMP schedule backups and doing a batch file / saved query to schedule my backups?

Answer: There are lots of differences. The first is the naming convention listed in questions 6 & 7. Additionally, with DBMP you can schedule your database to re-organize the data and index pages your DB is saved on, release the freed space back to the Operating System, to do an integrity check on your database and fix any minor problems, to schedule the regular backup and the Transaction Log backup at the same time instead of in two separate scheduled jobs, to record the job history in a table or text file, and to delete backups after a certain number of minutes, hours, days, weeks or months. However, there are some DBAÆs who swear by the T-SQL scheduled job method of backups over DBMP for their own reasons. I have not had any problems with the DBMP myself, but others have said there are times when it does not work properly. Before making your own decision, play with both on your Test Environment to see which works for you better.

9) All I have to do is backup my user databases and I should be fine in cases of emergency, right?

Answer: Unfortunately, that is not correct. I recommend backing up your Master, Model and MSDB system databases on a monthly basis. More often if you make major database changes. The Master database contains all of SQLÆs system definitions including collation method, system stored procedures, etc. The Model database contains the definitions of all your user databases, logins and objects. The MSDB database contains all your scheduled jobs (including DTS and Replication detail), triggers, user-defined functions and user created stored procedures. If you loose your hard drive and there is no way to recover it, having backups of these databases will save you from needing to recreate everything in SQL from scratch. YouÆll still have to do the installation of SQL on your new machine, but then you can restore your old system DBs and have everything you had before the crisis.

10) What is a Differential backup?

Answer: A Differential backup is one where all changes since the last complete database backup are saved. If you choose to do complete backups once a week and differential backups every day after, you could restore your complete backup, then the last differential backup and then the transaction backups since the last differential.

11) How do I decide if I need a Differential backup in my backup plan?

Answer: You need to determine which you require: a long backup and a quick restore or a quick backup and a long restore. Differential backups are good for quick backups (only changed datAnswer:, but they take longer to restore than a daily complete backup. A daily complete backup requires more hard drive space and takes longer to backup, but they are quicker to restore.

12) Is there a ôBest Practicesö method for doing database backups?

ANSWER: Absolutely! The best practices method is to make regular Database backups on a daily basis to your local drive. Also, depending on your Recovery Model, to make regular Transaction Log backups. Then to copy the full backups (and possibly the Transaction Log backups) to a removable media and store the secondary backup somewhere off-site. If your SQL Server hard drive crashes, you have a copy of the latest backup somewhere else that can be restored. If your building catches on fire or floods, you have a backup that has been stored off-site and can be brought back in so you donÆt lose all your data. Again, I recommend keeping a regular copy of the Master, Model and MSDB databases for emergencies like this. Additionally, I recommend having a test machine with exactly the same software and at least the same amount of hard drive space so you can randomly test your backups by restoring them to this test machine. There is nothing worse than having a great backup schedule only to find out when everything is gone that the backups are corrupted or un-restorable.

13) What does Recovery Model have to do with how my database backs up?

Answer: Everything, actually. Recovery Model determines what information, if any, your SQL Server writes to the Transaction Log.

14) What is the difference between Full, Bulk-Logged and Simple Recovery Model?

Answer: Full recovery model backs up every transaction ever made to the database. If you do a lot of inserts and deletes to tables, this can fill up your transaction log quite quickly.
Bulk-Logged recovery model backups up every transaction except any done via the BCP command, the Bulk Insert command, text and image operations such as WriteText and UpdateText, and most Create Index commands. This keeps the size of the Transaction Log down, but does not allow you to do a restore to point in time if you lose data.
Simple recovery model doesnÆt record much of anything to the Transaction Log and does not allow a point in time restore or a restore to point of failure. It is similar to the Truncate Log on Checkpoint option from SQL Server 7.

15) What is a Point In Time restore and why should it affect what recovery model I use for backups?

ANSWER:A Point In Time restore is literally the ability to restore your backups to a specific time. A good example is someone in the Accounting Department deletes or overwrites certain crucial transactions at 2:17 p.m. With a Full recovery model, you can restore your last full backup and then restore all Transaction Logs (including the one that runs at 3:00 p.m.) and tell it to only restore the transactions up to 2:16 p.m., a minute before the deletion happened. This feature is a handy one when transactions are the lifeblood of your business. Bulk-Logged only allows you to restore to the point of your last Transaction Log (the 2:00 p.m. one if you are running logs every hour). Then you have to re-run every last Bulk Insert, BCP, Text & Image operation and Create Index operation that was run since the last Transaction Log backup plus all the regular transactions between 2:00 p.m. and 2:16 p.m.

16) My Transaction Log is unexpectedly full. How do I empty it?

Answer: There are several ways to empty it. The first is to make a manual full database backup. This usually truncates (or empties) the Transaction Log. If you are unable to do a backup, there are other options. The first is to use the Truncate Log option with the T-SQL Backup Log statement. Read the options on this carefully as there are several commands that you can use with the Backup Log - Truncate option. Per Books Online, if you truncate the log, make sure to perform a full database backup immediately so you do not break the Transaction Log chain. Breaking the chain leads to the inability to restore Transaction Log backups when needed. The other option is to Detach your database and delete the .LDF file. LDF is the extension of the Transaction Log. DonÆt worry. ItÆll rebuild itself when you re-attached the database to SQL.

17) My recovery model is Full because I want to do point in time restores, but I have a major Bulk Insert / BCP operation to do. How can I accomplish this without filling up my Transaction Log?

Answer: Right-click your database, click on Properties and then click on the Options tab of the popup window. Temporarily change your Recovery Model to Bulk-Logged, click okay and continue with your Bulk Insert / BCP operation. When done, go back to the Options tab and change your Recovery Model back to Full.

18) What is Log Shipping and where do I find it on my SQL Server?

Answer: Log Shipping is a new (to SQL Server 2000) method of creating a standby SQL Server in case you lose your main SQL Server. It can be found in the DTS Import/Export Wizard on the screen where you choose what database you want to export. Log Shipping will only work if a single database is chosen, itÆs grayed out if more than one is chosen or if the database you clicked is a system db or one of MicrosoftÆs example DBs. Log Shipping creates a full database backup at the time of the job creation that transfers over to the new machine and then transfers and restores Transaction Logs on a regular basis to the new machine. Be warned that users cannot be accessing the standby machineÆs databases at the time of restore. Restore only works when no one is using the database.

19) No matter what I do, I canÆt find the Log Shipping or it is grayed out.

Answer:Verify you are using the Enterprise Edition of SQL Server. Log Shipping is only available in this edition. If your version is Enterprise Edition, verify you do not have multiple databases chosen in the database screen.









This FAQ was complied by Catadmin on March 10, 2005. Other contributors include SQLBill. You have my permission to distribute it as long as you keep these credits in place at the end. Additionally, if you have further questions you deem worthy of being added to this FAQ, you may email me at GoldPetalGraphics@Yahoo.Com. Please include the words ôBACKUP FAQö on the subject line so I know itÆs not spam.