SQL Server database backup and replication (3): Several Methods from backup to transfer

I. Back up the database
　　
1. Open the SQL enterprise controller 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 (for example, financial database cwdata) --> click tools in the menu above --> select backup database

4. Select full backup as the backup option. If there is a path or name for the backup in the target project, click Delete and then add. If there is no path or name, select Add directly, specify the path and file name, click OK to return to the backup window, and click OK to back up.

2. Restore the database

1. Open the SQL enterprise controller and click Microsoft SQL Server in the root directory of the console;

2. SQL Server group --> double-click to open your Server --> click the new database icon in the icon bar to retrieve the name of the new database;

3. Click the name of the newly created database (such as the financial database cwdata) --> click the tool in the menu above --> select recover database;

4. In the displayed window, select "restore from device"> "select device"> "add"> "select your backup file name"> "add" and click "OK" to return, at this time, the device column should display the database backup file name you just selected. The default backup number is 1 (if you have backed up the same file multiple times, you can click View content next to the backup number, select the latest backup in the check box and click OK) --> then click the option button next to the general button above;

5. In the displayed window, select force restore on the existing database, and select the option to enable the database to continue running but not to restore other transaction logs in the recovery completion status. To restore the database file in the middle of the window, you need 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, the physical file name should be changed based on the recovered machine. If your SQL database is installed in D: Program FilesMicrosoft SQL ServerMSSQLData, you can change the directory of the recovered machine, and the final file name should be changed to your current database name (for example, originally cw123_data.mdf, and now the database is cwdata, then cwdata_data.mdf ), log and data files must be modified in this way (the log file name is * _ log. ldf ends). You can set the recovery directory as required (if you can specify d: sqldatacwdata_data.mdf or d: sqldatacwdata_log.ldf). Otherwise, an error is returned;

6. After the modification is complete, click "OK" below to restore. a progress bar is displayed, prompting the recovery progress. After the restoration is complete, the system will automatically prompt "success". If an error is reported in the middle, please record the relevant error content and ask people familiar with SQL operations. The common error is nothing more than a directory error, repeated file names, incorrect file names, insufficient space, or database in use errors, if the database is in use, you can close all the SQL windows and re-open them to restore the SQL window. If you still receive an error, you can stop the SQL Service and start again, as for the above other errors, you can change the content of the errors to restore them.

Iii. Database shrinking

In general, the contraction of the SQL database does not greatly reduce the size of the database. Its main function is to shrink the log size. This operation should be performed on a regular basis to avoid excessive database logs.

1. Set database mode to simple mode: Enable the SQL enterprise controller, in the root directory of the console, open Microsoft SQL Server --> SQL Server group --> double-click to open your Server --> double-click to open the database directory --> select your database name (such as financial database cwdata) --> then right-click and select Properties --> Select Options --> select "simple" in the fault recovery mode, and click OK to save the settings;

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

3. After the database is shrunk, we recommend that you set your database attributes to the standard mode. The operation is the same as the first one, because logs are often an important basis for database recovery in case of exceptions.

4. Data Transfer (new database or transfer server)

Under normal circumstances, it is best to use backup and restoration operations to transfer data. Under special circumstances, you can use the Import and Export Method for transfer. Here we will introduce the Import and Export method, one function of data import/export is to reduce or contract the database size when the database contraction is invalid.

1. Export all tables and stored procedures of the original database into an SQL file, during export, you should select the options to write the index script and write the primary key, foreign key, default value, and check the constraints script;

2. Create a database and execute the SQL File Created in step 1 for the new database;

3. Use SQL to import and export all the table content of the original database to the new database.

V. Another method for restoring SQL database backup

1. COPY the two files * _ SQL _Data.MDF and * _ SQL _Log.LDF under the original C: Program FilesMicrosoft SQL ServerMSSQLData directory to the installation directory of SQL Server 2000 at the same time, for example: c: Program FilesMicrosoft SQL ServerMSSQLData;

2. Open "Enterprise controller"-> "machine name"-> "Database", right-click "all tasks"-> "Attach Database ", then select the COPY file * _ SQL _Data.MDF and click attach!

3. Complete other settings, such as logon identity and password.

This article from: Wuhu webmaster station (http://www.whzz.org/) original link: http://www.whzz.org/database/SQLserver/20071115/33765_2.html