SQL Server backup and Restoration

1. Three backup modes

Full backup, differential backup, and log backup

2. database recovery model

Full recovery, large-scale Log Recovery, simple mode

Switch Recovery Model: Right-click the database name, select Properties, select options, and click fault recovery.

3. Automatic Database Backup

(1) Expand server instance -- Management -- database maintenance plan -- New

(2) Expand server instance -- target database -- Right-click "all tasks" -- back up database -- "general" tab "scheduling" check box -- Set

(3) Job: No matter which method the automatic backup project is created, the time scheduling is completed through the job, so you can directly create a job.

 

Right-click "Maintenance Plan"> "create maintenance plan"> "enter the name of the maintenance plan". The window for creating a maintenance plan appears.

Click "..." on the right of "plan" to add the execution frequency of the plan.

Then, in the left Toolbox (if not, press CTRL + ALT + x), drag the "backup database task" to the yellow area in the middle.
Double-click the task to be dragged out. The design attribute window is displayed.
In the database, select the database you want to back up, set the backup to there, and set the backup file name
After the design, click "OK", you can also click "T-SQL" to see what SQL statements will be used to deal with your needs.

4. Backup and restoration of the system database

The system database does not store business data, but stores many SQL server settings, such as database maintenance plans and database security users.

MASTER: records system-level information, logon accounts, and system configuration settings, including:

• Create and delete a user database
• Operations related to logon security and database security operations. For example, adding a user to a database does not affect the master.
• Server Range Configuration
• Add or delete a logical device
• Configure the server as a distributed query and remote process call, such as adding a linked server or logging on remotely.

Model: database template, which must be backed up after modification (for example, adding user-defined types)

MSDB: used by SQL Server Agents to schedule alarms and jobs, and to record backups after MSDB data changes due to database maintenance plans, backup and restoration operations, new modification jobs, configuration release and distribution, and other operations.

Distribution: the server configured as a remote distributor or a combination of publishing/distribution servers has a distribution database, which can be configured or adjusted for post-release and distribution backups.

5. System database recovery

Master Reconstruction Procedure

(1)run rebuildm.exe and create a new master (this step is not required if the master is not damaged). You need to install the program.

(2)single-user mode to start SQL ,sqlserver.exe-C-m

(3) restore a database like a normal Database

The restoration of model, MSDB, and distribution databases is similar to that of common databases.

After the master node is re-built, the model and MSDB are also re-built, so they should be restored immediately, but the re-built master node has no effect on distribution.