Tuesday, October 07, 2008  Search
AADND Forums   Minimize
SearchForum Home
  Discussions  General  Small Shop SQL ...
 Small Shop SQL Server question
 
 2/19/2007 2:43:04 PM
evarlast
9 posts


Small Shop SQL Server question
As developers sometimes we are asked to do Systems Administration type tasks.  I find myself in this unfortunate situation right now and I am having a difficult time finding any documented better practice on the subject of backing up SQL Server databases.

Right now I do full backups to a file system file weekly, with a retention of 14 days, so I should always be able to go back at least 1 week without going to tape.  I do daily incremental backups to the same files.  I do hourly transaction log backups to a file system file.  These can get fairly large.

Anyone able to share their backup strategy?  I'd be interested to hear any non-production, dev/qa/test backup strategies too.

 2/19/2007 3:31:34 PM
Dave Baldwin
72 posts


Re: Small Shop SQL Server question

everlast,
I think you are going down the right path. It all comes down to how much data you can afford to lose. It looks like you will not lose any data in your backup scheme. By the way don't forget to test for a catastrophic failure. I've been at companies in the past where everything looked good, but when the server went down, the backups didn't work.

When it comes to dev/qa/test backup strategies. The way I set this up in the past is using 3 environments.

  1. Production
    This is normal production data backed up to your preference.
  2. Development
    This is an older snapshot of production data. This ranges from 2-8 months old. This data is refreshed when everyone (developers) agrees to. The reason for the lag is this is where all stored procedures are tested, and new tables or new columns are tested. Make sure you backup this database before you restore it from production data. There always seems to be one stored procedure you forgot to backup. 
  3. Testing
    This data is restored every day with production data. From here you can test code you are about to release into production with fresh data. This is where QA does most of their testing.

I hope this helps.

 - Dave B

 


Dave Baldwin
  Discussions  General  Small Shop SQL ...