Oh No This Is A Disaster

Ok, this is really bad.

A table has been accidentally DROPPED from our SQL Database...

We have the transaction log from the very day that this table was created...

However, as we are all C++ programmers (and not DB people!) we are not sure how to do this!

Could anyone explain how to recreate a table from a transaction log, perhaps up to a specific date - or point us to a resource on the web.

Thanks for any advice you can give. (Head now under the table, hiding from the management :mad:

Mark.Could you maybe restore from those old transaction logs (do you mean you have transaction log backups from the time the table was created)?
Notice that all tables, SPs etc will be restored then from the old times.|||Hi - thanks for the reply - In actual fact, the transaction log is complete - it has never been backed up...

In effect, I think what we need to do is replay the transaction log up until a certain point, BEFORE the table was accidentally dropped, thus, in effect 'rebuilding' the database (or maybe we can filter the transaction log for just one table?) from scratch.

We don't mind if we lose some data, say, up until the nearest check point before the accident, we can then manually re-enter the data...

Our current line of thinking is that we first need to backup the t/log (it will be a full backup since it's never been backed up) and then restore
it up until a certain point - however this is a programming shop and we're not really sure of the process/syntax required to do this - we just treat the server as a black box and pump data in/out with SQL Statements!!!

We're sinking fast!

The SQL server is SQL Server 7 - WE have just stopped the server and literally copied the disk files - just in case - we would like to take those files to a second box, so that we can work on it without causing any more damage(!) - Is there a method to do this?

Thanks again for any assistance you can offer.

Mark.|||Hi Mark,
I know that you can do a partial restore in SQl Server 2000 to a named mark in the transactional log but do not think this option is available to you with version 7. I just went searching on-line and found no evidence to counter my suspicions... Since you have never done a back-up of the transactional log, it is pretty useless to you. When is the last back-up you made for your database?

Offline|Online database

Hi,
Is there any method to get an historical log where appears the database status (Online|offline) ?
Regards.Not really. You can check the SQL Server errorlog, which will tell you at least when a database is brought online.

ODS

In the Event Log I'm received next message:
Source: MSSQLServer
Category: ODS
ID: 17052
Error: 17833, Severity: 18, State: 1 yyyy-mm-dd hh:mm:ss ods ListenOn
connection 'servername' is already in use.
But my SQL Server are working normally.
What's a problem?
Michael S. Androsov
EXPOCENTRHi,
Another application or service running in the server would have taken
control of the port before SQL Server starts.
Have a look into the below article.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;293107
Thanks
Hari
MCDBA
"Michael S. Androsov" <androsov@.expocentr.ru> wrote in message
news:f99f3e13.0403030156.77a793c5@.posting.google.com...
> In the Event Log I'm received next message:
> Source: MSSQLServer
> Category: ODS
> ID: 17052
> Error: 17833, Severity: 18, State: 1 yyyy-mm-dd hh:mm:ss ods ListenOn
> connection 'servername' is already in use.
> But my SQL Server are working normally.
> What's a problem?
> Michael S. Androsov
> EXPOCENTR|||"Hari" <hari_prasad_k@.hotmail.com> wrote in message news:<OkydjcQAEHA.712@.tk2msftngp13.phx.gbl>...
> Hi,
> Another application or service running in the server would have taken
> control of the port before SQL Server starts.
> Have a look into the below article.
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;293107
> Thanks
> Hari
> MCDBA
> "Michael S. Androsov" <androsov@.expocentr.ru> wrote in message
> news:f99f3e13.0403030156.77a793c5@.posting.google.com...
> > In the Event Log I'm received next message:
> > Source: MSSQLServer
> > Category: ODS
> > ID: 17052
> > Error: 17833, Severity: 18, State: 1 yyyy-mm-dd hh:mm:ss ods ListenOn
> > connection 'servername' is already in use.
> >
> > But my SQL Server are working normally.
> >
> > What's a problem?
> >
> > Michael S. Androsov
> > EXPOCENTR
I have't other applications working with 1433 or 1118 ports. If this
applications exist my SQL Server will not working. But now the server
is working.
Michael S. Androsov
EXPOCENTR

ODS

In the Event Log I'm received next message:
Source: MSSQLServer
Category: ODS
ID: 17052
Error: 17833, Severity: 18, State: 1 yyyy-mm-dd hh:mm:ss ods ListenOn
connection 'servername' is already in use.
But my SQL Server are working normally.
What's a problem?
Michael S. Androsov
EXPOCENTRHi,
Another application or service running in the server would have taken
control of the port before SQL Server starts.
Have a look into the below article.
http://support.microsoft.com/defaul...kb;EN-US;293107
Thanks
Hari
MCDBA
"Michael S. Androsov" <androsov@.expocentr.ru> wrote in message
news:f99f3e13.0403030156.77a793c5@.posting.google.com...
> In the Event Log I'm received next message:
> Source: MSSQLServer
> Category: ODS
> ID: 17052
> Error: 17833, Severity: 18, State: 1 yyyy-mm-dd hh:mm:ss ods ListenOn
> connection 'servername' is already in use.
> But my SQL Server are working normally.
> What's a problem?
> Michael S. Androsov
> EXPOCENTR|||"Hari" <hari_prasad_k@.hotmail.com> wrote in message news:<OkydjcQAEHA.712@.tk2msftngp13.phx.
gbl>...
> Hi,
> Another application or service running in the server would have taken
> control of the port before SQL Server starts.
> Have a look into the below article.
> http://support.microsoft.com/defaul...kb;EN-US;293107
> Thanks
> Hari
> MCDBA
> "Michael S. Androsov" <androsov@.expocentr.ru> wrote in message
> news:f99f3e13.0403030156.77a793c5@.posting.google.com...
I have't other applications working with 1433 or 1118 ports. If this
applications exist my SQL Server will not working. But now the server
is working.
Michael S. Androsov
EXPOCENTR

Odd Server Behavior

I have a new SQL Server installation on a remote dedicated web server server
hosting several websites.
If I use Remote Desktop, I can log in on the server and perform any DB task,
using any of the created logins without any problem. I can use Enterprise
Manager & Query Analyzer without any problems.
But if I connect with Query Analyzer or Enterprise Manager remotely (from my
home or office PC), things start getting really strange.
First, I don't see all the databases when I connect as sa in Query Analyzer
or Enterprise Manager. Now, I don't like using sa remotely in the first
place, so I have an account that has complete admin access to the database
server. And that account works fine on the server. But no other account on
the server will connect to the database remotely except sa. When I do
connect as sa, I don't see all objects on the server. I don't see 1
database. The other 2 databases I do see? I don't see all the objects using
either QA or EM. I can't run select statements on them. If I run SELECT *
FROM SYSOBJECTS remotely, I don't see all the objects. If I run SELECT *
FROM SYSOBJECTS on the server, I see everything. And yes, the accounts are
set up correctly, they all work as configured on the server. And when I
thought it couldn't behave more wierd, it did. I didn't see a table named
ClubCalendar' remotely. So I created it remotely to see what would happen.
And it created the new table. And if I log on the server, I don't see that
table. I know I'm connecting to the correct server because I doubt anyone
else would use the password I'm using for sa and that account does connect.
Anyone have any ideas about this? This is starting to drive me batty.
Dan
Problem solved. I had to shut down the local instance and not start it and
reset the TCP port. Even though it was set to 1433, I had to set it again.
Odd, since this was working yesterday morning.
Dan
"Dan" wrote:

> I have a new SQL Server installation on a remote dedicated web server server
> hosting several websites.
> If I use Remote Desktop, I can log in on the server and perform any DB task,
> using any of the created logins without any problem. I can use Enterprise
> Manager & Query Analyzer without any problems.
> But if I connect with Query Analyzer or Enterprise Manager remotely (from my
> home or office PC), things start getting really strange.
> First, I don't see all the databases when I connect as sa in Query Analyzer
> or Enterprise Manager. Now, I don't like using sa remotely in the first
> place, so I have an account that has complete admin access to the database
> server. And that account works fine on the server. But no other account on
> the server will connect to the database remotely except sa. When I do
> connect as sa, I don't see all objects on the server. I don't see 1
> database. The other 2 databases I do see? I don't see all the objects using
> either QA or EM. I can't run select statements on them. If I run SELECT *
> FROM SYSOBJECTS remotely, I don't see all the objects. If I run SELECT *
> FROM SYSOBJECTS on the server, I see everything. And yes, the accounts are
> set up correctly, they all work as configured on the server. And when I
> thought it couldn't behave more wierd, it did. I didn't see a table named
> ClubCalendar' remotely. So I created it remotely to see what would happen.
> And it created the new table. And if I log on the server, I don't see that
> table. I know I'm connecting to the correct server because I doubt anyone
> else would use the password I'm using for sa and that account does connect.
> Anyone have any ideas about this? This is starting to drive me batty.
> --
> Dan

Odd Server Behavior

I have a new SQL Server installation on a remote dedicated web server server
hosting several websites.
If I use Remote Desktop, I can log in on the server and perform any DB task,
using any of the created logins without any problem. I can use Enterprise
Manager & Query Analyzer without any problems.
But if I connect with Query Analyzer or Enterprise Manager remotely (from my
home or office PC), things start getting really strange.
First, I don't see all the databases when I connect as sa in Query Analyzer
or Enterprise Manager. Now, I don't like using sa remotely in the first
place, so I have an account that has complete admin access to the database
server. And that account works fine on the server. But no other account on
the server will connect to the database remotely except sa. When I do
connect as sa, I don't see all objects on the server. I don't see 1
database. The other 2 databases I do see? I don't see all the objects usin
g
either QA or EM. I can't run select statements on them. If I run SELECT *
FROM SYSOBJECTS remotely, I don't see all the objects. If I run SELECT *
FROM SYSOBJECTS on the server, I see everything. And yes, the accounts are
set up correctly, they all work as configured on the server. And when I
thought it couldn't behave more wierd, it did. I didn't see a table named
ClubCalendar' remotely. So I created it remotely to see what would happen.
And it created the new table. And if I log on the server, I don't see that
table. I know I'm connecting to the correct server because I doubt anyone
else would use the password I'm using for sa and that account does connect.
Anyone have any ideas about this? This is starting to drive me batty.
DanProblem solved. I had to shut down the local instance and not start it and
reset the TCP port. Even though it was set to 1433, I had to set it again.
Odd, since this was working yesterday morning.
Dan
"Dan" wrote:

> I have a new SQL Server installation on a remote dedicated web server serv
er
> hosting several websites.
> If I use Remote Desktop, I can log in on the server and perform any DB tas
k,
> using any of the created logins without any problem. I can use Enterprise
> Manager & Query Analyzer without any problems.
> But if I connect with Query Analyzer or Enterprise Manager remotely (from
my
> home or office PC), things start getting really strange.
> First, I don't see all the databases when I connect as sa in Query Analyze
r
> or Enterprise Manager. Now, I don't like using sa remotely in the first
> place, so I have an account that has complete admin access to the database
> server. And that account works fine on the server. But no other account
on
> the server will connect to the database remotely except sa. When I do
> connect as sa, I don't see all objects on the server. I don't see 1
> database. The other 2 databases I do see? I don't see all the objects us
ing
> either QA or EM. I can't run select statements on them. If I run SELECT
*
> FROM SYSOBJECTS remotely, I don't see all the objects. If I run SELECT *
> FROM SYSOBJECTS on the server, I see everything. And yes, the accounts ar
e
> set up correctly, they all work as configured on the server. And when I
> thought it couldn't behave more wierd, it did. I didn't see a table named
> ClubCalendar' remotely. So I created it remotely to see what would happen
.
> And it created the new table. And if I log on the server, I don't see tha
t
> table. I know I'm connecting to the correct server because I doubt anyone
> else would use the password I'm using for sa and that account does connect
.
> Anyone have any ideas about this? This is starting to drive me batty.
> --
> Dan

ODBC: SQL_ATTR_CONCURRENCY changing after simple select

The following is the log from an ODBC Test session against a SQL Server 2000 database.
MDAC Ver. 2.7 [SP1 I think].

The point of interest is that the SQL_ATTR_CONCURRENCY setting is changing from SQL_CONCUR_LOCK (READ/WRITE), to SQL_CONCUR_READ_ONLY after the SQLExecDirect() call [I've also tried SQLPrepare/SQLExecute with the same result].

Can anyone please throw some light onto why this happens, and how to avoid it? [It kills my SQLBulkOperations() call to insert values into the table]

Many thanks.
PeterC.

dbc: szSqlState = "01000", *pfNativeError = 5701, *pcbErrorMsg = 84, *ColumnNumber = -1, *RowNumber = -1
MessageText = "[Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'pctest'."
dbc: szSqlState = "01000", *pfNativeError = 5703, *pcbErrorMsg = 86, *ColumnNumber = -1, *RowNumber = -1
MessageText = "[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english."

Full Connect:

Full Connect(Use Driver)
Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3

Successfully connected to DSN 'Testpc'.

SQLSetStmtAttr:
In: StatementHandle = 0x00841978,
Attribute = SQL_ATTR_CONCURRENCY=7,
ValuePtr = SQL_CONCUR_LOCK=2,
StringLength = SQL_NTS=-3,
fAttribute Type = SQL_C_SLONG=-16
Return: SQL_SUCCESS=0

SQLSetStmtAttr:
In: StatementHandle = 0x00841978,
Attribute = SQL_ATTR_CURSOR_TYPE=6,
ValuePtr = SQL_CURSOR_KEYSET_DRIVEN=1,
StringLength = SQL_NTS=-3,
fAttribute Type = SQL_C_SLONG=-16
Return: SQL_SUCCESS=0

SQLSetStmtAttr:
In: StatementHandle = 0x00841978,
Attribute = SQL_ATTR_ROW_ARRAY_SIZE=27,
ValuePtr = 1,
StringLength = SQL_NTS=-3,
fAttribute Type = SQL_C_SLONG=-16
Return: SQL_SUCCESS=0

SQLExecDirect:
In: Statementhandle = 0x00841978,
StatementText = "select * from pc02",
Statementlength = 20
Return: SQL_SUCCESS_WITH_INFO=1

SQLGetStmtAttr:
In: StatementHandle = 0x00841978,
Attribute = SQL_ATTR_CONCURRENCY=7,
ValuePtr = 0x0014C3F8,
BufferLength = 300,
StringLengthPtr = 0x0014E968,
fAttribute Type = SQL_C_SLONG=-16
Return: SQL_SUCCESS=0
Out:
*ValuePtr = SQL_CONCUR_READ_ONLY = 1,
*StringLengthPtr = 4I should also add that the cursor is changing from SQL_CURSOR_KEYSET_DRIVEN to SQL_CURSOR_STATIC.

PeterC.