I modified this script originally created by Tibor Karaszi
http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp
Added it with some smarter features for work needs, and now I am here sharing it
[code]
/*
Original
http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp
Modified
-----------------------------------------------------------------
2009/05/31 Jerry Hung
Summary:
- support both 2000/2005/2008
- fills in missing \ if any
- better line break for dynamic SQL output of RESTORE
- filters on only BAK/DIFF extensions
- generate script to restore the "latest" BAK AND the latest DIFF file (if both DB.bak and DB1.diff, DB2.diff exist for example)
- restore multiple logical MDF/LDF files (if exists), and full-text catalogs
- alters database into SINGLE_USER before restore to avoid "db in use" error
-----------------------------------------------------------------
-- how to call
EXEC usp_RestoreFromAllFilesInDirectory
@SourceDirBackupFiles = '\\10.25.5.141\G$\DBA\DBVICASI1'
,@DestDirDbFiles = 'R:\MSSQL\DATA\'
,@DestDirLogFiles = 'L:\MSSQL\LOG\'
,@RecoveryMode = 'RECOVERY'
*/
/**/
CREATE PROCEDURE usp_RestoreFromAllFilesInDirectory
@SourceDirBackupFiles NVARCHAR(200) = NULL
,@DestDirDbFiles NVARCHAR(200) = 'R:\MSSQL\DATA\'
,@DestDirLogFiles NVARCHAR(200) = 'L:\MSSQL\LOG\'
,@RecoveryMode VARCHAR(100) = 'RECOVERY'
AS
/*
-- TEST
DECLARE @SourceDirBackupFiles NVARCHAR(1000)
,@DestDirDbFiles NVARCHAR(1000)
,@DestDirLogFiles NVARCHAR(1000)
,@RecoveryMode VARCHAR(100)
SET @SourceDirBackupFiles = '\\DBCA1\Z$\Recovery'
SET @DestDirDbFiles = 'R:\MSSQL\DATA\'
SET @DestDirLogFiles = 'L:\MSSQL\LOG\'
-- Jerry: set database recovery mode
SET @RecoveryMode = 'RECOVERY' -- RECOVERY/NORECOVERY
*/
SET XACT_ABORT, NOCOUNT ON
BEGIN TRAN
-- Jerry: Ensure a trailing \
IF RIGHT(@SourceDirBackupFiles, 1) <> '\'
SET @SourceDirBackupFiles = @SourceDirBackupFiles + '\'
--Table to hold each backup file name in
CREATE TABLE #files
(
fname VARCHAR(1000)
,depth INT
,file_ INT
)
INSERT #files
EXECUTE master.dbo.xp_dirtree @SourceDirBackupFiles, 1, 1
---------------------------------------------------------------------------------------------------------
-- Jerry: filter on file extensions, and only restore the LATEST FULL backup and LATEST DIFF backup (no TRN restore for now)
---------------------------------------------------------------------------------------------------------
--SELECT * FROM #files
-- delete non-backup files
DELETE #files
WHERE 1 = 1
AND fname NOT LIKE '%DIFF%'
AND fname NOT LIKE '%BAK%'
-- keep only latest BAK file
DELETE F1
FROM #files F1
WHERE 1 = 1
AND fname LIKE '%BAK%'
AND fname < (
SELECT MAX(fname)
FROM #files F2 (NOLOCK)
WHERE F2.fname LIKE '%BAK%'
AND LEFT(F1.fname, 20) = LEFT(F2.fname, 20)
)
-- keep only latest DIFF file
DELETE F1
FROM #files F1
WHERE 1 = 1
AND fname LIKE '%DIFF%'
AND fname < (
SELECT MAX(fname)
FROM #files F2 (NOLOCK)
WHERE F2.fname LIKE '%DIFF%'
AND LEFT(F1.fname, 20) = LEFT(F2.fname, 20)
)
--SELECT * FROM #files
---------------------------------------------------------------------------------------------------------
-- Jerry: section to handle SQL 2000 differently from 2005/2008
---------------------------------------------------------------------------------------------------------
DECLARE @IsSQLServer2000 BIT
IF @@VERSION LIKE '%2000%'
SET @IsSQLServer2000 = 1
-- PRINT @@VERSION
--Table to hold the result from RESTORE HEADERONLY. Needed to get the database name out from
IF @IsSQLServer2000 = 1
BEGIN
CREATE TABLE #bdev2000
(
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)
,DifferentialBaseLSN 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)
)
--Table to hold result from RESTORE FILELISTONLY. Need to generate the MOVE options to the RESTORE command
CREATE TABLE #dbfiles2000
(
LogicalName NVARCHAR(128)
,PhysicalName NVARCHAR(260)
,Type CHAR(1)
,FileGroupName NVARCHAR(128)
,Size NUMERIC(20, 0)
,MaxSize NUMERIC(20, 0)
)
END
ELSE
BEGIN
--Table to hold the result from RESTORE HEADERONLY. Needed to get the database name out from
CREATE TABLE #bdev2005
(
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)
-- new in 2005
,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
)
--Table to hold result from RESTORE FILELISTONLY. Need to generate the MOVE options to the RESTORE command
CREATE TABLE #dbfiles2005
(
LogicalName NVARCHAR(128)
,PhysicalName NVARCHAR(260)
,Type CHAR(1)
,FileGroupName NVARCHAR(128)
,Size NUMERIC(20, 0)
,MaxSize BIGINT
-- new in 2005
,FileId INT
,CreateLSN NUMERIC(25, 0)
,DropLSN NUMERIC(25, 0)
,UniqueId UNIQUEIDENTIFIER
,ReadOnlyLSN NUMERIC(25, 0)
,ReadWriteLSN NUMERIC(25, 0)
,BackupSizeInBytes BIGINT
,SourceBlockSize INT
,FilegroupId INT
,LogGroupGUID UNIQUEIDENTIFIER
,DifferentialBaseLSN NUMERIC(25)
,DifferentialBaseGUID UNIQUEIDENTIFIER
,IsReadOnly INT
,IsPresent INT
)
END
DECLARE @fname VARCHAR(1000)
DECLARE @dirfile VARCHAR(1000)
DECLARE @LogicalName NVARCHAR(1000)
DECLARE @PhysicalName NVARCHAR(1000)
DECLARE @type CHAR(1)
DECLARE @DbName SYSNAME
DECLARE @sql NVARCHAR(2000)
DECLARE @LogicalCounter TINYINT
DECLARE @recoverySQL VARCHAR(4000)
SET @recoverySQL = ''
DECLARE files CURSOR FAST_FORWARD
FOR SELECT fname
FROM #files
WHERE [file_] = 1
IF @IsSQLServer2000 = 1
DECLARE dbfiles CURSOR FAST_FORWARD
FOR SELECT LogicalName
,PhysicalName
,Type
FROM #dbfiles2000
ELSE
DECLARE dbfiles CURSOR FAST_FORWARD
FOR SELECT LogicalName
,PhysicalName
,Type
FROM #dbfiles2005
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.
IF @IsSQLServer2000 = 1
BEGIN
TRUNCATE TABLE #bdev2000
INSERT #bdev2000
EXEC
('RESTORE HEADERONLY FROM DISK = ''' + @dirfile
+ ''''
)
--SELECT * FROM #bdev
SET @DbName = (
SELECT TOP 1
DatabaseName
FROM #bdev2000
)
END
ELSE
BEGIN
TRUNCATE TABLE #bdev2005
INSERT #bdev2005
EXEC
('RESTORE HEADERONLY FROM DISK = ''' + @dirfile
+ ''''
)
--SELECT * FROM #bdev2005
SET @DbName = (
SELECT TOP 1
DatabaseName
FROM #bdev2005
)
END
--Construct the beginning for the RESTORE DATABASE command
SET @sql = 'RESTORE DATABASE [' + @DbName + '] FROM DISK = N'''
+ @dirfile + ''''
--+ char(13)+char(10)
--PRINT('RESTORE HEADERONLY FROM DISK = ''' + @dirfile + '''')
---------------------------------------------------------------------------------------------------------
-- Jerry: Only add logical name parts if FULL backup restore; skip the logical name part for DIFF file
---------------------------------------------------------------------------------------------------------
IF @dirfile LIKE '%.BAK'
BEGIN
--PRINT('RESTORE FILELISTONLY FROM DISK = ''' + @dirfile + '''')
--Get information about database files from backup device into temp table
IF @IsSQLServer2000 = 1
BEGIN
TRUNCATE TABLE #dbfiles2000
INSERT #dbfiles2000
EXEC
('RESTORE FILELISTONLY FROM DISK = '''
+ @dirfile + ''''
)
END
ELSE
BEGIN
TRUNCATE TABLE #dbfiles2005
INSERT #dbfiles2005
EXEC
('RESTORE FILELISTONLY FROM DISK = '''
+ @dirfile + ''''
)
--SELECT * FROM #dbfiles2005
END
--SELECT LogicalName, PhysicalName, Type FROM #dbfiles
SET @sql = @sql + CHAR(13) + CHAR(10) + 'WITH'
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName,
@type
--For each database file that the database uses
---------------------------------------------------------------------------------------------------------
-- Jerry: capable of handling multiple LDF file (DB1.ldf, DB2.ldf, etc...)
---------------------------------------------------------------------------------------------------------
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + CHAR(13) + CHAR(10) + ' MOVE '
IF @type = 'D' -- Data
BEGIN
SET @sql = @sql + '''' + @LogicalName
+ ''' TO ''' + @DestDirDbFiles + @DbName
+ '.mdf'','
SET @LogicalCounter = 0
END
ELSE
BEGIN
IF @type IN ('L') -- Log
BEGIN
SET @sql = @sql + '''' + @LogicalName
+ ''' TO ''' + @DestDirLogFiles
+ @DbName + CASE @LogicalCounter
WHEN 0 THEN ''
ELSE CAST(@LogicalCounter AS VARCHAR)
END + '.ldf'','
SET @LogicalCounter = @LogicalCounter
+ 1
END
ELSE
---------------------------------------------------------------------------------------------------------
-- Jerry: restore full-text as best as we can
---------------------------------------------------------------------------------------------------------
IF @type IN ('F') -- Full-text
BEGIN
SET @sql = @sql + ''''
+ @LogicalName + ''' TO '''
+ @DestDirDbFiles + @DbName
+ CASE @LogicalCounter
WHEN 0 THEN ''
ELSE CAST(@LogicalCounter AS VARCHAR)
END + '.' + @LogicalName + ''','
SET @LogicalCounter = @LogicalCounter
+ 1
END
END
FETCH NEXT FROM dbfiles INTO @LogicalName,
@PhysicalName, @type
END
CLOSE dbfiles
SET @sql = @sql + CHAR(13) + CHAR(10) + 'REPLACE, STATS, '
+ @RecoveryMode
END
ELSE
SET @sql = @sql + CHAR(13) + CHAR(10) + 'WITH REPLACE, STATS, '
+ @RecoveryMode
--Here's the actual RESTORE command
PRINT 'PRINT ''--RESTORE FILELISTONLY FROM DISK = ''''' + @dirfile
+ '''' + '''' + ''''
--PRINT '--RESTORE FILELISTONLY FROM DISK = ''' + @dirfile + ''''
---------------------------------------------------------------------------------------------------------
-- Jerry: Set ONLINE DB to single user before restore
---------------------------------------------------------------------------------------------------------
IF EXISTS ( SELECT [name]
FROM master.dbo.sysdatabases (NOLOCK)
WHERE 1 = 1
AND NAME = @dbname
AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 -- not in Standby mode
AND DATABASEPROPERTYEX([name], 'Status') = 'ONLINE' -- only worry about ONLINE DB's
)
PRINT 'ALTER DATABASE [' + @DbName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
PRINT @sql
PRINT 'GO'
PRINT ''
IF @RecoveryMode = 'NORECOVERY'
BEGIN
SET @recoverySQL = @recoverySQL + 'RESTORE DATABASE [' + @DbName + '] WITH RECOVERY;' + CHAR(10)
END
--Remove the comment below if you want the procedure to actually execute the restore command.
--EXEC(@sql)
FETCH NEXT FROM files INTO @fname
END
-- provide a quick way to change to RECOVERY mode from NORECOVERY
PRINT '/*'+ @recoverySQL + '*/'
-- cleanup section
----------------------------------------------------------------------
CLOSE files
DEALLOCATE dbfiles
DEALLOCATE files
DROP TABLE #files
IF @IsSQLServer2000 = 1
BEGIN
DROP TABLE #bdev2000
DROP TABLE #dbfiles2000
END
ELSE
BEGIN
DROP TABLE #bdev2005
DROP TABLE #dbfiles2005
END
----------------------------------------------------------------------
COMMIT
GO
/*
Outline
Below stored procedure reads the contents of a number of backup files in a directory and based on that generates RESTORE DATABASE commands. The outline of the procedure is:
* Use xp_dirtree to save all file names in a directory in a temp table.
* For each file, EXEC RESTORE HEADERONLY into a temp table to get the database name from the backup file.
* Use EXEC and RESTORE FILELISTONLY into a temp table so we can go through that and generate MOVE for each database file.
* Print out the RESTORE commands.
Usage
@SourceDirBackupFiles nvarchar(200)
This is the name of the directory where the backup files are stored.
@DestDirDbFiles nvarchar(200)
This is the name of the directory where the databases' data files are to be created.
@DestDirLogFiles nvarchar(200)
This is the name of the directory where the databases' log files are to be created.
Note that the procedure doesn't execute the RESTORE commands; it only outputs them to the result window so you can go through them before pasting them to the query window and executing them.
Limitations
Only one backup on each backup file.
Only database backups in the files.
Only one mdf and one ldf file per database.
Sample execution
EXEC sp_RestoreFromAllFilesInDirectory 'C:\Temp\', 'C:\SqlDataFiles\', 'D:\SqlLogFiles\'
Copyright Tibor Karaszi, Nucleus Datakonsult, 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.
Modified to work with SQL Server 2005 [Andreas Moe, Ole Robin 2008]:
Added posibility to put log files in different location than database file, altered if statement
Updated Table creating #bedev and #dbfiles to suite SQL2005(also works with SQL2000), more columns added
Sample execution:
EXEC sp_RestoreFromAllFilesInDirectory 'C:\Mybakfiles\', 'D:\Mydatabasesdirectory\' ,’C:\MylogDirectory\’
*/
[/code]
