February 9, 2008 at 6:17 pm
I have posted this issue with a little different scenario script. Please read the following and let me know if you have any question(s).
This is so important to my business that is holding up the production.
I really appreciate your professional assistance in my issue.
thx
Here it goes:
server A = production
server B = test/development environment
server A was a VM and it was destroyed, therefore the SQL engine is gone.
The databases were on:
>>> drive XXX for data
>>> drive YYY for logs
The two above drives are NAS
YYY got destroyed too... go figure... :w00t:
Therefore the only thing is left to do is to figure out if the MDF files can be restored in the test / development environment.
make sense?
Again, thank you for any help in advance!
John Esraelo
Cheers,
John Esraelo
February 10, 2008 at 12:56 am
I cannot see anything significantly different from your other post here http://www.sqlservercentral.com/Forums/Topic453385-149-1.aspx. The answer there, sp_attach_single_file_db, is the way to go. And remember, backup, backup, backup 🙂 And a NAS is not always a good idea for MS SQL databases....
February 10, 2008 at 9:06 am
If it's that important, call PSS. They'll walk you through the process.
February 10, 2008 at 12:28 pm
That's a good idea. I took over this job not too long ago and working with the CIO in placing strict policies and procedures in place.
Not having the adequate backup and/or the nas whether it is a good idea for such activities and functions or not; these are the things that I have to go through and start cleaning up.
Thank you for your reply.
Cheers,
John Esraelo
February 10, 2008 at 12:33 pm
tried sp_attach_single_file_db and still did not work and honestly I wasn't expecting that work anyway, since the the DB was not DETACHED to begin with..
thank you for your help though..
I will have to find professionals who can perform the resurrection and by spending couple of thousands of dollars (if that is the route we should take) a lesson will be learned.
thx
John Esraelo
Cheers,
John Esraelo
February 11, 2008 at 9:33 am
It shouldnt matter whether the database was detached to begin with. Do you see the database in Management Studio (EM)? If so get rid of it then try to attach. What is the error when trying to attach? Maybe investigating that error will help determine why the sp_attach_single_file_db didn't work. Was SQL Server reinstalled prior to trying the reattach? Maybe SQL needs to be reinstalled first. If you do this save the logins in a file and readd them after the reinstall.
Francis
February 11, 2008 at 9:49 am
Thank you for the reply.
We are taking a different approach on this issue at work.
However, unfortunately I don't have the error message right now but it had something to do with the LDF. It was persistingly asking for the LDF.
I have tried several different options that folks have recommended here and other forums and none of them were effective.
The bottom line was that I wanted to restore/connect/attach the MDF on a totally different server and that was not happening.
Thank you again for assisting me in this, I guess live and learn...
🙂
John Esraelo
Cheers,
John Esraelo
February 11, 2008 at 12:17 pm
try the create database for attach_rebuild_log command this worked out fine for me as did they dbcc rebuild_log in the past
February 11, 2008 at 12:20 pm
I don't think the dbcc rebuild_log is a sql server 2005 command. I think that is one of the dbcc commands in 7.0 or 2000 version.
I could be wrong though.
John Esraelo
Cheers,
John Esraelo
February 11, 2008 at 12:57 pm
yes they are commands for the old version replaced by the attach_rebuild_log. But anyways I missread the post since it's asking to restore the mdf not the ldf file. 🙁
February 12, 2008 at 3:16 am
You'll need to use sp_attach_db rather than sp_attach_single_file_db, the latter only works when all files other than the .mdf are in the same location they were when the db was last up and running.
In contrast, sp_attach_db allows you to respecify the FQ path location of each file (up to 16 files.)
Example:
EXEC sp_attach_db 'mydb', 'D:\Data\mydb_data.mdf', 'E:\Logs\mydb_log.ldf'
Internally, for the example above, sp_attach_db merely builds this statement and executes it:
CREATE DATABASE [mydb] ON
(FILENAME = 'D:\Data\mydb_data.mdf')
,(FILENAME = 'E:\Logs\mydb_log.ldf')
FOR ATTACH
I was recently forced to delve into the workings of sp_attach_db, when I needed to attach a db with 17 files (in 6 filegroups) to a different server. The rather arbitrary 16 filename parameter limit became a show-stopper... momentarily. (I adapted a copy of it's code, to make it accept more parameters.)
-Mark McGinty
[font="Comic Sans MS"]The Black Knight ALWAYS triumphs. Have at you![/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply