MSSQL database backup and Restoration Methods graphic tutorial

A summary of several methods of MSSQL database backup and restoration is provided with images and texts.

A summary of several methods of MSSQL database backup and restoration is provided with images and texts.

1. Back up a single database through the Enterprise Manager. Open the SQL server Enterprise Manager, expand the database under the SQL SERVER group LOCAL, right-click the database you want to back up, and select the backup database under all tasks in the pop-up menu, the backup database dialog box is displayed:

Click Add, enter the path and file name of the backup file, click OK to add the backup file, and click Backup in the backup dialog box to start backup.

2. Use the Enterprise Manager to back up multiple data at the specified time. Open the SQL server Enterprise Manager, expand the management nodes in the SQL SERVER group, right-click the database maintenance plan, and select create maintenance plan. In the pop-up welcome dialog box, select next and the following dialog box is displayed:

Select the database you want to back up and next step. In the next step, all options are selected by default. In the current dialog box, you can choose whether to check the integrity of the database. By default, you can proceed to the next step. The following figure shows the dialog box:

Select the backup database as part of the maintenance plan, click the change button to modify the backup database time, click Next, and enter:

Here, we can select "use this directory" as the backup path, and select "delete files earlier than this time" to specify the backup file retention time.

In the following steps, we select the default settings. At last, we will name the backup in the completion dialog box. After clicking finish, the database will be backed up at the specified time. For more information about the database maintenance plan, see other relevant materials.


3. Back up database files. Open SQL server Enterprise Manager, expand the database under the SQL SERVER group LOCAL, right-click the database you want to back up, and select Properties. In the displayed dialog box, select a data file. The path of the data file is displayed, for example:


Open the SQL server service manager and stop SQL SERVER. Open my computer, find the data file path, and copy it for backup (note that the. LDF file is backed up at the same time ).

Finally, enable the SQL SERVER service

4. Reply to the database backup file (applicable to databases backed up by the Enterprise Manager ). Open the SQL server Enterprise Manager, expand the database under the SQL SERVER group LOCAL, right-click the database you want to restore, and Select Restore database under all tasks. The following dialog box is displayed:

Select a device, click Select device, add the backup file path for restoration, and then confirm the restoration.

5. Restore the database backup file. In Enterprise Manager, right-click the database node and select an additional database under all tasks. The attach database dialog box is displayed. For example:

Select a data file you copied before, and click "OK". (Note: if the database already exists, delete the database and append it ).