0

I use the following script from John Eisbrener to get the chain of backups, in order to restore them:

WITH BackupHist
AS
(
       SELECT
                s.server_name
              , d.name AS database_name
              , m.physical_device_name
              , CASE m.device_type
                     WHEN 2 THEN 'Disk'
                     WHEN 102 THEN 'Backup Device (Disk)'
                     WHEN 5 THEN 'Tape'
                     WHEN 105 THEN 'Backup Device (Tape)'
                     WHEN 7 THEN 'Virtual Device'
                END AS device_type
              , CAST (s.backup_size / 1048576.0 AS FLOAT) AS backup_size_mb
              , CAST (s.compressed_backup_size / 1048576.0 AS FLOAT) AS compressed_backup_size_mb
              , s.backup_start_date
              , s.first_lsn
              , s.backup_finish_date
              , s.database_backup_lsn
              , CASE s.[type]
                     WHEN 'D' THEN 'Database (Full)'
                     WHEN 'I' THEN 'Database (Differential)'
                     WHEN 'L' THEN 'Transaction Log'
                     WHEN 'F' THEN 'File or Filegroup (Full)'
                     WHEN 'G' THEN 'File or Filegroup (DIfferential)'
                     WHEN 'P' THEN 'Partial (Full)'
                     WHEN 'Q' THEN 'Partial (Differential)'
                END AS backup_type
              , s.recovery_model
              , ROW_NUMBER () OVER (PARTITION BY s.database_name, s.database_backup_lsn ORDER BY s.backup_start_date) AS Row
       FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily m
              ON s.media_set_id = m.media_set_id
              RIGHT OUTER JOIN sys.databases d
              ON s.database_name = d.name
              AND s.recovery_model = d.recovery_model_desc
       COLLATE SQL_Latin1_General_CP1_CI_AS
), BackupHistFullIterations AS
(
    SELECT database_name
         , backup_finish_date
         , first_lsn
         , ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY backup_finish_date DESC) AS BackupIteration
    FROM BackupHist
    WHERE backup_type = 'Database (Full)'
)
SELECT    bh.server_name
        , bh.database_name
        , bh.backup_finish_date
        , bh.backup_type
        , CASE backup_type WHEN 'Database (Full)' THEN 'RESTORE DATABASE [' + bh.database_name + '] FROM  DISK = N''' + bh.physical_device_name + ''' WITH  FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5'
            WHEN 'Transaction Log' THEN 'RESTORE LOG [' + bh.database_name + '] FROM  DISK = N''' + bh.physical_device_name + ''' WITH  FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10'
            ELSE ''
          END AS RestoreStatement
FROM BackupHist bh INNER JOIN 
        (
            SELECT *
            FROM BackupHistFullIterations
            WHERE BackupIteration = 1 -- Show the X most recent iteration(s)
        ) bhfi
     ON bh.database_name = bhfi.database_name
    AND (bh.database_backup_lsn >= bhfi.first_lsn
         OR bh.first_lsn = bhfi.first_lsn)
    AND (bh.backup_finish_date >= bhfi.backup_finish_date)  -- used in case db was rebuilt/lsn reset
ORDER BY 1, 2, 3

after I restore my last transaction log....

Msg 33117, Level 16, State 2, Line 671 Transparent Data Encryption is not available in the edition of this SQL Server instance. See books online for more details on feature support in different SQL Server editions. Msg 3013, Level 16, State 1, Line 671 RESTORE DATABASE is terminating abnormally. Msg 933, Level 21, State 1, Line 671 Database 'Radhe' cannot be started because some of the database functionality is not available in the current edition of SQL Server.

I was in a rush and went off to restore a database to a suport server, then I get the error below, after I finished restoring the last transaction log and wanted to bring the database to life:

-- @MediaSetIdStopAT: 304393
-- @LogMediaSetID....: 304393
-- @backup_finish_date 06 Feb 2024 17:20:04
RESTORE Log [Radhe]
FROM DISK = '\\my_server\SQLBackups\LONDONSCLS001$BESTCLIENTCS001\PRIMITIVO\LOG\LONDONSCLS001$BESTCLIENTCS001\_LOG_20240206_172002.trn'
WITH FILE = 1
,STATS=1,NORECOVERY

 
RESTORE DATABASE [Radhe] WITH RECOVERY 

and when you think everything is ok, you get that message above.

so,

I know that you need to check for certificates:

but other than that: what should I do next time, to make sure I don't get to the same error?

2
  • What is the version and edition of source and destination servers? TDE is available in Standard edition only for SQL 2019.
    – SergeyA
    Feb 7 at 22:24
  • very well mentioned @SergeyA, on this server in particular it is a developer edition of sql server 2016 Feb 8 at 10:44

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.