How to back up and restore the MSSQL database)

How to back up and restore the MSSQL database)

I. Back up the database
1. Enterprise Manager
2. SQL statement (full backup): backup database to disk = 'C: \ your backup Filename'

2. Restore the database
1. Enterprise Manager
2. SQL statement: restore database from disk = 'C: \ your Backup Filename'

The procedure is as follows:

I. Back up the database

1. Open SQL Enterprise Manager and click Microsoft SQL Server in the root directory of the console;

2. SQL Server group --> double-click to open your Server --> double-click to open the database directory;

3. Select Your Database Name (such as Forum database Forum) --> click tools in the menu above --> select backup database;

4. Select full backup as the backup option. If there is a path or name for the backup in the target project, click Delete and then add. If there is no path or name, select Add directly, specify the path and file name, click OK to return to the backup window, and click OK to back up.

2. Restore the database

1. Open SQL Enterprise Manager and click Microsoft SQL Server in the root directory of the console;

2. SQL Server group --> double-click to open your Server --> click the new database icon in the icon bar to retrieve the name of the new database;

3. Click the new database name (such as the Forum database Forum) --> then click the tool in the menu above --> select recover database;

4. In the displayed window, select "restore from device"> "device"> "add"> "backup file name"> "add", and click "OK" to return, at this time, the device column should display the database backup file name you just selected. The default backup number is 1. (If you have backed up the same file multiple times, click View next to the backup number, select the latest backup in the check box and click OK) --> then click the option button next to the general button above;

5. In the displayed window, select force restore on the existing database, and select the option to enable the database to continue running but not to restore other transaction logs in the recovery completion status. To restore the database file in the middle of the window, you need to set it according to your SQL installation (you can also specify your own directory). The logical file name does not need to be changed, the file name to be moved to the physical file must be changed based on the machine you have recovered. For example, if your SQL database is installed in D: \ Program Files \ Microsoft SQL Server \ MSSQL \ Data, then modify the directory of the recovered machine according to the change, and the final file name should be changed to your current database name (for example, if it was originally bbs_data.mdf, the current database is forum, change it to forum_data.mdf). The log and data files must be modified in this way (the log file name is * _ log. ldf). You can set the recovery directory as needed, provided that the directory must exist (for example, you can specify d: \ sqldata \ bbs_data.mdf or d: \ sqldata \ bbs_log.ldf ), otherwise, an error is returned;

6. After the modification is complete, click "OK" below to restore. a progress bar is displayed, prompting the recovery progress. After the restoration is complete, the system will automatically prompt "success". If an error is reported in the middle, please record the relevant error content and ask people familiar with SQL operations. The common error is nothing more than a directory error, repeated file names, incorrect file names, insufficient space, or database in use errors, you can close all SQL windows and re-open them to restore the database. If an error is prompted, stop the SQL Service and restart it, as for the above other errors, you can change the content of the errors to restore them.

3. Set daily automatic Database Backup

1. Open Enterprise Manager, open Microsoft SQL Server> SQL Server group in the root directory of the console, and double-click to open your Server;

2. Click Tools in the menu above --> select the Database Maintenance Scheduler;

3. Next, select the data to be automatically backed up --> next, update the data optimization information. You do not need to select here --> next, check data integrity, or choose not;

4. Next, specify the database maintenance plan. The default one-week backup is performed. Click "change" and select "Back up every day". Click "OK;

5. Next, specify the backup disk directory and select the specified directory. For example, you can create a directory on disk d, such as d: \ databak, and then select to use this directory here, if you have a large number of databases, it is best to create a subdirectory for each database, and then select the number of days before the deletion of the backup, generally set to 4-7 days, depending on your specific backup requirements, generally, the backup file extension is the default value for bak;

6. Specify the transaction log backup plan in the next step. Check whether you need to select the report to be generated in the next step. Generally, do not select the report, it is best to use the default option --> next step;

7. After completion, the system will probably prompt that the SQL Server Agent service is not started. First, click confirm to complete the plan settings, find the SQL green icon in the rightmost status bar of the desktop, and double-click it, select SQL Server Agent from the service list, and click the run arrow to automatically start the service when the OS is started;

8. The database plan has been successfully run at this time, and it will be automatically backed up according to the above settings.

Author: "www. qq. com6500000000ol .."