How to recover from dropped tempdb?

I am testing sql server 2005 in different disaster situations happened under 2000. I was reading a lot about no update on system tables, so:

sp_detach_db 'tempdb'

In sql 2000 i could issue something like the following command to recover:

use master

sp_configure 'allow updates', 1

reconfigure with override

go

insert sys.sysdatabases (name, dbid, sid, mode, status, status2, crdate, reserved, category, cmptlevel, filename, version)

values ('tempdb', 2, 0x01, 0, 8, 1090520064, '2007-01-27 13:03:10.873', '1900-01-01 00:00:00.000', 0, '90', 'D:\beep\mssql\temp\tempdb.mdf', 611)

go

sp_configure 'allow updates', 0

reconfigure with override

go

As tempdb is hardcoded db id 2, I do not have a chance to recover without a master backup.

Of course, this situation can be recovered using a master backup, but it is not always available.

Thank you for your comments.

How is it that you are detaching tempdb?

1> sp_detach_db tempdb
2> go
Msg 7940, Level 16, State 1:
System databases master, model, msdb, and tempdb cannot be detached.
1>

|||

Thanks for the fast response.

Yes, it is the normal behavior. But imagine the following situation:

The end-user tries to move the system databases to another disk. He reads the information on your site (http://support.microsoft.com/kb/224071/) and starts the server with /T3608. He executes the procedure step by step, moves msdb and model, forgets to read the remaining part and happy to move tempdb the same way.

This is the point, when you are getting involved. How to proceed?

|||Well, that script won'y work with SQL Server 2005. You can not allowed to make direct updates to system tables. You can't drop or detach a system database, so the only way of having an issue with tempdb is that it either becomes damaged while SQL Server is running in which case the SQL Server will go offline and fixing the issue is a matter of restarting SQL Server or you have an issue while starting up SQL Server in which case the SQL Server won't start and you would then need to go through the process documented in BOL for recreating tempdb.|||I have asked that the article be updated to remove SQL 2005 from the section that does the moves via detach. ALTER DATABASE should be used to change the file paths in these cases and there are detailed instructions in books online.|||

Thank you for your comment.

You _can_ detach the tempdb, if you start SQL Server 2005 with the /T3608 parameter [I have tried before posting the question]. From the point tempdb detached it cannot be re-attached, nor can be re-created using the way you mention, as the row with hardcoded database id "2" does not exists in the sysdatabases table.

|||

Thank you.

For closing the issue can we say, that the only way to recover from this situation is attaching a clean master db and re-attach the databases and re-create all global settings (logins, endpoints, etc.) stored in master?

|||

If you have a backup of master, you could restore that after putting in the clean master. Master is usually small enough that it should not be too big a burden to do full backups on a regular basis.

And if you had truly followed the article, the first step is

?

Make a current backup of all databases, especially master, from their current location.

which should allow you to recover.

How to recover from dropped tempdb?

I am testing sql server 2005 in different disaster situations happened under 2000. I was reading a lot about no update on system tables, so:

sp_detach_db 'tempdb'

In sql 2000 i could issue something like the following command to recover:

use master

sp_configure 'allow updates', 1

reconfigure with override

go

insert sys.sysdatabases (name, dbid, sid, mode, status, status2, crdate, reserved, category, cmptlevel, filename, version)

values ('tempdb', 2, 0x01, 0, 8, 1090520064, '2007-01-27 13:03:10.873', '1900-01-01 00:00:00.000', 0, '90', 'D:\beep\mssql\temp\tempdb.mdf', 611)

go

sp_configure 'allow updates', 0

reconfigure with override

go

As tempdb is hardcoded db id 2, I do not have a chance to recover without a master backup.

Of course, this situation can be recovered using a master backup, but it is not always available.

Thank you for your comments.

How is it that you are detaching tempdb?

1> sp_detach_db tempdb
2> go
Msg 7940, Level 16, State 1:
System databases master, model, msdb, and tempdb cannot be detached.
1>

|||

Thanks for the fast response.

Yes, it is the normal behavior. But imagine the following situation:

The end-user tries to move the system databases to another disk. He reads the information on your site (http://support.microsoft.com/kb/224071/) and starts the server with /T3608. He executes the procedure step by step, moves msdb and model, forgets to read the remaining part and happy to move tempdb the same way.

This is the point, when you are getting involved. How to proceed?

|||Well, that script won'y work with SQL Server 2005. You can not allowed to make direct updates to system tables. You can't drop or detach a system database, so the only way of having an issue with tempdb is that it either becomes damaged while SQL Server is running in which case the SQL Server will go offline and fixing the issue is a matter of restarting SQL Server or you have an issue while starting up SQL Server in which case the SQL Server won't start and you would then need to go through the process documented in BOL for recreating tempdb.|||I have asked that the article be updated to remove SQL 2005 from the section that does the moves via detach. ALTER DATABASE should be used to change the file paths in these cases and there are detailed instructions in books online.|||

Thank you for your comment.

You _can_ detach the tempdb, if you start SQL Server 2005 with the /T3608 parameter [I have tried before posting the question]. From the point tempdb detached it cannot be re-attached, nor can be re-created using the way you mention, as the row with hardcoded database id "2" does not exists in the sysdatabases table.

|||

Thank you.

For closing the issue can we say, that the only way to recover from this situation is attaching a clean master db and re-attach the databases and re-create all global settings (logins, endpoints, etc.) stored in master?

|||

If you have a backup of master, you could restore that after putting in the clean master. Master is usually small enough that it should not be too big a burden to do full backups on a regular basis.

And if you had truly followed the article, the first step is

?

Make a current backup of all databases, especially master, from their current location.

which should allow you to recover.

How to reading clusters ?

Hi,

For our trainign data clustering models show the maximum accuracy.

We want to preset the results to our client. In order to do that we want to present what are the properties of each cluster.

For example for my predictable attribute if Cluster 9 is showing maximum population then I want to show what conditions of attributes make cluster 9 .

What is the way of doing this ?

Thanks,

Vkas

There are a couple of ways. You can get the cluster description from the content by issuing a query like

SELECT NODE_DESCRIPTION FROM MyModel.CONTENT WHERE NODE_CAPTION='Cluster 9'

The node description is a description of what attributes bring a case in to a cluster. You can also use the cluster discrimination pane of the viewer to show Cluster 9 vs all other clusters to see what is important for that cluster. You can copy the contents of that view into excel and edit for presentation (copy will be enhanced in SP2 coming the first part of 2007).

Additionally, the discrimination view is availalbe as a thin client (web) sample control that ships with the product. You will need to install the product samples to access this control.

|||

Hi,

Can I create reports on mining model using Reporting Services ? Could you pl. provide me some steps for that ?

thanks,

Vikas

How to read if an index column is descending

I am reading the index keys through a query and not using sp_helpindex
because I need to make a join on the query.
How can I know if the index column is in descending order
the query i am using is the following:
select sysobjects.name as TableName
, sysindexes.name as IndexName
, sysindexkeys.keyno as Position
, syscolumns.name as FieldName
from sysindexkeys
left outer join sysobjects on sysindexkeys.id = sysobjects.id
left outer join sysindexes on sysindexkeys.id = sysindexes.id and
sysindexkeys.indid = sysindexes.indid
left outer join syscolumns on sysindexkeys.id = syscolumns.id and
sysindexkeys.colid = syscolumns.colid
order by sysobjects.name, sysindexes.name, sysindexkeys.keynoHi
Although you are not using sp_helpindex, you may have noticed that decending
keys are notated by a '(-)' next to the key columns when displaying, checkin
g
out the source for sp_helpindex you will see:
if (indexkey_property(@.objid, @.indid, 1, 'isdescending') = 1)
select @.keys = @.keys + '(-)'
Therefore this can be incorporated in your statement:
select o.name as TableName
, i.name as IndexName
, k.keyno as Position
, c.name as FieldName
, CASE WHEN indexkey_property(o.id, i.indid, 1, 'isdescending') = 1) THEN
'Descending'
ELSE 'Ascending'
END AS Direction
from sysindexkeys k
left join sysobjects o on k.id = o.id
left join sysindexes i on k.id = i.id and k.indid = i.indid
left outer join syscolumns c on k.id = c.id and k.colid = c.colid
order by o.name, i.name, k.keyno
John
"Nadim Wakim - Lebanon" wrote:

> I am reading the index keys through a query and not using sp_helpindex
> because I need to make a join on the query.
> How can I know if the index column is in descending order
>
> the query i am using is the following:
> select sysobjects.name as TableName
> , sysindexes.name as IndexName
> , sysindexkeys.keyno as Position
> , syscolumns.name as FieldName
> from sysindexkeys
> left outer join sysobjects on sysindexkeys.id = sysobjects.id
> left outer join sysindexes on sysindexkeys.id = sysindexes.id and
> sysindexkeys.indid = sysindexes.indid
> left outer join syscolumns on sysindexkeys.id = syscolumns.id and
> sysindexkeys.colid = syscolumns.colid
> order by sysobjects.name, sysindexes.name, sysindexkeys.keyno
>
>|||Dear John, although indexkey_property is working in the current db, i need
to use it to query from another db in order to compare indexes between 2
dbs.
if i use:
Select indexkey_property(o.id, i.indid, 1, 'isdescending')
from db1.dbo.sysobjects o
left outer join sysobjects current on ......
, the function returns NULL because it is looking for the id in the current
database.
if i try to use db1.dbo.indexkey_property(), i get an error.
I am trying to compare the indexes structure between a template database and
the database i am connecting to in a query.
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:9498A7E0-D40B-4D13-A1C2-751EB0B03B8F@.microsoft.com...
> Hi
> Although you are not using sp_helpindex, you may have noticed that
> decending
> keys are notated by a '(-)' next to the key columns when displaying,
> checking
> out the source for sp_helpindex you will see:
> if (indexkey_property(@.objid, @.indid, 1, 'isdescending') = 1)
> select @.keys = @.keys + '(-)'
> Therefore this can be incorporated in your statement:
> select o.name as TableName
> , i.name as IndexName
> , k.keyno as Position
> , c.name as FieldName
> , CASE WHEN indexkey_property(o.id, i.indid, 1, 'isdescending') = 1) THEN
> 'Descending'
> ELSE 'Ascending'
> END AS Direction
> from sysindexkeys k
> left join sysobjects o on k.id = o.id
> left join sysindexes i on k.id = i.id and k.indid = i.indid
> left outer join syscolumns c on k.id = c.id and k.colid = c.colid
> order by o.name, i.name, k.keyno
>
> John
> "Nadim Wakim - Lebanon" wrote:
>|||Instead of reinventing the wheel there are lots of inexpensive 3rd party
products out there that do these comparisons for you.
http://www.aspfaq.com/show.asp?id=2209
Some of them (I know Red-Gate does) come with an api to allow you to do
these comparisons programatically.
Andrew J. Kelly SQL MVP
"Nadim Wakim" <nadimlb@.cyberia.net.lb> wrote in message
news:e35REvNEFHA.1264@.TK2MSFTNGP12.phx.gbl...
> Dear John, although indexkey_property is working in the current db, i need
> to use it to query from another db in order to compare indexes between 2
> dbs.
> if i use:
> Select indexkey_property(o.id, i.indid, 1, 'isdescending')
> from db1.dbo.sysobjects o
> left outer join sysobjects current on ......
> , the function returns NULL because it is looking for the id in the
> current database.
> if i try to use db1.dbo.indexkey_property(), i get an error.
> I am trying to compare the indexes structure between a template database
> and the database i am connecting to in a query.
>
>
> "John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
> news:9498A7E0-D40B-4D13-A1C2-751EB0B03B8F@.microsoft.com...
>|||Dear Andrew, i tried 'Red gate' it's very efficient but it doesn't work in
case or replication.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eFH6llQEFHA.732@.TK2MSFTNGP12.phx.gbl...
> Instead of reinventing the wheel there are lots of inexpensive 3rd party
> products out there that do these comparisons for you.
> http://www.aspfaq.com/show.asp?id=2209
> Some of them (I know Red-Gate does) come with an api to allow you to do
> these comparisons programatically.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Nadim Wakim" <nadimlb@.cyberia.net.lb> wrote in message
> news:e35REvNEFHA.1264@.TK2MSFTNGP12.phx.gbl...
>|||Hi
You can put your results into a table and then process them
USE TEMPDB
CREATE TABLE [IndexTypes] (
[DBName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[name] [sysname] NOT NULL ,
[id] [int] NOT NULL ,
[indid] [smallint] NOT NULL ,
[Direction] [int] NULL
)
GO
USE DB1
INSERT INTO tempdb..IndexTypes
Select db_name() as DBName, o.name, o.id, i.indid,
indexkey_property(o.id, i.indid, 1, 'isdescending') as Direction
from sysobjects o
JOIN sysindexes i ON o.id = i.id
USE DB2
INSERT INTO tempdb..IndexTypes
Select db_name() as DBName, o.name, o.id, i.indid,
indexkey_property(o.id, i.indid, 1, 'isdescending') as Direction
from sysobjects o
JOIN sysindexes i ON o.id = i.id
SELECT * FROM tempdb..IndexTypes
where name = 'MyTable2'
John
Nadim Wakim wrote:
> Dear Andrew, i tried 'Red gate' it's very efficient but it doesn't
work in
> case or replication.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eFH6llQEFHA.732@.TK2MSFTNGP12.phx.gbl...
party
to do
db, i
indexes
the
database
that
displaying,
1)
sp_helpindex
and
and

How to read DTS log ?

Hi everybody,
Someone to know a program for DTS log reading ?
When I open it with Wordpad there was same confusion.
Thanks a lot.To view package logs

In SQL Server Enterprise Manager, expand Data Transformation Services.

Do one of the following:
Right-click Local Packages (if the Data Transformation Services (DTS) package log was saved to Microsoft SQL Server) and then click Package Logs.

Right-click Meta Data Services Packages (if the package log was saved to SQL Server 2000 Meta Data Services), and then click Package Logs.

Click Local Packages or Meta Data Services Packages, and in the details pane, right-click a package and click Package Logs.

how to read binary file

Need help reading a binary file see below for details...

I have uploaded a csv file into a sql table.

Now i want to extract the data and insert the data in the csv file into another sql table.

What commands can i use in sql to extract/ read the data ?

INSERT INTO {table1}({col1})

SELECT {col2}

FROM {table2}

|||

i'm not reading data from a table....i'm want to read data from a binary file that is stored in a table

|||

Retrieve the value into a string.

Use the string as the buffer of a memory stream.

Use the memory stream as the base stream of whatever type of stream you want to use to actually read the data.

Or

Retrieve the value from the database into a string, and parse it?

Or

Retrieve the value from the database, and save it as a temporay file, then open the temporary file and parse like normal.

|||

Here's how to do the 2nd approach (Which is probably easiest):

dim conn as new sqlconnection(configurationmanager.connectionstrings("ConnectionString").Connectionstring)

dim cmd as new sqlcommand("SELECT MyBlobField FROM MyTable WHEREID=@.ID",conn)

cmd.Parameters.Add("@.ID").Value= {your key here}

conn.open

dim csv as string

csv=cmd.executescalar

conn.close

for each row as string incsv.Split(NewString() {vbCrLf}, StringSplitOptions.RemoveEmptyEntries)

dim cols() as string=row.Split(","c)

' Do insert here by referencing cols(0) - cols(x) for each of the columns in the csv

next

Does that help? Obviously, that's a very simplistic CSV parser, and it doesn't handle embedded cr/lf's, nor does it handle unix/linux written files, nor does it handle quoted fields, or quoted fields with commas in them. You can do a similiar approach using regular expressions to handle more complex CSVs if you need, but I didn't want to over complicate a simple example.

|||

If you are going to go the first or third of my options, you may want to use this to do the actual parsing for you, as it handles most of the known gotchas in CSVs:

http://www.codeproject.com/cs/database/CsvReader.asp

|||

thanks for the info...the info you posted is very helpful but its not exactly what i'm looking for.....i believe i'm not making my problem clear.....

what i'm trying to find out is there a way to read the binary (csv file) in a store procedure and extract the data and insert it into a table.

the csv file contains 2 column of data key and value.

I like to insert each row into a sql table call "TempA" which has colums key and value. I want to do this in a store procedure?

Any ideas?

again thanks for your help

|||

Hi,

As your .csv file is saved as binary data in a database table, I suggest you to pull the .csv file from the datafield first (by BinaryReader) and read the csv file, create the datatable, call your store procedure to insert the table into your database. Here's the sample code for you to transfer your csv data to datatable.

int intColCount = 0;
bool blnFlag = true;
DataTable mydt = new DataTable("myTableName");

DataColumn mydc;
DataRow mydr;

string strpath = ""; //cvs file path

string strline;
string [] aryline;

System.IO.StreamReader mysr = new System.IO.StreamReader(strpath);

while((strline = mysr.ReadLine()) != null)
{
aryline = strline.Split(new char[]{','});

if (blnFlag)
{
blnFlag = false;
intColCount = aryline.Length;
for (int i = 0; i < aryline.Length; i++)
{
mydc = new DataColumn(aryline[i]);
mydt.Columns.Add(mydc);
}
}

mydr = mydt.NewRow();
for (int i = 0; i < intColCount; i++)
{
mydr[i] = aryline[i];
}
mydt.Rows.Add(mydr);
}

Thanks.