Database compare

Hi,
I am a novice server 2000 guy. I have two databases running under one
server. One is in production and another one was created by my
predecessor to retire production database and he might have added some
extra tables and fields in the new database. My job is to make sure
that whatever in the current production database is in the new
database. How can I compare the tables and if any data doesn't match
print that raw?
I tried few select ...join but did not work...
Thank you in advance...
ChorI would just like to elaborate more on my quetion...
let's say I have two identical tables with different data...
pubs.dbo.authors has 23 records
tempdb.dbo.authors has 20 records
I want to see the difference. Hope this make sense|||3rd party tool. SQL Compare by RedGate. Free 30 day
trial...www.red-gate.com
HTH. Ryan
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1147788619.613730.8390@.j33g2000cwa.googlegroups.com...
> Hi,
> I am a novice server 2000 guy. I have two databases running under one
> server. One is in production and another one was created by my
> predecessor to retire production database and he might have added some
> extra tables and fields in the new database. My job is to make sure
> that whatever in the current production database is in the new
> database. How can I compare the tables and if any data doesn't match
> print that raw?
> I tried few select ...join but did not work...
> Thank you in advance...
> Chor
>|||1 way would be :-
SELECT * FROM pubs.dbo.authors where UniqueID NOT IN (SELECT UniqueID FROM
tempdb.dbo.authors)
There are far more elaborate solutions and i'm sure i'll get flamed for
using NOT IN rather than EXISTS...
HTH. Ryan
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1147791030.151031.288430@.u72g2000cwu.googlegroups.com...
>I would just like to elaborate more on my quetion...
> let's say I have two identical tables with different data...
> pubs.dbo.authors has 23 records
> tempdb.dbo.authors has 20 records
> I want to see the difference. Hope this make sense
>|||SELECT a.ID, a.CheckSum
>From (Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
FROM pubs.dbo.authors ) a
Inner Join (
Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
FROM tempdb.dbo.authors ) b
On a.ID = b.ID
Where a.CheckSum != b.CheckSum
I tried this query but it doesn't return last three missing records(I
deleted those records from tempdb.dbo.rad_oltp to check if they come
from pubs.dbo.authors table up) ... but nothing returned...|||here are 3 more ways
select * from tempdb..authors t2 right join pubs..authors t1 on
t1.au_id =t2.au_id
where t2.au_id is null
select * from pubs..authors t1 left join tempdb..authors t2 on
t1.au_id =t2.au_id
where t2.au_id is null
select * from pubs..authors t1 where not exists(select * from
tempdb..authors where t1.au_id =au_id)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||SELECT a.ID, a.CheckSum

>From (Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
FROM pubs.dbo.authors ) a
Inner Join (
Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
FROM tempdb.dbo.authors ) b
On a.ID = b.ID
Where a.CheckSum != b.CheckSum
I tried this query but it doesn't return last three missing records(I
deleted those records from tempdb.dbo.rad_oltp to check if they come
from pubs.dbo.authors table up) ... but nothing returned...
My mistake.. there is a typo .. it is tempdb.dbo.authors|||Here we go
--Not in Pubs
select 'DoesNotExistOnProduction',t2.au_id from pubs..authors t1
right join tempdb..authors t2 on t1.au_id =t2.au_id
where t1.au_id is null
union all
--Not in Temp
select 'DoesNotExistInStaging',t1.au_id from pubs..authors t1 left
join tempdb..authors t2 on t1.au_id =t2.au_id
where t2.au_id is null
union all
--Data Mismatch
select 'DataMismatch', t1.au_id from( select BINARY_CHECKSUM(*) as
CheckSum1 ,au_id from pubs..authors) t1
join(
select BINARY_CHECKSUM(*) as CheckSum2,au_id from tempdb..authors) t2
on t1.au_id =t2.au_id
Where CheckSum1 <> CheckSum2
Here is the complete script that I used to create the authors2 table
and modify/add records to test
--let's copy over 20 rows to a table named authors2
select top 20 * into tempdb..authors2 from pubs..authors
--update 5 records by appending X to the au_fnam
set rowcount 5
update tempdb..authors2
set au_fname =au_fname +'X'
set rowcount 0
--let's insert a row that doesn't exist in pubs
insert into tempdb..authors2
select '666-66-6666', au_lname, au_fname, phone, address, city, state,
zip, contract
from tempdb..authors2
where au_id ='172-32-1176'
--The BIG SELECT
--Not in Pubs
select 'DoesNotExistOnProduction',t2.au_id from pubs..authors t1
right join tempdb..authors2 t2 on t1.au_id =t2.au_id
where t1.au_id is null
union all
--Not in Temp
select 'DoesNotExistInStaging',t1.au_id from pubs..authors t1 left
join tempdb..authors2 t2 on t1.au_id =t2.au_id
where t2.au_id is null
union all
--Data Mismatch
select 'DataMismatch', t1.au_id from( select BINARY_CHECKSUM(*) as
CheckSum1 ,au_id from pubs..authors) t1
join(
select BINARY_CHECKSUM(*) as CheckSum2,au_id from tempdb..authors2) t2
on t1.au_id =t2.au_id
Where CheckSum1 <> CheckSum2
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thank you so much.. that worked!!
You made my day|||No problem
Denis the SQL Menace
http://sqlservercode.blogspot.com/

Database compare

Hi,
I am a novice server 2000 guy. I have two databases running under one
server. One is in production and another one was created by my
predecessor to retire production database and he might have added some
extra tables and fields in the new database. My job is to make sure
that whatever in the current production database is in the new
database. How can I compare the tables and if any data doesn't match
print that raw?
I tried few select ...join but did not work...
Thank you in advance...
ChorI would just like to elaborate more on my quetion...
let's say I have two identical tables with different data...
pubs.dbo.authors has 23 records
tempdb.dbo.authors has 20 records
I want to see the difference. Hope this make sense|||3rd party tool. SQL Compare by RedGate. Free 30 day
trial...www.red-gate.com
--
HTH. Ryan
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1147788619.613730.8390@.j33g2000cwa.googlegroups.com...
> Hi,
> I am a novice server 2000 guy. I have two databases running under one
> server. One is in production and another one was created by my
> predecessor to retire production database and he might have added some
> extra tables and fields in the new database. My job is to make sure
> that whatever in the current production database is in the new
> database. How can I compare the tables and if any data doesn't match
> print that raw?
> I tried few select ...join but did not work...
> Thank you in advance...
> Chor
>|||1 way would be :-
SELECT * FROM pubs.dbo.authors where UniqueID NOT IN (SELECT UniqueID FROM
tempdb.dbo.authors)
There are far more elaborate solutions and i'm sure i'll get flamed for
using NOT IN rather than EXISTS...
--
HTH. Ryan
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1147791030.151031.288430@.u72g2000cwu.googlegroups.com...
>I would just like to elaborate more on my quetion...
> let's say I have two identical tables with different data...
> pubs.dbo.authors has 23 records
> tempdb.dbo.authors has 20 records
> I want to see the difference. Hope this make sense
>|||SELECT a.ID, a.CheckSum
>From (Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
FROM pubs.dbo.authors ) a
Inner Join (
Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
FROM tempdb.dbo.authors ) b
On a.ID = b.ID
Where a.CheckSum != b.CheckSum
I tried this query but it doesn't return last three missing records(I
deleted those records from tempdb.dbo.rad_oltp to check if they come
from pubs.dbo.authors table up) ... but nothing returned...|||here are 3 more ways
select * from tempdb..authors t2 right join pubs..authors t1 on
t1.au_id =t2.au_id
where t2.au_id is null
select * from pubs..authors t1 left join tempdb..authors t2 on
t1.au_id =t2.au_id
where t2.au_id is null
select * from pubs..authors t1 where not exists(select * from
tempdb..authors where t1.au_id =au_id)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||SELECT a.ID, a.CheckSum
>From (Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
FROM pubs.dbo.authors ) a
Inner Join (
Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
FROM tempdb.dbo.authors ) b
On a.ID = b.ID
Where a.CheckSum != b.CheckSum
I tried this query but it doesn't return last three missing records(I
deleted those records from tempdb.dbo.rad_oltp to check if they come
from pubs.dbo.authors table up) ... but nothing returned...
My mistake.. there is a typo .. it is tempdb.dbo.authors|||Here we go
--Not in Pubs
select 'DoesNotExistOnProduction',t2.au_id from pubs..authors t1
right join tempdb..authors t2 on t1.au_id =t2.au_id
where t1.au_id is null
union all
--Not in Temp
select 'DoesNotExistInStaging',t1.au_id from pubs..authors t1 left
join tempdb..authors t2 on t1.au_id =t2.au_id
where t2.au_id is null
union all
--Data Mismatch
select 'DataMismatch', t1.au_id from( select BINARY_CHECKSUM(*) as
CheckSum1 ,au_id from pubs..authors) t1
join(
select BINARY_CHECKSUM(*) as CheckSum2,au_id from tempdb..authors) t2
on t1.au_id =t2.au_id
Where CheckSum1 <> CheckSum2
Here is the complete script that I used to create the authors2 table
and modify/add records to test
--let's copy over 20 rows to a table named authors2
select top 20 * into tempdb..authors2 from pubs..authors
--update 5 records by appending X to the au_fnam
set rowcount 5
update tempdb..authors2
set au_fname =au_fname +'X'
set rowcount 0
--let's insert a row that doesn't exist in pubs
insert into tempdb..authors2
select '666-66-6666', au_lname, au_fname, phone, address, city, state,
zip, contract
from tempdb..authors2
where au_id ='172-32-1176'
--The BIG SELECT
--Not in Pubs
select 'DoesNotExistOnProduction',t2.au_id from pubs..authors t1
right join tempdb..authors2 t2 on t1.au_id =t2.au_id
where t1.au_id is null
union all
--Not in Temp
select 'DoesNotExistInStaging',t1.au_id from pubs..authors t1 left
join tempdb..authors2 t2 on t1.au_id =t2.au_id
where t2.au_id is null
union all
--Data Mismatch
select 'DataMismatch', t1.au_id from( select BINARY_CHECKSUM(*) as
CheckSum1 ,au_id from pubs..authors) t1
join(
select BINARY_CHECKSUM(*) as CheckSum2,au_id from tempdb..authors2) t2
on t1.au_id =t2.au_id
Where CheckSum1 <> CheckSum2
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thank you so much.. that worked!! :)
You made my day:):):)|||No problem
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||One more question... I tried using more then one column to check the
data mismatch...
--Data Mismatch
select 'DataMismatch', t1.au_id, t1.au_lname, t1.au_fname from( select
BINARY_CHECKSUM(*) as
CheckSum1 ,au_id, au_lname, au_fname from pubs..authors) t1
join(
select BINARY_CHECKSUM(*) as CheckSum2,au_id, from tempdb..authors2)
t2
on t1.au_id =t2.au_id
Where CheckSum1 <> CheckSum2
did not work as I intended...|||Sorry what I just posted was a stupid question... my mistake.....
ddddduhhhh|||CHECKSUM(*) will use all columns
Denis the SQL Menace
http://sqlservercode.blogspot.com/sql

Database being set to SIMPLE Recovery (no MSDB)

My production seems to, either upon admin rebooting it for updates, or
for someother reason set the Recovermy Mode FROM Full TO Simple?
Weird. How can I track down what is doing this? its SQL 2000 SP4.
Thanks
Erik> My production seems to, either upon admin rebooting it for updates, or
> for someother reason set the Recovermy Mode FROM Full TO Simple?
> Weird. How can I track down what is doing this? its SQL 2000 SP4.
Use the Profiler - track ALTER DATABASE yourdb SET RECOVERY command.
--
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message

Database being set to SIMPLE Recovery (no MSDB)

My production seems to, either upon admin rebooting it for updates, or
for someother reason set the Recovermy Mode FROM Full TO Simple?
Weird. How can I track down what is doing this? its SQL 2000 SP4.
Thanks
Erik
> My production seems to, either upon admin rebooting it for updates, or
> for someother reason set the Recovermy Mode FROM Full TO Simple?
> Weird. How can I track down what is doing this? its SQL 2000 SP4.
Use the Profiler - track ALTER DATABASE yourdb SET RECOVERY command.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message

Database being set to SIMPLE Recovery (no MSDB)

My production seems to, either upon admin rebooting it for updates, or
for someother reason set the Recovermy Mode FROM Full TO Simple?
Weird. How can I track down what is doing this? its SQL 2000 SP4.
Thanks
Erik> My production seems to, either upon admin rebooting it for updates, or
> for someother reason set the Recovermy Mode FROM Full TO Simple?
> Weird. How can I track down what is doing this? its SQL 2000 SP4.
Use the Profiler - track ALTER DATABASE yourdb SET RECOVERY command.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message

Database become suspect..

Hello,
One of my production database become suspect. Here is the messages taken from event veiwer-> Application Log. This is all I can share with you. Please help.
Regards
Nurullah
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D
9/18/2003 12:27:57 PM MSSQLSERVER Information
-2 17055 N/A SERVERA "19013 :
SQL server listening on TCP, Shared Memory, Named Pipes.
"
--
9/18/2003 12:27:57 PM MSSQLSERVER Information
-2 17055 N/A SERVERA 17126 :
SQL Server is ready for client connections =09
--
9/18/2003 12:28:08 PM MSSQLSERVER Information
-2 17055 N/A SERVERA 8128:00:00
Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'. =09
--
9/18/2003 12:28:08 PM MSSQLSERVER Information
-2 17055 N/A SERVERA 8128:00:00
--
9/18/2003 12:33:29 PM MSSQLSERVER Error -2
17055 N/A SERVERA "17066 :
SQL Server Assertion: File: <page.cpp>, line=3D2610 Failed Assertion =3D 'spaceNeeded <=3D spaceContig && spaceNeeded <=3D space_usable'.
"
--
9/18/2003 12:33:29 PM MSSQLSERVER Error -2
17055 N/A SERVERA "18052 :
Error: 3624, Severity: 20, State: 1.
"
--
9/18/2003 12:33:29 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 644, Severity: 21, State: 5
Could not find the index entry for RID '1618d4000000100' in index page (1:9759), index ID 2, database 'eBill_Main'. "
--
9/18/2003 12:33:29 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 3314, Severity: 21, State: 4
Error while undoing logged operation in database 'eBill_Main'. Error at log record ID (25502:819:174). "
--
9/18/2003 12:33:31 PM MSSQLSERVER Error -2
17055 N/A SERVERA "17066 :
SQL Server Assertion: File: <scanrid.cpp>, line=3D321 Failed Assertion =3D 'm_len !=3D 0'.
"
--
9/18/2003 12:33:31 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 644, Severity: 21, State: 5
Could not find the index entry for RID '=01' in index page (1:9759), index ID 2, database 'eBill_Main'. "
--
9/18/2003 12:33:34 PM MSSQLSERVER Error -2
17055 N/A SERVERA "17066 :
SQL Server Assertion: File: <logscan.cpp>, line=3D3063 Failed Assertion =3D '(m_lastLSN =3D=3D NullLSN) || (m_lastLSN > m_curLSN)'.
"
--
9/18/2003 12:33:36 PM MSSQLSERVER Error -2
17055 N/A SERVERA "17066 :
SQL Server Assertion: File: <logscan.cpp>, line=3D3282 Failed Assertion =3D 'm_lastLSN =3D=3D NullLSN || startLSN < m_lastLSN'.
"
--
9/18/2003 12:33:36 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 9004, Severity: 23, State: 7
An error occurred while processing the log for database 'eBill_Main'. "
--
9/18/2003 12:33:36 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 3314, Severity: 21, State: 4
Error while undoing logged operation in database 'eBill_Main'. Error at log record ID (25502:819:174). "
--
9/18/2003 12:33:36 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 9001, Severity: 21, State: 1
The log for database 'eBill_Main' is not available. "
--An assertion is when a piece of SQL Server's C Code goes west.
This line interested me though
9/18/2003 12:33:29 PM MSSQLSERVER Error -2
17055 N/A SERVERA "17066 :
SQL Server Assertion: File: <page.cpp>, line=2610
Failed Assertion = 'spaceNeeded <= spaceContig &&
spaceNeeded <= space_usable'.
Do you have enough disk space?
What version SQL Server and SP are you running ?
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

Database back-up

Hi there,
What we basically want is seperating the development and production
servers. I need to transfer all the data to the new server from the
existing server.
My question is that what's a good way to transfer schema, tables and
data from one SQL Server to another. I have tried export option in the
enterprise manager, but I guess it doesn't transfer things like pk,
relationships etc or does it?
Once I have all data on the other server, I can set up a Trans
Replication, so that I get same data on both servers.
Thanks in advance.
Regards,
Ricky Singh
--
Posted via http://dbforums.comYou could create backups of your main databases and restore them into other
environments. Make sure you desensitize info like credit card numbers etc.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Ricky_Singh" <member32195@.dbforums.com> wrote in message
news:3059366.1057005218@.dbforums.com...
Hi there,
What we basically want is seperating the development and production
servers. I need to transfer all the data to the new server from the
existing server.
My question is that what's a good way to transfer schema, tables and
data from one SQL Server to another. I have tried export option in the
enterprise manager, but I guess it doesn't transfer things like pk,
relationships etc or does it?
Once I have all data on the other server, I can set up a Trans
Replication, so that I get same data on both servers.
Thanks in advance.
Regards,
Ricky Singh
--
Posted via http://dbforums.com|||Originally posted by Quentin Ran
> IMO backing up and then restore on the new machine is a easy,
> convenient
> way.
> Quentin
> Thanks for the replies. I m a newbie on SQL Server. Can I back-up
> using Tools-Backup database on enterprise manager and then save the
> file over WAN' I was trying doing that but cudn't see the mapped
> network drive over the WAN!!
>
> "Ricky_Singh" wrote in message
> news:3059366.1057005218@.dbforums.com"]news:3059366.1057005218@.d-
> bforums.com[/url]...
> > Hi there,
> > What we basically want is seperating the development and
> production
> > servers. I need to transfer all the data to the new server from
> the
> > existing server.
> > My question is that what's a good way to transfer schema, tables
> and
> > data from one SQL Server to another. I have tried export option
> in the
> > enterprise manager, but I guess it doesn't transfer things like
> pk,
> > relationships etc or does it?
> > Once I have all data on the other server, I can set up a
> Trans
> > Replication, so that I get same data on both servers.
> > Thanks in advance.
> > Regards,
> > Ricky Singh
> > --
> Posted via
http://dbforums.com/http://dbforums.com
Posted via http://dbforums.com|||Ricky,
I never tried that. I believe you can backup to a mapped LAN drive. If you
have this option, you can then transfer the file over to the WAN site.
Quentin
"Ricky_Singh" <member32195@.dbforums.com> wrote in message
news:3059937.1057009483@.dbforums.com...
> Originally posted by Quentin Ran
> > IMO backing up and then restore on the new machine is a easy,
> > convenient
> > way.
> >
> > Quentin
> >
> > Thanks for the replies. I m a newbie on SQL Server. Can I back-up
> > using Tools-Backup database on enterprise manager and then save the
> > file over WAN' I was trying doing that but cudn't see the mapped
> > network drive over the WAN!!
> >
> >
> > "Ricky_Singh" wrote in message
> > news:3059366.1057005218@.dbforums.com"]news:3059366.1057005218@.d-
> > bforums.com[/url]...
> > > Hi there,
> > > What we basically want is seperating the development and
> > production
> > > servers. I need to transfer all the data to the new server from
> > the
> > > existing server.
> > > My question is that what's a good way to transfer schema, tables
> > and
> > > data from one SQL Server to another. I have tried export option
> > in the
> > > enterprise manager, but I guess it doesn't transfer things like
> > pk,
> > > relationships etc or does it?
> > > Once I have all data on the other server, I can set up a
> > Trans
> > > Replication, so that I get same data on both servers.
> > > Thanks in advance.
> > > Regards,
> > > Ricky Singh
> > > --
> > Posted via
> http://dbforums.com/http://dbforums.com
>
> --
> Posted via http://dbforums.com|||Originally posted by Quentin Ran
> Ricky,
> I never tried that. I believe you can backup to a mapped LAN
> drive. If you
> have this option, you can then transfer the file over to the WAN site.
> Quentin
> Thanks a lot Quentin, I ll try that. Another question I have is that
> if I make a copy of the database file, Can I use that copy for restore
> at another SQL Server location.
> Thanks again!!
> "Ricky_Singh" wrote in message
> news:3059937.1057009483@.dbforums.com"]news:3059937.1057009483@.d-
> bforums.com[/url]...
> > Originally posted by Quentin Ran
> > > IMO backing up and then restore on the new machine is a
> easy,
> > > convenient
> > > way.
> > >
> > > Quentin
> > >
> > > Thanks for the replies. I m a newbie on SQL Server. Can I
> back-up
> > > using Tools-Backup database on enterprise manager and then
> save the
> > > file over WAN' I was trying doing that but cudn't see the
> mapped
> > > network drive over the WAN!!
> > >
> > >
> > > "Ricky_Singh" wrote in message
> > > news:3059366.1057005218@.dbforums.com"]news:3059366.1057-
> 005218@.dbforums.com[/url]"]news:3059366.1057005218@.d-"]news-
> :3059366.1057005218@.d-[/url]
> > > bforums.com[/url]...
> > > > Hi there,
> > > > What we basically want is seperating the development
> and
> > > production
> > > > servers. I need to transfer all the data to the new server
> from
> > > the
> > > > existing server.
> > > > My question is that what's a good way to transfer schema,
> tables
> > > and
> > > > data from one SQL Server to another. I have tried export
> option
> > > in the
> > > > enterprise manager, but I guess it doesn't transfer things
> like
> > > pk,
> > > > relationships etc or does it?
> > > > Once I have all data on the other server, I can set up
> a
> > > Trans
> > > > Replication, so that I get same data on both servers.
> > > > Thanks in advance.
> > > > Regards,
> > > > Ricky Singh
> > > > --
> > > Posted via
> > http://dbforums.com/http://dbforums.com"]http://dbfor-
> ums.com/http://dbforums.com[/url]
> > --
> Posted via
http://dbforums.com/http://dbforums.com
Posted via http://dbforums.com|||Certainly.
"Ricky_Singh" <member32195@.dbforums.com> wrote in message
news:3062421.1057073733@.dbforums.com...
> Originally posted by Quentin Ran
> > Ricky,
> >
> > I never tried that. I believe you can backup to a mapped LAN
> > drive. If you
> > have this option, you can then transfer the file over to the WAN site.
> >
> > Quentin
> >
> > Thanks a lot Quentin, I ll try that. Another question I have is that
> > if I make a copy of the database file, Can I use that copy for restore
> > at another SQL Server location.
> > Thanks again!!
> >
> > "Ricky_Singh" wrote in message
> > news:3059937.1057009483@.dbforums.com"]news:3059937.1057009483@.d-
> > bforums.com[/url]...
> > > Originally posted by Quentin Ran
> > > > IMO backing up and then restore on the new machine is a
> > easy,
> > > > convenient
> > > > way.
> > > >
> > > > Quentin
> > > >
> > > > Thanks for the replies. I m a newbie on SQL Server. Can I
> > back-up
> > > > using Tools-Backup database on enterprise manager and then
> > save the
> > > > file over WAN' I was trying doing that but cudn't see the
> > mapped
> > > > network drive over the WAN!!
> > > >
> > > >
> > > > "Ricky_Singh" wrote in message
> > > > news:3059366.1057005218@.dbforums.com"]news:3059366.1057-
> > 005218@.dbforums.com[/url]"]news:3059366.1057005218@.d-"]news-
> > :3059366.1057005218@.d-[/url]
> > > > bforums.com[/url]...
> > > > > Hi there,
> > > > > What we basically want is seperating the development
> > and
> > > > production
> > > > > servers. I need to transfer all the data to the new server
> > from
> > > > the
> > > > > existing server.
> > > > > My question is that what's a good way to transfer schema,
> > tables
> > > > and
> > > > > data from one SQL Server to another. I have tried export
> > option
> > > > in the
> > > > > enterprise manager, but I guess it doesn't transfer things
> > like
> > > > pk,
> > > > > relationships etc or does it?
> > > > > Once I have all data on the other server, I can set up
> > a
> > > > Trans
> > > > > Replication, so that I get same data on both servers.
> > > > > Thanks in advance.
> > > > > Regards,
> > > > > Ricky Singh
> > > > > --
> > > > Posted via
> > > http://dbforums.com/http://dbforums.com"]http://dbfor-
> > ums.com/http://dbforums.com[/url]
> > > --
> > Posted via
> http://dbforums.com/http://dbforums.com
>
> --
> Posted via http://dbforums.com|||Originally posted by Quentin Ran
> Certainly.
> The problem I m facing is that when I try to back-up the server, I am
> unable to see the mapped network drive. I see a dialog box with the
> server name and the drives on that server, but no mapped drives.
> Thanks for the help.
> "Ricky_Singh" wrote in message
> news:3062421.1057073733@.dbforums.com"]news:3062421.1057073733@.d-
> bforums.com[/url]...
> > Originally posted by Quentin Ran
> > > Ricky,
> > >
> > > I never tried that. I believe you can backup to a mapped
> LAN
> > > drive. If you
> > > have this option, you can then transfer the file over to the
> WAN site.
> > >
> > > Quentin
> > >
> > > Thanks a lot Quentin, I ll try that. Another question I have
> is that
> > > if I make a copy of the database file, Can I use that copy for
> restore
> > > at another SQL Server location.
> > > Thanks again!!
> > >
> > > "Ricky_Singh" wrote in message
> > > news:3059937.1057009483@.dbforums.com"]news:3059937.1057-
> 009483@.dbforums.com[/url]"]news:3059937.1057009483@.d-"]news-
> :3059937.1057009483@.d-[/url]
> > > bforums.com[/url]...
> > > > Originally posted by Quentin Ran
> > > > > IMO backing up and then restore on the new machine is
> a
> > > easy,
> > > > > convenient
> > > > > way.
> > > > >
> > > > > Quentin
> > > > >
> > > > > Thanks for the replies. I m a newbie on SQL Server. Can
> I
> > > back-up
> > > > > using Tools-Backup database on enterprise manager and
> then
> > > save the
> > > > > file over WAN' I was trying doing that but cudn't see
> the
> > > mapped
> > > > > network drive over the WAN!!
> > > > >
> > > > >
> > > > > "Ricky_Singh" wrote in message
> > > > > news:3059366.1057005218@.dbforums.com"]news:3059366.-
> 1057005218@.dbforums.com[/url]"]news:3059366.1057-"]news:305-
> 9366.1057-[/url]
> > > 005218@.dbforums.com[/url]"]news:3059366.1057005218@.-
> d-news:3059366.1057005218@.d-"]news-
> > > :3059366.1057005218@.d-[/url]
> > > > > bforums.com[/url]...
> > > > > > Hi there,
> > > > > > What we basically want is seperating the
> development
> > > and
> > > > > production
> > > > > > servers. I need to transfer all the data to the new
> server
> > > from
> > > > > the
> > > > > > existing server.
> > > > > > My question is that what's a good way to transfer
> schema,
> > > tables
> > > > > and
> > > > > > data from one SQL Server to another. I have tried
> export
> > > option
> > > > > in the
> > > > > > enterprise manager, but I guess it doesn't transfer
> things
> > > like
> > > > > pk,
> > > > > > relationships etc or does it?
> > > > > > Once I have all data on the other server, I can set
> up
> > > a
> > > > > Trans
> > > > > > Replication, so that I get same data on both
> servers.
> > > > > > Thanks in advance.
> > > > > > Regards,
> > > > > > Ricky Singh
> > > > > > --
> > > > > Posted via
> > > > http://dbforums.com/http://dbforums.com"]http://d-
> bforums.com/http://dbforums.com[/url]"]http://dbfor-/"]http-
> ://dbfor-[/url]
> > > ums.com/http://dbforums.com[/url]"]http://dbforums.-
> com[/url][/url]
> > > > --
> > > Posted via
> > http://dbforums.com/http://dbforums.com"]http://dbfor-
> ums.com/http://dbforums.com[/url]
> > --
> Posted via
http://dbforums.com/http://dbforums.com
Posted via http://dbforums.comsql

DataBase Backup

SQL Server 2000
We have a db maintenance job which backup all the db's in a Production
Server to a remote server.
Production Server - Windows 2000 server
Remote Box - Windows 2003 Standard Edition
We recently applied SP1 to Windows 2003 box (Remote Box), after that Db
maintenance job with error message 'Operating system error 64 (The specified
network is no longer available..)
Certain days it will backup all databases but with this error message.
Any thoughts/suggestions/ideas
Thanks
MikeMS User wrote:
> SQL Server 2000
> We have a db maintenance job which backup all the db's in a Production
> Server to a remote server.
> Production Server - Windows 2000 server
> Remote Box - Windows 2003 Standard Edition
> We recently applied SP1 to Windows 2003 box (Remote Box), after that Db
> maintenance job with error message 'Operating system error 64 (The specifi
ed
> network is no longer available..)
> Certain days it will backup all databases but with this error message.
> Any thoughts/suggestions/ideas
> Thanks
> Mike
>
I'm not sure this is a SQL problem, sounds more like a network or OS
problem on the remote side. You're losing the connection to the remote
box during the file copy (the backup) that SQL is doing. Search Google
for "The specified network is no longer available", lots of hits.

Database backup

I am currently building a new production server. For that I need to move
the databases and set up replication between the SQL Server databases.
I have backed-up the databases on the present server.
How can I now restore these databases on the other(new) server? I dont
have those databases already on that server and SQL Server asks which
database I wanna restore, if try to start the restoration, so basically
it assumes the database is already on the server!!
Do I have to create the databases on the other(new) Server before a
restore? If yes, how? I m running SQL 2k on Win 2k on both servers.
A point to a good article or explanation will be highly appreciated.
Best Regards!
--
Posted via http://dbforums.comAssuming that both servers can be online at the same time, go to the new
server and run the T-SQL RESTORE DATABASE statement. In the FROM line of
the RESTORE statement, use DISK=<UNC path pointing to backup file on old
server> In effect, you will be pulling the database backup from the old
server to the new one.
Should be something like:
RESTORE DATABASE mydatabase
FROM DISK = '\\oldserver\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Mydbbackup.bak'
Books online points out that the account under which you are running SQL
Server on the new server must have READ access to the remote "old" server's
backup file.
See "RESTORE" in books online for more info.
HTH,
Rob
"Ricky_Singh" <member32195@.dbforums.com> wrote in message
news:3146187.1059020556@.dbforums.com...
> I am currently building a new production server. For that I need to move
> the databases and set up replication between the SQL Server databases.
> I have backed-up the databases on the present server.
> How can I now restore these databases on the other(new) server? I dont
> have those databases already on that server and SQL Server asks which
> database I wanna restore, if try to start the restoration, so basically
> it assumes the database is already on the server!!
> Do I have to create the databases on the other(new) Server before a
> restore? If yes, how? I m running SQL 2k on Win 2k on both servers.
> A point to a good article or explanation will be highly appreciated.
> Best Regards!
> --
> Posted via http://dbforums.com|||It is asking which database you wish to create. It doesn't
have to exist. If it does exists you have to check the
option to overwrite.
Also look at the next tab which holds the location of the
files to create - you may have to change them if the paths
are different on this m/c.
>--Original Message--
>I am currently building a new production server. For that
I need to move
>the databases and set up replication between the SQL
Server databases.
>I have backed-up the databases on the present server.
>How can I now restore these databases on the other(new)
server? I dont
>have those databases already on that server and SQL
Server asks which
>database I wanna restore, if try to start the
restoration, so basically
>it assumes the database is already on the server!!
>Do I have to create the databases on the other(new)
Server before a
>restore? If yes, how? I m running SQL 2k on Win 2k on
both servers.
>A point to a good article or explanation will be highly
appreciated.
>Best Regards!
>--
>Posted via http://dbforums.com
>.
>

DataBase Backup

SQL Server 2000
We have a db maintenance job which backup all the db's in a Production
Server to a remote server.
Production Server - Windows 2000 server
Remote Box - Windows 2003 Standard Edition
We recently applied SP1 to Windows 2003 box (Remote Box), after that Db
maintenance job with error message 'Operating system error 64 (The specified
network is no longer available..)
Certain days it will backup all databases but with this error message.
Any thoughts/suggestions/ideas
Thanks
MikeMS User wrote:
> SQL Server 2000
> We have a db maintenance job which backup all the db's in a Production
> Server to a remote server.
> Production Server - Windows 2000 server
> Remote Box - Windows 2003 Standard Edition
> We recently applied SP1 to Windows 2003 box (Remote Box), after that Db
> maintenance job with error message 'Operating system error 64 (The specified
> network is no longer available..)
> Certain days it will backup all databases but with this error message.
> Any thoughts/suggestions/ideas
> Thanks
> Mike
>
I'm not sure this is a SQL problem, sounds more like a network or OS
problem on the remote side. You're losing the connection to the remote
box during the file copy (the backup) that SQL is doing. Search Google
for "The specified network is no longer available", lots of hits.

Database Archive Data

I have a production database thats approximately 50 GB in
size with SQL Server 2000 Enterprise Edition. I truncate
the non-essential tables nightly. Does Microsoft
recommend partitioning tables for core active data in the
tables thats access by the user (Active), the other part
of the partition as data that is presently not used by
the application (Non-Active).
What archiving technique does Microsoft recommend?
Thank You,
Mike
There is no general rule of thumb or official recommendation for things like
this. There are so many things that are app or design specific that can
affect this decision. The choice to partition / archive / truncate etc. is
really dependant on what you need to do with this data. If you almost never
have to access it again then it is usually not a bad idea to archive it to
files or another database. If your current operation and hardware can
support it either way, you may choose to just leave it where it is. 50GB is
not that large of a database these days and the sheer size alone is really
no indication that you should do one thing over another.
Andrew J. Kelly SQL MVP
"MIke" <anonymous@.discussions.microsoft.com> wrote in message
news:67aa01c482d0$c6706680$a601280a@.phx.gbl...
> I have a production database thats approximately 50 GB in
> size with SQL Server 2000 Enterprise Edition. I truncate
> the non-essential tables nightly. Does Microsoft
> recommend partitioning tables for core active data in the
> tables thats access by the user (Active), the other part
> of the partition as data that is presently not used by
> the application (Non-Active).
> What archiving technique does Microsoft recommend?
> Thank You,
> Mike

Database Archive Data

I have a production database thats approximately 50 GB in
size with SQL Server 2000 Enterprise Edition. I truncate
the non-essential tables nightly. Does Microsoft
recommend partitioning tables for core active data in the
tables thats access by the user (Active), the other part
of the partition as data that is presently not used by
the application (Non-Active).
What archiving technique does Microsoft recommend?
Thank You,
MikeThere is no general rule of thumb or official recommendation for things like
this. There are so many things that are app or design specific that can
affect this decision. The choice to partition / archive / truncate etc. is
really dependant on what you need to do with this data. If you almost never
have to access it again then it is usually not a bad idea to archive it to
files or another database. If your current operation and hardware can
support it either way, you may choose to just leave it where it is. 50GB is
not that large of a database these days and the sheer size alone is really
no indication that you should do one thing over another.
Andrew J. Kelly SQL MVP
"MIke" <anonymous@.discussions.microsoft.com> wrote in message
news:67aa01c482d0$c6706680$a601280a@.phx.gbl...
> I have a production database thats approximately 50 GB in
> size with SQL Server 2000 Enterprise Edition. I truncate
> the non-essential tables nightly. Does Microsoft
> recommend partitioning tables for core active data in the
> tables thats access by the user (Active), the other part
> of the partition as data that is presently not used by
> the application (Non-Active).
> What archiving technique does Microsoft recommend?
> Thank You,
> Mike

Database Archive Data

I have a production database thats approximately 50 GB in
size with SQL Server 2000 Enterprise Edition. I truncate
the non-essential tables nightly. Does Microsoft
recommend partitioning tables for core active data in the
tables thats access by the user (Active), the other part
of the partition as data that is presently not used by
the application (Non-Active).
What archiving technique does Microsoft recommend?
Thank You,
MikeThere is no general rule of thumb or official recommendation for things like
this. There are so many things that are app or design specific that can
affect this decision. The choice to partition / archive / truncate etc. is
really dependant on what you need to do with this data. If you almost never
have to access it again then it is usually not a bad idea to archive it to
files or another database. If your current operation and hardware can
support it either way, you may choose to just leave it where it is. 50GB is
not that large of a database these days and the sheer size alone is really
no indication that you should do one thing over another.
--
Andrew J. Kelly SQL MVP
"MIke" <anonymous@.discussions.microsoft.com> wrote in message
news:67aa01c482d0$c6706680$a601280a@.phx.gbl...
> I have a production database thats approximately 50 GB in
> size with SQL Server 2000 Enterprise Edition. I truncate
> the non-essential tables nightly. Does Microsoft
> recommend partitioning tables for core active data in the
> tables thats access by the user (Active), the other part
> of the partition as data that is presently not used by
> the application (Non-Active).
> What archiving technique does Microsoft recommend?
> Thank You,
> Mike

data update suggestions

We have production database (1TB, sql server 2005) and we will receive daily
updates from a vendor. All the updates will be imported in another database
first (source db). What would be the best way to apply the updated
information to the from the source DB to the production DB (example of
updates are: new products or just price changes on existing products
etc...). I was thinking about unidirectional merge database (src db to prod
db) but if someone can give some suggestions it would be greatly appreciated!
Thank you all
--
System Engineer
MSc in CS, MCSE4, IBM Certified MQ 5.3 Administrator
"I saw it work in a cartoon once so I am pretty sure I can do it."
I guess you should start with estimating amount of data being transfered, it
frequency and concurrency requirement.
the more data and more frequent and how fast you have to be "in sync"-
replication is most likely scenario you should go with. Less data and less
frequent - a custom app or SSIS package might do the trick...
"Sas" <Sas@.discussions.microsoft.com> wrote in message
news:0F14BBEA-A720-40FF-88A3-8C7B126DBC46@.microsoft.com...
> We have production database (1TB, sql server 2005) and we will receive
> daily
> updates from a vendor. All the updates will be imported in another
> database
> first (source db). What would be the best way to apply the updated
> information to the from the source DB to the production DB (example of
> updates are: new products or just price changes on existing products
> etc...). I was thinking about unidirectional merge database (src db to
> prod
> db) but if someone can give some suggestions it would be greatly
> appreciated!
> Thank you all
> --
> --
> System Engineer
> MSc in CS, MCSE4, IBM Certified MQ 5.3 Administrator
> "I saw it work in a cartoon once so I am pretty sure I can do it."
|||Why are you using merge when the data flow is in one direction. You should
be using transactional for this. Expect at least twice the amount of data
flow that hits the publisher to be applied on the subscriber. However this
can vary widely depending on how many rows are affected by each DML on the
publisher and whether text or binary data is involved.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Rafael Lenartowicz" <rafaell@.rcl-consulting.com> wrote in message
news:OrX0acKfHHA.1252@.TK2MSFTNGP04.phx.gbl...
>I guess you should start with estimating amount of data being transfered,
>it frequency and concurrency requirement.
> the more data and more frequent and how fast you have to be "in sync"-
> replication is most likely scenario you should go with. Less data and less
> frequent - a custom app or SSIS package might do the trick...
> "Sas" <Sas@.discussions.microsoft.com> wrote in message
> news:0F14BBEA-A720-40FF-88A3-8C7B126DBC46@.microsoft.com...
>
|||How are the updates imported into the source db? If they are indeed applied
as TSQL updates (as opposed to tables being overwritten) then look at
transactional replication. If the schema of the source db and production db
are the same and you have PKs on all the involved tables then things will be
straightforward.
Paul Ibison