sqlserver备份恢复命令

备份

-- A 完整备份

EXECUTE master.dbo.xp_create_subdir N'备份文件存放目录'
GO
BACKUP DATABASE [数据库名称] TO  DISK = N'备份文件完整目录+文件名.bak' WITH NOFORMAT, NOINIT,  NAME = N'备份名称', SKIP, REWIND, NOUNLOAD,  STATS = 10

-- B 差异备份
EXECUTE master.dbo.xp_create_subdir N'备份文件存放目录'
GO
BACKUP DATABASE [数据库名称] TO  DISK = N'备份文件完整目录+文件名.bak' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'备份名称', SKIP, REWIND, NOUNLOAD,  STATS = 10

-- C 事务日志备份
EXECUTE master.dbo.xp_create_subdir N'备份文件存放目录'

GO

BACKUP LOG [数据库名称] TO  DISK = N'备份文件完整目录+文件名.trn' WITH NOFORMAT, NOINIT,  NAME = N'备份名称', SKIP, REWIND, NOUNLOAD,  STATS = 10

 

恢复

-- A 数据库改为离线，回滚所有未完成的事务
ALTER DATABASE [数据库名称] SET OFFLINE WITH ROLLBACK IMMEDIATE 

 

 

-- 完整恢复 
restore database [数据库名称] from disk='备份文件完整目录+文件名.bak' 
with  FILE = 1,  
replace,
NOUNLOAD,  
STATS = 10, 
NORECOVERY    -- 没完成前必须加上此参数


-- 差异恢复  多个差异文件时，重复执行此步骤
RESTORE DATABASE [数据库名称] 
FROM  DISK = N'备份文件完整目录+文件名.bak' 
WITH  FILE = 1,  
NOUNLOAD,  
STATS = 10,
NORECOVERY     -- 没完成前必须加上此参数
  

-- 事务日志恢复  多个事务日志时，重复执行此步骤，
RESTORE DATABASE [数据库名称] 
FROM  DISK = N'备份文件完整目录+文件名.trn' 
WITH  FILE = 1,  
NOUNLOAD,  
STATS = 10
,NORECOVERY     -- 最后一个文件才能去除此参数 否则无法恢复后续备份
  

-- 查询所有数据库最近备份信息
SELECT D.name DatabaseName,  
    RestoreMode = CASE D.recovery_model_desc  
        WHEN 'SIMPLE' THEN 'Simple'  
        WHEN 'FULL' THEN 'Full'  
        ELSE 'Bulk record'  
    END,  
    ISNULL(CONVERT(varchar, BS.bdate, 120), 'Never Bakup') AS LastBakupDate,  
    BakupTypes = CASE BS.type  
        WHEN 'D' THEN 'Database'  
        WHEN 'I' THEN 'Differences'  
        WHEN 'L' THEN 'Recorde'  
        WHEN 'F' THEN 'Files or file groups'  
        WHEN 'G' THEN 'Differential file'  
        WHEN 'P' THEN 'Part'  
        WHEN 'Q' THEN 'Differences in part'  
        ELSE ''  
    END  
FROM sys.databases D LEFT JOIN    
(   
    SELECT database_name, MAX(backup_finish_date) bdate, type  
    FROM msdb.dbo.backupset  
    GROUP BY database_name, type  
) BS ON D.name = BS.database_name   
ORDER BY 1;  

-- 改为离线状态

ALTER DATABASE [数据库名称] SET OFFLINE WITH ROLLBACK IMMEDIATE

 --杀进程 

declare @dbname varchar(50)
set @dbname='数据库名称'
declare @sql varchar(50)
declare cs_result cursor local for select 'kill '+cast(spid as varchar(50)) from sys.sysprocesses where db_name(dbid)=@dbname 
open cs_result
fetch next from cs_result into @sql
while @@fetch_status=0
begin
    execute(@sql)
    fetch next from cs_result into @sql
end
close cs_result
deallocate cs_result

-- 查看备份的逻辑文件名和数据库名称

RESTORE filelistonly   FROM  DISK = N'文件完整路径'

-- 恢复数据库 

RESTORE DATABASE [数据库名] FROM  DISK = N'文件完整路径' 
WITH   MOVE N'逻辑文件名' TO N'数据数据文件名', 
 MOVE N'逻辑文件名' TO N'数据日志文件名 '
 , replace 
GO