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

Las ediciones Express de SQL Server de no ofrecen una forma de programar trabajos o planes de mantenimiento porque el componente Agente de SQL Server no se incluye en estas ediciones. Por lo tanto, tendrá que tomar un enfoque diferente para hacer copia de seguridad de las bases de datos al utilizar estas ediciones.   

Tendrá que seguir estos tres pasos para hacer la copia de seguridad de las bases de datos de SQL Server con el programador de tareas de Windows:

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

/ / 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.)

Uso [master]

GO 

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

SET ANSI_NULLS ON

Ir

SET QUOTED_IDENTIFIER ON

Ir

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

--Autor: Microsoft

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

--Descripción: bases de datos de copia de seguridad de SQLExpress

--parámetro1: databaseName

--parámetro2: tipoDeCopiaDeSeguridad F = completo, D = diferencial, L = registro de

--parámetro3: ubicación del archivo de copia de seguridad

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

CREATE PROCEDURE [dbo]. [sp_BackupDatabases]

@ sysname databaseName = null,

@ tipoDeCopiaDeSeguridad char (1),

@ ubicación 200

AS

SET NOCOUNT ON; 

           

            DECLARE @DBs TABLE

            (

                  ID int IDENTITY PRIMARY KEY,

                  DBNAME nvarchar(500)

            )

           

             -- Pick out only databases which are online in case ALL databases are chosen to be backed up

             -- If specific database is chosen to be backed up only pick that out from @DBs

            INSERT INTO @DBs (DBNAME)

            SELECT Name FROM master.sys.databases

            where state=0

            AND name=@DatabaseName

            OR @DatabaseName IS NULL

            ORDER BY Name

           

            -- Filter out databases which do not need to backed up

            IF @backupType='F'

                  BEGIN

                  Eliminar @ DBs donde DBNAME IN ('tempdb', 'Northwind', 'pubs', 'AdventureWorks')

final

ELSE IF @ tipoDeCopiaDeSeguridad = tenía '

comenzar

eliminar @ DBs donde DBNAME IN ('tempdb', 'Northwind', 'pubs', 'original', 'AdventureWorks')

final

ELSE IF @ tipoDeCopiaDeSeguridad = 'L'

comenzar

eliminar @ DBs donde DBNAME IN ('tempdb', 'Northwind', 'pubs', 'original', 'AdventureWorks')

final

ELSE

comenzar

devolver

END

--declarar las variables

DECLARE @ nombreCopiaSeguridad varchar (100)

DECLARE @ BackupFile varchar (100)

DECLARE @ DBNAME varchar(300)

DECLARE @ sqlCommand tipo

DECLARE @ dateTime nvarchar (20)

int DECLARE @ bucle

--recorrer uno a uno de las bases de datos

seleccione @ bucle = min(ID) FROM @ DBs

WHILE @ bucle no es nulo

comenzar

--los nombres de base de datos deben estar en formato de [dbname] ya que algunos tienen - o _ en su nombre

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

--establecer el formato de yyyyhhmmss n de fecha y hora actual

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

: crear el nombre de archivo de copia de seguridad en formato de path\filename.extension para las copias de seguridad completa, diff y de registro

si @ tipoDeCopiaDeSeguridad = 'F'

establecer @ BackupFile = @ ubicación + REPLACE (reemplazar (@ DBNAME, ' [', ''),'] ','') + '_FULL_' + @ dateTime + '.BAK'

ELSE IF @ tipoDeCopiaDeSeguridad = tenía '

establecer @ BackupFile = @ ubicación + REPLACE (reemplazar (@ DBNAME, ' [', ''),'] ','') + '_DIFF_' + @ dateTime + '.BAK'

ELSE IF @ tipoDeCopiaDeSeguridad = 'L'

establecer @ BackupFile = @ ubicación + REPLACE (reemplazar (@ DBNAME, ' [', ''),'] ','') + '_LOG_' + @ dateTime + '.TRN'

: proporcionar un nombre para almacenar en el medio de la copia de seguridad

si @ tipoDeCopiaDeSeguridad = 'F'

establecer @ nombreCopiaSeguridad = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' copia de seguridad completa para ' + @ dateTime

IF @ tipoDeCopiaDeSeguridad = tenía '

establecer @ nombreCopiaSeguridad = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' copia de seguridad diferencial para ' + @ dateTime

si @ tipoDeCopiaDeSeguridad = 'L'

establecer @ nombreCopiaSeguridad = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' la copia de seguridad para ' + @ dateTime

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

si @ tipoDeCopiaDeSeguridad 'F' =

comenzar

SET @ sqlCommand = 'Base de datos de copia de seguridad' +@DBNAME+ ' en disco = '''+@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

 

-- Execute the generated SQL command

       EXEC(@sqlCommand)

 

-- Goto the next database

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

final

Paso B: en un editor de texto, cree un archivo por lotes denominado Sqlbackup.bat y, a continuación, copie el texto de uno de los siguientes ejemplos en ese archivo, dependiendo de su situación:

Ejemplo1:Backups completos de todas las bases de datos en la instancia con nombre local de SQLEXPRESS mediante la autenticación de Windows

/ / Sqlbackup.bat

sqlcmd -S. ? e \EXPRESS -Q "EXEC sp_BackupDatabases @ ubicación = tenía: \SQLBackups\', @ tipoDeCopiaDeSeguridad = 'F'" 

Ejemplo2: copias de seguridad diferenciales de todas las bases de datos en la instancia con nombre local de SQLEXPRESS con un SQLLogin y su contraseña

/ / Sqlbackup.bat

Sqlcmd - U SQLLogin -P contraseña -S. \SQLEXPRESS -Q "EXEC sp_BackupDatabases @ ubicación = 'D:\SQLBackups', @ tipoDeCopiaDeSeguridad = tenía '"

Nota: SQLLogin la shouldhave al menos la función de operador de copia de seguridad en SQL Server.

Ejemplo 3:Copias de seguridad de registro de todas las bases de datos en una instancia con nombre local 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 con nombre local de SQLEXPRESS mediante la autenticación de Windows

/ / Sqlbackup.bat

Sqlcmd -S. \SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @ ubicación = tenía: \SQLBackups\', @ databaseName = 'USERDB', @ tipoDeCopiaDeSeguridad = 'F'"

De forma similar, puede hacer un diferencial copia de seguridad de USERDB pegando en sería ' para el parámetro @ tipoDeCopiaDeSeguridad y una copia de seguridad del registro de USERDB pegando en 'L' para el parámetro @ tipoDeCopiaDeSeguridad .

Paso C:Programación de un trabajo utilizando el programador de tareas de Windows para ejecutar el archivo por lotes que creó en el paso B. Para ello, siga estos pasos:

1. En el equipo que ejecuta de SQL Server Express, haga clic en Inicio, seleccione Todos los programas, Accesorios, Herramientas del sistemay, a continuación, haga clic en tareas programadas. 
2. Haga doble clic en Agregar tarea programada. 
3. En el Asistente para tarea programada, haga clic en siguiente. 
4. haga clic en Examinar, haga clic en el archivo por lotes que creó en el paso b y, a continuación, haga clic en Abrir. 
5. Escriba SQLBACKUP para el nombre de la tarea, haga clic en diariamentey, a continuación, haga clic en siguiente. 
6. Especifique información de una programación para ejecutar la tarea. (Se recomienda ejecutar esta tarea al menos una vez al día). A continuación, haga clic en siguiente.
7. En el campo Escriba el nombre de usuario , escriba un nombre de usuario y, a continuación, escriba una contraseña en el campo Escriba la contraseña .
   
   Nota Este usuario debe por lo menos asignarse la función de BackupOperator en el nivel de SQL Server si utiliza uno de los archivos de proceso por lotes en ejemplo 1, 3 ó 4.
8. Haga clic en siguientey, a continuación, haga clic en Finalizar. 
9. Ejecutar la tarea programada al menos una vez para asegurarse de que la copia de seguridad se ha creado correctamente.

   NotaLa carpeta para el ejecutable SQLCMD es generalmente en las variables de ruta de acceso para el servidor después de SQL Server está instalado, pero si la variable Path no muestra esta carpeta, puede encontrarla en la ubicación de instalación de < > \90\Tools\Binn (por ejemplo: Server\90\Tools\Binn de SQL de programa\Microsoft c:\Archivos).

   Tener en cuenta lo siguiente cuando utilice el procedimiento que se documenta en este artículo:

   • Debe ejecutar el servicio Programador de tareas de Windows en el momento en que el trabajo está programado para ejecutarse. Se recomienda que establezca el tipo de inicio para este servicio como automático. Esto garantiza que el servicio se ejecutará incluso en un reinicio.
   • Existe debe ser una gran cantidad de espacio en la unidad a la que se escriben las copias de seguridad. Le recomendamos que limpie los archivos antiguos en la carpeta de copia de seguridad con regularidad para asegurarse de que no se queda sin espacio en disco. La secuencia de comandos no contiene la lógica para limpiar los archivos antiguos.

   Referencias adicionales

   • Introducción al programador de tareas (Windows Server 2008 R2, Windows Vista)
   • Cómo programar tareas en Windows XP
   • </SPAN style="LINE-HEIGHT: 115%; FONT-FAMILY: 'verdana','sans-serif'; FONT-SIZE:>