Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to use DBCC CHECKPRIMARYFILE to attach several SQL Server databases in seconds


By:   |   Last Updated: 2014-09-23   |   Comments (15)   |   Related Tips: More > Database Administration

Problem

Have you ever had to attach databases on a SQL Server?  This could be done using the SQL Server Management Studio (SSMS) GUI which is great if it is a one time need, but if you have many databases this could take time.  The other option is to write T-SQL code to do the attach, but this also takes time to write for each database.  Or have you ever run into the situation where you have detached databases that need to be attached to a new server or an existing server?  In this tip I share a way to do this job fast without the need to have to do this manually or write T-SQL code.

Solution

DBCC CHECKPRIMARYFILE is one of the many undocumented SQL Server commands that we can use to do our job as DBAs. Because it is an "undocumented command" we should use caution, but fortunately this is not a dangerous command. This command is used only for reading meta data from the primary database data file on disk and to return information that is very useful, such as the database name, logical names, physical file names and more without having to attach it to an instance of SQL Server.

First of all, I will show the syntax and how to use DBCC CHECKPRIMARYFILE.

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

PhysicalFileName is the full path for the primary database file.

opt=0 - checks if the file a primary database file. 
opt=1 - returns name, size, maxsize, status and path of all files associated to the database.
opt=2 - returns the database name, version and collation.
opt=3 - returns name, status and path of all files associated with the database.

SQL Server DBCC CHECKPRIMARYFILE with Option = 0

First we will use it with zero to check if it is the primary file, if so it will return 1 otherwise 0.

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

How to use DBCC CHECKPRIMARYFILE to attach several databases in just a few seconds

Note that the option 0 is the default value, so you don't need to use a value if you only want to check if it is the primary file or not.

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

DBCC CHECKPRIMARYFILE with Option = 1

To check logical name, size, maxsize, status and physical name of all files we use option 1.

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

How to use DBCC CHECKPRIMARYFILE to attach several databases in just a few seconds

DBCC CHECKPRIMARYFILE with Option = 2

Checks the database name, version and collation.

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

How to use DBCC CHECKPRIMARYFILE to attach several databases in just a few seconds

DBCC CHECKPRIMARYFILE with Option = 3

Checks the logical name, status and path of all files.

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

How to use DBCC CHECKPRIMARYFILE to attach several databases in just a few seconds

Use DBCC CHECKPRIMARYFILE Attach SQL Server Database Files

With all of this info in our hands, we can use this to provide a solution to the problem explained at the beginning of this tip.  I will make use of this undocumented command, DBCC CHECKPRIMARYFILE, and combine it with CREATE DATABASE commands to generate a CREATE DATABASE FOR ATTACH statement for all databases we want to put on-line without having to write T-SQL code to do this.

We know that DBCC CHECKPRIMARYFILE needs as an input the physical primary file name, so it important to have this information somewhere. In this case, I will get the physical primary file names into a text file by using the bcp utility as follows:

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

Here is what the file looks like after running this.  You could also manually create this data or run a process periodically from your server that gets you this information, so you have it when needed.

list of primary sql server database files

The below code with use the DBCC CHECKPRIMARYFILE command via a cursor to generate the CREATE DATABASE FOR ATTACH code for each database from the list. The following code is easy and self-explanatory by itself.

set nocount on 

declare @TSQLFileName varchar(max)
declare @TSQLCreateDatabase varchar(max)
declare @DatabaseName varchar(512)
declare @LogicalFileName varchar(4000)
declare @PhysicalFileName varchar(4000)
declare @TSQLDBCCPPRIMARYFILE_getdbname varchar(4000)
declare @TSQLDBCCPPRIMARYFILE_getdbfiles varchar(4000)

create table #MasterDataFiles (PhysicalFileName varchar(4000))

--saving physical file names into a txt file 
bulk insert dbo.#masterdatafiles from 'c:\temp\masterdatafiles.txt'
with (rowterminator ='\n');

--reading the database name and all physical file names of each database
declare CursorPhysicalFileName cursor for 
select PhysicalFileName 
from #MasterDataFiles

open CursorPhysicalFileName 

fetch next from CursorPhysicalFileName into @PhysicalFileName

while (@@FETCH_STATUS=0)
begin 
  SET @LogicalFileName=''
  set @TSQLCreateDatabase=''
  set @TSQLFileName=''

  create table #db (property sql_variant, value sql_variant)
  create table #dbfiles(status int, fileid int, name varchar(100), filename varchar(512))

  set @TSQLDBCCPPRIMARYFILE_getdbname='DBCC CHECKPRIMARYFILE ('''[email protected]+''', 2)'
  set @TSQLDBCCPPRIMARYFILE_getdbfiles='DBCC CHECKPRIMARYFILE ('''[email protected]+''', 3)'

  insert into #db exec (@TSQLDBCCPPRIMARYFILE_getdbname)
  insert into #dbfiles exec (@TSQLDBCCPPRIMARYFILE_getdbfiles)

  select @DatabaseName = cast(value as varchar(512))from #db where property='Database Name'

  -- Now we will create the CREATE DATABASE FOR ATTACH code dynamically
  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 @[email protected] + '(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

As you can see the output is as follows, which can then be copied and pasted into a new query window to attach all the databases in a few seconds.

How to use DBCC CHECKPRIMARYFILE to attach several databases in just a few seconds

Note that the use of DBCC undocumented commands must be done with extreme caution and first tested in non-production environments.  Many of them can be dangerous and affect your database server if you don't really know how they work.

Next Steps


Last Updated: 2014-09-23


get scripts

next tip button



About the author
MSSQLTips author Percy Reyes Percy Reyes is a SQL Server MVP and Sr. Database Administrator focused on SQL Server Internals with over 10+ years of extensive experience managing critical database servers.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, October 14, 2014 - 4:38:31 PM - Jeremy H Back To Top

Percy - I see. It looks like if you have more than one data file within the primary filegroup it will produce incorrect results. I only have one file in each of the primary filegroups for my databases, so this works in my shop. With a little modification it could handle most peoples needs.

The point I was trying to make is that it seems like a very roundabout way to create the attach statements when you can just pull the script from the master_files table, however, your method is a very creative way to attach groups of databases!


Tuesday, October 14, 2014 - 2:37:12 PM - Percy Reyes Back To Top

Hi Jeremy H - Yes, I was run your code. It work good with database with only one data file, but not with databases with more than one data file

For example,

create the following database:

CREATE DATABASE [midb]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'midb', FILENAME = N'D:\SQLData\midb.mdf' , SIZE = 524288KB , MAXSIZE = UNLIMITED, FILEGROWTH = 524288KB ),
( NAME = N'midb_file2', FILENAME = N'D:\SQLData\midb_file2.ndf' , SIZE = 524288KB , MAXSIZE = UNLIMITED, FILEGROWTH = 524288KB )
 LOG ON
( NAME = N'midb_log', FILENAME = N'E:\SQLLog\midb_log.ldf' , SIZE = 131072KB , MAXSIZE = 2048GB , FILEGROWTH = 524288KB )
GO

for a database with two data files, this is the code generated by your script

CREATE DATABASE midb
ON (FILENAME = N'D:\SQLData\midb.mdf')
CREATE DATABASE midb
ON (FILENAME = N'D:\SQLData\midb_file2.ndf')
LOG ON (FILENAME = N'E:\SQLLog\midb_log.ldf') FOR ATTACH

as you can verify it fails. I think you must modify your code to take account just the data master file mdf or group the code generation by database.

I hope it helps!

Thanks for your comments! It is very appreciated!

 

 

 

 

 

 

 

 

 

 


Tuesday, October 14, 2014 - 2:07:27 PM - Greg Robidoux Back To Top

I think the script Jeremy H provided makes sense if all of the databases are attached to the SQL instance and you can get this data out of the master database.  The DBCC method is helpful if you just have the database files and they are not currently attached to a SQL instance.

-Greg


Tuesday, October 14, 2014 - 2:02:04 PM - Jeremy H Back To Top

Percy - perhaps I am not understanding what you mean by "how do you group them to generate the code by database?" It produces the same code as in your final picture above. Have you even tried running it to see what it produces?


Tuesday, October 14, 2014 - 11:39:37 AM - Percy Reyes Back To Top

Hi Jeremy H - I understand what you mean, but how do you group them to generate the code by database? I think you need some way to do it.

Thanks for your comments!


Tuesday, October 07, 2014 - 11:19:30 AM - Jeremy H Back To Top

Percy - sys.master_files contains all database files. I've used this to create the attach script for over 250 databases. This won't work for all cases, such as restoring an encrypted database to another instance, but for regular old DBs this creates the appropriate attach script for an entire instance. Try it out on a dev instances with more than one database.


Monday, October 06, 2014 - 8:40:50 PM - Percy Reyes Back To Top

Hi Jeremy H - Your code is valid and good, but it will work only for one specific database. You need any way to group by database and generate it dynamically for each database. My example is just one valid way how to take advantage of this DBCC command to attach all databases from one server. Have a great day!

Let me know if you have any concerns or comments or more code! great!


Monday, October 06, 2014 - 6:04:29 PM - Jeremy H Back To Top

I must be missing something. Is there something that DBCC CHECKPRIMARYFILE is providing that you can't get from sys.master_files (without accessing the file system or using a cursor)? Why not just use code like this:

SELECT 

CASE type_desc

WHEN 'ROWS' THEN 

CASE data_space_id

WHEN 1 THEN 'CREATE DATABASE ' + DB_Name(database_id) + CHAR(13)+CHAR(10) + 'ON (FILENAME = N''' + physical_name + ''')'

ELSE '  ,(FILENAME = N''' + physical_name + ''')'

END

WHEN 'LOG' THEN 'LOG ON (FILENAME = N''' + physical_name + ''') FOR ATTACH' + CHAR(13)+CHAR(10) + 'GO' + CHAR(13)+CHAR(10)

END

FROM sys.master_files

WHERE database_id NOT IN (1, 2, 3, 4)

ORDER BY database_id, [type], data_space_id

 

 


Wednesday, September 24, 2014 - 2:21:20 PM - Jeremy Kadlec Back To Top

Everyone,

Thank you for the feedback.  This tip has been updated.

Thank you,
Jeremy Kadlec
MSSQLTips.com Co-Leader


Wednesday, September 24, 2014 - 7:00:54 AM - Joao Noronha Back To Top
Thanks for sharing.
I made some improvements:
 
In your code is a space missing between "CursorPhysicalFileName" and "fetch":
open CursorPhysicalFileName fetch next from CursorPhysicalFileName into @PhysicalFileName
 
I add exec command to avoid copying the result text and execute:
  print @TSQLCreateDatabase
--add exec command  
  exec (@TSQLCreateDatabase)
 

Wednesday, September 24, 2014 - 5:30:51 AM - Reuben Back To Top

That's a really nice time-saver in DR scenarios! Handy script to keep in the DBA toolbox. 

 


Wednesday, September 24, 2014 - 5:00:21 AM - Paul H Back To Top

A novel approach, very interesting and useful. Thanks for posting.


Tuesday, September 23, 2014 - 12:33:05 PM - Renato Back To Top

Very interesting. I don't knew this DBCC, and that's a nice strategy for mass attach.

Thanks for sharing.


Tuesday, September 23, 2014 - 10:08:06 AM - Armando Prato Back To Top

I learned something today... Thanks for sharing!


Tuesday, September 23, 2014 - 9:50:39 AM - Rick Willemain Back To Top

Thank you !  I very much appreciate this effort and insight.

Very nicely presented and could be very useful.


Learn more about SQL Server tools