Grimpi IT Blog

septiembre 14, 2008

Como hacer un Attach dinámicamente de un conjunto de MDF a una instancia de SQL Server

Filed under: SQL Server — Etiquetas: , , — grimpi @ 10:30 pm

Recientemente tuve la necesidad de attach a una instancia de SQL Server, más de 200 bases de datos. Tenía los archivos .mdf (Datos) y .ldf (Transaction log) de cada una de las 200 bases.
Por supuesto que hacer esta operación manualmente es algo bastante engorroso. Por lo cual, investigué un poco como hacer esta operación dinámicamente. O sea, hacer un script que lea todos los archivos .mdf de un directorio y haga el attach de cada uno de los mdf a la instancia del SQL Server, obviamente con su respectivo archivo de transaction log.

Paso 1:
Lo primero que necesitamos es tener un listado de todos los archivos .mdf que se encuentran dentro de un directorio y que deseamos hacer el attach.
Existe en SQL Server 2005, un store procedure extendido no documentado, que se llama xp_dirtree, que devuelve todos los archivos existentes dentro de una carpeta.
Para capturar la info de este store, almacenamos dentro de una tabla temporal el resultado de la consulta:

declare @Path nvarchar(255)
select @Path = N‘C:\Data\’
create table #filetmp (Name nvarchar(255) NOT NULL, depth int NOT NULL, IsFile bit NULL)
insert #filetmp EXECUTE master.dbo.xp_dirtree @Path, 1, 1

Paso 2:
Necesitamos saber a partir del .mdf, cual es el nombre de la base de datos que tenemos que crear. Por ejemplo, un archivo puede llamarse Test1.mdf, sin embargo, lógicamente, el nombre de la base que contiene es distinto. Para averiguar este dato, debemos usar el comando DBCC no documentado de Microsoft, CheckPrimaryFile.

DBCC CHECKPRIMARYFILE (‘C:\Data\ISMM2_Data.MDF’ , 2)

(El primer parámetro es el path completo y el nombre del archivo mdf que se desea tener información, el segundo parámetro, indica que tipo de información queremos extraer)

El resultado de esta consulta es el siguiente:



Como podemos observar, este comando nos devuelve un listado de propiedades del .mdf, donde la primer propiedad, Database name, es el nombre lógico de la base de datos. Debemos capturar este valor para usarlo más adelante.


Paso 3:
Luego, debemos obtener todos los archivos dependientes de la base. Por ejemplo, una base de datos puede tener además obviamente del .mdf, otros archivos secundarios, como por ejemplo el archivo .ldf (transaction log) y en caso de que tenga más de un filegroup, sus respectivos archivos .ndf.
Para averiguar esta información, nuevamente debemos usar el comando CHECKPRIMARYFILE, pero el segundo parámetro ahora debe ser un 3.

DBCC CHECKPRIMARYFILE (‘C:\Data\ISMM2_Data.MDF’ , 3)

El resultado de esta consulta es el siguiente:



En este caso, la instrucción devuelve el nombre físico y lógico de todos los archivos dependientes de la base de datos. Debemos almacenar el valor del campo filename dentro de una tabla temporal (ver el script de ejemplo para ver como se hace).


Paso 4:
Ok, ya tenemos toda la info que necesitamos para hacer el attach dinámico de todas la base de datos a la instancia del SQL Server, pero como se hace vía script un attach a partir de un mdf en SQL Server? Bueno, existen 2 alternativas. Una es usando la instrucción CREATE DATABASE … FOR ATTACH y la otra es usando el Store Procedure sp_attach.
Personalmente encuentro mucho más claro el primer método.

Ejemplo de uso:

CREATE DATABASE ISMM ON
(FILENAME = N‘C:\Data\ISMM_Data.mdf’),
(FILENAME = N‘C:\Data\ISMM_log.ldf’) FOR ATTACH

Paso 5:
Ahora viene lo más difícil. Tenemos toda la info sobre las bases de datos y sabemos cómo hacer un attach individual de un mdf. Pero ahora tenemos que juntar toda la información y generar un script que haga el attach de cada una de las bases con todos sus archivos dependientes.
Copiar este script en la web, sería poco claro de entender. Por lo tanto, dejo a disposición de quien ande necesitando algo similar, un script genérico, para efectuar el attach dinámico, usando los 4 pasos anteriores.
Lo pueden bajar desde acá: http://opendbdiff.com/AttachMasivo.txt

Cualquier comentario, duda, mejora u error, no duden en informar. Espero que les sea útil. A mí me ahorro bastante tiempo.

9 comentarios »

  1. 200 bases de datos en la misma instancia? que tipo de software es eso? es para un ISP?

    Comentario por vqp — septiembre 30, 2008 @ 8:00 pm

  2. En realidad es un sistema de comunicacion que tiene una base de datos por pais, la cantidad de datos que tiene es simplemente enorme, y se destina un servidor por pais (o sea 200 servidores).
    Pero estamos estudiando implementar un cluster de servidores con una sola instancia para ver como se comporta.

    Comentario por Esteban — septiembre 30, 2008 @ 8:13 pm

    • Hola no se si podrias volver a subir el archivo, Gracias….

      Comentario por Claudia Hernandez — noviembre 11, 2011 @ 2:52 pm

  3. Hola, nose si podrías volver a subir el archivo donde está el Attach dinámico.

    Gracias y Saludos!!!

    Comentario por Amadeo — octubre 6, 2011 @ 12:06 am

  4. Student consolidate credit debt can ease the pressure of clearing them gets beyond what is easily manageable, decisive action
    is needed. A SBA 7a Term Loans should meet the size and
    type criteria, demonstrate the ability to get counseling or to buy your payment appointed to get a Consolidate Credit Debt
    agency. By doing this, you only have to pay their loans of credit cards are the major
    areas of debt for each person. Remember to read in between lines while taking the loan amount.

    Comentario por http://friendsite.com/QuentinLedbet/blog/54091/Queries+To+Consider++Practical+Guidelines+For+Elementary+Consolidate+Payday+Loans+Programs.html — junio 8, 2013 @ 11:11 pm

  5. zobacz

    Como hacer un Attach dinámicamente de un conjunto de MDF a una instancia de SQL Server | Grimpi IT Blog

    Trackback por zobacz — octubre 13, 2014 @ 12:58 am

  6. a great deal more

    Como hacer un Attach dinámicamente de un conjunto de MDF a una instancia de SQL Server | Grimpi IT Blog

    Trackback por a great deal more — noviembre 7, 2014 @ 1:17 pm

  7. odwiedź

    Como hacer un Attach dinámicamente de un conjunto de MDF a una instancia de SQL Server | Grimpi IT Blog

    Trackback por odwiedź — noviembre 19, 2014 @ 12:12 am

  8. el comando de attach también es valido para la versión 2012 y español o afecta el idioma
    por que tengo SQL server en español y me marca error

    Comentario por jason — abril 15, 2015 @ 12:54 am


RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Crea un blog o un sitio web gratuitos con WordPress.com.

A %d blogueros les gusta esto: