Backing up SQL DB more frequently

Question

I currently have a scheduled task that fires off each night at 2 AM that calls SQLCMD.exe and passes it a .sql script to run for the backup (shown below). We're a pretty small company with growing needs due to major growth on the business side. Losing 1 days of data at this point would cost tens of thousands of dollars vs a couple hundred this time last year. Until I can migrate this DB platform to a different solution where data mirroring occurs with major redundancy like SQL Azure, what is the best thing I can do to get more frequent backups? Does this script below force the DB to be offline? Can I run this script with users interacting with the DB?

USE CompanyCRM;
GO
BACKUP DATABASE CompanyCRM
TO DISK = 'D:\CRMBackups\CompanyCRMCRM.Bak'
   WITH FORMAT,
      MEDIANAME = 'CompanyCRM_Backup',
      NAME = 'Full Backup of CompanyCRM';
GO

Update

Wow, obviously a much more dedicated DBA community over here than on SO. Thanks for the feedback so far. Only thing missing is the "hows." I have shown the SQL command above that I'm using to do daily backups, but the incremental log backup examples are MIA. This is not a large DB, it currently runs on SQLExpress. When I say HA or SQL Azure, I'm specifically referring to the architecture in place that we do not have as a small business. This instance is currently running on our ONLY server. If that server crashes, our time to recover becomes a sticking point. This is why SQL Azure becomes attractive.

Answer 1

EDIT, from your update

As you have said that you can loose 1 days worth of data then I would just put the databases in SIMPLE recovery mode. You could then do a FULL each morning and/or evening. If you wanted to cover yourself during the day you might through in a differential backup of the database, one of those just in case situations. This will capture any changes made since the full backup. If I know a time frame where a lot of input is happening I might throw this type of backup in there after it is completed. It can save folks time in recoverying so they don't have to do extra data entry.

Since this is your only server I would make sure you are running DBCC CHECKDB against the databases. Backups don't do any good when you find out they are corrupt (I think someone mentioned this too). You can probalby find a few scripts out there to setup a scheduled task to check the SQL ERRORLOG for the DBCC message to catch any errors. SQL Server will not natively warn you of errors returned from DBCC messages, so unless you manually check each time a script that does it can help.

The differential backup command:

USE CompanyCRM; 
GO 
BACKUP DATABASE CompanyCRM 
   TO DISK = 'D:\CRMBackups\CompanyCRMCRM_diff.Bak'    
WITH FORMAT, DIFFERENTIAL,
MEDIANAME = 'CompanyCRM_Backup',       
NAME = 'Full Backup of CompanyCRM'; 
GO 

Answer 2

• Backups in SQL Server are non disrupting. I.e. the database stays operational. Read the documentation.
• Do a full backup every day, followed by shipped (copied off) LOG backups (again, the documentation has... documentation) more regularly - like every 15 minutes or so.

Answer 3

The first thing you need to do is figure out how much data you can afford to lose. Until then you'll have no idea how often to backup the database. This isn't a number that you should be coming up with. This is something which the business (or the CEO in a smaller company) would need to decide. The first number they are going to come back with is 0 minutes. Which can be done, but it going to be very expensive to do. In reality the smallest amount of data that you can take backups for is about every 2 minutes. If the amount of data changing in the system is small enough you can do backups every minute.

In order to do transaction log backups, which is what you'll need to do you'll need to put the database into FULL recovery mode.

If you can afford to lose 5 minutes worth of data then you'll probably want to do full backups daily, and transaction log backups every 5 minutes. If you can lose 15 minutes worth of data then you'll want to do full backups and transaction log backups every 15 minutes.

Another option would be to do weekly full backups, daily differential backups and transaction log backups every x minutes as I talk about above.

Keep in mind that the more often you need to do backups the more files you will need to restore in the event of a database failure or data deletion. It may make sense to do differential backups through out the day to shorted the amount of time that it takes to restore the database.

All backups which using the BACKUP database and BACKUP LOG statement are done online and do not stop users from accessing the database.

Answer 4

Lets say your have a common business scenario with your busiest time being: 9am-5pm Monday-Friday. Then I would suggest: Full backup at Sunday night. Differential backups at 8 am, 6 pm and 1 am (to reduce the recovery time). Log backups every hour or depending on what your business requires.

Depending on your retention period, you should have an automatic cleanup job to clear out the old backup files. All these can be created using SQL maintenance plans. Check this link out for SQL 2005.

You should store your backups on some form of redundant disk (mirrored), or you can use tapes for offsite storage. Users can continue to work on the system while the backups run.

Answer 5

I wouldn't do a full backup every night. If it is a large database then that could take a very long time, not to mention take up a lot of space on the media. Do a full backup every weekend and a differential backup every night. Then do a transaction log backup (assuming your database is in full recovery) every hour, or every half hour, but ensure these .bak and .trn files reside on a separate disk in case of disk failure.

Answer 6

Can you cloud synch your backups folder at night to get off-site storage? Since I'm pretty sure this is for a health care company, are there any that are secure enough for HiPA compliance? Or maybe just super-encrypt them?

Does the script at least put a copy of the backup on a network share? That way if the physical box blows up...