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 will shortly be migrating a database from SQL Server 2000 (part of SBS 2003) to SQL Server 2008 R2 Express Edition

The database is small, and there are only a few hundred short transactions per day - I'd like to keep everything as simple as possible from a recovery perspective whilst minimising the amount of lost data in the event of a failure

Can I just run a full backup every hour using Windows Scheduler? I already have a solution for long term archival of database backups which these could just plug in to

What can I do to back up the 'logins' - I understand they are not saved as part of a full database backup. I want to have everything necessary to be able to perform bare metal recovery if we have to

Anything else I need to think about?

share|improve this question
add comment

1 Answer 1

up vote 7 down vote accepted

To backup up logins, you'd backup the master database. For any jobs etc, it's be msdb

I assume you want to avoid some complexity by not having 96 log backups per day every 15 minutes.

Your approach to of hourly full backups will work, but I'd consider doing hourly or 30 minute differentials with a daily full backups. The combination of full/differential:

  • +consumes less space
  • +will give quicker backups
  • +can have more frequent backups (less average data loss)
  • -slightly increased restore time
  • -slightly increased complexity
share|improve this answer
    
Thanks, and I'll take your advice about differentials –  Jack Douglas May 27 '11 at 14:33
    
I couldn't have explained the process any better. The only think I could do was: 60 minutes times 24 hours = 1440. 1440 by 15 minutes = 96. So now I know there are 96 15 minutes per day. –  StanleyJohns May 27 '11 at 14:47
    
@Jack Douglas: How did this work out please? –  gbn Jun 2 '11 at 8:50
    
we are right in the thick of the migration now :) –  Jack Douglas Jun 2 '11 at 10:21
    
Do I need to back up msdb on Express (it has no Agent, so I guess no jobs but what about the "etc") –  Jack Douglas Dec 2 '11 at 9:26
show 1 more comment

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.