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
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.