When i search in the internet i see Stored procedure make restore from all files in directory i read it in the first then i take this Sp and make edit on it to customize it and add New paramter to make anather thing if you want to the source for this Sp go to this link
http://www.karaszi.com/SQLServer/code/sp_RestoreFromAllFilesInDirectory_2008sp1.txt
/*
Customized By Mostafa Elmasry
Dwenload Sp To SQL 2012: http://hotfile.com/dl/142851801/0d8e1c7/SP_Restore_Dynamic.sql.html
Dwenload SP to SQL 2005 : http://hotfile.com/dl/143006210/7b26c20/SP_Restore_Dynamic_to_SQL_2005.sql.html
This Stored Procedure use to Restore All file backup From @SourceDirBackupFiles
its update to work on SQL 2012 but if you run this script on this version must be Add this column [Containment] in the #bdev table in the last i add it
but i make this column is Uncommite . It’s Work very good on all version 2000, 2005, 2008, 2008R2 + 2012 (but don’t forget make Commentto the Column Containment in #bdev Table)
to Exec this Sp must be give it 5 paramter
@SourceDirBackupFiles = Your backup file location
@DestDirDbFiles = database file location MDF will creat on this path
@DestDirLogFiles = database Log_File location lDF will creat on this path
@Mode_Restore = 0 >> the database name will be = Logicalname , 1 >> Tha database name will be the = same backup name
@Exce_Command = 0 >> the Sp will Exec the Command restore , 1 >> The SP will print the comand and don’t make exec
it dont make overwite
IF the backup have more than backup in one file this code make restore to the max position
Restores from all files in a certain directory
Sample execution:
EXEC sp_RestoreFromAllFilesInDirectory
@SourceDirBackupFiles = ‘E:\DataBase\Backup\’,
@DestDirDbFiles = ‘E:\DataBase\R\’ ,
@DestDirLogFiles = ‘E:\DataBase\R\’ ,
@Mode_Restore = 1 ,
@Exce_Command = 0
*/
USE master
GO
/****** Object: StoredProcedure [dbo].[sp_RestoreFromAllFilesInDirectory] Script Date: 04/01/2010 06:08:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create proc [dbo].[sp_RestoreFromAllFilesInDirectory]
(
@SourceDirBackupFiles NVARCHAR(200) ,
@DestDirDbFiles NVARCHAR(200) ,
@DestDirLogFiles NVARCHAR(200) ,
@Mode_Restore int ,
@Exce_Command int
)
AS
begin transaction
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(300)
,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(30,0)
,LastLSN numeric(30,0)
,CheckpointLSN numeric(30,0)
,DatabaseBackupLSN numeric(30,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)
,Containment int ———————– if you use SQl 2012 Enable this Column
)
–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 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)
)
DECLARE @fname VARCHAR(200)
DECLARE @FNAME_RESTORE NVARCHAR(200)
DECLARE @dirfile VARCHAR(300)
DECLARE @LogicalName NVARCHAR(128)
DECLARE @PhysicalName NVARCHAR(260)
DECLARE @type CHAR(1)
DECLARE @DbName sysname
DECLARE @sql NVARCHAR(1000)
Declare @position int
—Check Mode_Reestore IF @Mode_Restore = 0 [Database Name will be = The Logical_name] ElseIF @mode_Restore = 0 [Database Name Will be = The backup Name] Else [Error massage]
DECLARE files CURSOR FOR
–SELECT FNAME FROM #files
select FNAME,left (fname,case charindex(‘.’,fname) when 0 then len(fname) else charindex(‘.’,fname) -1 end)from #files
DECLARE dbfiles CURSOR FOR
SELECT LogicalName, PhysicalName, Type FROM #dbfiles
OPEN files
FETCH NEXT FROM files INTO @fname , @FNAME_RESTORE
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 @position = (select max(position) from #bdev)
SET @DbName = (SELECT DatabaseName FROM #bdev where position = @position)
if @Mode_Restore = 1
begin
–Construct the beginning for the RESTORE DATABASE command
SET @sql = ‘RESTORE DATABASE ‘ + @FNAME_RESTORE + ‘ FROM DISK = ”’ + @dirfile + ”’ WITH MOVE ‘
end
Else If @Mode_Restore = 0
begin
–Construct the beginning for the RESTORE DATABASE command
SET @sql = ‘RESTORE DATABASE ‘ + @DbName + ‘ FROM DISK = ”’ + @dirfile + ”’ WITH MOVE ‘
end
else
BEGIN
— Rollback the transaction
ROLLBACK
— Raise an error and return
RAISERROR (‘Must be @mode_restore = 1 or o.’, 16, 1)
RETURN
end
–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
–For each database file that the database uses
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”’
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type
END
If @Exce_Command = 1
begin
EXEC(@sql)
end
Else IF @Exce_Command = 0
begin
–Here’s the actual RESTORE command
PRINT @sql
End
Else
BEGIN
— Rollback the transaction
ROLLBACK
— Raise an error and return
RAISERROR (‘Must be @Exce_Command = 1 or o.’, 16, 1)
RETURN
end
CLOSE dbfiles
FETCH NEXT FROM files INTO @fname , @FNAME_RESTORE
IF @@ERROR <> 0
BEGIN
— Rollback the transaction
ROLLBACK
— Raise an error and return
RAISERROR (‘Error in restoring the File’, 16, 1)
RETURN
END
END
COMMIT
CLOSE files
DEALLOCATE dbfiles
DEALLOCATE files
DB Cloud TECH 