T-SQL备份还原SQL Server的数据库

完整备份与还原

备份数据库到指定的位置---完整备份

backup database Test1 to disk ='D:\backups\Test1.bak'

使用备份文件还原数据库

-- 设置单用户模式
alter database Test1 set single_user WITH ROLLBACK IMMEDIATE
-- 还原数据库
restore database Test1 from disk='D:\backups\Test1.bak' with replace
-- 设置多用户模式
alter database Test1 set multi_user

差异备份与还原

差异备份

backup database Test1 to disk='D:\backups\Test1_One_Dif.bak' with differential 

差异备份还原

1. 设置单用户模式

   alter database Test1 set single_user WITH ROLLBACK IMMEDIATE
   

2. 完整备份集还原

   restore database Test1 from  disk='D:\backups\Test1.bak' with replace,norecovery
   

3. 差异备份还原

   restore database Test1 from disk ='D:\backups\Test1_One_Dif.bak' with recovery
   

4. 设置多用户模式

   alter database Test1 set multi_user
   

事务日志备份与还原

事务日志备份

backup log Test1 to disk='D:\backups\Test1_log.bak' with init

备份日志尾部

backup log Test1 to disk='D:\backups\Test1_log_last.bak' with init, no_truncate

事务日志还原-----先通过完全备份集还原，再用日志备份集还原

1. 设置单用户模式

   alter database Test1 set single_user WITH ROLLBACK IMMEDIATE
   

2. 先使用完全备份集还原

   restore database Test1 from  disk='D:\backups\Test1.bak'  with  replace,norecovery 
   

3. 用日志备份文件还原

   restore database Test1 from disk='D:\backups\Test1_log.bak' with  recovery
   

4. 若通过通过尾部日志还原 还原到某个时间点（修改或删除数据之前）

   restore database Test1 from disk  ='D:\backups\Test1_log_last.bak' with recovery,STOPAT='2024-12-17 18:24:00'
   

5. 设置多用户模式

   alter database Test1 set multi_user