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

Question

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

Answer 1

只是想对 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

Answer 2

这是建立在上述答案的基础上的。
源数据库和恢复数据库位于脚本中的多个位置。
这就为错误留下了空间，即您无法在需要的位置之一更正数据库名称或恢复数据库名称。

下面使用@user5054734的解决方案获取备份文件。
剩下的就是生成 SQL，然后执行 exec('sql string')
作为模板，我使用了通过恢复向导为我生成的 MSSQL 脚本。

我还将引用 @user2378139 的 exec(@sql) 方法。我已经有一段时间没有这样做了。
您必须使用 exec('')，因为 RESTORE DATABASE 命令无法使用命令中嵌入的变量。 （如果可以，请告诉我如何......很确定它不能）。

我希望生成的 sql 示例如下所示 这

 ALTER DATABASE [testDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [testDB] FROM  DISK = 'C:\sqlbackups\2023 09 07  10 00 proddb.bak' WITH  FILE = 1 
,MOVE 'prodDB' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\testDB.mdf'
,MOVE 'prodDB_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\testDB_log.ldf'
,  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [testDB] SET MULTI_USER

是生成该 SQL 然后执行它的脚本

希望它对某人有所帮助。

USE [master]

declare @backupfile as Varchar(255)
, @dataFolder varchar(255) =   N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\'
, @sourceDatabaseName varchar(100) = 'mydb'
, @restoreDatabaseName varchar(100) ='mydb_test'
 
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=USE [master]

将 @backupfile 声明为 Varchar(255)
，@dataFolder varchar(255) = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA'
, @sourceDatabaseName varchar(100) = 'circupool'
, @restoreDatabaseName varchar(100) ='circupool_testDWH'

选择 TOP 1 @backupfile=mf.physical_device_name from msdb..backupset bk
在 bk.media_set_id = mf.media_set_id
上加入 msdb..backupmediafamily mf
其中database_name=N'circupool' 和bk.type='D' order by
backup_set_id desc

声明 @restoreMDF varchar(200) = concat(@dataFolder,@restoreDatabaseName,'.mdf')
, @sourceLogFile varchar(200) = concat(@sourceDatabaseName,'_Log')
, @restoreLog varchar(200) = concat(@dataFolder,@restoreDatabaseName, '_log.ldf')

选择
@备份文件 [备份文件]
，@dataFolder [dataFolder]
, @sourceLogFile [sourceLogFile]
, @restoreDatabaseName [restoreDBname]
，@restoreMDF [恢复MDF]
, @restoreLog [restorelog]

声明 @crlf varchar(10) = CHAR(13) + CHAR(10)
声明 @sql varchar(1000) =
concat('ALTER DATABASE [',@restoreDatabaseName, '] 设置单用户立即回滚;', @crlf
,'从磁盘恢复数据库 [',@restoreDatabaseName,'] = ''',@backupfile,'''WITH FILE = 1 ',@crlf
,',移动 ''',@sourceDatabaseName, ''' 到 ''',@restoreMDF,'''', @crlf
,',移动''',@sourceLogFile,'''到''',@restoreLog,'''',@crlf
,', NOUNLOAD, 替换, 统计 = 5', @crlf
，'更改数据库 [circupool_testDWH] 设置多用户'，@crlf

）
打印@sql
exec( @sql ) 和 bk.type='D' 排序依据
backup_set_id 描述

 declare @restoreMDF varchar(200) = concat(@dataFolder,@restoreDatabaseName,'.mdf')
 , @sourceLogFile varchar(200) = concat(@sourceDatabaseName,'_Log')
 , @restoreLog varchar(200) = concat(@dataFolder,@restoreDatabaseName, '_log.ldf')

 select 
   @backupfile [backupfile]
   , @dataFolder [dataFolder]
 , @sourceLogFile [sourceLogFile]
 , @restoreDatabaseName [restoreDBname]
   , @restoreMDF [restoreMDF]
 , @restoreLog [restorelog]
 

 declare @crlf varchar(10) = CHAR(13) + CHAR(10)
 declare @sql varchar(1000) = 
concat('ALTER DATABASE [',@restoreDatabaseName, '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;', @crlf
,'RESTORE DATABASE [',@restoreDatabaseName, '] FROM  DISK = ''',@backupfile,''' WITH  FILE = 1 ', @crlf  
,',MOVE ''',@sourceDatabaseName, ''' TO ''',@restoreMDF,'''', @crlf
,',MOVE ''',@sourceLogFile,''' TO ''', @restoreLog, '''',  @crlf
,',  NOUNLOAD,  REPLACE,  STATS = 5', @crlf
,'ALTER DATABASE [circupool_testDWH] SET MULTI_USER', @crlf

)
print @sql 
exec( @sql )

Answer 3

只需在“源”产品上查询 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;

Answer 4

我有一个方便的脚本，当我将最近的备份从目录还原到要还原到的数据库时，我会使用该脚本。它非常适合通过夜间工作来刷新开发或测试箱！

/******************************************************
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 

Answer 5

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