Este equipo redactor ha estado mucho tiempo investigando para darle espuestas a tu búsqueda, te ofrecemos la respuesta de modo que nuestro objetivo es que te sea de mucha ayuda.
Solución:
La siguiente no es una buena solución de respaldo. A menos que esté renunciando a su trabajo y tenga ganas de hacerle una broma a su jefe al salir, no debe usarlo. Pero aprendí algunas cosas y me divertí escribiéndolas, espero que otras personas también lo hagan. Vamos a utilizar algunos conceptos de ETL que podrían resultar útiles en otras circunstancias. Los guiones completos están al final. Esto es lo que vamos a hacer:
Escribe el esquema en su propia tabla
Escriba cada tabla de la base de datos en su propio archivo de texto con nombre dinámico
Vuelva a crear el esquema en una nueva base de datos desde el archivo de texto
Recreación e inserción masiva de cada tabla desde su archivo de texto
Antes de comenzar: para hacer toda la base de datos, necesitará varios cientos de GB de almacenamiento. Si eres demasiado cobarde, puedes agregar ‘top 1000’s en el script para limitar el tamaño de los archivos .txt, entonces no tendrás que explicarle a tu jefe cómo llenaste C:
Habilite xp_cmdshell, explicado aquí:
- Escribe el esquema en un archivo de texto
Vamos a comprobar y ver si la tabla ya existe (si hemos ejecutado el script antes). Es más fácil dejar caer la mesa cada vez.
if exists (select name from sys.tables st with (Nolock) where name = 'HeaderTable')
begin
drop table HeaderTable
end
SELECT
st.name, sc.name 'Column_Name', t.Name 'Data_type',sc.max_length 'Max_Length',
sc.precision, sc.scale, sc.is_nullable
into HeaderTable
FROM
sys.tables st
inner join sys.columns sc on sc.object_id = st.object_id
INNER JOIN sys.types t ON sc.user_type_id = t.user_type_id
Ahora, si hace una selección rápida * de HeaderTable, verá cada tabla, cada columna, su tipo de datos y max_length, todo lo que necesitaremos para recrearlo.
- Escriba cada tabla de la base de datos en su propio archivo de texto con nombre dinámico
Aquí es donde empieza a ponerse guay. Usaremos un cursor para recorrer sys.tables y volcar cada uno en su propio .txt.
Vamos a utilizar un montón de variables:
@table va al cursor. Contendrá los nombres de cada tabla a medida que avanzamos.
@Database, @filepath, @filename, @filetype se utilizarán para construir un conjunto de sentencias SQL dinámicas.
@sql va a contener nuestros comandos SQL finales, para ponerlos en sp_executesql.
Las cosas se complican un poco con los delimitadores y los terminadores de fila. Si utiliza los valores predeterminados de | y / r, vas a tener dificultades con la tabla Comentarios. Tendremos que usar algo que sabemos que no se usa en ninguna parte de la base de datos StackOverflow. Puede usar newid (), cohetes y ojos saltones o puede usar su canción infantil favorita. Cualquier cosa, siempre que no esté ya en StackOverflow.
Aquí está el guión:
declare @table varchar(255),
@Database varchar(255),
@filepath varchar(255),
@filename varchar(255),
@filetype varchar(255),
@sql nvarchar(max),
@delimiter varchar(255),
@rowterminator varchar(255)
set @Database = 'StackOverflow'
set @filepath = 'C:Data' -- fix pretty-print mishandling of not-truly escaped '
set @filetype = '.txt'
set @delimiter = 'WhimmyWhammyWozzle'
set @rowterminator = 'WubaLubaDubDub'
declare c cursor local for
select name from sys.tables with (Nolock)
open c
fetch from c into @table
while @@FETCH_STATUS = 0
begin
SET @filename = @table
--output to txt
set @sql = N'declare @bcp varchar(4000)
set @bcp = ''bcp " select top 10000 * from ' + @table + ' " queryout '
+ @filepath + @filename + @filetype + ' -t "' + @delimiter + '" -r "'
+ @rowterminator + '" -c -T -d ' + @Database + '''
print @bcp
EXECUTE master.dbo.xp_cmdshell @BCP'
print @sql
--exec sp_executesql @sql
fetch next from c into @table
end
close c
deallocate c
Tenga en cuenta que he puesto el seguro en caso de que lo pegue y presione F5. No es que alguien pudiera hacer eso, ¿verdad? exec sp_executesql @sql no se ejecutará hasta que elimine los comentaristas. También he incluido un top 10000.
Vaya a la ruta de su archivo y debería ver un montón de archivos de texto.
Adelante, abra uno y cambie algunos datos. Si cree que abrirlos manualmente es para campesinos, puede usar Fart.exe para buscar y reemplazar en todos los archivos de texto.
- Vuelva a escribir el encabezado
Continúe y cree una nueva base de datos.
Vamos a codificar la recreación de la tabla de encabezado y la usaremos para reconstruir el resto.
restaurar encabezado:
if exists (select name from sys.tables where name = 'HeaderTable')
begin
drop table HeaderTable
end
create table HeaderTable
(Table_Name varchar(255),
Column_Name varchar(255),
Data_type varchar(255),
Max_Length varchar(255),
precision varchar(255),
scale varchar(255),
is_nullable varchar(255))
Y ahora, vamos a insertar de forma masiva nuestro Schema en HeaderTable:
set @sql = 'BULK INSERT HeaderTable FROM ''' + @filepath + 'HeaderTable'+ @filetype + ''' WITH (FIELDTERMINATOR = '''
+ @delimiter + ''', ROWTERMINATOR = ''' + @rowterminator + ''')'
print @sql
--exec sp_executesql @sql
We will have to tidy it up a bit, to make the next steps easier:
update HeaderTable
set Max_Length = 'max'
where Max_length = -1
update HeaderTable
set Max_Length = '(' + Max_Length + ')'
update HeaderTable
set Max_Length = ''
where Data_type in ( 'int', 'bigint', 'smallint', 'tinyint',
'date','datetime', 'uniqueidentifier', 'sysname', 'bit')
- Recreación e inserción masiva de cada tabla desde su archivo de texto
Y aquí es donde las cosas se ponen frescas de nuevo. Vamos a recorrer HeaderTable y volver a crear cada tabla, concatenando la instrucción Create con STUFF (). No me preguntes cómo funcionan las cosas (): un antiguo compañero de trabajo (Mike Ignatoski) me lo dio hace años. Fuentes confiables dicen que originalmente lo obtuvo de un tipo llamado Solomon.
declare @table varchar(255),
@column_string nvarchar(max),
@sql nvarchar(max),
@string nvarchar(max),
@filepath varchar(255),
@filename varchar(255),
@filetype varchar(255),
@sql nvarchar(max),
@delimiter varchar(255),
@rowterminator varchar(255)
set @filepath = 'C:Data' -- fix pretty-print mishandling of not-truly escaped '
set @filetype = '.txt'
set @delimiter = 'WhimmyWhammyWozzle'
set @rowterminator = 'WubaLubaDubDub'
declare c cursor local for
select distinct Table_Name from HeaderTable
where Table_Name != 'HeaderTable'
open c
fetch from c into @table
while @@FETCH_STATUS = 0
begin
set @string = null
set @string = (select stuff( (
select ', ' + Column_Name + ' ' + Data_type + Max_Length from HeaderTable
where Table_Name = @table
for xml path ('')),1,2,''))
print @string
set @sql = ' if not exists (select top 1 name from sys.tables where name = ''' + @table + ''') begin
create table ' + @table + ' (' + @string + ') end'
print @sql
exec sp_executesql @sql
--populate the table
set @sql = 'BULK INSERT ' + @table + ' FROM ''' + @filepath + @table + '.txt'' WITH (FIELDTERMINATOR = '''
+ @delimiter + ''', ROWTERMINATOR = ''' + @rowterminator + ''' )'
print @sql
exec sp_executesql @sql
fetch next from c into @table
end
close c
deallocate c
Y ahí lo tiene: su base de datos ha sido restaurada a partir de archivos de texto. ¡Puede consignar el archivo .bak a la pila de basura del historial! Siempre que no tenga funciones, procedimientos almacenados, vistas, restricciones o índices.
Aquí están los scripts completos: Bad Idea Jeans Backup:
declare @table varchar(255),
@Database varchar(255),
@filepath varchar(255),
@filename varchar(255),
@filetype varchar(255),
@sql nvarchar(max),
@delimiter varchar(255),
@rowterminator varchar(255)
set @Database = 'StackOverflow'
set @filepath = 'C:Data' -- fix pretty-print mishandling of not-truly escaped '
set @filetype = '.txt'
set @delimiter = 'WhimmyWhammyWozzle'
set @rowterminator = 'WubaLubaDubDub'
--create database header
if exists (select name from sys.tables st with (Nolock) where name = 'HeaderTable')
begin
drop table HeaderTable
end
SELECT
st.name, sc.name 'Column_Name', t.Name 'Data_type',sc.max_length 'Max_Length',
sc.precision, sc.scale, sc.is_nullable
into HeaderTable
FROM
sys.tables st
inner join sys.columns sc on sc.object_id = st.object_id
INNER JOIN sys.types t ON sc.user_type_id = t.user_type_id
select * from HeaderTable
declare c cursor local for
select name from sys.tables so with (Nolock)
open c
fetch from c into @table
while @@FETCH_STATUS = 0
begin
SET @filename = @table
--output to txt
set @sql = N'declare @bcp varchar(4000)
set @bcp = ''bcp " select top 10000 * from ' + @table + ' " queryout '
+ @filepath + @filename + @filetype + ' -t "' + @delimiter + '" -r "'
+ @rowterminator + '" -c -T -d ' + @Database + '''
print @bcp
EXECUTE master.dbo.xp_cmdshell @BCP'
print @sql
exec sp_executesql @sql
fetch next from c into @table
end
close c
deallocate c
Restauración de jeans de mala idea:
declare @table varchar(255),
@column_string nvarchar(max),
@sql nvarchar(max),
@string nvarchar(max),
@filepath varchar(255),
@filename varchar(255),
@filetype varchar(255),
@delimiter varchar(255),
@rowterminator varchar(255)
set @filepath = 'C:Data' -- fix pretty-print mishandling of not-truly escaped '
set @filetype = '.txt'
set @delimiter = 'WhimmyWhammyWozzle'
set @rowterminator = 'WubaLubaDubDub'
--restore header
if exists (select name from sys.tables where name = 'HeaderTable')
begin
drop table HeaderTable
end
create table HeaderTable
(Table_Name varchar(255),
Column_Name varchar(255),
Data_type varchar(255),
Max_Length varchar(255),
precision varchar(255),
scale varchar(255),
is_nullable varchar(255))
set @sql = 'BULK INSERT HeaderTable FROM ''' + @filepath + 'HeaderTable'+ @filetype + ''' WITH (FIELDTERMINATOR = '''
+ @delimiter + ''', ROWTERMINATOR = ''' + @rowterminator + ''')'
print @sql
exec sp_executesql @sql
--make some changes so that we can concatenate our create tables more easily
update HeaderTable
set Max_Length = 'max'
where Max_length = -1
update HeaderTable
set Max_Length = '(' + Max_Length + ')'
update HeaderTable
set Max_Length = ''
where Data_type in ( 'int', 'bigint', 'smallint', 'tinyint',
'date','datetime', 'uniqueidentifier', 'sysname', 'bit')
select * from HeaderTable
--restore DB
declare c cursor local for
select distinct name from sys.columns
where name != 'HeaderTable'
open c
fetch from c into @table
while @@FETCH_STATUS = 0
begin
set @string = null
set @string = (select stuff( (
select ', ' + Column_Name + ' ' + Data_type + Max_Length from HeaderTable
where name = @table
for xml path ('')),1,2,''))
print @string
set @sql = ' if not exists (select top 1 name from sys.tables where name = ''' + @table + ''') begin
create table ' + @table + ' (' + @string + ') end'
print @sql
--exec sp_executesql @sql
set @sql = 'BULK INSERT ' + @table + ' FROM ' + '' + @filepath + @table + '.txt'' WITH (FIELDTERMINATOR = '''
+ @delimiter + ''', ROWTERMINATOR = ''' + @rowterminator + ''' )'
print @sql
--exec sp_executesql @sql
fetch next from c into @table
end
close c
deallocate c
Si bien esta no es una buena opción para las copias de seguridad reales, existen algunos casos de uso para volcar tablas en archivos de texto. En ese contexto, un enfoque alternativo al uso xp_cmdshell llamar BCP.EXE es usar el DB_BulkExport Procedimiento almacenado SQLCLR que está disponible en SQL # (que escribí). A continuación, puede utilizar File_GZip para comprimir los archivos.
Las siguientes son ventajas y desventajas de usar DB_BulkExport en comparación con BCP.EXE:
Pros:
- No es necesario habilitar
xp_cmdshell - Agrega encabezados de columna
- Texto: no califica ningún campo, todos los campos o solo los campos que lo requieren (es decir, campos no numéricos / no binarios). Los campos de cadena que califican para texto significan que puede usar un delimitador de campo estándar (como una coma o tabulación) y un delimitador de fila estándar (como CRLF o LF / NL), en lugar de un carácter o secuencia de caracteres que espera que no sean presente en cualquier campo de cadena. El calificador de texto lo define el usuario (normalmente, entre comillas dobles) y la secuencia de escape para los calificadores de texto incrustados puede ser la que desee (normalmente también entre comillas dobles, pero también puede ser una barra invertida).
- Control sobre
BITrepresentación: 1/0, V / F o Verdadero / Falso. - (pronto) Control sobre
SMALLDATETIME,DATETIME,DATE,TIME,DATETIME2, yDATETIMEOFFSETformato.
Contras:
- No gratuito (DB_BulkExport y File_GZip solo están disponibles en la versión completa)
Tenga en cuenta que DB_BulkExport solo exporta datos; no registra la estructura del conjunto de resultados. Esto no es diferente a BCP, ni es realmente diferente a la solución proporcionada en la respuesta principal de la wiki de la comunidad, dado que la solución allí ignora las intercalaciones, las columnas calculadas, la mayoría de las opciones de columna, etc.
Otra opción para SQL Server, mencionada por Kin, es mssql-scripter. Esta es una herramienta gratuita / de código abierto, multiplataforma, basada en Python. No lo he usado, pero parece exportar declaraciones DDL y / o DML que se pueden ejecutar para recrear los datos y / o el esquema (tablas, restricciones, etc.). Parece que solo exporta los datos como INSERT declaraciones en lugar de campos delimitados. Parece bastante interesante, pero revise los “Problemas” para asegurarse de que no haya nada que pueda afectar su uso.
Además, aunque esta pregunta (y la respuesta principal de la wiki de la comunidad) mencionan SQL Server, los problemas relacionados con GDPR no son específicos de SQL Server. Entonces, solo pensé en mencionar que la capacidad de exportar tablas (e incluso esquemas, etc.) está disponible para MySQL en las siguientes dos utilidades que vienen con él:
- mysqldump
- mysqlpump
Lo mismo se puede hacer en PostgreSQL usando las siguientes dos utilidades que vienen con él:
- pg_dump
- pg_dumpall
Para Oracle, consulte los siguientes recursos, que creo que al menos lo acercarán mucho, si no un resultado totalmente equivalente (gracias a Michael Kutz por señalarme en la dirección correcta):
- ¿Cómo puedo poner en cola un archivo con formato CSV usando SQLPLUS?
- Generación rápida de CSV y JSON desde Oracle Database
- Creación de informes CSV
- Automatice la generación de salida de consultas SQL a CSV
No estoy seguro de si DB2 incluye utilidades similares.
Reseñas y valoraciones
Recuerda que tienes el privilegio agregar una reseña si te ayudó.