I need to restore a database to the state it was in six months ago. Though
backups were faithfully done, the backup files are not retained on the system
past one month. And the tape backups of the network, that do go back in
time, skipped the directories that held the bak and trn files.
What I have is an mdf file from that period. I tried doing an attach with
ATTACH_REBUILD_LOG, but it errors out because "the database was not cleanly
shut down." I'm guessing that this is because the database file was simply
copied as a physical file instead of being detached first.
In an act of desperation, I created an empty database of the same name, shut
down the server, and then copied over the old mdf file. I didn't really
expect it to work, and therefore was not disappointed.
Is there anything I can do to restore this old mdf?I saw several posts advising people to try sp_attach_single_file_db. It did
this but got the same error message that I had been getting.
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file
"D:\SysData\MSSQL\LogFiles\MCRSL_V65\MCRinc_65App_log.ldf". Operating system
error 5: "5(Access is denied.)".
File activation failure. The physical file name
"D:\SysData\MSSQL\LogFiles\MCRSL_V65\MCRinc_65App_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 'MCRinc_65App_Jun07'. CREATE DATABASE is aborted.
"Bev Kaufman" wrote:
> I need to restore a database to the state it was in six months ago. Though
> backups were faithfully done, the backup files are not retained on the system
> past one month. And the tape backups of the network, that do go back in
> time, skipped the directories that held the bak and trn files.
> What I have is an mdf file from that period. I tried doing an attach with
> ATTACH_REBUILD_LOG, but it errors out because "the database was not cleanly
> shut down." I'm guessing that this is because the database file was simply
> copied as a physical file instead of being detached first.
> In an act of desperation, I created an empty database of the same name, shut
> down the server, and then copied over the old mdf file. I didn't really
> expect it to work, and therefore was not disappointed.
> Is there anything I can do to restore this old mdf?|||Bev,
This may not work, depending on many things. But, having said that, it did
save my skin in one disaster. Please read and follow carefully:
http://www.tech-archive.net/Archive/SQL-Server/microsoft.public.sqlserver.server/2004-03/3026.html
As Jasper Smith says, this is unsupported.
RLF
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:C5E520BD-5FD1-44EE-9963-399369BABA22@.microsoft.com...
>I saw several posts advising people to try sp_attach_single_file_db. It
>did
> this but got the same error message that I had been getting.
> Msg 5120, Level 16, State 101, Line 1
> Unable to open the physical file
> "D:\SysData\MSSQL\LogFiles\MCRSL_V65\MCRinc_65App_log.ldf". Operating
> system
> error 5: "5(Access is denied.)".
> File activation failure. The physical file name
> "D:\SysData\MSSQL\LogFiles\MCRSL_V65\MCRinc_65App_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 'MCRinc_65App_Jun07'. CREATE DATABASE is
> aborted.
>
> "Bev Kaufman" wrote:
>> I need to restore a database to the state it was in six months ago.
>> Though
>> backups were faithfully done, the backup files are not retained on the
>> system
>> past one month. And the tape backups of the network, that do go back in
>> time, skipped the directories that held the bak and trn files.
>> What I have is an mdf file from that period. I tried doing an attach with
>> ATTACH_REBUILD_LOG, but it errors out because "the database was not
>> cleanly
>> shut down." I'm guessing that this is because the database file was
>> simply
>> copied as a physical file instead of being detached first.
>> In an act of desperation, I created an empty database of the same name,
>> shut
>> down the server, and then copied over the old mdf file. I didn't really
>> expect it to work, and therefore was not disappointed.
>> Is there anything I can do to restore this old mdf?|||I had to do something similar but I only thing was I couldn't get step 9 on
working. So what I did was after the database was in emergency state, I
created a new blank database. And used DTS services to transfer all objects
from original database to the new one. SQL was able to access the database
even though it had no log file; just didn't want me to make any transactions
against it where log was required I guess.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/
"Russell Fields" wrote:
> Bev,
> This may not work, depending on many things. But, having said that, it did
> save my skin in one disaster. Please read and follow carefully:
> http://www.tech-archive.net/Archive/SQL-Server/microsoft.public.sqlserver.server/2004-03/3026.html
> As Jasper Smith says, this is unsupported.
> RLF
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:C5E520BD-5FD1-44EE-9963-399369BABA22@.microsoft.com...
> >I saw several posts advising people to try sp_attach_single_file_db. It
> >did
> > this but got the same error message that I had been getting.
> >
> > Msg 5120, Level 16, State 101, Line 1
> > Unable to open the physical file
> > "D:\SysData\MSSQL\LogFiles\MCRSL_V65\MCRinc_65App_log.ldf". Operating
> > system
> > error 5: "5(Access is denied.)".
> > File activation failure. The physical file name
> > "D:\SysData\MSSQL\LogFiles\MCRSL_V65\MCRinc_65App_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 'MCRinc_65App_Jun07'. CREATE DATABASE is
> > aborted.
> >
> >
> > "Bev Kaufman" wrote:
> >
> >> I need to restore a database to the state it was in six months ago.
> >> Though
> >> backups were faithfully done, the backup files are not retained on the
> >> system
> >> past one month. And the tape backups of the network, that do go back in
> >> time, skipped the directories that held the bak and trn files.
> >>
> >> What I have is an mdf file from that period. I tried doing an attach with
> >> ATTACH_REBUILD_LOG, but it errors out because "the database was not
> >> cleanly
> >> shut down." I'm guessing that this is because the database file was
> >> simply
> >> copied as a physical file instead of being detached first.
> >>
> >> In an act of desperation, I created an empty database of the same name,
> >> shut
> >> down the server, and then copied over the old mdf file. I didn't really
> >> expect it to work, and therefore was not disappointed.
> >>
> >> Is there anything I can do to restore this old mdf?
>
>
