/*************************************************************
Script made by : Lester A. Policarpio
Email Address : lpolicarpio2001@yahoo.com
Date Created : September 03, 2007
--=UPDATES=--

January 17, 2008
- Solved outputed value of the @restoredb variable
- Solved the "invalid length parameter" issue

May 6, 2008
- Removed unused variables
- Add the headeronly command to accept non sql backup format 
 (dbname_db_YYYYMMDDHHMM.BAK)
- Add more comments

May 12, 2008
- Accept Backup Files With Multiple NDFs

May 23, 2008
- Solved the problem when RESTORE HEADERONLY produces more than 1 value

September 29,2011
- Update script to include backups in sub directories
- Include search string for detecting specific backups
	- i.e. finding backups with specific year only

--=LIMITATIONS=--
- This script is tested for backup files ".BAK" only 
-- SCRIPT NEEDS TO BE ALTERED IF BACKUP EXTENSION IS NOT ".BAK"
*************************************************************/
SET NOCOUNT ON
--Drop Tables if it exists in the database
if exists (select name from sysobjects where name = 'migration_lester')
DROP TABLE migration_lester
if exists (select name from sysobjects where name = 'header_lester')
DROP TABLE header_lester
if exists (select name from sysobjects where name = 'cmdshell_lester')
DROP TABLE cmdshell_lester


--Create Tables 
--(cmdshell_lester table for the cmdshell command)
--(migration_lester table for the restore filelistonly command)
--(header_lester table for the restore headeronly command)
CREATE TABLE cmdshell_lester( fentry varchar(1000),directory varchar(1000))


CREATE TABLE migration_lester(LogicalName varchar(1024),
PhysicalName varchar(4000),type char(1),FileGroupName varchar(50),
size real,MaxSize real)

CREATE TABLE header_lester (BackupName varchar(50),
BackupDescription varchar(100),BackupType int,
ExpirationDate nvarchar(50),Compressed int,Position int,
DeviceType int,UserName varchar(30),ServerName varchar(30),
DatabaseName varchar(50),DatabaseVersion int,
DatabaseCreationDate datetime,BackupSize bigint,FirstLsn binary,
LastLsn binary,CheckpointLsn binary,DifferentialBasLsn binary,
BackupStartDate datetime,BackupFinishDate datetime,SortOrder int,
CodePage int,UnicodeLocaleid int,UnicodeComparisonStyle int,
CompatibilityLevel int,SoftwareVendorId int,SoftwareVersionMajor int,
SoftwareVersionMinor int,SoftwareVersionBuild int,
MachineName varchar(50),Flags int,BindingId nvarchar(50),
RecoveryForkId nvarchar(50),Collation nvarchar(50))

--Declare Variables
DECLARE @path varchar(1024),@restore varchar(1024)
DECLARE @restoredb varchar(2000),@extension varchar(1024),@newpath_ldf varchar(1024)
DECLARE @pathension varchar(1024),@newpath_mdf varchar(1024),@header varchar(500)
DECLARE @search_string varchar(250)

--Set Values to the variables
SET @newpath_mdf = 'C:\' --new path wherein you will put the mdf
SET @newpath_ldf = 'C:\' --new path wherein you will put the ldf
SET @path = 'C:\' --Path of the Backup File (put \ on the end)
SET @search_string = '' -- leave it blank if you dont have a search string, change if neccessary
SET @extension = 'BAK'
SET @pathension = 'dir /OD/S "'+@Path+'*.'+@Extension+'"'

/****************************
SCRIPT FOR SUB DIRECTORIES
****************************/
--Drop Tables if it exists in the database
if exists (select name from sysobjects where name = 'cmdshell_lester_subdir')
DROP TABLE cmdshell_lester_subdir

--Create Table
CREATE TABLE cmdshell_lester_subdir( fentry varchar(1000))

--Insert the value of the command shell to the table
INSERT INTO cmdshell_lester_subdir exec master..xp_cmdshell @pathension
--Delete data not equal to directory
DELETE FROM cmdshell_lester_subdir WHERE FEntry NOT LIKE ' Directory of%' 
DELETE FROM cmdshell_lester_subdir WHERE FEntry is NULL

-- CREATE LOOP
DECLARE @subdir varchar(250),@subdir2 varchar(250)
DECLARE subdir CURSOR FOR
select substring(FEntry,15,len(FEntry)) as 'SUBDIR' from cmdshell_lester_subdir
OPEN subdir
FETCH NEXT FROM subdir INTO @subdir
WHILE (@@FETCH_STATUS = 0) BEGIN
SET @subdir2 = 'dir /OD "'+@subdir+'\*'+@search_string+'*.'+@Extension+'"'
INSERT INTO cmdshell_lester (FEntry) EXEC master..xp_cmdshell @subdir2
update cmdshell_lester set directory = @subdir where directory IS NULL
FETCH NEXT FROM subdir INTO @subdir
END
CLOSE subdir
DEALLOCATE subdir

/***************************
END OF SUB DIRECTORY SCRIPT
****************************/

--Insert the value of the command shell to the table
--INSERT INTO cmdshell_lester exec master..xp_cmdshell @pathension

--Delete non backup files data, delete null values
DELETE FROM cmdshell_lester WHERE FEntry NOT LIKE '%.BAK%' 
DELETE FROM cmdshell_lester WHERE FEntry is NULL
--Create a cursor to scan all backup files needed to generate the restore script
DECLARE @migrate varchar(1024),@directory varchar(1000)
DECLARE migrate CURSOR FOR
select substring(FEntry,40,50) as 'FEntry',directory from cmdshell_lester 
OPEN migrate
FETCH NEXT FROM migrate INTO @migrate,@directory
WHILE (@@FETCH_STATUS = 0)BEGIN
--Added feature to get the dbname of the backup file
SET @header = 'RESTORE HEADERONLY FROM DISK = '+''''+@directory+'\'+@Migrate+''''
INSERT INTO header_lester exec (@header)
--Get the names of the mdf and ldf
set @restore = 'RESTORE FILELISTONLY FROM DISK = '+''''+@directory+'\'+@migrate+''''
INSERT INTO migration_lester EXEC (@restore)
--Update value of the table to add the new path+mdf/ldf names
UPDATE migration_lester SET physicalname = reverse(physicalname)
UPDATE migration_lester SET physicalname = 
substring(physicalname,1,charindex('\',physicalname)-1)

UPDATE migration_lester SET physicalname = @newpath_mdf+reverse(physicalname) where type = 'D'
UPDATE migration_lester SET physicalname = @newpath_ldf+reverse(physicalname) where type = 'L'
--@@@@@@@@@@@@@@@@@@@@
--Set a value to the @restoredb variable to hold the restore database script
IF (select count(*) from migration_lester) = 2
BEGIN
SET @restoredb = 'RESTORE DATABASE '+(select top 1 DatabaseName from header_lester)
+' FROM DISK = '+ ''''+@directory+'\'+@migrate+''''+' WITH MOVE '+''''
+(select logicalname from migration_lester where type = 'D')+''''
+' TO '+ ''''+( select physicalname from migration_lester WHERE physicalname like '%mdf%')
+''''+', MOVE '+''''+ (select logicalname from migration_lester where type = 'L')
+''''+' TO '+''''+( select physicalname from migration_lester 
WHERE physicalname like '%ldf%')+''''
print (@restoredb) 
END

IF (select count(*) from migration_lester) > 2
BEGIN
SET @restoredb = 
'RESTORE DATABASE '+(select top 1 DatabaseName from header_lester)+
' FROM DISK = '+''''+@directory+'\'+@migrate+''''+'WITH MOVE '
DECLARE @multiple varchar(1000),@physical varchar(1000)
DECLARE multiple CURSOR FOR
Select logicalname,physicalname from migration_lester
OPEN multiple
FETCH NEXT FROM multiple INTO @multiple,@physical
WHILE(@@FETCH_STATUS = 0)
BEGIN
SET @restoredb=@restoredb+''''+@multiple+''''+' TO '+''''+@physical+''''+','+'MOVE '+''
FETCH NEXT FROM multiple INTO @multiple,@physical
END
CLOSE multiple
DEALLOCATE multiple
SET @restoredb = substring(@restoredb,1,len(@restoredb)-5)
print (@restoredb)
END

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- Run print @restoredb first to view the databases to be restored
-- When ready, run exec (@restoredb)
-- EXEC (@restoredb)

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--Clear data inside the tables to give way for the next 
--set of informations to be put in the @restoredb variable 
TRUNCATE TABLE migration_lester
TRUNCATE TABLE header_lester
FETCH NEXT FROM migrate INTO @migrate,@directory
END
CLOSE migrate
DEALLOCATE migrate
--@@@@@@@@@@@@@@@@@@@



--Drop Tables 
DROP TABLE migration_lester
DROP TABLE cmdshell_lester
DROP TABLE cmdshell_lester_subdir
DROP TABLE header_lester

USE	master

SET NOCOUNT ON

--Drop Tables if it exists in the database

IF exists (SELECT name 
			FROM sysobjects 
			WHERE name = 'migration')
DROP TABLE migration

IF exists (SELECT name 
		   FROM sysobjects 
		   WHERE name = 'header')
DROP TABLE header

IF exists (SELECT name 
		   FROM sysobjects 
		   WHERE name = 'cmdshell')
DROP TABLE cmdshell


/*Create Tables
(cmdshell table for the cmdshell command)
(migration table for the restore filelistonly command)
(header table for the restore headeronly command)
*/

CREATE TABLE cmdshell( fentry VARCHAR(1000))

CREATE TABLE migration(	LogicalName VARCHAR(1024),
						PhysicalName VARCHAR(4000),
						TYPE CHAR(1),
						FileGroupName VARCHAR(50),
						size REAL,
						MaxSize REAL,
						FileID BIGINT,
						CreateLSN NUMERIC(25,0),
						DropLSN NUMERIC(25,0),
						UniqueID BINARY,
						ReadOnlyLSN NUMERIC(25,0),
						ReadWriteLSN NUMERIC(25,0),
						BackupSizeInBytes BIGINT,
						SourceBlockSize INT,
						FileGroupID INT,
						LogGroupGUID BINARY,
						DifferentialBaseLSN NUMERIC(25,0),
						DifferentialBaseGUID BINARY,
						IsReadOnly BIT,
						IsPresent BIT,
						TDEThumbprint BINARY)

CREATE TABLE header (	BackupName VARCHAR(50),
						BackupDescription VARCHAR(100),
						BackupType INT,
						ExpirationDate NVARCHAR(50),
						Compressed INT,
						Position CHAR(30),
						DeviceType INT,
						UserName VARCHAR(30),
						ServerName VARCHAR(30),
						DatabaseName VARCHAR(50),
						DatabaseVersion INT,
						DatabaseCreationDate DATETIME,
						BackupSize BIGINT,
						FirstLsn BINARY,
						LastLsn BINARY,
						CheckpointLsn BINARY,
						DatabaseBackupLSN BINARY,
						BackupStartDate DATETIME,
						BackupFinishDate DATETIME,
						SortOrder INT,
						CodePage INT,
						UnicodeLocaleid INT,
						UnicodeComparisonStyle INT,
						CompatibilityLevel INT,
						SoftwareVendorId INT,
						SoftwareVersionMajor INT,
						SoftwareVersionMinor INT,
						SoftwareVersionBuild INT,
						MachineName VARCHAR(50),
						Flags INT,
						BindingId NVARCHAR(50),
						RecoveryForkId NVARCHAR(50),
						Collation NVARCHAR(50),
						FamilyGUID BINARY,
						HasBulkLoggedData INT,
						IsSnapshot INT,
						IsReadOnly INT,
						IsSingleUser INT,
						HasBackupChecksums INT,
						IsDamaged INT,
						BeginsLogChain INT,
						HasIncompleteMetaData INT,
						IsForceOffline INT,
						IsCopyOnly INT,
						FirstRecoveryForkID BINARY,
						ForkPointLSN BINARY,
						RecoveryModel NVARCHAR(60),
						DifferentialBaseLSN BINARY,
						DifferentialBaseGUID BINARY,
						BackupTypeDescription NVARCHAR(60),
						BackupSetGUID BINARY,
						CompressedBackupSize BIGINT)

--Declare Variables

DECLARE		@path varchar(1024),		@restore varchar(1024),		@DATE VARCHAR(6)		
DECLARE		@restoredb varchar(2000),	@extension varchar(1024),	@newpath_ldf varchar(1024)
DECLARE		@pathension varchar(1024),	@newpath_mdf varchar(1024),	@header varchar(500)


--Set Values to the variables

SET @newpath_mdf =	'E:\somefolder\MSSQL\DATA\' --Specify the new path wherein you will put the mdf
SET @newpath_ldf =	'E:\somefolder\MSSQL\DB Log\' --Specify the new path wherein you will put the ldf
SET @path =			'E:\BACKUPS\'							 --Specify the path of the Backup Files
SET @extension =	'BAK'									 --Extention of the Backups
SET @pathension =   'dir /OD '+@Path+'*.'+@Extension		 --Store the shell command to retreive all .BAK files
SET @DATE =			(CONVERT(VARCHAR,DATEPART(DAY,GETDATE()))) + '/' +(CONVERT(VARCHAR,DATEPART(MONTH,GETDATE()))) --Gets the current day and month


--Insert the value of the command shell to the table

INSERT INTO cmdshell EXEC master..xp_cmdshell @pathension


--Delete non backup file and null values
 
DELETE FROM cmdshell 
WHERE FEntry NOT LIKE '%.BAK%'
DELETE FROM cmdshell 
WHERE FEntry is NULL

-- I added this part to eliminate the backups that were not made the current day
DELETE FROM cmdshell
WHERE FEntry NOT LIKE  @DATE + '%'


--Create a cursor to scan all backup files needed to generate the restore script

DECLARE		@migrate varchar(1024)
DECLARE		migrate CURSOR FOR
SELECT		substring(FEntry,42,54) AS 'FEntry'
FROM		cmdshell


--Open the cursor "migrate"  to insert all backup names into @migrate

OPEN migrate
	FETCH NEXT FROM migrate INTO @migrate
	WHILE (@@FETCH_STATUS = 0)
		BEGIN


			--Get the dbname of the backup file using the RESTORE HEADERONLY command

			SET @header = 'RESTORE HEADERONLY FROM DISK = '+''''+@path+@migrate+''''
			INSERT INTO header 
			EXEC (@header)


			--Get the names of the mdf and ldf files using the RESTORE FILELISTONLY command

			SET @restore = 'RESTORE FILELISTONLY FROM DISK = '+''''+@path+@migrate+''''
			INSERT INTO migration 
			EXEC (@restore)


			--Update value of the table to add the new path+mdf/ldf names
		
			UPDATE migration 
			SET physicalname = reverse(physicalname)
			
			UPDATE migration 
			SET physicalname = substring(physicalname,1,charindex('\',physicalname)-1)

			UPDATE migration 
			SET physicalname = @newpath_mdf+reverse(physicalname) 
			WHERE TYPE = 'D'
			
			UPDATE migration 
			SET physicalname = @newpath_ldf+reverse(physicalname) 
			WHERE TYPE = 'L'


				--Generating the script for FULL RESTORE for backups with mdf, ldf and ndf files

					BEGIN
					BEGIN
				
						SET @restoredb =	'RESTORE DATABASE '+(SELECT TOP 1 DatabaseName FROM header)+
											' FROM DISK = '+''''+@path+@migrate+''''+'WITH FILE =' +''
											+(SELECT MIN (Position) FROM header WHERE BackupType= 1)
											+CHAR(10)+', NORECOVERY, STATS=10, NOUNLOAD, REPLACE, MOVE '
										
										
											--Declare variables and cursor for getting logical and physicalname of the mdf, ldf and ndf files
										
											DECLARE @multiple VARCHAR(1000),@physical VARCHAR(1000)
											DECLARE multiple CURSOR FOR
											SELECT logicalname,physicalname FROM migration
											
												OPEN multiple
													FETCH NEXT FROM multiple INTO @multiple,@physical
													WHILE(@@FETCH_STATUS = 0)
														BEGIN
															SET @restoredb=@restoredb+''''+@multiple+''''+' TO '+''''+@physical+''''+','+'MOVE '+''
															FETCH NEXT FROM multiple INTO @multiple,@physical
														END
												CLOSE multiple
												DEALLOCATE multiple
												
											SET @restoredb =	substring(@restoredb,1,len(@restoredb)-5)
											PRINT (@restoredb)
					END

					EXEC (@restoredb)	-- Run print @restoredb first to view the full backups to be restored
										-- When ready, run exec (@restoredb)

				--Generating the script for DIFFERRENTIAL RESTORE for backups with mdf, ldf and ndf files				
	
					BEGIN

						SET @restoredb =	
						CHAR(10)+'RESTORE DATABASE '+(SELECT TOP 1 DatabaseName FROM header)+
						' FROM DISK = '+''''+@path+@migrate+''''+'WITH FILE =' +''+(SELECT MAX (Position) FROM header WHERE BackupType= 5)
						+CHAR(10)+',STATS=10, NOUNLOAD, MOVE '
						
						--Declare variables and cursor for getting logical and physicalname of the mdf, ldf and ndf files
						
						DECLARE @multiple2 varchar(1000),@physical2 varchar(1000)
						DECLARE multiple2 CURSOR FOR
						SELECT logicalname,physicalname from migration
							OPEN multiple2
								FETCH NEXT FROM multiple2 INTO @multiple2,@physical2
								WHILE(@@FETCH_STATUS = 0)
									BEGIN
										SET @restoredb=	@restoredb+''''+@multiple2+''''+' TO '+''''+@physical2+''''+','+'MOVE '+''
										FETCH NEXT FROM multiple2 INTO @multiple2,@physical2
									END
							CLOSE multiple2
							DEALLOCATE multiple2
						END	
						SET @restoredb = substring(@restoredb,1,len(@restoredb)-5)
						PRINT (@restoredb)
					END
				
				EXEC (@restoredb)	-- Run print @restoredb first to view the differential backups to be restored
									-- When ready, run exec (@restoredb)
				


				--Clear data inside the tables to give way for the next
				--set of informations to be put in the @restoredb variable
				
				TRUNCATE TABLE migration
				TRUNCATE TABLE header
				FETCH NEXT FROM migrate INTO @migrate
			END
		CLOSE migrate
		DEALLOCATE migrate


	--Drop Tables
	DROP TABLE migration
	DROP TABLE cmdshell
	DROP TABLE header