Re: sp_attach_single_file_db
From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 06/18/04
- Next message: Tibor Karaszi: "Re: SQL 6.5 DB without a Master.dat"
- Previous message: Tibor Karaszi: "Re: samay - Between Question"
- In reply to: Sue Hoegemeier: "Re: sp_attach_single_file_db"
- Next in thread: Adam Machanic: "Re: sp_attach_single_file_db"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 18 Jun 2004 08:33:45 +0200
I totally agree with you, Sue.
Anyone worked a bit with SQL Server know that SQL Server does recovery at startup. This mean that
SQL Server go through the transaction log and "synchronizes" the database with what happened since
the last checkpoint. This mean REDO and then UNDO (rollback uncommitted transaction). Before REDO is
performed, the database is possibly in a physical inconsistent state (CHECKDB will return corruption
errors). And before UNDO is performed, the database is in logical inconsistent state (a transaction
was never committed, but the modifications were not rolled back).
So what if the transaction log file isn't available? Well, the database goes suspect, correctly so.
And this command is just like saying "let it be". The inconsistencies are still there, ad the log
file was not available, so SQL Server could not perform its REDO and UNDO operations.
-- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Sue Hoegemeier" <Sue_H@nomail.please> wrote in message news:o7a4d0hn3038gvt523jf6dcv4j5f632491@4ax.com... > 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. > >> > > >> > > >> > > >> > > >> > > >> >. > >> > > >> > > >> > > > > >> > > > > >> > > >. > >> > > > > >> > > >> > > >> > >> > > >
- Next message: Tibor Karaszi: "Re: SQL 6.5 DB without a Master.dat"
- Previous message: Tibor Karaszi: "Re: samay - Between Question"
- In reply to: Sue Hoegemeier: "Re: sp_attach_single_file_db"
- Next in thread: Adam Machanic: "Re: sp_attach_single_file_db"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
