SQL Server uses detach and attach to move database locations

The cloud concepts such as database backup and permissions will not be introduced. You can directly access the topic for operations.

Environment:

1. Server SQL Server 2005

2. Client tool: SQL Server Management Studio

3. The name of the database to be moved is oscardb.

Procedure

1. View and record the name and location of the data file and log file of the current database.

There are two methods:

• Directly operate on the interface, select the database to be moved, right-click and select "attribute", and you will be able to see it on the "file" selection page.
• In SQL Server Management studio, click "create query" to go to SQL query. Enter

use oscardbgo sp_helpfilego

2. Access the database exclusively to prevent other users from using

use mastergoalter database oscardbset single_usergo

3. Database Separation

There are also two ways:

Interface operation, right-click Database-> task --> detach

SQL statement

use mastergo sp_detach_db 'oscardb'go

4. Move data and log files to a new location (my side is D: \ sqlserverdata)

5. Database of the specified file in the attachment

Right-click the database and choose attach.

The SQL statement is:

use mastergo sp_attach_db 'xuemingdb','D:\sqlserverdata\oscardb.mdf','D:\sqlserverdata\oscardb_log.ldf'go