Introduction

Attaching databases in SQL Server can be a routine yet sometimes time-consuming task for Database Administrators (DBAs). If you're dealing with just a few databases, the process using SQL Server Management Studio (SSMS) is straightforward and efficient. However, when you need to attach multiple databases, especially in bulk, the manual process can become tedious and error-prone. Additionally, if you're working with detached databases that need to be attached to a new or existing server, finding an efficient way to automate the task is essential.

In this article, we will explore how to leverage a powerful but lesser-known SQL Server command, DBCC CHECKPRIMARYFILE, to quickly gather the necessary metadata from database files and use that information to attach multiple databases with minimal effort. This approach not only saves time but also reduces the need for complex scripting or manual intervention.

The Challenge

If you've ever had to attach databases in SQL Server, you know it can be a time-consuming task. While SQL Server Management Studio (SSMS) provides a convenient GUI for attaching databases, this method can become inefficient when dealing with multiple databases at once. Alternatively, if you’re working with detached databases that need to be attached to a new or existing server, manual steps or complex T-SQL code can be required. In this article, I’ll show you how to speed up the process using a lesser-known but powerful SQL Server command, DBCC CHECKPRIMARYFILE.

The Solution

DBCC CHECKPRIMARYFILE is an undocumented command in SQL Server, which means it should be used with caution. However, it’s not dangerous and can be incredibly useful when you need to retrieve important information about a database file, such as its name, logical names, file paths, and more. This can be done without needing to actually attach the database to an instance of SQL Server.

In this section, I’ll walk you through the syntax and functionality of DBCC CHECKPRIMARYFILE.

Syntax Overview:

This is the syntax for the command.

DBCC CHECKPRIMARYFILE ({'PhysicalFileName'} [,opt={0|1|2|3}])

• PhysicalFileName: Full path of the primary database file.
• opt=0: Verifies if the file is the primary database file.
• opt=1: Returns file details like name, size, max size, status, and path.
• opt=2: Retrieves database name, version, and collation.
• opt=3: Lists the logical names, status, and paths of all database files.

Using DBCC CHECKPRIMARYFILE for Verification

Let’s go through each option to see how we can use DBCC CHECKPRIMARYFILE.

Option 0 – Verify Primary Database File - Use this option to determine if a given file is the primary database file. It returns 1 if the file is primary, or 0 if it is not.

DBCC CHECKPRIMARYFILE('D:\SQLData\AdventureWorksDW2012_Data.mdf', 0) WITH NO_INFOMSGS;

Since option 0 is the default, you can omit the ,0 and just run.

DBCC CHECKPRIMARYFILE('D:\SQLData\AdventureWorksDW2012_Data.mdf') WITH NO_INFOMSGS;

Option 1 – Retrieve File Details - This option gives you detailed information about the database file, including the logical name, size, max size, status, and physical file name.

DBCC CHECKPRIMARYFILE('D:\SQLData\AdventureWorksDW2012_Data.mdf', 1) WITH NO_INFOMSGS;

Option 2 – Get Database Information - Use option 2 to fetch the database’s name, version, and collation.

DBCC CHECKPRIMARYFILE('D:\SQLData\AdventureWorksDW2012_Data.mdf', 2) WITH NO_INFOMSGS;

Option 3 – View All File Details - Option 3 returns the logical names, status, and physical paths of all files associated with the database.

DBCC CHECKPRIMARYFILE('D:\SQLData\AdventureWorksDW2012_Data.mdf', 3) WITH NO_INFOMSGS;

Automating Database Attachment

Now that you understand how to retrieve key file information, you can leverage DBCC CHECKPRIMARYFILE to automate the process of attaching multiple databases. By combining the output of this command with CREATE DATABASE statements, you can create a script that attaches databases quickly and efficiently.

To start, you'll need the physical file paths for all your databases. One way to obtain this is by using the bcp utility to export the paths into a text file.

Step 1: Export Physical File Paths

Run the following T-SQL to export the physical file names into a text file. This requires enabling advanced options and xp_cmdshell:

exec sp_configure 'show advanced options', 1;
go
reconfigure with override;
go
exec sp_configure 'xp_cmdshell', 1;
go
reconfigure with override;
go
exec xp_cmdshell 'mkdir c:\temp';
go
exec xp_cmdshell 'bcp "select rtrim(physical_name) from sys.master_files where file_id=1 and db_name(database_id) not in (''master'', ''model'', ''tempdb'', ''distribution'', ''msdb'')" queryout "C:\temp\MasterDataFiles.txt" -T -c';
go

set nocount on;
declare @TSQLFileName varchar(max);
declare @TSQLCreateDatabase varchar(max);
declare @DatabaseName varchar(512);
declare @LogicalFileName varchar(4000);
declare @PhysicalFileName varchar(4000);
create table #MasterDataFiles (PhysicalFileName varchar(4000));
-- Load file paths into the temporary table
bulk insert dbo.#MasterDataFiles from 'c:\temp\MasterDataFiles.txt'
with (rowterminator ='\n');
-- Iterate over each database file
declare CursorPhysicalFileName cursor for 
select PhysicalFileName from #MasterDataFiles;
open CursorPhysicalFileName;
fetch next from CursorPhysicalFileName into @PhysicalFileName;
while (@@FETCH_STATUS=0)
begin 
  set @TSQLCreateDatabase = '';
  create table #db (property sql_variant, value sql_variant);
  create table #dbfiles(status int, fileid int, name varchar(100), filename varchar(512));
  -- Execute DBCC commands to get database name and files
  insert into #db exec ('DBCC CHECKPRIMARYFILE (''' + @PhysicalFileName + ''', 2)');
  insert into #dbfiles exec ('DBCC CHECKPRIMARYFILE (''' + @PhysicalFileName + ''', 3)');
  select @DatabaseName = cast(value as varchar(512)) from #db where property='Database Name';
  -- Generate CREATE DATABASE FOR ATTACH code
  declare CursorFile cursor for 
  select CAST([filename] AS VARCHAR(MAX)) from #dbfiles;
  
  open CursorFile;
  fetch next from CursorFile into @LogicalFileName;
  while (@@FETCH_STATUS=0)
  begin
    set @TSQLFileName = @TSQLFileName + '(FILENAME=N''' + LTRIM(RTRIM(@LogicalFileName)) + '''),' + char(13);
    fetch next from CursorFile into @LogicalFileName;
  end
  close CursorFile;
  deallocate CursorFile;
  set @TSQLCreateDatabase = 'CREATE DATABASE ' + @DatabaseName + ' ON ' + char(13) + 
                            SUBSTRING(@TSQLFileName, 1, LEN(@TSQLFileName) - 2) + CHAR(13) + 'FOR ATTACH;';
  
  print @TSQLCreateDatabase;
  drop table #dbfiles;
  drop table #db;
  fetch next from CursorPhysicalFileName into @PhysicalFileName;
end;
close CursorPhysicalFileName;
deallocate CursorPhysicalFileName;
drop table #MasterDataFiles;
set nocount off;