Sql-server – Backing up SQL DB more frequently

backupsql server

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.

Best Answer

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
Related Question