SQL Server detach and attach

1, what is the database separation and additional

Database separation:

Detaching a database is removing a database (such as Student_mis) from the list of SQL Server databases so that it is no longer managed and used by SQL Server, but the database file (. MDF) and the corresponding log file (. LDF) are intact. Once the separation is successful, we can put the database file (. MDF) and the corresponding log file (. LDF) Copy to the other disk as a backup save.

Database Attach:

Detaching a database is removing a database (such as Student_mis) from the list of SQL Server databases so that it is no longer managed and used by SQL Server, but the database file (. MDF) and the corresponding log file (. LDF) are intact. Once the separation is successful, we can put the database file (. MDF) and the corresponding log file (. LDF) Copy to the other disk as a backup save.

2. Additional application Scenarios for database separation

A), to move the database by separating and attaching, or to change the database to a different instance of SQL Server on the same computer;
b), upgrade the database by separating and attaching, such as when upgrading the database from SQL Server 2005 to SQL Server 2012;

3. Detach the attached database with T-SQL statements

--Detach Database Xxxdb to the name of the database to be detached Use [Master]GOALTER DATABASE [Xxxdb] SETSingle_user with ROLLBACKIMMEDIATEGO Use [Master]GOEXECmaster.dbo.sp_detach_db@dbname =N'Xxxdb',@skipchecks = 'false'GO--attach the database, where E:\xxx\xxxDB.mdf is the database data file path and E:\XXX\XXXDB_LOG.LDF as the database log file path--where xxxxxxdb can change the name (appended database name) Use [Master]GOCREATE DATABASE [Xxxxxxdb]  on(FILENAME=N'E:\xxx\xxxDB.mdf'), (FILENAME=N'E:\xxx\xxxDB_log.ldf' )  forATTACHGO

Where the detach database process may involve a local explanation (when using the SSMs graphical tool):

Delete Connection: Disconnects the connection to the specified database because the database that is connected to the active state cannot be detached.
UPDATE STATISTICS: By default, the detach operation retains outdated optimization statistics when the database is detached; To update existing optimization statistics, click this check box.
Keep full-Text catalogs: By default, the detach operation retains all full-text catalogs associated with the database. To remove the full-text catalog, clear the Keep full-text catalog check box. This option is displayed only if you upgrade the database from SQL Server 2005

SQL Server detach and attach