SQL Server: Detach/Attach Gotchas!

Please checkout my post on “Moving Datafiles” which is very related to this method but is preferred more.

One would think that detaching and attaching databases is one of the simplest things to do. It is. However, there are so many things that can break by that simple operation by itself and nothing more. Recently, I had to move 50+ databases to a new physical location. I foresaw some problems but unfortunately did not anticipate others. This blog post is a starting point for a “Gotcha List”  and oddities that you need to know about when detaching and attaching.

A great resource for auto-generating detach/attach if you have a lot of databases is this post below. You just have to make a few changes to script out the complete attach/detaches based on your custom locations.

https://www.mssqltips.com/sqlservertip/1786/auto-generate-sql-server-database-attach-and-detach-scripts/

An alternate way to relocate files – OFFLINE option

Based on the comment from skshakya (Thank you), I am adding this note. You can prevent a lot of the problems by not “detaching” but rather taking the database offline and moving the files as explained in my previous blog post below. It is especially a great reference if you moving a lot of databases/datafiles conditionally.

https://sqljana.wordpress.com/2017/08/01/sql-server-move-data-files-to-another-drive/

With this method, I am not aware of any hidden gotchas although I would not expect most of the problems that happen with detach/attach!

Is there an ONLINE option to relocate? Yes, kind of…

This post shows a method to physically move the data (instead of the files) from one datafile to another datafile.

If you still have to detach and attach for whatever reason, please keep reading.

Database is in use and you cannot “Detach”

You can use one of the many methods from StackOverflow to make sure that the database(s) you are trying to detach are not in use. WARNING: That link is to DROP a database. So, DO NOT just copy and paste everything. You can just use this statement before your detach statement.

ALTER DATABASE [MyDatabase] SET single_user WITH ROLLBACK IMMEDIATE

File permissions change when you “Detach”

On the detached database files, the file permissions change when detaching. i.e., The AD account (your AD account) performing the detach operation becomes the owner and the only person with permissions to the file. It does not inherit the permissions from the folder. Oddly, if you just detach and reattach, it would attach fine even though SQL Server Service account does not have any explicit permissions on the files. However, others cannot attach a file that you detached even if they are on the Administrators group until they are explicitly granted permissions on the files themselves. This is well explained in this MSSQLTips article. Quoting the article, if you want to retain file permissions on detach, set the trace flag 1802.

“SQL Server 2005 introduced trace flag 1802 which retains the database files permission after the detach operation. The trace flag is tested and still applicable with SQL Server 2016.”

Permissions issues when attaching on another instance

When you try to reattach the file, you may run into permissions issues especially if it is on another instance and another location from where you detached.

Msg 5120, Level 16, State 101, Line 3
Unable to open the physical file "D:\Data\MyDatabase.mdf".
Operating system error 5: "5(Access is denied.)".

Make sure that your AD account (not the SQL Server Service account) has permissions to the file you are trying to attach.

You can use the ACL approach described later if you have a lot of files on which you need to reset permissions

File permissions change when you “Attach” too!

Let us say that you manged to get past the permissions issues you may have had and set some explicit permissions on the files as noted above. As soon as you “Attach”, all the permissions you painfully set will be wiped and only the Administrators and SQL Server service account have privileges now (besides OWNER RIGHTS). You would have to re-grant non-administrators the privileges even to view the files (although they were fine before you “attached”) in case you need to do such a thing.

Use PowerShell. Don’t right-click to change permissions on a lot of files!

A good way to deal with file permissions is by using PowerShell functions Get-ACL and Set-ACL (to manage Access Control). If the permissions on the files are messed-up already and you have to set permissions on a lot of files, here is a simple way to do so. If the permissions are not already messed-up, the procedure is similar too except, you will save the permissions to the model file ahead of doing any detach/attach.

You would have to set the permissions back after you do the final “Attach”.

First create a file named Model.txt in the folder where majority of the .mdf files are in the target location. By default, this file will inherit all folder permissions. To that, add your additional permissions (like IDs of other DBA’s and such). We will then copy the permissions on this file and apply it to the other .mdf/.ldf files in the folder using this PowerShell snippet (you know the concept, please change the locations and filter files as you need)

#Get the permissions from our model file with right permissions setup
#------------------------------------
$modelACL = get-acl -LiteralPath E:\DATA\Model.txt

#Change permissions on the .mdf files (DATA files)
#------------------------------------
$filesFolderTarget = 'E:\DATA\*.*df'
$filesTarget = Get-ChildItem $filesFolderTarget

foreach($file in $filesTarget)
{
    Set-Acl -LiteralPath $file.FullName -AclObject $modelACL
}

#Change permissions on the .ldf files (LOG files)
#------------------------------------
$filesFolderTarget = 'L:\LOG\*.*df'
$filesTarget = Get-ChildItem $filesFolderTarget

foreach($file in $filesTarget)
{
    Set-Acl -LiteralPath $file.FullName -AclObject $modelACL
}

Users are orphaned when attaching to another instance

This is obvious when you have database users without corresponding logins. You may also have database users with matching logins but the mismatching SID’s. You just have to deal with it and get them fixed.

Typical errors fixed by fixing orphaned users are:

Server: Msg 229, Level 14, State 5
The SELECT permission was denied on the object '', database '', schema 'dbo'.

The login operation for an user may itself fail

Login failed for user ''

An user may be able to login but may not be able to access the database

The database [DBName] is not accessible.

Default-database for user is reset on “detach” but restored on “attach” (Good news)

Thanks to the comment from Stephen Morris on this bullet.

When the default database for an user is detached, when the user logs in, he/she will be greeted this message:

TITLE: Connect to Server
------------------------------

Cannot connect to MYINSTANCE.

------------------------------
ADDITIONAL INFORMATION:

Cannot open user default database. Login failed.
Login failed for user 'MYDOMAIN\MYLOGIN'. (Microsoft SQL Server, Error: 4064)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=4064&LinkId=20476

The good news is that if the detached database is attached to the same instance, the default database for the user is indeed restored!

Stephen reports that this was an issue (not defaulting on re-attach) in the SQL 2005 but my tests on SQL 2014 shows this as having been fixed.

Read-only databases become regular read/write databases on “Attach”

You just have to make a note of the databases that are read-only before you detach and set them back to be read-only.

The command is straight-forward.

ALTER DATABASE MyDatabase SET READ_ONLY WITH NO_WAIT

You can script this ahead of time using this SQL:

SELECT 'ALTER DATABASE ' + QUOTENAME(name) + ' SET READ_ONLY WITH NO_WAIT;'
FROM sys.databases
WHERE is_read_only = 1

Service Broker is Broken!

Service broker is disabled when a database which had it enabled is detached and attached. You need to manually enable it. You may need exclusive access to do this. You can again use, ALTER DATABASE [MyDatabase] SET single_user WITH ROLLBACK IMMEDIATE

ALTER DATABASE ENABLE BROKER

You can generate the command ahead of time

SELECT 'ALTER DATABASE ' + QUOTENAME(name) + ' ENABLE BROKER;'
FROM sys.databases
WHERE is_broker_enabled = 1

“Trustworthy” settings are gone!

This is similar to Service Broker. Just re-enable by scripting this ahead of time. This is necessary for the CLR assemblies and cross-database chaining to work!

ALTER DATABASE SET TRUSTWORTHY ON;

You can generate the command ahead of time

SELECT 'ALTER DATABASE ' + QUOTENAME(name) + ' SET TRUSTWORTHY ON;'
FROM sys.databases
WHERE is_trustworthy_on = 1

There are a few additional things (like owner) you may have to check/change as shown below:

USE MASTER
GO
exec sp_configure 'clr enabled',1
go
reconfigure with override
GO 

USE YOUR_DATABASENAME_WITH_ASSEMBLIES
GO 

EXEC sp_changedbowner 'sa'
GO
ALTER DATABASE YOUR_DATABASENAME_WITH_ASSEMBLIES SET TRUSTWORTHY ON
GO

Database owner has changed to your account!

Thanks to the comment from John F for noting ownership change.

The owner of the DB does get changed upon detach/attach. You may have to save that off too if there is a dependency on that. CLR assembly permissions may require that the “sa” be the owner and not your AD account!

Make sure that you send the query results to “text” instead of “grid” to see the newlines in between statements. “Query -> Results To -> Results To Text” or CTRL+T.

SELECT 'USE ' +QUOTENAME(name) + CHAR(13) + 'EXEC sp_changedbowner ''' + suser_sname(owner_sid) + ''' ;'+ CHAR(13) + CHAR(13)
FROM sys.databases
where database_id >=5

If the owners are already messed-up, i.e., you forgot to save off the owners ahead of time, it is pretty safe to set the owner to “sa”.

SELECT 'USE ' +QUOTENAME(name) + CHAR(13) + 'EXEC sp_changedbowner ''sa'' ;'+ CHAR(13) + CHAR(13)
FROM sys.databases
where database_id >=5

Make sure you run the output produced by the above SQL manually!

Also, you would have to do this ahead of marking a database read-only as you cannot change anything about a read-only database including the owner.

Always-on/HA/Replication is broken!

You have to check if you have Always On or Replication enabled. Your HA/DR strategy might be in serious trouble if you detach a database without carefully analyzing these.

Cross-database ownership chaining breaks

Thanks to the comment from Hebel for noting this.

You need to note which databases have cross-database ownership chaining enabled and re-enable after re-attaching. You don’t have to worry about cross-database ownership chaining if the option is enabled at the instance level (but you have to worry about security).

Cross DB ownership chaining as explained in MSSQLTips: If you have an object in one database which refers to an object in a second database, both databases are configured for database ownership chaining (or it is configured at the server level), and both objects have the same owner, then a cross-database ownership chain will form.

SELECT is_db_chaining_on,name FROM sys.databases;

You could enable it for the instance (prev setting not affected by detach/attach)

EXECUTE sp_configure 'show advanced', 1;
RECONFIGURE;
EXECUTE sp_configure 'cross db ownership chaining', 1;
RECONFIGURE;

..or specific database

ALTER DATABASE MyDatabase SET DB_CHAINING ON;

If you are proactive, you can pre-generate and save:

SELECT 'ALTER DATABASE ' +QUOTENAME(name) + ' SET DB_CHAINING ON;'
FROM sys.databases
where database_id >=5
	AND is_db_chaining_on = 1

Recommendations

• Use the OFFLINE option (at the top of this post) if you can, instead of Detach/Attach
• If database is small enough and you could relocate the data
• If you are migrating from one instance to another, use dbatools. After all, the most powerful PowerShell library for SQL Server started out as a database migration toolset
• Gather and save as much information “before” you migrate. I recommend SQLTranscriptase
• Generate your detach/attach using the link at the very top of this post
• Use this blog post as a checklist and make sure you have everything covered/scripted (like Service broker etc).

I will try to keep this up to date by adding new items as I get to know them.