6

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?

7

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
  • 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
  • msdb is jobs, alerts, operators. Also maybe SSIS/DTS packages. SSRS adds some permissions there but this is for jobs... – gbn Dec 2 '11 at 9:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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