Deleted my log file

I inadvertently deleted my log file, but still have my data file...is there anyway to restore the database from the data file only?
Thanks
See if this helps:
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Ooops" <anonymous@.discussions.microsoft.com> wrote in message
news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
> I inadvertently deleted my log file, but still have my data file...is
there anyway to restore the database from the data file only?
> Thanks
|||You can try sp_attach_single_file_db (see Books Online).
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ooops" <anonymous@.discussions.microsoft.com> wrote in message
news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
>I inadvertently deleted my log file, but still have my data file...is there
>anyway to restore the database from the data file only?
> Thanks
|||Neither one of these methods is working for me. Any ideas why not?
The second one (which is much more simple) looks like this:
EXEC sp_attach_single_file_db @.dbname = imBART,
@.physname = 'D:\MSSQL\Data\imBART_Data.MDF'
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'imBART'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'D:\MSSQL\Data\imBART_Log.LDF' may be incorrect.
|||Hi
Have you tried to detach first the database and then run the Aaron's
solution?
"Oooops" <anonymous@.discussions.microsoft.com> wrote in message
news:DC865DE5-834B-4D4D-8D4E-3D68D0DF4478@.microsoft.com...
> Neither one of these methods is working for me. Any ideas why not?
> The second one (which is much more simple) looks like this:
> EXEC sp_attach_single_file_db @.dbname = imBART,
> @.physname = 'D:\MSSQL\Data\imBART_Data.MDF'
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'imBART'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
'D:\MSSQL\Data\imBART_Log.LDF' may be incorrect.
>
|||Hi,
Follow the steps defined below to recover the database:-
It seems your old database have mutiple LDF files. If you have mutilple LDF
files missed out you will not be able to use
the procedure sp_attach_single_file_db to attach the MDF file alone.
A solution for this is:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. Start SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
update sysdatabases set status=32768 where name ='dbname'
6. Restart sql server. now the database will be in emergency mode
7. Now execute the undocumented DBCC to create a log file
DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')
(replace the dbname and log file name based on ur requirement)
8. Execute sp_resetstatus <dbname>
9. Restart SQL server and see the database is online.
Thanks
Hari
MCDBA
"Ooops" <anonymous@.discussions.microsoft.com> wrote in message
news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
> I inadvertently deleted my log file, but still have my data file...is
there anyway to restore the database from the data file only?
> Thanks
|||Hari
I have not played with this situation (I mean that database have more than
one log file)
If you create a new database and then copy an old mdf file to the new
database location and then issue
sp_attach_db with those two log files. Will it be worked? Don't we need to
update as you suggested sysdatabase system table?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:euJg5srKEHA.2716@.tk2msftngp13.phx.gbl...
> Hi,
> Follow the steps defined below to recover the database:-
> It seems your old database have mutiple LDF files. If you have mutilple
LDF
> files missed out you will not be able to use
> the procedure sp_attach_single_file_db to attach the MDF file alone.
> A solution for this is:
> 1. Create a new database with the same name and same MDF and LDF files
> 2. Stop sql server and rename the existing MDF to a new one and copy the
> original MDF to this location and delete the LDF files.
> 3. Start SQL Server
> 4. Now your database will be marked suspect
> 5. Update the sysdatabases to update to Emergency mode. This will not use
> LOG files in start up
> update sysdatabases set status=32768 where name ='dbname'
> 6. Restart sql server. now the database will be in emergency mode
> 7. Now execute the undocumented DBCC to create a log file
> DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')
> (replace the dbname and log file name based on ur requirement)
> 8. Execute sp_resetstatus <dbname>
> 9. Restart SQL server and see the database is online.
> Thanks
> Hari
> MCDBA
>
>
> "Ooops" <anonymous@.discussions.microsoft.com> wrote in message
> news:027BB498-70A7-4D4E-AB44-7F9D368F191F@.microsoft.com...
> there anyway to restore the database from the data file only?
>
|||Neither operation are guaranteed to work except on a database that was
properly detached. The proper way to recover from something like this is to
restore from your last good backups. Without a backup these suggestions are
pretty much all you have although I haven't seen the one I posted fail
unless the file was corrupted. What is the error you get when trying the
first suggestion? You can always contact MS PSS if this doesn't work and
it's worth more than $250.00 or so. They may have some tricks up their
sleeve to help.
Andrew J. Kelly SQL MVP
"Oooops" <anonymous@.discussions.microsoft.com> wrote in message
news:DC865DE5-834B-4D4D-8D4E-3D68D0DF4478@.microsoft.com...
> Neither one of these methods is working for me. Any ideas why not?
> The second one (which is much more simple) looks like this:
> EXEC sp_attach_single_file_db @.dbname = imBART,
> @.physname = 'D:\MSSQL\Data\imBART_Data.MDF'
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'imBART'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
'D:\MSSQL\Data\imBART_Log.LDF' may be incorrect.
>
|||Hi Uri,
If we have more than 1 LDF file we will not be able to use
"sp_attach_single_file_db ". I have faced similar issue sime time back,
Myclient send only
1 MDF file not the LDF files. During that situation, I did the below 9 steps
to bring up the database.
If you create a new database and then copy an old mdf file to the new
database location and then issue sp_attach_db with those two log files.
Will it be worked?
I feel this may not work because of the log seqence number (LSN) change. The
LSN in the original file will be diffrent from the one created newly.
The DBCC REBULD_LOG command (mentioned earlier) will create a entirely new
log file with new LSN.
Thanks for the suggestion. I will try it out and get back tomorrow.
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#ukolLsKEHA.624@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Hari
> I have not played with this situation (I mean that database have more than
> one log file)
> If you create a new database and then copy an old mdf file to the new
> database location and then issue
> sp_attach_db with those two log files. Will it be worked? Don't we need to
> update as you suggested sysdatabase system table?
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:euJg5srKEHA.2716@.tk2msftngp13.phx.gbl...
> LDF
use
>
|||I don't have the error code right now, but I think that it was similar to the other error code about device activation...I would guess that the .mdf file is corrupted.
I shall go back to my prior back-up and "Sin no more" ;-)
...a beginners mistake.
Thanks to everyone for your help.