Double click to highlight it all, copy / paste in a query analyser window on the primary server (SQL Server 2005 or higher) where the backup history exists. Copy / Paste only works with Chrome or newer versions of Internet Explorer, a line feed/character return problem corrupts the T-SQL if copied using Internet Explorer 8 or lower.
USE [master]
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_RestoreGene')
EXEC ('CREATE PROC dbo.sp_RestoreGene AS SELECT ''stub version, to be replaced''')
GO
/*********************************************************************************************
Restore Generator v8.3 (2022-04-11)
(C) 2012, Paul Brewer
Feedback: paulbrewer@yahoo.co.uk
User Guide: https://restoregene.com
This procedure queries msdb database backup history and database file details in master.
It builds and returns RESTORE DATABASE commands as its result set, it does not execute the commands.
----------------------------------------------------------------------------------------------
PARAMETERS:
@Database - Default NULL, Restore a specific database or a list of comma seperated database names, NULL which restores all user databases.
@TargetDatabase - Defaults to NULL, override restored database name, only possible if restoring one specific database, if supplied will rename database files and log file too.
@WithMoveDataFiles - Defaults to the actual data file paths, overrides WITH MOVE for data file folder
@WithMoveLogFile - Defaults to the actual log file path, overrides WITH MOVE for log file folder
@WithMoveFileStreamFile - Defaults to the actual file stream file path, overrides WITH MOVE for file stream file folder
@FromFileFullUNC - Defaults to actual backup file drive and folder, UNC path to full backup file
@FromFileDiffUNC - Defaults to actual drive and folder, UNC path to differential backup file
@FromFileLogUNC - Defaults to actual drive and folder, UNC path to log backup files
@StopAt - Defaults to current (datetime2), Stop At a specific date time
@StandbyMode - Defaults to 0, Leave database in standby mode
@IncludeSystemDBs - Defaults to 0, include master, model and msdb database restores
@WithRecovery - Defaults to 0, Recover database(s) after restore
@WithCHECKDB - Defaults to 0, Include a CHECKDB after recovery
@WithReplace - Defaults to 0, replace existing database when no tail log backup exists
@LogShippingStartTime - Only used by ps_RestoreGene
@LogShippingLastLSN - Only used by ps_RestoreGene
@BlobCredential - Defaults to NULL, SQL Credential for Azure Blog Storage Account
@RestoreScriptReplaceThis - Defaults to NULL, Restore Script String Find
@RestoreScriptWithThis - Defaults to NULL, Restore Script String Replace
@SuppressWithMove - Default 1, Exclude WITH MOVE statements from the Restore Script
@PivotWithMove - Default 0, If SuppressWithMove = 0, pivot secondary files to new rows in the result set
@RestoreScriptOnly - Default 0, Return just the RESTORE DATABASE commands
@DropDatabaseAfterRestore - Default 0, Drops the database after the restore, use with check db
@SetSingleUser - Default 0, Adds ALTER DATABASE @database SET SINGLE_USER WITH ROLLBACK IMMEDIATE
@ExcludeDiffAndLogBackups - Default 0, 0=full+diff+ log, 1=full only, 2-full+diff, 3=full+log, 4=diff+log
@IncludeDeviceType7 - Defaults to 1, Include device type 7 backups in restore script
@IncludeDeviceType102 - Defaults to 1, Include device type 102 backups in restore script
@IncludeDeviceType2 - Defaults to 1, Include device type 2 backups in restore script
@IncludeDeviceType9 - Defaults to 1, Include device type 9 backups in restore script
----------------------------------------------------------------------------------------------
CHANGE LOG:
December 23, 2012 - V1.01 - Release
January 4, 2013 - V1.02 - LSN Checks + Bug fix to STOPAT date format
January 11, 2013 - V1.03 - SQL Server 2005 compatibility (backup compression problem) & @StandbyMode
January 14, 2013 - V1.04 - WITH COPY and up to 10 striped backup files
January 15, 2013 - V1.05 - Format of constructed restore script, enclose database name in [ ]
February 7, 2013 - V1.06 - Modified WHERE device_type IN (7,102,2,9)
May 26, 2013 - V1.07 - Various changes for PoSh Driver Script compatibility
October 14, 2013 - V1.08 - Rename parameters, more meaningful names
October 15, 2013 - V2.00 - Add 2nd CTE for striped backup files and remove repeating calls to CTE
November 5, 2013 - V2.01 - Secondary .ndf files only included if @WithMoveDataFiles NOT NULL
- Extended artificial LSN used for sequencing WITH RECOVERY/CHECKDB to 21 characters
- Include database name in results for logging in PoSh
May 23, 2014 - V3.0 - Improved logging and error handling, add 'LogShippingVariableDeclare' parameter
Default is Yes when called from query analyser, set to No when called by PoSh
May 30, 2014 - V3.1 - Restructured CTE, all predicates moved to the CTE
June 6, 2014 - V3.2 - Add 'LogShippingStartTime' parameter for PowerShell script.
To skip subsequent full and diff backups when restoring Logs in PoSh log shipping mode
- Add 'Target Restore Database Name' parameter
If restoring a specific database, allows overriding restored name
Probably used in conjunction with a @WithMove override of data and log files to a different folder.
- Add LogShippingLastLSN parameter, used to filter results sent to ps_LogShippingLight
June 10, 2014 - V3.3 - Standardize Log Shipping Parameter Names & make &msg variable declare unique
June 27, 2014 - V3.31 - Diff restore bug fix
July 13, 2014 - V3.32 - Feedback from Tony Davis
Rename framework to memorable name 'RestoreGene'
Paramater for WITH REPLACE
August 5, 2014 - V3.4 - Remove TRY CATCH blocks from log restores and checkdb
- Remove CTE's,use #temp tables, suggested by S.Stresser
August 25, 2014 - V3.5 - goestock suggestion - Include device type 7 for AWS EC2 backups
August 29th, 2014 - V3.6 - Richard found and fixed a problem with variable declare where database name has a space or full stop.
July 28, 2015 - V3.7 - Luke Sarro, modifications for case sensitivity & parameter sniffing, a massive amount of work done.
August 28, 2015 - V3.8 - Luke Sarro modifications, recovery fork management. supports forking in recovery plans when constructing a restore script.
Only tested where a single fork exists, IE a restore was done to a historic point in time, with recovery, then subsequent log backups are taken.
When a fork exists the restore script will follow the new path for subsequent differential and log backups taken after the forking point.
If no rbackup forks exist, the procedure works as before.
September 02 2015 - V3.9 - SQLMongo modification to replace hyphens in database name
September 04, 2015 - V4.0 - Domingo modification, check for default backup path = ''
September 29, 2015 - V4.2 - Fixes bug identifying full backups taken after a recovery fork, has introduced code duplication to be removed later but fixes problem
October 3, 2015 - V4.3 - Remove code duplication introduced in V4.2
*
June 3rd, 2016 - V5.0 - Feedback from Brent Ozar related viewing spike:
- V5.0 - Reintroduce SQL Server 2005 compatibility - Bob McLaren - Declaring and assigning variables on same line doesn't work with SQL Server 2005.
- V5.0 - Override data and log file paths if overriding restored database name
June 11th, 2016 - V5.1 - Azure http url restore support, mick@sqlfairy.com
- V5.1 - Restore Script find and replacement variables, mick@sqlfairy.com
June 17th, 2016 - V5.2 - Removed TRY CATCH block in log restore commands
June 26th, 2016 - V5.3 - Include FILESTREAM files in WITH MOVE, jgkiran
July 3rd, 2016 - V5.4 - New parameter for FILESTREAM files, for WITH MOVE parameter, jgkiran
July 14rd, 2016 - V5.5 - Rename rollback files when standby = 1 to allow multiple concurrent ps_RestoreGene invocations, jgkiran
August 8th, 2016 - V5.6 - Accepts a comma separated variable list of databases to restore, suggested Stephen T
- Got permission to reuse the 'SELECT DATABASES' code snippet from ola hallengren - https://ola.hallengren.com/
August 8th, 2016 - V5.7 - Make restore to new database, secondary file names, more meaningful
August 9th, 2016 - V5.8 - Include the WITH MOVE statements on the same results line as the corresponding RESTORE DATABASE statement, flatten the result set to simplify the PoSh driver
August 9th, 2016 - V5.9 - New Parameter, suppress WITH MOVE statements is default, parameter allows override to include them in the script.
August 13th, 2016 - V6.0
- Add parameter option to pivot WITH MOVE secondary files to a new result set line. Not compatible with PoSh driver, useful with large, complex databases.
- Add a parameter option to include just the restore script in the result set. Not compatible with PoSh driver, useful as SQL Agent Job step output.
August 14th, 2016 - V6.1 - Remove parameter @UseDefaultDatabaseBackupPath , no longer used
August 15th, 2016 - V6.2 - @TargetDatabase parameter, file rename tidy
August 17th, 2016 - V6.3 - Edge case fix to fork point LSN
August 19th, 2016 - V6.4 - Publish SQL Server Central
September 4th, 2016 - V6.41 - PoSh Driver Last LSN minor fix. - Published SQL Server Central
September 9th, 2016 - V6.42 - Fix for multiple log files with move, J.Peablles
September 9th, 2016 - V6.43 - Fix for mirrored backups, C.Richardson
September 9th, 2016 - V6.44 - Mod to log file LSN selection, include if matches diff, R.Devries
September 30th, 2016 - V6.45 - Improvements to the output generated when @RestoreScriptOnly = 1, John Lee
September 30th, 2016 - V6.5 - New 'Drop Database After Restore' parameter for use with CHECKDB and automated backup verification, John Lee
November 5th, 2016 - V6.51 - Increase length of @Database parameter to allow for more comma seperated database names
November 5th, 2016 - V6.52 - Edge case log exclusion criteria mod, R.Devries
November 5th, 2016 - V6.53 - New parameter option to include ALTER DATABASE @database SET SINGLE_USER WITH ROLLBACK IMMEDIATE, Hakan Ekman
November 5th, 2016 - V6.54 - New parameter option to exclude differential and log backups (presumaby for development environment refreshes), Mehedi Amin
November 19th, 2016 - V6.55 - Remove semi colon from start of T-SQL restore command strings, serves no purpose
November 19th, 2016 - V6.56 - Remove variable declare, log reference and RAISERROR, serves no purpose and generates an error in edge cases ( my_database and my-database ), Lars Rasmussen
November 19th, 2016 - V6.57 - Parameters options for device types to include, Lars Rasmussen
December 3rd, 2016 - V6.58 - Remove TIME function from STANDBY mode file name construct, for SQL Server 2005 compatibility, Erwin Louwerse
March 26th, 2017 - V6.59 - Significant performance improvements where an instance has many databases, Simone Bizzotto
June 24th, 2017 - V6.70 - Case Sensitive Collation Database Compatibility - M.Pollock
September 9th, 2017 - V6.71 - Device Type filter fix, LostInEurope01
November 11th, 2017 - V6.72 - Mod to log file selection, R.Devries
- Increase @Databases variable length, Bob
Febuary 14th, 2018 - V6.73 - Add escape characters to database name in SET SINGLE_USER, Rod
Febuary 16th, 2018 - V7.0 - Increase support for stripped backup files from 10 to 15, Mehedi
Febuary 17th, 2018 - V8.0 - Extensive changes to the backup fork handling, Mike
March 30th, 2018 - V8.04 - Code refactor
May 25th, 2018 - V8.05 - Missing exclusion of copy only backups reintroduced - Andy
August 25th, 2018 - V8.10 - Enhancements to the @ExcludeDiffAndLogBackups parameters, Bob and Chris F
- Change from parameter data type from bit to int
- 0 (default) = full, diff and logs, 1 = full only, 2 = full and diff, 3 = full and logs
- Existing BIT data type functionality retained and extended by new values 2 and 3.
November 8th, 2018 - V8.11 - Bug fix to last log file - Mike
December 6th, 2018 - V8.12 - Optimisation and environment setup fix - Adam
January 12th, 2019 - V8.13 - Sequence of FROM DISK = 'Stripe' files in RESTORE command
January 3rd, 2021 - V8.14 - Restore from Azure blog storage using FROM DISK instead of FROM URL for backup stripes - Aaron
January 9rd, 2021 - V8.15 - Change parameter @ExcludeDiffAndLogBackups, new option 4 to exclude full restore, return incremental diff & log restores - Mehedi Amin
July 11th, 2021 - V8.16 - Add support for an additional 5 stripes bring the total supported to 20, Steven Dannen
April 5th, 2023 - V8.2 - Masood : Bug fix restoring striped backups
April 11th, 2023 - V8.3 - Aaron : Result Set Columns fix
*
********************************************************************************************/
ALTER PROC [dbo].[sp_RestoreGene]
(
@Database VARCHAR(MAX) = NULL,
@TargetDatabase SYSNAME = NULL,
@WithMoveDataFiles VARCHAR(2000) = NULL,
@WithMoveLogFile VARCHAR(2000) = NULL,
@WithMoveFileStreamFile VARCHAR(2000) = NULL,
@FromFileFullUNC VARCHAR(2000) = NULL,
@FromFileDiffUNC VARCHAR(2000) = NULL,
@FromFileLogUNC VARCHAR(2000) = NULL,
@StopAt DATETIME = NULL,
@StandbyMode BIT = 0,
@IncludeSystemDBs BIT = 0,
@WithRecovery BIT = 0,
@WithCHECKDB BIT = 0,
@WithReplace BIT = 0,
-- Parameters for PowerShell script use only
@LogShippingStartTime DATETIME = NULL,
@LogShippingLastLSN VARCHAR(25) = NULL,
-- Parameters for PowerShell script use only
@BlobCredential VARCHAR(255) = NULL, --Credential used for Azure blob access
@RestoreScriptReplaceThis NVARCHAR(255) = NULL,
@RestoreScriptWithThis NVARCHAR(255) = NULL,
@SuppressWithMove BIT = 1,
@PivotWithMove BIT = 0,
@RestoreScriptOnly BIT = 0,
@DropDatabaseAfterRestore BIT = 0,
@SetSingleUser BIT = 0,
@ExcludeDiffAndLogBackups INT = 0,
-- Options to exclude device types
@IncludeDeviceType7 BIT = 0, --virtual device
@IncludeDeviceType102 BIT = 1,
@IncludeDeviceType2 BIT = 1,
@IncludeDeviceType9 BIT = 1
)
AS
BEGIN
SET NOCOUNT ON;
-- Avoid Parameter Sniffing Problems
DECLARE @Database_ VARCHAR(MAX);
SET @Database_ = @Database;
DECLARE @TargetDatabase_ SYSNAME;
SET @TargetDatabase_ = @TargetDatabase;
-- Special handling for PoSh Driver
DECLARE @WithMoveDataFiles_ VARCHAR(2000);
IF @WithMoveDataFiles <> '' SET @WithMoveDataFiles_= @WithMoveDataFiles;
DECLARE @WithMoveLogFile_ VARCHAR(2000);
IF @WithMoveLogFile <> '' SET @WithMoveLogFile_= @WithMoveLogFile;
DECLARE @WithMoveFileStreamFile_ VARCHAR(2000);
IF @WithMoveFileStreamFile <> '' SET @WithMoveFileStreamFile_= @WithMoveFileStreamFile;
DECLARE @FromFileFullUNC_ VARCHAR(2000);
SET @FromFileFullUNC_ = @FromFileFullUNC;
DECLARE @FromFileDiffUNC_ VARCHAR(2000);
SET @FromFileDiffUNC_ = @FromFileDiffUNC;
DECLARE @FromFileLogUNC_ VARCHAR(2000);
SET @FromFileLogUNC_ = @FromFileLogUNC;
DECLARE @StopAt_ DATETIME;
SET @StopAt_ = @StopAt;
DECLARE @StandbyMode_ BIT;
SET @StandbyMode_ = @StandbyMode;
DECLARE @IncludeSystemDBs_ BIT;
SET @IncludeSystemDBs_ = @IncludeSystemDBs;
DECLARE @WithRecovery_ BIT;
SET @WithRecovery_ = @WithRecovery;
DECLARE @WithCHECKDB_ BIT;
SET @WithCHECKDB_ = @WithCHECKDB;
DECLARE @WithReplace_ BIT;
SET @WithReplace_ = @WithReplace;
DECLARE @LogShippingStartTime_ DATETIME;
SET @LogShippingStartTime_ = @LogShippingStartTime;
DECLARE @LogShippingLastLSN_ VARCHAR(25);
SET @LogShippingLastLSN_ = @LogShippingLastLSN;
DECLARE @BlobCredential_ VARCHAR(255);
SET @BlobCredential_ = @BlobCredential;
DECLARE @RestoreScriptReplaceThis_ NVARCHAR(255);
SET @RestoreScriptReplaceThis_ = @RestoreScriptReplaceThis;
DECLARE @RestoreScriptWithThis_ NVARCHAR(255);
SET @RestoreScriptWithThis_ = @RestoreScriptWithThis;
DECLARE @SuppressWithMove_ BIT;
SET @SuppressWithMove_ = @SuppressWithMove;
DECLARE @PivotWithMove_ BIT
SET @PivotWithMove_ = @PivotWithMove;
DECLARE @RestoreScriptOnly_ BIT;
SET @RestoreScriptOnly_ = @RestoreScriptOnly;
DECLARE @DropDatabaseAfterRestore_ BIT;
SET @DropDatabaseAfterRestore_ = @DropDatabaseAfterRestore;
DECLARE @SetSingleUser_ BIT;
SET @SetSingleUser_ = @SetSingleUser;
DECLARE @ExcludeDiffAndLogBackups_ INT;
SET @ExcludeDiffAndLogBackups_ = @ExcludeDiffAndLogBackups;
-- Defaults Recovery Point Times
IF ISNULL(@StopAt_,'') = ''
SET @StopAt_ = GETDATE();
IF ISNULL(@LogShippingStartTime_,'') = ''
SET @LogShippingStartTime_ = @StopAt_;
-- Allow override of restored database name only if working with a specific database
IF @TargetDatabase_ IS NOT NULL AND @Database_ IS NULL
SET @TargetDatabase_ = NULL;
IF CHARINDEX(',',@Database_, 1) > 0
SET @TargetDatabase_ = NULL;
-- ps_LogShippingLight - Filtered Results
IF ISNULL(@LogShippingLastLSN_,'') = ''
SET @LogShippingLastLSN_ = '-1';
-- Present WITH MOVE if override paths are supplied
IF ISNULL(@WithMoveDataFiles,'') <> '' OR ISNULL(@WithMoveLogFile_,'') <> '' OR ISNULL(@WithMoveFileStreamFile_,'') <> '' OR ISNULL(@TargetDatabase_,'') <> ''
SET @SuppressWithMove_ = 0
-- Backup file locations defaulted to '' by ps_RestoreGene
IF @FromFileFullUNC_ = ''
SET @FromFileFullUNC_ = NULL;
IF @FromFileDiffUNC_ = ''
SET @FromFileDiffUNC_ = NULL;
IF @FromFileLogUNC_ = ''
SET @FromFileLogUNC_ = NULL;
-- Environment Preparation
IF OBJECT_ID('tempdb..#CTE') IS NOT NULL
DROP TABLE #CTE;
IF OBJECT_ID('tempdb..#Stripes') IS NOT NULL
DROP TABLE #Stripes;
IF OBJECT_ID('tempdb..#BackupFork') IS NOT NULL
DROP TABLE #BackupFork;
IF OBJECT_ID('tempdb..#ForkPointsCount') IS NOT NULL
DROP TABLE #ForkPointsCount;
IF OBJECT_ID('tempdb..#RestoreGeneResults') IS NOT NULL
DROP TABLE #RestoreGeneResults;
IF OBJECT_ID('tempdb..#tmpDatabases') IS NOT NULL
DROP TABLE #tmpDatabases;
IF OBJECT_ID('tempdb..#DeviceTypes') IS NOT NULL
DROP TABLE #DeviceTypes;
IF OBJECT_ID('tempdb..#FullBackups') IS NOT NULL
DROP TABLE #FullBackups;
CREATE TABLE #DeviceTypes (device_type tinyint);
IF @IncludeDeviceType7 = 1 INSERT INTO #DeviceTypes (device_type) SELECT 7;
IF @IncludeDeviceType102 = 1 INSERT INTO #DeviceTypes (device_type) SELECT 102;
IF @IncludeDeviceType2 = 1 INSERT INTO #DeviceTypes (device_type) SELECT 2;
IF @IncludeDeviceType9 = 1 INSERT INTO #DeviceTypes (device_type) SELECT 9;
--=======================================================================================================================================
-- 'SELECT DATABASES' code snippet used below with permission from the ola hallengren - https://ola.hallengren.com/
-----------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE #tmpDatabases
(
ID int IDENTITY,
DatabaseName nvarchar(450),
DatabaseNameFS nvarchar(max),
DatabaseType nvarchar(max),
Selected bit,
Completed bit
);
CREATE CLUSTERED INDEX IDX_tmpDatabases ON #tmpDatabases(DatabaseName);
IF CHARINDEX(',',@Database_,1) > 0
BEGIN
DECLARE @SelectedDatabases TABLE
(
DatabaseName nvarchar(max),
DatabaseType nvarchar(max),
Selected bit
);
DECLARE @ErrorMessage nvarchar(max);
DECLARE @Error int;
----------------------------------------------------------------------------------------------------
--// Select databases //--
----------------------------------------------------------------------------------------------------
SET @Database_ = REPLACE(@Database_, ', ', ',');
WITH Databases1 (StartPosition, EndPosition, DatabaseItem) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(',', @Database_, 1), 0), LEN(@Database_) + 1) AS EndPosition,
SUBSTRING(@Database_, 1, ISNULL(NULLIF(CHARINDEX(',', @Database_, 1), 0), LEN(@Database_) + 1) - 1) AS DatabaseItem
WHERE @Database_ IS NOT NULL
UNION ALL
SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(',', @Database_, EndPosition + 1), 0), LEN(@Database_) + 1) AS EndPosition,
SUBSTRING(@Database_, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @Database_, EndPosition + 1), 0), LEN(@Database_) + 1) - EndPosition - 1) AS DatabaseItem
FROM Databases1
WHERE EndPosition < LEN(@Database_) + 1
),
Databases2 (DatabaseItem, Selected) AS
(
SELECT CASE WHEN DatabaseItem LIKE '-%' THEN RIGHT(DatabaseItem,LEN(DatabaseItem) - 1) ELSE DatabaseItem END AS DatabaseItem,
CASE WHEN DatabaseItem LIKE '-%' THEN 0 ELSE 1 END AS Selected
FROM Databases1
),
Databases3 (DatabaseItem, DatabaseType, Selected) AS
(
SELECT CASE WHEN DatabaseItem IN('ALL_DATABASES','SYSTEM_DATABASES','USER_DATABASES') THEN '%' ELSE DatabaseItem END AS DatabaseItem,
CASE WHEN DatabaseItem = 'SYSTEM_DATABASES' THEN 'S' WHEN DatabaseItem = 'USER_DATABASES' THEN 'U' ELSE NULL END AS DatabaseType,
Selected
FROM Databases2
),
Databases4 (DatabaseName, DatabaseType, Selected) AS
(
SELECT CASE WHEN LEFT(DatabaseItem,1) = '[' AND RIGHT(DatabaseItem,1) = ']' THEN PARSENAME(DatabaseItem,1) ELSE DatabaseItem END AS DatabaseItem,
DatabaseType,
Selected
FROM Databases3
)
INSERT INTO @SelectedDatabases (DatabaseName, DatabaseType, Selected)
SELECT DatabaseName,
DatabaseType,
Selected
FROM Databases4
OPTION (MAXRECURSION 0)
INSERT INTO #tmpDatabases (DatabaseName, DatabaseNameFS, DatabaseType, Selected, Completed)
SELECT [name] AS DatabaseName,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([name],'\',''),'/',''),':',''),'*',''),'?',''),'"',''),'<',''),'>',''),'|',''),' ','') AS DatabaseNameFS,
CASE WHEN name IN('master','msdb','model') THEN 'S' ELSE 'U' END AS DatabaseType,
0 AS Selected,
0 AS Completed
FROM sys.databases
WHERE [name] <> 'tempdb'
AND source_database_id IS NULL
ORDER BY [name] ASC
UPDATE tmpDatabases
SET tmpDatabases.Selected = SelectedDatabases.Selected
FROM #tmpDatabases tmpDatabases
INNER JOIN @SelectedDatabases SelectedDatabases
ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]')
AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)
WHERE SelectedDatabases.Selected = 1
UPDATE tmpDatabases
SET tmpDatabases.Selected = SelectedDatabases.Selected
FROM #tmpDatabases tmpDatabases
INNER JOIN @SelectedDatabases SelectedDatabases
ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]')
AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)
WHERE SelectedDatabases.Selected = 0
IF @Database_ IS NULL OR NOT EXISTS(SELECT * FROM @SelectedDatabases) OR EXISTS(SELECT * FROM @SelectedDatabases WHERE DatabaseName IS NULL OR DatabaseName = '')
BEGIN
SET @ErrorMessage = 'The value for the parameter @Databases is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
END
--=======================================================================================================================================
-- Full Backups - Last before stop at for each database
SELECT
a.database_name
,MAX(a.backup_finish_date) AS backup_finish_date
INTO #FullBackups
FROM msdb.dbo.backupset a
JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
WHERE a.[type] = 'D'
AND (a.database_name IN (SELECT DatabaseName FROM #tmpDatabases WHERE Selected = 1) OR @Database_ IS NULL OR a.database_name = @Database_)
AND device_type IN (SELECT device_type FROM #DeviceTypes)
AND a.is_copy_only = 0
AND b.mirror = 0
AND a.backup_start_date <= @LogShippingStartTime_
GROUP BY a.database_name;
CREATE INDEX IDX_FullBackups ON #FullBackups(database_name);
--=======================================================================================================================================
-- Backup forking (suppress differential, log file select criteria)
SELECT bs.database_name, first_recovery_fork_guid, MIN(fork_point_lsn) AS fork_point_lsn
INTO #BackupFork
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily b
ON bs.media_set_id = b.media_set_id
JOIN #FullBackups fb
ON fb.database_name = bs.database_name
WHERE fb.backup_finish_date < bs.backup_start_date
AND bs.backup_finish_date < @LogShippingStartTime_
AND bs.first_recovery_fork_guid <> bs.last_recovery_fork_guid
GROUP BY bs.database_name, first_recovery_fork_guid;
-- Only one fork point between the selected full backup and the stop at time supported
SELECT database_name, COUNT(*) AS ForkPoints
INTO #ForkPointsCount
FROM #BackupFork rh
GROUP BY database_name;
--=======================================================================================================================================
--------------------------------------------------------------
-- CTE1 Full Backup
--------------------------------------------------------------
WITH CTE
(
database_name
,current_compatibility_level
,first_lsn
,last_lsn
,fork_point_lsn
,current_is_read_only
,current_state_desc
,current_recovery_model_desc
,has_backup_checksums
,backup_size
,[type]
,backupmediasetid
,family_sequence_number
,backupfinishdate
,physical_device_name
,position
,first_recovery_fork_guid
,last_recovery_fork_guid
)
AS
(
--------------------------------------------------------------
-- Full backup - Most recent before @LogShippingStartTime_
SELECT
bs.database_name
,d.[compatibility_level] AS current_compatibility_level
,bs.first_lsn
,bs.last_lsn
,bs.fork_point_lsn
,d.[is_read_only] AS current_is_read_only
,d.[state_desc] AS current_state_desc
,d.[recovery_model_desc] current_recovery_model_desc
,bs.has_backup_checksums
,bs.backup_size AS backup_size
,'D' AS [type]
,bs.media_set_id AS backupmediasetid
,mf.family_sequence_number
,bs.backup_finish_date AS backupfinishdate
,mf.physical_device_name
,bs.position
,bs.first_recovery_fork_guid
,bs.last_recovery_fork_guid
FROM msdb.dbo.backupset bs
INNER JOIN sys.databases d
ON bs.database_name = d.name
INNER JOIN #FullBackups fb
ON fb.database_name = bs.database_name
AND fb.backup_finish_date = bs.backup_finish_date
JOIN msdb.dbo.backupmediafamily mf
ON mf.media_set_id = bs.media_set_id
AND mf.family_sequence_number Between bs.first_family_number And bs.last_family_number
WHERE bs.type = 'D'
AND mf.mirror = 0
AND mf.physical_device_name NOT IN ('Nul', 'Nul:')
AND bs.is_copy_only = 0
UNION
--------------------------------------------------------------
-- Differential backup, most current immediately before @StopAt_, suppress if backup fork points exist
SELECT
bs.database_name
,d.[compatibility_level] AS current_compatibility_level
,bs.first_lsn
,bs.last_lsn
,bs.fork_point_lsn
,d.[is_read_only] AS current_is_read_only
,d.[state_desc] AS current_state_desc
,d.[recovery_model_desc] current_recovery_model_desc
,bs.has_backup_checksums
,bs.backup_size AS backup_size
,'I' AS [type]
,bs.media_set_id AS backupmediasetid
,mf.family_sequence_number
,bs.backup_finish_date AS backupfinishdate
,mf.physical_device_name
,bs.position
,bs.first_recovery_fork_guid
,bs.last_recovery_fork_guid
FROM msdb.dbo.backupset bs
INNER JOIN sys.databases d
ON bs.database_name = d.name
INNER JOIN #FullBackups fb
ON fb.database_name = bs.database_name
AND fb.backup_finish_date < bs.backup_finish_date
INNER JOIN -- Last Diff before STOPAT / Suppress if backup forkpoints exist
(
SELECT
a.database_name
,MAX(backup_start_date) backup_start_date
FROM msdb.dbo.backupset a
JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
WHERE a.[type] = 'I'
AND (a.database_name IN (SELECT DatabaseName FROM #tmpDatabases WHERE Selected = 1) OR @Database_ IS NULL OR a.database_name = @Database_)
AND device_type IN (SELECT device_type FROM #DeviceTypes)
AND a.is_copy_only = 0
AND b.mirror = 0
AND a.backup_start_date <= ISNULL(@StopAt_,GETDATE())
GROUP BY a.database_name
) x
ON x.database_name = bs.database_name
AND x.backup_start_date = bs.backup_start_date
INNER JOIN msdb.dbo.backupmediafamily mf
ON mf.media_set_id = bs.media_set_id
AND mf.family_sequence_number Between bs.first_family_number And bs.last_family_number
LEFT OUTER JOIN #BackupFork bf
ON bf.database_name = bs.database_name
WHERE bs.type = 'I'
AND mf.physical_device_name NOT IN ('Nul', 'Nul:')
AND mf.mirror = 0
AND @StopAt_ = @LogShippingStartTime_
AND bf.fork_point_lsn IS NULL
UNION
--------------------------------------------------------------
-- Log file backups - after 1st full / before @StopAt_
SELECT
bs.database_name
,d.[compatibility_level] AS current_compatibility_level
,bs.first_lsn
,bs.last_lsn
,bs.fork_point_lsn
,d.[is_read_only] AS current_is_read_only
,d.[state_desc] AS current_state_desc
,d.[recovery_model_desc] current_recovery_model_desc
,bs.has_backup_checksums
,bs.backup_size AS backup_size
,'L' AS [type]
,bs.media_set_id AS backupmediasetid
,mf.family_sequence_number
,bs.backup_finish_date as backupfinishdate
,mf.physical_device_name
,bs.position
,bs.first_recovery_fork_guid
,bs.last_recovery_fork_guid
FROM msdb.dbo.backupset bs
INNER JOIN sys.databases d
ON bs.database_name = d.name
INNER JOIN msdb.dbo.backupmediafamily mf
ON mf.media_set_id = bs.media_set_id
AND mf.family_sequence_number Between bs.first_family_number And bs.last_family_number
INNER JOIN #FullBackups fb
ON bs.database_name = fb.database_name
LEFT OUTER JOIN -- Select the first log file after STOPAT
(
SELECT DISTINCT x.database_name, CASE WHEN y.last_Log_After_StopAt IS NULL THEN CONVERT(datetime, '31 Dec 2050') ELSE y.last_Log_After_StopAt END AS last_Log_After_StopAt
FROM msdb.dbo.backupset x
LEFT JOIN
(
SELECT
database_name
,MIN(backup_start_date) last_Log_After_StopAt
FROM msdb.dbo.backupset a
JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
WHERE a.[type] = 'L'
AND (a.database_name IN (SELECT DatabaseName FROM #tmpDatabases WHERE Selected = 1) OR @Database_ IS NULL OR a.database_name = @Database_)
AND b.mirror = 0
AND device_type IN (SELECT device_type FROM #DeviceTypes)
AND a.backup_start_date > ISNULL(@StopAt_,'1 Jan, 1900')
GROUP BY database_name
) y
ON x.database_name = y.database_name
) x
ON bs.database_name = x.database_name
LEFT OUTER JOIN #BackupFork bf
ON bf.database_name = bs.database_name
AND bf.first_recovery_fork_guid = bs.first_recovery_fork_guid
WHERE bs.backup_start_date <= x.last_Log_After_StopAt -- Include 1st log after stop at
AND bs.backup_start_date >= fb.backup_finish_date -- Full backup finish
--AND bs.backup_finish_date <= @StopAt_
AND (bs.database_name IN (SELECT DatabaseName FROM #tmpDatabases WHERE Selected = 1) OR @Database_ IS NULL OR bs.database_name = @Database_)
AND mf.physical_device_name NOT IN ('Nul', 'Nul:')
AND mf.mirror = 0
AND bs.type = 'L'
AND device_type IN (SELECT device_type FROM #DeviceTypes)
AND (bf.fork_point_lsn > bs.first_lsn OR bf.first_recovery_fork_guid IS NULL OR bs.first_recovery_fork_guid <> bs.last_recovery_fork_guid)
)
SELECT * INTO #CTE FROM CTE;
CREATE INDEX IDX_CTE ON #CTE(database_name);
--------------------------------------------------------------
-- CTE2 Optionally, striped backup file details
--------------------------------------------------------------
WITH Stripes
(
database_name,
backupmediasetid,
family_sequence_number,
last_lsn,
S2_pdn,
S3_pdn,
S4_pdn,
S5_pdn,
S6_pdn,
S7_pdn,
S8_pdn,
S9_pdn,
S10_pdn,
S11_pdn,
S12_pdn,
S13_pdn,
S14_pdn,
S15_pdn,
--************************
S16_pdn,
S17_pdn,
S18_pdn,
S19_pdn,
S20_pdn
--************************
)
AS
(
SELECT
Stripe1.database_name,
Stripe1.backupmediasetid,
Stripe1.family_sequence_number,
Stripe1.last_lsn,
Stripe2.physical_device_name AS S2_pdn,
Stripe3.physical_device_name AS S3_pdn,
Stripe4.physical_device_name AS S4_pdn,
Stripe5.physical_device_name AS S5_pdn,
Stripe6.physical_device_name AS S6_pdn,
Stripe7.physical_device_name AS S7_pdn,
Stripe8.physical_device_name AS S8_pdn,
Stripe9.physical_device_name AS S9_pdn,
Stripe10.physical_device_name AS S10_pdn,
Stripe11.physical_device_name AS S11_pdn,
Stripe12.physical_device_name AS S12_pdn,
Stripe13.physical_device_name AS S13_pdn,
Stripe14.physical_device_name AS S14_pdn,
Stripe15.physical_device_name AS S15_pdn
--************************
,Stripe16.physical_device_name AS S16_pdn
,Stripe17.physical_device_name AS S17_pdn
,Stripe18.physical_device_name AS S18_pdn
,Stripe19.physical_device_name AS S19_pdn
,Stripe20.physical_device_name AS S20_pdn
--************************
FROM #CTE AS Stripe1
LEFT OUTER JOIN #CTE AS Stripe2
ON Stripe2.database_name = Stripe1.database_name
AND Stripe2.backupmediasetid = Stripe1.backupmediasetid
AND Stripe2.family_sequence_number = 2
LEFT OUTER JOIN #CTE AS Stripe3
ON Stripe3.database_name = Stripe1.database_name
AND Stripe3.backupmediasetid = Stripe1.backupmediasetid
AND Stripe3.family_sequence_number = 3
LEFT OUTER JOIN #CTE AS Stripe4
ON Stripe4.database_name = Stripe1.database_name
AND Stripe4.backupmediasetid = Stripe1.backupmediasetid
AND Stripe4.family_sequence_number = 4
LEFT OUTER JOIN #CTE AS Stripe5
ON Stripe5.database_name = Stripe1.database_name
AND Stripe5.backupmediasetid = Stripe1.backupmediasetid
AND Stripe5.family_sequence_number = 5
LEFT OUTER JOIN #CTE AS Stripe6
ON Stripe6.database_name = Stripe1.database_name
AND Stripe6.backupmediasetid = Stripe1.backupmediasetid
AND Stripe6.family_sequence_number = 6
LEFT OUTER JOIN #CTE AS Stripe7
ON Stripe7.database_name = Stripe1.database_name
AND Stripe7.backupmediasetid = Stripe1.backupmediasetid
AND Stripe7.family_sequence_number = 7
LEFT OUTER JOIN #CTE AS Stripe8
ON Stripe8.database_name = Stripe1.database_name
AND Stripe8.backupmediasetid = Stripe1.backupmediasetid
AND Stripe8.family_sequence_number = 8
LEFT OUTER JOIN #CTE AS Stripe9
ON Stripe9.database_name = Stripe1.database_name
AND Stripe9.backupmediasetid = Stripe1.backupmediasetid
AND Stripe9.family_sequence_number = 9
LEFT OUTER JOIN #CTE AS Stripe10
ON Stripe10.database_name = Stripe1.database_name
AND Stripe10.backupmediasetid = Stripe1.backupmediasetid
AND Stripe10.family_sequence_number = 10
LEFT OUTER JOIN #CTE AS Stripe11
ON Stripe11.database_name = Stripe1.database_name
AND Stripe11.backupmediasetid = Stripe1.backupmediasetid
AND Stripe11.family_sequence_number = 11
LEFT OUTER JOIN #CTE AS Stripe12
ON Stripe12.database_name = Stripe1.database_name
AND Stripe12.backupmediasetid = Stripe1.backupmediasetid
AND Stripe12.family_sequence_number = 12
LEFT OUTER JOIN #CTE AS Stripe13
ON Stripe13.database_name = Stripe1.database_name
AND Stripe13.backupmediasetid = Stripe1.backupmediasetid
AND Stripe13.family_sequence_number = 13
LEFT OUTER JOIN #CTE AS Stripe14
ON Stripe14.database_name = Stripe1.database_name
AND Stripe14.backupmediasetid = Stripe1.backupmediasetid
AND Stripe14.family_sequence_number = 14
LEFT OUTER JOIN #CTE AS Stripe15
ON Stripe15.database_name = Stripe1.database_name
AND Stripe15.backupmediasetid = Stripe1.backupmediasetid
AND Stripe15.family_sequence_number = 15
--************************
LEFT OUTER JOIN #CTE AS Stripe16
ON Stripe16.database_name = Stripe1.database_name
AND Stripe16.backupmediasetid = Stripe1.backupmediasetid
AND Stripe16.family_sequence_number = 16
LEFT OUTER JOIN #CTE AS Stripe17
ON Stripe17.database_name = Stripe1.database_name
AND Stripe17.backupmediasetid = Stripe1.backupmediasetid
AND Stripe17.family_sequence_number = 17
LEFT OUTER JOIN #CTE AS Stripe18
ON Stripe18.database_name = Stripe1.database_name
AND Stripe18.backupmediasetid = Stripe1.backupmediasetid
AND Stripe18.family_sequence_number = 18
LEFT OUTER JOIN #CTE AS Stripe19
ON Stripe19.database_name = Stripe1.database_name
AND Stripe19.backupmediasetid = Stripe1.backupmediasetid
AND Stripe19.family_sequence_number = 19
LEFT OUTER JOIN #CTE AS Stripe20
ON Stripe20.database_name = Stripe1.database_name
AND Stripe20.backupmediasetid = Stripe1.backupmediasetid
AND Stripe20.family_sequence_number = 20
-- ************************
)
SELECT * INTO #Stripes FROM Stripes;
CREATE INDEX IDX_Stripes ON #Stripes(database_name);
--------------------------------------------------------------
-- Results, T-SQL RESTORE commands, below are based on CTE's above
--------------------------------------------------------------
SELECT
CASE WHEN @RestoreScriptReplaceThis_ IS NOT NULL AND @RestoreScriptWithThis_ IS NOT NULL THEN REPLACE(a.Command, @RestoreScriptReplaceThis_, @RestoreScriptWithThis_) ELSE a.Command END AS TSQL, --Allow substitution of a path
CONVERT(nvarchar(30), a.backupfinishdate, 126)
AS BackupDate,
a.BackupDevice,
a.first_lsn,
a.last_lsn,
a.fork_point_lsn,
a.first_recovery_fork_guid,
a.last_recovery_fork_guid,
a.database_name ,
ROW_NUMBER() OVER(ORDER BY database_name, Sequence, a.backupfinishdate) AS SortSequence
INTO #RestoreGeneResults
FROM
(
--------------------------------------------------------------
-- Most recent full backup
--------------------------------------------------------------
SELECT
'RESTORE DATABASE [' + CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN d.[name] ELSE @TargetDatabase_ END + ']' + SPACE(1) + CHAR(13) +
CASE WHEN #CTE.physical_device_name LIKE 'http%' THEN ' FROM URL = N' ELSE ' FROM DISK = N' END + '''' + --Support restore from blob storage in Azure
CASE ISNULL(@FromFileFullUNC_,'Actual')
WHEN 'Actual' THEN #CTE.physical_device_name
ELSE @FromFileFullUNC_ + SUBSTRING(#CTE.physical_device_name,LEN(#CTE.physical_device_name) - CHARINDEX('\',REVERSE(#CTE.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(#CTE.physical_device_name),1) + 1)
END + '''' + SPACE(1) + CHAR(13) +
-- Striped backup files
CASE ISNULL(#Stripes.S2_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S2_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S2_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S2_pdn,LEN(#Stripes.S2_pdn) - CHARINDEX('\',REVERSE(#Stripes.S2_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S2_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S3_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S3_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S3_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S3_pdn,LEN(#Stripes.S3_pdn) - CHARINDEX('\',REVERSE(#Stripes.S3_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S3_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S4_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S4_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S4_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S4_pdn,LEN(#Stripes.S4_pdn) - CHARINDEX('\',REVERSE(#Stripes.S4_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S4_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S5_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S5_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S5_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S5_pdn,LEN(#Stripes.S5_pdn) - CHARINDEX('\',REVERSE(#Stripes.S5_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S5_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S6_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S6_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S6_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S6_pdn,LEN(#Stripes.S6_pdn) - CHARINDEX('\',REVERSE(#Stripes.S6_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S6_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S7_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S7_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S7_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S7_pdn,LEN(#Stripes.S7_pdn) - CHARINDEX('\',REVERSE(#Stripes.S7_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S7_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S8_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S8_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S8_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S8_pdn,LEN(#Stripes.S8_pdn) - CHARINDEX('\',REVERSE(#Stripes.S8_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S8_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S9_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S9_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S9_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S9_pdn,LEN(#Stripes.S9_pdn) - CHARINDEX('\',REVERSE(#Stripes.S9_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S9_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S10_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S10_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S10_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S10_pdn,LEN(#Stripes.S10_pdn) - CHARINDEX('\',REVERSE(#Stripes.S10_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S10_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S11_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S11_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S11_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S11_pdn,LEN(#Stripes.S11_pdn) - CHARINDEX('\',REVERSE(#Stripes.S11_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S11_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S12_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S12_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S12_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S12_pdn,LEN(#Stripes.S12_pdn) - CHARINDEX('\',REVERSE(#Stripes.S12_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S12_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S13_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S13_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S13_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S13_pdn,LEN(#Stripes.S13_pdn) - CHARINDEX('\',REVERSE(#Stripes.S13_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S13_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S14_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S14_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S14_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S14_pdn,LEN(#Stripes.S14_pdn) - CHARINDEX('\',REVERSE(#Stripes.S14_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S14_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S15_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S15_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S15_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S15_pdn,LEN(#Stripes.S15_pdn) - CHARINDEX('\',REVERSE(#Stripes.S15_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S15_pdn),1) + 1) END + ''''
END +
--*******
CASE ISNULL(#Stripes.S16_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S16_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S16_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S16_pdn,LEN(#Stripes.S16_pdn) - CHARINDEX('\',REVERSE(#Stripes.S16_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S16_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S17_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S17_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S17_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S17_pdn,LEN(#Stripes.S17_pdn) - CHARINDEX('\',REVERSE(#Stripes.S17_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S17_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S18_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S18_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S18_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S18_pdn,LEN(#Stripes.S18_pdn) - CHARINDEX('\',REVERSE(#Stripes.S18_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S18_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S19_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S19_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S19_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S19_pdn,LEN(#Stripes.S19_pdn) - CHARINDEX('\',REVERSE(#Stripes.S19_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S19_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S20_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S20_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S20_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S20_pdn,LEN(#Stripes.S20_pdn) - CHARINDEX('\',REVERSE(#Stripes.S20_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S20_pdn),1) + 1) END + ''''
END +
--******
' WITH ' +
CASE WHEN #CTE.physical_device_name LIKE 'http%' AND @BlobCredential_ IS NOT NULL THEN ' CREDENTIAL = ''' + @BlobCredential_ + ''', ' ELSE '' END +
CASE ISNULL(@WithReplace_,0) WHEN 1 THEN 'REPLACE, ' ELSE '' END + 'FILE = ' + CAST(#CTE.position AS VARCHAR(5)) + ',' +
CASE #CTE.has_backup_checksums WHEN 1 THEN 'CHECKSUM, ' ELSE ' ' END +
CASE @StandbyMode_ WHEN 0 THEN 'NORECOVERY,' ELSE 'STANDBY =N' + '''' + ISNULL(@FromFileFullUNC_,SUBSTRING(#CTE.physical_device_name,1,LEN(#CTE.physical_device_name) - CHARINDEX('\',REVERSE(#CTE.physical_device_name)))) + '\' + d.name + '_' + REPLACE(REPLACE(SUBSTRING(CONVERT(VARCHAR(24),GETDATE(),127),12,12),':',''),'.','') + '_ROLLBACK_UNDO.bak ' + '''' + ',' END + SPACE(1) +
'STATS=10' + SPACE(1) +
CASE @SuppressWithMove_ WHEN 0 THEN
', MOVE N' + '''' + x.LogicalName + '''' + ' TO ' +
'''' +
CASE ISNULL(@WithMoveDataFiles_,'Actual')
WHEN 'Actual' THEN x.PhysicalName
ELSE @WithMoveDataFiles_ + SUBSTRING(x.PhysicalName,LEN(x.PhysicalName) - CHARINDEX('\',REVERSE(x.PhysicalName),1) + 2,CHARINDEX('\',REVERSE(x.PhysicalName),1) + 1)
END + '''' + SPACE(1) +
', MOVE N' + '''' + y.LogicalName + '''' + ' TO ' +
'''' +
CASE ISNULL(@WithMoveLogFile_ ,'Actual')
WHEN 'Actual' THEN y.PhysicalName
ELSE @WithMoveLogFile_ + SUBSTRING(y.PhysicalName,LEN(y.PhysicalName) - CHARINDEX('\',REVERSE(y.PhysicalName),1) + 2,CHARINDEX('\',REVERSE(y.PhysicalName),1) + 1)
END + ''''
ELSE ' '
END
AS Command,
1 AS Sequence,
d.name AS database_name,
#CTE.physical_device_name AS BackupDevice,
#CTE.backupfinishdate,
#CTE.backup_size,
#CTE.first_lsn,
#CTE.last_lsn,
#CTE.fork_point_lsn,
#CTE.first_recovery_fork_guid,
#CTE.last_recovery_fork_guid
FROM sys.databases d
INNER JOIN
(
SELECT
DB_NAME(mf.database_id) AS name
,CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN mf.Physical_Name ELSE SUBSTRING(mf.Physical_Name,1,CASE CHARINDEX('.',mf.Physical_Name) WHEN 0 THEN LEN(mf.Physical_Name) ELSE CHARINDEX('.',mf.Physical_Name) - 1 END) + '_' + @TargetDatabase_ + '.mdf' END AS PhysicalName
,mf.Name AS LogicalName
FROM sys.master_files mf
WHERE type_desc = 'ROWS'
AND mf.file_id = 1
) x
ON d.name = x.name
INNER JOIN
(
SELECT
DB_NAME(mf.database_id) AS name, type_desc
,CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN mf.Physical_Name ELSE SUBSTRING(mf.Physical_Name,1,CASE CHARINDEX('.',mf.Physical_Name) WHEN 0 THEN LEN(mf.Physical_Name) ELSE CHARINDEX('.',mf.Physical_Name) - 1 END) + '_' + @TargetDatabase_ + '.ldf' END AS PhysicalName
,mf.Name AS LogicalName
FROM sys.master_files mf
-- Fix for multiple log files/with moves
INNER JOIN
(
SELECT database_id, MIN(file_id) AS logfile_id
FROM sys.master_files
WHERE type_desc = 'LOG'
GROUP BY database_id
) logfileid
on logfileid.database_id = mf.database_id
AND logfileid.logfile_id = mf.file_id
WHERE type_desc = 'LOG'
) y
ON d.name = y.name
LEFT OUTER JOIN #CTE
ON #CTE.database_name = d.name
AND #CTE.family_sequence_number = 1
INNER JOIN #Stripes
ON #Stripes.database_name = d.name
AND #Stripes.backupmediasetid = #CTE.backupmediasetid
AND #Stripes.last_lsn = #CTE.last_lsn
LEFT OUTER JOIN #ForkPointsCount fpc
ON fpc.database_name = #CTE.database_name
WHERE #CTE.[type] = 'D'
AND #CTE.family_sequence_number = 1
AND ISNULL(fpc.ForkPoints,0) < 2
AND @ExcludeDiffAndLogBackups_ IN (0,1,2,3)
--------------------------------------------------------------
-- Most recent differential backup
--------------------------------------------------------------
UNION
SELECT
'RESTORE DATABASE [' + CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN d.[name] ELSE @TargetDatabase_ END + ']' + SPACE(1) + CHAR(13) +
CASE WHEN #CTE.physical_device_name LIKE 'http%' THEN ' FROM URL = N' ELSE ' FROM DISK = N' END + '''' +
CASE ISNULL(@FromFileDiffUNC_,'Actual')
WHEN 'Actual' THEN #CTE.physical_device_name
ELSE @FromFileDiffUNC_ + SUBSTRING(#CTE.physical_device_name,LEN(#CTE.physical_device_name) - CHARINDEX('\',REVERSE(#CTE.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(#CTE.physical_device_name),1) + 1)
END + '''' + SPACE(1) + CHAR(13) +
-- Striped backup files
CASE ISNULL(#Stripes.S2_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S2_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S2_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S2_pdn,LEN(#Stripes.S2_pdn) - CHARINDEX('\',REVERSE(#Stripes.S2_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S2_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S3_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S3_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S3_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S3_pdn,LEN(#Stripes.S3_pdn) - CHARINDEX('\',REVERSE(#Stripes.S3_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S3_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S4_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S4_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S4_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S4_pdn,LEN(#Stripes.S4_pdn) - CHARINDEX('\',REVERSE(#Stripes.S4_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S4_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S5_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S5_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S5_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S5_pdn,LEN(#Stripes.S5_pdn) - CHARINDEX('\',REVERSE(#Stripes.S5_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S5_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S6_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S6_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S6_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S6_pdn,LEN(#Stripes.S6_pdn) - CHARINDEX('\',REVERSE(#Stripes.S6_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S6_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S7_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S7_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S7_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S7_pdn,LEN(#Stripes.S7_pdn) - CHARINDEX('\',REVERSE(#Stripes.S7_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S7_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S8_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S8_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S8_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S8_pdn,LEN(#Stripes.S8_pdn) - CHARINDEX('\',REVERSE(#Stripes.S8_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S8_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S9_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S9_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S9_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S9_pdn,LEN(#Stripes.S9_pdn) - CHARINDEX('\',REVERSE(#Stripes.S9_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S9_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S10_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S10_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S10_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S10_pdn,LEN(#Stripes.S10_pdn) - CHARINDEX('\',REVERSE(#Stripes.S10_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S10_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S11_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S11_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S11_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S11_pdn,LEN(#Stripes.S11_pdn) - CHARINDEX('\',REVERSE(#Stripes.S11_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S11_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S12_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S12_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S12_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S12_pdn,LEN(#Stripes.S12_pdn) - CHARINDEX('\',REVERSE(#Stripes.S12_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S12_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S13_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S13_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S13_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S13_pdn,LEN(#Stripes.S13_pdn) - CHARINDEX('\',REVERSE(#Stripes.S13_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S13_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S14_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S14_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S14_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S14_pdn,LEN(#Stripes.S14_pdn) - CHARINDEX('\',REVERSE(#Stripes.S14_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S14_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S15_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S15_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S15_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S15_pdn,LEN(#Stripes.S15_pdn) - CHARINDEX('\',REVERSE(#Stripes.S15_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S15_pdn),1) + 1) END + ''''
END +
--**************************
CASE ISNULL(#Stripes.S16_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S16_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S16_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S16_pdn,LEN(#Stripes.S16_pdn) - CHARINDEX('\',REVERSE(#Stripes.S16_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S16_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S17_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S17_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S17_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S17_pdn,LEN(#Stripes.S17_pdn) - CHARINDEX('\',REVERSE(#Stripes.S17_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S17_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S18_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S18_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S18_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S18_pdn,LEN(#Stripes.S18_pdn) - CHARINDEX('\',REVERSE(#Stripes.S18_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S18_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S19_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S19_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S19_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S19_pdn,LEN(#Stripes.S19_pdn) - CHARINDEX('\',REVERSE(#Stripes.S19_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S19_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S20_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S20_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S20_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S20_pdn,LEN(#Stripes.S20_pdn) - CHARINDEX('\',REVERSE(#Stripes.S20_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S20_pdn),1) + 1) END + ''''
END +
--**************************
' WITH FILE = ' + CAST(#CTE.position AS VARCHAR(5)) + ',' +
CASE #CTE.has_backup_checksums WHEN 1 THEN 'CHECKSUM, ' ELSE ' ' END +
CASE WHEN #CTE.physical_device_name LIKE 'http%' AND @BlobCredential_ IS NOT NULL THEN ' CREDENTIAL = ''' + @BlobCredential_ + ''', ' ELSE '' END +
CASE @StandbyMode_ WHEN 0 THEN 'NORECOVERY,' ELSE 'STANDBY =N' + '''' + ISNULL(@FromFileFullUNC_,SUBSTRING(#CTE.physical_device_name,1,LEN(#CTE.physical_device_name) - CHARINDEX('\',REVERSE(#CTE.physical_device_name)))) + '\' + d.name + '_' + REPLACE(REPLACE(SUBSTRING(CONVERT(VARCHAR(24),GETDATE(),127),12,12),':',''),'.','') + '_ROLLBACK_UNDO.bak ' + '''' + ',' END + SPACE(1) +
'STATS=10' + SPACE(1)
AS Command,
750000 AS Sequence,
d.name AS database_name,
#CTE.physical_device_name AS BackupDevice,
#CTE.backupfinishdate,
#CTE.backup_size,
#CTE.first_lsn,
#CTE.last_lsn,
#CTE.fork_point_lsn,
#CTE.first_recovery_fork_guid,
#CTE.last_recovery_fork_guid
FROM sys.databases d
INNER JOIN #CTE
ON #CTE.database_name = d.name
AND #CTE.family_sequence_number = 1
LEFT OUTER JOIN #Stripes
ON #Stripes.database_name = d.name
AND #Stripes.backupmediasetid = #CTE.backupmediasetid
AND #Stripes.last_lsn = #CTE.last_lsn
LEFT OUTER JOIN #ForkPointsCount fpc
ON fpc.database_name = #CTE.database_name
WHERE #CTE.[type] = 'I'
AND #CTE.family_sequence_number = 1
AND @ExcludeDiffAndLogBackups_ IN (0,2,4)
AND ISNULL(fpc.ForkPoints,0) < 2
--------------------------------------------------------------
UNION -- Log backups taken since most recent full or diff
--------------------------------------------------------------
SELECT
'RESTORE LOG [' + CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN d.[name] ELSE @TargetDatabase_ END + ']' + SPACE(1) + CHAR(13) +
CASE WHEN #CTE.physical_device_name LIKE 'http%' THEN ' FROM URL = N' ELSE ' FROM DISK = N' END + '''' +
CASE ISNULL(@FromFileLogUNC_,'Actual')
WHEN 'Actual' THEN #CTE.physical_device_name
ELSE @FromFileLogUNC_ + SUBSTRING(#CTE.physical_device_name,LEN(#CTE.physical_device_name) - CHARINDEX('\',REVERSE(#CTE.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(#CTE.physical_device_name),1) + 1)
END + '''' + CHAR(13) +
-- Striped backup files
CASE ISNULL(#Stripes.S2_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S2_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S2_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S2_pdn,LEN(#Stripes.S2_pdn) - CHARINDEX('\',REVERSE(#Stripes.S2_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S2_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S3_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S3_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S3_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S3_pdn,LEN(#Stripes.S3_pdn) - CHARINDEX('\',REVERSE(#Stripes.S3_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S3_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S4_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S4_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S4_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S4_pdn,LEN(#Stripes.S4_pdn) - CHARINDEX('\',REVERSE(#Stripes.S4_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S4_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S5_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S5_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S5_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S5_pdn,LEN(#Stripes.S5_pdn) - CHARINDEX('\',REVERSE(#Stripes.S5_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S5_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S6_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S6_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S6_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S6_pdn,LEN(#Stripes.S6_pdn) - CHARINDEX('\',REVERSE(#Stripes.S6_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S6_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S7_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S7_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S7_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S7_pdn,LEN(#Stripes.S7_pdn) - CHARINDEX('\',REVERSE(#Stripes.S7_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S7_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S8_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S8_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S8_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S8_pdn,LEN(#Stripes.S8_pdn) - CHARINDEX('\',REVERSE(#Stripes.S8_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S8_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S9_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S9_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S9_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S9_pdn,LEN(#Stripes.S9_pdn) - CHARINDEX('\',REVERSE(#Stripes.S9_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S9_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S10_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S10_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S10_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S10_pdn,LEN(#Stripes.S10_pdn) - CHARINDEX('\',REVERSE(#Stripes.S10_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S10_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S11_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S11_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S11_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S11_pdn,LEN(#Stripes.S10_pdn) - CHARINDEX('\',REVERSE(#Stripes.S11_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S11_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S12_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S12_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S12_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S12_pdn,LEN(#Stripes.S12_pdn) - CHARINDEX('\',REVERSE(#Stripes.S12_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S12_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S13_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S13_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S13_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S13_pdn,LEN(#Stripes.S13_pdn) - CHARINDEX('\',REVERSE(#Stripes.S13_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S13_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S14_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S14_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S14_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S14_pdn,LEN(#Stripes.S14_pdn) - CHARINDEX('\',REVERSE(#Stripes.S14_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S14_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S15_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S15_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S15_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S15_pdn,LEN(#Stripes.S15_pdn) - CHARINDEX('\',REVERSE(#Stripes.S15_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S15_pdn),1) + 1) END + ''''
END +
--***************
CASE ISNULL(#Stripes.S16_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S16_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S16_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S16_pdn,LEN(#Stripes.S16_pdn) - CHARINDEX('\',REVERSE(#Stripes.S16_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S16_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S17_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S17_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S17_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S17_pdn,LEN(#Stripes.S17_pdn) - CHARINDEX('\',REVERSE(#Stripes.S17_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S17_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S18_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S18_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S18_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S18_pdn,LEN(#Stripes.S18_pdn) - CHARINDEX('\',REVERSE(#Stripes.S18_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S18_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S19_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S19_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S19_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S19_pdn,LEN(#Stripes.S19_pdn) - CHARINDEX('\',REVERSE(#Stripes.S19_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S19_pdn),1) + 1) END + ''''
END +
CASE ISNULL(#Stripes.S20_pdn,'')
WHEN '' THEN ''
ELSE ',' + CASE WHEN #Stripes.S20_pdn LIKE 'http%' THEN ' URL = N' ELSE ' DISK = N' END + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S20_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S20_pdn,LEN(#Stripes.S20_pdn) - CHARINDEX('\',REVERSE(#Stripes.S20_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S20_pdn),1) + 1) END + ''''
END +
--*****************
CASE @StandbyMode_ WHEN 0 THEN ' WITH NORECOVERY,' ELSE ' WITH STANDBY =N' + '''' + ISNULL(@FromFileFullUNC_,SUBSTRING(#CTE.physical_device_name,1,LEN(#CTE.physical_device_name) - CHARINDEX('\',REVERSE(#CTE.physical_device_name)))) + '\' + d.name + '_' + REPLACE(REPLACE(SUBSTRING(CONVERT(VARCHAR(24),GETDATE(),127),12,12),':',''),'.','') + '_ROLLBACK_UNDO.bak ' + '''' + ',' END + SPACE(1) +
CASE WHEN #CTE.physical_device_name LIKE 'http%' AND @BlobCredential_ IS NOT NULL THEN ' CREDENTIAL = ''' + @BlobCredential_ + ''', ' ELSE '' END +
CASE #CTE.has_backup_checksums WHEN 1 THEN ' CHECKSUM, ' ELSE ' ' END +
+ 'FILE = ' + CAST(#CTE.position AS VARCHAR(5)) +
' ,STOPAT = ' + '''' + CONVERT(VARCHAR(21),@StopAt_,120) + ''''
AS Command,
1000000 AS Sequence,
d.name AS database_name,
#CTE.physical_device_name AS BackupDevice,
#CTE.backupfinishdate,
#CTE.backup_size,
#CTE.first_lsn,
#CTE.last_lsn,
#CTE.fork_point_lsn,
#CTE.first_recovery_fork_guid,
#CTE.last_recovery_fork_guid
FROM sys.databases d
INNER JOIN #CTE
ON #CTE.database_name = d.name
AND #CTE.family_sequence_number = 1
LEFT OUTER JOIN #Stripes
ON #Stripes.database_name = d.name
AND #Stripes.backupmediasetid = #CTE.backupmediasetid
AND #Stripes.last_lsn = #CTE.last_lsn
LEFT OUTER JOIN
(
SELECT database_name, MAX(last_lsn) last_lsn
FROM #CTE
WHERE [type] = 'I'
GROUP BY database_name
) after_diff
ON after_diff.database_name = #CTE.database_name
LEFT OUTER JOIN #ForkPointsCount fpc
ON fpc.database_name = #CTE.database_name
WHERE #CTE.[type] = 'L'
AND #CTE.family_sequence_number = 1
AND #CTE.last_lsn >= CASE WHEN @ExcludeDiffAndLogBackups_ IN(0,4) THEN ISNULL(after_diff.last_lsn,'0') ELSE 0 END
AND @ExcludeDiffAndLogBackups_ IN (0,3,4)
AND ISNULL(fpc.ForkPoints,0) < 2
--------------------------------------------------------------
UNION -- SET SINGLE USER WITH ROLLBACK IMMEDIATE
--------------------------------------------------------------
SELECT
'BEGIN TRY ALTER DATABASE [' + CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN d.[name] ELSE @TargetDatabase_ END + ']' + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE END TRY BEGIN CATCH PRINT' + '''' + 'SET SINGLE USER FAILED' + '''' + ' END CATCH' AS Command,
0 AS Sequence,
d.name AS database_name,
'' AS BackupDevice,
GETDATE() AS backupfinishdate,
#CTE.backup_size,
'0' AS first_lsn,
'0' AS last_lsn,
'0' AS fork_point_lsn ,
'00000000-0000-0000-0000-000000000000' AS first_recovery_fork_guid,
'00000000-0000-0000-0000-000000000000' AS last_recovery_fork_guid
FROM sys.databases d
INNER JOIN #CTE
ON #CTE.database_name = d.name
LEFT OUTER JOIN #ForkPointsCount fpc
ON fpc.database_name = #CTE.database_name
WHERE #CTE.[type] = 'D'
AND @SetSingleUser_ = 1
AND ISNULL(fpc.ForkPoints,0) < 2
--------------------------------------------------------------
UNION -- Restore WITH RECOVERY
--------------------------------------------------------------
SELECT
'RESTORE DATABASE [' + CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN d.[name] ELSE @TargetDatabase_ END + ']' + SPACE(1) + 'WITH RECOVERY' AS Command,
1000001 AS Sequence,
d.name AS database_name,
'' AS BackupDevice,
GETDATE() AS backupfinishdate,
#CTE.backup_size,
#CTE.first_lsn,
'999999999999999999997' AS last_lsn,
#CTE.fork_point_lsn ,
'00000000-0000-0000-0000-000000000000' AS first_recovery_fork_guid,
'00000000-0000-0000-0000-000000000000' AS last_recovery_fork_guid
FROM sys.databases d
INNER JOIN #CTE
ON #CTE.database_name = d.name
LEFT OUTER JOIN #ForkPointsCount fpc
ON fpc.database_name = #CTE.database_name
WHERE #CTE.[type] = 'D'
AND @WithRecovery_ = 1
AND ISNULL(fpc.ForkPoints,0) < 2
--------------------------------------------------------------
UNION -- CHECKDB
--------------------------------------------------------------
SELECT
'DBCC CHECKDB(' + '''' + CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN d.[name] ELSE @TargetDatabase_ END + '''' + ') WITH NO_INFOMSGS, ALL_ERRORMSGS' AS Command,
1000002 AS Sequence,
d.name AS database_name,
'' AS BackupDevice,
DATEADD(minute,1,GETDATE()) AS backupfinishdate,
#CTE.backup_size,
#CTE.first_lsn,
'999999999999999999998' AS last_lsn,
#CTE.fork_point_lsn,
'00000000-0000-0000-0000-000000000000' AS first_recovery_fork_guid,
'00000000-0000-0000-0000-000000000000' AS last_recovery_fork_guid
FROM sys.databases d
INNER JOIN #CTE
ON #CTE.database_name = d.name
LEFT OUTER JOIN #ForkPointsCount fpc
ON fpc.database_name = #CTE.database_name
WHERE #CTE.[type] = 'D'
AND @WithCHECKDB_ = 1
AND @WithRecovery_ = 1
AND ISNULL(fpc.ForkPoints,0) < 2
--------------------------------------------------------------
UNION -- Drop database after restore
--------------------------------------------------------------
SELECT
-- Comment out all commands if multiple forking points exist
--CASE WHEN @CommentOut = 1 THEN ' -- Multipe backup fork points detected, command suppressed -- ' ELSE '' END +
'DROP DATABASE [' + CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN d.[name] ELSE @TargetDatabase_ END + ']' + SPACE(1) AS Command,
1000003 AS Sequence,
d.name AS database_name,
'' AS BackupDevice,
GETDATE() AS backupfinishdate,
#CTE.backup_size,
#CTE.first_lsn,
'999999999999999999999' AS last_lsn,
#CTE.fork_point_lsn,
'00000000-0000-0000-0000-000000000000' AS first_recovery_fork_guid,
'00000000-0000-0000-0000-000000000000' AS last_recovery_fork_guid
FROM sys.databases d
INNER JOIN #CTE
ON #CTE.database_name = d.name
LEFT OUTER JOIN #ForkPointsCount fpc
ON fpc.database_name = #CTE.database_name
WHERE #CTE.[type] = 'D'
AND @DropDatabaseAfterRestore_ = 1
AND ISNULL(fpc.ForkPoints,0) < 2
--------------------------------------------------------------
UNION -- RAISERROR suppress restore when multiple forkpoints exist
--------------------------------------------------------------
SELECT
'RAISERROR (' + '''' + 'Multiple restores per performed between the selected full backup and stop at time for database ' + CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN d.[name] ELSE @TargetDatabase_ END + ' - Restore Gene processing aborted.' + '''' + ',0,0);' + + SPACE(1) AS Command,
1 AS Sequence,
d.name AS database_name,
'' AS BackupDevice,
GETDATE() AS backupfinishdate,
#CTE.backup_size,
'000000000000000000000' AS first_lsn,
'999999999999999999999' AS last_lsn,
#CTE.fork_point_lsn,
'00000000-0000-0000-0000-000000000000' AS first_recovery_fork_guid,
'00000000-0000-0000-0000-000000000000' AS last_recovery_fork_guid
FROM sys.databases d
INNER JOIN #CTE
ON #CTE.database_name = d.name
LEFT OUTER JOIN #ForkPointsCount fpc
ON fpc.database_name = #CTE.database_name
WHERE #CTE.[type] = 'D'
AND ISNULL(fpc.ForkPoints,0) > 1
--------------------------------------------------------------
UNION -- WITH MOVE secondary data files, allows for up to 32769/2 file groups
--------------------------------------------------------------
SELECT
-- Comment out all commands if multiple forking points exist
--CASE WHEN @CommentOut = 1 THEN ' -- Multipe backup fork points detected, command suppressed -- ' ELSE '' END +
' ,MOVE N' + '''' + b.name + '''' + ' TO N' + '''' +
CASE b.type_desc
WHEN 'ROWS' THEN
CASE ISNULL(@WithMoveDataFiles_,'Actual')
WHEN 'Actual' THEN CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN b.Physical_Name ELSE SUBSTRING(b.Physical_Name,1,CASE CHARINDEX('.',b.Physical_Name) WHEN 0 THEN LEN(b.Physical_Name) ELSE CHARINDEX('.',b.Physical_Name) - 1 END) + '_' + @TargetDatabase_ + '.ndf' END
ELSE CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN @WithMoveDataFiles_ + SUBSTRING(b.Physical_Name,LEN(b.Physical_Name) - CHARINDEX('\',REVERSE(b.Physical_Name),1) + 2,CHARINDEX('\',REVERSE(b.Physical_Name),1) + 1)
ELSE @WithMoveDataFiles_ + SUBSTRING (b.Physical_Name, (LEN(b.Physical_Name)-CHARINDEX('\',REVERSE(b.Physical_Name)) + 2),CHARINDEX('\',REVERSE(b.Physical_Name)) - 1 - CHARINDEX('.',REVERSE(b.Physical_Name))) + '_' + @TargetDatabase_ + '.ndf' END
END
WHEN 'FILESTREAM' THEN
CASE ISNULL(@WithMoveFileStreamFile_,'Actual')
WHEN 'Actual' THEN CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN b.Physical_Name ELSE SUBSTRING(b.Physical_Name,1,CASE CHARINDEX('.',b.Physical_Name) WHEN 0 THEN LEN(b.Physical_Name) ELSE CHARINDEX('.',b.Physical_Name) - 1 END) + '_' + @TargetDatabase_ END
ELSE CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN @WithMoveFileStreamFile_ + SUBSTRING(b.Physical_Name,LEN(b.Physical_Name) - CHARINDEX('\',REVERSE(b.Physical_Name),1) + 2,CHARINDEX('\',REVERSE(b.Physical_Name),1) + 1)
ELSE @WithMoveFileStreamFile_ + SUBSTRING (b.Physical_Name, (LEN(b.Physical_Name)-CHARINDEX('\',REVERSE(b.Physical_Name)) + 2),CHARINDEX('\',REVERSE(b.Physical_Name)) - 1 - CHARINDEX('.',REVERSE(b.Physical_Name))) + '_' + @TargetDatabase_ END
END
WHEN 'LOG' THEN
CASE ISNULL(@WithMoveLogFile_,'Actual')
WHEN 'Actual' THEN CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN b.Physical_Name ELSE SUBSTRING(b.Physical_Name,1,CASE CHARINDEX('.',b.Physical_Name) WHEN 0 THEN LEN(b.Physical_Name) ELSE CHARINDEX('.',b.Physical_Name) - 1 END) + '_' + @TargetDatabase_ + '.ldf' END
ELSE CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN @WithMoveLogFile_ + SUBSTRING(b.Physical_Name,LEN(b.Physical_Name) - CHARINDEX('\',REVERSE(b.Physical_Name),1) + 2,CHARINDEX('\',REVERSE(b.Physical_Name),1) + 1)
ELSE @WithMoveLogFile_ + SUBSTRING (b.Physical_Name, (LEN(b.Physical_Name)-CHARINDEX('\',REVERSE(b.Physical_Name)) + 2),CHARINDEX('\',REVERSE(b.Physical_Name)) - 1 - CHARINDEX('.',REVERSE(b.Physical_Name))) + '_' + @TargetDatabase_ + '.ldf' END
END
END
+ '''',
b.file_id AS Sequence,
DB_NAME(b.database_id) AS database_name,
'SECONDARY FULL' AS BackupDevice,
#CTE.backupfinishdate,
#CTE.backup_size,
#CTE.first_lsn,
#CTE.last_lsn,
#CTE.fork_point_lsn,
#CTE.first_recovery_fork_guid,
#CTE.last_recovery_fork_guid
FROM sys.master_files b
INNER JOIN #CTE
ON #CTE.database_name = DB_NAME(b.database_id)
LEFT OUTER JOIN #ForkPointsCount fpc
ON fpc.database_name = #CTE.database_name
WHERE #CTE.[type] = 'D'
AND b.type_desc IN ('ROWS','FILESTREAM','LOG')
AND b.file_id > 2
AND ISNULL(fpc.ForkPoints,0) < 2
--------------------------------------------------------------
) a
LEFT OUTER JOIN #tmpDatabases b
ON a.database_name = b.DatabaseName
--------------------------------------------------------------
WHERE (@Database_ IS NULL OR b.Selected = 1 OR @Database_ = a.database_name)
AND (@IncludeSystemDBs_ = 1 OR a.database_name NOT IN('master','model','msdb'))
AND a.last_lsn >= @LogShippingLastLSN_;
CREATE INDEX IDX_RestoreGeneResults ON #RestoreGeneResults (database_name,SortSequence,BackupDate);
--------------------------------------------------------------
-- Result Set
IF @SuppressWithMove_ = 1
BEGIN
IF @RestoreScriptOnly_ = 0
BEGIN
SELECT x4.TSQL, x4.BackupDate, x4.BackupDevice, x4.first_lsn, x4.last_lsn, x4.fork_point_lsn, x4.first_recovery_fork_guid, x4.last_recovery_fork_guid, x4.database_name, x4.SortSequence
FROM #RestoreGeneResults x4
WHERE ISNULL(x4.BackupDevice,'') <> 'SECONDARY FULL'
ORDER BY
x4.database_name,
x4.SortSequence,
x4.BackupDate
END
ELSE
BEGIN
SELECT x4.TSQL AS [--TSQL]
FROM #RestoreGeneResults x4
WHERE ISNULL(x4.BackupDevice,'') <> 'SECONDARY FULL'
ORDER BY
x4.database_name,
x4.SortSequence,
x4.BackupDate
END
END
ELSE
BEGIN
IF @PivotWithMove_ = 1
BEGIN
IF @RestoreScriptOnly_ = 0
BEGIN
SELECT
x4.TSQL, x4.BackupDate, x4.BackupDevice, x4.first_lsn, x4.last_lsn, x4.fork_point_lsn, x4.first_recovery_fork_guid, x4.last_recovery_fork_guid, x4.database_name, x4.SortSequence
FROM #RestoreGeneResults x4
ORDER BY
x4.database_name,
x4.SortSequence,
x4.BackupDate
END
ELSE
BEGIN
SELECT
x4.TSQL AS [--TSQL]
FROM #RestoreGeneResults x4
ORDER BY
x4.database_name,
x4.SortSequence,
x4.BackupDate
END
END
ELSE
BEGIN
IF @RestoreScriptOnly_ = 0
BEGIN
WITH WithMoves AS
(
SELECT
last_lsn,
STUFF((SELECT ' ' + TSQL FROM #RestoreGeneResults x3 WHERE x3.last_lsn = x2.last_lsn AND ISNULL(x3.BackupDevice,'') = 'SECONDARY FULL' ORDER BY x3.SortSequence FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'), 1,1,'') AS WithoveCmds
FROM #RestoreGeneResults x2
GROUP BY last_lsn
)
SELECT
CASE @SuppressWithMove_ WHEN 0 THEN CASE ISNULL(x5.WithoveCmds,'') WHEN '' THEN x4.TSQL ELSE x4.TSQL + ' ' + x5.WithoveCmds END
ELSE x4.TSQL
END AS TSQL,
x4.BackupDate, x4.BackupDevice, x4.first_lsn, x4.last_lsn, x4.fork_point_lsn, x4.first_recovery_fork_guid, x4.last_recovery_fork_guid, x4.database_name, x4.SortSequence
FROM #RestoreGeneResults x4
LEFT OUTER JOIN WithMoves x5
ON x4.last_lsn = x5.last_lsn
WHERE ISNULL(x4.BackupDevice,'') <> 'SECONDARY FULL'
ORDER BY
x4.database_name,
x4.SortSequence,
x4.BackupDate
END
ELSE
BEGIN
WITH WithMoves AS
(
SELECT
last_lsn,
STUFF((SELECT ' ' + TSQL FROM #RestoreGeneResults x3 WHERE x3.last_lsn = x2.last_lsn AND ISNULL(x3.BackupDevice,'') = 'SECONDARY FULL' ORDER BY x3.SortSequence FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'), 1,1,'') AS WithoveCmds
FROM #RestoreGeneResults x2
GROUP BY last_lsn
)
SELECT
CASE @SuppressWithMove_ WHEN 0 THEN CASE ISNULL(x5.WithoveCmds,'') WHEN '' THEN x4.TSQL ELSE x4.TSQL + ' ' + x5.WithoveCmds END
ELSE x4.TSQL
END AS [--TSQL]
FROM #RestoreGeneResults x4
LEFT OUTER JOIN WithMoves x5
ON x4.last_lsn = x5.last_lsn
WHERE ISNULL(x4.BackupDevice,'') <> 'SECONDARY FULL'
ORDER BY
x4.database_name,
x4.SortSequence,
x4.BackupDate
END
END
END
END
GO
Restore Gene 
There are bugs in the code in 2 cases that I found. (F=Full, D=Incremental, L=Log), I am running your SP with just one parameter (database name).
If you have this sequence: F, D1, Recover (Fork), L1, L2. If you restore using this sequence, your script shows that we need to restore F, L1, L2. That does not work. The correct sequence should be F, D1, L1, L2.
If you have this sequence F, L1, L2, Recover (Fork), L3, L4. If you restore only part of L2 (using STOPATMARK), your script shows that we need to restore F, L1, L2, L3, L4. That does not work. You cannot restore L2 because you have forked in the middle of L2. The correct sequence should be F, L1, L3, L4 (because L3’s start LSN will be equal to L2’s start LSN)
LikeLike
Thanks Steve, will look into this
LikeLike
No worries. I can mock up some code if you want to look at it. Thanks.
LikeLiked by 1 person
thanks Paul. Is there any easy way to add to 40 stripes in the script? thanks
Hui
LikeLike
Hi Hui, Sorry no it’s hardcoded at 20 maximum. Paul
LikeLike
Hi Paul,
I got a error when I run the stored procedure.
–=======================================================================================================================================
————————————————————–
— CTE1 Full Backup
————————————————————–
WITH CTE
(
database_name
,current_compatibility_level
,first_lsn
,last_lsn
,fork_point_lsn
,current_is_read_only
,current_state_desc
,current_recovery_model_desc
,has_backup_checksums
,backup_size
,[type]
,backupmediasetid
,family_sequence_number
,backupfinishdate
,physical_device_name
Msg 241, Level 16, State 1, Line 478
Conversion failed when converting date and/or time from character string.
Any idea ?
Best regards,
Eric
LikeLike
You most likely are using a hardcoded “wordy date” like “01 March 2023”, but your SQL Server’s dateformat is set to something else.
Check your settings with:
SELECT @@LANGUAGE AS [@@LANGUAGE] –your current language setting
;EXEC sp_helplanguage @Language = @@LANGUAGE –what is its preferred format?
LikeLike
Hey Paul,
I found an issue when trying to restore a database with a Filestream directory. On line 1573, the creating the new Filestream directory is still trying to find and parse out a file extension that doesn’t exist. Normally, this wouldn’t be a problem, but if there is a period in the path to the Filestream directory, it will cause the length passed to the SUBSTRING function to be incorrect.
Below is the change I made to my version of the proc to resolve this issue, though there may be some cases that are not accounted for in my fix:
WHEN ‘FILESTREAM’ THEN
CASE ISNULL(@WithMoveFileStreamFile_,’Actual’)
WHEN ‘Actual’ THEN CASE ISNULL(@TargetDatabase_,”) WHEN ” THEN b.Physical_Name ELSE b.Physical_Name + ‘_’ + @TargetDatabase_ END
ELSE CASE ISNULL(@TargetDatabase_,”) WHEN ” THEN @WithMoveFileStreamFile_ + SUBSTRING(b.Physical_Name,LEN(b.Physical_Name) – CHARINDEX(‘\’,REVERSE(b.Physical_Name),1) + 2,CHARINDEX(‘\’,REVERSE(b.Physical_Name),1) + 1)
ELSE @WithMoveFileStreamFile_ + SUBSTRING(b.Physical_Name,LEN(b.Physical_Name) – CHARINDEX(‘\’,REVERSE(b.Physical_Name),1) + 2,CHARINDEX(‘\’,REVERSE(b.Physical_Name),1) + 1) + ‘_’ + @TargetDatabase_ END
END
Thanks for all you do.
LikeLike
Thanks Brian,
There are a bunch of fixes like this that need applying, will try to get them done soon.
Best regards
Paul
LikeLike
Hey Paul and Steven, I’ve been using the procedure for a while, but I think I’ve found a bug in it that corresponds to how SQL Server handles Filestream directories.
On line 1573, when trying to create a new directory for a copied Filestream, the CASE statements are still treating the directory as a file, trying to parse to the last period (.) to get the extension. However, this fails if the directory does not have a period in it (as it shouldn’t have a file extension), but you installed SQL Server using the default directory on the C:\ drive.
In this case, the Filestream files would be under the folder “C:\Program Files\Microsoft SQL Server\MSSQL.MSSQLSERVER\MSSQL\DATA\”. However, when the proc tries to parse where to create the new Filestream files, it find the period in the MSSQL.MSSQLSERVER and the SUBSTRING function will send a potentially negative number as the length parameter.
Thanks for all your great work, just wanted to make you aware of this as a potential future fix.
LikeLike
Hey Paul,
Just an idea – but how about adding optional parameters for [MAXTRANSFERSIZE] & [BUFFERCOUNT] to optimize the restore. I’ve sometimes gotten up to 50% faster restores by doing this.
LikeLike
That’s a great idea, thanks. There are a few bug fixes outstanding too, I’ll move it to git soon and make a start
LikeLike
I found a small issue in the part where you setup MDF and LDF paths in combination with Move; In the move destination there is an extra dot in the path; default path of SQL Server databases. That is where it goes wrong when using charindex trying to find the location of the . for the filetype.
I’ve changed the procedure a little bit using reverse:
INNER JOIN
(
SELECT
DB_NAME(mf.database_id) AS name
,CASE ISNULL(@TargetDatabase_,”) WHEN ” THEN mf.Physical_Name ELSE SUBSTRING(mf.Physical_Name,1,CASE CHARINDEX(‘.’,mf.Physical_Name) WHEN 0 THEN LEN(mf.Physical_Name) ELSE LEN(mf.Physical_Name) – CHARINDEX(‘.’,REVERSE(mf.physical_name)) END) + ‘_’ + @TargetDatabase_ + ‘.mdf’ END AS PhysicalName
,mf.Name AS LogicalName
FROM sys.master_files mf
WHERE type_desc = ‘ROWS’
AND mf.file_id = 1
) x
ON d.name = x.name
INNER JOIN
(
SELECT
DB_NAME(mf.database_id) AS name, type_desc
,CASE ISNULL(@TargetDatabase_,”) WHEN ” THEN mf.Physical_Name ELSE SUBSTRING(mf.Physical_Name,1,CASE CHARINDEX(‘.’,mf.Physical_Name) WHEN 0 THEN LEN(mf.Physical_Name) ELSE LEN(mf.Physical_Name) – CHARINDEX(‘.’,REVERSE(mf.physical_name)) END) + ‘_’ + @TargetDatabase_ + ‘.ldf’ END AS PhysicalName
,mf.Name AS LogicalName
FROM sys.master_files mf
LikeLike
Thanks Martin
LikeLike
Not sure if this has been pointed out before, but in version 8.16 the column first_lsn is not returned in the result set when @SupressWithMove=0 and @RestoreScriptOnly=0 (see line #1696)
LikeLike
Hello Paul,
Just having a look at the new changes where the number of stripes have been increased from 15 to 20.
Referring to the new Stripes(16 to 20) code in the “Select into #RestoreGeneResults”, it looks like the Striping sequence numbers are messed up:
For S16 -> SUBSTRING(#Stripes.S16_pdn,LEN(#Stripes.S15_pdn)
For S17 -> SUBSTRING(#Stripes.S15_pdn,LEN(#Stripes.S17_pdn)
For S18 -> SUBSTRING(#Stripes.S18_pdn,LEN(#Stripes.S15_pdn)
For S19 -> SUBSTRING(#Stripes.S19_pdn,LEN(#Stripes.S19_pdn)
For S20 -> SUBSTRING(#Stripes.S20_pdn,LEN(#Stripes.S15_pdn)
LikeLike
Thanks Masood, I’ll fix that ASAP.
Regards
LikeLike
— show when and what file is being restored
…
@ExcludeDiffAndLogBackups INT = 0,
@ShowWhatWhen TINYINT = 0, — 20211217 add which file being restored and when —
— Options to exclude device types
…
July 11th, 2021 – V8.16 – Add support for an additional 5 stripes bring the total supported to 20, Steven Dannen
December 17th, 2021 – V8.17 – Add which file being restored and when, RLively —
*
…
AND a.last_lsn >= @LogShippingLastLSN_;
IF @ShowWhatWhen > 0
BEGIN — 20211217 — RLively — begin — add which file being restored and when —
UPDATE x4
set SortSequence = SortSequence * 2
FROM #RestoreGeneResults x4
WHERE SortSequence < (750000 / 2);
INSERT INTO #RestoreGeneResults
([TSQL], SortSequence, BackupDevice, [database_name], BackupDate)
SELECT
'GO'
+ CHAR(10) + 'PRINT ''– '' + CONVERT(VARCHAR(20), GETDATE(), 120);'
+ CHAR(10) + 'RAISERROR (' + '''– restoring: …'''''
+ RIGHT(BackupDevice, @ShowWhatWhen)
+ ''''''',0,1) WITH NOWAIT;',
SortSequence – 1,
BackupDevice,
[database_name],
BackupDate
FROM #RestoreGeneResults x4
WHERE SortSequence < (750000 / 2)
AND LEFT([TSQL], 7) = N'RESTORE'
AND BackupDevice ”;
END — 20211217 — RLively — end —
CREATE INDEX IDX_RestoreGeneResults ON #RestoreGeneResults (database_name,SortSequence,BackupDate);
—
END
IF @ShowWhatWhen > 0
BEGIN — 20211217 — RLively — begin — add which file being restored and when —
PRINT ‘GO’;
PRINT ‘PRINT ”– ” + CONVERT(VARCHAR(20), GETDATE(), 120);’;
PRINT ‘RAISERROR (”– end restores”,0,1) WITH NOWAIT;’;
END — 20211217 — RLively — end —
GO
…
LikeLike
Thanks Ricky, will look to add that over the Christmas break
LikeLike
HI Paul, trying to take advantage of this great sproc. I’m trying to restore back to a point in time and I’m using blob storage. The result set from this sproc gives my a TSQL column but, it doesn’t have the absolute path to the actual backup file.
LikeLike
Hi Rick, are you using the latest version? This sounds like an issue that was fixed a while ago.
LikeLike
Hi Paul, thank you so much for such a great script!! I only have one request, can it have some logging feature? Sometimes we need to migrate 2-300 DBs from one VM to another, it would be really nice to know where the restore fails if it does happen. It would be even nicer if the script can continue on with the rest of DBs but write the failed DB to logs so human operator don’t have to sit there and stare at the monitor. Additional If the script has send mail section for important messages such as start/error/finish, it will be dope. Would that be good to add to your wonderful script? Many thanks.
LikeLike
Hi Michael, the proc used to generate try catch raise error statements a long time ago but everyone hated them, sorry, no plans to reintroduce them.
LikeLike
Hey Paul,
Thank you for your hard work. I am hoping you can help me out with something. On our production server we run two separate backups jobs at the same time (primary & secondary), each backing up to two different backup devices.
I only want to restore from the primary backup device, but the script is returning me the most recent backups of both locations. Is it possible to filter on the backup device, maybe using the server name of one of the backup servers?
Thanks for your help.
LikeLike
Hi Lucas, I will have a look at it and get back to you, many thanks
LikeLike
Hi Paul,
Thanks for the prompt response. I’ve been speaking with a few people and I think our current backup strategy is incorrect. Rather than running two backup jobs from the source, we should be copying from the backup location to secondary location. So from my side, feel free to ignore my previous message 🙂
LikeLike
Hello Paul,
i have another request if it fit into your script. Earlier, i requested to exclude Diff and Logs “November 5th, 2016 – V6.54 – New parameter option to exclude differential and log backups (presumaby for development environment refreshes), Mehedi Amin”
Now my target is different, let me explain my scenario:
In Prod , Full backup weekly once , 6 days Diff backup. Logs every 15 Mins
In DR , Restore daily by gene restore script.
My concern is, if Full backup (+ Diff and Log) restore on Saturday with no recovery then on Sunday (through rest of the weekdays) i want to restore only Diff and Logs.
@ExcludeDiffAndLogBackups INT = 0, is excluding or including with full. I want only Diff and Log script if possible.
/mehedi
LikeLike
Hi Mehedi, there are a couple of fixes outstanding which I am going to look at over the Christmas holiday, will look at this too.
Thanks
Paul
LikeLiked by 1 person
Eagerly waiting to see new updates. Thank you so much.
/mehedi
LikeLike
Nearly there, will be posting the new version of RestoreGene (sp and PoSh) in a couple of days time.
LikeLike
Hi Mehedi,
There’s a new option ‘4’ for parameter @ExcludeDiffAndLogBackups, 0=full+diff+log, 1=full only, 2=full+diff only, 3=full+logs only, 4=diff+logs only
Thanks and regards
Paul
LikeLike
thank you so much, it is working as expected….
LikeLike
Hi,
I found one bug in the script. When you have multiple files in a full or diff backup from BLOB storage the first one creates FROM URL correctly. The subsequent files in the RESTORE string say DISK =, this blows up. It should be “URL” instead of “DISK”.
Thanks for the great script!
LikeLike
Thanks Aaron, I will fix that. Best regards, Paul
LikeLike
Hi Mike,
I’ve found and fixed this bug with FROM URL defaulting to FROM DISK with stripped backups files in azure storage.
Thanks and regards
Paul
LikeLike
Hi Paul,
I am a new user to the script, but it is truly awesome!
I have been reading thru the doc and comments/questions (there are a lot of comments/questions), and maybe I missed it, but I have 3 questions. Sorry if you have answered these before.
1. We take Weekly Fulls, Daily DIffs, and Trans Logs every 30 mins. Is there a parameter or set of parameters which will restore ONLY the Full backup, and not any DIffs or Tran Logs? When we copy Prod to Non-Prod, most people are happy with only the last Full. Especially if it is a Thursday or Friday, we don’t want to have to restore all of the Logs. When there are hundreds of db’s to restore, it can be challenging to edit the output.
2. Is there a way to only return the TSQL and not any other columns?
3. Is there a way to exclude certain instance specific user databases like SSISDB, ReportServer, ReportServerTempDB, DWConfiguration, DWDiagnostics, DWQueue, and Ola’s DBAUtil while still running the script with no @database?
Thanks,
Mike
LikeLike
So Sorry….. I just found:
@RestoreScriptOnly BIT = 0,
@ExcludeDiffAndLogBackups INT = 0,
So I have only 1 question about excluding certain instance specific user databases.
LikeLike
Hi Mike,
The ‘select database’ code section in sp_restoregene uses Ole Hallengren’s code from his maintenance solution, with his permission. To be honest I haven’t tested using ‘-databasename’ to exclude specific databases but it should work I think.
Thanks and best regards
Paul
LikeLike
This is really an awesome script and thanks a lot for providing it for everyone to use it.
I have a Question,I am trying to get the backup file Information and restoring on different server for RestoreTesting or Restoring on Dev/Test Box, all our backups go to the Drive on the Server,so i want to add “\\ServerName\Drive$\” in the front of the Backup File, can you please help where can i make that change.
Thanks,
Megna
LikeLike
Thanks Megna,
There are three parameters, FromFileFullUNC FromFileDiffUNC and FromFileLogUNC, supply a value such as \\servername\Drive$\ and it should use it.
Best regards
Paul
LikeLike
Thanks for the Reply Paul,
I added the Parameter FromFileFullUNC= ‘\\ServerName\Drive$\’ now the Output is missing part of the Path, i got this “\\ServerName\Drive$\Databasebackup.bak” there are more folder under that Drive$. Can you please help in figuring out if i am missing anything.
I am using Ola Hallengren’s Backup Script.
Thanks,
Megna.
LikeLike
Hi Megna, In PowerShell, the $ symbol has to have an escape character in front of it. Is that the issue?
LikeLike
Hi Paul,
Thanks for the wonderful script. I have a requirement where the primary and secondary servers are in different domains and no communication is possible directly. Now I am copying .bak & .trn files through FTP and restore it. The secondary database is in StandBy for report generation. Is there a way I can automate this using sp_restoregene?
Thanks In Advance.
LikeLike
Hi Ratheesh, on the primary server you could add a step to the backup SQL AGENT job, the new job step could execute sp_restoregene with the @RestoreScriptOnly parameter = 1. In the advanced properties of the new job step, set the Output File Location and store the restore script in the same folder as the backup files that are FTP transferred. The restore script would include all backup files though, it would not be incremental. It would be a start though. Regards
LikeLike
I am trying to find an easy way to only enumerate the logs only. Is this in the works.
LikeLike
Hi Shane, There are options to exclude differential and log backups but there’s no option to exclude the full backup. It would be easy to achieve though, use INSERT … EXEC to pipe RestoreGene results to a temporary table the select just the logs from that. Cheers
LikeLike
Hi Paul,
First many thanks for your script it simplify a lot my life ;).
I’m involved on a database server migration and the path for the data file and log file is different in the new server. I use parameters @WithMoveDataFiles and @WithMoveLogFile to select the new path, but the it also includes the database name e.g. F:\disk_1\data\database_name and G:\disk_2\log\database_name.
I modify a bit the stored procedure and I get the correct path when I select an specific database but if I select all of them it is not working.
Any idea where I have to modify the procedure to take into account all databases?.
Many thank in advance
Pablo
LikeLike
Hi Pablo, I’ll try and have a look at this soon. Have you tried using the RestoreScriptReplaceThis and RestoreScriptWithThis parameters instead?
Best wishes
Paul
LikeLike
How do we automate refreshing lower environments weekly using restore generated script?
LikeLike
There’s a power shell script here that calls restore gene on a primary server, the executed the restore script on a standby server – https://paulbrewer.wordpress.com/ps_restoregene/
LikeLike
Is there a setting to just generate script for full backups, exclude diff and log backups?
LikeLike
Set parameter @ExcludeDiffAndLogBackups = 1
LikeLike
Hello Paul,
I have one simple question regarding your script:
my customer is going to get process on regular base (monthly) as follow: prod. data will be backed up and saved as .bak-Files (around 80 DB’s with different sizes, total size about 60TB, some DB’s are > 3TB) local (on Extended disk) and then all files will be transfered / copied to another system (Extended disk).
is It possible to restore backed up databases with different storage disk configurations (about 60 disks) on different system with your script? For example: at first prove existing DB files configurtion as well as free space on the disk and Change this configuration while restoring existing backup? I’m asking you cause some databases have many(!) data files, which can be placed differently on QA system.
Thank you for reply.
LikeLike
Hi Dmitry,
sorry for the slow replay, I have been on a sailing boat at sea for the last few days.
The output (a restore script) from RestoreGene can be piped to a temporary table using the INSERT … EXEC command, the script can then be updated. In the temp table using UPDATE commands. I think it’s possible to construct and tailor a restore script for most scenarios using this technique.
Best wishes
Paul
LikeLike
I have a need to make multiple changes using the @RestoreScriptReplaceThis. I am not seeing that this is possible am I correct?
LikeLike
It only supports a single string replacement. You could define a temporary table, insert into … exec sp_restoregene then update the temp table. I’ve used this approach before and will document it at the weekend.
LikeLike
USE [master]
GO
IF OBJECT_ID(‘tempdb..#RestoreGene’) IS NOT NULL
DROP TABLE #RestoreGene;
CREATE TABLE #RestoreGene
(
TSQL VARCHAR(1000),
BackupDate DATETIME,
BackupDevice VARCHAR(1000),
first_lsn FLOAT,
last_lsn FLOAT,
fork_point_lsn FLOAT,
first_recovery_fork_guid UNIQUEIDENTIFIER,
last_recovery_fork_guid UNIQUEIDENTIFIER,
database_bane VARCHAR(1000),
SortSequene INT
)
INSERT INTO #RestoreGene
EXEC dbo.sp_RestoreGene
@Database = ‘workspace’
,@WithRecovery = 1
,@WithReplace = 1
GO
SELECT *
FROM #RestoreGene
ORDER BY SortSequene
Glenn,
Add whatever REPLACE commands are needed to the final SELECT.
Regards
Paul
LikeLike
Hi Glen, INSERT … EXEC into a temp table then update is the way to go, by default only one replace string is included.
Best wishes
Paul
LikeLike
At line 679 you have select columns defined as:
Stripe9.physical_device_name AS S9_pdn,
Stripe11.physical_device_name AS S10_pdn,
Stripe12.physical_device_name AS S11_pdn,
Stripe13.physical_device_name AS S12_pdn,
Stripe14.physical_device_name AS S13_pdn,
Stripe15.physical_device_name AS S14_pdn,
Stripe10.physical_device_name AS S15_pdn
From my reading, the column you define as S10_pdn reads from the alias Stripe11. The alias Stripe10 is read into column S15_pdn. It would seem the column names are offset with respect to the tables from which they are selected. The restore statements down around line 785 don’t appear to account for this offset.
Can you advise whether restoring out of sequence impacts the veracity of the restore?
Further, with that same select, around line 691 you left outer join #CTE once per stripe where each join specifies the condition Stripe2.family_sequence_number = 2 (for stripe 2) and so on. Yet the initial #CTE AS Stripe1 does not specify any requirement for family_sequence_number = 1. I am not sure whether there is a limit to the number of stripes that may be specified, but in the event someone has backups across more than 15 stripes, wouldn’t the records having family_sequence_number = 1 and family_sequence_number >= 16 all be caught by the first query; ie. #CTE AS Stripe1 ? The procedure documentation in the comments at the start of the procedure note a limit of 10 stripes (which was the case back in 2010) but there is no note about a limit of 15. Perhaps better than a note, the SQL could check whether the actual number of stripes exceeds the number accounted for by the script, and degrade gracefully?
LikeLike
Thanks Chris, I’ll modify the join sequence numbers at the weekend and change the comments to reflect the new 15 stripe limit.
The striped files appear in the constructed RESTORE command as:
FROM DISK = ‘Stripe9’, DISK = ‘Stripe11’, Disk = ‘Stripe10’ etc
the ordinal position of the files doesn’t cause a restore to fail, If a stripe file were omitted or specified twice it would cause a failure, no one has complained.
Well spotted though, it doesn’t look great.
Best regards
Paul
LikeLike
Hi Paul,
Subquery at line 601 cause memory pressure (section — Select the first log file after STOPAT)
Table ‘backupset’. Scan count 32895, logical reads 1019622, […].
Table ‘syspalvalues’. Scan count 0, logical reads 131572, [..]
DISTINCT operator fixed it for me.
SELECT DISTINCT x.database_name, CASE WHEN y.last_Log_After_StopAt IS NULL THEN CONVERT(datetime, ’31 Dec 2050′) ELSE y.last_Log_After_StopAt END AS last_Log_After_StopAt
Something less important:
missing drop # in Environment Preparation section
IF OBJECT_ID(‘tempdb..#ForkPointsCount’) IS NOT NULL
DROP TABLE #ForkPointsCount;
btw great script 🙂
/Adam
LikeLike
Hi Adam,
Thanks very much for the suggestions, both are great and have been incorporated into V 8.12
Best regards
Paul
LikeLike
Perhaps we have done something wrong, but I haven’t figured out what that is yet…We had a rollback scenario last week and used your script, as we had in the past, to generate the commands necessary to do a restore to a point in time. We only have Full and Log backups, no Diffs. When we ran the script for a specific time point it generated the commands, but didn’t include the last log file. We wanted to restore to 20:08. The last log file that was included in the script was 20:00 and not 20:15 (our next log backup). So instead of restoring to 20:08 as we were expecting, the script actually restored our database to 20:00. Looking at the Stored Procedure, I think the issue is on line 627:
AND bs.backup_finish_date <= @StopAt_
This only pulls log backups that occurred before the restore point. In order to do a point-in-time recovery, we would also need to include the first log backup taken after the @StopAt_ time.
I was hoping to have a solution before posting here, but I haven't figured that out yet. I'm surprised others have not come across this yet so maybe it is just something we are doing wrong? Appreciate your work on this procedure.
Thanks!
LikeLike
Hi Mike,
Is it possible there were no transactions after 20:00? The script checks the LSN of backup files too and if they are the same as the previous one they are ignored.
Regards
Paul
LikeLike
There were definitely transactions, which is why we noticed the problem. There was data missing after the restore so we had to do another one. Here’s the LSNs from the backupset query:
backup_start_date first_lsn last_lsn
2018-11-01 19:45:05.000 152226000001019400001 152226000003141700001
2018-11-01 20:00:03.000 152226000003141700001 152226000003834500001
2018-11-01 20:15:06.000 152226000003834500001 152226000004116800001
LikeLike
I may be clutching at straws here but there wasn’t a full backup just after 20:00 was there?
LikeLike
Nope. Those 3 were the only backups taken during the timespan from 19:45 – 20:15 and they are all Log Backups
LikeLike
Fixed in V 8.11, thanks very much for the feedback.
LikeLike
Hey Paul,
thank you again for your continued support. I have been using this tool of yours for a while now and don’t want to miss it.
I do feel the need to report that I am still using v6.72 due to a serious performance issue I am having – any version 8 and after shows an increase in the response time of ~3min as compared to 2s with v6.72 for a call as such:
EXEC sp_RestoreGene @Database = ‘myBasicDB’, @WithRecovery = 1, @WithReplace = 1, @WithCheckDB = 1, @RestoreScriptOnly = 1;
We keep 45 days bkp hist and have 70 DBs with monthly full, daily diff a,d 15min tlogs during the day. I have the basic suggested indexes for msdb and have blindly tried the index suggestions from SSMS which brought the response time down from 3min+ to just under 3min. As I didn’t have alot of time to invest for testing for the current version (v8.10) I have again gone back to v6.72. I don’t consider this to be a back breaker as the setup and backup requirements are very basic at this site and therefore v.6.72 is fine for now. However, I would like to one day get back on the band wagon and therefore ask for your suggestion as how to possibly proceed.
Thanks again,
Greg
LikeLike
Hi Greg, I’m away on holiday at the moment but will try and figure out what the problem is when I get back.
Thanks and regards
Paul
LikeLike
Hey,
its been a while since I made it back here, but as a server migration is in the works I thought of updating this script and was curious if you had had a moment to look into this issue.
Cheers!
Greg
LikeLike
Hi, was this a performance issue?
LikeLike
I have a use case where I would like to restore to the latest full and diff, but not all the logs. Is there an easy way to do this with the parameters the SP currently has? Currently to accomplish this I would have to update/modify the SP by either adding a parameter or commenting out the log restore portions, correct? Could this functionality be built into the SP?
Ex:
@ExcludeDiffAndLogBackups=’1′ will get the last full and @ExcludeDiffAndLogBackups=’0′ will get the last full and all logs.
LikeLike
Hi Bob, RestoreGene won’t meet your requirement at the moment with the existing parameters, I’ll add this in the next release. Thanks for the suggestion and best wishes, Paul
LikeLike
I will be interested to see how you accomplish this as there are many different ways. Once upon a time I want to say I changed parts of @ExcludeDiffAndLogBackups from being a BIT variable to an INT variable.
Then added AND @ExcludeDiffAndLogBackups_ IN (0,2)
and just before — Result Set
IF @ExcludeDiffAndLogBackups_ = 2
BEGIN
DELETE FROM #RestoreGeneResults WHERE TSQL LIKE ‘%RESTORE LOG%’
end
That was simple and quick without having to increase the total lines of code and then allowed for @ExcludeDiffAndLogBackups to use 0,1,2 where 2 would be Full and Diff backups with logs excluded.
What it didn’t account for was anyone expecting to see the SortSequence go 1,2,3 would now be 1,2 and jump to whatever was removed in the delete statement however it was still 100% functional and allowed for the least amount of change overall. At least in my eyes. Granted you are doing more work then if you were to only select what you requested upfront.
Looking back at this again I see that rather than adding @ExcludeDiffAndLogBackups_ IN (0,2) to both lines 1081 and 1219 simply adding it to the one line allows for not having to have a DELETE on the Logs because they are not selected upfront and honors the SortSequence and only does the work that is needed.
I will look forward to your next release and how it is implemented.
LikeLike
Thanks for the idea Chris, your approach sounds good and I’ll try and get it implemented in the next few weeks.
A lot of RestoreGene was rewritten in version 8 specifically to simplify it, changing the bit parameter to an int sounds nice as it could be tuned to honour the existing functionality invoked when 0 and 1 values are received.
Best wishes
Paul
LikeLike
Hi Bob, Chris
Enhancements to the @ExcludeDiffAndLogBackups parameters
Change from parameter data type from bit to int
0 (default) = full, diff and logs, 1 = full only, 2 = full and diff, 3 = full and logs
Existing BIT data type functionality retained and extended by new values 2 and 3.
Best regards
Paul
LikeLike
Number of files is not supported? for example:
BACKUP DATABASE [Northwind] TO
DISK = ‘C:\Northwind_file1.bak’,
DISK = ‘D:\Northwind_file2.bak’,
DISK = ‘E:\Northwind_file3.bak’,
DISK = ‘F:\Northwind_file4.bak’
WITH INIT , NOUNLOAD , NAME = ‘Northwind backup’, NOSKIP , STATS = 10, NOFORMAT
which parameter to use to build the recovery?
LikeLike
Hi,
It detects the number of files used for the backup automatically.
Best wishes
Paul
LikeLike
HI
It seems that copy_only Backups are also included in the outputscript. I don’t have yet found it wher to correct this….
Thank’s
Andy
LikeLike
Hi Andy,
Version 8.00 was a major code refactoring exercise, lots of unnecessary stuff was removed from the procedure. This check to exclude copy only backups was removed by accident, it’s been added back now in version 8.05.
Many thanks for the feedback and best wishes
Paul
LikeLike
Thanks for the script,
I am using version V7.0 of the stored procedure.
I am running the following command, however it returns 0 rows (I corrected the exec command)
— =============================================
EXEC [dbo].[sp_RestoreGene]
@Database = N’SUSDB’,
@TargetDatabase = N’SUSDB_Test1′,
@WithMoveDataFiles = N’H:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA’,
@WithMoveLogFile = N’H:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA’,
@WithMoveFileStreamFile = N’T:\WSUS_Backup’,
@FromFileLogUNC = N’T:\WSUS_Backup\SUSDB’,
@StandbyMode = 1,
@WithRecovery = 0,
@WithCHECKDB = 0,
@STOPAT = ‘2018-03-14 08:42:37′,
@WithReplace = 0
GO
— =============================================
I checked the history table by running the following:
select database_name,name, type, backup_start_date, backup_finish_date from msdb.dbo.backupset
where database_name =’susdb’
order by backup_finish_date
GO
— =============================================
and it returns the following rows:
database_name name type backup_start_date backup_finish_date
SUSDB SUSDB_backup_2018_02_15_040502_4127893 D 2018-02-15 04:05:03.000 2018-02-15 04:05:33.000
SUSDB SUSDB_backup_2018_02_16_040501_6887245 L 2018-02-16 04:05:01.000 2018-02-16 04:05:43.000
SUSDB SUSDB_backup_2018_02_17_040501_4192123 L 2018-02-17 04:05:01.000 2018-02-17 04:05:06.000
SUSDB SUSDB_backup_2018_02_18_040502_7774583 D 2018-02-18 04:05:03.000 2018-02-18 04:05:33.000
SUSDB SUSDB_backup_2018_02_19_013504_4637961 L 2018-02-19 01:35:04.000 2018-02-19 01:35:13.000
SUSDB SUSDB_backup_2018_02_19_040501_4440761 L 2018-02-19 04:05:01.000 2018-02-19 04:05:02.000
SUSDB SUSDB_backup_2018_02_20_040502_4488353 D 2018-02-20 04:05:03.000 2018-02-20 04:05:33.000
SUSDB SUSDB_backup_2018_02_21_040501_4028001 L 2018-02-21 04:05:01.000 2018-02-21 04:05:10.000
SUSDB SUSDB_backup_2018_02_22_040502_4546654 D 2018-02-22 04:05:03.000 2018-02-22 04:05:33.000
SUSDB SUSDB_backup_2018_02_23_040501_1432811 L 2018-02-23 04:05:01.000 2018-02-23 04:05:15.000
SUSDB SUSDB_backup_2018_02_24_040501_9675345 L 2018-02-24 04:05:02.000 2018-02-24 04:05:07.000
SUSDB SUSDB_backup_2018_02_25_040502_7022576 D 2018-02-25 04:05:03.000 2018-02-25 04:05:33.000
SUSDB SUSDB_backup_2018_02_26_040501_9333934 L 2018-02-26 04:05:02.000 2018-02-26 04:05:50.000
SUSDB SUSDB_backup_2018_02_27_040502_8944373 D 2018-02-27 04:05:03.000 2018-02-27 04:05:33.000
SUSDB SUSDB_backup_2018_02_28_040501_5614082 L 2018-02-28 04:05:01.000 2018-02-28 04:05:11.000
SUSDB SUSDB_backup_2018_03_01_040503_6788159 D 2018-03-01 04:05:04.000 2018-03-01 04:05:34.000
SUSDB SUSDB_backup_2018_03_02_040501_3913949 L 2018-03-02 04:05:01.000 2018-03-02 04:05:10.000
SUSDB SUSDB_backup_2018_03_03_040501_6257470 L 2018-03-03 04:05:01.000 2018-03-03 04:05:05.000
SUSDB SUSDB_backup_2018_03_04_040502_6369748 D 2018-03-04 04:05:03.000 2018-03-04 04:05:33.000
SUSDB SUSDB_backup_2018_03_05_040501_7150675 L 2018-03-05 04:05:02.000 2018-03-05 04:05:11.000
SUSDB SUSDB_backup_2018_03_06_040502_2922258 D 2018-03-06 04:05:03.000 2018-03-06 04:05:32.000
SUSDB SUSDB_backup_2018_03_07_040501_2460415 L 2018-03-07 04:05:01.000 2018-03-07 04:05:11.000
SUSDB SUSDB_backup_2018_03_08_040502_1101755 D 2018-03-08 04:05:03.000 2018-03-08 04:05:32.000
SUSDB SUSDB_backup_2018_03_09_040501_0518502 L 2018-03-09 04:05:01.000 2018-03-09 04:05:10.000
SUSDB SUSDB_backup_2018_03_10_040501_5461780 L 2018-03-10 04:05:01.000 2018-03-10 04:05:06.000
SUSDB SUSDB_backup_2018_03_11_040502_6551662 D 2018-03-11 04:05:03.000 2018-03-11 04:05:33.000
SUSDB SUSDB_backup_2018_03_12_040501_5307497 L 2018-03-12 04:05:01.000 2018-03-12 04:05:10.000
SUSDB SUSDB_backup_2018_03_13_040502_1695132 D 2018-03-13 04:05:03.000 2018-03-13 04:05:32.000
SUSDB SUSDB_backup_2018_03_14_040501_2744958 L 2018-03-14 04:05:01.000 2018-03-14 04:05:09.000
SUSDB SUSDB_backup_2018_03_14_094236_7501922 L 2018-03-14 09:42:37.000 2018-03-14 09:42:39.000
SUSDB SUSDB_backup_2018_03_15_040501_2010587 D 2018-03-15 04:05:02.000 2018-03-15 04:05:32.000
SUSDB SUSDB_backup_2018_03_16_040501_8363626 L 2018-03-16 04:05:02.000 2018-03-16 04:05:41.000
SUSDB SUSDB_backup_2018_03_17_040501_8968769 L 2018-03-17 04:05:02.000 2018-03-17 04:05:06.000
— =============================================
Please advise.
Thanks.
LikeLike
Hi Paul: Would it be possible to add an option to insert a “go” after each restore is complete? Many thanks for your scripts. They are truly amazing.
LikeLike
Hi Deino, I’m working on version 8 at the moment and will try but can’t guarantee it. Thanks and regards
LikeLike
Thanks Paul. It is greatly appreciated.
LikeLike
Paul, we are using your script for restores and it has been working great for us. So first off, thanks for putting this together!
Now to my issue… we just came across an issue when multiple Full backups are taken before and after a restore occurs. It’s difficult to explain, but I’ll try:
1. Full backup ran @ 22:18 with last_lsn of 131124000005401900001
2. Restore done from a prior backup (before 22:18)
3. New full backup ran @ 22:31 with last lsn of 131124000002149500001
What we saw when we ran the script to generate the restore log chain (for another restore we had to do after 22:31), there were several log backup files that were skipped. I traced it back to lines 603 & 604 where you are getting the MAX of both the backup_start_date and last_lsn columns. In our case, those max values came from 2 different rows (max date was 22:31 backup and max lsn came from the 22:18 backup). I think this is a fairly rare scenario, but it happened to us.
I was able to get the proper list of backup sets by adding a window function, but if you want the script backward compatible you would probably want to go a different route (probably subquery to get Max start date and then get last lsn for that backup?).
SELECT database_name,backup_start_date,last_lsn
FROM (
SELECT
a.database_name
,a.backup_start_date backup_start_date
,a.last_lsn last_lsn
, ROW_NUMBER() OVER (ORDER BY a.backup_start_date Desc) AS RowNum
FROM msdb.dbo.backupset a
JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
WHERE a.[type] = ‘D’
AND ( @Database_ IS NULL OR a.database_name = @Database_)
AND device_type IN (SELECT device_type FROM #DeviceTypes)
AND a.is_copy_only = 0
AND b.mirror = 0
AND a.backup_start_date <= @LogShippingStartTime_
) x
WHERE RowNum = 1
I'm hoping we never have another night like last night so we never run into this again, but thought we should share this with you in case someone else runs into a similar issue in the future.
LikeLike
Hi Mike,
Fork points are really difficult to manage in Restore Gene as there are so many possible ‘scenarios’. Version 8 has been changed so that if there is more than one fork point LSN between the selected full backup and the @StopAt point Restore Gene raises an information message rather than generating a restore script.
I’ve always felt the strengths of Restore Gene are it’s flexibility and automation possibilities, for really advanced DR recovery the wizard in SSMS is a better bet. If there are multiple fork points then I don’t think Restore Gene is the right tool.
This might not completely fixes the issue you’ve described, I’ll attempt to reproduce it again later this year when things are less busy.
Thanks and regards
Paul
LikeLike
Still using these awesome scripst, thanks. Got another issue. line 1182 does not escape the database name. (line 1202 is correct).
LikeLike
Hi Rod, the escape characters have been added, thanks for the suggestion, nice one.
Cheers
Paul
LikeLike
Hello Paul,
We have been using your RestoreGene since long time and it work fine with us.
A while ago, you added a new parameter for us (November 5th, 2016 – V6.54) these was very helpful.
This time, i have been challenging new things, recently we have increased stripes backup files from 10 to 15 which is not supported by restore gene. Each time, result generate as blank.
At this moment , do you have any plans to add more strip files in your RestoreGene?
Thank you for your help.
LikeLike
Hi Amin,
Adding support in sp_RestoreGene for 5 more striped backups is quite a bit of work, if you nominate me for an Microsoft Data Platform MVP award I’ll do it by the end of March. Thanks and regards
Paul
LikeLike
Hey Paul,
End of the march should be good enough however if you can make it early that would be more helpful.
I will nominate you regardless for the MVP award.
Please send me a link.
Thank you.
/Mehedi
LikeLike
Thanks Mehedi,
It’s quite a bit of work to add those extra stripes, I’m off for a few days early in March and will try and get it done then.
The mvp award link is https://mvp.microsoft.com/en-us/Nomination/nominate-an-mvp . It would be good to get something back for all the work on sp_RestoreGene (a free MSDN subscription comes with the MVP award)
Best wishes
Paul
LikeLike
DONE!!!
LikeLike
Hi Mehedi, support for 15 striped backup files has been added in version 7. Thanks, Paul
LikeLike
Thank you
LikeLike
Hi Paul, Thank you for such a great Automated script. is it possible to generate the restore script for all the DBs in one instance?
LikeLike
Hi Kiran, yes, to generate restore scripts for all databases you just leave the @database parameter blank. Thanks, Paul
LikeLike
Hi Paul,
Just trying out your script for the first time, it is awesome – but I do have a problem I cannot solve. In the ‘Restore Database’ statement the ‘From Disk’ parameter doesn’t include the filename.
If I include the path in the parameter @FromFileFullUNC it looks like this:
RESTORE DATABASE db
FROM DISK = N’T:\SQL Server\Backup\FULL\’ …
If I exclude the @FromFileFullUNC parameter, it puts a GUID in place of the path:
RESTORE DATABASE db
FROM DISK = N'{4DDB5E19-0E98-4452-9258-ECE963F50CDD}5′ …
Any help would be much appreciated.
Cheers.
LikeLike
Hi Adrian, if you query the table msdb.dbo.backupmediafamily , is that GUID stored anywhere ? I’m a bit stumped, it’s the first time this has happened.
Best wishes
Paul
LikeLike
Hi Paul,
Appreciate your reply. I looked in the msdb.dbo.backupmediafamily table I found the GUID and it was associated with a type 7 record. Not sure yet how these got there as we only do backups to disk. Anyway, adding the parameter @IncludeDeviceType7 = 0 worked like a charm.
Thanks again,
Adrian.
LikeLike
Might be a good idea to incorporate some linked server functionality for centralized management compatibility. Adding a parameter to pull information from the msdb and master db’s from linked servers.
LikeLike
Hi Carlos,
Its an interesting idea but, rather than changing sp_RestoreGene code, it would be much easier to install it on all servers then call it on remote servers from a CMS using a four part name.
IE EXEC servername.master.dbo.sp_RestoreGene .
Thanks and regards
Paul
LikeLike
Something else I’ve noticed. When a new database file is created after the latest full backup, the restore script generated will not work when specifying with move. This is because the script tries to move the new file, which did not exist in the backup. I have checked the script generated using SSMS’s restore wizard and it has the same issue. I think to fix this you can add this condition on line 1306: AND b.[differential_base_lsn] IS NOT NULL. That should filter out files that were created after the last full backup. That fixes the full backup restoration, however this doesn’t completely fix the issue since once the script gets to restoring the log backup that contains the new file creation, it will try and create it in whatever the original location was and still error out:
Msg 3634, Level 16, State 1, Line 273
The operating system returned the error ‘3(The system cannot find the path specified.)’ while attempting ‘CreateFileW’ on ‘C:\Somepath\Somefile.ndf’.
I think failing at the log backup restore is better than failing at the full backup restore, though.
LikeLike
After some further poking, it looks like to get this to work you would need to find what log backup the file was created in, and append a MOVE for the file to that restore command. I’ll see if I can figure out how to do that.
LikeLike
Hi,
The problem is the file details aren’t stored in backup history, there’s no simple way of knowing whether files have been added or removed between backups. There’s a note about this issue in the limitations documentation, it’s only an issue if you over-ride the restore file locations (IE, use WITH MOVE).
Thanks for looking at it, cheers
Paul
LikeLike
I think there is an issue with the condition on line 654. I have run into a case where an extra log backup is trying to apply. I think this is due to the following time table:
FULL backup starts at 12:30 AM
LOG backup starts at 1 AM
LOG backup finishes at 1:01 AM
FULL backup finishes at 1:05 AM
LOG backup starts at 2 AM
LOG backup finishes at 2:01 AM
In this case, the 1 AM LOG backup isn’t required, since the last LSN in the FULL backup is newer than the last LSN in that LOG backup. However, since only the start dates are being compared, it is included in the script. I think the fix is to just add in “AND bs.last_lsn > y.last_lsn”, and add “,MAX(last_lsn) last_lsn” after line 600.
LikeLike
Thanks, I’ll check this and update the procedure this weekend. Best wishes, Paul
LikeLiked by 1 person
Thanks, it was a good suggestion, I’ve run a full regression test including your suggested changes and it looks good.
Best wishes
Paul
LikeLike
Hi,
It took quite a quite of bit of time regression testing this change is close to the ‘backup forking’ logic but it worked perfectly, it’s been implemented in V6.72.
Thanks very much
Paul
LikeLike
Thanks, running against all my production servers/databases successfully now.
LikeLike
Hi Paul,
I’m using the proc to generate restores of databases with a large number of data files and the output seems to be getting lopped off.
For example MOVE N’Rdw_FactGenLookbackRevenue_100277′ TO N’G:\MS
Not sure if there’s a limitation in the number of files that the proc concatenates?? I tried to set my query options at larger numbers but still no luck. Please advise. Is there a parm that I can set in the proc maybe?
Bob
LikeLike
Hi Bob,
You need to set the PivotWithMove parameter to 1 and the SupressWithMove parameter to 0 and it should then be Ok. The restore script will be over many rows but it’s still valid. I have this problem at work too.
Cheers
Paul
LikeLike
Hi Paul,
Your quick response is much appreciated. I added the parameters but errored out on the SupressWithMove parameter. I received the “Must Declare Scalar Variable Error”. It took the PivotWithMove parameter. Here’s how I’m executing.
USE Master
Go
exec
dbo.sp_RestoreGene @database=DB List,
@withrecovery=1,
@WithReplace = 1,
@PivotWithMove = 1,
–@SupressWithMove = 0, <<<>>>
@WithMoveDataFiles = ‘x’,
@WithMoveLogFile = ‘y’
Please advise,
Regards.
Bob
LikeLike
Spelling mistake, SuppressWithMove = 0
LikeLike
Hi Paul,
I’m restoring some really large databases (several hundred data files each) and it’s taking very long. I need to mirror these databases after I restore them and I’m pretty sure I’ll have to apply a bunch of transaction log backups since several hours have passed. How do I generate the “second pass” of restores with just transaction log backups now that I have restored the full backups? Not sure why the restores are taking this long either? Could be al I/O I presume
since the central backup location and database servers are geographically in the same location. Not sure if it’s a bandwidth issue. Not sure if these are good numbers or not either RESTORE DATABASE successfully processed 5607205 pages in 1642.830 seconds (26.665 MB/sec).
Regards,
Bob.
LikeLike
Hi Bob,
You could use the log shipping parameters and pass last lsn but it’s quite complicated. The easiest thing is to call restore gene again and generate all the restores up to the current point in time, then just run the log restores that haven’t been run yet. I’d, just copy/execute outstanding log restores.
Regards
Paul
LikeLike
SQL Fairy automirror handles this by performing a second pass after the initial restore is done and calculating which additional logs to restore.
http://sqlfairy.com
LikeLike
Hi Paul,
I’m still lumbering along with my large database restores and I’m at a point where I will be backing up my VLDB’s with Ola Hallengren’s script by providing it the NumberOfFiles parm and 20 as input.
This works fine but when I execute sp_RestoreGene I’m getting a restore script with just the first 10 backup files. Is there a parm I can supply to the proc so it will give me all twenty backup files?
Regards,
Bob
.
LikeLike
No, sorry, RestoreGene supports a maximum of 10 striped backup files (it automatically detects them)
Regards
Paul
LikeLike
Hi Paul,
Is there a limit on the number of databases that we can pass to the databases variable. I don’t seems to be getting all of my databases. It seems to be stopping at 60?
LikeLike
Hi Bob, there’s a 2,000 character limit, I’ll check it carefully for other limitations this weekend though. Thanks and best wishes, Paul
LikeLike
Thanks Bob. The variable containing database names has been increased in length, in V6.72, which should fix the issue.
Best wishes
Paul
LikeLike
Thanks Paul.
LikeLike
Regarding the restore performance stats, the #sqlhelp hashtag on Twitter might get you a better answer than I can give to be honest.
LikeLike
Hi Paul,
thanks for the latest update (6.7) – it helped a serious performance issue we were having.
A minor heads up for you: a quick study/compare between 6.5 and 6.7 showed the device_type lookup is still in old style in 6.7 on line 641. By design?
Thanks again for the script, the updates and continued support. 🙂
Greg
LikeLike
Hi Greg,
Thanks very much for the pointer, good spot! 🙂
Cheers
Paul
LikeLike
If you run sp_whoisactive , does it give you anything in the wait_type column?
LikeLike
I don’t see the wait_type column, but I have NULL in the wait_info column. Thanks for your help.
LikeLike
There are two big queries in sp_restoregene, the first creates a temporary table called #CTE, the second reads this temporary table. Can you tell which of the queries is hanging?
LikeLike
It’s the query that starts with “WITH CTE” and ends with “SELECT * INTO #CTE FROM CTE;”.
LikeLike
Are you supplying a database name parameter or are you passing null to generate restore scripts for all user databases?
LikeLike
I tried both and it still hangs.
LikeLike
Does the restore wizard work? Right click on the database and select ‘tasks’ the ‘restore’ and try to generate a restore script.
Do you have a lot of databases and backup history? If you right click on msdb , select reports then ‘disk usage by table’. You might need to tidy backup history!
LikeLike
Yes the wizard works, but takes like 2 min to appear now
We have 4 databases with full backups every 1 week, diff backups every 1 day and log backups every 15 min, keeping 2 weeks.
So there are a several thousands backups files in total for all databases. msdb.dbo.backupfile has 118,366 records so I guess I should clean that?
LikeLike
It’s got to be worth a try, sp_delete_backuphistory is the way to go.
The processing power of the database server might be a factor, an Italian chap sent me some performance mods for the procedure recently, he was running it on a server with 100’s of databases.
LikeLike
Just want to say that sp_delete_backuphistory solved my issue. The stored procedure runs well now 🙂 Thanks again!
LikeLike
Excellent 😊
LikeLike
Hi, I used this stored procedure for weeks without any problem, but since this morning it’s not working all of a sudden. I execute it and the query is in “runnable” status for hours. Any idea?
LikeLike
Hi Sebastian, someone in Italy sent me a new version of the procedure which is apparently much faster. I haven’t had a chance to test it but will do so this weekend and get it published., it’s much quicker apparently.
The problem sounds like a bad query plan has need generated, if there is a lot of backup history for many database this could be the problem.
Regards
Paul
LikeLike
Ok thanks for the quick response! I will wait for the new version.
Executing the stored procedure was taking seconds yesterday so it’s weird. I used DBCC FREEPROCCACHE to clear the plan cache, but no success.
LikeLike
Hi Sebastien,
V6.59 includes a changes suggested by Simone which should make a significant improvement to the performance of the procedure where a SQL instance has many databases. The change is to filter the databases more carefully at the start to avoid unnecessary work.
Best wishes
Paul
LikeLike
Nice, but I have the same problem with the new version. Executing the stored procedure doesn’t work. How can I debug this?
LikeLike
Fabulous script! Thank you for taking the time to create and maintain this. I have a question about Availability Group databases. I’m using Ola’s backup system with a network path. I have a full weekly, a differential daily and 15 min transaction log backups. For simplicity, I have two servers S1 and S2. S1 is primary, then failover occurs and S2 becomes primary. When I run restoregene on S1, I get the following backup chain: Full, 1 diff, logs (stops when failover occurred). When I run restoregene on S2, I get nothing for databases in the AG, which I suspect is because the backup chain is broken, even though there are subsequent differential and log backups; I can see in the msdb that the backup info is being recorded. Any suggestions on how I can capture the complete backup chain (Full+diff+logs) to restore the database?
LikeLike
Hi Laurisa,
Restore Genie relies on finding a full backup in the local instances msdb history, because that occurred on S1, it returns nothing on S2. There’s no way round this unfortunately, Restore Gene will only work again on S2 once a new full backup is taken on S2.
Regards
Paul
LikeLike
That was what I suspected based on my testing and reading through the code, but I wanted to confirm. Thank you for your response. Happy Holidays.
LikeLike
Thanks, have a Merry Christmas too.
LikeLike
Hi Paul,
I really love this stored procedure!
However in a SQL2005 environment v.6.57 still won’t work ;-(
Msg 243, Level 16, State 1, Procedure sp_RestoreGene, Line 747
Type TIME is not a defined system type.
Msg 243, Level 16, State 1, Procedure sp_RestoreGene, Line 747
Type TIME is not a defined system type.
Msg 243, Level 16, State 1, Procedure sp_RestoreGene, Line 747
Type TIME is not a defined system type.
Msg 8155, Level 16, State 2, Procedure sp_RestoreGene, Line 747
No column was specified for column 1 of ‘a’.
Msg 207, Level 16, State 1, Procedure sp_RestoreGene, Line 747
Invalid column name ‘Command’.
Msg 207, Level 16, State 1, Procedure sp_RestoreGene, Line 747
Invalid column name ‘Command’.
Kind regards,
Erwin.
LikeLike
Hi Erwin,
The TIME function used during the construction of STANDBY file names has been replaced in V5.8, I don’t think that is compatible with SQL Server 2005 hence the problem, could you let me know if it works? I’m having problems installing SQL Server 2005 (compatibility issues) so can’t test it properly. The change doesn’t affect SQL Server 2008 or higher, these regression tests passed after making this change.
Thanks and regards
Paul
LikeLiked by 1 person
Hi Paul,
Sorry for the late response.
The script (v6.58) now works in a SQL2005 environment; I just tested it.
Thanks for the modification!
Kind regards,
Erwin
LikeLike
Brilliant, thanks for confirming Erwin, cheers
LikeLike
Hey Paul,
I’ve come across a couple instances where if I have a database that the FULL and LOG backup have the same BackupDate, it will wrongfully include the LOG backup in the restore chain, even if the last_lsn is lower than the last_lsn of the FULL. Changing line 620 from “AND bs.backup_start_date >= y.backup_start_date” to “AND bs.backup_start_date > y.backup_start_date” seems to fix it, but I think it would be better to use the LSNs as the comparison. I think you can do that by adding “MAX(a.last_lsn) last_lsn” after line 570, then change the comparison at line 620 to “AND bs.last_lsn >= y.last_lsn”
LikeLike
Thanks Ryan, I’ll have a look at it. There is a request for a new ‘Full Backups Only’ parameter and a fix is needed to the @DatabaseName parameter data type ( needs to be longer for big comma separated DB names lists) . Your suggestion makes 3 fixes needed which is my notional new release threshold trigger, will try and do it all this coming weekend.
Regards
Paul
LikeLike
Hi Ryan,
Your suggestion has been added, I couldn’t reproduce the error but the fix doesn’t break any of the regression tests.
Thanks and regards
Paul
LikeLike
Hi Paul,
I used below command and have couple of issues:
exec sp_RestoreGene @Database = ‘Dev1’, @TargetDatabase = ‘Dev2’, @WithReplace = 1, @WithMoveDataFiles = ‘F:\DATA\Dev2.mdf’, @WithMoveLogFile = ‘F:\LOG\Dev2_log.ldf’
After executing the above it returned command as follows:
;RESTORE DATABASE [GP2_Dev2]
FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Dev1.bak’
WITH REPLACE, FILE = 1, NORECOVERY, STATS=10 , MOVE N’Dev1′ TO ‘F:\DATA\Dev2.mdfDev1_Dev2.mdf’ , MOVE N’Dev1_log’ TO ‘F:\LOG\Dev2_log.ldfDev1_log_Dev2.ldf’
First issue is that the original mdf and log files are appended even though I have specified the path in the parameters.
Second issue is after running the RESTORE sql manually (and replacing mdf and ldf paths with correct one), it does restore successfully but leaves the database in “Restoring” state and makes it inaccessible in SSMS. Do I need to specify some other parameter?
Third issue is why the procedure is not running the restore command automatically and is only returning the sql text?
Thanks.
LikeLike
Hi Deepesh,
The @WithMove parameters expects path only, ‘f:\data\’ for example, don’t include the file names.
The database is left in ‘restoring’ state because you did not supply parameter @WithRecovery = 1
Best wishes
Paul
LikeLike
Also… RestoreGene isn’t designed to execute the restore statement(s) that are generated. It generates restore statements to be used by another process (or yourself manually).
If you wish to restore a database based on the supplied parameters then either Paul’s ps_RestoreGene (a powershell script which does wonderful things including restore databases) which can be found here at https://paulbrewer.wordpress.com/ps_restoregene/ or my own SQL Fairy Backup Tools (SQL stored procedures for automagic database restores and much more) at http://www.sqlfairy.com.au/downloads/
Cheers,
Mick.
LikeLike
Not sure if this is a bug or a feature, but I’ve noticed with Line 1132 set to “AND #CTE.last_lsn > ISNULL(after_diff.last_lsn,’0′)” it returns different results for me than SSMS’s restore script generation for cases where the last LSN of the log backups is the same as the dif backup. Changing it to AND #CTE.last_lsn >= ISNULL(after_diff.last_lsn,’0′) seems to replicate SSMS’s behavior. I think this is probably due to nothing changing in any of the log backups, making them unnecessary. However, I by restoring them anyways it gives me more insight into the amount of time behind the current time the last backup was.
LikeLike
Hi Ryan,
It makes sense to include that log restore even though not strictly necessary, I’ve added your mod in with 2 other bug fixes pointed out recently. If mirrored backups existed or more than 1 log file and @WithMove was specified there were problems, both also resolved in V6.44.
Thanks
Paul
LikeLike
Thanks, looks like you typo-ed my name in the change log though 🙂
LikeLike
Sorry, I’ll correct it on the next update. It’s all gone quiet on the bug fixing front so may not be for a while hopefully 😄
LikeLike
Hi Stephen, I sent Ola Hallengren an email and asked if I minded me reusing some code in his maintenance procedures, he very kindly said OK. There is some code in his ‘Backup Database’ procedure that splits a CSV list of database names into a temporary table. That code has been added here in Version 5.6, you can supply a list of database names, separated by a comma. Thanks for the suggestion Stephen and also to Ola for permission to reuse a bit of his code.
LikeLike
This looks like a fantastic script and I hope to use it in the future. One thing I am looking for and could not find in the documentation is an option to specify more than one user databases, say like a csv list and get the restore script only for those databases? Do you have any suggestions for that? Thanks for the awesome script!
LikeLike
Hi Stephen, if you don’t pass a database name parameter to the scrip, it will return restore scripts for all user databases. Alternatively, you would need to call it multiple times and pass database name on each call.
It’s a nice idea, pass a comma separate list of databases, I will add that to a future release.
Thanks and best wishes, Paul
LikeLike
Thanks Paul! Keep up the great work. You are a blessing.
LikeLike
Just wanted to drop you a note and let you know that my migration from SQL 2005 to SQL 2014 was a success, and this script performed spectacularly. It made the process so much easier to script, and allowed me to focus my attention on the other important details. Thanks again to you Paul and to all contributors!
LikeLike
Thanks very much for the suggestions you made for improving the script Bob, and for this comment. I’m really pleased the script was useful and your migration went well!
Best wishes,
Paul
LikeLike
Thanks Paul, this is awesome 🙂
LikeLike
Thanks Paul, much appreciated.This is the most useful tool ever 🙂
LikeLike
Version 5.4 Includes a new parameter to move file stream files to the folder name supplied as a parameter.
Thanks for the suggestion Kiran 🙂
Best wishes, Paul
LikeLike
Hi Kiran,
I can do that no problem, it involves a change to change to the PoSh driver script too. Will get it done over the weekend and let you know when complete.
Thanks
Paul
LikeLike
HI Paul,
Just tried this parameter , works like a charm, thanks for considering it, it would be beneficial if we can split this into a separate variable which will allow to move FileStream Files to a separate drive (helpfull to manage disk space on non-prod servers while restoring the databases)
Regards,
Kiran
LikeLike
Thanks Paul, much appreciated.
LikeLike
Hi Kiran,
If a database contains a FILESTREAM file, it is now included in the WITH MOVE statements, if you pass a value to the parameter @WithMoveDataFiles when calling sp_RestoreGene, the file stream file is now moved to this folder too. It’s a good sensible suggestion, many thanks.
Paul
LikeLike
HI Paul,
Is there a parameter to specify for Filestream files location similar to -WithMoveDataFiles and -WithMoveLogFile ? Please advise.
Cheers,
Kiran
LikeLike
Hi Kiran, there isn’t a parameter for that at the moment but I’ll add one next weekend. A few people have requested this feature, I’ll try and release it by June 27th.
Best wishes
Paul
LikeLike
Version 5.1 released today includes the following changes:
1. If Target database name is supplied (when restoring to a database name different to the back up), a string is appended to the end of the filenames in the format : c:\sql\data\name.mdf. @TargetDatabase_ + _RestoreGene_TargetDBRenamed. This makes the filenames unique so the restore can be on the same instance using the same folders.
2. If a backup was taken to Azure url storage, the restore script constructed includes ‘FROM URL’ rather than ‘FROM FILE’. The credentials used to take the backup can be supplied as a new parameter.
3. Two new parameters – one for a string scan and one for replace that can optionally be applied to the restore script.
4. The PowerShell driver script now defaults ‘With Standby’ to false.
Thanks for the feedback, all of these changes were suggested recently.
Paul
LikeLike
This is an awesome solution and I thank you for sharing it with the community!
With regards to the parameter @TargetDatabase — when I supply a new database name, the script generated does provide that new database name, however, it does not rename the data and log files, so the restore (when attempted on the same server) fails because those file names are in use. There needs to be a case statement in the building of the restore script that if the @TargetDatabase is supplied, the WITH MOVE needs to be @TargetDatabase+’.mdf’, etc.
Also I notice in the case of databases with more than one file (multiple ndf), no script is generated to restore those. Only the mdf and log files are supported with your script.
Thanks!
LikeLike
Hi Margaret, there are fixes for both the issues you mention, and Azure Url backups, in the next release. June 11th (when I get back from holiday and have Internet access again). Thanks and best wishes
LikeLike
Version 5.0 includes the following suggestions:
– Re-enabled SQL Server 2005 compatibility
– If overriding the restored database name, physical file names changed so the new database can be restored to the same instance
The 2nd suggestion was by Mick@SQLFairy.com who has developed a fully featured Azure compatible backup/restore solution based on Ola’s backup procedures and restore gene – http://www.sqlfairy.com.au/teh-sql-fairy-blog/
Thanks
LikeLike
The sproc creation fails to run on my SQL 2005 server with error, “Cannot assign a default value to a local variable.” I have a feeling the parameter sniffing fix you applied is triggering this issue. This fancy stuff is too cool for SQL 2005:
DECLARE @Database_ SYSNAME = @Database,
@TargetDatabase_ SYSNAME = @TargetDatabase, …
LikeLike
Thanks Bob, yes, you can’t declare and assign a variable in one statement in SQL Server 2005 can you. I’ll add that to the list of fixes for the next release too.
I had thought the procedure was done and dusted but since Brent published the link there have been thousands of views and really good suggestions.
Best wishes
Paul
LikeLike
Hi Paul,
Great script, I have created a similar but far simpler script for creating the NORECOVERY restore scripts on DR servers. I think you might like the part to include the datafile size in MB on the same line as the MOVE statement, it makes moving things around easy.
I have put it here – https://liamchew.wordpress.com/2016/05/31/sql-create-restore-script-from-db-backup-files/
The part of the query is this:
select @datafile_str = ‘,MOVE ”’ + s.name + ”’ TO ”’ + @destination_drive + RIGHT(physical_name, len(physical_name) – 1) + ””
+ ‘ –‘ + CONVERT(VARCHAR(20),CONVERT(MONEY,CONVERT(INT, (1024.0 * (size / 128.0)) / 1000.0)),1) + ‘mb’
+ CHAR(13) + CHAR(10)
+ @datafile_str
from sys.master_files s
join sys.databases d
on d.database_id = s.database_id
where d.name = @db_name
order by size
Regards,
Liam
LikeLike
Hi Liam, this looks like a nice idea but I’m hoping the restored files match the size they were when backed up so leaving this one for now. Thanks
LikeLike
Hi Paul,
Thanks very much for this fantastic script.
Have you considered releasing this under any license like Apache 2.0? Unfortunately our legal dept won’t let me touch it otherwise 😦
Dan
LikeLike
Thanks Dan,
The MIT ‘License’ has been applied to Version 6, thanks – https://opensource.org/licenses/MIT
Best wishes
Paul
LikeLike
Hi Paul, just wondered if you’d had time to do any investigations into the issue I described on November 4?
Regards
Tiran
LikeLike
Hi Tiran,
There is a STOPAT parameter, if you supply a value prior to the most recent full backup, the procedure will query backup history and find the most recent full back prior to that date/time and construct a recovery plan accordingly. You can specify any value and, as long as backup history exists for that period, you should be fine.
Best wishes
Paul
LikeLike
Hi Paul, that works great – thanks. Look forward to hearing from you next week if you’re able to replicate the other issue.
LikeLike
Hi Paul
Great script but I’ve just noticed an issue since I started testing V4.3 today. I was using V3.4 originally which worked fine. However, V4.3 is not extracting the path to the full backup file (the log backups are located ok). In V3.4 the restore command generated is “RESTORE DATABASE [MEXDB_TEST] FROM DISK = N’\\\FULL\BBD-SQL01_MEXDB_FULL_20151103_210041.bak’ etc.”. In V4.3 I get “RESTORE DATABASE [MEXDB_TEST] FROM DISK = N'{AA05B80B-98EB-4F33-8585-F5AF714962FB}8′ etc.”. Can you tell me why this is happening? Note: I’m using Ola Hallengren’s SQL backup Scripts to manage my backups. Thanks
LikeLike
Hi Tiran, thanks for the feedback, I’ll try and reproduce the error at the weekend and get back to you. Best wishes, Paul
LikeLike
Hi Paul
Thanks for looking into my previous question.
There’s another scenario I’ve come across which I’m not sure if you’re able to support. At present I have a database for which I am doing a full backup daily and log backups hourly and retaining these for 8 days. My dilemma is that when I run your script I get commands generated to restore only the most recent full backup plus all subsequent logs or log backups up to a point in time – which is fine. However, what if I need to restore to a point prior to the most recent full backup?
Is there a way, using your script, I could select the full backup from say 3 days ago then all the subsequent log backups up to a point in time before the next full backup?
Thanks
LikeLike
Version 4 released which includes the following fixes:
– Lots of unnecessary _msg variables removed
– Support for a single backup fork added
– Support for default backup paths, for UNC backups there is now no need to declare them in ps_RestoreGene
– Support for databases which a – in their name
All feedback and suggestions most welcome, thanks
Paul
LikeLike
New release 3.8 supports a fork in backup history. It’s only been tested where a single fork exists, IE a restore was performed to a historic point in time, with recovery, then subsequent log backups are taken. When a fork exists the restore script will follow the new path for subsequent differential and log backups taken after the forking point. Where no fork, exists the script continues to perform as before.
LikeLike
Great work! I’m testing it out in advance of a migration and ran into an issue with database names that contain a hyphen ‘-‘ character. The generated code uses the database name as part of a variable name, which cannot contain a hyphen. character. I’d suggest replacing any characters in database names that are not allowable in variable names with an underscore ‘_’ character.
LikeLike
I’m implementing your suggestions into release 3.9, thanks.
LikeLike
can I get rid of those set@msg statements?? What is the point in those?
LikeLike
Version 3.9 removes a lot of the @msg variables, they were unnecessary clutter as you suggested. Thanks
LikeLike
Easier for me to just use v1. The @msg and Raiserrors just looks messy. I doubt I’ll get time to modify your code.
LikeLike
Version 3.7 released today with fixes for a collation (case sensitivity) issue and a forking problem with historic backups. The fixes were supplied by Luke Sarro, I wasn’t able to reproduce the problem but a full regression test shows the changes haven’t introduced any problems..
Thanks and regards
Paul
LikeLike
Is there any possibility that this script could include support for databases with more than 10 backup files. One of the system I look after has the full 63 files, I’d admit a simple copy/paste and a bit of editing gets me the script I need but it would be great not having to do so.
LikeLike
I sympathise but can’t help with this, sorry. It would physically quadruple the size of the script for what must be fringe cases. I don’t think many people use even the 10 stripped files it currently supports. Best wishes
LikeLike
How do you handle FULLTEXT index folders?
LikeLiked by 1 person
Not handled at the moment, sorry.
LikeLike
Nice script, really useful. I was running some tests and got some unusual results. We have several data files spread on several drives and when running it I got repeated lines with same information. Is there a possibility to include a list of files and their new paths? Seems no from what I see. I’m creating a similar script, but want to include this option. I already took into consideration Peter concern, creating restore script from existing backup files, comes handy to restore older backups, not last ones, including dif and trn. Working on this possibility to create MOVE statements for different files and paths.
LikeLike
You can move all data files to a new folder during the restore via the WithMoveDataFiles parameter, only to one new folder though. Feel free to chop up my script if you think you can improve it, I won’t sue 🙂
Thanks and regards
LikeLike
Jajaja! No, don’t worry, will not change yours. I landed here seeking for different approaches on how to handle the MOVE statement for different files on several locations. I already handle same case as yours, moving to a different folder, but all files on same one. Thanks!
LikeLike
Paul i love your script!
One question: It’s not possible to create the output not from the database-information but from existing backup-files from disk?
LikeLike
Hi Peter,
I’m pleased you like the script, it took ages to develop so feedback is really appreciated.
It does depend on backup history in the msdb database though, it can’t read details from backup files directly.
Many thanks and best wishes
Paul
LikeLike
Paul, Thanks for the nice script. We have a server set to Case Sensitive collation and I had to correct several casings to be able to run the proc. Where can I contribute this change back?
LikeLike
The project has removed from GitHub, people were publishing versions of it there I wasn’t comfortable being associated with. The master copy of the procedure is, and only ever has been, published here on word press.
LikeLike
Version 3.6 just released which includes a fix, identified and supplied, by a colleague. If database name includes a space or full stop, the msg declare at the start of each database recovery sequence was invalid.
LikeLike
Version 3.5, just released, Includes the Device Type 7 suggestion, thanks
LikeLike
Besides the (102,2) issue (AWS networked drives show up as Device_Type (7), which is not captured in your script; I’m running into another issue; your Database Script takes in the Backup Path for an object; but it could legitimately query the Primary server for this information using the following TSQL:
select physical_device_name, backup_start_date, backup_finish_date
from msdb.dbo.backupset b join msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
where database_name = ‘MyDB’
order by backup_finish_date DESC
The reason this is important is that Ola Hallengren’s SQL backup Scripts (Which are highly recommended by various entities in the SQL Server world) puts each database into a folder in the following structure: BackupDriveLetter:\Backup\ServerName\DatabaseName\TypeOfBackup\FileName.(bak|sqb)
So in my case:
F:\Backup\SQL07\DB1\FULL\SQL07_DB1_FULL_20140817_000001.sqb
As an added feature, can you include the ability to query what the default path for that database’s backups are based on where it stores its database? I ask because the parameter: “FromFileFullUNC” shouldn’t need to be something I provide, since SQL server knows where it’s storing databases already.
LikeLike