I achieved it by following these steps.
- I moved all the backup files from the network to the local server in a backup folder.
- Created a table BackupFileList to store the current database name and the backup file names using the command shell script
- Executed a dynamic SQL script to create all the databases.
- Executed the dynamic script to restore all the databases I made use of the created table to build a dynamic query to find the database name and the backup file.
First of all need to make sure that the 'xp_cmdshell' is enabled.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO
I ran the following query to see the physical path of the back up sets that was moved over from the network.
restore filelistonly from disk V:\SQL\Backup\Daily\DB1_backup_2015_06_22_194001_8960340.bak'
with file = 1
go
[In SQL Server 2008 and later, you need to have CREATE DATABASE permission to get this information
If there are multiple backup sets on the backup device, specify the backup set number using the “WITH FILE = n” option.
To specify the second backup set on the device run this code:
restore filelistonly from disk V:\SQL\Backup\Daily\DB1_backup_2015_06_22_194001_8960340.bak'
with file = 2
go ]
Script 1 :
Important!!!!
BEFORE RUNNING MAKE SURE THAT THE SERVICE ACCOUNT HAS PERMISSION IN THE PATH DIR.
--To create a backupfiles list and populate a table in the work db
--delete from HCFS_WORKDB..DBBackUpFilesList
CREATE TABLE #DirectoryList (
line VARCHAR(512))
DECLARE @f table (Fname varchar(100))
DECLARE @Path varchar(256) = 'dir V:\SQL\Backup\Daily\'
DECLARE @Command varchar(1024) = @Path+ '/b /a-d /s *.bak ' --' /s /b /o:gn' --/a:d /s /b | sort'
PRINT @Command
INSERT #DirectoryList
EXEC MASTER.dbo.xp_cmdshell @Command
DELETE #DirectoryList WHERE Line IS NULL
delete from #DirectoryList where ( line not like ('%2015_06_24%')) --keep the recent backup file
delete from #DirectoryList where line like '%system%'
delete from #DirectoryList where line like '%\Diffferntial%'
delete from #DirectoryList where line not like '%\client_%'
--select line from #DirectoryList
insert into @f
select SUBSTRING(line,charindex('\client_',line,1)+1,512 ) from #DirectoryList
--capture the database name and the filename in the table
insert into DB_WORKDB..DBBackUpFilesList (DBname,BackupfileName)
select rtrim(left(SUBSTRING(fname ,charindex('\client_',fname,1)+1,100 ),charindex('_backup',SUBSTRING(fname ,charindex('\client_',fname,1)+2,100 ),1))),SUBSTRING(fname ,charindex('\client_',fname,1)+1,100 ) from @f
--select substring(SUBSTRING(line,charindex('\client_',line,1)+1,512 ),charindex('\',line,1)+1,512) from #DirectoryList
DROP TABLE #DirectoryList
GO
-------------------------------------------------------------------------------
Script 2 : Create multiple databases using a cursor
declare @curClientCode as cursor ,@db varchar(100),
@sqlCreateDb nvarchar(max),
@dbName varchar(100)
SET @curClientCode = CURSOR FOR
--crete db for arm clients too
select name from sys.databases
where name like 'client_%'
OPEN @curClientCode
FETCH NEXT
FROM @curClientCode into @db
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dbName = cast(@db as varchar)
SET @sqlCreateDb = 'USE master' + CHAR(13) +
--'DROP DATABASE ' + @dbName + CHAR(13) +
' CREATE DATABASE ' + @dbName + CHAR(13) +
' ON (NAME = ' + @dbName + '_data, FILENAME = ''E:\SQL\Data\' + @dbName +'.mdf'', Size = 100MB,MAXSIZE=unlimited, FILEGROWTH = 10)' + char(13) +
' LOG ON (NAME = ' + @dbName + '_log, FILENAME = ''L:\SQL\Data\' + @dbName +'.ldf'', Size = 5MB,MAXSIZE=500MB, FILEGROWTH = 10); '
print @sqlCreateDb
exec sp_executesql @sqlCreateDb;
FETCH NEXT
FROM @curClientCode into @db
END
CLOSE @curClientCode
DEALLOCATE @curClientCode
---------------------------------------------------------
Script 3 : Restore the database with MOVE command from the file
The backup files name were found from the table in the DB_workdb that was used earlier to capture the database name and the backup files
use master
Go
declare @curClientCode as cursor ,@db varchar(10),
@sqlRestoreDb nvarchar(max),
@dbName varchar(20),
@backupFile varchar(100)
SET @curClientCode = CURSOR FOR
select name from sys.databases
where name like 'client_%'
OPEN @curClientCode
FETCH NEXT
FROM @curClientCode into @db
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dbName = @db
SELECT @backupFile = BackupFileName from HCFS_WORKDB..dbBackUpFilesList where dbname = @dbName
SET @sqlRestoreDb = 'ALTER DATABASE ' + @dbName + char(13) +
' SET SINGLE_USER WITH ROLLBACK IMMEDIATE ' + char(13) + char(13)
SET @sqlRestoreDb = @sqlRestoreDb + ' RESTORE DATABASE ' + @dbName + char(13) +
' FROM DISK = ''V:\SQL\Backup\Daily\' + @dbName + '\'+ + @backupFile + ''''+ char(13)+
' WITH MOVE N''' + @dbName + ''' TO N''E:\SQL\Data\' + @dbName + '.mdf'',' + char(13)+
' MOVE N''' + @dbName + '_log'' TO N''L:\SQL\Data\' + @dbName + '.ldf'',' + char(13)+
' REPLACE, STATS = 10 ' + char(13)
SET @sqlRestoreDb = @sqlRestoreDb + 'ALTER DATABASE ' + @dbName + char(13) +
' SET MULTI_USER WITH ROLLBACK IMMEDIATE ' + char(13)
print @sqlRestoreDb
exec sp_executesql @sqlRestoreDb;
FETCH NEXT
FROM @curClientCode into @db
END
CLOSE @curClientCode
DEALLOCATE @curClientCode
GO
