Sql Server利用日志恢复数据库

Sql Server利用日志恢复数据库

要有一个完全备份+加上当前最新的日志备份才行

 

*--说明：
将主数据库中的数据变化及时反馈到备用数据库中
备用数据库的数据可以随时用于查询，但不能被更新（备用数据库只读）。
--*/

--首先,创建一个演示用的数据库(主数据库)

1. CREATE DATABASE Db_test  
2. ON  
3. ( NAME = Db_test_DATA,  
4.       FILENAME = 'c:/Db_test.mdf' )  
5. LOG ON  
6. ( NAME = Db_test_LOG,  
7.    FILENAME = 'c:/Db_test.ldf')  
8. GO  

--对数据库进行备份

1. BACKUP DATABASE Db_test TO DISK='c:/test_data.bak' WITH FORMAT  
2. GO  

--把数据库还原成备用数据库(演示主数据库与这个备用数据库之间的同步)

1. RESTORE DATABASE Db_test_bak FROM DISK='c:/test_data.bak'  
2. WITH REPLACE,STANDBY='c:/db_test_bak.ldf'  
3. ,MOVE 'Db_test_DATA' TO 'c:/Db_test_data.mdf'  
4. ,MOVE 'Db_test_LOG' TO 'c:/Db_test_log.ldf'  
5. GO  

--启动 SQL Agent 服务

1. EXEC master..xp_cmdshell 'net start sqlserveragent',no_output  
2. GO  

--创建主服务器数据训与备用服务器数据库之间同步的作业

1. DECLARE @jogid uniqueidentifier  
2. EXEC msdb..sp_add_job  
3. @job_id = @jogid OUTPUT,  
4. @job_name = N'数据同步处理'  

--创建同步处理步骤

1. EXEC msdb..sp_add_jobstep  
2. @job_id = @jogid,  
3. @step_name = N'数据同步',  
4. @subsystem = 'TSQL',  
5. @command = N'  

--主数据库中进行日志备份

1. BACKUP LOG Db_test TO DISK=''c:/test_log.bak'' WITH FORMAT  

--备用数据库中还原主数据库的日志备份(应用主数据库中的最新变化
--实际应该时主数据库备份与备用数据库的还原作业应该分别在主服务器和备用服务器上建立，并且备份文件应该放在主服务器和备用都能访问的共享目录中

1. RESTORE LOG Db_test_bak FROM DISK=''c:/test_log.bak'' WITH STANDBY=''c:/test_log.ldf''',  
2. @retry_attempts = 5,  
3. @retry_interval = 5  

--创建调度(每分钟执行一次)

1. EXEC msdb..sp_add_jobschedule  
2. @job_id = @jogid,  
3. @name = N'时间安排',  
4. @freq_type=4,  
5. @freq_interval=1,  
6. @freq_subday_type=0x4,  
7. @freq_subday_interval=1,  
8. @freq_recurrence_factor=1  

-- 添加目标服务器

1. EXEC msdb.dbo.sp_add_jobserver  
2. @job_id = @jogid,  
3. @server_name = N'(local)'  
4. GO  

--通过上述处理,主数据库与备用数据库之间的同步关系已经设置完成

下面开始测试是否能实现同步

 

--在主数据库中创建一个测试用的表

1. CREATE TABLE Db_test.dbo.TB_test(ID int)  
2. GO  

--等待1分钟30秒（由于同步的时间间隔设置为1分钟,所以要延时才能看到效果）

1. WAITFOR DELAY '00:01:30'  
2. GO  

--查询一下备用数据库,看看同步是否成功

1. SELECT * FROM Db_test_bak.dbo.TB_test  

/*--结果:
ID         
-----------

 

（所影响的行数为 0 行）
--*/

--测试成功
GO

--最后删除所有的测试

1. DROP DATABASE Db_test,Db_test_bak  
2. EXEC msdb..sp_delete_job @job_name=N'数据同步处理'  
3. GO  

/*===========================================================*/

 

/*--服务器档机处理说明
使用这种方式建立的数据库同步，当主数据库不可用时（例如，主数据库损坏或者停机检修）
可以使用以下两种方法使备用数据库可用。
--*/

--1. 如果主数据库损坏，无法备份出最新的日志，可以直接使用下面的语句使备用数据库可读写（丢失最近一次日志还原后的所有数据）。
--RESTORE LOG Db_test_bak WITH RECOVERY

--2. 如果主数据库可以备份出最新日志，则可以使用下面的语句。
--先备份主数据库的最新的事务日志
--BACKUP LOG Db_test TO DISK=''c:/test_log.bak'' WITH FORMAT
--再在备用数据库中恢复最新的事务日志，并且使备用数据库可读写（升级为主数据库）
--RESTORE LOG Db_test_bak FROM DISK='c:/test_log.bak'

 

 

简单地说:
1. 你的sql服务要使用指定的windows用户登陆, 而不能使用"本地系统帐户"
2. 用于登陆sql服务的用户要求对共享目录具有所有权限
3. 如果你的电脑没有加入到域, 还必须保证源和目标服务器的sql服务设置的登陆用户是一样的(用户名和密码都一样)

 

网络备份主要是权限设置问题, 参考下面的备份文件共享目录权限设置方法去解决目录的共享权限就可以了

下面假设是假设A服务器上的数据库备份到B服务器上的共享目录权限设置(两台服务器应该在局域网内,允许目录共享访问)::

1.机器A,B创建一个同名的windows用户,用户组设置为administrators,并设置相同的密码,做为备份文件夹文件夹的有效访问用户,操作:
我的电脑
--控制面板
--管理工具
--计算机管理
--用户和组
--右键用户
--新建用户
--建立一个隶属于administrator组的登陆windows的用户

2.在B机器器上,新建一个共享目录,做为备份文件的存放目录,操作:
我的电脑--D:/ 新建一个目录,名为: BAK
--右键这个新建的目录
--属性--共享
--选择"共享该文件夹"
--通过"权限"按纽来设置具体的用户权限,保证第一步中创建的用户具有对该文件夹的所有权限
--确定

3.设置 MSSQLSERVER 及 SQLSERVERAGENT 服务的启动用户
开始--程序--管理工具--服务
--右键 MSSQLSERVER
--属性--登陆--选择"此账户"
--输入或者选择第一步中创建的windows登录用户名
--"密码"中输入该用户的密码
--确定
--同样的方法设置 SQLSERVERAGENT

4.在A机器上完成对B机器BAK目录的映射

5.查询分析器中执行下面的语句,检验是否成功:
exec master..xp_cmdshell 'dir 映射的盘符'

6.A服务器上做备份计划

备注:创建一个新的用户只是为了让MSSQLSERVER服务的启动帐户与共享目录的有效访问同名且密码相同,这样才能通过验证(所以你也可以用其他有效的用户来代替,只需要满足用户名和密码相同,并且拥有足够的权限)

 

 

 

看这个例子

SQL code--数据还原到指定时间点的处理示例
--创建测试数据库
CREATE DATABASE Db
GO

--对数据库进行备份
BACKUP DATABASE Db TO DISK='c:/db.bak' WITH FORMAT
GO

--创建测试表
CREATE TABLE Db.dbo.TB_test(ID int)

--延时1秒钟,再进行后面的操作(这是由于SQL Server的时间精度最大为百分之三秒,不延时的话,可能会导致还原到时间点的操作失败)
WAITFOR DELAY '00:00:01'
GO

--假设我们现在误操作删除了 Db.dbo.TB_test 这个表
DROP TABLE Db.dbo.TB_test

--保存删除表的时间
SELECT dt=GETDATE() INTO #
GO

--在删除操作后,发现不应该删除表 Db.dbo.TB_test

--下面演示了如何恢复这个误删除的表 Db.dbo.TB_test

--首先,备份事务日志(使用事务日志才能还原到指定的时间点)
BACKUP LOG Db TO DISK='c:/db_log.bak' WITH FORMAT
GO

--接下来,我们要先还原完全备份(还原日志必须在还原完全备份的基础上进行)
RESTORE DATABASE Db FROM DISK='c:/db.bak' WITH REPLACE,NORECOVERY
GO

--将事务日志还原到删除操作前（这里的时间对应上面的删除时间，并比删除时间略早
DECLARE @dt datetime
SELECT @dt=DATEADD(ms,-20,dt) FROM # --获取比表被删除的时间略早的时间
RESTORE LOG Db FROM DISK='c:/db_log.bak' WITH RECOVERY,STOPAT=@dt
GO

--查询一下,看表是否恢复
SELECT * FROM Db.dbo.TB_test

/*--结果:
ID         
-----------

（所影响的行数为 0 行）
--*/

--测试成功
GO

--最后删除我们做的测试环境
DROP DATABASE Db
DROP TABLE #

如何利用日志还原功能?