How to use SQL statements to separate and attach Databases

You can use Manage Studio to view the interface operations on your own.
Separate database:
If the DATABASE is separated by stored procedures and the user link cannot be terminated, you can use the alter database command and set the DATABASE to SINGLE_USER mode by using a termination option that can interrupt the existing link, the code for setting it to SIGLE_USER is as follows:
Alter database [DatabaseName] SET SINGLE_USER with rollback immediate the CMD command for DATABASE Separation
EXEC sp_detach_db DatabaseName once a database is successfully separated, it is no different from deleting the database from the perspective of SQL Server.
Additional database:
FOR additional databases, you can use the sp_attach_db stored procedure or the create database Command with the for attach option. In SQL Server2005 or a later version, we recommend that you use the latter FOR forward compatibility, it is gradually eliminated, while the latter provides more control over files.
Create database databasename
ON (FILENAME = 'd: \ Database \ dbname. mdf ')
For attach | FOR ATTACH_REBUILD_LOG
However, we should pay attention to a few additional points. Because it involves rebuilding logs.
1. If a read/write database contains an available log file, no matter whether it is for attach or FOR ATTACH_REBULD_LOG, the log file is the same, and the database will not be rebuilt. If the log file is unavailable or the log file does not exist physically, use for attach or FOR ATTACH_REBULID_LOG to recreate the log file, therefore, if we copy a database with a large number of log files to another server, we can only copy it. mdf file, you do not need to copy the log file, and then use the FOR ATTACH_REBULD_LOG option to recreate the log. The condition is that this server will mainly use or only use the copy of the database to perform read operations.
2. for a read-only database, it is a little different. If the log file is not available, the master file cannot be updated, so the log cannot be rebuilt. Therefore, when we attach a read-only database, you must specify the log file in the for attach clause.
If the log file is rebuilt using the attached database. Using FOR ATTACH_REBUILD_LOG will interrupt the log backup chain. It is best to perform a full database backup before performing this operation.
One advantage of using sp_detach_db stored procedure is that a database is cleanly closed, so the log file is not necessary FOR appending the database. We can use the FOR ATTACH_REBUILD_LOG command to recreate the log, obtain the smallest log file. It is also a method for quickly shrinking a large log file.
Author: xingbaifang
Web: Http: // xbf321.cnblogs.com