Microsoft SQL Server, Wiederherstellung eines Backups einer Datenbank mit einem Befehl

Question

Wenn wir eine Datenbank aus der Produktion herunterkopieren, erstellen wir eine Sicherungskopie der Datenbank, zippen sie und kopieren die Sicherungskopie herunter. Dann müssen wir die Datenbank über die grafische Benutzeroberfläche von SQL Server wiederherstellen, wobei wir uns durch mehrere Menüs und Fenster bewegen müssen. Soweit ich weiß, kann man das nicht mit den in SQL Server eingebauten gespeicherten Prozeduren machen, weil man nicht weiß, welche logical filename der Datenbank (die für die Wiederherstellung erforderlich ist). Die Abfrage besteht also aus folgendem Vorgehen:

RESTORE FILELISTONLY
FROM DISK = 'C:\backup_of_production_database.bak'
GO

Die obigen Angaben liefern die logischen Dateinamen aus der Sicherungsdatei, die Sie dann in der nächsten Abfrage verwenden müssen...

RESTORE DATABASE NewDevelopmentDatabase
FROM DISK = 'C:\backup_of_production_database.bak'
WITH MOVE 'YourMDFLogicalName' TO 'C:\mssql\data\DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'C:\mssql\data\DataYourLDFFile.mdf'

Wie Sie sehen können, scheint dies ineffizient zu sein, da Sie die logischen Dateinamen manuell in die nächste Abfrage eingeben müssen.

Meine Lösung für dieses Problem finden Sie unten als Antwort.

Answer 1

Hier ist ein SQL-Skript, das eine Datenbank wiederherstellen kann, ohne dass eine Interaktion erforderlich ist. Geben Sie einfach Ihre "Quelldatenbank" und Ihre "Zieldatenbank" ein - das Skript erledigt den Rest :)

SET NOCOUNT ON;

DECLARE 
    @MySourceDatabase NVarchar(1000),
    @MyDestinationDatabase NVarchar(100),
    @DeviceFrom NVarchar(1000),
    @DeviceTo NVarchar(1000),
    @LogicalName NVarchar(1000),
    @PhysicalName NVarchar(1000),
    @SQL NVarchar(MAX),
    @RowsToProcess integer,
    @CurrentRow integer,
    @Comma NVarchar(25);

--SOURCE DATABASE (DATABASE TO RESTORE)
SET @MySourceDatabase = 'D:\Backups\backup_db.bak';

--DESTINATION DATABASE (DATABASE TO RESTORE TO)
SET @MyDestinationDatabase = 'mydatabase_db';

SELECT @DeviceFrom = SUBSTRING(physical_name, 1,
CHARINDEX(@MyDestinationDatabase + '.mdf',
physical_name) - 1) 
FROM master.sys.master_files
WHERE name = @MyDestinationDatabase AND FILE_ID = 1;

SET @SQL = 'RESTORE DATABASE ' + @MyDestinationDatabase + ' FROM DISK = ''' + @MySourceDatabase + ''' WITH ';
SET @CurrentRow = 0;
SET @Comma = ',';

DECLARE @FileList TABLE (
    RowID int not null primary key identity(1,1)
    ,LogicalName NVARCHAR(128) 
    ,PhysicalName NVARCHAR(260) 
    ,Type CHAR(1) 
    ,FileGroupName NVARCHAR(128) 
    ,Size numeric(20,0) 
    ,MaxSize numeric(20,0) 
    ,FileId BIGINT 
    ,CreateLSN numeric(25,0) 
    ,DropLSN numeric(25,0) 
    ,UniqueId uniqueidentifier 
    ,ReadOnlyLSN numeric(25,0) 
    ,ReadWriteLSN numeric(25,0) 
    ,BackupSizeInBytes BIGINT 
    ,SourceBlockSize BIGINT 
    ,FilegroupId BIGINT 
    ,LogGroupGUID uniqueidentifier 
    ,DifferentialBaseLSN numeric(25) 
    ,DifferentialBaseGUID uniqueidentifier 
    ,IsReadOnly BIGINT 
    ,IsPresent BIGINT
    ,TDEThumbprint VARBINARY(32) -- Remove this line for SQL Server 2005
    );

INSERT INTO @FileList
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @MySourceDatabase + '''')
SET @RowsToProcess = @@RowCount;

WHILE @CurrentRow < @RowsToProcess
BEGIN
    SET @CurrentRow= @CurrentRow + 1;
    BEGIN
    IF @CurrentRow = @RowsToProcess
        SET @Comma = ',REPLACE';
    END
    SELECT @LogicalName = LogicalName,@PhysicalName = PhysicalName FROM @FileList WHERE RowID=@CurrentRow;
    SET @PhysicalName = Replace(@PhysicalName,@LogicalName,@MyDestinationDatabase);
    SET @SQL = @SQL + 'MOVE ''' + @LogicalName + ''' TO ''' + @PhysicalName + '''' + @Comma + '';
END

--PREVIEW THE GENERATED QUERY
SELECT @SQL;

--EXECUTE THE GENERATED QUERY
--EXEC(@SQL);

Es wird automatisch eine Abfrage wie diese erstellt:

RESTORE DATABASE mydatabase_db 
FROM DISK = 'D:\Backups\backup_db.bak' 
WITH 
MOVE 'backup_db' 
TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\mydatabase_db.mdf',
MOVE 'backup_db_log' 
TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\mydatabase_db.LDF',
REPLACE

Answer 2

Die Lösung:

Unter Verwendung verschiedener Ressourcen habe ich die folgende gespeicherte Prozedur entwickelt, mit der Sie diese Wiederherstellung auf einen Schritt reduzieren können. Ich hoffe, sie erweist sich für andere als ebenso nützlich wie für mich selbst.

ALTER PROCEDURE [dbo].[sp_makedev] 
    @backupfile sysname,
    @newdatabase sysname
AS
BEGIN

DECLARE @fname VARCHAR(200) 
DECLARE @dirfile VARCHAR(300) 
DECLARE @LogicalName NVARCHAR(128) 
DECLARE @PhysicalName NVARCHAR(260) 
DECLARE @type CHAR(1) 
DECLARE @sql NVARCHAR(1000) 
DECLARE @mdfFilePath  varchar(1000)
DECLARE @ldfFilePath varchar(1000)

CREATE TABLE #dbfiles( 
 LogicalName NVARCHAR(128) 
,PhysicalName NVARCHAR(260) 
,Type CHAR(1) 
,FileGroupName NVARCHAR(128) 
,Size numeric(20,0) 
,MaxSize numeric(20,0) 
,FileId INT 
,CreateLSN numeric(25,0) 
,DropLSN numeric(25,0) 
,UniqueId uniqueidentifier 
,ReadOnlyLSN numeric(25,0) 
,ReadWriteLSN numeric(25,0) 
,BackupSizeInBytes INT 
,SourceBlockSize INT 
,FilegroupId INT 
,LogGroupGUID uniqueidentifier 
,DifferentialBaseLSN numeric(25) 
,DifferentialBaseGUID uniqueidentifier 
,IsReadOnly INT 
,IsPresent INT 
)

set @mdfFilePath = ''c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data''
set @ldfFilePath = ''c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data''
set @sql = ''RESTORE DATABASE '' + @newdatabase + '' FROM DISK = '''''' + @backupfile + '''''' WITH MOVE '' 

DECLARE dbfiles CURSOR FOR 
SELECT LogicalName, PhysicalName, [type] FROM #dbfiles 

INSERT #dbfiles 
EXEC(''RESTORE FILELISTONLY FROM DISK = '''''' + @backupfile + '''''''') 

OPEN dbfiles 
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type 
WHILE @@FETCH_STATUS = 0 
BEGIN 
IF @type = ''D'' 
    SET @sql = @sql + '''''''' + @LogicalName + '''''' TO '''''' + @mdfFilePath + ''\'' + @newdatabase  + ''.mdf'''', MOVE '' 
ELSE IF @type = ''L'' 
    SET @sql = @sql + '''''''' + @LogicalName + '''''' TO '''''' +  @ldfFilePath + ''\'' + @newdatabase  + ''.ldf'''''' 

FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type 
END 

CLOSE dbfiles
DEALLOCATE dbfiles
EXEC(@SQL)
END

Ich bin sicher, dass einige Dinge an dieser Abfrage verbessert werden können, aber ich habe schon genug Zeit damit verschwendet, diese Lösung zu finden. Trotzdem würde ich mich über ein Feedback freuen. Ich hoffe, dass andere dies nützlich finden!