SQL Server Backup and recovery measures

One, BACKUP database

1. Open SQL Enterprise Manager, click to open Microsoft SQL Server in the console root directory

2, SQL Server group--> double hit Open your server--> double-click to open the database directory

3, select your database name (such as Forum Database Forum)--> then click the Tools in the above menu--> Select Backup Database

4, Backup options Select Full Backup, the purpose of the backup to the original if there is a path and name then select the name Point Delete, then click Add, if there is no path and name is directly selected to add, and then specify the path and filename, specify the back-point of the Backup window, and then click OK

Second, restore the database

1. Open SQL Enterprise Manager, click to open Microsoft SQL Server in the console root directory

2, SQL Server group--> double hit Open your server--> point icon Bar of the new database icon, the name of the new database to take

3, click on the new database name (such as Forum Database Forum)--> then click the Tools in the above menu--> select Restore Database

4, in the pop-up window of the Restore option to choose from the device--> point Select Device--> Point add--> and then select your backup file name--> Add after the point of return, this time the device bar should appear you just select the database backup file name, backup number defaults to 1 ( If you have made multiple backups of the same file, you can click on the view next to the backup number, select the latest backup point in the check box--> then click the option button next to the General section above.

5. Select the Force restore on the existing database in the window that appears, and select the option to keep the database running but not to restore other transaction logs in the recovery completion state. In the middle of the window to restore the database file to here to follow the installation of your SQL settings (you can also specify your own directory), the logical file name does not need to change, to move to the physical file name to change according to the machine condition you restored, such as your SQL database installed in D:\Program files\ Microsoft SQL Server\MSSQL\Data, then follow the changes that you have made to the directory where you restored the machine, and the last file name is best changed to your current database name (such as the original Bbs_ Data.mdf, now the database is forum, changed to Forum_data.mdf), log and data files to do in this way related changes (the log file name is *_log.ldf end), where the recovery directory you can set up, provided that the directory must exist (If you can specify D:\sqldata\bbs_data.mdf or d:\sqldata\bbs_log.ldf), the recovery will error

6, after the completion of the modification, click on the determination of the following recovery, there will be a progress bar, prompt recovery progress, the system will automatically prompt success after recovery, such as the middle of the prompt error, please record the relevant errors and ask about the SQL operation more familiar with the personnel, The general error is simply a directory error or file name duplication or file name error or lack of space or database is in use error, database is using the error you can try to close all about the SQL window and then reopen for recovery operations, If you also hint that the error you are using can stop the SQL service and then restart it, as the above other errors can generally be changed according to the wrong content to restore

Third, shrinking the database

In general, the contraction of the SQL database does not greatly reduce the size of the database, its primary role is to shrink the log size, you should do this periodically to avoid the database log too large

1, set the database mode to Simple mode: Open SQL Enterprise Manager, in the console root in turn, click the Microsoft SQL Server-->sql Server group--> double hit Open your server--> double-click to open the database directory--> Select your database name (such as Forum Database Forum)--> and then right-click to select Properties--> Select option--> in the failover mode select "Simple" and then press OK to save

2, in the current database point right, look at all tasks in the shrinking database, the general inside the default settings do not need to adjust, direct point to determine

3. After the database is finished, it is recommended that you reset your database properties to standard mode, with the 1th, because the log is often an important basis for restoring the database in some unusual cases.