Record Locks: second

I was trying to relocate my transaction log to a bigger drive using
sp_movedevice but I made a mistake in the syntax of the second parameter
and put only the path, not the path and the file name.
Now my database is marked as "suspect" and I get an error message in my log
upon database start up saying that the log file cannot be open.

Is there a way to have MS SQL 6.5 "forget" all the logs of this database,
create new ones and restart the database? The logs contained nothing
important, I had truncated them an hour or so before I made my mistake. I
just want to make sure the data are still usable.
When I look at the devices with sp_helpdevice, I can see a log that exist
and is hopefully in pristine condition and the one that doesn't exist
anymore.
I looked in the archives of various newsgroups but couldn't find something
that correspond closely to my situation. I saw something similar but with
MS SQL 7.0
(http://groups.google.com/groups?hl=...om %26rnum%3D4)
using sp_attach_db/sp_detach_db. What would be the equivalent with version
6.5?

Thanks!

Charles

--
Charles-E. Nadeau Ph.D
http://radio.weblogs.com/0111823/[posted and mailed]

Charles Nadeau (charlesnadeau@.hotmail.com) writes:
> I was trying to relocate my transaction log to a bigger drive using
> sp_movedevice but I made a mistake in the syntax of the second parameter
> and put only the path, not the path and the file name. Now my database
> is marked as "suspect" and I get an error message in my log upon
> database start up saying that the log file cannot be open.
> Is there a way to have MS SQL 6.5 "forget" all the logs of this database,
> create new ones and restart the database? The logs contained nothing
> important, I had truncated them an hour or so before I made my mistake. I
> just want to make sure the data are still usable.
> When I look at the devices with sp_helpdevice, I can see a log that exist
> and is hopefully in pristine condition and the one that doesn't exist
> anymore.

Indeed, you should be able to get to the data. I know how to deal with
the situation in SQL2000, but I don't know if those commands work in
6.5. (It's not the same as in the link you posted; sp_attach_db is not
in 6.5.) As the hour is late, I am not going to test, but I'll ask
around with my contacts at Microsoft, to see if they know.

If the mail address is not one you monitor, please send me a mail, since
the answer I get from MS may not be suitable for a public newsgroup. (The
method for SQL2000 is very dangerous if you don't understand what you
are doing.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You can set to emergency mode
http://support.microsoft.com/defaul...kb;en-us;165918

Which should allow you to access the data.

Didn't you take a backup first?

Nigel Rivett
www.nigelrivett.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Erland Sommarskog wrote:

> [posted and mailed]
> Charles Nadeau (charlesnadeau@.hotmail.com) writes:
>> I was trying to relocate my transaction log to a bigger drive using
>> sp_movedevice but I made a mistake in the syntax of the second parameter
>> and put only the path, not the path and the file name. Now my database
>> is marked as "suspect" and I get an error message in my log upon
>> database start up saying that the log file cannot be open.
>>
>> Is there a way to have MS SQL 6.5 "forget" all the logs of this database,
>> create new ones and restart the database? The logs contained nothing
>> important, I had truncated them an hour or so before I made my mistake. I
>> just want to make sure the data are still usable.
>> When I look at the devices with sp_helpdevice, I can see a log that exist
>> and is hopefully in pristine condition and the one that doesn't exist
>> anymore.
> Indeed, you should be able to get to the data. I know how to deal with

Just a few more details:
My log files are on the f: drive:

F:\mssql\data>dir
*Volume in drive F has no label.
*Volume Serial Number is 8018-882A

*Directory of F:\mssql\data

18/05/04* 01:40p******* <DIR>********* .
18/05/04* 01:40p******* <DIR>********* ..
24/08/01* 02:14p*************** 26,522 defn_ticket.sql.sql
18/05/04* 01:40p******* <DIR>********* dump
22/09/00* 01:45p**************** 1,384 dump.bat
18/05/04* 01:39p********** 268,435,456 master.mir
10/04/03* 08:12a*********** 42,844,160 Support-Processed.ps
18/05/04* 11:47a********** 268,435,456 ticket.mir
18/05/04* 12:16p******** 1,073,741,824 Ticketlog2.DAT
18/05/04* 12:16p******** 1,073,741,824 Ticket_log.dat
18/05/04* 01:39p************ 8,388,608 Winnie.DAT
18/05/04* 01:39p*********** 25,165,824 Winnie_log.DAT
************* 12 File(s)* 2,760,781,058 bytes
************************* 4,938,989,568 bytes free

The valid log file is Ticketlog2.DAT. Ticket_log.dat is simply a copy of
Ticketlog2.DAT I renamed and put there to try to force SQL Server to read
it.

And here is the output of two commands listing the state of my devices:

select * from sysdevices
go
sp_helpdevice
go

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 16995 ms.
SQL Server Parse and Compile Time:
** cpu time = 0 ms.
low******** high******* status cntrltype name**************************
phyname******************************************* ************************************************** ***************************
mirrorname**************************************** ************************************************** ***************************
stripeset*********************

---- ---- -- --- ----------
--------------------------------------
--------------------------------------
----------

0********** 19********* 16**** 3******** diskettedumpa*****************
a:sqltable.dat************************************ ************************************************** ***************************
(null)******************************************** ************************************************** ***************************
(null)************************

0********** 19********* 16**** 4******** diskettedumpb*****************
b:sqltable.dat************************************ ************************************************** ***************************
(null)******************************************** ************************************************** ***************************
(null)************************

0********** 131071***** 739*** 0******** master************************ C
\MSSQL\DATA\MASTER.DAT**************************** ************************************************** *************************
f:\mssql\data\master.mir************************** ************************************************** ***************************
(null)************************

2130706432* 2130709503* 2***** 0******** MSDBData********************** C
\MSSQL\DATA\MSDB.DAT****************************** ************************************************** *************************
(null)******************************************** ************************************************** ***************************
(null)************************

2113929216* 2113930239* 2***** 0******** MSDBLog*********************** C
\MSSQL\DATA\MSDBLOG.DAT*************************** ************************************************** *************************
(null)******************************************** ************************************************** ***************************
(null)************************

16777216*** 16908287*** 2***** 0******** ticket************************ f
\mssql\data\ticket.mir**************************** ************************************************** *************************
(null)******************************************** ************************************************** ***************************
(null)************************

33554432*** 33832959*** 2***** 0******** Ticket_log******************** f
\mssql\data\ticket_log.dat************************ ************************************************** *************************
(null)******************************************** ************************************************** ***************************
(null)************************

0********** 0********** 16**** 2******** TicketBackUp****************** F
\mssql\BackUp\TicketBackUp.DAT******************** ************************************************** *************************
(null)******************************************** ************************************************** ***************************
(null)************************

83886080*** 84410367*** 2***** 0******** Ticketlog2******************** F
\mssql\data\Ticketlog2.DAT************************ ************************************************** *************************
(null)******************************************** ************************************************** ***************************
(null)************************

50331648*** 50335743*** 2***** 0******** Winnie************************ F
\MSSQL\DATA\Winnie.DAT**************************** ************************************************** *************************
(null)******************************************** ************************************************** ***************************
(null)************************

0********** 0********** 16**** 2******** Winnie_backup***************** f
\MSSQL\BACKUP\Winnie_backup.DAT******************* ************************************************** *************************
(null)******************************************** ************************************************** ***************************
(null)************************

67108864*** 67121151*** 2***** 0******** Winnie_log******************** F
\MSSQL\DATA\Winnie_log.DAT************************ ************************************************** *************************
(null)******************************************** ************************************************** ***************************
(null)************************

(12 row(s) affected)

Table: sysdevices* scan count 1,* logical reads: 1,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 0 ms.
SQL Server Parse and Compile Time:
** cpu time = 0 ms.
SQL Server Parse and Compile Time:
** cpu time = 70 ms.
Table: sysdevices* scan count 1,* logical reads: 1,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 261 ms.

SQL Server Execution Times:
** cpu time = 10 ms.* elapsed time = 50 ms.

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 0 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 12,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 1,* logical reads: 1,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 10 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 16,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 8,* logical reads: 8,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 1,* logical reads: 1,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 10 ms.* elapsed time = 40 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 4,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 2,* logical reads: 2,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 1,* logical reads: 1,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 10 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 4,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 2,* logical reads: 2,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 1,* logical reads: 1,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 20 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 0,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 0,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 1,* logical reads: 1,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 10 ms.* elapsed time = 20 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 4,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 2,* logical reads: 2,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 1,* logical reads: 1,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 110 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 0,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 0,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 1,* logical reads: 1,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 521 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 0,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 0,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 1,* logical reads: 1,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 20 ms.
Table: sysdevices* scan count 1,* logical reads: 1,* physical reads: 0,*
read ahead reads: 0
Table: spt_values* scan count 1,* logical reads: 2,* physical reads: 1,*
read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 10 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 2,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 12,* logical reads: 12,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 10 ms.* elapsed time = 20 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 0,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 12,* logical reads: 12,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 10 ms.* elapsed time = 20 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 2,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 12,* logical reads: 12,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 20 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 8,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 12,* logical reads: 12,* physical reads: 0,*
read ahead reads: 0
Table: spt_values* scan count 12,* logical reads: 24,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 10 ms.* elapsed time = 20 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 16,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 12,* logical reads: 12,* physical reads: 0,*
read ahead reads: 0
Table: spt_values* scan count 12,* logical reads: 24,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 20 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 16,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 12,* logical reads: 12,* physical reads: 0,*
read ahead reads: 0
Table: spt_values* scan count 1,* logical reads: 2,* physical reads: 1,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 10 ms.* elapsed time = 70 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 0,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 12,* logical reads: 12,* physical reads: 0,*
read ahead reads: 0
Table: spt_values* scan count 12,* logical reads: 24,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 10 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 0,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 12,* logical reads: 12,* physical reads: 0,*
read ahead reads: 0
Table: spt_values* scan count 12,* logical reads: 24,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 20 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 0,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 12,* logical reads: 12,* physical reads: 0,*
read ahead reads: 0
Table: spt_values* scan count 12,* logical reads: 24,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 10 ms.

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 0 ms.
device_name******************* physical_name*********************************
description*************************************** ************************************************** ************************************************** ************************************************** ************************************************** *****
status cntrltype device_number low******** high*******

----------
--------------
----------------------------------------------------------------------------
-- --- ---- ---- ----

TicketBackUp****************** F:\mssql\BackUp\TicketBackUp.DAT**************
disk, dump device******************************************** ************************************************** ************************************************** ************************************************** ********************************************
16**** 2******** 0************ 0********** 0**********

Winnie_backup***************** f:\MSSQL\BACKUP\Winnie_backup.DAT*************
disk, dump device******************************************** ************************************************** ************************************************** ************************************************** ********************************************
16**** 2******** 0************ 0********** 0**********

diskettedumpa***************** a:sqltable.dat********************************
diskette, 1.2 MB, dump device******************************************** ************************************************** ************************************************** ************************************************** ********************************
16**** 3******** 0************ 0********** 19*********

diskettedumpb***************** b:sqltable.dat********************************
diskette, 1.2 MB, dump device******************************************** ************************************************** ************************************************** ************************************************** ********************************
16**** 4******** 0************ 0********** 19*********

MSDBData********************** C:\MSSQL\DATA\MSDB.DAT************************
special, physical disk, 6 MB************************************************ ************************************************** ************************************************** ************************************************** *****************************
2***** 0******** 127********** 2130706432* 2130709503*

MSDBLog*********************** C:\MSSQL\DATA\MSDBLOG.DAT*********************
special, physical disk, 2 MB************************************************ ************************************************** ************************************************** ************************************************** *****************************
2***** 0******** 126********** 2113929216* 2113930239*

ticket************************ f:\mssql\data\ticket.mir**********************
special, physical disk, 256 MB************************************************ ************************************************** ************************************************** ************************************************** ***************************
2***** 0******** 1************ 16777216*** 16908287***

Ticket_log******************** f:\mssql\data\ticket_log.dat******************
special, physical disk, 544 MB************************************************ ************************************************** ************************************************** ************************************************** ***************************
2***** 0******** 2************ 33554432*** 33832959***

Ticketlog2******************** F:\mssql\data\Ticketlog2.DAT******************
special, physical disk, 1024 MB************************************************ ************************************************** ************************************************** ************************************************** **************************
2***** 0******** 5************ 83886080*** 84410367***

Winnie************************ F:\MSSQL\DATA\Winnie.DAT**********************
special, physical disk, 8 MB************************************************ ************************************************** ************************************************** ************************************************** *****************************
2***** 0******** 3************ 50331648*** 50335743***

Winnie_log******************** F:\MSSQL\DATA\Winnie_log.DAT******************
special, physical disk, 24 MB************************************************ ************************************************** ************************************************** ************************************************** ****************************
2***** 0******** 4************ 67108864*** 67121151***

master************************ C:\MSSQL\DATA\MASTER.DAT**********************
special, MIRROR ENABLED, mirrored on 'f:\mssql\data\master.mir', serial
writes, reads mirrored, default disk, physical disk, 256 MB************************************************ ************************************************** **************************
739*** 0******** 0************ 0********** 131071*****

(1 row(s) affected)

Table: sysdevices* scan count 12,* logical reads: 12,* physical reads: 0,*
read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0
Table: spt_values* scan count 1,* logical reads: 2,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 10 ms.* elapsed time = 10 ms.

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 0 ms.

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 20 ms.
SQL Server Parse and Compile Time:
** cpu time = 0 ms.

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 0 ms.

I hope this will shed more lights on my problem. Thanks again for your help!

Charles

> the situation in SQL2000, but I don't know if those commands work in
> 6.5. (It's not the same as in the link you posted; sp_attach_db is not
> in 6.5.) As the hour is late, I am not going to test, but I'll ask
> around with my contacts at Microsoft, to see if they know.
> If the mail address is not one you monitor, please send me a mail, since
> the answer I get from MS may not be suitable for a public newsgroup. (The
> method for SQL2000 is very dangerous if you don't understand what you
> are doing.)

--
Charles-E. Nadeau Ph.D
http://radio.weblogs.com/0111823/|||Nigel Rivett wrote:

> You can set to emergency mode
> http://support.microsoft.com/defaul...kb;en-us;165918

Using this I can backup my data and truncate the log but I don't think it
can take care of the bad information inside sysdevices. I'll take a deeper
look at it later on.

> Which should allow you to access the data.
> Didn't you take a backup first?

I backup the database daily by "dumping" them using bcp (i.e. bcp
Ticket.dbo.Item2Ticket out f:\mssql\data\dump\Item2Ticket.tbl /n /U
username /P password /S WINHKONG ).
This is my last backup:

K:\mssql\data\dump>dir
*Volume in drive K has no label.
*Volume Serial Number is 8018-882A

*Directory of K:\mssql\data\dump

18/05/04* 01:40p******* <DIR>********* .
18/05/04* 01:40p******* <DIR>********* ..
15/10/01* 04:10p*************** 29,022 all db scripts.sql.sql
17/05/04* 04:15p****************** 184 Item2Ticket.tbl
17/05/04* 04:15p*************** 15,895 LastInteraction.tbl
17/05/04* 04:15p*************** 30,647 Mission.tbl
17/05/04* 04:15p****************** 164 RSM.tbl
17/05/04* 04:15p******************* 28 Status.tbl
17/05/04* 04:15p**************** 2,059 TaskItems.tbl
17/05/04* 04:15p******************* 69 Tasks.tbl
17/05/04* 04:15p******************* 24 TaskType.tbl
17/05/04* 04:20p*********** 43,394,880 Ticket.tbl
17/05/04* 04:20p****************** 286 Topics.tbl
17/05/04* 04:20p******************* 60 Types.tbl
17/05/04* 04:20p************** 316,258 Words.tbl
17/05/04* 04:24p*********** 19,785,780 WordsInTicket.tbl
************* 16 File(s)**** 63,575,356 bytes
************************* 4,938,989,568 bytes free

> Nigel Rivett
> www.nigelrivett.net

Thanks for your help, much appreciated!

Charles

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

--
Charles-E. Nadeau Ph.D
http://radio.weblogs.com/0111823/|||Have you tried setting to emergency mode?
That should allow you to bcp all the data out and copy to another
database.

Nigel Rivett
www.nigelrivett.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Charles Nadeau (charlesnadeau@.hotmail.com) writes:
> Using this I can backup my data and truncate the log but I don't think it
> can take care of the bad information inside sysdevices. I'll take a deeper
> look at it later on.

Once you have gotten the data out, I suggest that you drop the database
and create a new one. You should not continue with the database once it
has been marked suspect.

I assume that you have your scripts on version control, but if not there are
scripting facitilies in Enterprise Manager.

> I backup the database daily by "dumping" them using bcp (i.e. bcp
> Ticket.dbo.Item2Ticket out f:\mssql\data\dump\Item2Ticket.tbl /n /U
> username /P password /S WINHKONG ).
> This is my last backup:

While that saves your data, you don't save metadata and stored procedures
this way. What's wrong with the regular DUMP command.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:

> Charles Nadeau (charlesnadeau@.hotmail.com) writes:
>> Using this I can backup my data and truncate the log but I don't think it
>> can take care of the bad information inside sysdevices. I'll take a
>> deeper look at it later on.
> Once you have gotten the data out, I suggest that you drop the database

This is what I did. Everything was smooth:
I put the database is emergency mode, bcp all the data out of it.
I cheked where my last back-up and the database scripts were.
I droped the database and its devices, then recreated the devices.
I created the database FOR LOAD.
I restored from my latest back-up (I didn't loose any data!).
I set the databse for use not only by dbo.
At this point I panicked a bit because the database was flagged as loading
even after I had put the data back into it. I restarted the server and I
was back in business. I was expecting the whole thing to be longer and more
painfull.
Thanks a lot for your help!

Charles

> and create a new one. You should not continue with the database once it
> has been marked suspect.
> I assume that you have your scripts on version control, but if not there
> are scripting facitilies in Enterprise Manager.
>> I backup the database daily by "dumping" them using bcp (i.e. bcp
>> Ticket.dbo.Item2Ticket out f:\mssql\data\dump\Item2Ticket.tbl /n /U
>> username /P password /S WINHKONG ).
>> This is my last backup:
> While that saves your data, you don't save metadata and stored procedures
> this way. What's wrong with the regular DUMP command.

--
Charles-E. Nadeau Ph.D
http://radio.weblogs.com/0111823/|||Charles Nadeau (charlesnadeau@.hotmail.com) writes:
> This is what I did. Everything was smooth:
> I put the database is emergency mode, bcp all the data out of it.
> I cheked where my last back-up and the database scripts were.
> I droped the database and its devices, then recreated the devices.
> I created the database FOR LOAD.
> I restored from my latest back-up (I didn't loose any data!).
> I set the databse for use not only by dbo.
> At this point I panicked a bit because the database was flagged as
> loading even after I had put the data back into it. I restarted the
> server and I was back in business. I was expecting the whole thing to be
> longer and more painfull.

Glad to hear that it worked out fine!

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Monday, March 26, 2012

Recovery deleted row

Friday, March 23, 2012

Recovering from transaction log deletion in 6.5

Record Locks

Blog Archive

About Me