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=e...com%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/ 8  2093 
[posted and mailed]
Charles Nadeau (ch***********@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, so****@algonet.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Erland Sommarskog wrote: [posted and mailed]
Charles Nadeau (ch***********@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 = 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 = 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
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: 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 = 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
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: 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: 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
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 = 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/default...b;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 (ch***********@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, so****@algonet.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Erland Sommarskog wrote: Charles Nadeau (ch***********@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 (ch***********@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, so****@algonet.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: rkusenet |
last post by:
Hi,
I am still not very proficient in SQLServer. So apology if the
question sounds basic.
We have a script to clean old unwanted data. It basically deletes
all rows which are more than 2...
|
by: Avanish Pandey |
last post by:
Hello All
We have 3 differen services (in 3 different server) Service A,B,C . We
want to implement distributed transaction when call methods of B and C
from A. Is it possible? if yes then how?
...
|
by: vb |
last post by:
I need to delete records from several large tables - 1 million plus
rows. Some of these tables have nested foreign key relationships.
When I perform the delete statements, it can take forever as...
|
by: MLH |
last post by:
I don't know how it happened. I have hundreds
of hours worth of work invested in a file I foolishly
named DB9.mdb. I was intending on renaming the
file soon. But I neglected to do so before...
|
by: John Lee |
last post by:
Hi,
I have few questions related to .NET 2.0 TransactionScope class behavior:
1. Check Transaction.Current.TransactionInformation.DistributedIdentifier to
identify if distributed transaction...
|
by: Oleg Lebedev |
last post by:
Is there a way to rollback a dblink transaction?
Say, I delete some data from the remote database, but I don't want this
to be visible untill the data is inserted in the current database. And
if...
|
by: ruben20 |
last post by:
Hi:
Is there any way to recover data from a corrupted table? I can only run
SELECTs on certain WHERE conditions.
I cannot vacuum, pg_dump, I've deleted the indexes and try to reindex,
always...
|
by: Sheldon |
last post by:
Someone here at work was out and her backup, apparently, erased a very
important table. While the Help Desk is trying to locate a backup
tape, I was wondering if there is a way to get the table...
|
by: A_Republican |
last post by:
I am interested in writing my own secure file deletion program. I want to be
able to read and write to my hard drive directly. My application will seach
my hard drive for all locations marked for...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |
