备份
-- 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