SQL获取SQL Server中特定数据库的最新完整备份文件 [英] Sql to get most recent full backup file for a specific database in sql server

问题描述

我需要一个sql命令(dbcc或扩展存储过程?)或可以从sql server存储过程调用的东西，以获取可用于特定数据库的最新完整备份文件.备份文件的名称将放置在varchar变量中，该变量可以与存储过程中的RESTORE DATABASE命令一起使用.此过程将用于从生产数据库还原到沙箱/培训数据库，因此还原完成后，我需要该过程才能继续运行，以便可以对数据库进行一些修改.

I need an sql command (dbcc or extended stored procedure?) or something that can be called from a sql server stored procedure to get the most recent full backup file available for a specific database. The name of the backup file will be placed in a varchar variable that I can use with a RESTORE DATABASE command in the stored procedure. This procedure will be used to restore from production database to a sandbox/training database, so after the restore completes I need the procedure to continue running so I can make a few modifications to the db.

推荐答案

只需查询 源"产品服务器上的msdb..backupset(MSDN)

还有一个工作示例(SQL Rockstar)

编辑，2018

SELECT
    bs.database_name,
    bs.backup_start_date,
    bmf.physical_device_name
FROM
    msdb.dbo.backupmediafamily bmf
    JOIN
    msdb.dbo.backupset bs ON bs.media_set_id = bmf.media_set_id
WHERE
    bs.database_name = 'MyDB'
ORDER BY
    bmf.media_set_id DESC;

这篇关于SQL获取SQL Server中特定数据库的最新完整备份文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！