Things To Think About Before Detaching And Attaching A Database

Jana Sattainathan has a few considerations before running a detach-attach operation:

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.”

Click through for several tips of similar ilk.

Related Posts

Monitoring Apache NiFi With A Custom Dashboard

Tim Spann has started a new series on monitoring Apache NiFi: In this little proof of concept work, we grab some of these flows process them in Apache NiFi and then store them in Apache Hive 3 tables for analytics. We should probably push the data to HBase for aggregates and Druid for time series. […]

Read More

Finding Databases With Multiple Data Or Log Files

Lori Brown has a couple of quick scripts to help find databases made up of several data or log files: This might be kind of basic but since I am working on a comprehensive script to discover things that a DBA really needs to know about, I made a couple of queries that will produce […]

Read More

Categories

April 2018
MTWTFSS
« Mar May »
 1
2345678
9101112131415
16171819202122
23242526272829
30