Modified Script with some changes......
Add dbname and filename from bak file, Add secondary file location if there is different folder, add with recovery or norecovery flag.
Will be helpful in db migration..... Running successful for SQL 2012.
*********************************************************************
Script
**********************************************************************************************************************
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_CSS_RestoreDir_ndf_Final] Script Date: 31.07.2017 14:17:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
/***************************************************************************************/
-- Procedure Name: sp_CSS_RestoreDir
-- Purpose: Restore one or many database backups from a single directory. This script reads all
-- database backups that are found in the @restoreFromDir parameter.
-- Any database backup that matches the form %_db_% will be restored to
-- the file locations specified in the RestoreTo... parameter(s). The database
-- will be restored to a database name that is based on the database backup
-- file name. For example Insurance_db_200305212302.BAK will be restored to
-- a database named Insurance. The characters preceeding the '_db_' text determines
-- the name.
--
-- Input Parameters: @restoreFromDir - The directory where the database backups are located
-- @restoreToDataDir - The directory where the data files (i.e. MDF) will be restored to
-- @restoreToLogDir - The directory where the log files (i.e. LDF) will be restored to. If
-- this parameter is not provided then the log files are restored to @restoreToDataDir.
-- @MatchFileList - set to 'Y' to restore to same directory structure contained in the backup,
-- also allows for secondary data files 'ndf' to to be in a different dir than mdf files
-- @DBName - restore just this one database - selects the latest bak file
--
-- Output Parameters: None
--
-- Return Values:
--
-- Written By: Chris Gallelli -- 8/22/03
-- Modified By:
-- Modifications: Bruce Canaday -- 10/20/2003
-- Added optional parameters @MatchFileList and @DBName
-- Bruce Canaday -- 10/24/2003
-- Get the db name as the characters LEFT of the right most '_db_' in the bak filenaame
-- This is to handle databases such as ALIS_DB
-- Bruce Canaday -- 10/28/2003
-- When using @MatchFileList = 'Y' attempt to create any directory that doesn't exist
-- Bruce Canaday -- 11/04/2003
-- Allow spaces in the @restoreFromDir directory name
-- paul Wegmann -- 07/11/2012
-- Chnaged the create table to allow more feilds to support SQL Server 2008r2 and SQl Server 2012
-- create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20),
-- FileId int,CreateLSN bit, DropLSN bit, UniqueID varchar(255),ReadOnlyLSn bit, ReadWriteLSN bit, backupSizeInBytes varchar(50), SourceBlockSize int,
-- FileGroupid Int, LogGroupGUID varchar(255),DifferentialBaseLSN varchar(255),DifferentialBaseGUID varchar(255),isReadOnly bit, IsPresent bit,TDEThumbprint varchar(255) )
-- Paul Wegmann -- 07/11/2012 changed from stored proc to set
-- declare @restoreFromDir varchar(255),
-- @restoreToDataDir varchar(255),
-- @restoreToLogDir varchar(255) ,
-- @MatchFileList char(1) ,
-- @OneDBName varchar(255)
--
-- set @restoreFromDir = 'location of directory where your backup exist'
-- set @restoreToDataDir = 'location where your data files will be restored too'
-- set @restoreToLogDir = 'location of LDF files needs to be restored too'
-- set @restoreToSecondaryDataDir = 'Location of secondary data file for all databases'
-- set @recovery = 0 -- default is 0 (without recovery) ; 1 for with recovery
-- set @MatchFileList = 'N'
-- set @OneDBName = null
-- Nitin Varshney - 07/01/2017 1. changes for picking db name from backup file,
-- 2. Added secondary File Location
-- 3. With Recovery or without recovery option
-- 4. All bak file will be select from the folder
-- 5. Print all restore command.
-- Sample Execution : exec sp_CSS_RestoreDir_ndf_Final 'G:\SQLBACKUP_File', 'G:\Data1', 'G:\Log','G:\Data2',1
--
-- Sample Execution: exec sp_CSS_RestoreDir_ndf_Final 'C:\sqldb\sql_backup', 'C:\sqldb\sql_data', 'C:\sqldb\sql_log' (if you use declare/set option then you don't have to use this command to restore)
--
-- Alternate Execution: exec sp_CSS_RestoreDir_ndf_Final 'C:\sqldb\sql_backup', @MatchFileList = 'Y' (if you use declare/set option then you don't have to use this command to restore)
--
-- Reviewed By: Anoar Hassan
--
/***************************************************************************************/
Alter proc sp_CSS_RestoreDir_ndf_Final
@restoreFromDir varchar(255),
@restoreToDataDir varchar(255)= null,
@restoreToLogDir varchar(255) = null,
@restoreToSecondaryDataDir varchar(255) = null,
@recovery int = 0,
@MatchFileList char(1) = 'N',
@OneDBName varchar(255) = null
as
-- to use delare/set option, use the following code and commond -- the create proc SP_CSS_RestoreDir
-- declare @restoreFromDir varchar(255),
-- @restoreToDataDir varchar(255),
-- @restoreToLogDir varchar(255) ,
-- @MatchFileList char(1) ,
-- @OneDBName varchar(255)
--
-- set @restoreFromDir = 'M:\WEBQA2008R2'
-- set @restoreToDataDir = 'J:\MSSQL10_50.WEBQASQL2008R2\MSSQL\DATA'
-- set @restoreToLogDir = 'J:\MSSQL10_50.WEBQASQL2008R2\MSSQL\Log'
-- set @MatchFileList = 'N'
-- set @OneDBName = null
--If a directory for the Log file is not supplied then use the data directory
If @restoreToLogDir is null
set @restoreToLogDir = @restoreToDataDir
If @restoreToSecondaryDataDir is null
set @restoreToSecondaryDataDir = @restoreToDataDir
set nocount on
declare @filename varchar(255),
@cmd varchar(8000),
@cmd2 varchar(500),
@cmd3 varchar(255),
@DataName varchar (255),
@LogName varchar (255),
@LogicalName varchar(255),
@PhysicalName varchar(255),
@Type varchar(20),
@FileGroupName varchar(255),
@Size varchar(20),
@MaxSize varchar(20),
@restoreToDir varchar(255),
@searchName varchar(255),
@DBName varchar(255),
@PhysicalFileName varchar(255)
create table #dirList (filename varchar(100))
--edited by Anoar
create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20),
FileId int,CreateLSN bit, DropLSN bit, UniqueID varchar(255),ReadOnlyLSn bit, ReadWriteLSN bit, backupSizeInBytes varchar(50), SourceBlockSize int,
FileGroupid Int, LogGroupGUID varchar(255),DifferentialBaseLSN varchar(255),DifferentialBaseGUID varchar(255),isReadOnly bit, IsPresent bit,TDEThumbprint varchar(255) )
Create table #Dbnameheaders
(
BackupName varchar(256), BackupDescription varchar(256), BackupType varchar(256), ExpirationDate varchar(256), Compressed varchar(256), Position varchar(256),
DeviceType varchar(256), UserName varchar(256), ServerName varchar(256), DatabaseName varchar(256), DatabaseVersion varchar(256), DatabaseCreationDate varchar(256),
BackupSize varchar(256), FirstLSN varchar(256), LastLSN varchar(256), CheckpointLSN varchar(256), DatabaseBackupLSN varchar(256), BackupStartDate varchar(256),
BackupFinishDate varchar(256), SortOrder varchar(256), CodePage varchar(256), UnicodeLocaleId varchar(256), UnicodeComparisonStyle varchar(256), CompatibilityLevel varchar(256),
SoftwareVendorId varchar(256), SoftwareVersionMajor varchar(256), SoftwareVersionMinor varchar(256), SoftwareVersionBuild varchar(256), MachineName varchar(256), Flags varchar(256),
BindingID varchar(256), RecoveryForkID varchar(256), Collation varchar(256), FamilyGUID varchar(256), HasBulkLoggedData varchar(256), IsSnapshot varchar(256), IsReadOnly varchar(256),
IsSingleUser varchar(256), HasBackupChecksums varchar(256), IsDamaged varchar(256), BeginsLogChain varchar(256), HasIncompleteMetaData varchar(256), IsForceOffline varchar(256),
IsCopyOnly varchar(256), FirstRecoveryForkID varchar(256), ForkPointLSN varchar(256), RecoveryModel varchar(256), DifferentialBaseLSN varchar(256), DifferentialBaseGUID varchar(256),
BackupTypeDescription varchar(256), BackupSetGUID varchar(256), CompressedBackupSize varchar(256), Containment varchar(256)
);
--Get the list of database backups that are in the restoreFromDir directory
if @OneDBName is null
select @cmd = 'dir /b /on "' +@restoreFromDir+ '"'
else
select @cmd = 'dir /b /o-d /o-g "' +@restoreFromDir+ '"'
--select @cmd,'AllFiles' -- Give All Files in Backup Folder
insert #dirList exec master..xp_cmdshell @cmd
--select * from #dirList where filename like '%_db_%' --order by filename -- List all the files in backup folder
select * from #dirList where filename like '%.bak' --order by filename -- List all the files in backup folder
if @OneDBName is null
declare BakFile_csr cursor for
select * from #dirList where filename like '%.bak' order by filename
else
begin -- single db, don't order by filename, take default latest date /o-d parm in dir command above
select @searchName = @OneDBName + '_db_%bak'
select @searchName = @OneDBName + '%.bak'
declare BakFile_csr cursor for
select top 1 * from #dirList where filename like @searchName
end
open BakFile_csr
fetch BakFile_csr into @filename
while @@fetch_status = 0
begin
select @cmd = "RESTORE FILELISTONLY FROM disk = '" + @restoreFromDir + "\" + @filename + "'"
insert #filelist exec ( @cmd )
--identify the db name from backup file
select @cmd3 = "RESTORE HEADERONLY FROM disk = '" + @restoreFromDir + "\" + @filename + "'"
insert #Dbnameheaders exec (@cmd3)
Select @dbName = DatabaseName from #Dbnameheaders
--identify the db name from backup file [END Here]
if @OneDBName is null
select @dbName = @dbName --left(@filename,datalength(@filename) - patindex('%_bd_%',reverse(@filename))-3)
else
select @dbName = @OneDBName
select @cmd = "RESTORE DATABASE " + @dbName +
" FROM DISK = '" + @restoreFromDir + "\" + @filename + "' WITH "
PRINT ''
PRINT 'RESTORING DATABASE ' + @dbName
--Select * from #filelist ---List of files in backupfile all mdf,ndf,ldf
declare DataFileCursor cursor for
select LogicalName, PhysicalName, Type, FileGroupName, Size, MaxSize
from #filelist
open DataFileCursor
fetch DataFileCursor into @LogicalName, @PhysicalName, @Type, @FileGroupName, @Size, @MaxSize
while @@fetch_status = 0
begin
if @MatchFileList != 'Y'
begin -- RESTORE with MOVE option
select @PhysicalFileName = reverse(substring(reverse(rtrim(@PhysicalName)),1,patindex('%\%',reverse(rtrim(@PhysicalName)))-1 ))
/*
if @Type = 'L'
select @restoreToDir = @restoreToLogDir
else
select @restoreToDir = @restoreToDataDir
*/
if @Type = 'L'
select @restoreToDir = @restoreToLogDir
else if @PhysicalFileName like '%.ndf'
select @restoreToDir = @restoreToSecondaryDataDir
else
select @restoreToDir = @restoreToDataDir
select @cmd = @cmd +
" MOVE '" + @LogicalName + "' TO '" + @restoreToDir + "\" + @PhysicalFileName + "', "
end
else
begin -- Match the file list, attempt to create any missing directory
select @restoreToDir = left(@PhysicalName,datalength(@PhysicalName) - patindex('%\%',reverse(@PhysicalName)) )
select @cmd2 = "if not exist " +@restoreToDir+ " md " +@restoreToDir
exec master..xp_cmdshell @cmd2
end
fetch DataFileCursor into @LogicalName, @PhysicalName, @Type, @FileGroupName, @Size, @MaxSize
end -- DataFileCursor loop
close DataFileCursor
deallocate DataFileCursor
if @recovery = 0
select @cmd = @cmd + ' NORECOVERY, STATS = 5'
else
select @cmd = @cmd + ' RECOVERY, STATS = 5'
select @cmd 'command'
--select 'test1'
--EXEC (@CMD)
truncate table #filelist
truncate table #Dbnameheaders
fetch BakFile_csr into @filename
end -- BakFile_csr loop
close BakFile_csr
deallocate BakFile_csr
drop table #dirList
return