Attaching an mdf file that was not properly detached...
pmcc
I have some xxxx.mdf files recovered from a sql7 machine that was shut down
and failed to restart (the disk failed and the mdf files were subsequently
recovered). So these mdf files were NOT detached with sp_detach.
Is there some way to attach these files even though they were not properly
detached?
Thanks,
Paul McCarthy
Alvin Zhao[MS]
Do you have the log file(.ldf) related to the .mdf file? If so, you can use
sp_datach_db 'dbname', 'xxxx.mdf', 'xxxx.ldf' to attach the database to the
server and put them in a consistent state.
If you lost the log file, you can use sp_attach_single_file_db 'dbname',
'xxxx.mdf' to attach the database to the server, but the consistency can't
be guaranteed. sp_attach_single_file_db will attempt to recreate the .ldf
file in its original location,
After the attachment of the database, you can use DBCC CHECKDB to check the
database integrity.
Sincerely,
Alvin Zhao
Microsoft Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
pmcc
Thanks for the reply.
However, I'm getting an error message when running:
exec sp_attach_single_file_db 'xxxx', 'c:\mssql7\data\xxxx.mdf'
Server: Msg 945, Level 14, State 2, Line 1
Database 'xxxx' cannot be opened because some of the files could not be
activated.
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'xxxx'. CREATE DATABASE is aborted.
There is a difference between the original server and the current server:
Originally, on the machine that shut donwn and never restarted, the xxxx.mdf
and xxxx.ldf were on an F raid drive.
The new machine, onto which I am running the sp_attach, only has a c drive.
Are you saying, below, that it's trying to create the new log file on the f
drive??
Thanks again,
Paul McCarthy
"Alvin Zhao[MS]" <alv...@online.microsoft.com> wrote in message
news:5ChtBHcdCHA.1064@cpmsftngxa08...
Alvin Zhao[MS]
The system stored procedure sp_attach_single_file_db is not intended to be
used with databases that have multiple log files. When you try to use
sp_attach_single_file_db to attach the database with multiple log files,
error 945 may be raised.
Since you still have the log file, you can copy all the data files and log
files out and use sp_attach_db to attach the database to a new server.
pmcc
Unfortunalely, I didn't recover the log file. Just the mdf file. But this
database only ever had ONE log file. Are you saying that the 945 error
indicates that SQLserver somehow thinks that there were more than one log
file?
Two other facts that may be relevant:
1) the size of the mdf file is 21GB. The [lost] log file was approx 10GB.
2) several months ago I had moved the database by detaching and attaching.
When re-attaching, I ran sp_attach_single_file_db because I wanted to get
rid of the large log file. (does that mean that the mdf file has some
internal memory of more than one log file?)
Thanks and regards,
Paul
"Alvin Zhao[MS]" <alv...@online.microsoft.com> wrote in message
news:SP6xYykdCHA.2224@cpmsftngxa08...
Alvin Zhao[MS]
Actually, system stored procedure sp_attach_single_file_db will create a
new log file if the previous log file doesn't exist and the database will
take the new log file as its log file. So the operation of detaching and
reattaching using sp_attach_single_file_db is the cause of the problem.
If you can't attach the database to a new server, you can copy the data
from the database to another database. To do this, please follow the steps
below:
1.Create a blank database with the same name,logical names, physical names
and sizes for the files.
2.Stop SQL Server and replace the blank mdf with the original ones.
3.Start SQL Server, the database is going to report problems and be marked
suspect.
4.Set the database into emergency mode
To set the database into emergency mode, follow the steps below:
a. Change the database context to Master and allow updates to system
tables:
Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go
b. Set the database in Emergency (bypass recovery) mode:
select * from sysdatabases where name = '<db_name>'
-- note the value of the status column for later use
begin tran
update sysdatabases set status = 32768 where name =
'<db_name>'
-- Verify one row is updated before committing
commit tran
5. Stop and restart SQL server.
6. Copy the data from the source database to another database. You can use
DTS to do that.
7. Drop the source database
In this way, you can copy the data in the original database out to another
database and use the database instead.
pmcc
Thank you! Your procedure below worked. I have the database in Emergency
Mode and I'm in the process of moving out the tables.
Thanks again.
Sincerely,
Paul McCarthy
"Alvin Zhao[MS]" <alv...@online.microsoft.com> wrote in message
news:6rxFuLOeCHA.1896@cpmsftngxa09...
Abhijeet
I tried the all the steps as mentioned by you but now my database is
in "Suspect\Emergency mode". I think my case is bit different.
My database initially gave an error saying disk size is full, so I
shrinked it and tried to delete some old records. But the problem
increased and I had to detach the database. The size of .mdf is 67 GB
and .LDF is 5.7 GB. Since, I could not restore it on the same machine,
I managed to copy it on another disk which has an extra 25 GB free
space.
Following are the details of my attempts to attach this db
-------
EXEC sp_attach_db @dbname = N'AdSrvDBNew',
@filename1 = N'F:\DB\ADSrvDBNew.mdf',
@filename2 = N'F:\DB\ADSrvDBNew_log.ldf'
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'm_LogBackup' with unique
index 'IX_m_LogBackup_2'.
Server: Msg 3314, Level 21, State 3, Line 1
Error while undoing logged operation in database 'AdSrvDBNew'. Error
at log record ID (140893:11311:115).
Connection Broken
-----------------
EXEC sp_attach_single_file_db @dbname = 'AdSrvDBNew',
@physname = 'F:\DB\ADSrvDBNew.mdf'
Server: Msg 5173, Level 16, State 1, Line 1
Cannot associate files with different databases.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'AdSrvDBNew'. CREATE DATABASE is aborted.
Log file 'e:\Program Files\Microsoft SQL
Server\MSSQL\data\ADSrvDBNew_log.ldf' does not match the primary file.
It may be from a different database or the log may have been rebuilt
previously.
---------------------------------------
Pls let me know if there is any way out to restore this database.
Thanking you and all others in anticipation.
Abhijeet
alv...@online.microsoft.com (Alvin Zhao[MS]) wrote in message news:<6rxFuLOeCHA.1896@cpmsftngxa09>...
Alvin Zhao[MS]
Please follow the steps below to check if the database is in an consistent
status:
1.Set the database into emergency mode
To set the database into emergency mode, follow the steps below:
a. Change the database context to Master and allow updates to system
tables:
Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go
b. Set the database in Emergency (bypass recovery) mode:
select * from sysdatabases where name = '<db_name>'
-- note the value of the status column for later use
begin tran
update sysdatabases set status = 32768 where name =
'<db_name>'
-- Verify one row is updated before committing
commit tran
c. Stop and restart SQL server.
2. Run dbcc checkdb to check if database has any errors. If so, please
re-cycled server and run checkdb with repair_allow_data_loss to fix errors.
After that, you can try to reattach database again.
Sincerely,
Alvin Zhao
Microsoft Support
This posting is provided "AS IS" with no warranties, and confers no rights.
chanth...@gmail.com
I have un problem in ma sql serveur 2008 R2
Msg 5173, Level 16, State 2, Line 134
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
Can you help me ?
Thank
Chanthol