BCP: Export en SQL Server de todas las tablas de una base de datos

Hola a todos,

hoy nos gustaría compartir con vosotros una forma de realizar export de los datos en SQL Server utilizando la herramienta «bcp«. Como ya conocéis, la herramienta de backup de SQL Server realiza una copia física de los datos de una forma equivalente al RMAN de Oracle, por lo que recuperar objetos de una forma individual se hace muy complicado, sobre todo para bases de datos muy grandes.

Con el script que os vamos a poner de ejemplo realizamos un volcado de cada una de las tablas de una base de datos de forma individual, simplificando el proceso de recuperación en caso de necesitar los datos de una sola tabla. Para ellos se va a automatizar el proceso de volcado mediante bcp sacando el nombre de las tablas de forma dinámica.

Script completo:

CREATE PROCEDURE bcp_tables 
 as
 SET DATEFORMAT mdy

DECLARE
 @table VARCHAR(128),
 @filename VARCHAR(128),
 @begindate DATETIME,
 @sql VARCHAR(500),
 @sqldir VARCHAR(500)
 --dDateAdd(interval, number, date)
 SET @begindate = DATEADD(d,-1 ,CONVERT(VARCHAR,GETDATE(),101)) 
 --obtenemos el nombre de las tablas con el cursor
 DECLARE c1 CURSOR FOR 
 SELECT 
 DISTINCT sysobjects.name
 FROM sysobjects
 INNER JOIN syscolumns ON sysobjects.id=syscolumns.id
 INNER JOIN syscolumns dbl ON sysobjects.id=dbl.id
 WHERE sysobjects.xtype = 'U'

--creamos un directorio para cada dia
 SET @sqldir='mkdir G:\export\' + CONVERT(VARCHAR,@begindate,112)
 EXEC xp_CmdShell @sqldir, NO_OUTPUT

--recorremos las tablas
 OPEN c1
 FETCH next FROM c1 INTO @table
 WHILE @@fetch_status <> -1
 BEGIN

 --creamos el nombre del fichero utilizando el nombre de la tabla y la fecha, como tabla1_20170908.txt
 SET @filename = 'G:\export\' + CONVERT(VARCHAR,@begindate,112) + '\' + @table + '_' + CONVERT(VARCHAR,@begindate,112) + '.txt' 
 SET @sql = 'SELECT * FROM [' + @table + ']'
 --lanzamos el bcp con los parametros anteriores pasando el usuario, contraseña y base de datos
 SET @sql = 'bcp "' + @sql + '" queryout "' + @filename + '" -c -Uexport -Ptemporal -S127.0.0.1 -d bd1'
 
--sacamos el comando para ver que es correcto
 PRINT @sql

 --lo ejecutamos
 EXEC xp_CmdShell @sql, NO_OUTPUT

--sacamos la siguiente tabla
 FETCH next FROM c1 INTO @table
 END
 CLOSE c1
 DEALLOCATE c1
 go

El nombre de la base de datos la dejamos fija en este ejemplo, pero se puede modificar el procedimiento para pasarlo por parámetro y poder reutilizarse en varias de ellas.

Os recordamos que para poder lanzar comandos de sistema operativo con xp_cmdshell necesitamos habilitarlo a nivel de instancia.

Con estos sencillos pasos tenemos un volcado a texto de todas las tablas de una base de datos. Si queremos generar también la DDL de las tablas para tener el script de creación hay que hacer una pequeña modificación al script, pero eso ya os lo contamos en otro post :-D.

Un saludo y esperamos que os sea de utilidad.

Equipo de base de datos.

Deja una respuesta