snippetsqlMinor
How to back up a small database in SQL Server 2008 R2 Express Edition
Viewed 0 times
express2008sqldatabasebacksmallhowserveredition
Problem
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?
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?
Solution
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:
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
Context
StackExchange Database Administrators Q#2986, answer score: 7
Revisions (0)
No revisions yet.