Hello,
We are using SQL Server 2000 DB for our project.Some 10 ppl are using the server.Today we found that a key table records are missing.It has lot of referntial integrity constraints.Could anyone tell me how to track who deleted the table..Does the server keeps any log of all the queries executed by the clients.Pls help us.ThanksProbably one of the ten most requested things for SQL. Unfortunately, no; natively SQL does not have this capability. There are 3rd party tools for doing this sort of thing, but nothing native to SQL Server 7.0 or 2000 (maybe in 2005, but I haven't seen that yet).
Some things that you CAN do to minimize/mitigate the problem:
1. Establish a proper security model. Each user has an assigned username / password (I prefer integrated security). Each user should be assigned the minimum privileges necessary to complete there assigned task(s). No one (not even you) should be using the sa account.
2. Establish a backup and recovery process that meets your specific requirements. Suggested profile would be a complete backup daily for system and user databases, plus transaction log backups every 1-3 hours depending on your requirements and the traffic on your server. Be sure that your backups are written to another server so that you can recover in the event of a failure of the disk on the primary server.
3. Audit the access to your SQL server; at a minimum log failed access requests.
4. Periodically audit the privileges for each user; check in particular for sysadmin or dbowner privileges.
I'm sorry about your loss and I hope that you are able to recover. I wish I had a better answer (and I hope MS comes up with a better one soon!). Hopefully it's not one that will cost anyone their job and you can chalk it up to experience.
Kindest regards,
hmscott
PS. I should mention that one thing many experts recommend is to run a continuous trace file of the most recent commands issued to SQL server. This IS a native capability of SQL server. I think there is an article on how to set one up on http://www.sqlservercentral.com. This would tell you who had done what and when (provided it happened within the window of the log file.
Hello,
We are using SQL Server 2000 DB for our project.Some 10 ppl are using the server.Today we found that a key table records are missing.It has lot of referntial integrity constraints.Could anyone tell me how to track who deleted the table..Does the server keeps any log of all the queries executed by the clients.Pls help us.Thanks|||Hello,
We are using SQL Server 2000 DB for our project.Some 10 ppl are using the server.Today we found that a key table records are missing.It has lot of referntial integrity constraints.Could anyone tell me how to track who deleted the table..Does the server keeps any log of all the queries executed by the clients.Pls help us.ThanksRestore from yesterday's backups, and follow Scott's suggestions...You DO have backups, right?|||Thanks for your reply...We do have backups
Showing posts with label records. Show all posts
Showing posts with label records. Show all posts
Sunday, March 11, 2012
Saturday, February 25, 2012
Db Migration with Identity constraints and their Fkey records
Hi,
we have to migrate data from three tables in 3 different servers into one
table on fourth servers.. and the scenariou is as below
ServerA/Database : One table named as Access with Pkey and identity field.
This identity field is Fkey in another table.
ServerB/Database : One table named as Access with Pkey and identity field.
This identity field is Fkey in another table.
ServerC/Database : One table named as Access with Pkey and identity field.
This identity field is Fkey in another table.
We have to merge data/table of all the above three servers into one
database. We have to merge all records from all three servers into one tabl
e
which will be in another server.
How should we take care of identity issues and fkey constraints as table has
different identity in all three servers and they are related to some other
tables as Fkey Constraints. table structure is same in all servers.
Thanks in advance ,
SunnySunny
> We have to merge data/table of all the above three servers into one
> database. We have to merge all records from all three servers into one
> table
> which will be in another server.
Perhsps by using DTS Package to move the data but you will have to decide
how to store the data because what if these table have the same identity
value. It is up to you to decide. Ome option is to create a group of these
values (i mean identities) from three table and generate a PK to each
group.
"Sunny" <Sunny@.discussions.microsoft.com> wrote in message
news:760AF5E1-D44C-4888-BFB5-B4D61E4F7EB0@.microsoft.com...
> Hi,
> we have to migrate data from three tables in 3 different servers into one
> table on fourth servers.. and the scenariou is as below
> ServerA/Database : One table named as Access with Pkey and identity field.
> This identity field is Fkey in another table.
> ServerB/Database : One table named as Access with Pkey and identity field.
> This identity field is Fkey in another table.
> ServerC/Database : One table named as Access with Pkey and identity field.
> This identity field is Fkey in another table.
> We have to merge data/table of all the above three servers into one
> database. We have to merge all records from all three servers into one
> table
> which will be in another server.
> How should we take care of identity issues and fkey constraints as table
> has
> different identity in all three servers and they are related to some other
> tables as Fkey Constraints. table structure is same in all servers.
> Thanks in advance ,
> Sunny
>|||Thanks, but could you please provide more details what exactly you mean here
.
"Uri Dimant" wrote:
> Sunny
>
> Perhsps by using DTS Package to move the data but you will have to decide
> how to store the data because what if these table have the same identity
> value. It is up to you to decide. Ome option is to create a group of thes
e
> values (i mean identities) from three table and generate a PK to each
> group.
>
> "Sunny" <Sunny@.discussions.microsoft.com> wrote in message
> news:760AF5E1-D44C-4888-BFB5-B4D61E4F7EB0@.microsoft.com...
>
>
we have to migrate data from three tables in 3 different servers into one
table on fourth servers.. and the scenariou is as below
ServerA/Database : One table named as Access with Pkey and identity field.
This identity field is Fkey in another table.
ServerB/Database : One table named as Access with Pkey and identity field.
This identity field is Fkey in another table.
ServerC/Database : One table named as Access with Pkey and identity field.
This identity field is Fkey in another table.
We have to merge data/table of all the above three servers into one
database. We have to merge all records from all three servers into one tabl
e
which will be in another server.
How should we take care of identity issues and fkey constraints as table has
different identity in all three servers and they are related to some other
tables as Fkey Constraints. table structure is same in all servers.
Thanks in advance ,
SunnySunny
> We have to merge data/table of all the above three servers into one
> database. We have to merge all records from all three servers into one
> table
> which will be in another server.
Perhsps by using DTS Package to move the data but you will have to decide
how to store the data because what if these table have the same identity
value. It is up to you to decide. Ome option is to create a group of these
values (i mean identities) from three table and generate a PK to each
group.
"Sunny" <Sunny@.discussions.microsoft.com> wrote in message
news:760AF5E1-D44C-4888-BFB5-B4D61E4F7EB0@.microsoft.com...
> Hi,
> we have to migrate data from three tables in 3 different servers into one
> table on fourth servers.. and the scenariou is as below
> ServerA/Database : One table named as Access with Pkey and identity field.
> This identity field is Fkey in another table.
> ServerB/Database : One table named as Access with Pkey and identity field.
> This identity field is Fkey in another table.
> ServerC/Database : One table named as Access with Pkey and identity field.
> This identity field is Fkey in another table.
> We have to merge data/table of all the above three servers into one
> database. We have to merge all records from all three servers into one
> table
> which will be in another server.
> How should we take care of identity issues and fkey constraints as table
> has
> different identity in all three servers and they are related to some other
> tables as Fkey Constraints. table structure is same in all servers.
> Thanks in advance ,
> Sunny
>|||Thanks, but could you please provide more details what exactly you mean here
.
"Uri Dimant" wrote:
> Sunny
>
> Perhsps by using DTS Package to move the data but you will have to decide
> how to store the data because what if these table have the same identity
> value. It is up to you to decide. Ome option is to create a group of thes
e
> values (i mean identities) from three table and generate a PK to each
> group.
>
> "Sunny" <Sunny@.discussions.microsoft.com> wrote in message
> news:760AF5E1-D44C-4888-BFB5-B4D61E4F7EB0@.microsoft.com...
>
>
Friday, February 17, 2012
DB is ReadOnly?
Hi all,
I am using DAO 3.6 to connect to a SQL Server db on a remote machine.
I can open and close the connection, read records, run stored procedures
but when I try to add a record I get "object or database is read only"
If I connect to the database using ADO I can add records.
Here is my code:
' ----
Dim wrk_DAO As DAO.Workspace
Dim cnn_DAO As DAO.Connection
Dim rec_DAO as DAO.Recordset
Set wrk_DAO = CreateWorkspace("MyWorkspace", "sa", "pw", dbUseODBC)
Set cnn_DAO = wrk_DAO.OpenConnection("MyConnection", dbDriverNoPrompt,
False, _
"ODBC;DATABASE=SQLTest;UID=sa;PWD=pw;DSN=odbc_test ")
Set rec_DAO = cnn_DAO.OpenRecordset(strSQL, dbOpenDynamic)
rec_DAO.AddNew '<<< ERROR occurs here
rec_DAO!Field1 = 1
rec_DAO!Field2 = "Test"
rec_DAO.Update
rec_DAO.Close
cnn_DAO.Close
' ----
If I use the same code but only read, it works fine.
What am I doing wrong?
Thanks.
kpg
"kpg" <ipost@.thereforeiam.com> wrote in message
news:%23Oremk8pEHA.2588@.TK2MSFTNGP12.phx.gbl...
> I am using DAO 3.6 to connect to a SQL Server db on a remote machine.
> I can open and close the connection, read records, run stored procedures
> but when I try to add a record I get "object or database is read only"
> If I connect to the database using ADO I can add records.
> Here is my code:
> ' ----
> Dim wrk_DAO As DAO.Workspace
> Dim cnn_DAO As DAO.Connection
> Dim rec_DAO as DAO.Recordset
> Set wrk_DAO = CreateWorkspace("MyWorkspace", "sa", "pw", dbUseODBC)
> Set cnn_DAO = wrk_DAO.OpenConnection("MyConnection", dbDriverNoPrompt,
> False, _
> "ODBC;DATABASE=SQLTest;UID=sa;PWD=pw;DSN=odbc_test ")
> Set rec_DAO = cnn_DAO.OpenRecordset(strSQL, dbOpenDynamic)
> rec_DAO.AddNew '<<< ERROR occurs here
> rec_DAO!Field1 = 1
> rec_DAO!Field2 = "Test"
> rec_DAO.Update
> rec_DAO.Close
> cnn_DAO.Close
> ' ----
> If I use the same code but only read, it works fine.
> What am I doing wrong?
It may not be anything you are doing "wrong", other than using a possibly
incompatible, old library to access SQL Server. DAO was written an intended
to be a library for Jet. The fact that your code works in ADO speaks to the
proper library to use...
Steve
|||"Steve Thompson" wrote
> It may not be anything you are doing "wrong", other than using a possibly
> incompatible, old library to access SQL Server.
<snip>
Yes, thanks for the reply. I solved my little problem however...
If I set the locking type to 'dbOptimisticValue' it works fine:
Re:
'--
Set rec_DAO = cnn_DAO.OpenRecordset(strSQL, dbOpenDynamic, _
dbExecDirect, dbOptimisticValue)
'--
I guess the default was a snapshot?
BTW: I would never really consider using DAO for a SQL Server DB,
I was mainly interested in benchmarking the difference between Access, SQL
Server, ADO and DAO, for use in a management proposal.
I use a SQL Server and an Access db located on the same machine on my LAN.
This way the netword has to be involved in all IO.
My test is not very rigid from a scientific standpoint and the results are
not very
suprising, but here the are:
Add 1000
Add x100
Read
SP
Delete
Total
ADO ACCESS
0.07
1.66
0.03
0.03
0.02
1.81
DAO ACCESS
0.06
1.2
0.03
0.02
0.01
1.32
ADO SQL
1.54
1.63
0.03
0.03
0.02
3.25
DAO SQL
0.66
2.67
0.05
0.04
0.03
3.45
times are in seconds.
Add 1000: adds 1000 small records by opening the db once, looping, then
closing.
Add x100: adds the same recodes bu7t opens and closes the db for each record
(100 times)
Read: reads the 1100 records by opening once, reading in a while loop,
closing.
SP: run a stored procedure instead of a SELECT Query, otherwise the same as
Read
Delete: execute a DELETE FORM Table command.
kpg
In theory, there is no difference between theory and practice.
But, in practice, there is - Jan L.A. van de Snepscheut
|||I'm glad you found that -- the default is snapshot from what I remember (and
that is digging back a while).
BTW, speed is only one factor, I would be more concerned about stability,
portability and support of your code. While DAO is fast (and it has always
been known for speed), DAO is not the way to go for a strategic software
development with SQL Server. You can achieve higher performance in SQL
Server by taking advantage of VIEWS and Stored Procedures (for data
manipulation).
Steve
"kpg" <ipost@.thereforeiam.com> wrote in message
news:upbmBF$pEHA.596@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> "Steve Thompson" wrote
possibly
> <snip>
> Yes, thanks for the reply. I solved my little problem however...
> If I set the locking type to 'dbOptimisticValue' it works fine:
> Re:
> '--
> Set rec_DAO = cnn_DAO.OpenRecordset(strSQL, dbOpenDynamic, _
> dbExecDirect, dbOptimisticValue)
> '--
> I guess the default was a snapshot?
> BTW: I would never really consider using DAO for a SQL Server DB,
> I was mainly interested in benchmarking the difference between Access, SQL
> Server, ADO and DAO, for use in a management proposal.
> I use a SQL Server and an Access db located on the same machine on my LAN.
> This way the netword has to be involved in all IO.
> My test is not very rigid from a scientific standpoint and the results are
> not very
> suprising, but here the are:
>
> Add 1000
> Add x100
> Read
> SP
> Delete
> Total
> ADO ACCESS
> 0.07
> 1.66
> 0.03
> 0.03
> 0.02
> 1.81
> DAO ACCESS
> 0.06
> 1.2
> 0.03
> 0.02
> 0.01
> 1.32
> ADO SQL
> 1.54
> 1.63
> 0.03
> 0.03
> 0.02
> 3.25
> DAO SQL
> 0.66
> 2.67
> 0.05
> 0.04
> 0.03
> 3.45
>
> times are in seconds.
> Add 1000: adds 1000 small records by opening the db once, looping, then
> closing.
> Add x100: adds the same recodes bu7t opens and closes the db for each
record
> (100 times)
> Read: reads the 1100 records by opening once, reading in a while loop,
> closing.
> SP: run a stored procedure instead of a SELECT Query, otherwise the same
as
> Read
> Delete: execute a DELETE FORM Table command.
> --
> kpg
> In theory, there is no difference between theory and practice.
> But, in practice, there is - Jan L.A. van de Snepscheut
>
>
I am using DAO 3.6 to connect to a SQL Server db on a remote machine.
I can open and close the connection, read records, run stored procedures
but when I try to add a record I get "object or database is read only"
If I connect to the database using ADO I can add records.
Here is my code:
' ----
Dim wrk_DAO As DAO.Workspace
Dim cnn_DAO As DAO.Connection
Dim rec_DAO as DAO.Recordset
Set wrk_DAO = CreateWorkspace("MyWorkspace", "sa", "pw", dbUseODBC)
Set cnn_DAO = wrk_DAO.OpenConnection("MyConnection", dbDriverNoPrompt,
False, _
"ODBC;DATABASE=SQLTest;UID=sa;PWD=pw;DSN=odbc_test ")
Set rec_DAO = cnn_DAO.OpenRecordset(strSQL, dbOpenDynamic)
rec_DAO.AddNew '<<< ERROR occurs here
rec_DAO!Field1 = 1
rec_DAO!Field2 = "Test"
rec_DAO.Update
rec_DAO.Close
cnn_DAO.Close
' ----
If I use the same code but only read, it works fine.
What am I doing wrong?
Thanks.
kpg
"kpg" <ipost@.thereforeiam.com> wrote in message
news:%23Oremk8pEHA.2588@.TK2MSFTNGP12.phx.gbl...
> I am using DAO 3.6 to connect to a SQL Server db on a remote machine.
> I can open and close the connection, read records, run stored procedures
> but when I try to add a record I get "object or database is read only"
> If I connect to the database using ADO I can add records.
> Here is my code:
> ' ----
> Dim wrk_DAO As DAO.Workspace
> Dim cnn_DAO As DAO.Connection
> Dim rec_DAO as DAO.Recordset
> Set wrk_DAO = CreateWorkspace("MyWorkspace", "sa", "pw", dbUseODBC)
> Set cnn_DAO = wrk_DAO.OpenConnection("MyConnection", dbDriverNoPrompt,
> False, _
> "ODBC;DATABASE=SQLTest;UID=sa;PWD=pw;DSN=odbc_test ")
> Set rec_DAO = cnn_DAO.OpenRecordset(strSQL, dbOpenDynamic)
> rec_DAO.AddNew '<<< ERROR occurs here
> rec_DAO!Field1 = 1
> rec_DAO!Field2 = "Test"
> rec_DAO.Update
> rec_DAO.Close
> cnn_DAO.Close
> ' ----
> If I use the same code but only read, it works fine.
> What am I doing wrong?
It may not be anything you are doing "wrong", other than using a possibly
incompatible, old library to access SQL Server. DAO was written an intended
to be a library for Jet. The fact that your code works in ADO speaks to the
proper library to use...
Steve
|||"Steve Thompson" wrote
> It may not be anything you are doing "wrong", other than using a possibly
> incompatible, old library to access SQL Server.
<snip>
Yes, thanks for the reply. I solved my little problem however...
If I set the locking type to 'dbOptimisticValue' it works fine:
Re:
'--
Set rec_DAO = cnn_DAO.OpenRecordset(strSQL, dbOpenDynamic, _
dbExecDirect, dbOptimisticValue)
'--
I guess the default was a snapshot?
BTW: I would never really consider using DAO for a SQL Server DB,
I was mainly interested in benchmarking the difference between Access, SQL
Server, ADO and DAO, for use in a management proposal.
I use a SQL Server and an Access db located on the same machine on my LAN.
This way the netword has to be involved in all IO.
My test is not very rigid from a scientific standpoint and the results are
not very
suprising, but here the are:
Add 1000
Add x100
Read
SP
Delete
Total
ADO ACCESS
0.07
1.66
0.03
0.03
0.02
1.81
DAO ACCESS
0.06
1.2
0.03
0.02
0.01
1.32
ADO SQL
1.54
1.63
0.03
0.03
0.02
3.25
DAO SQL
0.66
2.67
0.05
0.04
0.03
3.45
times are in seconds.
Add 1000: adds 1000 small records by opening the db once, looping, then
closing.
Add x100: adds the same recodes bu7t opens and closes the db for each record
(100 times)
Read: reads the 1100 records by opening once, reading in a while loop,
closing.
SP: run a stored procedure instead of a SELECT Query, otherwise the same as
Read
Delete: execute a DELETE FORM Table command.
kpg
In theory, there is no difference between theory and practice.
But, in practice, there is - Jan L.A. van de Snepscheut
|||I'm glad you found that -- the default is snapshot from what I remember (and
that is digging back a while).
BTW, speed is only one factor, I would be more concerned about stability,
portability and support of your code. While DAO is fast (and it has always
been known for speed), DAO is not the way to go for a strategic software
development with SQL Server. You can achieve higher performance in SQL
Server by taking advantage of VIEWS and Stored Procedures (for data
manipulation).
Steve
"kpg" <ipost@.thereforeiam.com> wrote in message
news:upbmBF$pEHA.596@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> "Steve Thompson" wrote
possibly
> <snip>
> Yes, thanks for the reply. I solved my little problem however...
> If I set the locking type to 'dbOptimisticValue' it works fine:
> Re:
> '--
> Set rec_DAO = cnn_DAO.OpenRecordset(strSQL, dbOpenDynamic, _
> dbExecDirect, dbOptimisticValue)
> '--
> I guess the default was a snapshot?
> BTW: I would never really consider using DAO for a SQL Server DB,
> I was mainly interested in benchmarking the difference between Access, SQL
> Server, ADO and DAO, for use in a management proposal.
> I use a SQL Server and an Access db located on the same machine on my LAN.
> This way the netword has to be involved in all IO.
> My test is not very rigid from a scientific standpoint and the results are
> not very
> suprising, but here the are:
>
> Add 1000
> Add x100
> Read
> SP
> Delete
> Total
> ADO ACCESS
> 0.07
> 1.66
> 0.03
> 0.03
> 0.02
> 1.81
> DAO ACCESS
> 0.06
> 1.2
> 0.03
> 0.02
> 0.01
> 1.32
> ADO SQL
> 1.54
> 1.63
> 0.03
> 0.03
> 0.02
> 3.25
> DAO SQL
> 0.66
> 2.67
> 0.05
> 0.04
> 0.03
> 3.45
>
> times are in seconds.
> Add 1000: adds 1000 small records by opening the db once, looping, then
> closing.
> Add x100: adds the same recodes bu7t opens and closes the db for each
record
> (100 times)
> Read: reads the 1100 records by opening once, reading in a while loop,
> closing.
> SP: run a stored procedure instead of a SELECT Query, otherwise the same
as
> Read
> Delete: execute a DELETE FORM Table command.
> --
> kpg
> In theory, there is no difference between theory and practice.
> But, in practice, there is - Jan L.A. van de Snepscheut
>
>
Tuesday, February 14, 2012
DB Growth Issue
I have done this experiment on one of the tables. There is table called
build havinf nvText Field with large no of records. I want to drop that
column and recover space. These are the results I got.
build havinf nvText Field with large no of records. I want to drop that
column and recover space. These are the results I got.
SP_SPACEUSED BUILD Results
name rows reserved data index_size unused
1. Before Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
2. After Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
3. After Executing the Shrink Database from Enterprise Manager.
Build 663211 608280 KB 604744 KB 3456 KB 80 K
4. After Executing DBCC DBReindex (build,'',70)
Build 663211 124096 KB 123392 KB 712 KB -8 KB
Can anyone please explain me after executing step 3 i.e shrink data
column as well as index_size shows an increased figure whereas logically
it should be a reduced figure.
Regards,
Harcharan
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Harcharan Jassal <hjjassal@.yahoo.com> wrote in message news:<41218aa5$0$14438$c397aba@.news.newsgroups.ws>...
> I have done this experiment on one of the tables. There is table called
> build havinf nvText Field with large no of records. I want to drop that
> column and recover space. These are the results I got.
> SP_SPACEUSED ?BUILD? Results
> name rows reserved data index_size unused
> 1. Before Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 2. After Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 3. After Executing the Shrink Database from Enterprise Manager.
> Build 663211 608280 KB 604744 KB 3456 KB 80 K
> 4. After Executing DBCC DBReindex (build,'',70)
> Build 663211 124096 KB 123392 KB 712 KB -8 KB
> Can anyone please explain me after executing step 3 i.e shrink data
> column as well as index_size shows an increased figure whereas logically
> it should be a reduced figure.
> Regards,
> Harcharan
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
As Books Online mentions, in some situations the data returned by
sp_spaceused "may not be current". You can try using @.updateusage to
see if that returns correct information:
exec sp_spaceused 'build', 'true'
See the Remarks section of sp_spaceused and also DBCC UPDATEUSAGE in
Books Online.
Simon
Subscribe to:
Posts (Atom)