A conversation on Backups, part 1...level: beginner

[Kevin3NF]

This topic is primarily aimed at the new SQL Server DBA, or someone installing a 3rd party product that uses SQL Server as a back-end storage mechanism.

Sr. DBAs know all this by heart, but feel free to contribute.

The goal here is to gather input from anyone that reads it and turn the thread into ERT static content.

Lets start with the basics:

1.  Why Backup?

*your server died
*you lost a hard drive
*your database became corrupted
*you need to refresh a dev/test environment.

2. What if I don't backup?

* As the company DBA, you bear primary responsibility for ensuring you can recover the data, so in a worst case scenario failure to backup may become a "resume generating event" for you.

* Best case, your network team may have done a backup you are not aware of, such as Veritas netbackup or some other utility

3. How often should I backup?

* It depends.  The frequency of backups varies widely based on a number of factors
-Database size - 2 TB takes a lot longer than 2GB
-Available space for the backup files - drives vs. tape
-time it takes to do the backup - hours vs. minutes
-tolerance for dataloss - could be days or seconds or even none
-read-only, or read-write data?
-others

*most of my clients start with a Full backup daily and transaction logs throughout the day at various intervals depending on the amount of activity.  If the database size makes full backups cumbersome and affects performance, you may need to look at a FUll/Differential/T-Log strategy

4. Recovery Model

Recovery model is simply a setting that determines how much you are able to recover in the event you need to restore.
*Full - all logged operations stay in the Transaction Log until the t-log is backed up
*Simple - all transactions are logged, but purged from the T-Log after being committed to the database (more accurately, they are purged at the next checkpoint)
*Bulk-Logged - certain bullk operations (Select Into, bcp, etc.) will have to be re-run as they are not fully logged...just the fact that the operation happened is logged.

That said, here is the summary I give my clients:
Use Full recovery model on any read/write production db, and Simple on any read-only or dev/test user databases.

System databases are in Simple recovery model by default.

5. How do I backup?

***See Books Online***

The Basic, simplest syntax is:
BACKUP DATABASE MyDatabase
TO DISK = 'C:\MyDatabase.bak'

You can use Enterprise Manager, but over time you need to know the t-SQL syntax well as there is bound to be a time where EM is not available and you are using Query Analyzer or osql to backup your database.



Feel free to add comments, correct me where I err or suggest additional info.

This pertains to SQL Server 7.0 and 2000.  I have not yet worked with 2005.

Kevin3NF