Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I moved my database files. When I go attach the MDF file by itself, SQL Server will tell me that it can't find the log files. How do I go about asking the MDF what files it is expecting before attaching it?

More background information: I have a bunch of SAN backup volumes. I attached them to Windows through iSCSI, and now half the drive letters are messed up. Moreover, I multiple volumes should be mapped to the save drive letter, so I can't restore the correct drive letters.

I know that the files are all there, but I don't know how many and which LDF/NDFs I should be attaching with each MDF.

share|improve this question

3 Answers 3

up vote 3 down vote accepted

There's no way to find those files with a detached database .mdf. As Aaron suggests, you can create the database with ATTACH_REBUILD_LOG. Another option is if you have an "old" backup file of the database, you can use RESTORE FILELISTONLY to interrogate the backup file for the state of the database files at the time of that backup. This will give you a starting point to track down your files.

Edit Because I like Powershell, here's a script that will read through all the full backup files in a directory and build an attachdbs.sql in your Documents folder:

param([parameter(Mandatory=$true)][string] $dir,
        [parameter(Mandatory=$true)][string] $server)


#load assemblies
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
$smosrv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $server


#get backup files
$files = gci $dir | where {$_.name -like "*.bak"}

$output=([Environment]::GetFolderPath("MyDocuments")) + "\attachdbs.sql"

"/*****************************************" > $output
"Attach script based off of backup files" >> $output
"*****************************************/" >> $output
foreach($file in $files){
    $rs=new-object("Microsoft.SqlServer.Management.Smo.Restore")
    $rs.Devices.AddDevice($file.FullName, "File")
    $hd=$rs.ReadBackupHeader($smosrv)
    $dbname=$hd.Rows[0].DatabaseName

    $dbfiles=$rs.ReadFileList($smosrv)

    "CREATE DATABASE $dbname ON" >> $output
    $filewrite=@()
    foreach($dbfile in $dbfiles){
        $filewrite+="(FILENAME='"+$dbfile.PhysicalName+"')"
    }

    $filewrite -join ",`n" >> $output

    "FOR ATTACH; `n--------------------------" >> $output
    }
share|improve this answer
    
Thanks for the suggestions. I don't want to rebuild the log since I have the log files, and I just need to know what their names are. I would prefer to not have to go through the backups if I don't have to, but I may have to try the FILELISTONLY option. –  John Tseng Aug 13 '13 at 23:01
    
You only need one backup, I'd recommend your most recent full. The command will list all files in the backup, which should be all your database files. Including the log file. –  Mike Fal Aug 13 '13 at 23:02
    
Well, I have about 600 databases that are backed up individually. =/ So even at one each, that's going to take a while. –  John Tseng Aug 13 '13 at 23:07
    
@JohnTseng Or, if you can assemble all the backup files into one location, you could use Powershell to read through your files and report on the file locations. –  Mike Fal Aug 13 '13 at 23:11
    
That I can do. I have all the backups in a few locations. I can loop through them no problem. So I would RESTORE FILELISTONLY on all of them, and then query backupset? –  John Tseng Aug 13 '13 at 23:15

If you have the information for all of the backups in the backupset table, you can build all of the RESTORE FILELISTONLY commands this way, then insert the results into a #temp table, then use a cursor to build the eventual CREATE DATABASE ... FOR ATTACH commands. I still think it would be faster to suck it up and just restore the backups already, but to each his own I suppose...

First, create a #temp table to hold the RESTORE FILELISTONLY output:

SET NOCOUNT ON;

CREATE TABLE #x
(
  ln SYSNAME, [path] NVARCHAR(512), Type CHAR(1), fgn NVARCHAR(128), 
  size BIGINT, msize BIGINT, FileId INT, lsn1 BIGINT, lsn2 BIGINT, 
  id UNIQUEIDENTIFIER, lsn3 BIGINT, lsn4 BIGINT, bsize BIGINT, bs INT, 
  fg INT, lgid UNIQUEIDENTIFIER, lsn5 BIGINT, bid UNIQUEIDENTIFIER, 
  ro BIT, p BIT, TDEThumbprint VARBINARY(32)
); 

Now, based on the backups stored in msdb..backupset, generate the commands themselves and run them:

DECLARE @sql NVARCHAR(MAX) = N'';

;WITH x AS 
(
  SELECT *,
    rn = ROW_NUMBER() OVER (PARTITION BY database_name 
     ORDER BY backup_start_date DESC)
  FROM msdb..backupset
)
SELECT @sql += '
  INSERT #x(ln) SELECT ''' + x.database_name + ''';
  INSERT #x EXEC sp_executesql N''RESTORE FILELISTONLY '
  + ' FROM DISK = ''''' + mf.physical_device_name + ''''''
  + ' WITH FILE = ' + RTRIM(x.position) + ';'';'
FROM x
INNER JOIN msdb..backupmediaset AS ms 
  ON x.media_set_id = ms.media_set_id
INNER JOIN msdb..backupmediafamily AS mf 
  ON ms.media_set_id = mf.media_set_id
WHERE x.rn = 1;

EXEC sp_executesql @sql;

Now, some of those might fail, if the .bak files have been moved. Also note that the position value is important, in case you have backed up multiple times to the same file. You want to be sure you have the most recent version that reflects the current file structure of the database (but there is still no guarantee that the last backup reflects reality).

Now, add a column to serve as a row number. Warning: this relies on "natural" sort order of a heap. If this table is super large this may not work.

ALTER TABLE #x ADD rn INT;
GO

;WITH x AS (SELECT *, r = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM #x)
UPDATE x SET rn = r
OPTION (MAXDOP 1);
GO

Once you have the paths in there, you will see that they reflect the old locations and drive letters of the database files. You may want to run a query that replaces the known old locations with the new ones. For example, if you know that databases previously located at C:\MSSQL\Data\ are not located at D:\MyInstance\SQLData\, you can run:

UPDATE #x SET [path] = REPLACE([path], 'C:\MSSQLData\', 'D:\MyInstance\SQLData\')
  WHERE [path] LIKE 'C:\MSSQLData\%';

Wash, rinse, repeat until all of the [path] values accurately reflect the new location of the files.

Now, we're ready to roll up our sleeves. This could very well serve as a bad practices, do as I say but not as I do post. We need a cursor to build the CREATE DATABASE ... FOR ATTACH commands. The following cursor relies on the rn column we added and builds commands that accurately handle multiple data files, multiple log files, or both.

DECLARE @ln SYSNAME, @rn INT, @ubound INT, @cmd NVARCHAR(MAX);

DECLARE c CURSOR LOCAL FAST_FORWARD
 FOR SELECT ln, rn FROM #x WHERE [path] IS NULL;

OPEN c;

FETCH c INTO @ln, @rn;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @ubound = MIN(rn) FROM #x WHERE [path] IS NULL AND rn > @rn;

  SET @ubound = COALESCE(@ubound, 2000000000);

  SELECT @cmd = N'CREATE DATABASE ' + QUOTENAME(@ln) + '
    ON '
    + STUFF((SELECT ',' + '(NAME = ''' + ln + ''',FILENAME=''' 
      + [path] + ''')' FROM #x WHERE rn > @rn AND rn < @ubound 
      AND [Type] = 'D' ORDER BY FileId 
      FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'),1,1,'')
    + ' 
    LOG ON 
    ' + STUFF((SELECT ',' + '(NAME = ''' + ln + ''',FILENAME=''' 
      + [path] + ''')' FROM #x WHERE rn > @rn AND rn < @ubound 
      AND [Type] = 'L' ORDER BY FileId 
      FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'),1,1,'')
    + '
      FOR ATTACH;';

  PRINT @cmd;

  FETCH c INTO @ln, @rn;
END

CLOSE c;
DEALLOCATE c;

This will print a bunch of individual CREATE DATABASE ... FOR ATTACH commands. However they will only be as reliable as (a) the data in msdb..backupset, (b) the presence of the actual backup files, (c) how accurate the backup files are in terms of current database file structures, and (d) your re-mapping of old paths -> new paths.

share|improve this answer

Based on Mike and Aaron's ideas, I created a short script to get the filenames:

RemoteAccess.NetUse("10.20.1.100");
var files = (new System.IO.DirectoryInfo(@"\\10.20.1.100\SQLDataBackup"))
    .GetDirectories("B3DB*")
    .Where(d=>!d.Name.EndsWith("21"))
    .SelectMany(d => d.GetFiles("*.bak")).ToList();
var filenames = files.
    ToDictionary(f=> f.Name.Slice(null, -4), 
        bf=> Database.Central().
            WithQueryOrProcedure("RESTORE FILELISTONLY FROM DISK='{0}'".Fmt(bf.FullName)).
            WithResult<DataTable>().Execute().AsEnumerable().
            Select(r=> r.Field<string>("PhysicalName")).
            Select(df => (new System.IO.FileInfo(df)).Name));

Then I found out I can't do this in the DR environment since the backups are not available in DR. So I will need another way to get list of files.

share|improve this answer
    
Not a 10-minute job, is it? :-) –  Aaron Bertrand Aug 14 '13 at 19:41
    
@AaronBertrand Nope. I was only talking about the final part where after I get all the files necessary, to generate "Create database for attach...". –  John Tseng Aug 14 '13 at 20:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.