Entièrement automatisé de Restauration SQL Server

Question

Je ne suis pas très à l'aise avec des commandes SQL Server.

J'ai besoin d'un script pour restaurer une base de données à partir d'un .bak fichier et déplacez le logical_data et logical_log fichiers à un chemin spécifique.

Je peux faire:

restore filelistonly from disk='D:\backups\my_backup.bak'

Cela va me donner un ensemble de résultats avec une colonne LogicalName, ensuite, j'ai besoin d'utiliser les noms logiques du jeu de résultats dans la commande de restauration:

restore database my_db_name from disk='d:\backups\my_backups.bak' with file=1,
move 'logical_data_file' to 'd:\data\mydb.mdf',
move 'logical_log_file' to 'd:\data\mylog.ldf'

Comment puis-je capturer les noms logiques du premier ensemble de résultats dans des variables qui peuvent être fournis à la commande "move"?

Je pense que la solution serait peut-être trivial, mais je suis assez nouveau à SQL Server.

Answer 1

Voici, entièrement automatisé et de restauration de T-SQL, procédures stockées. Accepte trois(3) des paramètres.

1. Base De Données Cible
2. Source Base De Données
3. Pleinement Qualifié du nom du fichier de sauvegarde emplacement
   (\\yourserver\yourshare\backupfile.bak ou tout simplement c:\backup.bak)

CREATE PROC [dbo].[restoreDB]
    @p_strDBNameTo SYSNAME,
    @p_strDBNameFrom SYSNAME,
    @p_strFQNRestoreFileName VARCHAR(255)
AS 
    DECLARE 
        @v_strDBFilename VARCHAR(100),
        @v_strDBLogFilename VARCHAR(100),
        @v_strDBDataFile VARCHAR(100),
        @v_strDBLogFile VARCHAR(100),
        @v_strExecSQL NVARCHAR(1000),
        @v_strExecSQL1 NVARCHAR(1000),
        @v_strMoveSQL NVARCHAR(4000),
        @v_strREPLACE NVARCHAR(50),
        @v_strTEMP NVARCHAR(1000),
        @v_strListSQL NVARCHAR(4000),
        @v_strServerVersion NVARCHAR(20)

    SET @v_strREPLACE = ''   
    IF exists (select name from sys.databases where name = @p_strDBNameTo)
        SET @v_strREPLACE = ', REPLACE'

    SET @v_strListSQL = ''
    SET @v_strListSQL = @v_strListSQL + 'IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''##FILE_LIST''))'
    SET @v_strListSQL = @v_strListSQL + 'BEGIN'
    SET @v_strListSQL = @v_strListSQL + '   DROP TABLE ##FILE_LIST '
    SET @v_strListSQL = @v_strListSQL + 'END '

    SET @v_strListSQL = @v_strListSQL + 'CREATE TABLE ##FILE_LIST ('
    SET @v_strListSQL = @v_strListSQL + '   LogicalName VARCHAR(64),'
    SET @v_strListSQL = @v_strListSQL + '   PhysicalName VARCHAR(130),'
    SET @v_strListSQL = @v_strListSQL + '   [Type] VARCHAR(1),'
    SET @v_strListSQL = @v_strListSQL + '   FileGroupName VARCHAR(64),'
    SET @v_strListSQL = @v_strListSQL + '   Size DECIMAL(20, 0),'
    SET @v_strListSQL = @v_strListSQL + '   MaxSize DECIMAL(25,0),'
    SET @v_strListSQL = @v_strListSQL + '   FileID bigint,'
    SET @v_strListSQL = @v_strListSQL + '   CreateLSN DECIMAL(25,0),'
    SET @v_strListSQL = @v_strListSQL + '   DropLSN DECIMAL(25,0),'
    SET @v_strListSQL = @v_strListSQL + '   UniqueID UNIQUEIDENTIFIER,'
    SET @v_strListSQL = @v_strListSQL + '   ReadOnlyLSN DECIMAL(25,0),'
    SET @v_strListSQL = @v_strListSQL + '   ReadWriteLSN DECIMAL(25,0),'
    SET @v_strListSQL = @v_strListSQL + '   BackupSizeInBytes DECIMAL(25,0),'
    SET @v_strListSQL = @v_strListSQL + '   SourceBlockSize INT,'
    SET @v_strListSQL = @v_strListSQL + '   filegroupid INT,'
    SET @v_strListSQL = @v_strListSQL + '   loggroupguid UNIQUEIDENTIFIER,'
    SET @v_strListSQL = @v_strListSQL + '   differentialbaseLSN DECIMAL(25,0),'
    SET @v_strListSQL = @v_strListSQL + '   differentialbaseGUID UNIQUEIDENTIFIER,'
    SET @v_strListSQL = @v_strListSQL + '   isreadonly BIT,'
    SET @v_strListSQL = @v_strListSQL + '   ispresent BIT'

    SELECT @v_strServerVersion = CAST(SERVERPROPERTY ('PRODUCTVERSION') AS NVARCHAR)

    IF @v_strServerVersion LIKE '10.%' 
        BEGIN
            SET @v_strListSQL = @v_strListSQL + ', TDEThumbpr DECIMAL'
            --PRINT @v_strServerVersion
        END

    SET @v_strListSQL = @v_strListSQL + ')'

    EXEC (@v_strListSQL)


    INSERT INTO ##FILE_LIST EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @p_strFQNRestoreFileName + '''')

    DECLARE curFileLIst CURSOR FOR 
        SELECT 'MOVE N''' + LogicalName + ''' TO N''' + replace(PhysicalName, @p_strDBNameFrom, @p_strDBNameTo) + ''''
          FROM ##FILE_LIST

    SET @v_strMoveSQL = ''

    OPEN curFileList 
    FETCH NEXT FROM curFileList into @v_strTEMP
    WHILE @@Fetch_Status = 0
    BEGIN
        SET @v_strMoveSQL = @v_strMoveSQL + @v_strTEMP + ', '
        FETCH NEXT FROM curFileList into @v_strTEMP
    END

    CLOSE curFileList
    DEALLOCATE curFileList

    PRINT 'Killing active connections to the "' + @p_strDBNameTo + '" database'

    -- Create the sql to kill the active database connections
    SET @v_strExecSQL = ''
    SELECT   @v_strExecSQL = @v_strExecSQL + 'kill ' + CONVERT(CHAR(10), spid) + ' '
    FROM     master.dbo.sysprocesses
    WHERE    DB_NAME(dbid) = @p_strDBNameTo AND DBID <> 0 AND spid <> @@spid

    EXEC (@v_strExecSQL)

    PRINT 'Restoring "' + @p_strDBNameTo + '" database from "' + @p_strFQNRestoreFileName + '" with '
    PRINT '  data file "' + @v_strDBDataFile + '" located at "' + @v_strDBFilename + '"'
    PRINT '  log file "' + @v_strDBLogFile + '" located at "' + @v_strDBLogFilename + '"'

    SET @v_strExecSQL = 'RESTORE DATABASE [' + @p_strDBNameTo + ']'
    SET @v_strExecSQL = @v_strExecSQL + ' FROM DISK = ''' + @p_strFQNRestoreFileName + ''''
    SET @v_strExecSQL = @v_strExecSQL + ' WITH FILE = 1,'
    SET @v_strExecSQL = @v_strExecSQL + @v_strMoveSQL
    SET @v_strExecSQL = @v_strExecSQL + ' NOREWIND, '
    SET @v_strExecSQL = @v_strExecSQL + ' NOUNLOAD '
    SET @v_strExecSQL = @v_strExecSQL + @v_strREPLACE


    --PRINT '---------------------------'
    --PRINT @v_strExecSQL
    --PRINT '---------------------------'


    EXEC sp_executesql @v_strExecSQL

Answer 2

RESTAURER FILELISTONLY produit un résultat qui est documenté dans la MSDN. Vous devez ensuite effectuer une itération cet ensemble de résultats et de construire une RESTAURATION approprié ... BOUGER... Comment vous capture et de réitérer le résultat dépend de votre environnement. Dans une application C# vous utilisez un SqlDataReader. Dans la pure T-SQL, vous utilisez INSERT ... EXEC.

Le squelette d'un pur SQL solution serait:

declare @filelist table (LogicalName nvarchar(128), PhysicalName nvarchar(260), Type char(1), ...);
insert into @filelist exec sp_executesql N'restore filelistonly from disk=''D:\backups\my_backup.bak''';

set @sql = N'RESTORE database my_database from disk ''D:\backups\my_backup.bak'' with ';
select @sql = @sql + N' move ' + LogicalName + N' to ' udf_localFilePath(PhysicalName) + N','
from @filelist;

set @sql = substring(@sql, 1, len(@sql)-1); -- remove last ','
exec sp_executesql @sql;

Ce n'est pas le code de travail effectif, mais juste pour vous faire une idée. Vous pouvez également utiliser un curseur au lieu de la non-affectation standard-l'intérieur-de construction de requête de @sql

Answer 3

À l'aide de

• http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp
• http://weblogs.sqlteam.com/dang/archive/2009/06/13/Restore-Database-Stored-Procedure.aspx

comme références, je suis venu avec cette .. et je pense que cela fonctionne (pas testé pour les sauvegardes avec plusieurs fichiers)

DECLARE @FileList TABLE
      (
      LogicalName nvarchar(128) NOT NULL,
      PhysicalName nvarchar(260) NOT NULL,
      Type char(1) NOT NULL,
      FileGroupName nvarchar(120) NULL,
      Size numeric(20, 0) NOT NULL,
      MaxSize numeric(20, 0) NOT NULL,
      FileID bigint NULL,
      CreateLSN numeric(25,0) NULL,
      DropLSN numeric(25,0) NULL,
      UniqueID uniqueidentifier NULL,
      ReadOnlyLSN numeric(25,0) NULL ,
      ReadWriteLSN numeric(25,0) NULL,
      BackupSizeInBytes bigint NULL,
      SourceBlockSize int NULL,
      FileGroupID int NULL,
      LogGroupGUID uniqueidentifier NULL,
      DifferentialBaseLSN numeric(25,0)NULL,
      DifferentialBaseGUID uniqueidentifier NULL,
      IsReadOnly bit NULL,
      IsPresent bit NULL,
      TDEThumbprint varbinary(32) NULL
 );

 declare @RestoreStatement nvarchar(max), @BackupFile nvarchar(max);

 set @BackupFile = 'D:\mybackup.bak'

 SET @RestoreStatement =  N'RESTORE FILELISTONLY
      FROM DISK=N''' + @BackupFile + ''''

INSERT INTO @FileList
      EXEC(@RestoreStatement);

declare @logical_data nvarchar(max), @logical_log nvarchar(max);

set @logical_data = (select LogicalName from @FileList where Type = 'D' and FileID = 1)
set @logical_log = (select LogicalName from @FileList where Type = 'L' and FileID = 2)

Answer 4

/*
Automate restore w/o needing to know the logical file names.
Specify destination database name, database backup source filename
and .MDF, .LDF and .NDF directories.
I do nightly automated database restores,
and I've been using this code for about a month.
Works for sql server 2008, might work for 2005.
Created by wtm 5/27/2010
*/

 -- BEGIN - MODIFY THIS CODE - create a blank db
if not exists(select * from master.sys.databases where [name]='sc')
begin
    create database sc
end
go
-- END - MODIFY THIS CODE - create a blank db

declare @strDatabase varchar(130)='sc' -- MODIFY THIS LINE - db name
declare @strBackupFile varchar(500)='c:\docs\db-backups\sc.bak' -- MODIFY THIS LINE - source db backup file
declare @strRestoreMDFFilesTo varchar(500)='c:\docs\sqldata\' -- MODIFY THIS LINE - destination restore directory for main files
declare @strRestoreLDFFilesTo varchar(500)='c:\docs\sqldata\' -- MODIFY THIS LINE - destination restore directory for tlog files
declare @strRestoreNDFFilesTo varchar(500)='c:\docs\sqldata\' -- MODIFY THIS LINE - destination restore directory for non-main files

-- other variables used
declare @strSQL nvarchar(max)
declare @strOriginalPhysicalName varchar(150)
declare @strPhysicalName varchar(150)
declare @strLogicalName varchar(150)
declare @intReturn int

-- begin restoring
begin try
    drop table #tmpFilelist
end try
begin catch
end catch
create table #tmpFilelist (
    LogicalName varchar(64), PhysicalName varchar(130), [Type] varchar(1), FileGroupName varchar(64), Size decimal(20, 0)
    ,MaxSize decimal(25, 0), FileID bigint, CreateLSN decimal(25,0), DropLSN decimal(25,0), UniqueID uniqueidentifier
    ,ReadOnlyLSN decimal(25,0), ReadWriteLSN decimal(25,0), BackSizeInBytes decimal(25,0), SourceBlockSize int
    ,filegroupid int, loggroupguid uniqueidentifier, differentialbaseLSN decimal(25,0), differentialbaseGUID uniqueidentifier
    ,isreadonly bit, ispresent bit, TDEThumbpr decimal
)
if not exists(select * from sc.sys.tables) or exists(select * from sc.sys.tables where [name]='not-an-original-table') -- MODIFY THIS LINE - business logic to see if we need to restore the database at all
begin
    print 'Restoring '+@strDatabase+' db ...'
    use master
    exec msdb.dbo.sp_delete_database_backuphistory @database_name = @strDatabase
    use [master]
    exec('alter database '+@strDatabase+' set single_user with rollback immediate')
    use [master]
    exec('drop database '+@strDatabase)
    insert into #tmpFilelist
        exec('restore filelistonly from disk = '''+@strBackupFile+'''')
    set @strSQL='restore database ['+@strDatabase+'] from disk='''+@strBackupFile+''' with '
    set @strSQL=@strSQL+ 'file=1 '
    set @strSQL=@strSQL+ ',nounload '
    set @strSQL=@strSQL+ ',replace '
    set @strSQL=@strSQL+ ',stats=10 ' -- show restore status every 10%
    while exists(select * from #tmpFilelist)
    begin
        select top 1 @strOriginalPhysicalName=PhysicalName, @strLogicalName=LogicalName from #tmpFilelist
        set @strPhysicalName=@strOriginalPhysicalName
        set @strPhysicalName=reverse(@strPhysicalName)
        set @strPhysicalName=left(@strPhysicalName, charindex('\', @strPhysicalName)-1)
        set @strPhysicalName=reverse(@strPhysicalName)
        set @strPhysicalName=replace(@strPhysicalName, '.', '_'+@strDatabase+'.')
        if @strPhysicalName like '%.mdf'
            set @strPhysicalName=@strRestoreMDFFilesTo+@strPhysicalName
        else if @strPhysicalName like '%.ldf'
            set @strPhysicalName=@strRestoreLDFFilesTo+@strPhysicalName
        else
            set @strPhysicalName=@strRestoreNDFFilesTo+@strPhysicalName
        set @strSQL=@strSQL+ ',move '''+@strLogicalName+''' to '''+@strPhysicalName+''' '
        delete from #tmpFilelist where PhysicalName=@strOriginalPhysicalName
    end
    execute @intReturn=sp_executesql @strSQL
end

Answer 5

J'ai eu le même problème, mais dans mon environnement, j'ai de nombreux fichiers de sauvegarde (des sauvegardes plus rapides), et n'a besoin de restaurer à l'emplacement personnalisé. Cette requête est la dernière sauvegarde complète d'infos et rétablit le chemin que vous spécifiez. Testé sur SQL 2005/2008.

SET NOCOUNT ON

Declare @BackupFiles varchar(500), @data_file_path VARCHAR(512), @log_file_path VARCHAR(512), @RestoreFileList varchar(2000), @RestoreStatement varchar(3000), @MoveFiles varchar(2000), @DBName varchar(150)

DECLARE @filelist TABLE (LogicalName NVARCHAR(128) NOT NULL, PhysicalName NVARCHAR(260) NOT NULL, [Type] CHAR(1) NOT NULL, FileGroupName NVARCHAR(120) NULL, Size NUMERIC(20, 0) NOT NULL, MaxSize NUMERIC(20, 0) NOT NULL, FileID BIGINT NULL, CreateLSN NUMERIC(25,0) NULL, DropLSN NUMERIC(25,0) NULL, UniqueID UNIQUEIDENTIFIER NULL, ReadOnlyLSN NUMERIC(25,0) NULL , ReadWriteLSN NUMERIC(25,0) NULL, BackupSizeInBytes BIGINT NULL, SourceBlockSize INT NULL, FileGroupID INT NULL, LogGroupGUID UNIQUEIDENTIFIER NULL, DfferentialBaseLSN NUMERIC(25,0)NULL, DifferentialBaseGUID UNIQUEIDENTIFIER NULL, IsReadOnly BIT NULL, IsPresent BIT NULL, TDEThumbprint VARBINARY(32) NULL) 

SET @data_file_path = 'E:\SQLData\' 
SET @log_file_path  = 'E:\SQLLog\' 
SET @DBName = 'Adventureworks'

--Get last full backup:
SELECT @BackupFiles=Coalesce(@BackupFiles + ',', '') + 'DISK = N'''+physical_device_name+''''
FROM msdb..backupset S
JOIN msdb..backupmediafamily M ON M.media_set_id=S.media_set_id
WHERE backup_set_id = ( SELECT max(backup_set_id)
                    FROM msdb..backupset S
                    JOIN msdb..backupmediafamily M ON M.media_set_id=S.media_set_id
                    WHERE S.database_name = @DBName and Type = 'D')

SELECT @RestoreFileList= 'RESTORE FILELISTONLY FROM ' + @BackupFiles + ' WITH  FILE = 1 '

IF (@@microsoftversion / 0x1000000) & 0xff >= 10 --TDE capability
Begin
    INSERT into @filelist (LogicalName,PhysicalName,Type,FileGroupName,Size,MaxSize,FileID,CreateLSN,DropLSN,UniqueID,ReadOnlyLSN,ReadWriteLSN,BackupSizeInBytes,SourceBlockSize,FileGroupID,LogGroupGUID,DfferentialBaseLSN,DifferentialBaseGUID,IsReadOnly,IsPresent,TDEThumbprint)
    EXEC (@RestoreFileList)
End
Else
Begin
    INSERT into @filelist (LogicalName,PhysicalName,Type,FileGroupName,Size,MaxSize,FileID,CreateLSN,DropLSN,UniqueID,ReadOnlyLSN,ReadWriteLSN,BackupSizeInBytes,SourceBlockSize,FileGroupID,LogGroupGUID,DfferentialBaseLSN,DifferentialBaseGUID,IsReadOnly,IsPresent)
    EXEC (@RestoreFileList)
End

--next version, do a count on filename, any >1 put in alternate data/log location.
SELECT  @MoveFiles=Coalesce(@MoveFiles + ',' , '') + 'MOVE N''' + LogicalName + ''' to N''' +
    Case When type = 'D' Then @data_file_path+Right(physicalname, charindex('\',reverse(physicalname),1)-1)
    when type = 'L' Then @log_file_path+Right(physicalname, charindex('\',reverse(physicalname),1)-1)
    Else 'Full Text - code not complete'
    END
    +''''
From @filelist

SELECT @RestoreStatement='RESTORE DATABASE [AuctionMain] FROM ' + @BackupFiles + ' WITH  FILE = 1, ' + @MoveFiles + ', NOUNLOAD, REPLACE, STATS = 20'

Print @RestoreStatement

Exec(@RestoreStatement)