Crear una copia de seguridad completa de base de datos

Se aplica a: SQL Server

En este artículo se describe cómo crear una copia de seguridad completa de la base de datos en SQL Server con SQL Server Management Studio, Transact-SQL o PowerShell.

Para más información, consulte Copia de seguridad y restauración de SQL Server con el servicio Microsoft Azure Blob Storage y Copia de seguridad en URL de SQL Server.

Limitaciones y restricciones

• La instrucción BACKUP no se permite en una transacción explícita o implícita.
• Las copias de seguridad que se crean en una versión más reciente de SQL Server no se pueden restaurar en versiones anteriores de SQL Server.

Para obtener información general, pero también especializada, sobre los conceptos y las tareas de copia de seguridad, vea Información general de copia de seguridad (SQL Server) antes de continuar.

Recomendaciones

• A medida que la base de datos aumenta de tamaño, las copias de seguridad completas requieren más tiempo para finalizar y más espacio de almacenamiento. Para las bases de datos grandes, considere la posibilidad de complementar las copias de seguridad completas con una serie de copias de seguridad diferenciales.
• Calcule el tamaño de una copia de seguridad completa de la base de datos mediante el procedimiento almacenado del sistema sp_spaceused .
• De forma predeterminada, cada operación de copia de seguridad correcta agrega una entrada en el registro de errores de SQL Server y en el registro de eventos del sistema. Si se hace una copia de seguridad con frecuencia, los mensajes que indican la corrección de la operación se acumularán rápidamente, lo que da lugar a registros de errores muy grandes que pueden dificultar la búsqueda de otros mensajes. En esos casos, puede suprimir estas entradas de registro de copia de seguridad con la marca de seguimiento 3226 si ninguno de los scripts depende de ellas. Para obtener más información, vea Marcas de seguimiento (Transact-SQL).

Seguridad

TRUSTWORTHY se establece en OFF en una copia de seguridad de base de datos. Para obtener información sobre cómo establecer TRUSTWORTHY en ON, vea Opciones de ALTER DATABASE SET (Transact-SQL).

A partir de SQL Server 2012 (11.x), ya no están disponibles las opciones PASSWORD y MEDIAPASSWORD para crear copias de seguridad. Todavía puede restaurar las copias de seguridad creadas con contraseñas.

Permisos

De forma predeterminada, los permisos BACKUP DATABASE y BACKUP LOG se corresponden a los miembros del rol fijo de servidor sysadmin y de los roles fijos de base de datos db_owner y db_backupoperator.

Los problemas de propiedad y permisos del archivo físico del dispositivo de copia de seguridad pueden interferir con una operación de copia de seguridad. El servicio SQL Server debe leer y escribir en el dispositivo. La cuenta en la que se ejecuta el servicio SQL Server debe tener permisos de escritura en el dispositivo de copia de seguridad. En cambio, sp_addumpdevice, que agrega una entrada para un dispositivo de copia de seguridad en las tablas del sistema, no comprueba los permisos de acceso a los archivos. Es posible que los problemas en el archivo físico del dispositivo de copia de seguridad no aparezcan hasta que se usa la copia de seguridad o se intenta realizar una restauración.

Uso de SQL Server Management Studio

1. Después de conectarse a la instancia adecuada del Motor de base de datos de Microsoft SQL Server, en el Explorador de objetos, expanda el árbol del servidor.

2. Expanda Bases de datosy seleccione la base de datos de un usuario o expanda Bases de datos del sistema y seleccione una base de datos del sistema.

3. Haz clic con el botón derecho en la base de datos de la que quieras hacer una copia de seguridad, selecciona Tareas y, después, Copia de seguridad...

4. En el cuadro de diálogo Copia de seguridad de base de datos, la base de datos seleccionada aparece en la lista desplegable (que puede cambiar a cualquier otra base de datos del servidor).

5. En la lista desplegable Tipo de copia de seguridad, seleccione el tipo de copia de seguridad; el valor predeterminado es Completa.

   Importante

   Debe realizar al menos una copia de seguridad completa de la base de datos para poder realizar una copia de seguridad diferencial o del registro de transacciones.

6. En Componente de copia de seguridad, seleccione Base de datos.

7. En la sección Destino, revise la ubicación predeterminada del archivo de copia de seguridad (en la carpeta ../mssql/data).

   Puede usar la lista desplegable Hacer copia de seguridad en para seleccionar otro dispositivo. Seleccione Agregar para agregar objetos de copia de seguridad o destinos. Puede fragmentar el conjunto de copia de seguridad en varios archivos para aumentar la velocidad de copia de seguridad.

   Para eliminar un destino de copia de seguridad, selecciónelo y elija Quitar. Para ver el contenido de un destino de copia de seguridad existente, selecciónelo y elija Contenido.

8. (Opcional) Revise las demás opciones disponibles en las páginas Opciones multimedia y Opciones de copia de seguridad.

   Para obtener más información sobre las distintas opciones de copia de seguridad, vea página General, página Opciones multimedia y página Opciones de copia de seguridad.

9. Seleccione Aceptar para iniciar la copia de seguridad.

10. Cuando la copia de seguridad se complete correctamente, seleccione Aceptar para cerrar el cuadro de diálogo SQL Server Management Studio.

Información adicional

• Después de crear una copia de seguridad completa de la base de datos, puede crear una copia de seguridad diferencial o una copia de seguridad del registro de transacciones.

• (opcional) También puede activar la casilla Copia de seguridad de solo copia para crear una copia de seguridad de solo copia. Una copia de seguridad de solo copia es una copia de seguridad de SQL Server que es independiente de la secuencia de copias de seguridad de SQL Server convencionales. Para obtener más información, consulte Copias de seguridad de solo copia (SQL Server). Una copia de seguridad de solo copia no está disponible para el tipo de copia de seguridad Diferencial.

• La opción Sobrescribir medios está deshabilitada en la página Opciones multimedia si la copia de seguridad se realiza en una dirección URL.

Ejemplos

Para los ejemplos siguientes, cree una base de datos de prueba con el siguiente código Transact-SQL:

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE TABLE SQLTest
   (
      ID INT NOT NULL PRIMARY KEY,
      c1 VARCHAR(100) NOT NULL,
      dt1 DATETIME NOT NULL DEFAULT getdate()
   );
GO

USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

A Crear copia de seguridad completa en disco en una ubicación predeterminada

En este ejemplo, se creará una copia de seguridad de la base de datos SQLTestDB en disco en la ubicación de copia de seguridad predeterminada.

1. Después de conectarse a la instancia adecuada del Motor de base de datos de Microsoft SQL Server, en el Explorador de objetos, expanda el árbol del servidor.

2. Expanda Bases de datos, haga clic con el botón derecho en SQLTestDB, seleccione Tareasy, finalmente, Copia de seguridad...

3. Seleccione Aceptar.

4. Cuando la copia de seguridad se complete correctamente, seleccione Aceptar para cerrar el cuadro de diálogo SQL Server Management Studio.

B. Crear copia de seguridad completa en disco en una ubicación no predeterminada

En este ejemplo, se creará una copia de seguridad de la base de datos SQLTestDB en disco en la ubicación que elija.

1. Después de conectarse a la instancia adecuada del Motor de base de datos de Microsoft SQL Server, en el Explorador de objetos, expanda el árbol del servidor.

2. Expanda Bases de datos, haga clic con el botón derecho en SQLTestDB, seleccione Tareasy, finalmente, Copia de seguridad...

3. En la página General , en la sección Destino , seleccione Disco en la lista desplegable Copia de seguridad en: .

4. Seleccione Quitar hasta que se quiten todos los archivos de copia de seguridad existentes.

5. Seleccione Agregar y se abrirá el cuadro de diálogo Seleccionar destino de la copia de seguridad.

6. Escriba una ruta de acceso y un nombre de archivo válidos en el cuadro de texto Nombre de archivo y use .bak como extensión para simplificar la clasificación de este archivo.

7. Seleccione Aceptar y, después, otra vez Aceptar para iniciar la copia de seguridad.

8. Cuando la copia de seguridad se complete correctamente, seleccione Aceptar para cerrar el cuadro de diálogo SQL Server Management Studio.

C. Crear una copia de seguridad cifrada

En este ejemplo se creará una copia de seguridad de la base de datos SQLTestDB con cifrado en la ubicación de copia de seguridad predeterminada.

1. Después de conectarse a la instancia adecuada del Motor de base de datos de Microsoft SQL Server, en el Explorador de objetos, expanda el árbol del servidor.

2. Expanda Bases de datos y Bases de datos del sistema, haga clic con el botón derecho en master y seleccione Nueva consulta para abrir una ventana de consulta con una conexión a la base de datos SQLTestDB.

3. Ejecute los comandos siguientes para crear una clave maestra de base de datos y un certificado en la base de datos master.

   -- Create the master key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';  
   
   -- If the master key already exists, open it in the same session that you create the certificate (see next step)
   OPEN MASTER KEY DECRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe'
   
   -- Create the certificate encrypted by the master key
   CREATE CERTIFICATE MyCertificate
   WITH SUBJECT = 'Backup Cert', EXPIRY_DATE = '20201031';  
   

4. En el Explorador de objetos, en el nodo Bases de datos, haga clic con el botón derecho en SQLTestDB y seleccione sucesivamente Tareas y Hacer copia de seguridad…

5. En la página Opciones multimedia, en la sección Sobrescribir medios, seleccione Hacer copia de seguridad en un nuevo conjunto de medios y borrar todos los conjuntos de copia de seguridad existentes.

6. En la página Opciones de copia de seguridad , en la sección Cifrado , active la casilla Cifrar copia de seguridad .

7. En la lista desplegable Algoritmo, seleccione AES 256.

8. En la lista desplegable Certificado o clave asimétrica , seleccione MyCertificate.

9. Seleccione Aceptar.

D. Copia de seguridad en Azure Blob Storage

En el ejemplo siguiente se crea una copia de seguridad completa de SQLTestDB en Azure Blob Storage. En este ejemplo se asume que ya tiene una cuenta de almacenamiento con un contenedor de blobs. En este ejemplo se crea una firma de acceso compartido de forma automática; se produce un error si el contenedor tiene una firma de acceso compartido existente.

Si no tiene un contenedor de Azure Blob Storage en una cuenta de almacenamiento, cree uno antes de continuar. Consulte Creación de una cuenta de almacenamiento de uso general y Creación de un contenedor.

1. Después de conectarse a la instancia adecuada del Motor de base de datos de Microsoft SQL Server, en el Explorador de objetos, expanda el árbol del servidor.

2. Expanda Bases de datos, haga clic con el botón derecho en SQLTestDB, seleccione Tareasy, finalmente, Copia de seguridad...

3. En la página General de la sección Destino , seleccione URL en la lista desplegable Copia de seguridad en: .

4. Seleccione Agregar y se abrirá el cuadro de diálogo Seleccionar destino de la copia de seguridad.

5. Si previamente ha registrado el contenedor de almacenamiento de Azure que quiere usar con SQL Server Management Studio, selecciónelo. En caso contrario, seleccione Nuevo contenedor para registrar un contenedor nuevo.

6. En el cuadro de diálogo Conectarse a una suscripción de Microsoft, inicie sesión en la cuenta.

7. En el cuadro de texto desplegable Seleccionar cuenta de almacenamiento, seleccione la cuenta de almacenamiento.

8. En el cuadro de texto desplegable Seleccionar contenedor de blobs, seleccione el contenedor de blobs.

9. En el cuadro de calendario desplegable Expiración de la directiva de acceso compartido, seleccione una fecha de expiración para la directiva de acceso compartido que se va a crear en este ejemplo.

10. Seleccione Crear credencial para generar una firma de acceso compartido y una credencial en SQL Server Management Studio.

11. Seleccione Aceptar para cerrar el cuadro de diálogo Conectarse a una suscripción de Microsoft.

12. En el cuadro de texto Archivo de copia de seguridad, modifique el nombre del archivo de copia de seguridad (opcional).

13. Seleccione Aceptar para cerrar el cuadro de diálogo Seleccionar destino de la copia de seguridad.

14. Seleccione Aceptar para iniciar la copia de seguridad.

15. Cuando la copia de seguridad se complete correctamente, seleccione Aceptar para cerrar el cuadro de diálogo SQL Server Management Studio.

Usar Transact-SQL

Ejecute la instrucción BACKUP DATABASE para crear la copia de seguridad de base de datos completa, y especifique lo siguiente:

• El nombre de la base de datos de la que se va a realizar una copia de seguridad.
• El dispositivo de copia de seguridad en el que se escribe la copia de seguridad de base de datos completa.

La sintaxis básica de Transact-SQL para crear una copia de seguridad de base de datos completa es:

BACKUP DATABASE base_de_datos TO dispositivo_de_copia_de_seguridad [ ,...n ] [ WITH con_opciones [ ,...o ] ] ;

Opcionalmente, especifique una o varias opciones de WITH. A continuación se describen algunas de las opciones de WITH básicas. Para obtener información sobre todas las opciones de WITH, vea BACKUP (Transact-SQL).

Opciones de WITH básicas del conjunto de copia de seguridad:

• { COMPRESSION | NO_COMPRESSION }: En SQL Server 2008 (10.0.x) Enterprise y versiones posteriores únicamente, especifica si la compresión de copia de seguridad se realiza en esta copia de seguridad, lo que invalida la configuración predeterminada del nivel servidor.
• ENCRYPTION (ALGORITHM, SERVER CERTIFICATE | ASYMMETRIC KEY): En SQL Server 2014 o versiones posteriores únicamente, especifica el algoritmo de cifrado que se va a utilizar y el certificado o la clave asimétrica que se va a usar para proteger el cifrado.
• DESCRIPTION = { 'text' | @text_variable }: Especifica el texto de forma libre que describe el conjunto de copia de seguridad. La cadena puede tener un máximo de 255 caracteres.
• NAME = { backup_set_name | @backup_set_name_var }: Especifica el nombre del conjunto de copia de seguridad. Los nombres pueden tener un máximo de 128 caracteres. Si no se especifica NAME, está en blanco.

De forma predeterminada, BACKUPanexa la copia de seguridad a un conjunto de medios existente, y se conservan los conjuntos de copia de seguridad existentes. Para especificarlo de forma explícita, use la opción NOINIT. Para obtener información sobre la anexión a conjuntos de copia de seguridad existentes, vea Conjuntos de medios, familias de medios y conjuntos de copias de seguridad (SQL Server).

Para dar formato a los medios de copia de seguridad, use la opción FORMAT:

FORMAT [ , MEDIANAME = { media_name | @media_name_variable } ] [ , MEDIADESCRIPTION = { text | @text_variable } ]

Use la cláusula FORMAT cuando utilice los medios por primera vez o cuando quiera sobrescribir todos los datos existentes. De manera opcional, puede asignar a los nuevos medios un nombre y una descripción.

Ejemplos

Para los ejemplos siguientes, cree una base de datos de prueba con el siguiente código Transact-SQL:

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE TABLE SQLTest (
   ID INT NOT NULL PRIMARY KEY,
   c1 VARCHAR(100) NOT NULL,
   dt1 DATETIME NOT NULL DEFAULT GETDATE()
)
GO

USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

A Copia de seguridad en un dispositivo de disco

En el ejemplo siguiente se realiza una copia de seguridad completa de la base de datos SQLTestDB en el disco y se usa FORMAT para crear un conjunto de medios nuevo.

USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
TO DISK = 'c:\tmp\SQLTestDB.bak'
   WITH FORMAT,
      MEDIANAME = 'SQLServerBackups',
      NAME = 'Full Backup of SQLTestDB';
GO

B. Copia de seguridad en un dispositivo de cinta

En este ejemplo se realiza una copia de seguridad en cinta de la base de datos SQLTestDB completa y se anexa a las copias de seguridad anteriores.

USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
   TO TAPE = '\\.\Tape0'
   WITH NOINIT,
      NAME = 'Full Backup of SQLTestDB';
GO

C. Copia de seguridad en un dispositivo de cinta lógico

En este ejemplo, se crea un dispositivo de copia de seguridad lógico para una unidad de cinta. A continuación, se realiza una copia de seguridad completa de la base de datos SQLTestDB en dicho dispositivo.

-- Create a logical backup device,
-- SQLTestDB_Bak_Tape, for tape device \\.\tape0.
USE master;
GO
EXEC sp_addumpdevice 'tape', 'SQLTestDB_Bak_Tape', '\\.\tape0'; USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
   TO SQLTestDB_Bak_Tape
   WITH FORMAT,
      MEDIANAME = 'SQLTestDB_Bak_Tape',
      MEDIADESCRIPTION = '\\.\tape0',
      NAME = 'Full Backup of SQLTestDB';
GO

Mediante PowerShell

Use el cmdlet Backup-SqlDatabase . Para indicar de forma explícita una copia de seguridad completa de la base de datos, especifique el parámetro -BackupAction con su valor predeterminado, Database. Este parámetro es opcional para las copias de seguridad de base de datos completas.

Ejemplos

A Copia de seguridad completa (local)

En el ejemplo siguiente se crea una copia de seguridad completa de la base de datos <myDatabase> en la ubicación de copia de seguridad predeterminada de la instancia de servidor Computer\Instance. Opcionalmente, en este ejemplo se especifica -BackupAction Database.

Para obtener ejemplos con la sintaxis completa, consulte Backup-SqlDatabase.

$credential = Get-Credential

Backup-SqlDatabase -ServerInstance Computer[\Instance] -Database <myDatabase> -BackupAction Database -Credential $credential

B. Copia de seguridad completa en Azure

En el ejemplo siguiente se crea una copia de seguridad completa de la base de datos <myDatabase> en la instancia de <myServer> en Azure Blob Storage. Se ha creado una directiva de acceso almacenada con derechos de lectura, escritura y lista. La credencial de SQL Server, https://<myStorageAccount>.blob.core.windows.net/<myContainer>, se creó con una Firma de acceso compartido asociada a la directiva de acceso almacenada. El comando de PowerShell usa el parámetro BackupFile para especificar la ubicación (dirección URL) y el nombre del archivo de copia de seguridad.

$credential = Get-Credential
$container = 'https://<myStorageAccount>blob.core.windows.net/<myContainer>'
$fileName = '<myDatabase>.bak'
$server = '<myServer>'
$database = '<myDatabase>'
$backupFile = $container + '/' + $fileName

Backup-SqlDatabase -ServerInstance $server -Database $database -BackupFile $backupFile -Credential $credential

Tareas relacionadas

• Realizar una copia de seguridad de una base de datos (SQL Server)
• Crear una copia de seguridad diferencial de una base de datos (SQL Server)
• Restore a Database Backup Using SSMS
• Restaurar una copia de seguridad de base de datos en el modelo de recuperación simple (Transact-SQL)
• Restaurar una base de datos según el punto de error en el modelo de recuperación completa (Transact-SQL)
• Restaurar una base de datos a una nueva ubicación (SQL Server)
• Usar el Asistente para planes de mantenimiento

Contenido relacionado

• Solución de problemas de SQL Server de backup y restore de las operaciones
• Backup Overview (SQL Server)
• Copias de seguridad de registros de transacciones (SQL Server)
• Conjuntos de medios, familias de medios y conjuntos de copias de seguridad (SQL Server)
• sp_addumpdevice (Transact-SQL)
• BACKUP (Transact-SQL)
• Copia de seguridad de base de datos (página General)
• Copia de seguridad de la base de datos (página Opciones de copia de seguridad)
• Copias de seguridad diferenciales (SQL Server)
• Copias de seguridad completas de bases de datos (SQL Server)