SQL Server: All about detach and attach operations and common scenarios where they may or may not work
Introduction
There are different ways of moving a SQL Server database and ‘detaching and reattaching’ remains one of the feasible ways of doing so. Detaching or disengaging something, by definition, means separating or removing it from something that it is connected to. In SQL Server terminology it refers to separating a database from the SQL instance that it is connected to.
In this article, we will see how 'detach' and 'attach' operations work and how to migrate a database using this method, gotchas to keep in mind when adopting this method, conditions which might fail this procedure and how the log is rebuilt automatically and how this new log affects the log backup chain of a database.
Detaching and re-attaching a database
Simply put, when a SQL database is detached, all the metadata pertaining to the detached database is removed from the SQL instance. The only trace one might be left with is the backup and restore history for that database unless that has already been purged from the MSDB system database. Even in the event, one is able to retrieve the backup and restore history for it, that information alone would not be sufficient to derive the information about the database contents or structure.
One of the requirements for using this procedure is that there should be no active connections to the database or it will fail. The database being detached can be taken into SINGLE_USER mode WITH ROLLBACK IMMEDIATE to break the currently connected sessions and roll them back immediately.
Detaching a database using sp_detach_db
The SP_DETACH_DB stored procedure is used to cleanly detach a database and the command syntax is given below:
EXEC SP_DETACH_DB <DatabaseName>
One of the benefits of using this method is, SP_DETACH_DB ensures that all dirty data pages are flushed to disk and that there are no uncommitted transactions in the database. The reason is, it needs the database cleanly shut down or it won’t execute. By not having incomplete transactions in the database, there is no need for the transaction log file to be present in order to re-attach the database because the data files would not have any uncommitted transactions to be rolled back upon re-attaching.
We will see later in this article as to how a database without a transaction log file can be attached using this method. If the log is missing, SQL Server automatically re-builds a transaction log file with the minimum size of 1 MB. Remember, the automatic rebuilding of transaction log file is possible only when the database is cleanly shut down which is exactly what happens when we run SP_DETACH_DB. As soon as the database is detached, it won’t appear in the SYS.DATABASES system view.
One important thing to note is that the database does not have to be “detached” first (using SP_DETACH_DB) for the “attach” and for the automatic rebuild of the transaction log to work. If the database was cleanly shut down or if there was no activity against the database when the SQL instance gracefully turned off, it should be able to attach and rebuild the transaction log file automatically, in case it goes missing. That is, the attach post clean SQL restart works exactly as though the database was detached using sp_detach_db.
On the other hand, if there were ongoing transactions in the database when the SQL instance shut down, then the transaction log file MUST be present for the re-attach to work because SQL Server performs recovery against the database when it is attached. Hence, the presence of a transaction log file is a must when the database was not cleanly shut down.
Attaching a detached database
There are two ways to attach a database that is detached. One is to use “FOR ATTACH” option in the “CREATE DATABASE” statement and the second way is to execute “sp_attach_db” stored procedure.
1) FOR ATTACH
This is the supported and the most efficient way to attach a database. It gives the user the control over data and log file paths and can specify 32,767 files and 32,767 filegroups for each database. The syntax for using this method is given below:
CREATE DATABASE <DBName>
ON <FilesPath> [, n]
FOR {ATTACH
| ATTACH_REBUILD_LOG}
It is to be noted that the primary data file contains information about the locations of all other files and as such, must be specified in the**<FilesPath>**entry. SQL Server would then automatically know where the other files are located. If the plan is to attach the database to different paths, then provide the new paths in the <FilesPath> entry above otherwise, if unspecified, SQL Server assumes the files to be in last known location.
All data files MUST be available for the attach operation to succeed. Presence of log file is contingent upon whether the database was cleanly shutdown using sp_detach_db or not. As mentioned above, if the database was indeed cleanly shut down using sp_detach_db or the SQL instance was cleanly shutdown with no dirty pages in memory, then the log file is not required for the attach operation to work. SQL Server, in that case, rebuilds the missing log file when we execute the CREATE DATABASE with FOR ATTACH operation.
One key thing to note here is, FOR ATTACH rebuilds only single log file i.e. if the database being attached had an only one log file that is missing, then FOR ATTACH rebuilds it and updates the information about the new log file in the primary data file. Whereas, if the database being attached had multiple log files and all are missing, FOR ATTACH fails to rebuild the log file. That is where the other option “ATTACH_REBUILD_LOG” comes into play.
ATTACH_REBUILD_LOG doesn’t have the limitation of rebuilding only single log file. If a database has multiple log files and if all go missing, this option rebuilds the log. If, on the other hand, log files do exist, and we still specify the ATTACH_REBUILD_LOG option in the CREATE DATABASE statement, SQL Server uses the log files and does not rebuild them. Hence, ATTACH_REBUILD_LOG option is ignored.
It must be noted that if the transaction log is rebuilt upon using the ATTACH_REBUILD_LOG option to attach a database, the log backup chain is broken, and a full backup of the database must be immediately taken after the database is attached. This is demonstrated in a later example.
2) SP_ATTACH_DB Stored Procedure
Just like SP_DETACH_DB, this stored procedure is used to attach a database. It must be noted that this stored procedure has been deprecated and is not recommended due to its limitations. One of its limitations is, it is limited to only sixteen files. As mentioned above, FOR ATTACH supports as many as 32,767 files.
**Note: **This stored procedure was deprecated in SQL 2005 and is not supported. So even if it works on whatever version we are on, we should always consider using the FOR ATTACH method discussed above.
Theofficial documentationsays thatsp_attach_dbstored procedure can only be used on the databases that were previously detached from the database server by using an explicit sp_detach_db. This isnotalways necessary. As mentioned above, as long as the database was cleanly shutdown and there were no dirty pages in the buffer pool at the time the SQL instance was taken down, the database is always attachable, and it is as if the database was detached using sp_detach_db.
On the other hand, if the SQL instance was processing a transaction when it went off, in this case, the log file(s) must be present to recover the transaction upon attaching. The log file keeps track of the changes and recovery is performed when the database is attached to ensure consistency.
alter database...modify file
Although “FOR ATTACH” is one of the ways to migrate a database to another storage but the most opted and the most preferred approach is to use “ALTER DATABASE...MODIFY FILE”.
One of the advantages of using this method is that if we have a database participating in Log Shipping, the log backup chain is not broken if the files need to be moved to different storage. The next log backup on the new storage follows the log sequence and backs up all changes from the last log backup which was taken when the database was on the old storage and hence, the log shipping is intact. In contrast, if the database is migrated using detach and attached with log file rebuilt, log backup chain is broken and log shipping needs to be reinitialized with a fresh full backup.
This procedure is really a four-step process. An example is given later in the article.
- Run the ALTER DATABASE…MODIFY FILE statement by providing the new data and/or log file paths. As soon as the statement executes, the paths are modified in the internal system tables, but the new paths take effect either when the database is taken offline followed by online or after the SQL instance is rebooted.
- Take the database being migrated offline by running the following statement.
- ALTER DATABASE <DatabaseName> SET OFFLINE (with ROLLBACK IMMEDIATE, if we want to break the existing connections immediately)
- **Note: **The steps 1 and 2 can be executed in any order.
- Manually copy the database files to the new locations. Make absolutely sure that these are the paths that were given in the ALTER DATABASE MODIFY FILE command.
- Turn the database back online by executing the following statement.
ALTER DATABASE <DatabaseName> SET ONLINE
Optionally, run the following to ensure that new paths are effective.
USE masterSELECTname, physical_name
FROMsys.master_files
WHEREdatabase_id = DB_ID("<DatabaseName>");
Common scenarios where attach may or may not work
Now that we know the underlying concept of detaching and attaching a database, let us see some examples and understand how the mechanism works. The following are several scenarios that we, as DBAs, commonly face in our work environments. Some allow the attach operation, some allow it by rebuilding the t-log file automatically and some do not allow it due to reasons we will see in the corresponding example.
Usual Detach and Attach to Migrate a Database
The below example creates a new database named “SAMPLE” and detaches it using “SP_DETACH_DB” stored procedure and then re-attaches it, using “CREATE DATABASE...FOR ATTACH”. All database files are intact and hence, the re-attaching succeeds without any issues.
If we notice, the first attempt to detach the database fails due to open connections to the database. As mentioned, SP_DETACH_DB expects no active connections. Hence, the command runs successfully after the database is switched to single user mode.
Outcome: Database SAMPLE re-attaches successfully.
Example:
CREATEDATABASE[SAMPLE]
ONPRIMARY
(NAME= N'SAMPLE', FILENAME = N'F:\SQL_DATA\sample.mdf',SIZE= 4096KB , FILEGROWTH = 1024KB )
LOGON
(NAME= N'SAMPLE_log', FILENAME = N'D:\SQL_LOGS\sample_log.ldf',SIZE= 7168KB , FILEGROWTH = 10%)
GO
ALTERDATABASE[SAMPLE]SETRECOVERYFULL
GO
USE [SAMPLE]
GO
IFNOTEXISTS (SELECTnameFROMsys.filegroupsWHEREis_default=1ANDname= N'PRIMARY')
ALTERDATABASE[SAMPLE]MODIFYFILEGROUP [PRIMARY]DEFAULT
GO
The database is successfully detached when it is taken in single-user mode. Also, note that the entry fromsys.sysdatabasesview has been removed.
ALTERDATABASE[SAMPLE]SETSINGLE_USERWITHROLLBACKIMMEDIATE
GO
EXECsp_detach_db'SAMPLE'
GO
SELECT*FROMsys.sysdatabaseswherenamelike'%SAMPLE%'
GO
If we would like to migrate the database to a new drive, manually move the database files to the new location while the database is detached and provide the new database files' path in the "Create Database" statement below. The database would then be attached to the new location.
USE [master]
GO
CREATEDATABASE[SAMPLE]ON
( FILENAME = N'F:\SQL_DATA\sample.mdf'),
( FILENAME = N'D:\SQL_LOGS\sample_log.ldf')
FORATTACH
GO
When SQL instance is cleanly shut down
As discussed above, when SQL instance is cleanly shut down with no uncommitted transactions in the data file(s) and nothing to be recovered from the log file, it is as though the database was detached using “SP_DETACH_DB”. The following example proves that a database can be re-attached successfully post SQL reboot using “FOR ATTACH”.
As soon as the SQL instance was stopped, the database files were renamed. This caused the database “SAMPLE” to go into “Recovery Pending” upon instance restart. The database files of the SAMPLE database are attached to a new database “SAMPLE_2”.
**Outcome: **The database SAMPLE_2 is successfully attached using the data and log files of SAMPLE database. Again, SAMPLE has turned Recovery Pending” as the database files are now being used by SAMPLE_2.
Example:
SQL instance is shut down and the database files are renamed to "*_2". These files are attached as a new database SAMPLE_2.
USE [master]
GO
CREATEDATABASE[SAMPLE_2]ON
( FILENAME = N'F:\SQL_DATA\sample_2.mdf'),
( FILENAME = N'D:\SQL_LOGS\sample_log_2.ldf')
FORATTACH
GO
When a single log file is missing
When a database, being detached has only one log (.ldf) file which goes missing after the database has been detached, then SQL Server rebuilds the log file automatically when the database is attached with only data file using “CREATE DATABASE…FOR ATTACH”. The condition for the rebuilt of a log file to succeed is that the database must have been cleanly detached either using “SP_DETACH_DB” or the SQL instance was taken down when there was no activity against the database. The reason is, if there are records in the log file that need to be rolled back or rolled forward then SQL Server cannot rebuild the missing log, in which case the presence of the log file is a must.
A crucial point to note here is, SQL rebuilds the log using “FOR ATTACH” only if the database had a single log file which is missing. If there were multiple log files that are missing, FOR ATTACH will fail, in which case, “ATTACH_REBUILD_LOG” can be used. This is discussed in the next example.
In the below example, the database SAMPLE_2 is detached and its log file is removed. Because there were no ongoing transactions, the attach using “FOR ATTACH” completed successfully and rebuilt a new log file.
Outcome: SAMPLE_2 successfully attaches using FOR ATTACH by rebuilding the log file.
Example:
ALTERDATABASE[SAMPLE_2]SETSINGLE_USERWITHROLLBACKIMMEDIATE
GO
EXECsp_detach_db'SAMPLE_2'
GO
USE [master]
GO
CREATEDATABASE[SAMPLE_2]ON
( FILENAME = N'F:\SQL_DATA\sample_2.mdf')
FORATTACH
GO
When multiple log files are missing
When multiple log files are missing, then “FOR ATTACH” fails. In this case, ATTACH_REBUILD_LOG is used to rebuild the log. This is demonstrated in the example below.
In the below example, an additional log file is added to database SAMPLE_2 thus, making the total number of log files as 2. The database is detached, and the two log files are removed. As we can see below, an attempt to attach this database failed since there are two missing log files and “FOR ATTACH” holds good only when there is a single missing log file. Hence, the attach succeeds only by using the “ATTACH_REBUILD_LOG”.
Outcome: SAMPLE_2 successfully attaches using ATTACH_REBUILD_LOG and a new log file is automatically created.
Example:
USE [master]
GO
ALTERDATABASE[SAMPLE_2]ADDLOG FILE (NAME= N'SAMPLE_log2', FILENAME = N'F:\SQL_DATA\sample_2_log2.ldf',SIZE= 7168KB , FILEGROWTH = 10%)
GO
ALTERDATABASE[SAMPLE_2]SETSINGLE_USERWITHROLLBACKIMMEDIATE
GO
EXECsp_detach_db'SAMPLE_2'
GO
--Rename the log files at this point, else, the operation will use the old exisitng ones
USE [master]
GO
CREATEDATABASE[SAMPLE_2]ON
( FILENAME = N'F:\SQL_DATA\sample_2.mdf')
FORATTACH
GO
Now, using ATTACH_REBUILD_LOG,
USE [master]
GO
CREATEDATABASE[SAMPLE_2]ON
( FILENAME = N'F:\SQL_DATA\sample_2.mdf')
FORATTACH_REBUILD_LOG
GO
When a log file cannot be rebuilt
The example below demonstrates the behavior of the attach operation when the log file cannot be rebuilt. This happens when the log file which had records that need to be recovered goes missing. SQL Server cannot recover the database upon reattaching due to the missing log file and hence the operation fails.
Note: The best way to come out of this situation is by restoring the most recent good backup. If a backup is unavailable, then there is a workaround that Paul Randal talks about in his blog but it may or may not work. Further, it has many downsides so restoring from the latest backup should be the way to go.
In the below example, a new table named “TEST” is created an insert operation is started. While the insert is still in progress, the SQL instance is stopped. As we can see, the running transaction fails. The log file for this database is then removed and an attempt to attach this database fails because the database needs to be recovered and the recovery is only possible when there is a log file.
The same error is logged in the SQL error logs because when the SQL instance comes back online, it attempts to rebuild the missing log but since the missing log file had records that need to be rolled back, SQL fails to rebuild the log.
Outcome: The attach of SAMPLE_2 fails due to the missing log file that contained an active transaction.
Example:
Use SAMPLE_2
GO
CreatetableTEST (idint,namevarchar(max))
GO
--Insert records in the newly created TEST table.
--SQL instance is stopped while the below insert is in progress.
--Once the SQL instance stops, the log file is removed
Use SAMPLE_2
GO
declare@idint
select@id = 1
while @id <= 800000
begin
insertintoTESTvalues(@id,'john'+convert(varchar(5), @id))
select@id = @id + 1
end
While the insert is in progress, SQL instance is stopped and the session is killed. The log file is then removed and an attempt to attach the database as a new database SAMPLE_3 is then made.
--the data file sample_2.mdf is renamed to _3.mdf after taking SAMPLE_2 offline
--An attempt it made to attach the _3.mdf datafile as a new database SAMPLE_3
--The attempt fails because the data file has a missing corresponding log file that has incomplete records
--and needs to perform recovery
USE [master]
GO
CREATEDATABASE[SAMPLE_3]ON
( FILENAME = N'F:\SQL_DATA\sample_3.mdf')
FORATTACH
GO
A similar error is logged in the SQL Server error logs when the instance came back up.
When the database is read-only
When a database is in read-only mode and goes offline. Further if its log file is removed, SQL Server automatically tries to rebuild its log when it comes online the next time but because the database was read-only when it went offline, the attempt fails because, as mentioned above, the information about the log file is stored in the primary data file and because the database was read-only, the new log file’s details cannot be updated in the primary file, hence the operation fails. This is demonstrated in the example below.
Note: The data file can still be attached using “FOR ATTACH” if there were no active records in the missing log that require recovery.
Outcome: SQL Server’s automatic attempt to rebuild a missing log file fails since the database was in read-only mode.
Example:
ALTERDATABASE[SAMPLE_2]SETREAD_ONLYWITHNO_WAIT
GO
ALTERDATABASE[SAMPLE_2]SETOFFLINEWITHROLLBACKIMMEDIATE
GO
--Now remove the log file
ALTERDATABASE[SAMPLE_2]SETONLINEWITHROLLBACKIMMEDIATE
GO
--SQL automatically tried rebuilding the log but failed as the primary data file is read-only
--This data file can be attached as a new database using FOR ATTACH
How log rebuild breaks the log backup chain
When a database is attached using “ATTACH_REBUILD_LOG”, SQL Server rebuilds a new log file for it. The attached database changes its recovery model to SIMPLE and if there were sequential log backups for it; they will be good only up to the point the database was detached. A new log backup cannot be taken post attach until the database is switched to full recovery model and re-initialized with a fresh full backup. This behavior is demonstrated in the following example.
Example:
In the below example, a new table is created in the SAMPLE database and records are inserted into it. The first full backup is followed by a couple of T-log backups. Then the database is detached and its log file is removed. The same database is then attached using "ATTACH_REBUILD_LOG" which rebuilds the log. Finally, an attempt is made to take the third log backup which ends up failing due to the change in recovery model to SIMPLE when the database was attached.
Use SAMPLE
GO
CreatetableTEST (idint,namevarchar(max))
GO
InsertintoTESTvalues(1,'Joseph')
GO
--At this point, take a full backup of SAMPLE database
BACKUPDATABASE[SAMPLE]TODISK = N'D:\SQL_BACKUPS\sample_Full.bak'WITHNOFORMAT, NOINIT,
NAME= N'SAMPLE-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
--After full backup completes, insert another row
InsertintoTESTvalues(2,'Josh')
GO
--Now kick off the first log backup
BACKUP LOG [SAMPLE]TODISK = N'D:\SQL_BACKUPS\sample_log1.trn'WITHNOFORMAT, NOINIT,
NAME= N'SAMPLE-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
--insert another record
InsertintoTESTvalues(3,'Nancy')
GO
--Now kick off the second log backup
BACKUP LOG [SAMPLE]TODISK = N'D:\SQL_BACKUPS\sample_log2.trn'WITHNOFORMAT, NOINIT,
NAME= N'SAMPLE-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
--Now detach the database
ALTERDATABASE[SAMPLE]SETSINGLE_USERWITHROLLBACKIMMEDIATE
GO
EXECsp_detach_db'SAMPLE'
GO
--After detach is successful, remove the log file and attach it.
--It rebuilds a new log file
USE [master]
GO
CREATEDATABASE[SAMPLE]ON
( FILENAME = N'F:\SQL_Data\sample.mdf')
FORATTACH_REBUILD_LOG
GO
--Insert a new record
InsertintoTESTvalues(4,'Kelly')
GO
--Now try to take the third log backup. Remember the earlier two log backups occurred before the DB was detached
--the log backup fails because when the log was rebuilt in the above step, it changed the recovery model of the
--database to simple and hence, the log backup chain is broken & needs to be reinitialized with a new full backup.
BACKUP LOG [SAMPLE]TODISK = N'D:\SQL_BACKUPS\sample_log3.trn'WITHNOFORMAT, NOINIT,
NAME= N'SAMPLE-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Detach vs Drop
While detaching a database removes it from the SQL instance and makes the database disappear from the SSMS object explorer and sys.sysdatabases catalog view, it doesn't delete the underlying database files i.e. the .mdf and the .ldf files. The files continue to reside on the drives.
In contrast, dropping a database removes all database files and leaves no traces of the database on the SQL instance. Just like detach, SQL Server ensures that there are no active connections to the database being dropped (in which case, the drop operation fails) but it never checks for dirty pages in the buffer cache or any open user transactions. The only way to get the database back after a drop operation is to restore it from a previous known good backup. If there is none available, then the database is gone for good.
Conclusion
Detach and attach can be used to either migrate database files to other locations or attach a database when you are left with only data files with no log files. As long as the database was cleanly detached, it should be attachable. Although Microsoft recommends using the "ALTER DATABASE...MODIFY FILE" to migrate the database files, detach and attach can still be used as an alternative. One of the occasions where attaching a database proves helpful is when the database's log file grows very large. You can cleanly detach the database followed by removing its log file and attaching it with "ATTACH_REBUILD_LOG" so that a new log file with much smaller size is created. This is a quick alternative to shrinking the log.