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

This article mainly explains "how to achieve ms SQL server database backup, compression and SQL database data processing", 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 achieve ms SQL server database backup, compression and SQL database data processing" bar!

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)-> then click tools in the menu above-> Select backup database.

4. Backup option: select full backup. Select the name point to delete if there is a path and name in the destination, and then click add. If there is no path and name, select add directly, 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. Select from Devices-> Click Select Devices-> Click add-> then select your backup file name-> click OK to return in the pop-up window. At this time, the database backup file name you just selected should appear in the device bar. The backup number defaults to 1 (if you have backed up the same file several times, you can click the backup number 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 be set 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 of your recovery 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, shrink the database

In general, the shrinking of the SQL database can not greatly reduce the size of the database. Its main function is to reduce the size of the log. This operation should be done regularly to prevent the database log from being too large.

1. Set the database mode to simple mode: open SQL Enterprise Manager and click Microsoft SQL Server-- > SQL Server group in the console root directory-- > double-click to open your server-- > double-click to open the database directory-- > Select your database name (such as forum database Forum)-- > then right-click to select Properties-- > Select options-- > Select "simple" in the failure recovery mode. Then press OK to save

2. Right-click on the current database to see the shrinking database in all tasks. Generally, the default settings do not need to be adjusted. Click OK directly.

3. After shrinking the database, it is recommended to reset your database properties to standard mode. The operation method is the same as the first point, because logs are often an important basis for restoring the database in some abnormal cases.

Fourth, set daily automatic backup database strongly recommended for conditional users to do this operation!

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

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 back up automatically according to your above settings.

Modify the plan:

1. Open Enterprise Manager, click Microsoft SQL Server-- > SQL Server Group-> double click to open your server-> manage-> Database maintenance Plan-> Open the plan you set in the root directory of the console, and you can modify or delete it.

5. Data transfer (new database or transfer server)

In general, it is best to use backup and restore operations to transfer data. In special cases, you can transfer data by means of import and export. Here, import and export is introduced. One of the functions of transferring data by import and export is that it can be used to reduce (shrink) the size of the database when the database is invalid. This operation defaults to that you have some knowledge of SQL operation. If you do not understand some of these operations, you can consult the relevant personnel of Mobile Network or inquire about online materials.

1. Export all tables and stored procedures of the original database into a SQL file. When exporting, pay attention to the options of writing index script and writing primary key, foreign key, default value and check constraint script.

2. Create a new database and execute the SQL file created in the first step for the new database.

3. Import all the table contents in the original database for the new database by using the import and export method of SQL.

Add a method for backup and recovery of SQL database:

1. COPY the two files * _ sql_Data.MDF and * _ sql_Log.LDF from the original backup C:\ Program Files\ Microsoft SQL Server\ MSSQL\ Data directory to your SQL Server 2000 installation directory at the same time, such as C:\ Program Files\ Microsoft SQL Server\ MSSQL\ Data

2. Open "Enterprise Manager"-> "Machine name"-> "Database", right-click "all tasks"-> "additional Database"

Then select the file * _ sql_Data.MDF of COPY just now, click attach, successful!

3. Make other settings, such as login identity, password and so on.

Thank you for your reading, the above is the content of "how to achieve ms SQL server database backup, compression and SQL database data processing". After the study of this article, I believe you have a deeper understanding of how to achieve ms SQL server database backup, compression and SQL database data processing, 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.