Shulou(Shulou.com)05/31 Report--

This article mainly introduces what the SQL SERVER data backup scheme is, which is very detailed and has a certain reference value. Friends who are interested must finish reading it!

SQL SERVER data backup scheme

The backup methods of SQL SERVER database mainly include full backup, differential backup, transaction log backup and so on. According to the requirements of data security, the recommended backup method is a full backup once a week, a differential backup every day, and a transaction log backup every half an 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.

Issues with the ownership and permissions of the physical files of the backup device may hinder backup operations. SQL Server must be able to read and write to devices; the account running the SQL Server service 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 avoid backup affecting the execution of the database job.

The SQL SERVER maintenance plan function can better realize automatic backup. Before using this function, start the SQL SERVER agent function on the database manager.

Full backup

1) Select Management-maintenance Plan-maintenance Plan Wizard

2) after opening the maintenance Plan Wizard, you can add a maintenance plan and name it, as shown below:

3) Select backup database (full) maintenance task

4) set database complete maintenance plan related information, including backup database, backup set expiration time, backup file storage path

5) Select the next step, and then select finish

6) specify the execution cycle for the maintenance plan

As shown in the figure above, the plan type selects repeated execution, the cycle selects weekly, and the plan is executed in the early hours of every Sunday morning.

7) after the backup plan is created, the expired backup database needs to be deleted automatically to ensure the efficient use of disk space. Establish a cleanup maintenance task in the current maintenance plan to delete backup files that have been retained for longer than the set value.

As shown in the figure above, open the data backup plan you just established, drag the cleanup maintenance task from the maintenance plan task list into the box where the backup task is located, and drag the associated arrow to establish the relationship 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 cleanup and maintenance task

9) the above way is to complete the setting through the maintenance plan wizard, or through the new maintenance plan function, through the new maintenance plan, and then drag the backup database task to clear the maintenance task, and the relevant settings are the same as the above method.

Differential backup

1) Select maintenance plan-create a new maintenance plan

2) set the name of the maintenance plan: differential backup

3) Open the differential backup schedule, and drag the backup database task and cleanup maintenance task to the current scheduled task box.

4) set the backup type to difference in the backup database task, and set the backup type, backup specified database, backup storage folder

5) double-click to open the clear maintenance task and set the folder and file retention cycle to clear the maintenance task.

Transaction log backup

1) Select maintenance plan-create a new maintenance plan

2) set maintenance plan name: transaction log backup

3) Open the differential backup schedule, and drag the backup database task and cleanup maintenance task to the current scheduled task box.

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

5) double-click to open the clear maintenance task and set the folder and file retention cycle to clear the maintenance task.

Database recovery restore full backup

Restore full backup refers to restore the full backup files of the database and restore the database to the state when it is complete.

1) choose to restore the database

2) under the general options, set the restore target database name, source device (database complete file), and so on.

3) set the storage path of the restore database file, and choose to overwrite the existing database and roll back the uncommitted transaction.

Restore full backup + differential backup

This way is to restore the database to the state of differential backup. After you restore the full backup, you can continue to restore the differential backup to the target database, which is used to update the data saved by the differential backup into the current database and restore the database to the state it was at the time of the differential backup.

1) choose to restore the database

2) under the general options, set the restore target database name, source device (database complete file), and so on.

3) set the storage path of the restore database file, and choose to overwrite the existing database and not roll back the uncommitted transaction to the database.

4) after the completion of the previous step, the database is in a state of restore; you can continue to restore the database, and the source device selects differential backup files.

5) set the file storage path and the rollback mode of database transaction log when the differential backup file is restored.

Restore full backup + differential backup + transaction log backup

This is done by restoring the database to the state it was in 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, which is used to update the data saved by the differential backup and transaction log backup into the current database. Restore the database to the state it was in when the transaction log was backed up.

1) choose to restore the database

2) under the general options, set the restore target database name, source device (database complete file), and so on.

3) set the storage path of the restore database file, and choose to overwrite the existing database and not roll back the uncommitted transaction to the database.

4) after the completion of the previous step, the database is in a state of restore; you can continue to restore the database, and the source device selects differential backup files.

5) set the file storage path and the rollback mode of database transaction log when the differential backup file is restored.

6) after the differential backup restore is completed, the database is in the state of restore; continue to choose to restore and restore the transaction log files to the current database.

Choose to restore transaction logs, general selection settings

Restore the point-in-time setting; by setting this point in time, you can restore the database to any point in time from completion to transaction log backup.

Option to roll back uncommitted transactions

After the execution is complete, 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 is the state when the transaction log is backed up.

The above is all the contents of the article "what is the SQL SERVER data backup scheme?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.