Cómo programar y automatizar copias de seguridad de bases de datos de SQL Server en SQL Server Express

Se aplica a: Microsoft SQL Server

Resumen


Las ediciones de SQL Server Express no ofrecen la opción de programar trabajos o planes de mantenimiento porque no incluyen el componente Agente SQL Server. Por lo tanto, al usar estas ediciones, tiene que buscar otra forma de crear copias de seguridad de las bases de datos.   

Actualmente, los usuarios de SQL Server Express pueden crear copias de seguridad de sus bases de datos a través de uno de estos métodos:

  • Usar un script Transact-SQL que utilice la familia de comandos de CREAR UNA COPIA DE SEGURIDAD DE LA BASE DE DATOS. Para obtener más información, vaya al sitio web de MSDN:

    COPIA DE SEGURIDAD (Transact-SQL)

En este artículo se describe cómo usar un script Transact-SQL junto con el Programador de tareas de Windows para automatizar la programación de las copias de seguridad de las bases de datos de SQL Server Express.

Más información


Para crear una copia de seguridad de las bases de datos de SQL Server con el Programador de tareas de Windows, debe seguir estos tres pasos:

Paso A: Usar SQL Server Management Studio Express Sqlcmd para crear el siguiente procedimiento almacenado en la base de datos maestra:

// Copyright © Microsoft Corporation.  Todos los derechos reservados.

//Este código publicado bajo los términos de la

//licencia pública de Microsoft (MS-PL, http://opensource.org/licenses/ms-pl.html).

USE [master] 

GO 

/****** Objeto:  StoredProcedure [dbo].[sp_BackupDatabases] ******/ 

SET ANSI_NULLS ON 

GO 

SET QUOTED_IDENTIFIER ON 

GO 

 

-- ============================================= 

-- Autor: Microsoft 

-- Fecha de creación: 2010-02-06

-- Descripción: Copia de seguridad de bases de datos para SQLExpress

-- Parameter1: databaseName 

-- Parameter2: backupType F=full, D=differential, L=log

-- Parameter3: ubicación del archivo de copia de seguridad

-- =============================================

 

CREAR PROCEDIMIENTO [dbo].[sp_BackupDatabases]  

            @databaseName sysname = null,

            @backupType CHAR(1),

            @backupLocation nvarchar(200) 

AS 

 

       SET NOCOUNT ON; 

           

            DECLARE @DBs TABLE

            (

                  ID int IDENTITY PRIMARY KEY,

                  DBNAME nvarchar(500)

            )

           

             -- Elija solo bases de datos que estén en línea en caso de que se haya optado por crear copias de seguridad de TODAS las bases de datos

             -- Si se optó por hacer una copia de seguridad de una base de datos específica, selecciónela en @DBs

            INSERTAR EN @DBs (DBNAME)

            SELECCIONAR Nombre DESDE master.sys.databases

            where state=0

            AND name=@DatabaseName

            OR @DatabaseName IS NULL

            ORDENAR POR Nombre

           

            -- Filtra las bases de datos que no requieren copia de seguridad

            IF @backupType='F'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')

                  END

            ELSE IF @backupType='D'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')

                  END

            ELSE IF @backupType='L'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')

                  END

            ELSE

                  BEGIN

                  RETURN

                  END

           

            -- Declare las variables

            DECLARE @BackupName varchar(100)

            DECLARE @BackupFile varchar(100)

            DECLARE @DBNAME varchar(300)

            DECLARE @sqlCommand NVARCHAR(1000) 

        DECLARE @dateTime NVARCHAR(20)

            DECLARE @Loop int                  

                       

            -- Repetir una base de datos tras otra

            SELECT @Loop = min(ID) FROM @DBs

 

      WHILE @Loop IS NOT NULL

      BEGIN

 

-- Los nombres de base de datos tienen que tener el formato [dbname], ya que algunos incluyen los caracteres - o _

      SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'

 

-- Define la fecha y hora actuales en formato aaaahhmmss

      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')  

 

-- Crea el nombre de archivo de copia de seguridad en formato ruta\nombredearchivo.extension para copias de seguridad completas, diferencial y de registro

      IF @backupType = 'F'

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'

      ELSE IF @backupType = 'D'

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'

      ELSE IF @backupType = 'L'

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'

 

-- Coloca un nombre a la copia de seguridad para guardarla en los medios

      IF @backupType = 'F'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime

      IF @backupType = 'D'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime

      IF @backupType = 'L'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime

 

-- Genera el comando SQL dinámico que se ejecutará

 

       IF @backupType = 'F' 

                  BEGIN

               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'

                  END

       IF @backupType = 'D'

                  BEGIN

               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        

                  END

       IF @backupType = 'L' 

                  BEGIN

               SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        

                  END

 

-- Ejecuta el comando SQL generado

       EXEC(@sqlCommand)

 

-- Ir a la siguiente base de datos

SELECT @Loop = min(ID) FROM @DBs where ID>@Loop

 

END

Paso B: En un editor de texto, cree un archivo por lotes con nombre Sqlbackup.bat y, después, copie el texto de uno de los siguientes ejemplos a ese archivo, según su caso:

Ejemplo 1: Copias de seguridad completas de todas las bases de datos en la instancia local con nombre de SQLEXPRESS mediante la autenticación de Windows

// Sqlbackup.bat

sqlcmd -S .\EXPRESS E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'" 

Ejemplo 2: Copias de seguridad diferenciales de todas las bases de datos en la instancia local con nombre de SQLEXPRESS mediante un SQLLogin y su contraseña

// Sqlbackup.bat

sqlcmd -U SQLLogin -P password -S .\SQLEXPRESS -Q "EXEC sp_BackupDatabases @backupLocation ='D:\SQLBackups', @BackupType=D"

Nota: SQLLogin debe tener al menos el rol del operador de copia de seguridad en SQL Server.

Ejemplo 3: Las copias de seguridad de registro de todas las bases de datos en la instancia local con nombre de SQLEXPRESS mediante la autenticación de Windows

// Sqlbackup.bat

sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\',@backupType='L'"

Ejemplo 4: Copias de seguridad completas de la base de datos USERDB en la instancia local con nombre de SQLEXPRESS mediante la autenticación de Windows

// Sqlbackup.bat

sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @databaseName=USERDB, @backupType='F'"

De forma similar, puede realizar una copia de seguridad diferencial de USERDB pegando en "D" para el parámetro @backupType y una copia de seguridad de registro de USERDB pegando en "L" para el parámetro @backupType.

Paso C: Programe un trabajo con el Programador de tareas de Windows para ejecutar el archivo por lotes creado en el paso B. Para ello, siga estos pasos:

  1. En el equipo que ejecute SQL Server Express, haga clic en Iniciar, vaya a Todos los programas > Accesorios > Herramientas del sistema y, después, haga clic en Tareas programadas. 
  2. Haga doble clic en Agregar tarea programada. 
  3. En el Asistente para tarea programada nueva, haga clic en Siguiente. 
  4. Haga clic en Examinar, luego en el archivo por lotes creado en el paso B y, por último, en Abrir. 
  5. Escriba el nombre SQLBACKUP para la tarea, haga clic en Diariamente y, por último, haga clic en Siguiente. 
  6. Especifique la información para programar la ejecución de la tarea. (Le recomendamos ejecutar esta tarea al menos una vez por día). Luego, haga clic en Siguiente.
  7. En el campo Escriba el nombre de usuario, especifique un nombre de usuario y, después, escriba una contraseña en el campo Escriba la contraseña.

    Nota Este usuario debe tener al menos el rol BackupOperator en el nivel de SQL Server si está usando uno de los archivos por lotes en el ejemplo 1, 3 o 4.
  8. Haga clic en Siguiente y luego en Finalizar. 
  9. Ejecute la tarea programada al menos una vez para asegurarse de que la copia de seguridad se haya creado correctamente.

Nota La carpeta para el ejecutable SQLCMD suele estar en las variables de ruta de acceso del servidor una vez que se instala SQL Server, pero si la variable de ruta de acceso no indica esta carpeta, podrá encontrarla en <Ubicación de instalación>\90\Tools\Binn (por ejemplo: C:\Archivos de programa\Microsoft SQL Server\90\Tools\Binn).

 Al usar el procedimiento descrito en este artículo, tenga en cuenta lo siguiente:

  • El servicio del Programador de tareas de Windows se debe estar ejecutando al momento de programar la ejecución de la tarea. Le recomendamos definir el tipo de inicio para este servicio en Automático. Así se garantizará que el servicio se ejecuta incluso si se produce un reinicio.
  • Debe haber mucho espacio en la unidad de disco en la que se escribirán las copias de seguridad. Le recomendamos borrar los archivos viejos de la carpeta de copia de seguridad con regularidad para asegurarse de no quedarse sin espacio en el disco. El script no contiene la lógica para borrar archivos antiguos.

Referencias adicionales