Como agendar e automatizar backups de bancos de dados SQL Server no SQL Server Express

SQL Server Express editions não oferecem uma maneira de agendar trabalhos ou planos de manutenção, porque o componente SQL Server Agent não está incluído nessas edições. Portanto, você precisa adotar uma abordagem diferente para fazer backup de seus bancos de dados quando você usar essas edições.   

Você tem três etapas para fazer backup de seus bancos de dados SQL Server usando o Agendador de tarefas do Windows:

Etapa um: uso SQL Server Management Studio Express ou Sqlcmd para criar o procedimento armazenado no banco de dados mestre:

/ / Copyright © Microsoft Corporation.  Todos os direitos reservados.

/ / Este código lançado sob os termos da

/ / Licença pública do Microsoft (MS-PL http://opensource.org/licenses/ms-pl.html.)

[Mestre] uso

GO 

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

SET ANSI_NULLS ON

Ir

SET QUOTED_IDENTIFIER ON

Ir

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

-Autor: Microsoft

-Data de criação: 06-02-2010

-Descrição: bancos de dados de Backup para SQLExpress

-Parâmetro1: databaseName

-Parâmetro2: TipoDeBackup F = completo, D = diferencial, L = log

-Parâmetro3: local do arquivo de backup

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

procedimento CREATE [dbo]. [sp_BackupDatabases]

@ sysname databaseName = null,

@ TipoDeBackup char (1),

@ backupLocation nvarchar(200)

como

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

                  Excluir @ DBs onde DBNAME em ('tempdb', 'Northwind', 'pubs', 'AdventureWorks')

final

ELSE IF @ TipoDeBackup = tinha '

começar

DELETE @ DBs onde DBNAME em ('tempdb', 'Northwind', 'pubs' 'mestre', 'AdventureWorks')

final

ELSE IF @ TipoDeBackup = 'l'

começar

DELETE @ DBs onde DBNAME em ('tempdb', 'Northwind', 'pubs' 'mestre', 'AdventureWorks')

final

ELSE

começar

retornar

final

-declarar variáveis

DECLARE @ NomeDoBackup varchar(100)

DECLARE @ BackupFile varchar(100)

DECLARE @ DBNAME varchar(300)

DECLARE @ sqlCommand NVARCHAR(1000)

DECLARE @ dateTime NVARCHAR(20)

@ Loop DECLARAR int

-Loop pelos bancos de dados, um por um

selecione @ Loop = min(ID) de @ DBs

ENQUANTO @ Loop É não nulo

começar

? têm nomes de banco de dados no formato [dbname], pois alguns têm - ou _ no nome de

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

- definir o formato de n yyyyhhmmss de data e hora atual

conjunto @ dateTime = substituir (converter (VARCHAR, GETDATE(),101),'/','') + '_' + substituir (converter (VARCHAR, GETDATE(),108),':','')

- - criar o arquivo backup no formato path\filename.extension para backups de log, comparação e completo

se @ TipoDeBackup = 'F'

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

ELSE IF @ TipoDeBackup = tinha '

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

ELSE IF @ TipoDeBackup = 'l'

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

? Forneça um nome para o armazenamento em mídia de backup

se @ TipoDeBackup = 'F'

definir @ NomeDoBackup = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' backup completo para ' + @ dateTime

se @ TipoDeBackup = tinha '

definir @ NomeDoBackup = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' backup diferencial para ' + @ dateTime

se @ TipoDeBackup = 'l'

definir @ NomeDoBackup = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' backup de log de ' + @ dateTime

- gerar o comando SQL dinâmico a ser executado

se @ TipoDeBackup = 'F'

começar

conjunto @ sqlCommand = 'BACKUP DATABASE' +@DBNAME+ ' para 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 > Loop @

final

Etapa B: em um editor de texto, crie um arquivo em lotes chamado Sqlbackup.bat e copie o texto de um dos exemplos a seguir no arquivo, dependendo do cenário:

Exemplo1:Backups completos de todos os bancos de dados na instância local nomeado do SQLEXPRESS usando a autenticação do Windows

/ / Sqlbackup.bat

sqlcmd -S. ? e \EXPRESS -Q "EXEC sp_BackupDatabases @ backupLocation = tinha: \SQLBackups\', @ TipoDeBackup = 'F'" 

Exemplo2: backups diferenciais de todos os bancos de dados na instância local nomeado do SQLEXPRESS usando um SQLLogin e sua senha

/ / Sqlbackup.bat

Sqlcmd - U SQLLogin -P senha -S. \SQLEXPRESS -Q "EXEC sp_BackupDatabases @ backupLocation = 'D:\SQLBackups', @ TipoDeBackup = tinha '"

Observação: O SQLLogin shouldhave pelo menos a função de operador de Backup SQL Server.

Exemplo 3:Backups de log de todos os bancos de dados local chamado instância SQLEXPRESS usando a autenticação do Windows

/ / Sqlbackup.bat

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

Exemplo 4:Backups completos do banco de dados USERDB na instância local nomeada do SQLEXPRESS usando a autenticação do Windows

/ / Sqlbackup.bat

Sqlcmd -S. \SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @ backupLocation = tinha: \SQLBackups\', @ databaseName = 'USERDB', @ TipoDeBackup = 'F'"

Da mesma forma, você pode fazer um diferencial backup de USERDB colando no seria ' para o parâmetro @ TipoDeBackup e um backup de log de USERDB, colando 'L' para o parâmetro @ TipoDeBackup .

Etapa c:Agendar um trabalho usando o Agendador de tarefas do Windows para executar o arquivo em lotes criado na etapa B. Para isso, siga estas etapas:

1. No computador que está executando o SQL Server Express, clique em Iniciar, aponte para Programas, aponte para Acessórios, aponte para Ferramentas do sistemae clique em tarefas agendadas. 
2. Clique duas vezes em Adicionar tarefa agendada. 
3. No Assistente de tarefa agendada, clique em Avançar. 
4. clique em Procurar, clique no arquivo em lotes criado na etapa b e clique em Abrir. 
5. Digite SQLBACKUP para o nome da tarefa, clique em diárioe clique em Avançar. 
6. Especifique informações de uma agenda executar a tarefa. (Recomendamos que você execute essa tarefa pelo menos uma vez por dia.) Em seguida, clique em Avançar.
7. No campo Digite o nome do usuário , digite um nome de usuário e digite uma senha no campo Digite a senha .
   
   Observação Este usuário menos deve atribuído a função BackupOperator no nível de SQL Server se você estiver usando um dos arquivos de lote no exemplo 1, 3 ou 4.
8. Clique em Avançare em Concluir. 
9. Execute a tarefa agendada pelo menos uma vez para certificar-se de que o backup é criado com êxito.

   Observaçãoa pasta para o executável SQLCMD é geralmente em variáveis de caminho para o servidor depois de SQL Server é instalado, mas se a variável Path não lista nesta pasta, pode encontrar em < local > \90\Tools\Binn (por exemplo: C:\Program Files\Microsoft SQL Server\90\Tools\Binn).

   Estar ciente do seguinte ao usar o procedimento documentado neste artigo:

   • O serviço Agendador de tarefas do Windows deve estar executando no momento em que o trabalho é agendado para execução. Recomendamos que você defina o tipo de inicialização para este serviço como automático. Isso garante que o serviço será executado mesmo em uma reinicialização.
   • Há deve ser muito espaço na unidade em que os backups estão sendo gravados. Recomendamos que você limpe os arquivos antigos na pasta de backup regularmente para certificar-se de que você não execute espaço em disco. O script não contém a lógica para limpar arquivos antigos.

   Referências adicionais

   • Visão geral sobre o Agendador de tarefas (Windows Server 2008 R2, Windows Vista)
   • Como agendar tarefas no Windows XP
   • </SPAN style="LINE-HEIGHT: 115%; FONT-FAMILY: 'verdana','sans-serif'; FONT-SIZE:>