Scheduling database backups
You can backup your data at regular hourly, daily and weekly intervals, using three different backup methods - full, differential and incremental (transaction log) backups. With a backup strategy that enables these three backup types, you can restore databases to any hour of any specific day of the week. This suggested backup strategy is based on rigorous lab tests. You may choose any backup strategy which is acceptable to your organization:
- Do not create overlapping schedules for backups since only one backup at a time can be executed.
- Start with a full backup of the database.
- Create a full backup for weekend nights.
- Create a differential backup for every night of a working day.
- Create a transaction log backup for every hour (exclude hours when full and differential backups are running).
- Backup to a remote machine or use a local drive and copy the backup files to a remote server.
- Save four full database backup generations. That is, save four weeks of full database backups.
- If you save the last two full, six differential, and 24 incremental database backups so that they are stored on the network, you can restore any database to any hour of any day during the last week, or to the last full backup for the previous week.
Creating a backup job using the maintenance plan wizard
Before you begin:
- Decide on your database backup strategies. For more information, see Database backup and restore.
- Decide on backup job names that follow System Monitor naming conventions.
- Ensure the user running the SQL Server Management Studio has sysadmin privileges.
To configure a recurring backup, follow the steps below:
Step 1 - Open the SQL Server Management Studio (SSMS), and click Object Explorer.
Step 2 - Connect to the relevant server.
Step 4 - Right-click the Management folder and select the Maintenance Plan Wizard.
Step 5 - Select the Backup task. Follow the steps of the wizard to create a maintenance plan, according to your database backup strategies.
For more information on using the wizard, see https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/use-the-maintenance-plan-wizard