-- presently using this script
-- attempting to automate as much as possible
-- backup files located on G:\Transfer\ *.BAK
-- Having problems with dynamic execution EXEC @Restore
-- this is the error
RETURN yitzstokes@gmail.com
/*
RESTORE DATABASE [CSOS]
FROM DISK =
'G:\Transfer\CSOS.BAK'
WITH
MOVE 'CSOS_Data' TO 'E:\Data\CSOS_Data.MDF' ,' is not a valid identifier.
*/
USE [master]
GO
SET NOCOUNT ON
GO
DECLARE
@backup_path varchar(500),
@totCount int,
@Current int,
@dirCmd varchar(200),
@Filename varchar(200),
@DatabaseName varchar(200),
@CreateDBFolder bit,
@tab varchar(1),
@cr varchar(2),
@new_data_path varchar(500),
@new_data_path2 varchar(500),
@new_log_path varchar(500),
@new_log_path2 varchar(500),
@Restore nvarchar(4000)
SELECT @backup_path = 'G:\Transfer\' --> Folder where full database backupfiles are located
SELECT @new_data_path = 'E:\Data\' --> Optional, leave blank to use the default from the backup file
SELECT @new_log_path = 'F:\Log\' --> Optional, leave blank to use the default from the backup file
SELECT @CreateDBFolder = 0 --> If 1 is specidied the data/log files will be placed in a separate folder named after the database
CREATE TABLE #BackupFiles (
ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
[Filename] VARCHAR(500)
)
SET @dirCmd = 'dir /b "' + @backup_path + '*.bak" '
INSERT INTO #BackupFiles ([Filename])
EXEC xp_cmdshell @dirCmd
--select * from #BackupFiles
--drop table #BackupFiles
print ' Delete excess row in #BackupFiles'
DELETE #BackupFiles WHERE [Filename] IS NULL OR [Filename] NOT LIKE '%.bak'
print ' Delete excess row in #BackupFiles completed'
CREATE TABLE #header (
BackupName nvarchar(128),
BackupDescription nvarchar(255),
BackupType smallint,
ExpirationDate datetime,
Compressed bit,
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),
FamilyGUID uniqueidentifier,
HasBulkLoggedData bit,
IsSnapshot bit,
IsReadOnly bit,
IsSingleUser bit,
HasBackupChecksums bit,
IsDamaged bit,
BeginsLogChain bit,
HasIncompleteMetaData bit,
IsForceOffline bit,
IsCopyOnly bit,
FirstRecoveryForkID uniqueidentifier,
ForkPointLSN numeric(25,0) NULL,
RecoveryModel nvarchar(60),
DifferentialBaseLSN numeric(25,0) NULL,
DifferentialBaseGUID uniqueidentifier,
BackupTypeDescription nvarchar(60),
BackupSetGUID uniqueidentifier NULL,
CompressedBackupSize bigint,
Seq int NOT NULL identity(1,1)
)
CREATE TABLE #filelist (
LogicalName nvarchar(256),
PhysicalName nvarchar(260),
Type char(1),
FileGroupName nvarchar(256),
Size numeric(20,0),
MaxSize numeric(20,0),
FileID bigint,
CreateLSN numeric(20,0),
DropLSN numeric(20,0) NULL,
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(20,0) NULL,
ReadWriteLSN numeric(20,0) NULL,
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupID int,
LogGroupGUID uniqueidentifier NULL,
DifferentialBaseLSN numeric(20,0) NULL,
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit,
TDEThumbprint varbinary(32),
Seq int NOT NULL identity(1,1)
)
print ' tables header & filelist created'
SET @totCount = (SELECT COUNT(*) FROM #BackupFiles)
SET @Current = 1
SELECT @tab = char(9), @cr = char(13)+Char(10)
--> Loop through the files that hasn't been restored before and restore them one by one
print 'starting loop ' + 'total count:'+cast(@totCount as char(5))
WHILE (@Current <= @totCount AND @totCount > 0)
BEGIN
SELECT @Filename = [Filename] FROM #BackupFiles WHERE ID = @Current
INSERT INTO #header
EXEC ('RESTORE HeaderOnly FROM DISK = ''' + @backup_path + @Filename + '''')
INSERT INTO #filelist
EXEC ('RESTORE FilelistOnly FROM DISK = ''' + @backup_path + @Filename + '''')
SELECT
@DatabaseName = Databasename,
@new_data_path2 = CASE
WHEN @CreateDBFolder = 1 THEN @new_data_path + DatabaseName + '\'
ELSE @new_data_path
END,
@new_log_path2 = CASE
WHEN @CreateDBFolder = 1 THEN @new_log_path + DatabaseName + '\'
ELSE @new_log_path
END
FROM #header
--SET @Current = @Current + 1
print 'Replace the old data file path with a new one if specified'
IF @new_data_path2 <> ''
UPDATE #filelist
SET PhysicalName = @new_data_path2 + right(physicalname,
CHARINDEX('\',REVERSE(physicalname))-1)
WHERE Type = 'D'
print' Replace the old log file path with a new one if specified'
IF @new_log_path2 <> ''
UPDATE #filelist
SET PhysicalName = @new_log_path2 + right(physicalname,
CHARINDEX('\',REVERSE(physicalname))-1)
WHERE Type = 'L'
--> Print restore script
declare @restore_script table (vrestore nvarchar(4000))
insert into @restore_script
select CASE
WHEN a.Seq = 1 AND @CreateDBFolder = 1 THEN
@cr + 'EXEC xp_cmdshell ''md ' + @new_data_path2 + '''' +
@cr + 'EXEC xp_cmdshell ''md ' + @new_log_path2 + ''''
ELSE ''
END
+
CASE
WHEN a.Seq = 1 THEN @cr +
@cr + 'RESTORE DATABASE [' + c.DatabaseName + ']' +
@cr + 'FROM DISK =' + @cr + @tab + '''' +
@backup_path + @Filename + '''' + @cr + 'WITH' + @cr
ELSE ''
END
+
@tab + 'MOVE ''' + a.LogicalName + ''' TO ''' + a.PhysicalName + ''' ,'
+
CASE
WHEN a.Seq = b.Seq THEN
@cr + @tab + 'REPLACE'
ELSE ''
END
FROM
#filelist a
CROSS JOIN
(SELECT Seq = MAX(b1.Seq) FROM #filelist b1 ) b
CROSS JOIN
(SELECT DatabaseName = MAX(c1.DatabaseName) FROM #header c1) c
ORDER BY
a.Seq
SET @Current = @Current + 1
--@Restore
-- exec (@Restore)
TRUNCATE TABLE #header
TRUNCATE TABLE #filelist
END
print 'execute restore script table'
--select * from @restore_script;
print 'execute loop to execute string'
declare curs cursor for
select vrestore from @restore_script;
open curs;
fetch next from curs into @Restore;
WHILE (@@FETCH_STATUS <> -1)
BEGIN;
-- print @Restore;
EXEC @Restore;
-- print 'exec '+substring(@restore,17,10)
fetch next from curs into @Restore ;
END;
PRINT 'databases restored';
CLOSE curs;
DEALLOCATE curs;
GO
--select * from #header
--select * from #filelist
DROP TABLE #header
DROP TABLE #filelist
DROP TABLE #BackupFiles
As you have requested
dbrestore