操作数据库时难免会误删除一些表啊, 视图啊, 存储过程之类的东西. 那么, 怎么把它们找回来就很关键了.
我个人不是DBA, 所以对于数据库的维护知识知道的不多, 所以我就说我自己常用的一种找回方式, 那就是使用sys.fn_dblog.
While playing with database, it will inevitably encounter all sorts of mistaken deletion, like view, table, stored procedures and so on. So it's quite important to know how to get them back. As I've never played a role of DBA, and know little knowledge of DB maintenance, sys.fn_dblog will be the only solution I am gonna talk about in this article.
如果还有更好的方法, 我这里也欢迎指教, 下面进入正文.
Please favor me with your comments if you know alternatives.
Now let's roll.
------------------------------------
sys.fn_dblog在网上可以找到的资料不多, 尤其是官方资料很少. 但是它的工作原理很简单, 它是通过transaction log(事务日志)来找回删掉的object(对象)的, 也就是说万一transaction log由于维护或其它需求而已经被truncate掉了的话, 那么事情就尴尬了... 不过至少的至少, 只要你发现的及时, 就来得及.
sys.fn_dblog is a function that i would call "undocumented", especially for official documents. But its working principle is quite straightforward, it goes to Transaction-log and gets your objects back. In other words, in case your T-log has been truncated unfortunately due to some reasons, that will be absolutely awkward... But at least you can get a deleted object back as long as you realize the deletion timely.
为了先了解一下sys.fn_dblog, 你可以先运行一下下面的语句, 看看结果长什么样.
To understand sys.fn_dblog, you can execute the following query in your machine and see what results will be returned.
SELECT * FROM sys.fn_dblog(NULL, NULL);
一看结果, 天啦鲁, 一共129列... 这肯定需要更准确的查询才行
Gosh, a total of 129 columns in the result set, we need more filters..
经过排查, 发现其实我们最需要的只有一列, 它的名字叫[Rowlog Contents 0], 再加上几个where条件, 就形成了下面的这段查询.
After investigation, I found there is only one column which is essential - [Rowlog Contents 0], along with a few of filters applied, I got the following query finally.
Select
Convert(varchar(Max),Substring([RowLog Contents 0],33,LEN([RowLog Contents 0]))) as [Script]
from fn_dblog(NULL,NULL)
Where [Operation]='LOP_DELETE_ROWS' And [Context]='LCX_MARK_AS_GHOST'
And [AllocUnitName]='sys.sysobjvalues.clst'
为了验证结果, 我故意删除了一个view, 跑了上面的查询后, 结果是这样的. 你可以看到两个view的语句就摆在那里 (第一条带bkp的是我自己在备份这个view时误删的, 你看, 误删是经常的事情...).
To verify the result, I deleted a view on purpose, by running the query, you can find the result set from the following screenshot, there are two lines there, each one is the script of a view
那么, 到这里你就可以copy paste到Query窗口跑一下, view就回来了, 于是无所顾忌xjb删的日子又可以继续了.
Now the only thing you are going to do is copy the script and paste to your Query window, then run it.. Now the careless days shall go on!
