Sql-server – Copy database removed source – Cannot acces mdf file

sql serversql-server-2017ssms

I attempted to copy a database, using Microsoft SQL Server Management Studio (SSMS), but right-clicking and following the wizard. I wanted to target server to be the same as the source, so I added "_new" to the database name.

The process failed (unfortunately I missed the error message) and now the source database is missing as well. I found another post on the same topic, which suggests locating and atttaching the .mdf file.

However, when I try to attach the mdf file, I get the following error meesage:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

Luckily this was just a test database, so I didn't lose any data, that couldn't be easily reproduced. But is there a way to recover from this? If it ever happened on a production database, I would like to be prepared.

Best Answer

The process failed (unfortunately I missed the error message) and now the source database is missing as well. I found another post on the same topic, which suggests locating and atttaching the .mdf file.

If the database is no longer listed on the instance, but you can still see the .mdf file, it somehow got detached during this first unsuccessful attempt. The screenshot error says:

CREATE FILE encountered operating system error 5(Access denied.)

The error is related to security and Microsoft states something about security changes during the process of detaching or attaching a database:

File access permissions are set during a number of database operations, including detaching or attaching a database.

In order to fix it, check the permissions of the .mdf file and if you confirm it has really been changed, alter them to allow you to execute the attach procedure or run the attach procedure with the same login that is now the owner of the .mdf file.

DANIEL HUTMACHER described this behaviour very well in his article Detaching a database also alters file permissions and his conclusions are:

Detaching a database when logged on with a Windows account will change the file permissions, restricting SQL Server’s service account and only allowing your user to access the files.

Detaching a database as “sa” or a SQL Server account will leave the files’ permission sets unchanged.

If you’re connected as “sa” or another SQL Server account, SQL Server’s service account needs permissions on any database files you
want to attach.

If you’re connected as a Windows login, you need sufficient permissions on the files to attach them.

Related Solutions

Sql-server – Repair a SQL Server 2000 MDF file

The fastest, simplest solution is to restore your last good backup. If that isn't an option you can try a workaround to make MSSQL think the database already exists:

http://sqlskills.com/BLOGS/PAUL/post/Disaster-recovery-101-hack-attach-a-damaged-database.aspx

Then you can run DBCC and see if it can repair the database, but even in that case you may still have data loss. You could also call Microsoft (maybe you already have a support contract), but the bottom line is that if you have a damaged database and no backup then there's not much that anyone can do.

Sql-server – Database cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery

This isn't an access issue. The problem is that you are attaching the database to a higher version of SQL Server than the instance the .mdf was originally attached to and it's read only. SQL Server is trying to upgrade the database as part of the attach and can not because the db is in read-only mode.

If your database is in READ_ONLY (which sounds like it is the case based on your error messages), then you need all files (both log and data) to attach it per the Technet documentation. If you look under the FOR ATTACH clause, it explicitly describes that:

...for a read-only database, the log cannot be rebuilt because the primary file cannot be updated. Therefore, when you attach a read-only database whose log is unavailable, you must provide the log files or files in the FOR ATTACH clause.

You need the accompanying .ldf for the database. I would contact whoever provided the database and ask them to provide both the .mdf and .ldf files. Then, you can attach it with the following syntax:

CREATE DATABASE [foo]
ON (FILENAME='<<path to mdf>>')
LOG ON (FILENAME='<<path to ldf>>')
FOR ATTACH;

The READ_ONLY status cannot be removed from the database without attaching it because it is stored in the metadata of the database. To alter it, the database needs to be online and attached.

Another alternative is to ask for a backup of the database. You can work around some of these issues more easily if you're doing a database restore instead of an attach.

Best Answer

Related Solutions

Sql-server – Repair a SQL Server 2000 MDF file

Sql-server – Database cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery

Related Question