How can I get back the lost view?

Hi,

I am a newbie working on MS Sql Server 2000 for a while. I accidentally deleted a view through Query Analyzer and want to get it back. All data are backed-up every day but there are a lot of red tapes I have to go through in order to draw the lost view from the backup. Indeed, a different division is taking care of backups in our organization and they don't want to spend time on my issue.

I'm wondering if there is an automatic logging capability of sql server showing modified/ deleted/ updated data objects on daily basis with their contents that can be accessed later on. Or is there another recovery mechanism that can be used to get back the lost view?

Thanks for your attention to this matter,

Batuhan

Hi,

as this is your first post in here, welcome to the groups :-)

Actions are logged within the tranaction log of SQL Server, but if you did not change the view (with an alter or create command) there will be no information in the log to rely on, in addition you would need to have the last backup for applying the transaction (log) to this version. i guess you will have to go the hard way and let the backup division restore the database for you to an older version. Thats why I keep a script of my database as a "small" backup to restore the object that are just scriptable Perhaps you should add this as a best practise to your daily work.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I'd like to second Jens's suggestion to keep track of the T-SQL that was used to generate any database object - think of it as the database source code. You can manage that as you manage your application code, using SourceSafe, for example.

Thanks
Laurentiu

|||

Thanks for replying my post.

I'm really curious about the content of transaction-log. Does it record every change we made in the database or just 'transactions'?
Does it cover logging of update, insert, delete operations that were executed in the database? Another question is how I can view the transaction log. Do you know any free software tool to read transaction log?

Batuhan

|||Statements are wrapped in transactions to ensure the ACID of databases. You can either use explicit transactions using BEGIN TRANSACTIONS or the appropiate functionality of the provider like the ADO.NET implementation or implicit while doing a regular DML operation which is not wrapped up in a explicit transaction. The transactionlog cannot be viewed easily, there are special (non-free tools) for viewing these like this from L**igent (you will propably find the name searching on the internet).

There is an undocumented way to read the log, but this is sort a cryptic to investigate:

DBCC Log('tempdb',1) --use the appopiate parameters to read the log

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de