Início / dba / Perguntas / 91223
Accepted
toryan
Asked: 2015-02-06 12:11:19 +0800 CST

Restaurar automaticamente o banco de dados do SQL Server a partir do arquivo de outro servidor

  • 772

Temos uma tarefa diária de sobrescrever vários bancos de dados de desenvolvimento usando backups dos bancos de dados de produção associados. Os backups são produzidos por planos de manutenção no servidor de produção e depois transferidos para o servidor de desenvolvimento por FTP. Todos os dias, executamos uma instrução SQL semelhante a esta para sobrescrever cada banco de dados:

RESTORE DATABASE [Database1] 
FROM DISK = N'D:\path\to\Database1_backup_2015_02_05_190004_7401803.bak'
WITH FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

Cada vez que executamos isso, temos que substituir o nome do arquivo pelo arquivo correto mais recente. Eu gostaria de automatizar isso de alguma forma para minimizar a chance de erro do operador. O problema é que não podemos controlar o nome do .bakarquivo (embora o formato seja consistente - nome do banco de dados, data, hora e qualquer que seja o número de sete dígitos), e a pasta geralmente contém vários dias de backups.

sql-server maintenance

3 respostas

  1. Best Answer

    Como todos os detalhes dos backups são mantidos no banco de dados msdb, você deve apenas extrair o nome do arquivo de backup do servidor de origem.

    Você pode criar um servidor vinculado a partir do seu servidor de desenvolvimento para acessar o banco de dados msdb do servidor de produção. Ou você pode usar OPENQUERY para consultar os mesmos dados. (OPENQUERY pode ser mais rápido, pois a consulta está realmente sendo executada no servidor de produção.)

    Por exemplo:

    SELECT * from OPENQUERY([LinkToPRD], 
     'EXEC database.dbo.ExecDailyRestore');

    Isso mostra a execução de um procedimento armazenado sem parâmetros, o que pode se adequar ao seu plano de restauração diário.

    Se você pesquisar "gerar automaticamente scripts de restauração do banco de dados do servidor SQL", encontrará muitos scripts. Um exemplo de Paul Brewer é sp_RestoreGeneque você pode usar como está ou como base para criar seu próprio ExecDailyRestoreprocedimento armazenado.

    https://paulbrewer.wordpress.com/sp_restoregene/

    Aqui estão os parâmetros suportados por sp_restoregene:

        @Database SYSNAME = NULL,
    @TargetDatabase SYSNAME = NULL,
    @WithMoveDataFiles VARCHAR(2000) = NULL,
    @WithMoveLogFile  VARCHAR(2000) = NULL,
    @FromFileFullUNC VARCHAR(2000) = NULL,
    @FromFileDiffUNC VARCHAR(2000) = NULL,
    @FromFileLogUNC VARCHAR(2000) = NULL,
    @StopAt DATETIME = NULL,
    @StandbyMode BIT = 0,
    @IncludeSystemDBs BIT = 0,
    @WithRecovery BIT = 0,
    @WithCHECKDB BIT = 0,
    @WithReplace BIT = 0,
    @UseDefaultDatabaseBackupPath BIT = 0,
    @Log_Reference VARCHAR (250) = NULL,
    @LogShippingVariableDeclare BIT = 1,
    @LogShippingStartTime DATETIME = NULL,
    @LogShippingLastLSN VARCHAR(25) = NULL

    E aqui está um script de amostra:

    RESTORE DATABASE db_workspace 
FROM DISK = 'X:\Backups\Temp\db_workspace.bak' WITH REPLACE, 
FILE = 1,CHECKSUM,NORECOVERY, STATS=10
, MOVE 'db_workspace' TO 'x:\data\db_workspace.mdf'
, MOVE 'db_workspace_log' TO 'x:\data\db_workspace_log.ldf'
, MOVE 'db_workspace_FG2' TO 'x:\data\db_workspace_FG2.ndf'
, MOVE 'db_workspace_FG1' TO 'x:\data\db_workspace_FG1.ndf'
    • 10

  2. Os DBAs aqui provavelmente vão jogar ovos e tomates em mim, mas vou jogar isso de qualquer maneira.

    Você pode usar um trabalho de serviços de integração para executar isso. Primeiro, crie um loop para cada arquivo e configure-o para ser executado para cada arquivo nesse local. Cópia de segurança

    Mapeie o resultado para uma variável. Variável

    Em seguida, crie e execute a tarefa SQL dentro do contêiner. Husa

    Consulta de mapa

    Feito isso, você poderá criar um trabalho SQL e agendá-lo adequadamente. Não estou dizendo que esta é a melhor solução, mas deve funcionar.

    FreeHand

    • 8

  3. Isso deve resolver o problema para você exatamente. Obviamente, se você estiver restaurando para um banco de dados em que o mdf/ldf tenha nomes diferentes, talvez seja necessário ajustar ligeiramente o comando de restauração final. Isso funciona apenas listando os backups em um diretório e escolhendo com base em um padrão. Acho que poderia ter feito do padrão uma variável também, mas você entendeu.

         DECLARE @FileName varchar(255), @PathToBackup varchar(255), @RestoreFilePath varchar(1000)

     DECLARE @Files TABLE (subdirectory varchar(255), depth int, [file] int)

     SET NOCOUNT ON

     SET @PathToBackup = 'D:\path\to'

     -- insert into our memory table using dirtree and a single file level
     INSERT INTO @Files
     EXEC master.dbo.xp_DirTree @PathToBackup,1,1

     SELECT TOP 1 
        @FileName = [subdirectory]
     FROM 
        @Files
     WHERE
        -- get where it is a file
        [file] = 1
     AND    
        subdirectory LIKE 'Database1_backup%.bak'
     ORDER BY
        -- order descending so newest file will be first by naming convention
        subdirectory DESC

    IF LEFT(REVERSE(@PathToBackup), 1) != '\'
    BEGIN
        SET @PathToBackup = @PathToBackup + '\'
    END

    SET @RestoreFilePath = @PathToBackup + @FileName

    SELECT @RestoreFilePath

    RESTORE DATABASE [Database1] 
    FROM DISK = @RestoreFilePath
    WITH FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
    • 5

relate perguntas