Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Sep 24 '11 at 23:22

This question came from our site for professional and enthusiast programmers.

    
edited my answer to your update, hope it helps –  Shawn Melton Sep 28 '11 at 20:53

6 Answers 6

up vote 5 down vote accepted

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 
share|improve this answer
    
Thanks Shawn. I appreciate the good sound advice –  RSolberg Sep 28 '11 at 23:00
1  
@Shawn: OP said that "Losing 1 days of data at this point would cost tens of thousands of dollars vs a couple hundred this time last year", where did he say that he can lose 1 day worth of data?! –  Marian Feb 14 '12 at 22:28
  • 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.
share|improve this answer
4  
I think backup is something you dont need a howto for, but a full read of the documentation - it is business critical AND it is - seriously - important. Not cooking eggs style. Hire a pro. –  TomTom Sep 27 '11 at 5:55
2  
Too bad that I can't vote answers down on this site... –  RSolberg Sep 27 '11 at 7:44
1  
@RSolberg: TomTom's answer is valid, along with the other advices you got already. I would suggest that you don't expect any of the responders to show you really the basic syntax of backup. Though I see Shawn was kind enough to do that to. To design a BACKUP strategy is not enough. You need to pair it up with a RESTORE strategy so that everything will be valid when you need it. –  Marian Sep 28 '11 at 21:00
2  
@RSolberg: sorry for pissing you off. This is not intended. But how did this community not help? You have good and valid answers (and comments). Your own message was: "Losing 1 days of data at this point would cost tens of thousands of dollars vs a couple hundred this time last year." So you need a solid backup&restore strategy so you don't get there. A solid strategy comes from knowing very well the basics. Which come from reading and understanding the manual. Sorry if you understood anything else! –  Marian Sep 29 '11 at 8:07
2  
I agree with @RSolberg that most of the answers here don't show "how" to do it, but that's also because the question was lacking in some detail "what" you didn't understand Russell. You have to tell us a bit more about what you need, but I agree with you that the site here is not supposed to be about "RTFM n00b". Also, as you pointed out, you have 10k on Stack Overflow, so you surely understand flagging comments that are rude or disruptive. In the future, you should do that sooner, rather than boil over in frustration. –  jcolebrand Oct 26 '11 at 23:37

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.

share|improve this answer
    
I'm actually quite able to assess how much data we can lose. Small businesses require folks to wear many hats, as the CIO I tend to get involved with line of business decisions daily. –  RSolberg Sep 27 '11 at 5:28
1  
That'll make the discussion much shorter then. –  mrdenny Sep 28 '11 at 1:23

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.

share|improve this answer

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.

share|improve this answer
    
Tags were lost, this isn't a huge database. It is currently running on a SQLExpress instance. –  RSolberg Sep 27 '11 at 5:29
1  
Tens of thousands of dollars, running on Express? Interesting! –  Andrei Rinea Mar 20 '12 at 11:07
    
Not really. Depending what you store (no texts, no binaries) - that is 10 gigabyte of data. You can run an accounting system for a large company - SERIOUSLY large company - in 10 gigabyte. An online shop with 100.000 orders per day can easily do the shop side in 10 gigabyte. –  TomTom Jul 7 '13 at 8:50

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

share|improve this answer
    
Yes to all of the above. We actually backup to a network drive that is mirrored and we copy data out of that environment to a secure data center. –  RSolberg Sep 27 '11 at 5:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.