Enterprise Manager backup and restore SQL Server database _mssql

In daily database operations, we often backup operations on the database to facilitate the database in the event of a disaster in the database to restore, thereby ensuring the security of the database. SQL Server databases use Enterprise Manager to do these things simple and intuitive, and to automatically back up the database on a daily basis. This article we have introduced this process, next let us come to understand together.

One, BACKUP database

1. Open SQL Enterprise Manager, and click 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 option Select Full Backup, the purpose of the backup to if there is a path and name then select the name Point Delete, then click Add, if there is no path and name then directly select Add, then specify the path and file name, specify the backend to return to the Backup window, and then confirm the backup.

Second, restore the database

1. Open SQL Enterprise Manager, and click 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--> then click the Tools in the above menu--> select Restore Database.

4, in the pop-up window of the Restore option select 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, The 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 checkbox)--> 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, 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 (if it turns out to be zw0001.mdf, now the database is zw0002, changed to Zw0002.mdf), log and data files to do in this way related changes (log file name is. ldf end), here the recovery directory you can set, the premise Is that the directory must exist (such as you can specify D:\sqldata\zw0002.mdf or d:\sqldata\zw0002.ldf), otherwise the recovery will be an 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 changes can be restored.

Third, set up daily automatic backup database

1, open Enterprise Manager, in the console root directory in order to open the Microsoft SQL Server-->sql Server group--> double hit Open your server.

2, then click the Tool--> in the menu above to select the Database maintenance Planner.

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

4, the next step to specify the database maintenance plan, the default is 1 weeks backup, click on the change to select a daily backup point to determine.

5, next specify the backup disk directory, select the specified directory, such as you can create a new directory in D disk such as: D:\databak, and then choose to use this directory here, if your database is more than best choose to create subdirectories for each database, and then choose to delete the backup earlier than how many days ago, Generally set 4-7 days, this depends on your specific backup requirements, backup file extensions are generally bak with the default.

6, next specify the transaction log backup plan, see your need to make a choice--> next to generate the report, generally do not choose--> the next step is to maintain the plan history, preferably with the default option--> next step.

7, after the completion of the system is likely to prompt the SQL Server Agent service is not started, the first point to determine the completion plan, and then find the desktop rightmost status bar in the SQL Green icon, double-click Point Open, select SQL Server Agent in the service, and then click the run arrow, Select the below to start the service automatically when you start the OS.

8, this time the database plan has been successfully run, it will be based on your previous settings for automatic backup.

About the SQL Server database backup and restore knowledge about this, I hope to help you learn!