首页 > > 网络编程 > Mssql >

日志恢复数据到时间点(总结)

2008-04-02 10:32:05来源：互联网阅读 ()

　　工作中的一点小总结希望对大家有点帮助 Powered by jiangchuandong

　　一、利用sql语句或存储过程操作

　　一种处理中用到的备份还原的存储过程

RESTORE DATABASE MyNwind
FROM MyNwind_1, MyNwind_2
WITH NORECOVERY
RESTORE LOG MyNwind
FROM MyNwindLog1
WITH NORECOVERY
RESTORE LOG MyNwind
FROM MyNwindLog2
WITH RECOVERY, STOPAT = 'Apr 15, 1998 12:00 AM'

--关闭用户进程处理
if @overexist=1 and @killuser=1
begin
declare hCForEach cursor for
select s='kill ' cast(spid as varchar) from master..sysprocesses
where dbid=db_id(@dbname)
exec sp_msforeach_worker '?'
end

　　长处:查询分析器直接操作数据库恢复数据，有良好的改装和适用性

　　缺点 :需要多次操作熟悉其过程，比较难达到企业安全统计审计需要

　　二、使用组件编程来实现

　　1、使用sqldmo.dll

　　C#语言书写的用于Microsoft SQL Server数据库备份和恢复的类:

using System;

namespace DbService
{
/// <summary>
/// DbOper类,主要实现对Microsoft SQL Server数据库的备份和恢复，注意使用了数据库时就必须杀死该进程
/// </summary>
public sealed class DbOper
{
/// <summary>
/// DbOper类的构造函数
/// </summary>
private DbOper()
{
}

/// <summary>
/// 数据库备份
/// </summary>
public static void DbBackup()
{
try
{
SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
oSQLServer.LoginSecure = false;
oSQLServer.Connect("localhost", "sa", "1234");
oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
oBackup.Database = "Northwind";
oBackup.Files = @"d:\\Northwind.bak";
oBackup.BackupSetName = "Northwind";
oBackup.BackupSetDescription = "数据库备份";
oBackup.Initialize = true;
oBackup.SQLBackup(oSQLServer);
}
catch
{
throw;
}
}

/// <summary>
/// 数据库恢复
/// </summary>
public static void DbRestore()
{
try
{
SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
oSQLServer.LoginSecure = false;
oSQLServer.Connect("localhost", "sa", "1234");
oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
oRestore.Database = "Northwind";
oRestore.Files = @"d:\\Northwind.bak";
oRestore.FileNumber = 1;
oRestore.ReplaceDatabase = true;
oRestore.SQLRestore(oSQLServer);
}
catch
{
throw;
}
}
}
}