Detailed tutorials on how to back up and restore MSSQL databases

Backup Database
1. Enterprise Manager
2. SQL statement (full backup): Backup database to disk= ' C: your backup filename '

Ii. Restoring the Database
1. Enterprise Manager
2. SQL statement: Restore database Database from disk= ' C: your backup filename '

The following is a detailed procedure:

Backup Database

1, open SQL Enterprise Manager, in the console root directory in order to open Microsoft SQL Server;

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)--> 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.

Ii. Restoring the Database

1, open SQL Enterprise Manager, in the console root directory in order to open Microsoft SQL Server;

2. SQL Server group--> double-click to open your server--> point icon Bar of the new database icon, the new database name to take;

3, click on the new database name (such as Forum Database Forum)--> then click on the menu above the tool--> choose to restore the database;

4, in the pop-up window in 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 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 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 be set according to your SQL installation (or you can 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 the D:program FilesMicrosoft SQL Servermssqldata, 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 are to do in this way related changes (log file name is *_ Log.ldf end of), here's the recovery directory you can set freely, provided that the directory must exist (such as you can specify D:sqldatabbs_data.mdf or d:sqldatabbs_log.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 turn open Microsoft SQL Server-->sql Server group--> Double-click to 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 have to do the selection--> 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 once, 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, such as: D:databak, and then choose to use this directory here, if your database is better choose to create subdirectories for each database, and then choose to delete earlier than how many days before the backup, 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--> Next maintenance Plan history, preferably with the default option--> the next step to complete;

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 starting the OS;

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