If you aren’t doing backups, you aren’t doing your job.

Apparently people still haven’t figured out that taking backups of SQL Server databases is actually a requirement of having a SQL Server database.  There was a conversation recently on Twitter about yet another forum question where the OP talked about the crashed database that they had without any backups what so ever.

Some people don’t seem to understand that server hardware failing isn’t a question of if the hardware will fail, but rather when the hardware will fail.  If you don’t have backups (that you have verified will work) then you haven’t done what needs to be done to protect yourself from WHEN that hardware fails.  If you’ve been working with SQL Server for a while now and you haven’t had a hardware failure, then remember one simple thing… That hardware failure is coming, and when it comes to kick you in the junk, it is going to hurt.

But I work for a small company, and we can’t afford to do backups.

If you work for a small company, then doing backups if even more important.  If you are a small company, can you afford to lose all of the data within your SQL Server database?  How much money will it cost the company to rebuild all that data?  How much money will it cost the company if you loose all of the contacts that are stored within the database?  Will the company that you work for go out of business if the data within the database is lost?  Will you as the systems admin or database administrator be fired if the data within the database is lost?

Most of those questions I can’t answer for you, except for the last one.  If I was your manager, and you weren’t backing up the database that ran our business, and that system failed you would be out on the street in a heart beat.  Odds are as your manager I’ll be getting fired about an hour after you, since I didn’t make sure that you were backing up the data which runs the company.

But I don’t no how to do backups.

Setting up backups doesn’t need to be all hard and complex.  For companies with just a couple of database servers, you don’t need any sort of large complex backup solution, or hard written scripts.  Using the database maintenance plan wizard will work just fine for database backups.  People in large companies don’t like using it, and myself and other MVPs complain about it, because it doesn’t scale when you have a lot of servers.  But as far as the actual backups them selves, it’ll work just fine for you.

Assuming that you need to be able to loose as little data as possible, you’ll want to put your databases into full recovery mode (not the system databases, just your user databases).  Then setup full backups to run either daily or weekly, with transaction logs running at regular intervals.  This is where you need to ask your manager or the company owner how much data you can afford to loose.  This will tell you how often you need to setup the transaction log backups.

To setup all these backups you’ll want to setup two maintenance plan packages (I’m talking about SQL Server 2005 and newer here, I’ll talk about SQL 7 and SQL 2000 in a minute).  One to do the full backups and one to do the log backups.  In both maintenance plans use the “Back Up Database Task”.  In the plan which will do the full backups, change the backup type to “Full” and change the Database(s) option to “All databases”.  Change the backup folder to a folder on the local server which doesn’t have any databases on it.  If there aren’t any drives on the server that don’t have any databases on it, change it to a network path on another server, like a file server.  If that isn’t an option a large USB drive is better than nothing.  Schedule the job to run daily.

For the transaction log backup maintenance plan do the same thing, but change the “Backup type” option to “Transaction Log” and change the Databases to “All users databases (excluding master, model, msdb, tempdb)”.  Change the path to the same one as the full backups, or a similar one.  Schedule the job to run every 15 minutes (or whatever schedule is needed based on discussions with management).  You’ve now got a SQL Server backup, so that when the server fails you can restore.

You also need to clean up the backups, so that you aren’t keeping 5 years of backups on disk.  In the full backup maintenance plan add a “Maintenance Cleanup Task”.  Point this to the same folder, and have it delete files based on the file age (the bottom check box).  I recommend keeping at least 1 week or data on disk.

In SQL Server 2000 you just need to run through the maintenance plan wizard.  This wizard will allow you to select which databases you want to backup via both full and log backups.  Once this wizard has been completed there will be a couple of different jobs which are created to do these backups.  In some respects it is easily to setup the backups via the maintenance plans in SQL 2000 as you get a nice quick and easy wizard to run through.  SQL 2005 and up don’t really give you this feature as the wizard in SQL 2005 and up really sucks.  When going through this wizard do NOT select the automatically fix index problems checkbox.  You want to be notified of problems, but not be fixing the problems automatically.

But I backup the server using backup exec or something that backs up all the files.

In other words you aren’t doing any sort of backups.  Backing up the SQL Server database files from in Windows means that you aren’t doing anything useful for a couple of reasons.

1. SQL Server keeps the data files locked so that other applications can’t access them and corrupt the databases.  Because of this when a backup application comes through to backup the files it can’t and you get corrupt backups, which are in other words no backups.
2. Even if the files weren’t locked, the backups would still be useless because the data file and the log file wouldn’t be backed up at the same time.  With SQL Server the files must be in sync with each other.  If you are backing up the data file and log file, the data file would probably be backed up first, then when it is backed up the log would be backed up.  So the timestamp of the backup of the data file would be several minutes or hours (depending on the size of the data file) before the log file.  Depending on the amount of data change, the data file might not be consistent with it self.

If I haven’t convinced you yet that you should start doing backups (remember that part above about losing your job, and if they fire you for not backing up the databases I wouldn’t count of a good recommendation when you are job hunting), what will it take to get you to start actually doing it?  Now here I’m being serous, comment here, post a blog post, tell me on twitter (@mrdenny), whatever just let me know what it’ll take to get you to start doing the backups.

Denny