SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  SQL_SCALAR_FUNCTION  fun_filename       Script Date: 2026-05-15 19:31:09.477 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[fun_filename]')) DROP FUNCTION [dbo].[fun_filename];
GO


CREATE FUNCTION [dbo].[fun_filename](
  @filename VARCHAR(265))
RETURNS VARCHAR(265)
AS
BEGIN
/*
SQLServer User-Defined Function

PURPOSE
Return the filename of a full filespec.

Everything after the last '\' in the filespec is interpreted as filename.
This function does not check if path or file exists.

PARAMETERS
@filename VARCHAR(265)  Filespec
RETURNS   VARCHAR(265)  Filename

EXAMPLES
1)
SELECT dbo.fun_filename('C:\Program Files\Microsoft SQLserver\install.log')
install.log

2)
SELECT dbo.fun_filename('C:\Program Files\Microsoft SQLserver\install')
install

3)
SELECT dbo.fun_filename('C:\Program Files\Microsoft SQLserver\')
{zero-byte-string}

HISTORY
2005-10-26 - Created function
2014-05-29 - Changed in- and output type from NVARCHAR to VARCHAR
2024-02-09 - Changed history table

TAGS
<program>
  <description>Return the filename of a full filespec</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2005-10-26</created>
  <lastmodified>2024-02-09</lastmodified>
</program>
*/
  DECLARE @fn VARCHAR(265)
  DECLARE @pos SMALLINT

  SET @fn = REVERSE(@filename);

  SET @pos = CHARINDEX('\',@fn);
  IF @pos > 0
    SET @fn = REVERSE(SUBSTRING(@fn,1,@pos-1));
  ELSE
    SET @fn = REVERSE(@fn);

  RETURN @fn;
END


GO

/****** Object:  SQL_SCALAR_FUNCTION  fun_filepath       Script Date: 2026-05-15 19:31:09.477 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[fun_filepath]')) DROP FUNCTION [dbo].[fun_filepath];
GO



CREATE FUNCTION [dbo].[fun_filepath](
  @filename VARCHAR(265))
RETURNS VARCHAR(265)
AS
BEGIN
/*
SQLServer User-Defined Function

PURPOSE
Return the filepath of a full filespec.

Everything before the last '\' in the filespec is interpreted as filepath.
This function does not check if path or file exists.

PARAMETERS
@filename VARCHAR(265)  Filespec
RETURNS   VARCHAR(265)  Filepath

EXAMPLES
1)
SELECT dbo.fun_filepath('C:\Program Files\Microsoft SQLServer\install.log')
C:\Program Files\Microsoft SQLServer

2)
SELECT dbo.fun_filepath('C:\Program Files\Microsoft SQLServer\install')
C:\Program Files\Microsoft SQLServer

3)
SELECT dbo.fun_filepath('C:\Program Files\Microsoft SQLServer\')
C:\Program Files\Microsoft SQLServer

4)
SELECT dbo.fun_filepath('C:\')
C:

5)
SELECT dbo.fun_filepath('C:')
C:

6)
SELECT dbo.fun_filepath('\\Vol1\Backup\PCF.bak')
\\Vol1\Backup

HISTORY
2005-10-27 - Created function
2014-05-29 - Changed in- and output type from NVARCHAR to VARCHAR
           - Output path does not end with '\' anymore
2024-02-09 - Changed history table

TAGS
<program>
  <description>Return the filepath of a full filespec</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2005-10-27</created>
  <lastmodified>2024-02-09</lastmodified>
</program>
*/
  DECLARE @path VARCHAR(256)
  DECLARE @pos SMALLINT

  SET @path = REVERSE(@filename);
  SET @pos = CHARINDEX('\',@path);
  IF @pos > 0
    SET @path = REVERSE(SUBSTRING(@path,@pos+1,LEN(@path)));
  ELSE
    SET @path = REVERSE(@path);

  RETURN @path;
END


GO

/****** Object:  SQL_SCALAR_FUNCTION  fun_FolderExist       Script Date: 2026-05-15 19:31:09.480 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[fun_FolderExist]')) DROP FUNCTION [dbo].[fun_FolderExist];
GO


CREATE FUNCTION [dbo].[fun_FolderExist] (
  @dirname VARCHAR(265))
RETURNS BIT
AS
BEGIN
/*
SQLServer User-Defined Function

PURPOSE
Check if folder exists.

Return value (BIT):
 0 (False) - Folder does not exist
 1 (True)  - Folder exists

EXAMPLES
SELECT dbo.fun_FolderExist('C:\Program Files')
1

SELECT dbo.fun_FolderExist('C:\xxxx')
0

-- IIF is available starting with SQL Server 2012
SELECT IIF(dbo.fun_FolderExist('C:\Program Files')=1,'Yes','No')
Yes

HISTORY
2015-11-24 - Created function
2024-02-09 - Changed history table
2026-01-23 - Changed return value type to BIT
2026-03-30 - Changed examples

TAGS
<program>
  <description>Check if folder exists</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2015-11-24</created>
  <lastmodified>2026-03-30</lastmodified>
</program>
*/
  DECLARE @fso INT
  DECLARE @hr INT
  DECLARE @ofolder INT
  DECLARE @name VARCHAR(265)
  DECLARE @result BIT

  EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @fso OUT;
  EXEC @hr = sp_OAMethod @fso, 'FolderExists', @ofolder OUT, @dirname;
  EXEC @hr = sp_OADestroy @fso;

  SET @result = @ofolder;
  RETURN @result;
END


GO

/****** Object:  SQL_SCALAR_FUNCTION  fun_sqlversion       Script Date: 2026-05-15 19:31:09.480 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[fun_sqlversion]')) DROP FUNCTION [dbo].[fun_sqlversion];
GO


CREATE FUNCTION [dbo].[fun_sqlversion] () 
RETURNS NUMERIC(5,1)
AS
BEGIN
/*
SQLServer User-Defined Function

PURPOSE
Returns the sqlversion.

The returned format is numeric(5,1), like 16.0.

EXAMPLES
SELECT dbo.fun_sqlversion()
16.0

HISTORY
2026-03-29 - Created function

TAGS
<program>
  <description>Returns the sqlversion</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2026-03-29</created>
  <lastmodified>2026-03-29</lastmodified>
</program>
*/
  DECLARE @result NUMERIC(5,1)
  SET @result = CONVERT(NUMERIC(5,1), (@@MICROSOFTVERSION / 0x01000000) + ((@@MICROSOFTVERSION & 0x0FFF000) /0x010000) /100.0);
  RETURN @result;
END


GO

/****** Object:  SQL_STORED_PROCEDURE  prc_kill_sessions       Script Date: 2026-05-15 19:31:09.480 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[prc_kill_sessions]')) DROP PROCEDURE [dbo].[prc_kill_sessions];
GO


CREATE PROCEDURE [dbo].[prc_kill_sessions] (
  @dbname NVARCHAR(128) = '') AS
BEGIN
/*
SQLServer Stored Procedure

PURPOSE
Kill all sessions from given database, except user sa.

HISTORY
2004-03-15 - Created procedure
2005-01-05 - Parameter @keep_loginame added
2014-11-26 - WITH (READUNCOMMITTED) added to prevent errors when sys tables are locked.
2014-12-16 - Changed cursor my_cur1 to LOCAL READ_ONLY, to avoid conflicts with other procedures.
2019-08-19 - Changed message to "Killed n sessions." 
2023-06-14 - Renamed parameter @database_name to @dbname
2026-03-30 - Parameter @keep_loginame is removed
           - Handle all errors with RAISERROR
           - Use DB_ID function in stead of using join to table sys.databases

TAGS
<program>
  <description>Kill all sessions from given database, except user sa</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2004-03-15</created>
  <lastmodified>2026-03-30</lastmodified>
</program>
*/
  DECLARE @sqlcmd NVARCHAR(255)
  DECLARE @spid SMALLINT
  DECLARE @cnt INT
  DECLARE @message VARCHAR(512)

  IF ISNULL(@dbname,'') = ''
  BEGIN
    SET @message = 'ERROR: Parameter @dbname can not be empty'
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  DECLARE my_cur1 CURSOR LOCAL READ_ONLY FOR
    SELECT [spid]
    FROM [master].dbo.sysprocesses WITH (READUNCOMMITTED)
    WHERE [dbid] = DB_ID(@dbname)
    AND [loginame] <> 'sa';

  SET @cnt = 0;
  OPEN my_cur1
  WHILE 1 < 2
  BEGIN
  FETCH NEXT FROM my_cur1 INTO @spid;
    IF @@FETCH_STATUS <> 0 BREAK;

    SET @cnt = @cnt + 1;
    SET @sqlcmd = N'KILL '+ CONVERT(NVARCHAR(10),@spid);
    EXEC (@sqlcmd);
  END
  CLOSE my_cur1;
  DEALLOCATE my_cur1;

  SET @message = '-> Killed ' +CONVERT(VARCHAR,@cnt) + ' sessions.';
  RAISERROR (@message, 0, 1) WITH NOWAIT;
END


GO

/****** Object:  SQL_STORED_PROCEDURE  prc_restore_database       Script Date: 2026-05-15 19:31:09.480 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[prc_restore_database]')) DROP PROCEDURE [dbo].[prc_restore_database];
GO

CREATE PROCEDURE [dbo].[prc_restore_database](
  @dbname NVARCHAR(128) = '',
  @backup_path VARCHAR(265) = '',
  @subdir BIT = 1,
  @new_dbname NVARCHAR(128) = '',
  @option VARCHAR(20)  = 'RESTORE') AS
BEGIN
/*
SQLServer Stored Procedure

PURPOSE
Restore a database from it's latest full-, differential- and log-backupfiles on a given dumpdirectory.
Backups cannot be of the type copy_only, because they are not part of a backupchain.

Please run this script with sufficient restore permissions.
The name of the database must be in the leading part of all backupfile names.
The restore is done "WITH REPLACE", so an existing destination database will be overwritten.
Optionally, a new (destination) database name can be supplied to restore to another database than the original.
The destination database can be OFFLINE or in READ_ONLY (STANDBY) mode before the restore operation. 
Before the full restore operation begins, all sessions are killed in the destination database,
  except for 'sa', using the dbo.prc_kill_sessions procedure in the local database.
After the restore operation the destination database is recovered (left operational).

PARAMETERS
 - 1 @dbname       NVARCHAR(128)                     Name of the database.
 - 2 @backup_path  VARCHAR(265)                      Path to folder holding all the database backup files.
 - 3 @subdir       BIT           0/1 (default)       Backup files are on subdirectory with database name when value = 1 (default). Value 0 means not on subdirectory.
 - 4 @new_dbname   NVARCHAR(128)                     Optional name of the destination database (must be different from the original database).
 - 5 @option       VARCHAR(20)  'RESTORE' (default)  Execute restore.
                                'SCRIPT'             Script restore. The script is presented in grid lines.
                                'REPORT'             Report backup files. The list is presented in grid lines.

EXAMPLES
-- 1) Restore the database from it's latest backup files and recover the database (leave it operational).
EXEC dbo.prc_restore_database @dbname='DBGERRIT', @backup_path='C:\Data\MSSQL\backup';

-- 2) Script restore the database from it's latest backupfiles and recover the database (leave it operational).
EXEC dbo.prc_restore_database @dbname='DBGERRIT', @backup_path='C:\Data\MSSQL\backup', @option='SCRIPT';

-- 3) Report all backupfiles of the database and mark latest backup files.
EXEC dbo.prc_restore_database @dbname='DBGERRIT', @backup_path='C:\Data\MSSQL\backup', @option='REPORT';

-- 4) Restore a database from it's latest backup files to a new database and recover that database (leave it operational).
EXEC dbo.prc_restore_database @dbname='DBGERRIT', @backup_path='C:\Data\MSSQL\backup', @new_dbname='DBGERRIT_NEW';

-- 5) Script restore a database from it's latest backup files to a new database and recover that database (leave it operational).
EXEC dbo.prc_restore_database @dbname='DBGERRIT', @backup_path='C:\Data\MSSQL\backup', @new_dbname='DBGERRIT_NEW', @option='SCRIPT';

USED OBJECTS
 - dbo.fun_sqlversion (UDF in local database)
 - dbo.fun_FolderExist (UDF in local database)
 - dbo.fun_filepath (UDF in local database)
 - dbo.fun_filename (UDF in local database)
 - dbo.prc_kill_sessions (SP in local database)
 - sys.dm_os_enumerate_filesystem

SQLSERVER CONFIGURATION
 - OLE Automation has to be enabled.

HISTORY
2011-10-12 - Created procedure
2012-04-25 - Use xp_dirtree instead of xp_cmdshell
2013-11-06 - Build in SQL version independency
2014-11-24 - Made suitable for sql2014
2014-12-15 - PRINT 'OK' after restore database with recovery omitted.
           - PRINT 'ERROR: ' before "Failed to execute sql - "
2014-12-16 - Changed cursor my_cur1 and my_cur2 to LOCAL READ_ONLY
2015-11-25 - Replaced procedure prc_isdir to function fun_FolderExist
           - Introduced table @header2 for new header layout in version 2014-SP1
2016-09-15 - Can now restore from splitted full backupfiles
           - Ignore copy_only backupfiles
2016-11-15 - Accepted SQL version 13.0
           - Introduced RAISERROR on every fail
2016-12-28 - Fixed bug: Mark splitted files failed on backups with same FirstLSN
2018-05-08 - Adapted SQL version 14.0
2020-11-23 - Adapted SQL2019
2023-03-13 - Removed compliancy for versions SQL2000 (9.0), SQL2008 (10.0) and SQL2008R2 (10.5)
           - Added compliancy for version SQL2022 (16.0)
           - Renamed parameter @dmpdir to @backup_path
2026-03-29 - Removed compliancy for versions below SQL2022 (16.0)
           - Added minversion tag (16)
2026-04-07 - Re-added compliancy for versions SQL2014-SP1 (12.0) until SQL2022 (16.0)
           - Renamed procedure from prc_restore_db to prc_restore_database
           - Use temp tables for backup header info and filelist info
           - Changed parameter @new_dbname to @dest_dbname
           - Changed parameter @bakdir to @backup_path
           - Added parameter @subdir
2026-04-08 - Procedure prc_restore_database now includes all functionality of procedure prc_recover_database.
           - Changed parameter @dest_dbname to @new_dbname
2026-04-10 - Fixed bug: some commands have no database name
           - Output script in grid lines
           - Reduced number of remark lines in output

TAGS
<program>
  <description>Restore a database from it's latest backupfiles</description>
  <generic>1</generic>
  <minversion>12</minversion>
  <author>Gerrit Mantel</author>
  <created>2011-10-12</created>
  <lastmodified>2026-04-10</lastmodified>
</program>
*/

  SET NOCOUNT ON

  DECLARE @fn NVARCHAR(128)
  DECLARE @position SMALLINT
  DECLARE @cnt INT
  DECLARE @sqlcmd NVARCHAR(MAX)
  DECLARE @exec NVARCHAR(MAX)
  DECLARE @filespec NVARCHAR(MAX)
  DECLARE @message VARCHAR(512)
  DECLARE @remark NVARCHAR(512)
  DECLARE @script TABLE ([line] INT IDENTITY, [command] NVARCHAR(MAX));
  DECLARE @version NUMERIC(5,1)
  DECLARE @productlevel NVARCHAR(128)

  DECLARE @move NVARCHAR(MAX)
  DECLARE @logical_name NVARCHAR(128)
  DECLARE @physical_name NVARCHAR(128)
  DECLARE @filegroup_name NVARCHAR(128)
  DECLARE @file_type CHAR(1)
  DECLARE @file_count INT
  DECLARE @dest_dbname NVARCHAR(128)
  DECLARE @first_fn NVARCHAR(128)

  SET @version = [dbo].[fun_sqlversion]();
  SET @productlevel = CONVERT(NVARCHAR(128),SERVERPROPERTY ('ProductLevel'));

  -- Compliant SQL versions are SQL2012-SP1 until SQL2022
  IF @version NOT IN (12.0, 13.0, 14.0, 15.0, 16.0)
  BEGIN
    SET @message = 'ERROR - SQL Server version not supported';
    RAISERROR(@message, 16, 1);
    RETURN 1;
  END

  IF (@version = 12.0 AND @productlevel = 'RTM')
  BEGIN
    SET @message = 'ERROR - SQL Server version not supported';
    RAISERROR(@message, 16, 1);
    RETURN 1;
  END

  -- Backup files table
  DECLARE @bakfiles TABLE (
    [fn] NVARCHAR(265));

  -- Restore files table
  DECLARE @resfiles TABLE (
    [fn] NVARCHAR(128),
    [Position] SMALLINT,
    [BackupType] SMALLINT,
    [FirstLSN] NUMERIC(25,0),
    [DatabaseBackupLSN] NUMERIC(25,0),
    [BackupStartDate] DATETIME,
    [IsCopyOnly] BIT,
    [BackupSetGUID] UNIQUEIDENTIFIER,
    [Mark] SMALLINT)

  -- Header table (must be a tempdb table to add columns for SQL2022)
  IF OBJECT_ID('tempdb..#tmp_header') IS NOT NULL DROP TABLE #tmp_header;
  CREATE TABLE #tmp_header (
    [BackupName] NVARCHAR(128),
    [BackupDescription] NVARCHAR(255),
    [BackupType] SMALLINT,
    [ExpirationDate] DATETIME,
    [Compressed] BIT,
    [Position] SMALLINT,
    [DeviceType] TINYINT,
    [UserName] NVARCHAR(128),
    [ServerName] NVARCHAR(128),
    [DatabaseName] NVARCHAR(128),
    [DatabaseVersion] INT,
    [DatabaseCreationDate] DATETIME,
    [BackupSize] NUMERIC(20,0),
    [FirstLSN] NUMERIC(25,0),
    [LastLSN] NUMERIC(25,0),
    [CheckpointLSN] NUMERIC(25,0),
    [DatabaseBackupLSN] NUMERIC(25,0),
    [BackupStartDate] DATETIME,
    [BackupFinishDate] DATETIME,
    [SortOrder] SMALLINT,
    [CodePage] SMALLINT,
    [UnicodeLocaleId] INT,
    [UnicodeComparisonStyle] INT,
    [CompatibilityLevel] TINYINT,
    [SoftwareVendorId] INT,
    [SoftwareVersionMajor] INT,
    [SoftwareVersionMinor] INT,
    [SoftwareVersionBuild] INT,
    [MachineName] NVARCHAR(128),
    [Flags] INT,
    [BindingID] UNIQUEIDENTIFIER,
    [RecoveryForkID] UNIQUEIDENTIFIER,
    [Collation] NVARCHAR(128),
    [FamilyGUID] UNIQUEIDENTIFIER,
    [HasBulkLoggedData] BIT,
    [IsSnapshot] BIT,
    [IsReadOnly] BIT,
    [IsSingleUser] BIT,
    [HasBackupChecksums] BIT,
    [IsDamaged] BIT,
    [BeginsLogChain] BIT,
    [HasIncompleteMetaData] BIT,
    [IsForceOffline] BIT,
    [IsCopyOnly] BIT,
    [FirstRecoveryForkID] UNIQUEIDENTIFIER,
    [ForkPointLSN] NUMERIC(25,0),
    [RecoveryModel] NVARCHAR(60),
    [DifferentialBaseLSN] NUMERIC(25,0),
    [DifferentialBaseGUID] UNIQUEIDENTIFIER,
    [BackupTypeDescription] NVARCHAR(60),
    [BackupSetGUID] UNIQUEIDENTIFIER,
    [CompressedBackupSize] BIGINT,
    [Containment] TINYINT, -- new in 2012
    [KeyAlgorithm] NVARCHAR(32), -- new in 2014-SP1
    [EncryptorThumbprint] VARBINARY(20), -- new in 2014-SP1
    [EncryptorType] NVARCHAR(32)); -- new in 2014-SP1

  IF @version = 16.0
  BEGIN
    ALTER TABLE #tmp_header ADD
    [LastValidRestoreTime] DATETIME, -- new in 2022 (Undocumented)
    [TimeZone] INT, -- new in 2022 (Undocumented)
    [CompressionAlgorithm] NVARCHAR(32); -- new in 2022
  END

  -- Filelist table (must be a tempdb table to add columns for SQL2016 and higher)
  IF OBJECT_ID('tempdb..#tmp_filelist') IS NOT NULL DROP TABLE #tmp_filelist;
  CREATE TABLE #tmp_filelist (
    [LogicalName] NVARCHAR(128),
    [PhysicalName] NVARCHAR(260),
    [Type] CHAR(1),
    [FileGroupName] NVARCHAR(128),
    [Size] NUMERIC(20,0),
    [MaxSize] NUMERIC(20,0),
    [FileID] BIGINT,
    [CreateLSN] NUMERIC(25,0),
    [DropLSN] NUMERIC(25,0),
    [UniqueID]  UNIQUEIDENTIFIER,
    [ReadOnlyLSN] NUMERIC(25,0),
    [ReadWriteLSN] NUMERIC(25,0),
    [BackupSizeInBytes] BIGINT,
    [SourceBlockSize] INT,
    [FileGroupID] INT,
    [LogGroupGUID] UNIQUEIDENTIFIER,
    [DifferentialBaseLSN] NUMERIC(25,0),
    [DifferentialBaseGUID] UNIQUEIDENTIFIER,
    [IsReadOnly] BIT,
    [IsPresent] BIT);

  IF @version >= 13.0
  BEGIN
    ALTER TABLE #tmp_filelist ADD
    [TDEThumbprint] VARBINARY(32), -- New in SQL2016
    [SnapshotURL] NVARCHAR(360); -- New in SQL2016
  END

  IF ISNULL(@dbname,'') = ''
  BEGIN
    SET @message = 'ERROR - No database name given'
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  IF ISNULL(@backup_path,'') = ''
  BEGIN
    SET @message = 'ERROR - No backup path given'
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  IF @new_dbname = @dbname
  BEGIN
    SET @message = 'ERROR - The new database cannot be the same as the (original) database'
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  -- Destination database
  IF ISNULL(@new_dbname,'') <> '' SET @dest_dbname = @new_dbname ELSE SET @dest_dbname = @dbname;

  -- Strip '\' from given dump directoryname
  IF RIGHT(@backup_path,1) = N'\' SET @backup_path = LEFT(@backup_path,LEN(@backup_path)-1);

  -- Check if backup_path exists
  IF dbo.fun_FolderExist(@backup_path) = 0
  BEGIN
    SET @message = 'ERROR - Backup path "' + @backup_path + '" does not exist';
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  IF @option NOT IN ('RESTORE', 'SCRIPT', 'REPORT')
  BEGIN
    SET @message = 'ERROR - Option must be RESTORE (default), SCRIPT or REPORT'
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  -- Define backup_path with subdir
  IF @subdir = 1
  BEGIN
    SET @backup_path = @backup_path + '\' + @dbname;
  END

  -- Retrieve all filenames on dumpdirectory for this database
  -- The leading part of the filename must be the databasename
  INSERT INTO @bakfiles ([fn])
  SELECT [file_or_directory_name]
  FROM sys.dm_os_enumerate_filesystem(@backup_path, N'*.*')
  WHERE [level] = 0 
  AND [is_directory] = 0
  AND [file_or_directory_name] LIKE @dbname+'%';

  SET @cnt = (SELECT COUNT(*) FROM @bakfiles);
  IF @cnt = 0
  BEGIN
    SET @message = 'ERROR - No backupfiles of database '+ @dbname + ' found'
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  DECLARE my_cur1 CURSOR LOCAL READ_ONLY FOR 
    SELECT [fn] 
    FROM @bakfiles 
    ORDER BY [fn];

  OPEN my_cur1
  WHILE 1 < 2
  BEGIN
    FETCH NEXT FROM my_cur1 INTO @fn
    IF @@FETCH_STATUS <> 0 BREAK
    SET @exec = N'RESTORE HEADERONLY FROM DISK ='''+@backup_path+'\'+@fn+''''

    TRUNCATE TABLE #tmp_header;
    INSERT INTO #tmp_header EXEC (@exec);
  
    INSERT INTO @resfiles ([fn], [Position], [BackupType], [FirstLSN], [DatabaseBackupLSN], [BackupStartDate], [IsCopyOnly], [BackupSetGUID])
    SELECT @fn, [Position], [BackupType], [FirstLSN], [DatabaseBackupLSN], [BackupStartDate], [IsCopyOnly], [BackupSetGUID]
    FROM #tmp_header
    WHERE [DatabaseName] = @dbname;
  END
  CLOSE my_cur1
  DEALLOCATE my_cur1

  -- Mark last full backup
  UPDATE t1
  SET t1.[Mark] = 1
  FROM @resfiles t1
  WHERE t1.[fn]+CONVERT(NVARCHAR,t1.[Position]) = (
    SELECT TOP 1 [fn]+CONVERT(NVARCHAR,[Position])
    FROM @resfiles
    WHERE [BackupType] = 1
    AND [IsCopyOnly] = 0
    ORDER BY [FirstLSN] DESC, [BackupStartDate] DESC);

  SET @cnt = (SELECT COUNT(*) FROM @resfiles WHERE [Mark] = 1);
  IF @cnt = 0
  BEGIN
    SET @message = 'ERROR - No full backupfiles (no COPY_ONLY) of database '+ @dbname + ' found'
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  -- Mark full backup splitfiles
  UPDATE t1
  SET t1.[Mark] = 1
  FROM @resfiles t1
  WHERE t1.[Mark] IS NULL
  AND t1.[BackupSetGUID] = (
    SELECT [BackupSetGUID]
    FROM @resfiles
    WHERE [Mark] = 1);

  -- Mark last diff backup
  UPDATE t1
  SET t1.[Mark] = 2
  FROM @resfiles t1
  WHERE t1.[fn]+CONVERT(NVARCHAR,t1.[Position]) = (
    SELECT TOP 1 [fn]+CONVERT(NVARCHAR,[Position])
    FROM @resfiles
    WHERE [BackupType] = 5
    AND [IsCopyOnly] = 0
    ORDER BY [FirstLSN] DESC, [BackupStartDate] DESC)
  AND [DatabaseBackupLSN] = (
    SELECT TOP 1 [FirstLSN]
    FROM @resfiles
    WHERE [Mark] = 1); 

  -- Mark last log backups
  UPDATE t1
  SET t1.[Mark] = 3
  FROM @resfiles t1
  WHERE t1.[BackupType] = 2
  AND t1.[IsCopyOnly] = 0
  AND t1.[DatabaseBackupLSN] = (
    SELECT TOP 1 [FirstLSN]
    FROM @resfiles
    WHERE [Mark] = 1)
  AND t1.[BackupStartDate] >= (
    SELECT MAX([BackupStartDate])
    FROM @resfiles
    WHERE [Mark] IN (1,2));

  IF @option = 'REPORT'
  BEGIN
    SELECT
      [fn],
      [Position],
      CASE [BackupType] WHEN 1 THEN 'Full' WHEN 5 THEN 'Diff' WHEN 2 THEN 'Log' ELSE 'Unknown' END AS [BackupType],
      [FirstLSN],
      [DatabaseBackupLSN],
      [BackupStartDate],
      [IsCopyOnly],
      [Mark] AS [RestoreOrder]
    FROM @resfiles
    ORDER BY [BackupStartDate], [Mark], [fn];

    RETURN
  END

  -- Kill sessions on target database
  SET @remark = N'-- Kill sessions on database ' + @dest_dbname;
  IF @option = 'SCRIPT'
  BEGIN
    SET @sqlcmd = N'EXEC dbo.[prc_kill_sessions] @dbname='''+@dest_dbname+N''';'
    INSERT INTO @script ([command]) VALUES (@remark);
    INSERT INTO @script ([command]) VALUES (@sqlcmd);
    INSERT INTO @script ([command]) VALUES (N'GO');
  END
  ELSE
  BEGIN
    SET @message = @remark;
    RAISERROR (@message, 0, 1) WITH NOWAIT;
    EXEC dbo.[prc_kill_sessions] @dbname=@dest_dbname;
  END

  SET @remark = N'-- Restore database ' + @dest_dbname;
  IF @option = 'SCRIPT'
  BEGIN
    INSERT INTO @script ([command]) VALUES (@remark);
  END
  ELSE
  BEGIN
    SET @message = @remark;
    RAISERROR (@message, 0, 1) WITH NOWAIT;
  END

  -- Get first full backup filename for error messagaging
  SET @first_fn = (SELECT TOP 1 [fn] FROM @resfiles WHERE [Mark] = 1 ORDER BY [FirstLSN], [BackupStartDate], [fn]);

  -- Get full backupfile list
  -- Full backupfiles may be splitted, so multiple files are possible
  DECLARE my_cur2 CURSOR LOCAL READ_ONLY FOR
    SELECT [fn], [Position]
    FROM @resfiles
    WHERE [Mark] = 1
    ORDER BY [FirstLSN], [BackupStartDate], [fn];

  SET @sqlcmd = N'RESTORE DATABASE [' + @dest_dbname + N'] FROM ';
  SET @filespec = N'';

  OPEN my_cur2
  WHILE 1 < 2
  BEGIN
    FETCH NEXT FROM my_cur2 INTO @fn, @position
    IF @@FETCH_STATUS <> 0 BREAK

    SET @filespec = @filespec + N', DISK=''' + @backup_path + N'\' + @fn + N'''';
  END
  CLOSE my_cur2
  DEALLOCATE my_cur2

  -- Trim ', ' from left of @filespec
  SET @filespec = RIGHT(@filespec,LEN(@filespec)-2);

  SET @sqlcmd = @sqlcmd + @filespec + N' WITH ';

  IF ISNULL(@new_dbname,'') <> ''
  BEGIN
    -- Make MOVE command
    -- Get filelist of full backupfile (use last file if splitted)
    SET @exec = N'RESTORE FILELISTONLY FROM DISK='''+@backup_path+N'\'+@fn+N''''
    INSERT INTO #tmp_filelist EXEC (@exec);
  
    -- Only files applied to the backup are used (is_present = 1)
    SET @move = N'';
    SET @file_count = 0;
  
    DECLARE my_cur3 CURSOR READ_ONLY FOR 
      SELECT [LogicalName], [PhysicalName], [Type], [FileGroupName]
      FROM #tmp_filelist
      WHERE [IsPresent] = 1
      ORDER BY [LogicalName];
  
    OPEN my_cur3
    WHILE 1 < 2
    BEGIN
      FETCH NEXT FROM my_cur3 INTO @logical_name, @physical_name, @file_type, @filegroup_name;
      IF @@FETCH_STATUS <> 0 BREAK;
      SET @file_count = @file_count + 1;
      IF @file_count > 1 SET @move = @move + ', ';
  
      -- Adjust physicalname
      -- When old database name is in physical name, replace it to new database name
      -- otherwise integrate new database name in physical name.
      IF CHARINDEX(@dbname,@physical_name) <> 0
        SET @physical_name = REPLACE(@physical_name, @dbname, @new_dbname);
      ELSE
        SET @physical_name = dbo.fun_filepath(@physical_name) + N'\' + @new_dbname + N'_' + dbo.fun_filename(@physical_name);
  
      SET @move = @move + N'MOVE ''' + @logical_name + N''' TO ''' + @physical_name + N'''';
    END
    CLOSE my_cur3
    DEALLOCATE my_cur3
    SET @sqlcmd = @sqlcmd + @move + N', ';
  END

  SET @sqlcmd = @sqlcmd + 'FILE=' + CONVERT(NVARCHAR,@position) + N', REPLACE, NORECOVERY;';

  IF @option = 'SCRIPT'
  BEGIN
    INSERT INTO @script ([command]) VALUES (@sqlcmd);
  END
  ELSE
  BEGIN
    EXEC sp_executesql @sqlcmd
    IF @@ERROR <> 0
    BEGIN
      SET @message = 'ERROR - Restore full backupfile '+@first_fn+' failed';
      RAISERROR(@message, 16, 1);
      RETURN 1
    END
  END

  -- Get diff backupfile list
  -- Assumed is that diff backupfiles are not splitted
  DECLARE my_cur4 CURSOR LOCAL READ_ONLY FOR
    SELECT [fn], [Position]
    FROM @resfiles
    WHERE [Mark] = 2
    ORDER BY [FirstLSN], [BackupStartDate], [fn];

  OPEN my_cur4
  WHILE 1 < 2
  BEGIN
    FETCH NEXT FROM my_cur4 INTO @fn, @position
    IF @@FETCH_STATUS <> 0 BREAK

    SET @sqlcmd = N'RESTORE DATABASE [' + @dest_dbname + N'] FROM DISK=''' + @backup_path + '\' + @fn + N''' WITH FILE=' + CONVERT(NVARCHAR,@position) + ', NORECOVERY;';
    IF @option = 'SCRIPT'
    BEGIN
      INSERT INTO @script ([command]) VALUES (@sqlcmd);
    END
    ELSE
    BEGIN
      EXEC sp_executesql @sqlcmd
      IF @@ERROR <> 0
      BEGIN
        SET @message = 'ERROR - Restore differential backupfile ' +@fn+ ' failed';
        RAISERROR(@message, 16, 1);
        RETURN 1
      END
    END
  END
  CLOSE my_cur4
  DEALLOCATE my_cur4

  -- Get log backupfile list
  -- Assume log backupfiles are not splitted
  DECLARE my_cur5 CURSOR LOCAL READ_ONLY FOR
    SELECT [fn], [Position]
    FROM @resfiles
    WHERE [Mark] = 3
    ORDER BY [FirstLSN], [BackupStartDate], [fn];

  OPEN my_cur5
  WHILE 1 < 2
  BEGIN
    FETCH NEXT FROM my_cur5 INTO @fn, @position
    IF @@FETCH_STATUS <> 0 BREAK

    SET @sqlcmd = N'RESTORE LOG [' + @dest_dbname + N'] FROM DISK=''' + @backup_path + N'\' + @fn + N''' WITH FILE=' + CONVERT(NVARCHAR,@position) + N', NORECOVERY;';
    IF @option = 'SCRIPT'
    BEGIN
      INSERT INTO @script ([command]) VALUES (@sqlcmd);
    END
    ELSE
    BEGIN
      EXEC sp_executesql @sqlcmd
      IF @@ERROR <> 0
      BEGIN
        SET @message = 'ERROR - Restore log backupfile ' +@fn+ ' failed';
        RAISERROR(@message, 16, 1);
        RETURN 1
      END
    END
  END
  CLOSE my_cur5
  DEALLOCATE my_cur5

  IF @option = 'SCRIPT'
  BEGIN
    INSERT INTO @script ([command]) VALUES (N'GO');
  END

  -- Recover database
  SET @remark = N'-- Recover database '+@dest_dbname;
  IF @option = 'SCRIPT'
  BEGIN
    INSERT INTO @script ([command]) VALUES (@remark);
  END
  ELSE
  BEGIN
    SET @message = @remark;
    RAISERROR (@message, 0, 1) WITH NOWAIT;
  END

  SET @sqlcmd = N'RESTORE DATABASE [' +@dest_dbname+ N'] WITH RECOVERY;';
  IF @option = 'SCRIPT'
  BEGIN
    INSERT INTO @script ([command]) VALUES (@sqlcmd);
    INSERT INTO @script ([command]) VALUES (N'GO');
  END
  ELSE
  BEGIN
    EXEC sp_executesql @sqlcmd
    IF @@ERROR <> 0
    BEGIN
      SET @message = 'ERROR: Recover database failed';
      RAISERROR(@message, 16, 1);
      RETURN 1
    END
  END

  IF ISNULL(@new_dbname,'') <> ''
  BEGIN
    SET @remark = N'-- Rename logical filenames of database ' + @new_dbname;
    IF @option = 'SCRIPT'
    BEGIN
      INSERT INTO @script ([command]) VALUES (@remark);
    END
    ELSE
    BEGIN
      SET @message = @remark;
      RAISERROR (@message, 0, 1) WITH NOWAIT;
    END
  
    -- Rename logical filenames
    -- Get datafile name list
    DECLARE my_cur6 CURSOR READ_ONLY FOR 
      SELECT [LogicalName]
      FROM #tmp_filelist
      WHERE [IsPresent] = 1
      ORDER BY [LogicalName];
  
    OPEN my_cur6
    WHILE 1 < 2
    BEGIN
      FETCH NEXT FROM my_cur6 INTO @logical_name;
      IF @@FETCH_STATUS <> 0 BREAK;
  
      -- Rename logical name only when @dbname can be replaced to @new_dbname
      IF CHARINDEX(@dbname,@logical_name) <> 0
      BEGIN
        SET @sqlcmd = 'IF DATABASEPROPERTYEX ('''+ @new_dbname +''', ''Updateability'') = ''READ_WRITE'' ALTER DATABASE ['+ @new_dbname +'] MODIFY FILE (NAME = ''' + 
          @logical_name + ''', NEWNAME = ''' + REPLACE(RTRIM(@logical_name),@dbname,@new_dbname) + ''')';
        IF @option = 'SCRIPT'
        BEGIN
          INSERT INTO @script ([command]) VALUES (@sqlcmd);
        END
        ELSE
        BEGIN
          EXEC sp_executesql @stmt=@sqlcmd;
          IF @@ERROR <> 0
          BEGIN
            SET @message = 'ERROR - Rename logical filename ' +@logical_name+ ' failed';
            RAISERROR(@message, 16, 1);
            RETURN 1;
          END
        END
      END
    END
    CLOSE my_cur6
    DEALLOCATE my_cur6

    IF @option = 'SCRIPT'
    BEGIN
      INSERT INTO @script ([command]) VALUES (N'GO');
      INSERT INTO @script ([command]) VALUES (N'');
    END
  END

  -- Select script when option is SCRIPT
  IF @option = 'SCRIPT' SELECT [command] FROM @script ORDER BY [line];

  -- Drop temp tables
  DROP TABLE #tmp_header;
  DROP TABLE #tmp_filelist;
END
GO