September 2, 2008 at 1:57 am
Hi Everyone,
Is there any way to undelete/restore stored procedures in SQL 2k5. I know I know there should be backup but it wasn't there as we created new DB and it wasn't part of maint plan. :pinch: Anyway, we've deleted some 100+ SP's which we were working on for last 3 months and now here I am, trying to restore them without backup. Does SQL saves some history somewhere or keep it somewhere or anything. Any idea would be just great and I will be very very very grateful to a person who can help me restore this.
Thanks in advance!!
- hr_sn
September 2, 2008 at 2:19 am
There isn't an undelete function. There's no history unless you have an audit-trail or similar. There's no built-in source control of any form in SQL.
Are you sure there's no backup somewhere? (check the DB properties, the last backup date will be listed there)
There's a slim chance that you may be able to get them back using a log reader tool. Catch is, I don't know if there are any SQL 2005 versions and they are not cheap. Also, if the DB is in simple recovery mode (which since there's no backups I assume it is), then the log gets truncated regularly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 2, 2008 at 4:28 am
When I checked with my IT guy if we got backup of this database, guess what, he got backup of all databases except this and said I wasn't aware of this new database which was around for 4 months. My bad 🙁
September 2, 2008 at 4:52 am
It's probably a longshot, but maybe you can find something in the default trace files.
Look under C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG for the *.trc files. Depending on the server activity and rebootcycles they might just reach far enough back for you to find back the creation commands.
For future use you should have a look at the DDLAudit tool by Jasper Smith. I use it on all my 2005 servers and it helped me a couple of times to find when and what had been changed in a database.
[font="Verdana"]Markus Bohse[/font]
September 2, 2008 at 6:43 am
In addition to setting up backups (4 months?), you should put all your data structures and code into source control.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply