Database backup and recovery solution

Recommended tutorial: SQL tutorial

SQL SERVER data Backup plan

The backup methods of SQL SERVER database mainly include full backup, differential backup, transaction log backup, etc. Based on data security requirements, the recommended backup methods are a full backup once a week, a differential backup once a day, and a transaction log backup every half hour.

By default, BACKUP DATABASE and BACKUP LOG permissions are granted to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

Ownership and permission issues with the physical files of the backup device may hinder the backup operation. SQL Server must be able to read and write to the device; the account under which the SQL Server service is running must have write permissions.

The backup file storage disk needs to be separated from the database file storage disk to avoid disk IO conflicts. The backup execution time is staggered with the database job execution time to prevent the backup from affecting the execution of the database job.

The SQL SERVER maintenance plan function can better realize automated backup. Start the SQL SERVER agent function on the database manager before using this function.

Full backup

1) Select Management-Maintenance Plan-Maintenance Plan Wizard

2) After opening the maintenance plan wizard, you can add a new maintenance plan and name the maintenance plan, as shown below:

3) Select the backup database (complete) maintenance task

4) Set up the database complete maintenance plan related Information, including backup database, backup set expiration time, backup file storage path

5) Select Next and then select Finish

6) Specify the execution cycle for the maintenance plan

As shown in the figure above, the plan type Select Repeat execution, select Weekly as the period, and execute the plan in the early morning of every Sunday.

7) After the backup plan is created, expired backup databases need to be automatically deleted to ensure efficient use of disk space. Create a cleanup maintenance task in the current maintenance plan to delete backup files whose retention time exceeds the set value.

As shown above, open the data backup plan you just created, drag the clear maintenance task from the maintenance plan task list into the box where the backup task is located; and drag The association arrow establishes the association between the backup task and the cleanup task. This association is used to specify the order of execution.

8) Set the folder and file retention period in the clear maintenance task

9 ) The above method is to complete the settings through the maintenance plan wizard, or through the new maintenance plan function. By creating a new maintenance plan, and then dragging the backup database task and clearing the maintenance tasks, the relevant settings are the same as above.

Differential backup

1) Select the maintenance plan-new maintenance plan

2) Set the maintenance plan name: differential backup

3) Open the differential backup plan and drag the backup database task and clear maintenance task to the current scheduled task box.

4) Set the backup type in the backup database task to differential, and set the backup type, back up the specified database, and back up the storage folder

5) Double-click to open the clear maintenance task, and set the folder and file retention period in the clear maintenance task

Transaction log backup

1) Select the maintenance plan - create a new maintenance plan

2) Set the maintenance plan name: transaction log backup

3) Open the differential backup plan and drag the backup Database tasks and clear maintenance tasks to the currently scheduled tasks box.

4) Set the backup type in the backup database task to transaction log, and set the backup type, back up the specified database, and back up the storage folder

5) Double-click to open the clear maintenance task, and set the folder and file retention period in the clear maintenance task

Database recovery Restore full backup

Restore full backup refers to restoring the complete backup file of the database and restoring the database to its complete state.

1) Select to restore the database

2) Under the general options, set the restore Target database name, source device (database complete file), etc.

3) Set the storage path of the restored database file, and choose to overwrite the existing database and roll back uncommitted transactions.

Restore full backup differential backup

This method is to restore the database to a differential backup status. After restoring the full backup, you can continue to restore the differential backup to the target database, which is used to update the data saved in the differential backup into the current database and restore the database to the state at the time of the differential backup.

1) Select the restore database

2) Under the general options, set the restore target database name, source device (database is complete files) etc.

3) Set the storage path of the restored database file, and choose to overwrite the existing database and not roll back uncommitted transactions on the database.

4) After the previous step is completed, the database is in the restoring state; you can continue to restore the database and select the differential backup file for the source device.

#5) Set the file storage path and database transaction log rollback method when restoring differential backup files.

Restore full backup differential backup transaction log backup

This method is to restore the database to the state when the transaction log was backed up. After restoring the full backup, you can continue to restore the differential backup to the target database and then continue to restore the transaction log backup. This is used to update the data saved in the differential backup and transaction log backup into the current database, so that the database can be restored to the state at the time of the transaction log backup.

1) Select the restore database

##2) Under the general options, set the restore target database name and source device (Database complete file) etc.

3) Set the storage path of the restored database file, and choose to overwrite the existing database and not roll back uncommitted transactions on the database.

4) After the previous step is completed, the database is in the restoring state; you can continue to restore the database and select the differential backup file for the source device.

5) Set the file storage path and database transaction log rollback method when restoring differential backup files.

6) After the differential backup and restore is completed, the database is in the restoring state; continue to select restore to restore the transaction log file to the current database.

Choose to restore the transaction log and select the general settings

Restore time point setting; by setting this time point, the database can be restored to any time point from the time of completion to the transaction log backup time period.

Set the option to roll back uncommitted transactions

After the execution is completed, the database returns to the available state and the data is updated to the state at the specified time. If no time is specified, it will be the state when the transaction log was backed up.

The above is the detailed content of Database backup and recovery solution. For more information, please follow other related articles on the PHP Chinese website!