SQLServer 数据库还原

记录一下SQLServer数据库还原，主要讲还原

前提：数据库有备份文件

思路

• 恢复部分数据

  • 新建一个备份数据库：TargetDB
  • 把待恢复的数据库直接全量还原到TargetDB
  • 查询相关表数据，把获取到的数据更新回源数据库

• 全量恢复数据库

  • 注意当前连接用户，需只有一个连接，即当前恢复数据连接，如何处理，自行百度
  • 其他无需注意

• 为了避免SQL工具”备份集中的数据库备份与现有的数据库不同”等问题，不能相信工具，直接语句还原

SQL

-- 查询数据库备份详细信息
SELECT  database_name,recovery_model,name FROM msdb.dbo.backupset order by database_name

-- 查询Move后面的逻辑名称
RESTORE FILELISTONLY FROM DISK = N'备份文件路径_backup_2022_08_23_013007_6567487.bak'

-- 还原备份数据库到新建数据库
RESTORE DATABASE TargetDB
FROM DISK = N'备份文件路径_backup_2022_08_23_013007_6567487.bak'
with replace,
MOVE 'xxx_Data' TO 'X:\XXXData\TargetDB.mdf',
MOVE 'xxx_Log' TO 'X:\XXXData\TargetDB_log.ldf'

------------------------------------------------------------------------------------------- 
-- Most Recent Database Backup for Each Database - Detailed 
-- 查询数据库最近一次备份 以及备份详细信息 全量备份
------------------------------------------------------------------------------------------- 
SELECT  
   A.[Server], 
   A.database_name, 
   A.last_db_backup_date,  
   B.backup_start_date,  
   B.expiration_date, 
   B.backup_size,  
   B.logical_device_name,  
   B.physical_device_name,   
   B.backupset_name, 
   B.description 
FROM 
   ( 
   SELECT   
       CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
       msdb.dbo.backupset.database_name,  
       MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date 
   FROM    msdb.dbo.backupmediafamily  
       INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
   WHERE   msdb..backupset.type = 'D' 
   GROUP BY 
       msdb.dbo.backupset.database_name  
   ) AS A 
    
   LEFT JOIN  

   ( 
   SELECT   
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.expiration_date, 
   msdb.dbo.backupset.backup_size,  
   msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
WHERE  msdb..backupset.type = 'D' 
   ) AS B 
   ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date] 
ORDER BY  
   A.database_name
   
   
-- 其他
-- 查询数据当前连接
SELECT * FROM sys.[sysprocesses] WHERE DB_NAME([dbid])='数据库名称'
SELECT * FROM sys.[sysprocesses] WHERE DB_NAME([dbid])='TargetDB'

-- 删除连接
kill 138 ;

-- 设置访问权限
ALTER DATABASE [history] SET MULTI_USER