2 votos

¿Puedo separar algunas tablas en mi base de datos SQL para hacer copias de seguridad?

Tengo una gran base de datos con muchas tablas. Los números no importan. MSSQL 2017 (14.0.2037.2) en Server 2016 del cual tengo control total.

Una gran cantidad de mis datos están en dos o tres tablas. Imagina que estás rastreando autos, tendrías una tabla de unos pocos miles de autos y cada auto tiene millones de puntos de datos después de un tiempo. Literalmente el 80% de mi espacio de almacenamiento es utilizado por tres tablas.

En producción no hay problema. Se realizan copias de seguridad, las restauraciones se prueban con éxito, todo está bien.

Quiero ser capaz de bajar las tablas no estúpidamente grandes al entorno de desarrollo de vez en cuando para solucionar problemas o simplemente para obtener algunos datos del mundo real de las otras tablas. No necesito las tablas grandes. Toma una hora y tiene que pasar por un servicio de transferencia de archivos (lo que pondría mi base de datos con datos privados fuera de mi control).

Para simplificar la pregunta, ¿hay una manera fácil de hacer copias de seguridad solo de tablas seleccionadas para restaurarlas en el servidor de desarrollo? Creo que estoy a punto de intentar mover las tablas grandes a un archivo separado y simplemente copiar el archivo más pequeño, pero eso no es tan limpio o a prueba de balas como una copia de seguridad/restauración adecuada, por supuesto.

¿Consejos de algún gurú de SQL?

**edición - después de haber estado funcionando por un tiempo, la respuesta a continuación fue técnicamente correcta pero no prácticamente útil al final. Tenía mis tablas grandes en su propio grupo de archivos lo que me permitió hacer copias de seguridad de los archivos por separado, sin embargo, no pude simplemente tomar el archivo más pequeño y restaurarlo sin sus hermanos grandes de todas formas sin introducir riesgos y tener que modificar manualmente.

La opción más "rápida" fue restaurar la base de datos en el servidor local y luego truncar las tablas innecesarias, luego hacer copia de seguridad y restaurar esa base de datos en el entorno de desarrollo.

Por supuesto, ahora he pasado a Azure así que todo es redundante.

Simplemente vayan a Azure, gente.

1voto

Alberto Morillo Puntos 118

Mi sugerencia es crear un archivo de base de datos secundario (.ndf) en un nuevo grupo de archivos y trasladar una o más tablas grandes al nuevo grupo de archivos, y hacer uso de la función de copia de seguridad/restauración de grupos de archivos.

Una vez que haya creado el nuevo archivo de base de datos y el grupo de archivos, mueva la tabla al grupo de archivos recién creado como se explica en este artículo.

A continuación encontrará una demostración completa del uso de la copia de seguridad/restauración de grupos de archivos recuperados de aquí.

USE [master]
GO

ALTER database [FGRestoreTEST] set offline with ROLLBACK IMMEDIATE;
DROP DATABASE [FGRestoreTEST]
GO

--Crear base de datos de ejemplo
CREATE DATABASE [FGRestoreTEST] ON PRIMARY (
    NAME = N'FGRestoreTEST'
    ,FILENAME = N'C:\SQLServer\FGRestoreTEST.mdf'
    ,SIZE = 5120 KB
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 1024 KB
    )
    ,FILEGROUP [secondaryFilegroup] (
    NAME = N'secondaryFilegroup'
    ,FILENAME = N'C:\SQLServer\secondaryFilegroup.ndf'
    ,SIZE = 5120 KB
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 1024 KB
    ) LOG ON (
    NAME = N'FGRestoreTEST_log'
    ,FILENAME = N'C:\SQLServer\FGRestoreTEST_log.ldf'
    ,SIZE = 2048 KB
    ,MAXSIZE = 2048 GB
    ,FILEGROWTH = 10 %
    )
GO

--Insertar algunos datos de prueba
use [FGRestoreTEST]
go
create table TestTable (id int) on secondaryFilegroup 
insert into TestTable values(1),(2)

--Hacer una copia de seguridad del grupo de archivos secundario y luego hacer una copia de seguridad del registro de transacciones
USE [master]
GO

BACKUP DATABASE FGRestoreTEST FILEGROUP = 'secondaryFilegroup' TO DISK = N'C:\SQLServer\secondaryFilegroupBackup.bak'
WITH INIT

BACKUP LOG FGRestoreTEST TO DISK = N'C:\SQLServer\tlog.trn'
WITH INIT;

--Restaurar el grupo de archivos secundario y luego restaurar el registro de transacciones
--tomado en el paso anterior
use [master]
go
RESTORE DATABASE FGRestoreTEST FILEGROUP = 'secondaryFilegroup'
FROM DISK = N'C:\SQLServer\secondaryFilegroupBackup.bak'
WITH norecovery

RESTORE LOG FGRestoreTEST
FROM DISK = N'C:\SQLServer\tlog.trn'
WITH recovery;

--En este punto, deberías haber visto los mensajes sobre
--Se requiere un avance adicional más allá de LSN ... para completar la secuencia de restauración
--Realice una copia de seguridad adicional del registro y luego restaure con recuperación
BACKUP LOG FGRestoreTEST TO DISK = N'C:\SQLServer\Taillog.trn'
WITH INIT;

RESTORE LOG FGRestoreTEST
FROM DISK = N'C:\SQLServer\Taillog.trn'
WITH recovery;

--Verifique que pueda seleccionar de TestTable
use [FGRestoreTEST]
go

SELECT *
FROM TestTable

EnMiMaquinaFunciona.com

EnMiMaquinaFunciona es una comunidad de administradores de sistemas en la que puedes resolver tus problemas y dudas.
Puedes consultar las preguntas de otros sysadmin, hacer tus propias preguntas o resolver las de los demás.

Powered by:

X