attach database - and rebuild log AT NEW LOCATION

Question

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

Answer 1

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.

• jazzcat: The problem is, the rebuild log always tries to put the log file at the old location, no matter which is the default location.
• Erik Reasonable Rates Darling: @jazzcat just map a dummy drive with the path it wants temporarily?
• Aaron Bertrand: You need to restart the instance in order for a change to the default locations to take hold. This is documented here. Unfortunately, restarting the instance conflicts with the requirement of keeping the other database online.
• Aaron Bertrand: @sp_BlitzErik The drive and path it wants does exist, but it contains a log file with the same name. I think the cleanest way is to involve a second instance.

Answer 2

Attach it to a different instance with the same @@VERSION, if you want you can change the physical names of the files to avoid any conflicts, but more importantly: Take a proper backup from this other instance (don't use MDF files as backups), and then you can use RESTORE ... WITH MOVE.