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.
Veröffentlicht unter Maintenance, PowerShell, SQL Server | Verschlagwortet mit , , , , | Kommentar hinterlassen

CREATE OR ALTER mit SQL 2016 SP1

Wie sich die Zeiten doch ändern. Vor einem Jahr habe ich kurz über das Thema  DROP…CREATE geschrieben, bzw. das Thema DROP..IF EXISTS angeschnitten, als es gerade neu war in der CTP des 2016ers.

Und jetzt, mit dem SP1, kommt tatsächlich die CREATE OR ALTER Syntax ins Haus geflattert.

Für mich als DBA ist das natürlich großartig zu wissen. Wenn man Datenbanken unter sich hat mit einer granularen Sicherheit, dann ist dieser Befehl nun echt Gold wert. Etwaige Applikationsupdates, welche auch die DB im Hintergrund anpacken, „zerschießen“ einem so nicht mehr die Berechtigungen, welche evtl. auf einzelnen Objekten vorhanden sind.

Also, schaut in Euren Skriptsammlungen und Quellcodeverwaltungen nach und überarbeitet Eure Skripte, wenn Ihr schon für den 2016er entwickelt.

Natürlich gibt es noch viele andere Neuerungen die mit dem SP1 kommen (neue query hint Optionen, Partitionierung und In-Memory Funktionalitäten in allen Editionen und und und…). Aber hier waren schon Andere schneller, was das bloggen angeht und mitunter auch schon richtig ausführlich. Hier nenne ich nur mal schnell den Andreas Wolter und Niko Neugebauer.

Und nun geht’s an Testen:

2016-11-17-10_07_42-sql-server-2008-r2-diagnostic-information-queries-cy-2016-sql-readonlysql

 

 

Veröffentlicht unter SQL Server, SQL Server Administration | Verschlagwortet mit , , | Kommentar hinterlassen

Sicherheitsupdates (MS16-136) für SQL Server 2012, 2014, 2016 veröffentlicht

Hallo zusammen,

gestern frisch von Microsoft veröffentlicht:

Microsoft-Sicherheitsbulletin MS16-136 – Hoch

Dabei handelt es sich um Sicherheitslücken, wo der potentielle Angreifer erweiterte Berechtigungen am SQL Server erlangen kann.

Für den SQL Server 2016 bedeutet es, dass es das CU3 gibt.

Happy patching and testing…

 

Veröffentlicht unter SQL Server, SQL Server Administration | Verschlagwortet mit , , , | 1 Kommentar

100% SQLKonferenz 2017, 25% SQLPaparazzo

Hallo zusammen,

im Februar 2017 ist es soweit, die SQLKonferenz findet wieder statt. Um genau zu sein: vom 14. bis 16. Februar an gewohnter Stelle, dem Darmstadtium.

Es ist inzwischen schon das 4. Mal, dass dieses großartige Event von der PASS Deutschland in Zusammenarbeit mit dem e-Team abgehalten wird. Viel braucht man eigentlich schon nicht mehr sagen. Es wird wieder losgehen mit einem Tag voller PreCons gefolgt von 2 Tagen voll mit Vorträgen aus insgesamt 6 verschiedenen Themengebieten. Noch ist Nichts publik, aber ich hab schon ein paar Insider Infos (als SQLPaparazzo schon fast selbstverständlich). Ich kann nur sagen: wie geil wird das denn?

Doch 2017 wird dennoch etwas anders sein. Der SQLPaparazzo wird nur auf “Sparflamme” arbeiten. Es ist das erste Mal, dass ich nicht privat als ein sogenannter Volunteer bei der SQLKonferenz aufschlage. Tatsächlich ist es 2017 so, dass mein Arbeitgeber mich auf die Konferenz schickt, was mich natürlich riesig freut. Aber genau aus dem Grund wird es mir 2017 nicht möglich sein in jede Session reinzuhüpfen, um ein paar Aufnahmen von den Sprechern und dem Publikum zu machen. Stattdessen wird es von meiner Seite aus dieses Mal nur Aufnahmen geben von Vorträgen, die ich von Anfang bis Ende besuchen werde plus das Ganze “Drumherum”.

Um jetzt eventuell die Veranstaltung Fototechnisch doch etwas mehr abdecken zu können frage ich einfach mal in die Community-Runde: Wer würde denn vielleicht noch seine Kamera mitbringen und mitmachen? Die- oder derjenige kann sich gerne bei Tillmann Eitelberg (t) oder mir melden.

Veröffentlicht unter SQL Community, SQL Server, SQLKonferenz | Verschlagwortet mit , , , , | Kommentar hinterlassen

SQL Server Management Studio 16.5 verfügbar

Der Titel sagt schon Alles. Eine neue Version des SSMS ist da.

Wie beim letzten Mal auch schon ist der deutsche Link noch nicht aktuell. Daher einfach auf https://msdn.microsoft.com/en-us/library/mt238290.aspx gehen und von dort die Version ziehen bzw. direkt diesen Link nehmen, wenn man eh mit der englischsprachigen Version arbeitet:

https://download.microsoft.com/download/C/B/C/CBCFAAD1-2348-4119-B093-199EE7AADCBC/SSMS-Setup-ENU.exe

Wie es scheint, sind wieder einige Bugs gefixt worden wie z.B. der Application Crash beim Schließen von SSMS (kenn ich auch schon) oder dass das „Manage Compression“ Menü deaktiviert ist bei Benutzertabellen im Objektbaum.

 

Veröffentlicht unter SQL Server, SSMS | 2 Kommentare

SQL Server Management Studio September Release (16.4.x)

Hallo zusammen,

nur eine kleine, schnelle Info für diejenigen, die es noch nicht mitbekommen haben: Das Management Studio 2016 liegt seit kurzem in einer neuen Version vor.

Ganz wichtig für mich ist vor Allem das Beheben der out of Memory exception, wozu es auch ein Connect Item gegeben hat.

Die aktuelle Version des Management Studios kann über folgenden Link bezogen werden:

Download SQL Server Management Studio (SSMS)

/EDIT 22.09.2016

Der Download wurde erst einmal von Microsoft wieder zurückgezogen:

2016-09-22-08_44_46-download-sql-server-management-studio-ssms-%e2%80%8e-microsoft-edge

/EDIT 26.09.2016

Nun gibt es das SSMS in der 16.4.1er Version zum Download.

2016-09-26-07_55_03-microsoft-sql-server-management-studio

 

 

 

Veröffentlicht unter SQL Server | Verschlagwortet mit , | 4 Kommentare

Den PlanExplorer gibt es nun komplett gratis

Hallo liebe Community,

genau, Ihr habt richtig gelesen. Ich bin gerade darüber gestolpert.

Der Plan Explorer von SQL Sentry ist gestern in der Version 3.0 erschienen und beinhaltet nun auch die „Pro“-Features wie zum Beispiel mehrere Tabs an Abfrageplänen zu öffnen, Deadlocks visualisieren und und und….

Jeder, der irgendwie mit Query Tuning zu tun hat, kommt jetzt um dieses Werkzeug bestimmt nicht mehr herum.

Hier geht es zur Produktseite:

http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view

 

/EDIT: An der Stelle muss ich auch noch mal ein kleines Sorry in Richtung SQLSentry aussprechen, da ich vor dem „big announcment“ schon über PE 3.0 gebloggt habe.

/EDIT zum 2: Hier ist nun auch das Announcment von Greg Gonzales, CEO von SQLSentry: http://blogs.sqlsentry.com/greggonzalez/plan-explorer-pro-goes-free/

Veröffentlicht unter SQL Community, SQL Server | Verschlagwortet mit , , , | Kommentar hinterlassen