Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to back up and restore MSSQL database", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "how to back up and restore MSSQL database" bar!

How to back up and restore MSSQL database (detailed tutorial for beginners)

I. back up the database

1. Enterprise Manager

2. SQL statement (full backup): backup database database to disk='c:\ your backup file name'

Restore the database

1. Enterprise Manager

2. SQL statement: restore database database from disk='c:\ your backup file name'

The following are the specific steps:

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 database directory

3. Select your database name (such as Forum)-- > then click tools in the menu above-- > Select backup database.

4, backup option Select full backup, backup to the destination if there is a path and name, select the name point to delete, then click add, if there is no path and name, then directly choose to add, and then specify the path and file name. specify the post-point to return to the backup window, and then click OK to back up.

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-- > the new database icon in the icon bar, and choose the name of the new database yourself.

3. Click the newly created database name (such as Forum Database Forum)-> and then click tools in the menu above-> Select restore Database.

4. In the restore options in the pop-up window, select from device-> Select device-- > add-- and then select your backup file name-- > confirm to return after adding. At this time, the device bar should appear the database backup file name you just selected, and the backup number defaults to 1 (if you have backed up the same file multiple times, you can click next to the backup number to view the contents. Select OK after the latest backup in the check box)-> then click the option button next to General above

5. In the window that appears, select the option to force restore on the existing database, and in the restore completion state, select the option that allows the database to continue to run but cannot restore other transaction logs. Restore the database file in the middle of the window 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, but the physical file name needs to be changed according to the machine you restored. If your SQL database is installed in D:\ Program Files\ Microsoft SQL Server\ MSSQL\ Data, then make the relevant changes according to the directory where you restored the machine. And the last file name had better be changed to your current database name (for example, the original bbs_data.mdf, now the database is forum, change it to forum_data.mdf), the log and data files should be changed in this way (the file name of the log ends with * _ log.ldf). You can set the recovery directory here freely. Only if the directory exists (if you can specify d:\ sqldata\ bbs_data.mdf or d:\ sqldata\ bbs_log.ldf), otherwise the restore will report an error

6. After the modification is completed, click OK below to restore, and a progress bar will appear to indicate the progress of the recovery. After the recovery is completed, the system will automatically prompt for success. If an error is reported in the middle, please record the relevant errors and ask people who are familiar with the SQL operation. The general error is nothing more than a directory error or a duplicate file name or an error in the file name or insufficient space or an error in the database that is in use. You can try to close all SQL windows and reopen them for recovery. If you are also prompted by the error in use, you can stop the SQL service and restart it. As for the other errors mentioned above, they can generally be recovered after making corresponding changes according to the contents of the error.

Third, set daily automatic backup of the database

1. Open Enterprise Manager, click Microsoft SQL Server-- > SQL Server Group in the root directory of the console, then double-click to open your server.

2. Then click tools in the menu above-> Select Database maintenance Planner

3. Next, select the data to be backed up automatically-- > update the data optimization information in the next step. Generally, you do not need to make a choice here-- > check the data integrity in the next step, and generally do not select.

4. Specify the database maintenance plan in the next step. The default is to back up once a week. Click change and select the point after backup every day.

5. Next, specify the disk directory for backup, and select the specified directory. For example, you can create a new directory on D disk, such as: d:\ databak, and then choose to use this directory here. If you have a large number of databases, you'd better choose to establish a subdirectory for each database, and then choose to delete backups that are earlier than how many days ago. Generally, set up 4mur7 days, depending on your specific backup requirements. The backup file extension is usually bak and uses the default

6. Specify the transaction log backup plan in the next step, depending on your need to make a choice-- > the report to be generated in the next step is generally not selected-- > the next step to maintain the plan history, it is best to use the default option-- > complete the next step.

7. After completion, the system is likely to prompt that the Sql Server Agent service has not been started, click to confirm the completion plan setting, then find the SQL green icon in the rightmost status bar of the desktop, double-click to open, select Sql Server Agent in the service, and then click the run arrow to start the service automatically when you start OS.

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

Thank you for your reading, the above is the content of "how to back up and restore MSSQL database". After the study of this article, I believe you have a deeper understanding of how to back up and restore MSSQL database, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.