April 24, 2014 at 3:56 am
ggoble (4/23/2014)
How does this script handle multiple files and filegroups?Edit: Does this script handle multiple file and file groups?
Yes. It handles multiple file & filegroups.
Here is an example for you to test:
CREATE DATABASE FGTest
ON
(NAME='FGTest', FILENAME='D:\SQLTestLab\FGTest.mdf') ,
(NAME='FGTest_2', FILENAME='D:\SQLTestLab\FGTest_2.ndf')
LOG ON
(NAME='FGTest_Log', FILENAME='D:\SQLTestLab\FGTest_Log.ldf')
GO
ALTER DATABASE FGTest ADD FILEGROUP [FG2]
GO
ALTER DATABASE FGTest ADD FILE (NAME='FGTest_3',FILENAME='D:\SQLTestLab\FGTest_3.ndf')
TO FILEGROUP [FG2]
GO
BACKUP DATABASE FGTest TO DISK = 'D:\SQLTestLab\FGTest.BAK' WITH STATS=1, INIT
Once you have created the database & the backup has been taken, you can execute this procedure to check the script printed by it
GO
EXECUTE SPS_RestoreDatabases@BackupFilesDirectory = 'D:\SQLTestLab',
@NewDataFilesDirectory = 'D:\SQLTestLab\DatabaseFiles\DataFiles',
@NewLogFilesDirectory = 'D:\SQLTestLab\DatabaseFiles\LogFiles',
@NewOtherFilesDirectory = 'D:\SQLTestLab\DatabaseFiles\LogFiles',
@BackupFileExtension = 'bak',
@ReplaceExistingDatabases = 0,
@IncludeSystemDatabases = 0,
@AutoExecute = 0
GO
The script printed by the procedure is:
--======================================== Restore Command For Database : [FGTest]
RESTORE DATABASE [FGTest] FROM DISK ='D:\SQLTestLab\FGTest.BAK'
WITH
MOVE'FGTest' TO 'D:\SQLTestLab\DatabaseFiles\DataFiles\FGTest.mdf',
MOVE'FGTest_2' TO 'D:\SQLTestLab\DatabaseFiles\DataFiles\FGTest_2.ndf',
MOVE'FGTest_3' TO 'D:\SQLTestLab\DatabaseFiles\DataFiles\FGTest_3.ndf',
MOVE 'FGTest_Log' TO 'D:\SQLTestLab\DatabaseFiles\LogFiles\FGTest_Log.ldf',
STATS = 1;
--==================================================================================================================================
April 24, 2014 at 5:06 am
jswong05 (4/23/2014)
http://dbace.usActually on SQLSaturday#308, I will show you how to generate a restore script from MSDB using T-SQL.
Jason
Hi Jason,
Actually I have seen that already, what you are going to show on SQLSaturday#308. You can also see it here on SSC on following link:
T-SQL Restore Script Generator By Paul Brewer
[/url]
jswong05 (4/23/2014)
I wrote this and use it for many years on many jobs.
I am sure whatever little You & I have written, has already been written by many others (whether or not they shared their work with the outer world).
jswong05 (4/23/2014)
Two advantages:
1) the MSDB catalog has LSN info as well.
Sure MSDB has got the LSN info, but so does the backup file itself. You can get this information using RESTORE HEADERONLY.
In fact I am thinking to write a similar procedure which generates the restore script based on LSN so that I can include the Differential & Log backup restore functionality as well in my current procedure (currently it supports FULL backups only).
However I will do that by reading the backup files from the directory so that the procedure works even when the MSDB is lost due to a server crash or something (we can use Paul Brewer's script when we have MSDB available).
Now, regarding the benefits you told :
jswong05 (4/23/2014)
Two advantages:
1) the MSDB catalog has LSN info as well.
2) in case someone did an ad-hoc backup that is stored somewhere else, the restore script generated from backup directory files won't work for broken chain. :-D:-P:w00t:
1.In case MSDB on the original server has been lost you will be unable to generate the restore script that depends on MSDB to work properly.
2.In case someone did an ad-hoc backup & deleted that backup file afterwards the restore script generated from MSDB system tables won't work either 😉
As you can see, my point is, that there is nothing that can guarantee to work 100% in all cases. So, the procedure provided in the article & the procedure that you are going to show in SQLSaturday, they both have their places in different situations & environments.
April 24, 2014 at 9:05 am
That was great. Very handy for DR. I am trying to restore all user databases with one script after restoring system databases.
I am having this error with that script:
I must have missed something
Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.
Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.
April 24, 2014 at 1:13 pm
Do a restore header only and check what field is not present in the output in the table definition.
April 24, 2014 at 1:35 pm
Here is what I got:
BackupNameBackupDescriptionBackupTypeExpirationDateCompressedPositionDeviceTypeUserNameServerNameDatabaseNameDatabaseVersionDatabaseCreationDateBackupSizeFirstLSNLastLSNCheckpointLSNDatabaseBackupLSNBackupStartDateBackupFinishDateSortOrderCodePageUnicodeLocaleIdUnicodeComparisonStyleCompatibilityLevelSoftwareVendorIdSoftwareVersionMajorSoftwareVersionMinorSoftwareVersionBuildMachineNameFlagsBindingIDRecoveryForkIDCollationFamilyGUIDHasBulkLoggedDataIsSnapshotIsReadOnlyIsSingleUserHasBackupChecksumsIsDamagedBeginsLogChainHasIncompleteMetaDataIsForceOfflineIsCopyOnlyFirstRecoveryForkIDForkPointLSNRecoveryModelDifferentialBaseLSNDifferentialBaseGUIDBackupTypeDescriptionBackupSetGUIDCompressedBackupSize
Dummy_backup_2014_04_24_142404_6342177NULL1NULL112NT AUTHORITY\SYSTEMDummyInstanceDummy6552013-12-09 15:22:31.0005596160590000000461000375900000004770000159000000046100037590000000436000372014-04-24 14:24:05.0002014-04-24 14:24:05.000520103319660910046081001600DummyInstance5125DBEB00A-CEC3-4AF6-8752-8D1C5B20C4E16A68F82C-88F7-492A-BEC8-B55ABC7B3C63SQL_Latin1_General_CP1_CI_AS6A68F82C-88F7-492A-BEC8-B55ABC7B3C6300000000006A68F82C-88F7-492A-BEC8-B55ABC7B3C63NULLSIMPLENULLNULLDatabase0E70B246-6128-4CAB-9070-72ED0858EEA52004365
April 24, 2014 at 3:07 pm
safazam (4/24/2014)
That was great. Very handy for DR. I am trying to restore all user databases with one script after restoring system databases.I am having this error with that script:
I must have missed something
Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.
Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.
Hi Safazam,
I think you got those errors when you were running the script on a SQL Server version below SQL Server 2012.
You were getting those errors because of the minor changes in output of RESTORE HEADERONLY command in various SQL Server versions. The output of RESTORE HEADERONLY command is different in SQL Server 2012 from what you get in SQL Server 2008 or SQL Server 2005.
Same is true for RESTORE FILELISTONLY command also.
I have updated the script now so that it can detect the version & executes accordingly.
Updated script is given below (it is also attached as a txt file under my signature in this post) & this should work equally well on SQL Server 2005, SQL Server 2008 , SQL Server 2008R2 & SQL Server 2012.
Let me know in case it is still not working on any of the specified versiosn.
Script:
/********************************************************************************************************************
----------->Script to restore all backup files inside a directory to a SQL Server instance.<-----------
Author:Sujeet Pratap Singh
Procedure:SPS_RestoreBackup
Description:This stored procedure takes the directory location of sql server database backup files and restores all the database
backup files from given location.
Parameters:@BackupFilesDirectory = Directory path where all the backup files are stored.
@NewDataFilesDirectory = Directory path to store the Data files of restored databases.
@NewLogFilesDirectory = Directory path to store the Log files of restored databases.
@NewOtherFilesDirectory = Directory path to store the other files (such as Full Text Search files etc.) of restored databases.
@BackupFileExtension = Extension for backup files without dot (.) such as bak for native SQL Server backup files ( you can specify * for all files)
@ReplaceExistingDatabases = Setting this to 1 will replace any existing database.
@IncludeSystemDatabases = Setting this to 1 will generate the restore commands for system database as well
@AutoExecute = It can be set to 0 OR 1. When set to 0 (default), it will only print the restore commands for backup files where as setting it to 1 will EXECUTE the command automatically along with printing.
Execution:EXECUTE SPS_RestoreDatabases@BackupFilesDirectory = 'D:\SQLTestLab\Backups',
@NewDataFilesDirectory = 'D:\SQLTestLab\DatabaseFiles\DataFiles',
@NewLogFilesDirectory = 'D:\SQLTestLab\DatabaseFiles\LogFiles',
@NewOtherFilesDirectory = 'D:\SQLTestLab\DatabaseFiles\LogFiles',
@BackupFileExtension = 'bak',
@ReplaceExistingDatabases = 0,
@IncludeSystemDatabases = 0,
@AutoExecute = 0
Last modified:25-April-2014 (Modified for SQL Server 2012 & above.)
********************************************************************************************************************/
IF OBJECT_ID('SPS_RestoreDatabases') IS NOT NULL
BEGIN
DROP PROCEDURE SPS_RestoreDatabases;
END
GO
CREATE PROCEDURE [dbo].[SPS_RestoreDatabases]
(
@BackupFilesDirectory VARCHAR(500),
@NewDataFilesDirectory VARCHAR(500),
@NewLogFilesDirectory VARCHAR(500),
@NewOtherFilesDirectory VARCHAR(500),
@BackupFileExtension VARCHAR(20) = 'bak',
@ReplaceExistingDatabases BIT = 0,
@IncludeSystemDatabases BIT = 0,
@AutoExecute BIT = 0
)
AS
SET NOCOUNT ON
BEGIN
DECLARE @FileName VARCHAR(1000)
DECLARE @DataFileName VARCHAR(1000)
DECLARE @LogFileName VARCHAR(1000)
DECLARE @FTSFileName VARCHAR(1000)
DECLARE @DataFileMoveStatements VARCHAR(1000)
DECLARE @LogFileMoveStatements VARCHAR(1000)
DECLARE @OtherFileMoveStatements VARCHAR(1000)
DECLARE @GetFileListCMD VARCHAR(1000)
DECLARE @DatabaseName VARCHAR(1000)
DECLARE @FileID INT
DECLARE @DatabaseRestoreCommand VARCHAR(1000)
DECLARE @DataFileCounter INT
DECLARE @LogFileCounter INT
DECLARE @OtherFileCounter INT
DECLARE @RestrictDatabaseAccess VARCHAR(1000)
DECLARE @Ext VARCHAR(11)
DECLARE @SQLServerVersion VARCHAR(30)
DECLARE @Major VARCHAR(5)
DECLARE @Minor VARCHAR(5)
IF (@BackupFilesDirectory IS NULL)
BEGIN
RAISERROR ('Provide a valid backup directory path.',16,1)
END
ELSE IF (@NewDataFilesDirectory IS NULL)
BEGIN
RAISERROR ('Provide a valid path for @NewDataFilesDirectory.',16,1)
END
ELSE IF (@NewLogFilesDirectory IS NULL)
BEGIN
RAISERROR ('Provide a valid path for @NewLogFilesDirectory.',16,1)
END
ELSE IF (@NewOtherFilesDirectory IS NULL)
BEGIN
RAISERROR ('Provide a valid path for @NewOtherFilesDirectory.',16,1)
END
ELSE IF (@IncludeSystemDatabases = 1 AND @AutoExecute = 1)
BEGIN
RAISERROR ('AutoExecute cannot be used with system databases. Set @AutoExecute = 0 to print the restore commands for system databases & then restore them manually OR set @IncludeSystemDatabases = 0 to exclude system databases. ',16,1)
END
ELSE
BEGIN
SET @Major = PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)),4)
SET @Minor = PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)),3)
IF (@Major = '9' AND @Minor='00')
BEGIN
SET @SQLServerVersion = 'SQLServer2005'
END
IF (@Major = '10' AND @Minor='00')
BEGIN
SET @SQLServerVersion = 'SQLServer2008'
END
IF (@Major = '10' AND @Minor='50')
BEGIN
SET @SQLServerVersion = 'SQLServer2008R2'
END
IF (@Major = '11' AND @Minor='0')
BEGIN
SET @SQLServerVersion = 'SQLServer2012'
END
--======================= Storing backup file names
SET @GetFileListCMD = 'DIR '+@BackupFilesDirectory+'\*.'+@BackupFileExtension+' /B /OD'
IF OBJECT_ID('tempdb..#BackupFiles') IS NOT NULL
BEGIN
DROP TABLE #BackupFiles
END
CREATE TABLE #BackupFiles
(
FileID INT IDENTITY(1,1),
[FileName] VARCHAR(1000)
)
INSERT INTO #BackupFiles ([FileName])
EXEC MASTER..xp_cmdshell @GetFileListCMD
DELETE FROM #BackupFiles WHERE [FileName] IS NULL OR [FileName] LIKE '%File Not Found%'
--======================= Temporary table to store backup header information
IF OBJECT_ID('tempdb..#RestoreHeaderResults') IS NOT NULL
BEGIN
DROP TABLE #RestoreHeaderResults
END
CREATE TABLE #RestoreHeaderResults
(
BackupName VARCHAR(250),
BackupDescription VARCHAR(500),
[BackupType] INT,
ExpirationDate DATETIME,
Compressed VARCHAR(2),
Position INT,
DeviceType INT,
UserName VARCHAR(250),
ServerName VARCHAR(250),
DatabaseName VARCHAR(250),
DatabaseVersion VARCHAR(250),
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 INT,
[CodePage] Numeric(18,2),
UnicodeLocaleId INT,
UnicodeComparisonStyle Numeric(18,2),
CompatibilityLevel VARCHAR(10),
SoftwareVendorId VARCHAR(10),
SoftwareVersionMajor Varchar (250),
SoftwareVersionMinor Varchar (250),
SoftwareVersionBuild Varchar (250),
MachineName Varchar (250),
Flags Varchar (250),
BindingID Varchar (250),
RecoveryForkID Varchar (250),
Collation Varchar (250),
FamilyGUID Varchar (250),
HasBulkLoggedData Varchar (250),
IsSnapshot Varchar (250),
IsReadOnly Varchar (250),
IsSingleUser Varchar (250),
HasBackupChecksums Varchar (250),
IsDamaged Varchar (250),
BeginsLogChain Varchar (250),
HasIncompleteMetaData Varchar (250),
IsForceOffline Varchar (250),
IsCopyOnly Varchar (250),
FirstRecoveryForkID Varchar (250),
ForkPointLSN Varchar (250),
RecoveryModel Varchar (250),
DifferentialBaseLSN Varchar (250),
DifferentialBaseGUID Varchar (250),
BackupTypeDescription Varchar (250),
BackupSetGUID Varchar (250),
CompressedBackupSize Numeric(18,2),
Containment TINYINT
)
--======================= Temporary table to store details of database files from backup file
IF OBJECT_ID('tempdb..#RestoreFileListResults') IS NOT NULL
BEGIN
DROP TABLE #RestoreFileListResults
END
CREATE TABLE #RestoreFileListResults
(
RowID INT IDENTITY(1,1),
LogicalName VARCHAR(250),
PhysicalName VARCHAR(500),
[Type] VARCHAR(2),
FileGroupName VARCHAR(100),
Size VARCHAR(100),
MaxSize VARCHAR(100),
FileId INT,
CreateLSN VARCHAR(250),
DropLSN VARCHAR(250),
UniqueId VARCHAR(250),
ReadOnlyLSN VARCHAR(250),
ReadWriteLSN VARCHAR(250),
BackupSizeInBytes VARCHAR(250),
SourceBlockSize INT,
FileGroupId INT,
FileGroupGUID VARCHAR(250),
DifferentialBaseLSN VARCHAR(250),
DifferentialBaseGUID VARCHAR(250),
IsReadOnly VARCHAR(2),
IsPresent VARCHAR(2),
TDEThumbPrint VARCHAR(250)
)
--======================= Getting complete path of backup file
DECLARE @BackupFileName VARCHAR(100)
DECLARE @CompleteBackupFilePath VARCHAR(500)
--======================= Looping through the backup files to generate database restore commands
DECLARE BackupFilesList CURSOR FAST_FORWARD FOR SELECT [FileName] FROM #BackupFiles ORDER BY [FileName],[FileID]
OPEN BackupFilesList
FETCH NEXT FROM BackupFilesList INTO @BackupFileName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CompleteBackupFilePath = @BackupFilesDirectory + '\' + @BackupFileName
--======================= Storing backup header information in temporary table
TRUNCATE TABLE #RestoreHeaderResults;
--===== Version : SQL Server 2005
IF (@SQLServerVersion = 'SQLServer2005')
BEGIN
INSERT INTO #RestoreHeaderResults (BackupName,
BackupDescription,
BackupType,
ExpirationDate,
Compressed,
Position,
DeviceType,
UserName,
ServerName,
DatabaseName,
DatabaseVersion,
DatabaseCreationDate,
BackupSize,
FirstLSN,
LastLSN,
CheckpointLSN,
DatabaseBackupLSN,
BackupStartDate,
BackupFinishDate,
SortOrder,
[CodePage],
UnicodeLocaleId,
UnicodeComparisonStyle,
CompatibilityLevel,
SoftwareVendorId,
SoftwareVersionMajor,
SoftwareVersionMinor,
SoftwareVersionBuild,
MachineName,
Flags,
BindingID,
RecoveryForkID,
Collation,
FamilyGUID,
HasBulkLoggedData,
IsSnapshot,
IsReadOnly,
IsSingleUser,
HasBackupChecksums,
IsDamaged,
BeginsLogChain,
HasIncompleteMetaData,
IsForceOffline,
IsCopyOnly,
FirstRecoveryForkID,
ForkPointLSN,
RecoveryModel,
DifferentialBaseLSN,
DifferentialBaseGUID,
BackupTypeDescription,
BackupSetGUID
)
EXEC('RESTORE HEADERONLY FROM DISK = '+''''+@CompleteBackupFilePath+'''')
END
--===== Version : SQL Server 2008 & 2008 R2
IF (@SQLServerVersion = 'SQLServer2008' OR @SQLServerVersion = 'SQLServer2008R2')
BEGIN
INSERT INTO #RestoreHeaderResults (BackupName,
BackupDescription,
BackupType,
ExpirationDate,
Compressed,
Position,
DeviceType,
UserName,
ServerName,
DatabaseName,
DatabaseVersion,
DatabaseCreationDate,
BackupSize,
FirstLSN,
LastLSN,
CheckpointLSN,
DatabaseBackupLSN,
BackupStartDate,
BackupFinishDate,
SortOrder,
[CodePage],
UnicodeLocaleId,
UnicodeComparisonStyle,
CompatibilityLevel,
SoftwareVendorId,
SoftwareVersionMajor,
SoftwareVersionMinor,
SoftwareVersionBuild,
MachineName,
Flags,
BindingID,
RecoveryForkID,
Collation,
FamilyGUID,
HasBulkLoggedData,
IsSnapshot,
IsReadOnly,
IsSingleUser,
HasBackupChecksums,
IsDamaged,
BeginsLogChain,
HasIncompleteMetaData,
IsForceOffline,
IsCopyOnly,
FirstRecoveryForkID,
ForkPointLSN,
RecoveryModel,
DifferentialBaseLSN,
DifferentialBaseGUID,
BackupTypeDescription,
BackupSetGUID,
CompressedBackupSize
)
EXEC('RESTORE HEADERONLY FROM DISK = '+''''+@CompleteBackupFilePath+'''')
END
--===== Version : SQL Server 2012
IF (@SQLServerVersion = 'SQLServer2012')
BEGIN
INSERT INTO #RestoreHeaderResults (BackupName,
BackupDescription,
BackupType,
ExpirationDate,
Compressed,
Position,
DeviceType,
UserName,
ServerName,
DatabaseName,
DatabaseVersion,
DatabaseCreationDate,
BackupSize,
FirstLSN,
LastLSN,
CheckpointLSN,
DatabaseBackupLSN,
BackupStartDate,
BackupFinishDate,
SortOrder,
[CodePage],
UnicodeLocaleId,
UnicodeComparisonStyle,
CompatibilityLevel,
SoftwareVendorId,
SoftwareVersionMajor,
SoftwareVersionMinor,
SoftwareVersionBuild,
MachineName,
Flags,
BindingID,
RecoveryForkID,
Collation,
FamilyGUID,
HasBulkLoggedData,
IsSnapshot,
IsReadOnly,
IsSingleUser,
HasBackupChecksums,
IsDamaged,
BeginsLogChain,
HasIncompleteMetaData,
IsForceOffline,
IsCopyOnly,
FirstRecoveryForkID,
ForkPointLSN,
RecoveryModel,
DifferentialBaseLSN,
DifferentialBaseGUID,
BackupTypeDescription,
BackupSetGUID,
CompressedBackupSize,
Containment)
EXEC('RESTORE HEADERONLY FROM DISK = '+''''+@CompleteBackupFilePath+'''')
END
--======================= Storing internal details of data & log files on the backup file
TRUNCATE TABLE #RestoreFileListResults;
IF (@SQLServerVersion = 'SQLServer2005')
BEGIN
INSERT INTO #RestoreFileListResults (
LogicalName,
PhysicalName,
[Type],
FileGroupName,
Size,
MaxSize,
FileId,
CreateLSN,
DropLSN,
UniqueId,
ReadOnlyLSN,
ReadWriteLSN,
BackupSizeInBytes,
SourceBlockSize,
FileGroupId,
FileGroupGUID,
DifferentialBaseLSN,
DifferentialBaseGUID,
IsReadOnly,
IsPresent
)
EXEC('RESTORE FILELISTONLY FROM DISK = '+''''+@CompleteBackupFilePath+'''')
END
ELSE IF (@SQLServerVersion = 'SQLServer2008' OR @SQLServerVersion = 'SQLServer2008R2' OR @SQLServerVersion='SQLServer2012')
BEGIN
INSERT INTO #RestoreFileListResults (
LogicalName,
PhysicalName,
[Type],
FileGroupName,
Size,
MaxSize,
FileId,
CreateLSN,
DropLSN,
UniqueId,
ReadOnlyLSN,
ReadWriteLSN,
BackupSizeInBytes,
SourceBlockSize,
FileGroupId,
FileGroupGUID,
DifferentialBaseLSN,
DifferentialBaseGUID,
IsReadOnly,
IsPresent,
TDEThumbPrint)
EXEC('RESTORE FILELISTONLY FROM DISK = '+''''+@CompleteBackupFilePath+'''')
END
--======================= Getting the name of the new database to be restored
SELECT @DatabaseName = DatabaseName FROM #RestoreHeaderResults
SET @DataFileMoveStatements = ''
SET @LogFileMoveStatements = ''
SET @OtherFileMoveStatements = ''
--======================= Generating statments to move data files
SELECT@DataFileMoveStatements = @DataFileMoveStatements + 'MOVE'+''''+LogicalName+''''+' TO '+''''+@NewDataFilesDirectory+'\'+ RIGHT(RFLR.PhysicalName, CHARINDEX('\', REVERSE(RFLR.PhysicalName))-1) +''','+CHAR(13)
FROM#RestoreFileListResults RFLR
WHERERFLR.[Type] = 'D'
ORDER BY RFLR.FileId
--======================= Generating statments to move log files
SELECT@LogFileMoveStatements = @LogFileMoveStatements+'MOVE '+''''+LogicalName+''''+' TO '+''''+@NewLogFilesDirectory+'\'+ RIGHT(RFLR.PhysicalName, CHARINDEX('\', REVERSE(RFLR.PhysicalName))-1) +''','+CHAR(13)
FROM#RestoreFileListResults RFLR
WHERERFLR.[Type] = 'L'
ORDER BY RFLR.FileId
--======================= Generating statments to move other files such as FileStream or FullTextCatalog files
SELECT@OtherFileMoveStatements = @OtherFileMoveStatements+'MOVE '+''''+LogicalName+''''+' TO '+''''+@NewOtherFilesDirectory+'\'+ RIGHT(RFLR.PhysicalName, CHARINDEX('\', REVERSE(RFLR.PhysicalName))-1) +''','+CHAR(13)
FROM#RestoreFileListResults RFLR
WHERERFLR.[Type] NOT IN ('D','L')
ORDER BY RFLR.FileId
--======================= Preparing database restore commands
IF (@ReplaceExistingDatabases = 1)
BEGIN
SET @RestrictDatabaseAccess = ''
IF DB_ID(@DatabaseName) IS NOT NULL
BEGIN
SET @RestrictDatabaseAccess = 'ALTER DATABASE ['+ @DatabaseName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'
END
SET @DatabaseRestoreCommand = 'RESTORE DATABASE ['+@DatabaseName+'] FROM DISK ='+''''+ @CompleteBackupFilePath+''''+CHAR(13)+'WITH '+CHAR(13)+@DataFileMoveStatements + @LogFileMoveStatements + @OtherFileMoveStatements + 'STATS = 1,REPLACE;'+CHAR(13)+''
SET @DatabaseRestoreCommand = @RestrictDatabaseAccess + CHAR(13) + CHAR(13) + @DatabaseRestoreCommand
END
ELSE
BEGIN
SET @DatabaseRestoreCommand = 'RESTORE DATABASE ['+@DatabaseName+'] FROM DISK ='+''''+ @CompleteBackupFilePath+''''+CHAR(13)+'WITH '+CHAR(13)+@DataFileMoveStatements + @LogFileMoveStatements + @OtherFileMoveStatements + 'STATS = 1;'+CHAR(13)+''
END
--======================= Preparing & printing database restore commands
IF (@AutoExecute = 0 AND @IncludeSystemDatabases = 1)
BEGIN
PRINT CHAR(13) + '--' + REPLICATE('=',40) + ' Restore Command For Database : ['+@DatabaseName+']' + CHAR(13)
PRINT @DatabaseRestoreCommand
PRINT CHAR(13) + '--' + REPLICATE('=',130)
END
IF (@AutoExecute = 0 AND @IncludeSystemDatabases = 0)
BEGIN
IF (UPPER(@DatabaseName) NOT IN ('MASTER','MSDB','MODEL','TEMPDB','DISTRIBUTION') AND NOT EXISTS (SELECT * FROM sys.databases WHERE [name] = @DatabaseName AND is_distributor = 1))
BEGIN
PRINT CHAR(13) + '--' + REPLICATE('=',40) + ' Restore Command For Database : ['+@DatabaseName+']' + CHAR(13)
PRINT @DatabaseRestoreCommand
PRINT CHAR(13) + '--' + REPLICATE('=',130)
END
END
IF (@AutoExecute = 1 AND @IncludeSystemDatabases = 0)
BEGIN
IF (UPPER(@DatabaseName) NOT IN ('MASTER','MSDB','MODEL','TEMPDB','DISTRIBUTION') AND NOT EXISTS (SELECT * FROM sys.databases WHERE [name] = @DatabaseName AND is_distributor = 1))
BEGIN
PRINT CHAR(13) + '--' + REPLICATE('=',40) + ' Executing Restore Command For Database : ['+@DatabaseName+']' + CHAR(13)
PRINT @DatabaseRestoreCommand
EXECUTE (@DatabaseRestoreCommand)
PRINT CHAR(13) + '--' + REPLICATE('=',130)
END
END
FETCH NEXT FROM BackupFilesList INTO @BackupFileName
END
CLOSE BackupFilesList
DEALLOCATE BackupFilesList
END
END
April 24, 2014 at 3:52 pm
Thank you for your reply. I was using sql2008 and sql 2012
This is from 2008
Now it says command completed successfully ,but no command printed, I don't see those tables in tempdb temporary tables either after creating SP.
EXECUTE SPS_RestoreDatabases
@BackupFilesDirectory = 'C:\Backup',
@NewDataFilesDirectory = 'C:\Program Files\Microsoft SQL Server\MSSQL10.TFB\MSSQL\DATA',
@NewLogFilesDirectory = 'C:\Program Files\Microsoft SQL Server\MSSQL10.TFB\MSSQL\DATA',
@NewOtherFilesDirectory = 'C:\Program Files\Microsoft SQL Server\MSSQL10.TFB\MSSQL\DATA',
@BackupFileExtension = 'bak',
@ReplaceExistingDatabases = 1,
@IncludeSystemDatabases = 0,
@AutoExecute = 0
no result,messages
Command(s) completed successfully.
April 25, 2014 at 12:29 am
safazam (4/24/2014)
Thank you for your reply. I was using sql2008 and sql 2012This is from 2008
Now it says command completed successfully ,but no command printed, I don't see those tables in tempdb temporary tables either after creating SP.
EXECUTE SPS_RestoreDatabases
@BackupFilesDirectory = 'C:\Backup',
@NewDataFilesDirectory = 'C:\Program Files\Microsoft SQL Server\MSSQL10.TFB\MSSQL\DATA',
@NewLogFilesDirectory = 'C:\Program Files\Microsoft SQL Server\MSSQL10.TFB\MSSQL\DATA',
@NewOtherFilesDirectory = 'C:\Program Files\Microsoft SQL Server\MSSQL10.TFB\MSSQL\DATA',
@BackupFileExtension = 'bak',
@ReplaceExistingDatabases = 1,
@IncludeSystemDatabases = 0,
@AutoExecute = 0
no result,messages
Command(s) completed successfully.
Hi Safazam,
I had tested the procedure on SQL Server 2005, 2008 R2 & 2012. However, I couldn't test it on SQL Server 2008 as I didn't have it.
Could you run following command & let me know the output:
SELECT PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)),4) AS Major , PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)),3) AS Minor
If you go through the procedure you will get a section like below. Change the values of @Major & @Minor variables with the values you get by running the command given above :
IF (@Major = '10' AND @Minor='00')
BEGIN
SET @SQLServerVersion = 'SQLServer2008'
END
April 28, 2014 at 8:50 am
MajorMinor
100
I have changed it to '0' and it printed the command.
Now I am trying to add diff and log files for restore. I will keep you posted if anything.
Thank you for your help.
May 7, 2014 at 7:11 am
Have you never heard of powershell...
May 9, 2014 at 4:37 am
steveyeadon (5/7/2014)
Have you never heard of powershell...
You must have felt better after saying that. I am glad for you :cool:.
September 23, 2016 at 4:23 am
I know I am about two years late to the party but this script is great. We are migrating 500 databases to a new instance and this saved me so much time.
November 28, 2016 at 3:31 am
tatlatme (9/23/2016)
I know I am about two years late to the party but this script is great. We are migrating 500 databases to a new instance and this saved me so much time.
Thanks Tatlatme.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply