If you’ve ever had a bunch of databases and need to just move them to another instance without having to manually do the attach, these two scripts may be helpful. I recently was faced with this situation and have learned from experience that the time it takes to script something involving more than a few configuration actions is worth it, not just to ensure reliability for the task at hand, but for the likelihood that I will need it again. The script is especially useful if you have lots of files. It just generates the SQL so you can edit. You will probably want to edit for most scenarios to specify the new locations, but that is a editor-processing task to do global search/replace, etc.

There are two scripts involved. One does the meat of the work and generates the concatenation of T-SQL file clauses using a cursor to enumerate the filenames associated with the database. The other simply calls the attachment scalar function and wraps it with the statements needed to actually create the database and specify the attach option.

Below are the two scripts. They work for my purposes, but I do not warranty them.

create function dbo.util_GenerateAttach (@dbid int)
    returns nvarchar(max)
as begin
    /*    Robert Leithiser, Authentic Intelligence 2013-11-23 – Generates the attach statement
        to move over a database. Run this as follows to generate attach statements for all databases
        on an instance:
    
        select ‘create database ‘ + d.name, dbo.util_GenerateAttach(d.dbid) + ‘ for attach;’
        from sys.sysdatabases  d where d.sid <> 01
    */
    declare @filestring nvarchar(max) = ”
    declare x cursor for select ‘(filename = N”’ + a.filename + ”’)’ as filename
    from sys.sysaltfiles a
    where a.dbid = @dbid
    open x
    fetch from x into @filestring
    while @@FETCH_STATUS = 0
    begin
        declare @filename nvarchar(max)
        fetch from x into @filename
        set @filestring = @filestring + ‘,’ + @filename
    end
    close x
    deallocate x
    return @filestring
end
go

create function dbo.util_tdf_GenerateAllAttachStatements ()
    returns table
    /*    Robert Leithiser, Authentic Intelligence 2013-11-23 – Generates the attach statement
        to move over a database. Run this as follows to generate attach statements for all datbases
        on an instance

        select * from dbo.util_tdf_GenerateAllAttachStatements()

    */
    as return select ‘create database ‘ + d.name + ‘ on ‘
     + dbo.util_GenerateAttach(d.dbid) + ‘ for attach;’ as AttachStatement
    from sys.sysdatabases  d
    where sid <> 01
go