Sql-server – attach database – and rebuild log AT NEW LOCATION

sql server

I have a running database that I cannot take offline. I also have an mdf-file copy of that same database (snapshot of a HDD backup taken at earlier time), but no ldf file.

I need to attach this database MDF file under a new name on the same server.

So I need to attach the DB without the log file and rebuild the log file at a new location (b/c the current log file location is obviously in use).

How do I do that? I tried this, but didn't work:

CREATE DATABASE [dbname] ON 
(FILENAME = N'E:\db_copy.mdf' ), --this file DOES exist
(FILENAME = N'E:\log_copy.ldf' ) --this file DOES NOT exist
FOR ATTACH_REBUILD_LOG

Best Answer

You cannot specify the location for the log file on ATTACH_REBUILD_LOG. Per https://msdn.microsoft.com/en-us/library/ms176061.aspx

the ATTACH_REBUILD_LOG automatically creates a new, 1 MB log file. This file is placed in the default log-file location.

As a workaround what you could do in this instance is change the default location for the log files to a new folder, and attach the data file with the rebuild log. Once that is done set the default location back to it's prior setting. Then you can run and ATLER DATABASE command to set the log file to the path/filename that you desire, set the database offline, copy over the log file to that new location, and set the database online again. After that just clean up the old file and folder.

Related Solutions

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.

Sql-server – SQL Server 2008 R2 Log File Location

Perhaps the log is on is a network share that is only visible to the SQL Server service account?

If this were the case you could log onto the server using that same service account used by SQL Server, and "see" the drive in Windows Explorer.

SQL Server will refuse to load a database if it cannot locate a valid log file. It will NOT simply create the log file in the "default" Log file location.

An alternate method to see if the log file really exists, and perhaps where it actually is, would be to use the xp_cmdshell extended stored procedure to run a dir command on the SQL Server itself, in the context of the service account. This relies on having exec sp_configure 'xp_cmdshell' being set to 1. If xp_cmdshell is enabled, you could run the following:

EXEC xp_cmdshell 'DIR C:\temp\*.ldf';  --replace with the path to the log file in question
EXEC xp_cmdshell 'net use'; -- this shows the mapped network drives SQL Server can "see"

Best Answer

Related Solutions

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

Sql-server – SQL Server 2008 R2 Log File Location

Related Question