Re: Restoring from MDF and LDF
From: Brad (me_at_privacy.net)
Date: 07/16/04
- Next message: tcicraig: "Re: transaction log not backing up"
- Previous message: jcassidy_at_-NOSPAM-nospam.msa.com: "Migrating Server to AD"
- In reply to: Hari Prasad: "Re: Restoring from MDF and LDF"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 16 Jul 2004 10:04:58 -0400
Why not do the following:
sp_configure 'allow updates',1
reconfigure with override
go
update sysdatabases
set status = 32768
where name = 'BADDBNAME'
go
dbcc rebuild_log('BADDBNAME','C:\NewLogFile.ldf')
go
update sysdatabases
set status = 0
where name = 'BADDBNAME'
go
sp_configure 'allow updates',0
reconfigure with override
Then it will create a new log file and it should be usable.
Just know that rebuild_log is not supported by Microsoft although I have
had it recommeneded to me by MSPSS in the past to solve corruption issues.
They make you sign a waiver before actually recommending it.
In article <udckLvtaEHA.3596@tk2msftngp13.phx.gbl>,
hari_prasad_k@hotmail.com said...
> Copy the original MDF and LDF to a safe location and try attaching the
> database only with MDF file. This will not work if you have more
> than 1 MDF and LDF files.
>
> EXEC sp_attach_single_file_db @dbname = 'pubs', @physname =
> 'c:\MSSQL\Data\pubs.mdf'
>
>
> If the above fail then try:- ( If you have a backup for the database then
> use that file to restore)
>
>
> 1. Start database in emergency mode
>
> Setting the database status to emergency mode tells SQL Server to skip
> automatic recovery and lets you access the data.
> To get your data, use this script:
>
> Sp_configure "allow updates", 1
> go
> Reconfigure with override
>
> GO
>
> Update sysdatabases set status = 32768 where name = "BadDbName"
> go
> Sp_configure "allow updates", 0
> go
> Reconfigure with override
> GO
>
> You might be able to use bulk copy program (bcp), simple SELECT commands, or
> use DTS to extract
> your data while the database is in emergency mode. After this database will
> be usable with out transaction log. AFter this
> create a new database and use DTS to transfer objects and data
>
> Thanks
> Hari
> MCDBA
>
> "JK" <JK@discussions.microsoft.com> wrote in message
> news:1A30B41D-8840-4837-BE9C-588A3373C87B@microsoft.com...
> > I tried this too then it gives error:
> > An error occurred while processing the log for database 'name'
> > Connection broken
> >
> > "Narayana Vyas Kondreddi" wrote:
> >
> > > Can you somehow add a D: drive, place the files in the right path and
> try
> > > attaching again.
> > >
> > > Or else, find another SQL Server with D: drive and try attaching these
> > > databases to that server.
> > >
> > > Note that, if you haven't detached these databases previously, you may
> not
> > > be able to attach them successfully.
> > > --
> > > HTH,
> > > Vyas, MVP (SQL Server)
> > > http://vyaskn.tripod.com/
> > > Is .NET important for a database professional?
> > > http://vyaskn.tripod.com/poll.htm
> > >
> > >
> > > "JK" <JK@discussions.microsoft.com> wrote in message
> > > news:C5B5AEC5-94AC-4F62-9592-0646D625F151@microsoft.com...
> > > Hi,
> > >
> > > Our hard disk parition failed and the only thing we can recover were MDF
> and
> > > LDF files for SQL Server Database.
> > >
> > > How do we recover the database from these files?
> > >
> > > When we try using sp_attach_db we get this error :
> > > Could not open new database 'name'. CREATE DATABASE is aborted.
> > > Device activation error. The physical file name
> 'D:\MSSQL\data\name_Log.LDF'
> > > may be incorrect.
> > >
> > > We do not have D:\ drive any more everything is in C:\ now.
- Next message: tcicraig: "Re: transaction log not backing up"
- Previous message: jcassidy_at_-NOSPAM-nospam.msa.com: "Migrating Server to AD"
- In reply to: Hari Prasad: "Re: Restoring from MDF and LDF"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
