Alle Datenbankbackups aus einem Ordner wiederherstellen

Hallo zusammen,

ich tippe mal, dass das Thema für viele schon ein alter Hut ist. Dennoch wollte ich ganz kurz darüber schreiben.

Folgender Hintergrund: Es soll ein neues Testsystem mit einer SQL Server 2016 Installation aufgebaut werden. Dabei sollen sämtliche Testdatenbanken, welche sich derzeit noch auf einem SQL Server 2008 R2 befinden, überführt werden.

Nun gibt es ja mehrere Wege, dies zu erreichen. Ich kann z.B. den Copy Database Wizard verwenden, welcher mir ein SSIS Paket erzeugt. Dieses Paket sorgt dann für das Kopieren der Datenbanken, offline (DETACH Methode) oder online (mit Hilfe von SMO’s). Generell bin ich aber irgendwie kein Fan von diesem Wizard und der SSIS Lösung. Zumal ich es irgendwie noch nie geschafft habe, dass ein Paket auf Anhieb durchläuft wenn zum Beispiel 10 oder mehr Datenbanken ausgewählt wurden.

Eine weitere Methode wäre die vorhandene Backuplösung zu verwenden. In der Regel hat man ja einen aktuellen Sicherungsstand im Zugriff und könnte so bequem die Datenbanken auf das neue System spielen.

Dies hat in meinem Fall aber nicht geklappt, da die eingesetzte Version des Backupprogrammes den SQL Server 2016 noch nicht unterstützt und somit eine Wiederherstellung nicht möglich ist.

Was habe ich also in dem Fall gemacht? Kurz Google bemüht und bei der Suche bin ich auf folgenden Artikel von Tibor Karaszi gestoßen:

„Restore all databases from a number of backup files“

Tibor stellt eine Prozedur bereit, welche aus einem vorgegebenen Ordner sämtliche Backupdateien ermittelt und mit Hilfe einiger temporären Tabellen und den Befehlen RESTORE HEADERONLY und RESTORE FILELISTONLY werden auf sehr einfache und schnelle Weise die eigentlichen Restore Skripte generiert. (Und Dank der Maintenance Solution von Ola Hallengren kann man auch schnell mal native „copy_only“ backups ziehen.)

Jedoch lässt sich die Prozedur nicht so ohne Weiteres auf einem SQL Server 2016 anwenden.  Mit dem SQL Server 2016 ist bei dem Befehl RESTORE FILELISTONLY ein weiteres Ausgabefeld namens SnapshotUrl hinzugekommen. welches beim Anlegen / Befüllen der temporären Tabelle noch nicht berücksichtigt ist.  Laut MSDN Doku beinhaltet das Feld die URL für den Azure Snapshot.

Lange Rede, kurzer Sinn. Hier ist die von mir angepasste Version von Tibors Prozedur:

CREATE PROCEDURE [dbo].[sp_RestoreFromAllFilesInDirectory]
 @SourceDirBackupFiles nvarchar(200), @DestDirDbFiles nvarchar(200), @DestDirLogFiles nvarchar(200)
AS
--Originally written by Tibor Karaszi 2004. Use at own risk.
--Restores from all files in a certain directory. Assumes that:
--  There's only one backup on each backup device.
--  Each database uses only two database files and the mdf file is returned first from the RESTORE FILELISTONLY command.  <<
--  edited by Dirk Hondong. Multiple Database files are supported (in a simple way)
--Sample execution:
-- EXEC sp_RestoreFromAllFilesInDirectory 'C:\Mybakfiles\', 'D:\Mydatabasesdirectory\' ,’C:\MylogDirectory\’
SET NOCOUNT ON
--Table to hold each backup file name in
CREATE TABLE #files(fname varchar(200),depth int, file_ int)
INSERT #files
EXECUTE master.dbo.xp_dirtree @SourceDirBackupFiles, 1, 1
--Table to hold the result from RESTORE HEADERONLY. Needed to get the database name out from
CREATE TABLE #bdev(
 BackupName nvarchar(128)
,BackupDescription nvarchar(255)
,BackupType smallint
,ExpirationDate datetime
,Compressed tinyint
,Position smallint
,DeviceType tinyint
,UserName nvarchar(128)
,ServerName nvarchar(128)
,DatabaseName nvarchar(128)
,DatabaseVersion bigint
,DatabaseCreationDate datetime
,BackupSize numeric(20,0)
,FirstLSN numeric(25,0)
,LastLSN numeric(25,0)
,CheckpointLSN numeric(25,0)
,DatabaseBackupLSN numeric(25,0)
,BackupStartDate datetime
,BackupFinishDate datetime
,SortOrder smallint
,[CodePage] smallint
,UnicodeLocaleId bigint
,UnicodeComparisonStyle bigint
,CompatibilityLevel tinyint
,SoftwareVendorId bigint
,SoftwareVersionMajor bigint
,SoftwareVersionMinor bigint
,SoftwareVersionBuild bigint
,MachineName nvarchar(128)
,Flags bigint
,BindingID uniqueidentifier
,RecoveryForkID uniqueidentifier
,Collation nvarchar(128)
,FamilyGUID uniqueidentifier
,HasBulkLoggedData bigint
,IsSnapshot bigint
,IsReadOnly bigint
,IsSingleUser bigint
,HasBackupChecksums bigint
,IsDamaged bigint
,BegibsLogChain bigint
,HasIncompleteMetaData bigint
,IsForceOffline bigint
,IsCopyOnly bigint
,FirstRecoveryForkID uniqueidentifier
,ForkPointLSN numeric(25,0)
,RecoveryModel nvarchar(128)
,DifferentialBaseLSN numeric(25,0)
,DifferentialBaseGUID uniqueidentifier
,BackupTypeDescription nvarchar(128)
,BackupSetGUID uniqueidentifier
,CompressedBackupSize bigint
,Containment bigint
)
--Table to hold result from RESTORE FILELISTONLY. Need to generate the MOVE options to the RESTORE command
CREATE TABLE #dbfiles(
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 int
,TDEThumbprint uniqueidentifier
,SnapshotUrl nvarchar (300)
)
DECLARE @fname varchar(200)
DECLARE @dirfile varchar(300)
DECLARE @LogicalName nvarchar(128)
DECLARE @PhysicalName nvarchar(260)
declare @fileid int
DECLARE @type char(1)
DECLARE @DbName sysname
DECLARE @sql nvarchar(2000)
DECLARE files CURSOR FOR
SELECT fname FROM #files
DECLARE dbfiles CURSOR FOR
SELECT LogicalName, PhysicalName, Type, FileId FROM #dbfiles
OPEN files
FETCH NEXT FROM files INTO @fname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dirfile = @SourceDirBackupFiles + @fname
--Get database name from RESTORE HEADERONLY, assumes there's only one backup on each backup file.
TRUNCATE TABLE #bdev
INSERT #bdev
EXEC('RESTORE HEADERONLY FROM DISK = ''' + @dirfile + '''')
SET @DbName = (SELECT DatabaseName FROM #bdev)
--Construct the beginning for the RESTORE DATABASE command
SET @sql = 'RESTORE DATABASE [' + @DbName + '] FROM DISK = ''' + @dirfile + ''' WITH REPLACE, MOVE '
--Get information about database files from backup device into temp table
TRUNCATE TABLE #dbfiles
INSERT #dbfiles
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @dirfile + '''')
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type, @fileid
--For each database file that the database uses
WHILE @@FETCH_STATUS = 0
BEGIN
IF @type = 'D' and @fileid = 1
SET @sql = @sql + '''' + @LogicalName + ''' TO ''' + @DestDirDbFiles + @DbName + '.mdf'', MOVE '
ELSE IF @type = 'D' and @fileid > 2
SET @sql = @sql + '''' + @LogicalName + ''' TO ''' + @DestDirDbFiles + @DbName + '.ndf'', MOVE '
ELSE IF @type = 'L'
SET @sql = @sql + '''' + @LogicalName + ''' TO ''' + @DestDirLogFiles + @DbName + '_log.ldf'''
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type, @fileid
END
--Here's the actual RESTORE command
PRINT @sql
--Remove the comment below if you want the procedure to actually execute the restore command.
--EXEC(@sql)
CLOSE dbfiles
FETCH NEXT FROM files INTO @fname
END
CLOSE files
DEALLOCATE dbfiles
DEALLOCATE files
GO

 

Ich hab Tibor vor einigen Wochen (ja, dieser Blogpost war schon etwas länger in der Pipeline) kontaktiert. Inzwischen hat er sich gemeldet und hat nun auch selbst eine 2016er Version bereit gestellt. Diese findet sich unter dem bereits oben erwähnten Link.

 

Jetzt muss ich aber das Thema noch etwas weiter ausführen, da es zu der „Problematik“ noch eine viel charmantere Lösung gibt: dbatools.io

Dies ist eine der tollsten PowerShelllösung, die ich seid kurzer Zeit kenne.

Wenn man Datenbanken von A nach B bringen muss, dann ist dass mit Hilfe der dbatools nur noch ein Einzeiler in Powershell und die Datenbanken werden per Backup/Restore überführt.

Oder man stellt, wenn man die Maintenance Solution von Ola verwendet, die Datenbanken einfach aus dem Verzeichnis wieder her mit dem Befehl:

Restore-SqlBackupFromDirectory -SqlServer MeinZielServer -Path \\MeineBackupLokation\MeinServer\MeineDatenbank

Einfacher geht es nun wirklich nicht.
Advertisements

Über Dirk Hondong

A MS server and ms sql server admin guy from germany. want to improve my skills a little bit, sharing my daily experience
Dieser Beitrag wurde unter Maintenance, PowerShell, SQL Server abgelegt und mit , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s