MSPSS: is there life after the helpdesk?

sharing solutions to uncommon IT problems

Posts Tagged ‘Scripting

Restore All DBs in a folder (even files with multiple backups in it)

leave a comment »


Ok, this is pretty cool…

I’ve used for a long time a script that allows me to restore all databases from every backup file stored in a folder.

The script was the edited and adapted version of something another guy did for SQL Server 2000 (thank you again sir, wherever you are).

This script proved itself super-useful for migrations, QA/TEST env refresh etc.

Its big limit was that it only allowed one backup per file while I like to group my backups by customer (to avoid having a mess in my backup folder).

Today, I finally found the time to work on it again and now it works no matter the number of backups per file (and the original script was a SP while this is a straight query).

I think it’s pretty cool…

Here it comes:

SET NOCOUNT ON
DECLARE @SourceDirBackupFiles VARCHAR(200) = 'D:\Migration\'
DECLARE @DestDirDbFiles VARCHAR(200) = 'D:\MSSQL\MSSQL10.MSSQLSERVER\MSSQL\DATA\'
DECLARE @DestDirLogFiles VARCHAR(200) = 'D:\MSSQL\MSSQL10.MSSQLSERVER\MSSQL\DATA\'
DECLARE @fname VARCHAR(200)
DECLARE @dirfile VARCHAR(300)
DECLARE @LogicalName NVARCHAR(128)
DECLARE @PhysicalName NVARCHAR(260)
DECLARE @type CHAR(1)
DECLARE @DbName sysname
DECLARE @position INT
DECLARE @sql NVARCHAR(1000)
DECLARE @Multiple INT
 
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
,TDEThumbprint NVARCHAR(128)
)
 
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 INT
,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 INT
,UnicodeComparisonStyle INT
,CompatibilityLevel tinyint
,SoftwareVendorId INT
,SoftwareVersionMajor INT
,SoftwareVersionMinor INT
,SoftwareVersionBuild INT
,MachineName NVARCHAR(128)
,Flags INT
,BindingID uniqueidentifier
,RecoveryForkID uniqueidentifier
,Collation NVARCHAR(128)
,FamilyGUID uniqueidentifier
,HasBulkLoggedData INT
,IsSnapshot INT
,IsReadOnly INT
,IsSingleUser INT
,HasBackupChecksums INT
,IsDamaged INT
,BegibsLogChain INT
,HasIncompleteMetaData INT
,IsForceOffline INT
,IsCopyOnly INT
,FirstRecoveryForkID uniqueidentifier
,ForkPointLSN numeric(25,0)
,RecoveryModel NVARCHAR(128)
,DifferentialBaseLSN numeric(25,0)
,DifferentialBaseGUID uniqueidentifier
,BackupTypeDescription NVARCHAR(128)
,BackupSetGUID uniqueidentifier
,CompressedBackupSize NVARCHAR(128)
)
 
CREATE TABLE #files(fname VARCHAR(200),depth INT, file_ INT)
INSERT #files
EXECUTE master.dbo.xp_dirtree @SourceDirBackupFiles, 1, 1
 
DECLARE files CURSOR FOR
SELECT fname FROM #files
DECLARE dbfiles CURSOR FOR
SELECT LogicalName, PhysicalName, Type FROM #dbfiles
DECLARE infiles CURSOR FOR
SELECT Position, DatabaseName FROM #bdev
 
OPEN files
FETCH NEXT FROM files INTO @fname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dirfile = @SourceDirBackupFiles + @fname
TRUNCATE TABLE #bdev
INSERT #bdev
EXEC ('RESTORE HEADERONLY FROM DISK = ''' + @dirfile + '''')
 
SET @Multiple = (select COUNT(*) from #bdev)
OPEN infiles
FETCH NEXT FROM infiles INTO @position, @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
if @Multiple > 1
BEGIN
SET @sql = 'RESTORE DATABASE ' + @DbName + ' FROM DISK = ''' + @dirfile + ''' WITH FILE = ' + cast(@position as varchar(10)) + ', MOVE '
END
ELSE
SET @sql = 'RESTORE DATABASE ' + @DbName + ' FROM DISK = ''' + @dirfile + ''' WITH MOVE '
 
TRUNCATE TABLE #dbfiles
 
if @Multiple > 1
BEGIN
INSERT #dbfiles
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @dirfile + ''' WITH FILE = ' + @position)
END
ELSE
INSERT #dbfiles
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @dirfile + '''')
 
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type
 
WHILE @@FETCH_STATUS = 0
BEGIN
IF @type = 'D'
SET @sql = @sql + '''' + @LogicalName + ''' TO ''' + @DestDirDbFiles + @LogicalName + '.mdf'', MOVE '
ELSE IF @type = 'L'
SET @sql = @sql + '''' + @LogicalName + ''' TO ''' + @DestDirLogFiles + @LogicalName + '.ldf'', MOVE '
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type
END
 
SET @sql = substring(@sql,1,LEN(@sql)-6)
PRINT @sql
CLOSE dbfiles
 
FETCH NEXT FROM infiles INTO @position, @dbname
END
CLOSE infiles
 
FETCH NEXT FROM files INTO @fname
END
CLOSE files
DEALLOCATE files
DEALLOCATE dbfiles
DEALLOCATE infiles
 
DROP TABLE #files
DROP TABLE #bdev
DROP TABLE #dbfiles
 

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

HTH,

Roberto.

Advertisements

Written by zantoro

June 13, 2013 at 5:52 pm

Posted in SQL Server

Tagged with , ,