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?
