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?

Improve this question

1 Answer 1

Reset to default
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
Improve this answer
5
  • 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
    Commented May 27, 2011 at 14:47
  • @Jack Douglas: How did this work out please?
    – gbn
    Commented Jun 2, 2011 at 8:50
  • we are right in the thick of the migration now :)
    – Jack Douglas
    Commented Jun 2, 2011 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
    Commented Dec 2, 2011 at 9:26
  • msdb is jobs, alerts, operators. Also maybe SSIS/DTS packages. SSRS adds some permissions there but this is for jobs...
    – gbn
    Commented Dec 2, 2011 at 9:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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