February 19, 2015 at 12:08 am
HELLO TEAM,
WE NEED URGENT HELP.
THE SCENARIO IS AS FOLLOWS:
WE HAD X-DATABASE IN SQL SERVER 2012. IN THIS DATABASE, WE HAD 3 IMPORTANT STORED PROCEDURES. UNFORTUNATELY, WE HAD NOT TAKEN ANY BACKUP OF THIS DATABASE, WHERE WE HAD CREATED 3 SPs. DUE TO CERTAIN ISSUES, WE TRIED TO RESTORE OLD X-DATABASE. SUDDENLY, WE REALIZE OUR MISTAKE AND STOPPED THE RESTORE TASK.
NOW, WE ARE LOOKING FOR OUR 3 IMPORTANT STORED PROCEDURES. IS THERE ANY WAY OR HOPE TO RECOVER THESE 3 SPs ?
ANY HELP WILL BE HIGHLY APPRECIATED.
February 19, 2015 at 12:22 am
From source control?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 19, 2015 at 12:27 am
WE TRIED TO RESTORE DATABASE FROM "SQL SERVER MANAGEMENT STUDIO (SSMS) 2012".
February 19, 2015 at 12:44 am
luhar.mitul (2/19/2015)
WE TRIED TO RESTORE DATABASE FROM "SQL SERVER MANAGEMENT STUDIO (SSMS) 2012".
NO NEED TO SHOUT.
Once again, if your database is under source control, you can retrieve the definitions there rather than having to do a restore.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 19, 2015 at 12:57 am
thanks and appreciate your quick response.
unfortunately, we are not using source control.
any other work around ?
February 19, 2015 at 1:20 am
If the database file has been corrupted and you have no backups, and no one has scripted out the procs you are talking about (check with the developers), I am afraid that you have lost your work.
Is your DB server hosted locally or in the cloud? If the latter, there may be volume snapshots available somewhere.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 19, 2015 at 1:39 am
Thanks for your help and suggestions.
I believe, we will require to re-write the scripts.
February 19, 2015 at 1:41 am
Quick suggestion, you can try to search in any/all temp folders on the workstations used to develop those procedures, there is a slim chance that you might find the code in temp files generated by SSMS, otherwise I think you are FUBAR.
😎
February 19, 2015 at 5:39 am
I strongly suggest you start putting your database code into source control in the same way as you would manage your application code. If this code is "important" as you said, you should absolutely treat it as such.
"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
February 19, 2015 at 9:11 pm
Hello Friends,
Thanks a lot for your feedback and suggestions.
We were able to resolve above issue and would like to share the steps which we took to resolve it.
Scenario:-
We had X-Database, which was in suspect mode. Its log-file was corrupted. Due to which, we were not able to access database. We had 3 important Stored Procedures (SP) in it. We did not had any previous backup. We only had MDF file. Our aim was to retrieve 3 important Sps from this suspected database.
Solution:-
We took following steps:
[1] Created new log file
[2] Brought SQL Server (2012) from Suspect Mode to Emergency Mode
[3] Performed DBCC checkdb
/* Below two statement worked for us to bring SQL Server 2012 from Suspect Mode to Emergency Mode */
[1] ALTER DATABASE <X-Database> SET EMERGENCY
[2] DBCC checkdb('<X-Database>')
After executing above two statements, some how, we were able to expand X-Database and access required 3 important SPs.
Thanks again to all of you.
Regards,
Mitul
February 19, 2015 at 9:56 pm
You were lucky this time, maybe it's time to implement source control and other recognized development practices;-)
😎
February 19, 2015 at 10:06 pm
Dear Eirikur Eiriksson,
Thank you very much for your valuable suggestions !
I agree with you, its time, we should implement source control tools !
Regards,
Mitul
February 20, 2015 at 2:49 am
There are both tools and education resources on source control and it's use on this page at Redgate Software[/url].
"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
February 20, 2015 at 6:45 am
By all means, get the database (and all others) on a backup schedule. You might not be as lucky next time and having reliable backups is important. After you get your backup schedule defined and in place, it is important that you test the backups to make sure they're viable. A backup is useless if you can't restore the database from it. The way to verify this is to actually do database restores (to a different database name) and make sure the restored copy works. Be sure to test full recovery and point-in-time restores.
The other think this will accomplish is it'll give you practice in doing restores. You don't want your first restore to be when you actually need it.
February 24, 2015 at 4:31 am
Were you only bothered about the 3 stored procedures in the database? Did the database not hold any data??
You better get some daily backups set up quickly.... 🙂
Rdgs, Dave.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply