SQL Sql获取sql server中特定数据库的最新完整备份文件

Sql to get most recent full backup file for a specific database in sql server

提问by Joel Coehoorn

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.

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

回答by gbn

Just query msdb..backupset (MSDN)on the "source" prod server

只需在“源”生产服务器上查询msdb..backupset (MSDN)

And a working example (SQL Rockstar)too

而一个工作示例（SQL的Rockstar）太

Edit, 2018

编辑，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;

回答by user2378139

I have a handy script that I use when I restore the most recent backup from a directory to the database you want to restore to. It is great for refreshing a dev or test box by using a nightly job!

我有一个方便的脚本，当我将最近的备份从目录恢复到要恢复到的数据库时，我会使用它。使用夜间作业刷新开发或测试框非常有用！

/******************************************************
Script: looks at the backup directory and restores the
    most recent backup (bak) file 
    You will have to modify the code
    to match your database names and paths.
    DO NOT USE IN PRODUCTION.  It kicks all users off!

Created By:
    Michael F. Berry
Create Date:
    1/15/2014
******************************************************/


--get the last backup file name and path

Declare @FileName varChar(255)
Declare @cmdText varChar(255)
Declare @BKFolder varchar(255)

set @FileName = null
set @cmdText = null
set @BKFolder = '\MyBackupStorageShare\server\FULL\'


create table #FileList (
FileName varchar(255),
DepthFlag int,
FileFlag int
)


--get all the files and folders in the backup folder and put them in temporary table
insert into #FileList exec xp_dirtree @BKFolder,0,1
--select * from #filelist

--get the latest backup file name
select top 1 @FileName = @BKFolder + FileName from #FileList where Filename like '%.bak' order by filename desc
select @filename


--kick off current users/processes
ALTER DATABASE DBName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;


--execute the restore
exec('
RESTORE DATABASE [DBNAME] FROM  DISK = ''' + @filename + '''
WITH  MOVE N''DBName_Data'' TO N''E:\SQLData\DBName.mdf'', MOVE N''DBName_Log'' TO N''E:\SQLLogs\DBName_log.ldf'',  NOUNLOAD,  REPLACE,  STATS = 10')


--Let people/processes back in!
ALTER DATABASE DBName
SET MULTI_USER WITH ROLLBACK IMMEDIATE;
go 

回答by user5054734

declare @backupfile as Varchar(255)

SELECT TOP 1 @backupfile=mf.physical_device_name from msdb..backupset bk      
join msdb..backupmediafamily mf on bk.media_set_id = mf.media_set_id   
where database_name=N'sourcedatabasename' and bk.type='D' order by  
backup_set_id desc

ALTER DATABASE [databasename] SET  SINGLE_USER  WITH ROLLBACK IMMEDIATE 

RESTORE DATABASE databasename
FROM DISK = @backupfile
WITH MOVE 'datafile' TO 'databasefilepath',
MOVE 'logfilename' TO 'logfilepath', REPLACE

ALTER DATABASE [databasename] SET  MULTI_USER  WITH ROLLBACK IMMEDIATE

回答by Phil

Just wanted to give an addition to the excellent answer from user2378139. I often have the need to restore multiple databases from 1 folder, and there will be multiple backup copies for each database in that folder. I need to get the most recent, and run that on a scheduled task. Below is my edit/update to make that happen. There are more temporary tables than i'd like, but haven't figured a better way to do it yet:

只是想对 user2378139 的出色回答进行补充。我经常需要从 1 个文件夹中恢复多个数据库，并且该文件夹中的每个数据库都会有多个备份副本。我需要获取最新的，并在计划任务上运行它。以下是我的编辑/更新以实现这一目标。有比我想要的更多的临时表，但还没有想出更好的方法来做到这一点：

IF OBJECT_ID('tempdb..#TemperedFileList') IS NOT NULL DROP TABLE #TemperedFileList
GO
Declare @FileName varChar(255)
Declare @cmdText varChar(255)
Declare @BKFolder varchar(255)
Declare @DBFolder varchar(255)

set @FileName = null
set @BKFolder = 'E:\SQLBackupFolder\'
set @DBFolder = 'C:\Program Files\Microsoft SQL Server\MSSQL13.COBRASERVER\MSSQL\DATA\'

declare @FileList table (FileName varchar(255), DepthFlag int, FileFlag int)
--get all the files and folders in the backup folder and put them in temporary table
insert into @FileList exec xp_dirtree @BKFolder,0,1

create table #TemperedFileList (FileName varchar(255),DBName varchar(255))
insert into #TemperedFileList (FileName,DBName) select FileName, SUBSTRING(FileName,0,CHARINDEX('_',FileName)) from @FileList WHERE Filename like '%.bak'

declare @RowCnt int
declare @MaxRows int
declare @tmpFileName varchar(255)
declare @tmpDBName varchar(255)
declare @sql nvarchar(3000)

select @RowCnt = 1

declare @Import table (rownum int IDENTITY (1, 1) Primary key NOT NULL , FileName varchar(255),DBName varchar(255))
insert into @Import (FileName,DBName) SELECT FileName,DBName FROM  (SELECT FileName,DBName,rank() over (partition by DBName order by FileName desc) r FROM  #TemperedFileList ) ilv where r=1

select @MaxRows=count(*) from @Import
while @RowCnt <= @MaxRows
begin
    select @tmpFileName=FileName from @Import where rownum = @RowCnt
    select @tmpDBName=DBName from @Import where rownum = @RowCnt

    set @sql ='ALTER DATABASE ' + @tmpDBName + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
    print @sql
    exec(@sql)

    set @sql ='RESTORE DATABASE [' + @tmpDBName + '] FROM  DISK=''' + @BKFolder + @tmpFileName + ''' with FILE=1, MOVE N''' + @tmpDBName + ''' TO N''' + @DBFolder + @tmpDBName + '.mdf'', MOVE N''' + @tmpDBName + '_Log'' TO N''' + @DBFolder + @tmpDBName + '_log.ldf'',  NOUNLOAD,  REPLACE,  STATS = 10'
    print @sql
    exec(@sql)

    set @sql ='ALTER DATABASE ' + @tmpDBName + ' SET MULTI_USER WITH ROLLBACK IMMEDIATE'
    print @sql
    exec(@sql)

    Set @RowCnt = @RowCnt + 1
end