SQL Server uses detach and attach to move database locations

Source: Internet
Author: User
Tags management studio sql server management sql server management studio

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.