CODEWITHSUNDEEP
sqlsql-server
Kioko Kiaza
Kioko Kiaza

Reputation: 1398

Given database and date, retrieve bak, diff and all trn files to restore a database

We usually seek using File Explorer for all files we need to restore a database to certain date. Now I want to automatize this maybe using a stored procedure with a database name and date like parameters. Which I need to obtain is:

This way, I will be able to restore the database. I have this which is helpful:

SELECT 
    database_name ,
    physical_device_name,
    backup_start_date,
    backup_finish_date,
    backup_size/1024.0 AS BackupSizeKB
FROM
    msdb.dbo.backupset b 
JOIN 
    msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE
    database_name = 'XEXPERTIS01'
ORDER BY 
    backup_finish_date DESC
go

Any help?

Upvotes: 0

Views: 157

Answers (1)

Ross Presser
Ross Presser

Reputation: 6255

-BAK file:

SELECT TOP 1
        database_name, physical_device_name, backup_start_date,
        backup_finish_date, backup_size / 1024.0 AS BackupSizeKB
FROM    msdb.dbo.backupset b
JOIN    msdb.dbo.backupmediafamily m
        ON b.media_set_id = m.media_set_id
WHERE   database_name = 'XEXPERTIS01'
        AND physical_device_name LIKE '%.bak'
ORDER BY backup_finish_date DESC;
    GO

DIFF files:

SELECT  database_name, physical_device_name, backup_start_date,
        backup_finish_date, backup_size / 1024.0 AS BackupSizeKB
FROM    msdb.dbo.backupset b
JOIN    msdb.dbo.backupmediafamily m
        ON b.media_set_id = m.media_set_id
WHERE   database_name = 'XEXPERTIS01'
        AND physical_device_name LIKE '%.diff'
        AND backup_start_date > (
                                  SELECT TOP 1
                                            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 = 'XEXPERTIS01'
                                            AND physical_device_name LIKE '%.bak'
                                  ORDER BY  backup_finish_date DESC
                                );
    GO

TRN files:

SELECT  database_name, physical_device_name, backup_start_date,
        backup_finish_date, backup_size / 1024.0 AS BackupSizeKB
FROM    msdb.dbo.backupset b
JOIN    msdb.dbo.backupmediafamily m
        ON b.media_set_id = m.media_set_id
WHERE   database_name = 'XEXPERTIS01'
        AND physical_device_name LIKE '%.trn'
        AND backup_start_date > (
                                  SELECT TOP 1
                                            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 = 'XEXPERTIS01'
                                            AND (physical_device_name LIKE '%.diff'
                                            OR physical_device_name LIKE '%.bak')
                                  ORDER BY  backup_finish_date DESC
                                );
    GO

Upvotes: 2

Related Questions