SQL Server - T-SQL script to attach database with multiple files

Last week I was working on one of our regular database restore tasks and as a part of it, I was also required to detach and attach the database (with around 100 additional data files) on different SQL server instance. 

Now, if your source and destination servers have same disk partition and folder hierarchy, than attaching a database is quite a straight job. All you need to do is just copy all the database files from source server to same named folder and under same folder hierarchy on the destination server and then attach the database using the SSMS or T-SQL statement pointing to the primary data (mdf) file only and you're done. SQL Server will take care of listing and attaching the remaining files.

But the tricky part comes in when destination server doesn't have same (name of the) disk partition or can't use the same folder/database file path (like may be same disk partition on the destination server doesn't have enough space to host that database). In that case using SSMS can become cumbersome if you have too many files (like in my case) as you have to manually point each file to new path. In such cases T-SQL comes as blessing. The following query is a simpler version but will return the resultant query in a single line:

--use <your database>
declare @cmd nvarchar(max)

set @cmd = 'CREATE DATABASE [DB1] ON '

select @cmd = @cmd + '( FILENAME = N'''+ replace(filename,'D:\SQL Server\DB Engine\Data\','F:\SQL Server\Data\') + ''' ),' from sys.sysfiles

select @cmd = LEFT(@cmd,len(@cmd)-1)

select @cmd = @cmd + ' For Attach'
select @cmd

In case you don't like your query in single line (or any other case), here is the following query which will return the same result in different fashion:

--use <your database>

declare @cmd nvarchar(max)='', @filename nvarchar(255), @rc int, @inc int 

set @rc = 1

set @inc = 1

set @cmd = 'CREATE DATABASE [DB1] ON '

print @cmd

select @rc = COUNT(1) from sys.sysfiles

declare cur_1 cursor for

select filename from sys.sysfiles

open cur_1

fetch next from cur_1 into @filename

while @@FETCH_STATUS = 0

begin

select @cmd =  '( FILENAME = N'''+ replace(@filename,'D:\SQL Server\DB Engine\Data\','F:\SQL Server\Data\') + ''' ),' 

if (@inc = @rc)

set @cmd = LEFT(@cmd,len(@cmd)-1)

print @cmd

set @inc = @inc + 1

fetch next from cur_1 into @filename

end

select @cmd = ' For Attach'

print @cmd

Using "case" function with "replace" is helpful in case you have several database file folder and want to map it to different destination folder(s).

In case you don't have database with such huge number of files to play with, you can use the following query to create one and do the above experiment.

use master

declare @a int , @cmd nvarchar(max)=''

set @a = 1

set @cmd = 'CREATE DATABASE [DB1] ON  PRIMARY '

print @cmd

while @a < 101

begin

select @cmd =  '( NAME = N''DB1_Data_File_'+ cast(@a as varchar) + ''', FILENAME = N''D:\SQL Server\DB Engine\Data\DB1_Data_File_'+ cast(@a as varchar) + '.mdf'' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),'

set @a = @a + 1

if (@a = 101)

begin

set @cmd = LEFT(@cmd,len(@cmd)-1)

print @cmd

end

else

print @cmd

end

select @cmd = '

 LOG ON 

( NAME = N''DB1_log'', FILENAME = N''D:\SQL Server\DB Engine\Data\DB1_log.ldf'' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO'

print @cmd