Crear una instantánea de base de datos (Transact-SQL)

  • Artículo

Se aplica a:SQL Server

El único modo de crear una instantánea de base de datos de SQL Server consiste en usar Transact-SQL. SQL Server Management Studio no admite la creación de instantáneas de base de datos.

Requisitos previos

La base de datos de origen, que puede usar cualquier modelo de recuperación, debe cumplir los siguientes requisitos previos:

  • La instancia del servidor debe ejecutarse en una edición de SQL Server que admita instantáneas de bases de datos. Para obtener más información sobre la compatibilidad con las instantáneas de bases de datos en SQL Server, consulte Ediciones y características compatibles de SQL Server 2022.

  • La base de datos de origen debe estar en línea, a menos que sea una base de datos reflejada dentro de una sesión de creación de reflejo de la base de datos.

  • Para crear una instantánea de base de datos en una base de datos reflejada, la base de datos debe hallarse en estado de reflejosincronizado.

  • La base de datos de origen no se puede configurar como una base de datos compartida escalable.

  • Antes de SQL Server 2019 (15.x), la base de datos de origen no podía contener un grupo de archivos MEMORY_OPTIMIZED_DATA. La compatibilidad con las instantáneas de base de datos en memoria se agregó en SQL Server 2019 (15.x).

Importante

Para obtener información sobre otras consideraciones importantes, vea Instantáneas de base de datos (SQL Server).

Recomendaciones

En esta sección se describen los procedimientos recomendados siguientes:

Procedimiento recomendado: asignar nombres a las instantáneas de base de datos

Antes de crear instantáneas, es importante pensar cómo asignarles un nombre. Cada instantánea de base de datos necesita un nombre de base de datos único. Para facilitar la administración, el nombre de una instantánea puede incorporar información que identifique la base de datos, por ejemplo:

  • Nombre de la base de datos de origen.

  • Una indicación de que el nuevo nombre es para una instantánea.

  • La fecha y hora de creación de la instantánea, un número de secuencia o cualquier otra información, por ejemplo, la hora del día, para distinguir instantáneas secuenciales en una base de datos dada.

Por ejemplo, piense en una serie de instantáneas de base de datos AdventureWorks2022 . Se crean tres instantáneas diarias a intervalos de 6 horas entre las 6 de la mañana y las 6 de la tarde, basándose en un reloj de 24 horas. Cada instantánea diaria se conserva 24 horas antes de que se quite y sea reemplazada por una nueva instantánea con el mismo nombre. Cada nombre de instantánea indica la hora, pero no el día:

AdventureWorks_snapshot_0600
AdventureWorks_snapshot_1200
AdventureWorks_snapshot_1800

Como alternativa, si la hora de creación de estas instantáneas diarias varía cada día, es posible que sea preferible disponer de una convención de nomenclatura menos precisa, por ejemplo:

AdventureWorks_snapshot_morning
AdventureWorks_snapshot_noon
AdventureWorks_snapshot_evening

Procedimiento recomendado: limitar el número de instantáneas de base de datos

La creación de una serie de instantáneas a lo largo del tiempo permite capturar instantáneas secuenciales de la base de datos de origen. Cada instantánea se conserva hasta que se quite de manera explícita. Las instantáneas siguen creciendo a medida que se actualizan las páginas originales, por lo que seguramente querrá ahorrar espacio en el disco eliminando una instantánea más antigua después de crear una nueva instantánea.

Nota:

Para volver a una instantánea de base de datos, debe eliminar cualquier otra instantánea de esa base de datos.

Procedimiento recomendado: conexiones de clientes con una instantánea de base de datos

Para usar una instantánea de base de datos, los clientes deben saber dónde encontrarla. Los usuarios pueden leer de una instantánea de base de datos mientras se crea o elimina otra. Sin embargo, si sustituye una nueva instantánea por otra ya existente, debe redirigir a los clientes a la nueva instantánea. Los usuarios pueden conectarse manualmente a una instantánea de base de datos con SQL Server Management Studio o Azure Data Studio. Sin embargo, para admitir un entorno de producción, debe crear una solución programática que dirija de un modo transparente a los clientes de escritura de informes a la instantánea de base de datos más reciente de la base de datos.

Permisos

Todos los usuarios que pueden crear una base de datos pueden crear una instantánea de base de datos; sin embargo, para crear una instantánea de una base de datos reflejada, es necesario ser miembro del rol fijo de servidor sysadmin .

Creación de una instantánea de base de datos utilizando Transact-SQL

  1. Basándose en el tamaño actual de la base de datos de origen, asegúrese de que tiene suficiente espacio en disco para incluir la instantánea de base de datos. El tamaño máximo de una instantánea de base de datos es el tamaño de la base de datos de origen en el momento de la creación de la instantánea. Para más información, consulte Ver el tamaño del archivo disperso de una instantánea de base de datos (Transact-SQL).

  2. Emite una instrucción CREATE DATABASE en los archivos mediante la cláusula AS SNAPSHOT OF. Para crear una instantánea, se debe especificar el nombre lógico de cada archivo de la base de datos de origen. La sintaxis es la siguiente:

    CREATE DATABASE database_snapshot_name
ON
(
    NAME = logical_file_name
    , FILENAME = 'os_file_name'
) [ , ...n ]

AS SNAPSHOT OF source_database_name
[;]

    Los argumentos son los siguientes:

    Argumento Descripción
    database_snapshot_name El nombre de la instantánea a la que desea revertir la base de datos.
    logical_file_name El nombre lógico de la base de datos de origen utilizado en SQL Server cuando se hace referencia al archivo.
    os_file_name La ruta de acceso y el nombre de archivo que el sistema operativo utiliza cuando se crea el archivo.
    source_database_name La base de datos de origen.

    Para obtener una descripción completa de esta sintaxis, vea CREATE DATABASE (SQL Server Transact-SQL).

    Nota:

    Cuando se crea una instantánea de base de datos, no se permite la presencia de archivos de registro, archivos sin conexión, archivos de restauración e inactivos en la instrucción CREATE DATABASE.

Ejemplos

La extensión .ss que se usa en estos ejemplos es para mayor comodidad y no es necesaria. En las bases de datos que contienen varios archivos, se deben especificar todos los archivos, de conformidad con la sintaxis. No se especifican grupos de archivos.

A Crear una instantánea de la base datos AdventureWorks

En este ejemplo se crea una instantánea de base datos AdventureWorks . El nombre de la instantánea, AdventureWorks_dbss_1800, y el nombre de archivo de su archivo disperso, AdventureWorks_data_1800.ss, indican la hora de creación: 6 P.M (1800 horas).

CREATE DATABASE AdventureWorks_dbss1800 ON (
    NAME = AdventureWorks,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Data\AdventureWorks_data_1800.ss'
    ) AS SNAPSHOT OF AdventureWorks;
GO

B. Crear una instantánea de la base datos Sales

En este ejemplo se crea una instantánea de base de datos, sales_snapshot1200, en la base de datos Sales, que es la misma base de datos de ejemplo de Crear una base de datos que tiene grupos de archivos en CREATE DATABASE.

--Create sales_snapshot1200 as snapshot of the Sales database:
CREATE DATABASE sales_snapshot1200 ON (
    NAME = SPri1_dat,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\data\SPri1dat_1200.ss'
    ),
    (
    NAME = SPri2_dat,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\data\SPri2dt_1200.ss'
    ),
    (
    NAME = SGrp1Fi1_dat,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\mssql\data\SG1Fi1dt_1200.ss'
    ),
    (
    NAME = SGrp1Fi2_dat,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\data\SG1Fi2dt_1200.ss'
    ),
    (
    NAME = SGrp2Fi1_dat,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\data\SG2Fi1dt_1200.ss'
    ),
    (
    NAME = SGrp2Fi2_dat,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\data\SG2Fi2dt_1200.ss'
    ) AS SNAPSHOT OF Sales;
GO

Contenido relacionado