MSSQL Server2000 Database separation and Addition

If your database system is installed on a system disk (such as a drive C), you may want your data to be stored on a non-system disk (such as a drive d) because the drive C is vulnerable to viruses, you do not need to reinstall the database. You just need to "separate" the data, copy the relevant files to a directory on disk D, and then "Attach" the database.

SQL Server 2000 allows the separation of database data and transaction log files, and then attaches them to the same Server or another Server. The detached database will delete the database from SQL Server, but the database remains intact in the data and transaction log files that make up the database. The data and transaction log files can then be used to attach the database to any SQL Server instance, so that the Usage Status of the database is identical with that of the database when it is separated.

Database separation and appending are actually very simple. Even if you contact the database for the first time, it is very easy to do.

1. Database Separation

1. Click program Microsoft SQL Server Enterprise Management to open the Enterprise Manager.

2. Expand the server group, expand the server, and select the database to be detached.

3. Right-click "all tasks" to split the database. The following window is displayed.

4. Click OK to split the selected database.

Note that the database can be separated only when the number of connections using the database is 0. Therefore, when you detach a database, try to disconnect all connections to the database to be detached. If there are programs connected to the database, the database connection status window will appear, showing the machines and names that are connecting to the database, click the Clear button to force disconnect the existing connection from the server.

2. Additional database

1. Copy Mobile Database Files

Before attaching a database, you must associate. MDF (primary data file ). LDF (Transaction Log File) copies the two files to the target server, or to different file directories on the same server. These two files are generally located in the C: Program FilesMicrosoft SQL ServerMSSQLData directory.

2. Click program Microsoft SQL Server Enterprise Management to open the Enterprise Manager.

3. Expand the server group and then expand the server

4. Right-click "Database" and select "all tasks" Attach database. The window is displayed.

5. Enter the MDF name of the database to be appended. If you are not sure where the file is located, Click Browse ("...") to search. To ensure that the specified MDF file is correct, click Verify ". In the "APPEND as" box, enter the database name. The database name cannot be the same as any existing database name. Database owner

6. Click OK. The database node of the newly attached database is created in the "Database" folder.