Database recovery with data file only

I had database whose log file and backup files are unavailable due to a media failure. I had the data file which is up to date.
Is it possible to recover the database?
I had tried using attach database utility but failed with an error message “Device Activation error. Physical file name “C:\...\xxx.ldf” may be incorrect.
Please advice me if anything can be done.
Thanking You
"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
exec sp_attach_single_file_db creates the ldf file for you
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
|||Hi,
If you have mutiple LDF files as well as if the database is not detached you
may not be able to use sp_attach_single_file_db command.
In this case you can follow the below procedure to bring the database up.
But few of the steps are purely undocuemnted.
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
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')
8. Execute sp_resetstatus <dbname>
9. Restart SQL server and see the database is online.
Thanks
Hari
MCDBA
"Bob Simms" <bob_simms@.somewhere.com> wrote in message
news:1vbDc.45061$ly2.28055@.doctor.cableinet.net... [vbcol=seagreen]
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> media failure. I had the data file which is up to date.
message
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
> exec sp_attach_single_file_db creates the ldf file for you
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
>
|||A word of warning regarding the technique proposed by Hari. Forcibly
rebuilding the transaction log results in a database with questionable
integrity. Data may be physically corrupt or logically inconsistent because
normal database recovery did not take place.
A preferable method is to restore from backup. If the log must be rebuilt
because no backup is available, I suggest data be exported and then imported
into a clean database.
Hope this helps.
Dan Guzman
SQL Server MVP
Hope this helps.
Dan Guzman
SQL Server MVP
"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
>
|||A word of warning regarding the technique proposed by Hari. Forcibly
rebuilding the transaction log results in a database with questionable
integrity. Data may be physically corrupt or logically inconsistent because
normal database recovery did not take place.
A preferable method is to restore from backup. If the log must be rebuilt
because no backup is available, I suggest data be exported and then imported
into a clean database.
Hope this helps.
Dan Guzman
SQL Server MVP
Hope this helps.
Dan Guzman
SQL Server MVP
"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
>
|||Hi Dan,
I accept what you say regarding data integrity.
I suggested /recommended this method only because ken do not have the
database Backup as well as
no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
approach. After bringing
up the database ken can execute a DBCC CHECKDB and confirm that database is
fine or not.
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> A word of warning regarding the technique proposed by Hari. Forcibly
> rebuilding the transaction log results in a database with questionable
> integrity. Data may be physically corrupt or logically inconsistent
because
> normal database recovery did not take place.
> A preferable method is to restore from backup. If the log must be rebuilt
> because no backup is available, I suggest data be exported and then
imported[vbcol=seagreen]
> into a clean database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> media failure. I had the data file which is up to date.
message
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
>
|||Hi Dan,
I accept what you say regarding data integrity.
I suggested /recommended this method only because ken do not have the
database Backup as well as
no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
approach. After bringing
up the database ken can execute a DBCC CHECKDB and confirm that database is
fine or not.
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> A word of warning regarding the technique proposed by Hari. Forcibly
> rebuilding the transaction log results in a database with questionable
> integrity. Data may be physically corrupt or logically inconsistent
because
> normal database recovery did not take place.
> A preferable method is to restore from backup. If the log must be rebuilt
> because no backup is available, I suggest data be exported and then
imported[vbcol=seagreen]
> into a clean database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> media failure. I had the data file which is up to date.
message
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
>
|||Although DBCC CHECKDB can detect physical corruption, there could be logical
errors as well, such as orphaned data and uncommitted data. I wanted Ken to
fully understand the implications of rebuilding the log.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
> Hi Dan,
> I accept what you say regarding data integrity.
> I suggested /recommended this method only because ken do not have the
> database Backup as well as
> no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
> approach. After bringing
> up the database ken can execute a DBCC CHECKDB and confirm that database
is[vbcol=seagreen]
> fine or not.
> --
> Thanks
> Hari
> MCDBA
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> because
rebuilt[vbcol=seagreen]
> imported
a
> message
>
|||Although DBCC CHECKDB can detect physical corruption, there could be logical
errors as well, such as orphaned data and uncommitted data. I wanted Ken to
fully understand the implications of rebuilding the log.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
> Hi Dan,
> I accept what you say regarding data integrity.
> I suggested /recommended this method only because ken do not have the
> database Backup as well as
> no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
> approach. After bringing
> up the database ken can execute a DBCC CHECKDB and confirm that database
is[vbcol=seagreen]
> fine or not.
> --
> Thanks
> Hari
> MCDBA
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> because
rebuilt[vbcol=seagreen]
> imported
a
> message
>
|||Basically using that command breaks your business logic as there's no
guarantee of any constraints (implied or explicit) being true any more.
Also, the use of the command is unsupported and its use is tracked by the
server.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:O5DUi6dXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> Although DBCC CHECKDB can detect physical corruption, there could be
logical
> errors as well, such as orphaned data and uncommitted data. I wanted Ken
to[vbcol=seagreen]
> fully understand the implications of rebuilding the log.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
database[vbcol=seagreen]
> is
> rebuilt
to
> a
>