使用Log Explorer恢复数据http://www.ttdown.com/SoftDown.asp?ID=14562 http://js.fixdown.com/soft/8324.htm http://www.yiii.net/app/servlet/net.yiii.club.DownloadServlet?Information_Id=I00023471 解压缩密码 www.heibai.net http://www.ttdown.com/softview_8647.htm 注册机产生的是注册码,是两个 用解压缩密码解开后,压缩包里也有一个注册机的 打开log explorer file=> attach log file-> 选择服务器和登陆方式-> connect-> 选择数据库-> attach-> 左面对话框中browse-> view log-> 就可以看到log记录了 想恢复的话: 右键log记录 undo transation-> 选择保存文件名和路径-> 然后打开该文件到查询分析器里执行 T-sql代码就可以了 例如 如果log是delete table where ...的话,生成的文件代码就是insert table .... Log Explorer for SQL Server v3.3 特别版 www.chinaz.com Log Explorer is the leading transaction analysis and data recovery solution for Microsoft SQL Server. By providing unprecedented access to the SQL Server transaction log, Log Explorer gives you the ability to understand and solve elusive database problems by browsing the transaction log, exporting data to create reports, and selectively recovering modified, deleted, dropped, or truncated data. 打开log explorer file=> attach log file-> 选择服务器和登陆方式-> connect-> 选择数据库-> attach-> 左面对话框中browse-> view log-> 就可以看到log记录了 想恢复的话: 右键log记录 undo transation-> 选择保存文件名和路径-> 然后打开该文件到查询分析器里执行 T-sql代码就可以 例如 如果log是delete table where ...的话,生成的文件代码就是insert table .... 右键log记录 undo transation-> 选择保存文件名和路径-> 然后打开该文件到查询分析器里执行T-sql代码 log explorer使用的一个问题 1)对数据库做了完全 差异 和日志备份 备份时选用了删除事务日志中不活动的条目 再用Log explorer打试图看日志时 提示No log recorders found that match the filter,would you like to view unfiltered data 选择yes 就看不到刚才的记录了 如果不选用了删除事务日志中不活动的条目 再用Log explorer打试图看日志时,就能看到原来的日志 2)修改了其中一个表中的部分数据,此时用Log explorer看日志,可以作日志恢复 3)然后恢复备份,(注意:恢复是断开log explorer与数据库的连接,或连接到其他数据上, 否则会出现数据库正在使用无法恢复) 恢复完后,再打开log explorer 提示No log recorders found that match the filter,would you like to view unfiltered data 选择yes 就看不到刚才在2中修改的日志记录,所以无法做恢复. -------------------------------------------------------------------------------- 系统中有一个数据库DB1,系统正在使用的过程中,不小心删除了某表T1的数据 现我可以按照用完整备份文件加日志备份时间点TIME1恢复的方法来恢复该数据库 但问题出在该数据库一直还必须在使用中,所以除了你删除了数据的TA表外,其它的 表的数据你不能只是恢复到错误时间点TIME1处,因为,TIME1以后也有数据变化 解决这个问题有两个方法 1:用LOG EXEPLORE 可以轻松搞定 2:先建立一个数据库DB2,将完整备份文件恢复到DB2中,再用DB2中的T1数据来更新 DB1中的T1数据即可. --查看备份信息 RESTORE FILELISTONLY FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\db1_backup ' --还原旧的备份 RESTORE DATABASE db2 FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\db1_backup ' with replace,norecovery, move 'db1_Data ' to 'C:\Program Files\Microsoft SQL Server\MSSQL\data\db2_data.mdf ', move 'db1_Log ' to 'C:\Program Files\Microsoft SQL Server\MSSQL\data\db2_log.ldf ' --还原数据日志到时间点 restore log db2 from disk= 'db_log_backup ' with stopat= '2003/4/22 9:57 '--这个可用于2005 Log Explorer for SQL Server 4.2 注册码 wv5rc-uxvpz-e33-nr4694qs2 Lumigent Log Explorer for SQL Server v4.0.2 特别版下载地址 http://down.chinaz.com/soft/7887.htm
参照: DBCC TRACEON (3604) is issued before each of the following DBCC examples in order to better demonstrate the effects of the command by displaying a trace of the output of the DBCC command. It is not actually required to run the DBCC commands examined below. If you run any of the DBCC commands below without the above option, the command runs, but you don not see what it is doing. DBCC BUFFER This command can be used to display buffer headers and pages from the buffer cache. Syntax: dbcc buffer ([dbid|dbname] [,objid|objname] [,nbufs], [printopt])where: dbid|dbname - database id|database name objid|objname - object id|object name nbufs - number of buffers to examine printopt - print option, which includes: 0 - print out only the buffer header and page header (default) 1 - print out each row separately and the offset table 2 - print out each row as a whole and the offset table--This is an example: DBCC TRACEON (3604) DBCC buffer(master,'sysobjects') DBCC BYTES This command can be used to dump out bytes from a specific address. Syntax: dbcc bytes (startaddress, length)where: startaddress - starting address to dump length - number of bytes to dump--This is an example: DBCC TRACEON (3604) DBCC bytes (10000000, 100) DBCC DBINFO Displays DBINFO structure for the specified database. Syntax: DBCC DBINFO [(dbname)]where: dbname - is the database name--This is an example: DBCC TRACEON (3604) DBCC DBINFO (master) DBCC DBTABLE This command displays the contents of the DBTABLE structure. Syntax: DBCC DBTABLE ({dbid|dbname})where: dbid|dbname - database name or database ID--This is an example: DBCC TRACEON (3604) DBCC DBTABLE (master)The DBTABLE structure has an output parameter called dbt_open. This parameter keeps track of how many users are in the database.DBCC DES Prints the contents of the specified DES (descriptor). Syntax: dbcc des [([dbid|dbname] [,objid|objname])] where: dbid|dbname - database id or the database name objid|objname - object id or the object name--This is an example: DBCC TRACEON (3604) DBCC DES DBCC HELP DBCC HELP returns syntax information for the specified DBCC statement. In comparison with DBCC HELP command in version 6.5, it returns syntax information only for the documented DBCC commands. Syntax: DBCC HELP ('dbcc_statement' | @dbcc_statement_var | '?')--This is an example: DBCC TRACEON (3604) DECLARE @dbcc_stmt sysname SELECT @dbcc_stmt = 'CHECKTABLE' DBCC HELP (@dbcc_stmt) DBCC IND Shows all pages in use by indexes of the specified table. Syntax: dbcc ind(dbid|dbname, objid|objname, printopt = {-2|-1|0|1|2|3}) where: dbid|dbname - database id or the database name objid|objname - object id or the object name printopt - print optionThere is change in this command in how it is used in SQL Server 7.0, in that the printopt parameter is now no longer optional.--This is an example: DBCC TRACEON (3604) DBCC IND (master, sysobjects, 0) DBCC LOG This command is used to view the transaction log for the specified database. Syntax: DBCC log ({dbid|dbname}, [, type={-1|0|1|2|3|4}]) where: dbid or dbname - Enter either the dbid or the name of the database type - is the type of output, and includes these options: 0 - minimum information (operation, context, transaction id) 1 - more information (plus flags, tags, row length, description) 2 - very detailed information (plus object name, index name, page id, slot id) 3 - full information about each operation 4 - full information about each operation plus hexadecimal dump of the current transaction log's row.-1 - full information about each operation plus hexadecimal dump of the current transaction log's row, plus Checkpoint Begin, DB Version, Max XDESIDby default, type = 0 To view the transaction log for the master database, run the following command: DBCC log (master) DBCC PAGE You can use this command to view the data page structure. Syntax: DBCC PAGE ({dbid|dbname}, pagenum [,print option] [,cache] [,logical]) where: dbid|dbname - Enter either the dbid or the name of the database pagenum - Enter the page number of the SQL Server page that is to be examined print option - (Optional) Print option can be either 0, 1, or 2 0 - (Default) This option causes DBCC PAGE to print out only the page header information. 1 - This option causes DBCC PAGE to print out the page header information, each row of information from the page, and the page's offset table. Each of the rows printed out will be separated from each other. 2 - This option is the same as option 1, except it prints the page rows as a single block of information rather than separating the individual rows. The offset and header will also be displayed. cache - (Optional) This parameter allows either a 1 or a 0 to be entered 0 - This option causes DBCC PAGE to retrieve the page number from disk rather than checking to see if it is in cache. 1 - (Default) This option takes the page from cache if it is in cache rather than getting it from disk only. logical - (Optional) This parameter is for use if the page number that is to be retrieved is a virtual page rather then a logical page. It can be either 0 or 1. 0 - If the page is to be a virtual page number. 1 - (Default) If the page is the logical page number.--This is an example: DBCC TRACEON (3604) DBCC PAGE (master, 1, 1) DBCC PROCBUF This command displays procedure buffer headers and stored procedure headers from the procedure cache. Syntax: DBCC procbuf([dbid|dbname], [objid|objname], [nbufs], [printopt = {0|1}]) where: dbid|dbname - database id or the database name objid|objname - object id or the object name nbufs - number of buffers to print printopt - print option (0 - print out only the proc buff and proc header (default), 1 - print out proc buff, proc header, and contents of buffer)--This is an example: DBCC TRACEON (3604) DBCC procbuf(master,'sp_help',1,0) DBCC PRTIPAGE This command prints the page number pointed to by each row on the specified index page. Syntax: DBCC prtipage(dbid, objid, indexid, indexpage)where: dbid - database ID objid - object ID indexid - index ID indexpage - the logical page number of the index page to dump--This is an example: DBCC TRACEON (3604) DECLARE @dbid int, @objectid int SELECT @dbid = DB_ID('master') SELECT @objectid = object_id('sysobjects') DBCC prtipage(@dbid,@objectid,1,0) DBCC PSS This command shows info about processes currently connected to the server. Syntax: DBCC pss(suid, spid, printopt = { 1 | 0 })where: suid - server user ID spid - server process ID printopt - print option (0 standard output, 1 all open DES's and current sequence tree)--This is an example: DBCC TRACEON (3604) DBCC pss DBCC RESOURCE This command shows the server's level RESOURCE, PERFMON and DS_CONFIG information. RESOURCE shows addresses of various data structures used by the server. PERFMON structure contains master..spt_monitor field info. DS_CONFIG structure contains master..syscurconfigs field information. Syntax: DBCC resource--This is an example: DBCC TRACEON (3604) DBCC resource DBCC TAB You can use the following undocumented command to view the data pages structure (in comparison with DBCC PAGE, this command will return information about all data pages for viewed table, not only for particular number) Syntax: DBCC tab (dbid, objid) where: dbid - is the database id objid - is the table id--This is an example: DBCC TRACEON (3604) DECLARE @dbid int, @objectid int SELECT @dbid = DB_ID('master') SELECT @objectid = object_id('sysdatabases') DBCC TAB (@dbid,@objectid)
type - 输出结果的类型:
0 - 最少信息(operation, context, transaction id)
1 - 更多信息(plus flags, tags, row length)
2 - 非常详细的信息(plus object name, index name,page id, slot id)
3 - 每种操作的全部信息
4 - 每种操作的全部信息加上该事务的16进制信息
默认 type = 0
例:DBCC DBCC log (haoin,4)要查看MSATER数据库的事务日志可以用以下命令:
DBCC log (master)
但DBCC log命令作用实在有限
使用Log Explorer恢复数据http://www.ttdown.com/SoftDown.asp?ID=14562
http://js.fixdown.com/soft/8324.htm
http://www.yiii.net/app/servlet/net.yiii.club.DownloadServlet?Information_Id=I00023471
解压缩密码 www.heibai.net http://www.ttdown.com/softview_8647.htm
注册机产生的是注册码,是两个 用解压缩密码解开后,压缩包里也有一个注册机的 打开log explorer file=> attach log file-> 选择服务器和登陆方式-> connect->
选择数据库-> attach-> 左面对话框中browse-> view log-> 就可以看到log记录了
想恢复的话: 右键log记录 undo transation-> 选择保存文件名和路径-> 然后打开该文件到查询分析器里执行
T-sql代码就可以了 例如 如果log是delete table where ...的话,生成的文件代码就是insert table ....
Log Explorer for SQL Server v3.3 特别版
www.chinaz.com
Log Explorer is the leading transaction analysis and data recovery solution for Microsoft SQL Server. By providing unprecedented access to the SQL Server transaction log, Log Explorer gives you the ability to understand and solve elusive database problems by browsing the transaction log, exporting data to create reports, and selectively recovering modified, deleted, dropped, or truncated data.
打开log explorer file=> attach log file-> 选择服务器和登陆方式-> connect->
选择数据库-> attach-> 左面对话框中browse-> view log-> 就可以看到log记录了
想恢复的话: 右键log记录 undo transation-> 选择保存文件名和路径-> 然后打开该文件到查询分析器里执行
T-sql代码就可以 例如 如果log是delete table where ...的话,生成的文件代码就是insert table .... 右键log记录 undo transation-> 选择保存文件名和路径-> 然后打开该文件到查询分析器里执行T-sql代码 log explorer使用的一个问题 1)对数据库做了完全 差异 和日志备份
备份时选用了删除事务日志中不活动的条目
再用Log explorer打试图看日志时
提示No log recorders found that match the filter,would you like to view unfiltered data
选择yes 就看不到刚才的记录了
如果不选用了删除事务日志中不活动的条目
再用Log explorer打试图看日志时,就能看到原来的日志 2)修改了其中一个表中的部分数据,此时用Log explorer看日志,可以作日志恢复 3)然后恢复备份,(注意:恢复是断开log explorer与数据库的连接,或连接到其他数据上,
否则会出现数据库正在使用无法恢复)
恢复完后,再打开log explorer 提示No log recorders found that match the filter,would you like to view unfiltered data
选择yes 就看不到刚才在2中修改的日志记录,所以无法做恢复. --------------------------------------------------------------------------------
系统中有一个数据库DB1,系统正在使用的过程中,不小心删除了某表T1的数据
现我可以按照用完整备份文件加日志备份时间点TIME1恢复的方法来恢复该数据库 但问题出在该数据库一直还必须在使用中,所以除了你删除了数据的TA表外,其它的
表的数据你不能只是恢复到错误时间点TIME1处,因为,TIME1以后也有数据变化 解决这个问题有两个方法
1:用LOG EXEPLORE 可以轻松搞定 2:先建立一个数据库DB2,将完整备份文件恢复到DB2中,再用DB2中的T1数据来更新
DB1中的T1数据即可.
--查看备份信息
RESTORE FILELISTONLY FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\db1_backup ' --还原旧的备份
RESTORE DATABASE db2 FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\db1_backup ' with replace,norecovery,
move 'db1_Data ' to 'C:\Program Files\Microsoft SQL Server\MSSQL\data\db2_data.mdf ',
move 'db1_Log ' to 'C:\Program Files\Microsoft SQL Server\MSSQL\data\db2_log.ldf ' --还原数据日志到时间点
restore log db2 from disk= 'db_log_backup ' with stopat= '2003/4/22 9:57 '--这个可用于2005
Log Explorer for SQL Server 4.2 注册码
wv5rc-uxvpz-e33-nr4694qs2 Lumigent Log Explorer for SQL Server v4.0.2 特别版下载地址
http://down.chinaz.com/soft/7887.htm
DBCC TRACEON (3604)
is issued before each of the following DBCC examples in order to better demonstrate the effects of the command by displaying a trace of the output of the DBCC command.
It is not actually required to run the DBCC commands examined below.
If you run any of the DBCC commands below without the above option, the command runs, but you don not see what it is doing.
DBCC BUFFER
This command can be used to display buffer headers and pages from the buffer cache.
Syntax:
dbcc buffer ([dbid|dbname] [,objid|objname] [,nbufs], [printopt])where:
dbid|dbname - database id|database name
objid|objname - object id|object name
nbufs - number of buffers to examine
printopt - print option, which includes:
0 - print out only the buffer header and page header (default)
1 - print out each row separately and the offset table
2 - print out each row as a whole and the offset table--This is an example:
DBCC TRACEON (3604)
DBCC buffer(master,'sysobjects')
DBCC BYTES
This command can be used to dump out bytes from a specific address.
Syntax:
dbcc bytes (startaddress, length)where:
startaddress - starting address to dump
length - number of bytes to dump--This is an example:
DBCC TRACEON (3604)
DBCC bytes (10000000, 100)
DBCC DBINFO
Displays DBINFO structure for the specified database.
Syntax:
DBCC DBINFO [(dbname)]where:
dbname - is the database name--This is an example:
DBCC TRACEON (3604)
DBCC DBINFO (master)
DBCC DBTABLE
This command displays the contents of the DBTABLE structure.
Syntax:
DBCC DBTABLE ({dbid|dbname})where:
dbid|dbname - database name or database ID--This is an example:
DBCC TRACEON (3604)
DBCC DBTABLE (master)The DBTABLE structure has an output parameter called dbt_open. This parameter keeps track of how many users are in the database.DBCC DES
Prints the contents of the specified DES (descriptor).
Syntax:
dbcc des [([dbid|dbname] [,objid|objname])]
where:
dbid|dbname - database id or the database name
objid|objname - object id or the object name--This is an example:
DBCC TRACEON (3604)
DBCC DES
DBCC HELP
DBCC HELP returns syntax information for the specified DBCC statement. In comparison with DBCC HELP command in version 6.5, it returns syntax information only for the documented DBCC commands.
Syntax:
DBCC HELP ('dbcc_statement' | @dbcc_statement_var | '?')--This is an example:
DBCC TRACEON (3604)
DECLARE @dbcc_stmt sysname
SELECT @dbcc_stmt = 'CHECKTABLE'
DBCC HELP (@dbcc_stmt)
DBCC IND
Shows all pages in use by indexes of the specified table.
Syntax:
dbcc ind(dbid|dbname, objid|objname, printopt = {-2|-1|0|1|2|3})
where:
dbid|dbname - database id or the database name
objid|objname - object id or the object name
printopt - print optionThere is change in this command in how it is used in SQL Server 7.0, in that the printopt parameter is now no longer optional.--This is an example:
DBCC TRACEON (3604)
DBCC IND (master, sysobjects, 0)
DBCC LOG
This command is used to view the transaction log for the specified database.
Syntax:
DBCC log ({dbid|dbname}, [, type={-1|0|1|2|3|4}])
where:
dbid or dbname - Enter either the dbid or the name of the database
type - is the type of output, and includes these options:
0 - minimum information (operation, context, transaction id)
1 - more information (plus flags, tags, row length, description)
2 - very detailed information (plus object name, index name, page id, slot id)
3 - full information about each operation
4 - full information about each operation plus hexadecimal dump of the current transaction log's row.-1 - full information about each operation plus hexadecimal dump of the current transaction log's row, plus Checkpoint Begin, DB Version, Max XDESIDby default, type = 0
To view the transaction log for the master database, run the following command:
DBCC log (master)
DBCC PAGE
You can use this command to view the data page structure.
Syntax:
DBCC PAGE ({dbid|dbname}, pagenum [,print option] [,cache] [,logical])
where:
dbid|dbname - Enter either the dbid or the name of the database
pagenum - Enter the page number of the SQL Server page that is to be examined
print option - (Optional) Print option can be either 0, 1, or 2
0 - (Default) This option causes DBCC PAGE to print out only the page header information.
1 - This option causes DBCC PAGE to print out the page header information, each row of information from the page, and the page's offset table. Each of the rows printed out will be separated from each other.
2 - This option is the same as option 1, except it prints the page rows as a single block of information rather than separating the individual rows. The offset and header will also be displayed.
cache - (Optional) This parameter allows either a 1 or a 0 to be entered
0 - This option causes DBCC PAGE to retrieve the page number from disk rather than checking to see if it is in cache.
1 - (Default) This option takes the page from cache if it is in cache rather than getting it from disk only.
logical - (Optional) This parameter is for use if the page number that is to be retrieved is a virtual page rather then a logical page. It can be either 0 or 1.
0 - If the page is to be a virtual page number.
1 - (Default) If the page is the logical page number.--This is an example:
DBCC TRACEON (3604)
DBCC PAGE (master, 1, 1)
DBCC PROCBUF
This command displays procedure buffer headers and stored procedure headers from the procedure cache.
Syntax:
DBCC procbuf([dbid|dbname], [objid|objname], [nbufs], [printopt = {0|1}])
where:
dbid|dbname - database id or the database name
objid|objname - object id or the object name
nbufs - number of buffers to print
printopt - print option
(0 - print out only the proc buff and proc header (default),
1 - print out proc buff, proc header, and contents of buffer)--This is an example:
DBCC TRACEON (3604)
DBCC procbuf(master,'sp_help',1,0)
DBCC PRTIPAGE
This command prints the page number pointed to by each row on the specified index page.
Syntax:
DBCC prtipage(dbid, objid, indexid, indexpage)where:
dbid - database ID
objid - object ID
indexid - index ID
indexpage - the logical page number of the index page to dump--This is an example:
DBCC TRACEON (3604)
DECLARE @dbid int, @objectid int
SELECT @dbid = DB_ID('master')
SELECT @objectid = object_id('sysobjects')
DBCC prtipage(@dbid,@objectid,1,0)
DBCC PSS
This command shows info about processes currently connected to the server.
Syntax:
DBCC pss(suid, spid, printopt = { 1 | 0 })where:
suid - server user ID
spid - server process ID
printopt - print option (0 standard output, 1 all open DES's and current sequence tree)--This is an example:
DBCC TRACEON (3604)
DBCC pss
DBCC RESOURCE
This command shows the server's level RESOURCE, PERFMON and DS_CONFIG information. RESOURCE shows addresses of various data structures used by the server. PERFMON structure contains master..spt_monitor field info. DS_CONFIG structure contains master..syscurconfigs field information.
Syntax:
DBCC resource--This is an example:
DBCC TRACEON (3604)
DBCC resource
DBCC TAB
You can use the following undocumented command to view the data pages structure (in comparison with DBCC PAGE, this command will return information about all data pages for viewed table, not only for particular number)
Syntax:
DBCC tab (dbid, objid)
where:
dbid - is the database id
objid - is the table id--This is an example:
DBCC TRACEON (3604)
DECLARE @dbid int, @objectid int
SELECT @dbid = DB_ID('master')
SELECT @objectid = object_id('sysdatabases')
DBCC TAB (@dbid,@objectid)