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.

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.