Re: sp_attach_single_file_db

From: Sue Hoegemeier (Sue_H_at_nomail.please)
Date: 06/17/04 

Date: Thu, 17 Jun 2004 17:37:02 -0600

It's incredibly dangerous and not recommended to be used
except as a last drastic measure. When it was first posted
and written about, most sites ended up posting something
about it. Unfortunately, no one other than SQL mag followed
up with how it is dangerous, leaves your database in an
inconsistent state, etc. Unfortunately, those warnings are
being posted with the undocumented rebuild log.
Being that Rob has access to the database and can still
detach and try other alternatives, using rebuild log would
be highly inadvisable. He has other options and that's what
he should pursue.

-Sue

On Thu, 17 Jun 2004 12:07:05 -0400, "Adam Machanic"
<amachanic@hotmail._removetoemail_.com> wrote:

>Thanks, Hari... I wish I'd had that about two months ago....
>
>
>"Hari Prasad" <hari_prasad_k@hotmail.com> wrote in message
>news:OAacUHIVEHA.2360@TK2MSFTNGP10.phx.gbl...
>> Hi,
>>
>> If you need to ignore the current LDF file and if your database is not
>> detached properly and if you do not have the backup, follow
>> the below steps to bring up your database. In the below step-7 is a
>> undocumented DBCC command.
>>
>> 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
>>
>> 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
>>
>> 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') -- Undocumented step to
>create
>> a new log file.
>>
>> (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
>>
>>
>>
>>
>>
>> "Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
>> news:edD$faHVEHA.3016@tk2msftngp13.phx.gbl...
>> > Rob,
>> >
>> > Were these MDF and LDF files cleanly detached from the previous server
>> using
>> > sp_detach_db?
>> >
>> >
>> > "Rob" <anonymous@discussions.microsoft.com> wrote in message
>> > news:1dffa01c45476$0aeced30$a001280a@phx.gbl...
>> > > Thanks Adam.
>> > >
>> > > So I do that and now I get the following error message:
>> > >
>> > > Server: Msg 9003, Level 20, State 6, Line 1
>> > > The LSN (509:87:1) passed to log scan in
>> > > database 'db_tdadatamart' is invalid.
>> > >
>> > > Connection Broken
>> > >
>> > > >-----Original Message-----
>> > > >Rob,
>> > > >
>> > > >Probably better to attach all of them and then get rid of
>> > > the extras using
>> > > >ALTER DATABASE. I'm not sure whether or not attaching
>> > > without all of the
>> > > >log files might comprimise your database's integrity.
>> > > Personally, I would
>> > > >play it on the safe side.
>> > > >
>> > > >
>> > > >"Rob" <anonymous@discussions.microsoft.com> wrote in
>> > > message
>> > > >news:1dc3001c45472$20531fe0$a501280a@phx.gbl...
>> > > >> The reason why I chose to use sp_attach_single_file_db
>> > > is
>> > > >> because I want the log file to be created auto.
>> > > >>
>> > > >> I have multiple log files in the DB where it was
>> > > detached
>> > > >> and I want to have just a single log file when attached
>> > > to
>> > > >> the other server.
>> > > >>
>> > > >> Thanks.
>> > > >>
>> > > >> >-----Original Message-----
>> > > >> >Rob,
>> > > >> >
>> > > >> >You can put the MDF and LDF files wherever you want and
>> > > >> attach them with
>> > > >> >sp_attach_db. If you have the LDF file, you should not
>> > > >> use
>> > > >> >sp_attach_single_file_db (which is for MDF file only).
>> > > >> >
>> > > >> >Just place the files in whatever directory you'd like,
>> > > on
>> > > >> whatever drive
>> > > >> >you'd like, and run:
>> > > >> >
>> > > >>
>> > > >sp_attach_db 'DatabaseName', 'path_to_mdf_file', 'path_to_
>> > > >> ldf_file'
>> > > >> >
>> > > >> >
>> > > >> >"Rob" <anonymous@discussions.microsoft.com> wrote in
>> > > >> message
>> > > >> >news:1d83001c4546e$a5261a50$a301280a@phx.gbl...
>> > > >> >> Hello:
>> > > >> >>
>> > > >> >> Is there a way to specify NEW file locations when
>> > > using
>> > > >> >>
>> > > >> >> sp_attach_single_file_db
>> > > >> >>
>> > > >> >> You see the issue I have here is that, on one of my
>> > > >> server
>> > > >> >> where the DB was detached, the file locations for
>> > > >> >> both .mdf and .ldf file exists on a drive letter that
>> > > >> >> doesn't exists on the other server where I'd like to
>> > > >> have
>> > > >> >> this DB attached.
>> > > >> >>
>> > > >> >> I tried reassinging the proper drive letter and had
>> > > also
>> > > >> >> created the correct dir path, but to no avail.
>> > > >> >>
>> > > >> >> Any help would be appreciated. Thanks.
>> > > >> >
>> > > >> >
>> > > >> >.
>> > > >> >
>> > > >
>> > > >
>> > > >.
>> > > >
>> >
>> >
>>
>>
>

Relevant Pages

  • Error 1813 or "WHY didnt they call me earlier?"
    ... 3- copy the data file (mdf and ndf's) over the ones from ... 4- start sql server ... >The SQL Server 7.0 database on this server appear after ... >but I get an error telling me that ldf file and mdf file ...
    (microsoft.public.sqlserver.server)
  • Re: HELP on How to move database files
    ... http://www.support.microsoft.com/?id=314546 Moving DB's between Servers ... Issues When a Database Is Moved Between SQL Servers ... for SQL Server ... > What I want to do is to move the transaction log file to a different> location or even better get rid of it and create a new log file in its new ...
    (microsoft.public.sqlserver.server)
  • Re: SPS - SQL Server - LOG FILES.
    ... Detach the database _site. ... move the log file to another drive. ... >> We Deleted these .LDF files after stopping the SQL Server ON TEST ...
    (microsoft.public.sharepoint.portalserver)
  • Re: Transaction Log suddenly became huge and database stopped working
    ... In addition to the other posts: to understand why it was difficult for you to shrink the log, you need to understand the concept of a virtual log file and also investigate the virtual log file structure in the ... We have an SQL Server 2005 on a windows 2003 webserver ... The database is set to autoshrink and also is backed up nightly. ... Microsoft Analysis Services Client Tools 2005.090.3042.00 ...
    (microsoft.public.sqlserver.server)
  • Re: sp_attach_single_file_db
    ... Anyone worked a bit with SQL Server know that SQL Server does recovery at startup. ... SQL Server go through the transaction log and "synchronizes" the database with what happened since ... Stop sql server and rename the existing MDF to a new one and copy the ... Now execute the undocumented DBCC to create a log file ...
    (microsoft.public.sqlserver.server)
Loading