Comparing two databases for deleted records

hi ,
there is a tool that do compare between 2 databases and display deleted records an changes in schema and data ,
it's friendlly tool with beautiful gui for users.
it is called dbMaestro.
You can find it here:
http://www.extreme.co.il
You might want to check out the Red-Gate tools SQL Compare and SQL
DataCompare. I think these tools will do what you are requesting. Here is
their website: http://www.red-gate.com/
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"yaniv cohen" <yanivc@.extreme.co.il> wrote in message
news:30D974F8-F7EC-4AD7-BCC7-DDE5D9146096@.microsoft.com...
> hi ,
> there is a tool that do compare between 2 databases and display deleted
records an changes in schema and data ,
> it's friendlly tool with beautiful gui for users.
> it is called dbMaestro.
> You can find it here:
> http://www.extreme.co.il
>

Comparing two databases for deleted records

How do I compare two databases to determine deleted
records."Aboki" <anonymous@.discussions.microsoft.com> wrote in message
news:13d7001c41b24$a4d4d240$a001280a@.phx
.gbl...
> How do I compare two databases to determine deleted
> records.
Select *
from dbArchive.dbo.table1 as a
left outer join
dbUpdated.dbo.table1 as u
on a.PK = u.PK
where u.pk is null
dbArchive = the old database name
dbUpdated = the one with the rows deleted
PK = whatever the Primary Key column is
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004|||hi ,
there is a tool that do compare between 2 databases and display deleted reco
rds an changes in schema and data ,
it's friendlly tool with beautiful gui for users.
it is called dbMaestro.
You can find it here:
http://www.extreme.co.il|||You might want to check out the Red-Gate tools SQL Compare and SQL
DataCompare. I think these tools will do what you are requesting. Here is
their website: http://www.red-gate.com/
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"yaniv cohen" <yanivc@.extreme.co.il> wrote in message
news:30D974F8-F7EC-4AD7-BCC7-DDE5D9146096@.microsoft.com...
> hi ,
> there is a tool that do compare between 2 databases and display deleted
records an changes in schema and data ,
> it's friendlly tool with beautiful gui for users.
> it is called dbMaestro.
> You can find it here:
> http://www.extreme.co.il
>

Compare and Restore Tables

I have a database with 44 user tables that had some data deleted. I have
a 2 month old backup of the .mdf and .ldf files I can reatach to the
Server (not the proper sql backup wizard method).

Is there a way I can compare the data that is in the old database to the
new and pull in the records from the old that don't exist in new
database?

Thanks,
Steve

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!Hi

Using the INFORMATION_SCHEMA views you can write a script that uses the PKs
for each table to insert into the new table that does not already exist in
the table already.

John
"Steve Bishop" <steveb@.viper.com> wrote in message
news:40eefab0$0$16477$c397aba@.news.newsgroups.ws.. .
> I have a database with 44 user tables that had some data deleted. I have
> a 2 month old backup of the .mdf and .ldf files I can reatach to the
> Server (not the proper sql backup wizard method).
> Is there a way I can compare the data that is in the old database to the
> new and pull in the records from the old that don't exist in new
> database?
> Thanks,
> Steve
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Compact method to reset Identity columns

I have a demo database in SqlCE that I am getting ready to deploy. I deleted a bunch of test records and now want to reset the identity columns. The compact method runs fine, but the identity columns are not being reset? So when I add a new record, the returned identity value is over 1,000 even though the highest value is only 50.

Any help is greatly appreciated!

Kind Regards,

Mat

Try out: ALTER TABLE <IdentityTable> ALTER COLUMN <Identity Column> <Data Type> (51, 1);

Thanks,

Laxmi

|||

I tried this:

ALTER TABLE asset ALTER COLUMN iid bigint (51,1)

but get the following error message:

The specified data type is not valid. [ Data type (if known) = bigint ]

Bigint is the data type of this field, but I get the same error message if I try int as well. Thanks for your help!

Kind Regards,

Mat

|||

Use

"ALTER TABLE asset ALTER COLUMN iid IDENTITY (51,1)"

|||

Pragya,

This worked perfectly!

Thanks,

Mat

Compact method to reset Identity columns

I have a demo database in SqlCE that I am getting ready to deploy. I deleted a bunch of test records and now want to reset the identity columns. The compact method runs fine, but the identity columns are not being reset? So when I add a new record, the returned identity value is over 1,000 even though the highest value is only 50.

Any help is greatly appreciated!

Kind Regards,

Mat

Try out: ALTER TABLE <IdentityTable> ALTER COLUMN <Identity Column> <Data Type> (51, 1);

Thanks,

Laxmi

|||

I tried this:

ALTER TABLE asset ALTER COLUMN iid bigint (51,1)

but get the following error message:

The specified data type is not valid. [ Data type (if known) = bigint ]

Bigint is the data type of this field, but I get the same error message if I try int as well. Thanks for your help!

Kind Regards,

Mat

|||

Use

"ALTER TABLE asset ALTER COLUMN iid IDENTITY (51,1)"

|||

Pragya,

This worked perfectly!

Thanks,

Mat

Compact and Repair in an adp

Hi,

I just deleted about 1 million records from a SQL Server 2000 database. I
access it using Access 2000.

I have Compact and Repair available in the adp under Tools. Should I do that
from Access? Will it not mess anything up?

Thanks!

J~JA wrote:

Quote:

Originally Posted by

I just deleted about 1 million records from a SQL Server 2000 database. I
access it using Access 2000.
>
I have Compact and Repair available in the adp under Tools. Should I do that
from Access? Will it not mess anything up?

No idea. You can definitely use Query Analyzer to run a
DBCC SHRINKDATABASE command.

If you expect the database to grow back to its previous size and
stay there, then you shouldn't shrink it. If you expect it to
stay at its reduced size, then you should.|||JA (jarmour@.kc.rr.com) writes:

Quote:

Originally Posted by

I just deleted about 1 million records from a SQL Server 2000 database. I
access it using Access 2000.
>
I have Compact and Repair available in the adp under Tools. Should I do
that from Access? Will it not mess anything up?

To me that sounds like tools for the local Access database.

In any case, one million rows is not that much, unless the rows are
extremely wide.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Commit Transaction Gets Deleted - Unable to save SP

I've re-written a stored procedure and when I post the following code
into the existing SP in EM, is saves OK. However, when I re-edit the
SP, the last line 'Commit Transaction' has been removed.

I cannot save the remainder of the SP as it throws error 208 (Invalid
Object name #Max) about two of the temp tables I use when I post the
entire script. It shows in a message box with the header : 'Microsoft
SQL-DMO(ODBC SQLState:42S02)

I haven't posted the full SP nor the structure as it's quite large
(2000 lines), so hopefully I have given enough detail, but my questions
are :

Why does it now have problems with (temp) #Tables ? The use of these
has not changed. All I have done is wrap the script into various
transactions as this helps a lot for performance and tweaked a few
parts later in the SP again for performance.

Also, why does the line get removed once I save the SP ?

If I run this in QA, I get the same errors, so I suspect it's my
script, but don't know where I'm going wrong.

SQL2000 (Need to upgrade the service pack as recently installed on my
PC, so this may help)

Thanks in advance

Ryan

CREATE PROCEDURE [dbo].[JAG_Extract] (@.ExtractYear INTEGER,
@.ExtractMonth INTEGER) AS

BEGIN TRANSACTION

SELECT 0 AS MaxYear, 0 AS MaxMonth INTO #Max

UPDATE #Max SET MaxYear = @.ExtractYear
UPDATE #Max SET MaxMonth = @.ExtractMonth

PRINT 'Stage 1 - ' + Convert(VarChar, GetDate())
CREATE TABLE #Extract (
[DEALER_SOURCE_DATA_ID] INT,
[DSD_YEAR] INT NULL,
[DSD_MONTH] INT NULL,
[DEALER_CODE] VarChar(20),
[FranDealerCode] VarChar(20) NULL,
[Line_No] VarChar(75),
[Current] [numeric](15, 5) NULL,
[YTD] [numeric](15, 5) NULL,
[12Months] [numeric](15, 5) NULL,
[24Months] [numeric](15, 5) NULL,
[Average_YTD] [numeric](15, 5) NULL,
[Average12Months] [numeric](15, 5) NULL,
[Average24Months] [numeric](15, 5) NULL,
[Last_YTD] [numeric](15, 5) NULL,
[Current_STATUS] INT,
[PD1] [numeric](15, 5) NULL,
[PD2] [numeric](15, 5) NULL,
[PD3] [numeric](15, 5) NULL,
[PD4] [numeric](15, 5) NULL,
[PD5] [numeric](15, 5) NULL,
[PD6] [numeric](15, 5) NULL,
[PD7] [numeric](15, 5) NULL,
[PD8] [numeric](15, 5) NULL,
[PD9] [numeric](15, 5) NULL,
[PD10] [numeric](15, 5) NULL,
[PD11] [numeric](15, 5) NULL,
[PD12] [numeric](15, 5) NULL,
[PD13] [numeric](15, 5) NULL,
[PD14] [numeric](15, 5) NULL,
[PD15] [numeric](15, 5) NULL,
[PD16] [numeric](15, 5) NULL,
[PD17] [numeric](15, 5) NULL,
[PD18] [numeric](15, 5) NULL,
[PD19] [numeric](15, 5) NULL,
[PD20] [numeric](15, 5) NULL,
[PD21] [numeric](15, 5) NULL,
[PD22] [numeric](15, 5) NULL,
[PD23] [numeric](15, 5) NULL,
[PD24] [numeric](15, 5) NULL,
[PD25] [numeric](15, 5) NULL,
[PD26] [numeric](15, 5) NULL,
[PD27] [numeric](15, 5) NULL,
[PD28] [numeric](15, 5) NULL,
[PD29] [numeric](15, 5) NULL,
[PD30] [numeric](15, 5) NULL,
[PD31] [numeric](15, 5) NULL,
[PD32] [numeric](15, 5) NULL,
[PD33] [numeric](15, 5) NULL,
[PD34] [numeric](15, 5) NULL,
[PD35] [numeric](15, 5) NULL,
[PD36] [numeric](15, 5) NULL)

INSERT INTO #Extract

SELECT DISTINCT
SD.DEALER_SOURCE_DATA_ID,
SD.DSD_YEAR,
SD.DSD_MONTH,
DN.DEALER_CODE,
DN.FRAN_DEALER_CODE,
DV.FIELD_CODE,
0,
0,
0,
0,
0,
0,
0,
0,
SD.STATUS,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0

FROM
DEALER_NAW DN WITH (NOLOCK)
INNER JOIN DEALER_SOURCE_DATA SD WITH (NOLOCK)
ON DN.DEALER_CODE = SD.DEALER_CODE
INNER JOIN DEALER_SOURCE_DATA_VALUES_Current DV WITH (NOLOCK)
ON SD.DEALER_SOURCE_DATA_ID = DV.DEALER_SOURCE_DATA_ID
AND SD.STATUS < 4096
INNER JOIN DEALER_FIXED_GROUP_RELATION GR WITH (NOLOCK)
ON DN.DEALER_CODE = GR.DEALER_CODE
AND GR.FIXED_GROUP_ID IN
(11,12,13,14,15,16,17,18,23,42,43,44,45,46,47,48,4 9,50,
51,52,53,54,55,56,57,58,59,60,61,106,109,110,111,1 12,
113,114,115,130,131,132,133,134,135,136,137)
GO
COMMIT TRANSACTIONYou need to remove 'GO' keyword.
Its a batch separator so you basically have two independent parts. Second
part is : COMMIT TRANSACTION.

MC

"Ryan" <ryanofford@.hotmail.com> wrote in message
news:1138113402.871088.270810@.g43g2000cwa.googlegr oups.com...
> I've re-written a stored procedure and when I post the following code
> into the existing SP in EM, is saves OK. However, when I re-edit the
> SP, the last line 'Commit Transaction' has been removed.
> I cannot save the remainder of the SP as it throws error 208 (Invalid
> Object name #Max) about two of the temp tables I use when I post the
> entire script. It shows in a message box with the header : 'Microsoft
> SQL-DMO(ODBC SQLState:42S02)
> I haven't posted the full SP nor the structure as it's quite large
> (2000 lines), so hopefully I have given enough detail, but my questions
> are :
> Why does it now have problems with (temp) #Tables ? The use of these
> has not changed. All I have done is wrap the script into various
> transactions as this helps a lot for performance and tweaked a few
> parts later in the SP again for performance.
> Also, why does the line get removed once I save the SP ?
> If I run this in QA, I get the same errors, so I suspect it's my
> script, but don't know where I'm going wrong.
> SQL2000 (Need to upgrade the service pack as recently installed on my
> PC, so this may help)
> Thanks in advance
>
> Ryan
> CREATE PROCEDURE [dbo].[JAG_Extract] (@.ExtractYear INTEGER,
> @.ExtractMonth INTEGER) AS
> BEGIN TRANSACTION
> SELECT 0 AS MaxYear, 0 AS MaxMonth INTO #Max
> UPDATE #Max SET MaxYear = @.ExtractYear
> UPDATE #Max SET MaxMonth = @.ExtractMonth
> PRINT 'Stage 1 - ' + Convert(VarChar, GetDate())
> CREATE TABLE #Extract (
> [DEALER_SOURCE_DATA_ID] INT,
> [DSD_YEAR] INT NULL,
> [DSD_MONTH] INT NULL,
> [DEALER_CODE] VarChar(20),
> [FranDealerCode] VarChar(20) NULL,
> [Line_No] VarChar(75),
> [Current] [numeric](15, 5) NULL,
> [YTD] [numeric](15, 5) NULL,
> [12Months] [numeric](15, 5) NULL,
> [24Months] [numeric](15, 5) NULL,
> [Average_YTD] [numeric](15, 5) NULL,
> [Average12Months] [numeric](15, 5) NULL,
> [Average24Months] [numeric](15, 5) NULL,
> [Last_YTD] [numeric](15, 5) NULL,
> [Current_STATUS] INT,
> [PD1] [numeric](15, 5) NULL,
> [PD2] [numeric](15, 5) NULL,
> [PD3] [numeric](15, 5) NULL,
> [PD4] [numeric](15, 5) NULL,
> [PD5] [numeric](15, 5) NULL,
> [PD6] [numeric](15, 5) NULL,
> [PD7] [numeric](15, 5) NULL,
> [PD8] [numeric](15, 5) NULL,
> [PD9] [numeric](15, 5) NULL,
> [PD10] [numeric](15, 5) NULL,
> [PD11] [numeric](15, 5) NULL,
> [PD12] [numeric](15, 5) NULL,
> [PD13] [numeric](15, 5) NULL,
> [PD14] [numeric](15, 5) NULL,
> [PD15] [numeric](15, 5) NULL,
> [PD16] [numeric](15, 5) NULL,
> [PD17] [numeric](15, 5) NULL,
> [PD18] [numeric](15, 5) NULL,
> [PD19] [numeric](15, 5) NULL,
> [PD20] [numeric](15, 5) NULL,
> [PD21] [numeric](15, 5) NULL,
> [PD22] [numeric](15, 5) NULL,
> [PD23] [numeric](15, 5) NULL,
> [PD24] [numeric](15, 5) NULL,
> [PD25] [numeric](15, 5) NULL,
> [PD26] [numeric](15, 5) NULL,
> [PD27] [numeric](15, 5) NULL,
> [PD28] [numeric](15, 5) NULL,
> [PD29] [numeric](15, 5) NULL,
> [PD30] [numeric](15, 5) NULL,
> [PD31] [numeric](15, 5) NULL,
> [PD32] [numeric](15, 5) NULL,
> [PD33] [numeric](15, 5) NULL,
> [PD34] [numeric](15, 5) NULL,
> [PD35] [numeric](15, 5) NULL,
> [PD36] [numeric](15, 5) NULL)
> INSERT INTO #Extract
> SELECT DISTINCT
> SD.DEALER_SOURCE_DATA_ID,
> SD.DSD_YEAR,
> SD.DSD_MONTH,
> DN.DEALER_CODE,
> DN.FRAN_DEALER_CODE,
> DV.FIELD_CODE,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> SD.STATUS,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0
> FROM
> DEALER_NAW DN WITH (NOLOCK)
> INNER JOIN DEALER_SOURCE_DATA SD WITH (NOLOCK)
> ON DN.DEALER_CODE = SD.DEALER_CODE
> INNER JOIN DEALER_SOURCE_DATA_VALUES_Current DV WITH (NOLOCK)
> ON SD.DEALER_SOURCE_DATA_ID = DV.DEALER_SOURCE_DATA_ID
> AND SD.STATUS < 4096
> INNER JOIN DEALER_FIXED_GROUP_RELATION GR WITH (NOLOCK)
> ON DN.DEALER_CODE = GR.DEALER_CODE
> AND GR.FIXED_GROUP_ID IN
> (11,12,13,14,15,16,17,18,23,42,43,44,45,46,47,48,4 9,50,
> 51,52,53,54,55,56,57,58,59,60,61,106,109,110,111,1 12,
> 113,114,115,130,131,132,133,134,135,136,137)
> GO
> COMMIT TRANSACTION|||Sorted. Thanks for the pointer. I should have spotted that earlier.

Ryan