Moving Databases from One Instance to Another Painlessly (Attach Statement Generator)

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. Smile

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

Advertisements
This entry was posted in SQL Server, SQL Tips and Techniques and tagged . Bookmark the permalink.

2 Responses to Moving Databases from One Instance to Another Painlessly (Attach Statement Generator)

  1. Mark Polczynski says:

    Is there any preparations suggested/required, such as Database or File Shrinking?

    • Bob L says:

      Sorry for the slow reply, somehow my notification for your post got buried. No prep needed, database shrinking is not normally recommended unless it is followed by defragmentation of indexes.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s