By: Percy Reyes | Updated: 2014-09-23 | Comments (18) | Related: 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
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
DBCC CHECKPRIMARYFILE with Option = 2
Checks the database name, version and collation.
DBCC CHECKPRIMARYFILE('D:\SQLData\AdventureWorksDW2012_Data.mdf',2) WITH NO_INFOMSGS
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
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.
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 ('''+@PhysicalFileName+''', 2)'
set @TSQLDBCCPPRIMARYFILE_getdbfiles='DBCC CHECKPRIMARYFILE ('''+@PhysicalFileName+''', 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 @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
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.
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
- Read these additional articles related to DBCC statements
About the author
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.
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2014-09-23
