November 20, 2012 at 10:55 pm
I have only .mdf file
How to Restore .mdf file into sqlserver 2008 without .ldf file
November 20, 2012 at 10:58 pm
http://msdn.microsoft.com/en-us/library/ms174385.aspx
-----------------------------------------------------------------------------
संकेत कोकणे
November 20, 2012 at 11:05 pm
CREATE DATABASE <database name>
ON (FILENAME = 'full path to the mdf')
FOR ATTACH_REBUILD_LOG ;
GO
November 20, 2012 at 11:09 pm
I got this Error When running this syntax
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MEDICALSTORE_log.ldf" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'MEDICALSTORE'. CREATE DATABASE is aborted.
November 20, 2012 at 11:15 pm
Does the path:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA
exist on your machine?
November 20, 2012 at 11:20 pm
This path exists in my system
November 20, 2012 at 11:22 pm
Apologies if I'm belabouring this bus does the file MEDICALSTORE_log.ldf exist in that path?
November 20, 2012 at 11:23 pm
MEDICALSTORE_log.ldf does not exists in tha path
November 20, 2012 at 11:29 pm
Okay - I'm assuming that you don't have a recent backup here - because that would be the best option. Short of that you need to do a bit of hacking. Try this first:
1. Make sure that the MDF file is in a safe place. "Safe place" means NOT on the drive that you normally store your MDF files, in this case.
2. Create a new database with the same name as the database in question. Note that the logical and physical file names should match the old ones.
3. Properly detach the new database.
4. Copy (not move) the old MDF file from Step 1 over the new MDF file created in Steps 2 and 3.
5. Try to reattach the new database.
November 20, 2012 at 11:31 pm
did you try
EXEC sp_attach_single_file_db @dbname = 'MEDICALSTORE',
@physname =
N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MEDICALSTORE_Data.mdf';
-----------------------------------------------------------------------------
संकेत कोकणे
November 20, 2012 at 11:34 pm
When I execute this syntax get this error Message
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MEDICALSTORE_Data.mdf". Operating system error 2: "2(The system cannot find the file specified.)".
November 20, 2012 at 11:36 pm
repoint that path to your actual mdf file
November 20, 2012 at 11:55 pm
Attach will fail, no matter how you specify it because, as the earlier error said, the database was not cleanly shut down before the log was lost. Without a log, SQL cannot perform crash recovery and hence cannot bring the DB back to a consistent state.
Best thing here would be to restore a database backup if the log file cannot be located.
If there is no database backup, then have a look at the following blog for steps to potentially recover the DB (I say potentially, because it does not always succeed)
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
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
November 20, 2012 at 11:56 pm
matt.bowler (11/20/2012)
Okay - I'm assuming that you don't have a recent backup here - because that would be the best option. Short of that you need to do a bit of hacking. Try this first:1. Make sure that the MDF file is in a safe place. "Safe place" means NOT on the drive that you normally store your MDF files, in this case.
2. Create a new database with the same name as the database in question. Note that the logical and physical file names should match the old ones.
3. Properly detach the new database.
4. Copy (not move) the old MDF file from Step 1 over the new MDF file created in Steps 2 and 3.
5. Try to reattach the new database.
Almost, but not quite the correct steps. Doing that will result in an error saying that the log and data file don't match. Have a look at the blog post I linked.
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
November 21, 2012 at 12:21 am
Really Stupid I am ...
Thats Really Worth reading blog by Gail .
If your lucky,you will get your databases back...But anyone can realize the importance of backups from this situation .
-----------------------------------------------------------------------------
संकेत कोकणे
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply