MS SQL: Attach database failed for Server because the database is read-only.

Attach database failed for Server because the database is read-only

Scenario: When you install any SQL DB based application, the default SQL DB goes into the respective SQL server ProgramFiles folder (like C:Program FilesMicrosoft SQL ServerMSSQL.5MSSQLDATA in case of SQL Server 2005 Express). However, you would like to relocate the DB to a different path that can scale up to the limits of DB growth estimated.

When you copy the SQL DB to a different location, and try to attach the DB it will fail with below error:

 

TITLE: Microsoft SQL Server Management Studio Express

——————————

Attach database failed for Server ‘TESTSRVMYSQL_INSTANCE’. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

——————————

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

——————————

Failed to update database “InvenDB” because the database is read-only. (Microsoft SQL Server, Error: 3906)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3906&LinkId=20476

——————————

BUTTONS:

OK

——————————

Cause:

When you copy the SQL DB the NTFS permissions aren’t retained. Specifically the SQLServer2005MSSQLUser permissions will be revoked as shown below:

C:>icacls “C:Program FilesMicrosoft SQL ServerMSSQL.5MSSQLDATAInvenDB.mdf”
C:Program FilesMicrosoft SQL ServerMSSQL.5MSSQLDATAInvenDB.mdf

TESTSRVSQLServer2005MSSQLUser$TESTSRV$MYSQL_INSTANCE:(OI)(CI)(F)
NT AUTHORITYNETWORK SERVICE:(OI)(CI)(F)

BUILTINAdministrators:(OI)(CI)(F)

Successfully processed 1 files; Failed processing 0 files

C:>

 

C:>icacls “C:tempInvenDB.mdf”
C:tempInvenDB.mdf NT AUTHORITYSYSTEM:(I)(F)
BUILTINAdministrators:(I)(F)
BUILTINUsers:(I)(RX)

Successfully processed 1 files; Failed processing 0 files

C:>

 

Fix:

Copy the DB files along with File ACLs and Ownership using the command line tools like Xcopy as shown below:

xcopy /O “C:\Program Files\Microsoft SQL Server\MSSQL.5\MSSQLData\InvenDB.mdf” “C:temp”

when used /O option, xcopy Copies file ownership and ACL information.

 

 

Automation:

When you use xcopy /O at file level in Windows 2003 platform, it still fails to copy the ownership at times. The workaround is to assign the required permissions on the folder level and copy over the folder itself as shown below:

Fails: xcopy /O “C:\Program Files\Microsoft SQL Server\MSSQL.5\MSSQLData*” “C:temp”

Works: xcopy /O “C:\Program Files\Microsoft SQL Server\MSSQL.5\MSSQLData” “C:\temp\Data” /I /Y /Q