Creating an exact replica of the db

Hi,
I need to create a mirror image of one our database. So that whatever data
is there in our db... show up in the other db at each second.
Is there a way this can be done..? And how.
Thanks
pmud
Hi,
You could implement Transactional Replication. See Transactional Replication
topic in Books online.
Thanks
Hari
SQL Server MVP
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
> Hi,
> I need to create a mirror image of one our database. So that whatever data
> is there in our db... show up in the other db at each second.
> Is there a way this can be done..? And how.
> Thanks
> --
> pmud
|||one second latency? Not sure if that can be accomplished, but you will need
to use replication to get you close.
What is the purpose here? Disaster recovery? Reporting server?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
> Hi,
> I need to create a mirror image of one our database. So that whatever data
> is there in our db... show up in the other db at each second.
> Is there a way this can be done..? And how.
> Thanks
> --
> pmud
|||Just to add, replication will only copy data, but not other structural
changes and new proceures, new tables etc. What's the requirement? If you
want a 'mirror' copy as you said, may be logshipping is what you are after
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
> Hi,
> I need to create a mirror image of one our database. So that whatever data
> is there in our db... show up in the other db at each second.
> Is there a way this can be done..? And how.
> Thanks
> --
> pmud
|||Hi ,
I want a mirror image for everything not only data but all stored
procedures, views ( and everythjing else) that are added in one db should be
automatically reflected in the mirror image too . 1 second latency is not
necessary; a few minutes will do too.
The mirror image is needed for testing purposes. We develop our sites on a
Dev db. Then port all the data to live. But we are now planning to introduce
another db layer to testing. So that all our sites are develpoed in the dev
db. We have a mirror image of the LIVE db running. Then we migrate everything
from DEV db to the MIRROR image db. Then from there we move to the live db .
This is because once everything is working on the mirror image, it will work
on live too since both are the exact same.
What is the best thing for this... replication? or Logshipping or something
else.? And where can I find some information about it?
Thanks for your help.
pmud
"Narayana Vyas Kondreddi" wrote:

> Just to add, replication will only copy data, but not other structural
> changes and new proceures, new tables etc. What's the requirement? If you
> want a 'mirror' copy as you said, may be logshipping is what you are after
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
>
>
|||In your case, you need to look into log-shipping.
Check this out,
http://www.microsoft.com/technet/pro...y/hasog02.mspx
FYI, in SQL Server 2005 there is a new concept called database mirroring,
which comes close to your requirement. Google it to get more info.
- - - - - - - - -
Thanks
Yogish
"pmud" wrote:
[vbcol=seagreen]
> Hi ,
> I want a mirror image for everything not only data but all stored
> procedures, views ( and everythjing else) that are added in one db should be
> automatically reflected in the mirror image too . 1 second latency is not
> necessary; a few minutes will do too.
> The mirror image is needed for testing purposes. We develop our sites on a
> Dev db. Then port all the data to live. But we are now planning to introduce
> another db layer to testing. So that all our sites are develpoed in the dev
> db. We have a mirror image of the LIVE db running. Then we migrate everything
> from DEV db to the MIRROR image db. Then from there we move to the live db .
> This is because once everything is working on the mirror image, it will work
> on live too since both are the exact same.
> What is the best thing for this... replication? or Logshipping or something
> else.? And where can I find some information about it?
> Thanks for your help.
> --
> pmud
>
> "Narayana Vyas Kondreddi" wrote:
sql

Creating an exact replica of the db

Hi,
I need to create a mirror image of one our database. So that whatever data
is there in our db... show up in the other db at each second.
Is there a way this can be done..? And how.
Thanks
--
pmudHi,
You could implement Transactional Replication. See Transactional Replication
topic in Books online.
Thanks
Hari
SQL Server MVP
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
> Hi,
> I need to create a mirror image of one our database. So that whatever data
> is there in our db... show up in the other db at each second.
> Is there a way this can be done..? And how.
> Thanks
> --
> pmud|||one second latency? Not sure if that can be accomplished, but you will need
to use replication to get you close.
What is the purpose here? Disaster recovery? Reporting server?
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
> Hi,
> I need to create a mirror image of one our database. So that whatever data
> is there in our db... show up in the other db at each second.
> Is there a way this can be done..? And how.
> Thanks
> --
> pmud|||Just to add, replication will only copy data, but not other structural
changes and new proceures, new tables etc. What's the requirement? If you
want a 'mirror' copy as you said, may be logshipping is what you are after
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
> Hi,
> I need to create a mirror image of one our database. So that whatever data
> is there in our db... show up in the other db at each second.
> Is there a way this can be done..? And how.
> Thanks
> --
> pmud|||Hi ,
I want a mirror image for everything not only data but all stored
procedures, views ( and everythjing else) that are added in one db should be
automatically reflected in the mirror image too . 1 second latency is not
necessary; a few minutes will do too.
The mirror image is needed for testing purposes. We develop our sites on a
Dev db. Then port all the data to live. But we are now planning to introduce
another db layer to testing. So that all our sites are develpoed in the dev
db. We have a mirror image of the LIVE db running. Then we migrate everything
from DEV db to the MIRROR image db. Then from there we move to the live db .
This is because once everything is working on the mirror image, it will work
on live too since both are the exact same.
What is the best thing for this... replication? or Logshipping or something
else.? And where can I find some information about it?
Thanks for your help.
--
pmud
"Narayana Vyas Kondreddi" wrote:
> Just to add, replication will only copy data, but not other structural
> changes and new proceures, new tables etc. What's the requirement? If you
> want a 'mirror' copy as you said, may be logshipping is what you are after
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
> > Hi,
> >
> > I need to create a mirror image of one our database. So that whatever data
> > is there in our db... show up in the other db at each second.
> >
> > Is there a way this can be done..? And how.
> >
> > Thanks
> > --
> > pmud
>
>|||In your case, you need to look into log-shipping.
Check this out,
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog02.mspx
FYI, in SQL Server 2005 there is a new concept called database mirroring,
which comes close to your requirement. Google it to get more info.
--
- - - - - - - - -
Thanks
Yogish
"pmud" wrote:
> Hi ,
> I want a mirror image for everything not only data but all stored
> procedures, views ( and everythjing else) that are added in one db should be
> automatically reflected in the mirror image too . 1 second latency is not
> necessary; a few minutes will do too.
> The mirror image is needed for testing purposes. We develop our sites on a
> Dev db. Then port all the data to live. But we are now planning to introduce
> another db layer to testing. So that all our sites are develpoed in the dev
> db. We have a mirror image of the LIVE db running. Then we migrate everything
> from DEV db to the MIRROR image db. Then from there we move to the live db .
> This is because once everything is working on the mirror image, it will work
> on live too since both are the exact same.
> What is the best thing for this... replication? or Logshipping or something
> else.? And where can I find some information about it?
> Thanks for your help.
> --
> pmud
>
> "Narayana Vyas Kondreddi" wrote:
> > Just to add, replication will only copy data, but not other structural
> > changes and new proceures, new tables etc. What's the requirement? If you
> > want a 'mirror' copy as you said, may be logshipping is what you are after
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "pmud" <pmud@.discussions.microsoft.com> wrote in message
> > news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
> > > Hi,
> > >
> > > I need to create a mirror image of one our database. So that whatever data
> > > is there in our db... show up in the other db at each second.
> > >
> > > Is there a way this can be done..? And how.
> > >
> > > Thanks
> > > --
> > > pmud
> >
> >
> >

Creating an exact replica of the db

Hi,
I need to create a mirror image of one our database. So that whatever data
is there in our db... show up in the other db at each second.
Is there a way this can be done..? And how.
Thanks
--
pmudHi,
You could implement Transactional Replication. See Transactional Replication
topic in Books online.
Thanks
Hari
SQL Server MVP
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
> Hi,
> I need to create a mirror image of one our database. So that whatever data
> is there in our db... show up in the other db at each second.
> Is there a way this can be done..? And how.
> Thanks
> --
> pmud|||one second latency? Not sure if that can be accomplished, but you will need
to use replication to get you close.
What is the purpose here? Disaster recovery? Reporting server?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
> Hi,
> I need to create a mirror image of one our database. So that whatever data
> is there in our db... show up in the other db at each second.
> Is there a way this can be done..? And how.
> Thanks
> --
> pmud|||Just to add, replication will only copy data, but not other structural
changes and new proceures, new tables etc. What's the requirement? If you
want a 'mirror' copy as you said, may be logshipping is what you are after
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
> Hi,
> I need to create a mirror image of one our database. So that whatever data
> is there in our db... show up in the other db at each second.
> Is there a way this can be done..? And how.
> Thanks
> --
> pmud|||Hi ,
I want a mirror image for everything not only data but all stored
procedures, views ( and everythjing else) that are added in one db should b
e
automatically reflected in the mirror image too . 1 second latency is not
necessary; a few minutes will do too.
The mirror image is needed for testing purposes. We develop our sites on a
Dev db. Then port all the data to live. But we are now planning to introduce
another db layer to testing. So that all our sites are develpoed in the dev
db. We have a mirror image of the LIVE db running. Then we migrate everythin
g
from DEV db to the MIRROR image db. Then from there we move to the live db
.
This is because once everything is working on the mirror image, it will work
on live too since both are the exact same.
What is the best thing for this... replication? or Logshipping or something
else.? And where can I find some information about it?
Thanks for your help.
pmud
"Narayana Vyas Kondreddi" wrote:

> Just to add, replication will only copy data, but not other structural
> changes and new proceures, new tables etc. What's the requirement? If you
> want a 'mirror' copy as you said, may be logshipping is what you are after
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
>
>|||In your case, you need to look into log-shipping.
Check this out,
http://www.microsoft.com/technet/pr...oy/hasog02.mspx
FYI, in SQL Server 2005 there is a new concept called database mirroring,
which comes close to your requirement. Google it to get more info.
--
- - - - - - - - -
Thanks
Yogish
"pmud" wrote:
[vbcol=seagreen]
> Hi ,
> I want a mirror image for everything not only data but all stored
> procedures, views ( and everythjing else) that are added in one db should
be
> automatically reflected in the mirror image too . 1 second latency is not
> necessary; a few minutes will do too.
> The mirror image is needed for testing purposes. We develop our sites on a
> Dev db. Then port all the data to live. But we are now planning to introdu
ce
> another db layer to testing. So that all our sites are develpoed in the de
v
> db. We have a mirror image of the LIVE db running. Then we migrate everyth
ing
> from DEV db to the MIRROR image db. Then from there we move to the live d
b .
> This is because once everything is working on the mirror image, it will wo
rk
> on live too since both are the exact same.
> What is the best thing for this... replication? or Logshipping or somethi
ng
> else.? And where can I find some information about it?
> Thanks for your help.
> --
> pmud
>
> "Narayana Vyas Kondreddi" wrote:
>

Creating a dev database

Hi,

I have a test database running on the server. Now what I want to do is to create a dev db that has the exact same data model as the test one so that I can test out my application on the dev db and then migrate it to the test db. Can somebody tell me how I can achieve something like that.

Thanks...

Crazy:

The easiest way to do this is:

create a brandnew "dev" database with the name you wish to retain

Create view using UNION

I have 2 tables with exact structure:
Employee:
ID
FirstName
LastName
Position
ExEmployee:
ID
FirstName
LastName
Position
And the reference table
Position:
ID
Name
I want to create a view something like:
CREATE VIEW viewEMPLOEE_ALL
as
SELECT e.ID AS EmployeeID, e.FirstName AS FirstName, e.LastName AS LastName,
p.Name AS Position
FROM Employee e
LEFT JOIN Position p ON p.ID = e.Position
ORDER BY e.LastName, e.FirstName
UNION ALL
SELECT ex.ID AS EmployeeID, ex.FirstName AS FirstName, ex.LastName AS
LastName, p.Name AS Position
FROM Employee ex
LEFT JOIN Position p ON p.ID = ex.Position
ORDER BY ex.LastName, ex.FirstName
This is similar to my actual SQL as my original SQL is much much more table
join.
I got an error when I tried to run this SQL in Query Analyser:
"Server: Msg 107, Level 16, State 3, Procedure viewEMPLOEE_ALL, Line 5
The column prefix 'Employee' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Procedure viewEMPLOEE_ALL, Line 5
The column prefix 'Employee' does not match with a table name or alias name
used in the query."Man Utd wrote:
> I have 2 tables with exact structure:
> Employee:
> ID
> FirstName
> LastName
> Position
>
> ExEmployee:
> ID
> FirstName
> LastName
> Position
>
> And the reference table
> Position:
> ID
> Name
> I want to create a view something like:
> CREATE VIEW viewEMPLOEE_ALL
> as
> SELECT e.ID AS EmployeeID, e.FirstName AS FirstName, e.LastName AS
> LastName, p.Name AS Position
> FROM Employee e
> LEFT JOIN Position p ON p.ID = e.Position
> ORDER BY e.LastName, e.FirstName
> UNION ALL
> SELECT ex.ID AS EmployeeID, ex.FirstName AS FirstName, ex.LastName AS
> LastName, p.Name AS Position
> FROM Employee ex
> LEFT JOIN Position p ON p.ID = ex.Position
> ORDER BY ex.LastName, ex.FirstName
> This is similar to my actual SQL as my original SQL is much much more
> table join.
> I got an error when I tried to run this SQL in Query Analyser:
> "Server: Msg 107, Level 16, State 3, Procedure viewEMPLOEE_ALL, Line 5
> The column prefix 'Employee' does not match with a table name or
> alias name used in the query.
> Server: Msg 107, Level 16, State 1, Procedure viewEMPLOEE_ALL, Line 5
> The column prefix 'Employee' does not match with a table name or
> alias name used in the query."
Remove the ORDER BY in the first SELECT and use the ordinal position for
the ORDER BY in the second only if absolutely necessary. You're better
off not using an ORDER BY at all since it just creates overhead. You can
always add the ORDER BY later on to any queries you write against the
view.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I removed the ORDER BY clause in each SELECT but still got the same error.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:#QNoEBr2FHA.744@.TK2MSFTNGP10.phx.gbl...
> Man Utd wrote:
> Remove the ORDER BY in the first SELECT and use the ordinal position for
> the ORDER BY in the second only if absolutely necessary. You're better
> off not using an ORDER BY at all since it just creates overhead. You can
> always add the ORDER BY later on to any queries you write against the
> view.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Hi,
Sorry, my careless mistake.
In my second SELECT statement I have a CASE comparing the "ExEmployee" table
field, but I used "Employee" instead.
Solved.
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:Om3fuTr2FHA.3912@.TK2MSFTNGP15.phx.gbl...
> I removed the ORDER BY clause in each SELECT but still got the same error.
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:#QNoEBr2FHA.744@.TK2MSFTNGP10.phx.gbl...
>