Copying dBs

I am running SQL 2000 (using replication) & Win
2000.
We are getting a new production sever and would like to
know the best practice for moving all the SQL data from
the Old Server to the New Server. There are a few
questions the concern me.
- Will I have to setup replication on the new server?
- Will all the dB defaults be preserved?
Larry,
if the server names are the same, and you have backups of all databases, then you should be able torestore them on the new production server. If you're doing transactional replication, then make sure the distribution database is synchronized with the prod
uction database. If the server names are different, then I'd script out the publications and recreate on the new server. Provided no subscribers are allowed to do any data changes during this time, you should be able to add the subscriptions without apply
ing the data snapshot (@.sync_type ='none').
HTH,
Paul Ibison
|||In the long run you would be best to drop the subscriptions and publications
on the current production server. Back it up and restore it to the new
publisher.
Recreate the publications from scripts and then do create a nosync
subscription to your existing subscriber.
"larry" <anonymous@.discussions.microsoft.com> wrote in message
news:238c01c427b5$7a2e6040$a501280a@.phx.gbl...
> I am running SQL 2000 (using replication) & Win
> 2000.
> We are getting a new production sever and would like to
> know the best practice for moving all the SQL data from
> the Old Server to the New Server. There are a few
> questions the concern me.
> - Will I have to setup replication on the new server?
> - Will all the dB defaults be preserved?
>

copying Data Base from one sever to another

Hi,
Hope I explain this clearly. I'm a newbie to sql. I currently have 2
servers running sql 2000. One is a Production and the other a test server. On
the test server I have a duplicate sql 2000 with a copy of the database from
the production server. Which all are working fine. Both systems have a tape
drive. It's a very old D.B. copy on the test server so I would like to copy a
newer one over from the Prod. server. The tape drive on the production server
is a newer drive than the one on the test server. So I'm unable to restore
from the Prod. to the test server via tape. I would not like to stop sql
running on the prod. server. Is the a way to load the tape in the Prod server
and restore it to the test server? -- I only need the D.B. copyed. I would
not want to detach the D.B.
GregHi
This sounds like an access issue rather than anything else! If you have disk
space on the production server for a backup to disk, you may then be able to
either use a network share/FTP... etc to move it to the production system.
Failing that maybe a removable disk (but then there is a risk when
introducing new hardware!).
HTH
John
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:ECC06AC5-7FB7-423E-A023-71A3F0D8C102@.microsoft.com...
> Hi,
> Hope I explain this clearly. I'm a newbie to sql. I currently have 2
> servers running sql 2000. One is a Production and the other a test server.
> On
> the test server I have a duplicate sql 2000 with a copy of the database
> from
> the production server. Which all are working fine. Both systems have a
> tape
> drive. It's a very old D.B. copy on the test server so I would like to
> copy a
> newer one over from the Prod. server. The tape drive on the production
> server
> is a newer drive than the one on the test server. So I'm unable to restore
> from the Prod. to the test server via tape. I would not like to stop sql
> running on the prod. server. Is the a way to load the tape in the Prod
> server
> and restore it to the test server? -- I only need the D.B. copyed. I would
> not want to detach the D.B.
> Greg|||Depends what you have available.
Can you transfer anything between the prod and test server? If not then
obviously you can't refresh.
If you can transfer data then backup the prod database copy the backup to
the test server (via tape if necessary) and restore it.
If you can't do that much then you probably need to spend some time getting
into a situation where you can.
"Greg" wrote:
> Hi,
> Hope I explain this clearly. I'm a newbie to sql. I currently have 2
> servers running sql 2000. One is a Production and the other a test server. On
> the test server I have a duplicate sql 2000 with a copy of the database from
> the production server. Which all are working fine. Both systems have a tape
> drive. It's a very old D.B. copy on the test server so I would like to copy a
> newer one over from the Prod. server. The tape drive on the production server
> is a newer drive than the one on the test server. So I'm unable to restore
> from the Prod. to the test server via tape. I would not like to stop sql
> running on the prod. server. Is the a way to load the tape in the Prod server
> and restore it to the test server? -- I only need the D.B. copyed. I would
> not want to detach the D.B.
> Greg

copying Data Base from one sever to another

Hi,
Hope I explain this clearly. I'm a newbie to sql. I currently have 2
servers running sql 2000. One is a Production and the other a test server. O
n
the test server I have a duplicate sql 2000 with a copy of the database from
the production server. Which all are working fine. Both systems have a tape
drive. It's a very old D.B. copy on the test server so I would like to copy
a
newer one over from the Prod. server. The tape drive on the production serve
r
is a newer drive than the one on the test server. So I'm unable to restore
from the Prod. to the test server via tape. I would not like to stop sql
running on the prod. server. Is the a way to load the tape in the Prod serve
r
and restore it to the test server? -- I only need the D.B. copyed. I would
not want to detach the D.B.
GregHi
This sounds like an access issue rather than anything else! If you have disk
space on the production server for a backup to disk, you may then be able to
either use a network share/FTP... etc to move it to the production system.
Failing that maybe a removable disk (but then there is a risk when
introducing new hardware!).
HTH
John
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:ECC06AC5-7FB7-423E-A023-71A3F0D8C102@.microsoft.com...
> Hi,
> Hope I explain this clearly. I'm a newbie to sql. I currently have 2
> servers running sql 2000. One is a Production and the other a test server.
> On
> the test server I have a duplicate sql 2000 with a copy of the database
> from
> the production server. Which all are working fine. Both systems have a
> tape
> drive. It's a very old D.B. copy on the test server so I would like to
> copy a
> newer one over from the Prod. server. The tape drive on the production
> server
> is a newer drive than the one on the test server. So I'm unable to restore
> from the Prod. to the test server via tape. I would not like to stop sql
> running on the prod. server. Is the a way to load the tape in the Prod
> server
> and restore it to the test server? -- I only need the D.B. copyed. I would
> not want to detach the D.B.
> Greg|||Depends what you have available.
Can you transfer anything between the prod and test server? If not then
obviously you can't refresh.
If you can transfer data then backup the prod database copy the backup to
the test server (via tape if necessary) and restore it.
If you can't do that much then you probably need to spend some time getting
into a situation where you can.
"Greg" wrote:

> Hi,
> Hope I explain this clearly. I'm a newbie to sql. I currently have 2
> servers running sql 2000. One is a Production and the other a test server.
On
> the test server I have a duplicate sql 2000 with a copy of the database fr
om
> the production server. Which all are working fine. Both systems have a tap
e
> drive. It's a very old D.B. copy on the test server so I would like to cop
y a
> newer one over from the Prod. server. The tape drive on the production ser
ver
> is a newer drive than the one on the test server. So I'm unable to restore
> from the Prod. to the test server via tape. I would not like to stop sql
> running on the prod. server. Is the a way to load the tape in the Prod ser
ver
> and restore it to the test server? -- I only need the D.B. copyed. I would
> not want to detach the D.B.
> Greg

copying Data Base from one sever to another

Hi,
Hope I explain this clearly. I'm a newbie to sql. I currently have 2
servers running sql 2000. One is a Production and the other a test server. On
the test server I have a duplicate sql 2000 with a copy of the database from
the production server. Which all are working fine. Both systems have a tape
drive. It's a very old D.B. copy on the test server so I would like to copy a
newer one over from the Prod. server. The tape drive on the production server
is a newer drive than the one on the test server. So I'm unable to restore
from the Prod. to the test server via tape. I would not like to stop sql
running on the prod. server. Is the a way to load the tape in the Prod server
and restore it to the test server? -- I only need the D.B. copyed. I would
not want to detach the D.B.
Greg
Hi
This sounds like an access issue rather than anything else! If you have disk
space on the production server for a backup to disk, you may then be able to
either use a network share/FTP... etc to move it to the production system.
Failing that maybe a removable disk (but then there is a risk when
introducing new hardware!).
HTH
John
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:ECC06AC5-7FB7-423E-A023-71A3F0D8C102@.microsoft.com...
> Hi,
> Hope I explain this clearly. I'm a newbie to sql. I currently have 2
> servers running sql 2000. One is a Production and the other a test server.
> On
> the test server I have a duplicate sql 2000 with a copy of the database
> from
> the production server. Which all are working fine. Both systems have a
> tape
> drive. It's a very old D.B. copy on the test server so I would like to
> copy a
> newer one over from the Prod. server. The tape drive on the production
> server
> is a newer drive than the one on the test server. So I'm unable to restore
> from the Prod. to the test server via tape. I would not like to stop sql
> running on the prod. server. Is the a way to load the tape in the Prod
> server
> and restore it to the test server? -- I only need the D.B. copyed. I would
> not want to detach the D.B.
> Greg
|||Depends what you have available.
Can you transfer anything between the prod and test server? If not then
obviously you can't refresh.
If you can transfer data then backup the prod database copy the backup to
the test server (via tape if necessary) and restore it.
If you can't do that much then you probably need to spend some time getting
into a situation where you can.
"Greg" wrote:

> Hi,
> Hope I explain this clearly. I'm a newbie to sql. I currently have 2
> servers running sql 2000. One is a Production and the other a test server. On
> the test server I have a duplicate sql 2000 with a copy of the database from
> the production server. Which all are working fine. Both systems have a tape
> drive. It's a very old D.B. copy on the test server so I would like to copy a
> newer one over from the Prod. server. The tape drive on the production server
> is a newer drive than the one on the test server. So I'm unable to restore
> from the Prod. to the test server via tape. I would not like to stop sql
> running on the prod. server. Is the a way to load the tape in the Prod server
> and restore it to the test server? -- I only need the D.B. copyed. I would
> not want to detach the D.B.
> Greg

Copying a database to a different SQL Server

Hi,
I have a SQL Server 2000 database. I want to copy the database to a
different SQL server 2000. The destination sever does not at all have that
database. How can I copy the database schema and the all the data stored in
the database.
Any help regarding this will be highly appreciated.
Thank,
Vinita"Vinita Sharma" <sharmavi@.mail.armstrong.edu> wrote in message
news:#paERIR4DHA.2304@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I have a SQL Server 2000 database. I want to copy the database to a
> different SQL server 2000. The destination sever does not at all have

---

that

quote:

---

> database. How can I copy the database schema and the all the data stored

---

in

quote:

---

> the database.

---

The best way is to use a current BACKUP from your source server and do a
RESTORE to your target server. See BOL for syntax.
You could experiment with the copy database wizard from Enterprise manager
on your target server, right click databases> all tasks > copy database
wizard. I have to caution you I have not used it -- I prefer to do things
the old fashioned way.
Steve|||Watch out if you need to move logins and users, see this page -
http://support.microsoft.com/defaul...;EN-US;Q246133&
Ray Higdon MCSE, MCDBA, CCNA
--
"Vinita Sharma" <sharmavi@.mail.armstrong.edu> wrote in message
news:%23paERIR4DHA.2304@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hi,
> I have a SQL Server 2000 database. I want to copy the database to a
> different SQL server 2000. The destination sever does not at all have

---

that

quote:

---

> database. How can I copy the database schema and the all the data stored

---

in

quote:

---

> the database.
> Any help regarding this will be highly appreciated.
> Thank,
> Vinita
>

---

|||I also prefer the old fashioned way. I like the ability to control things.
Make sure you run the sp_help_revlogin to bring over your logins properly.
Lately I been experimenting with DTS and I am fascinated by its power.
Steven S. Warren
MCSA, MCSE, MCDBA, CCA, CIW-SA, CIW-MA, NETWORK+, i-NET+
http:\\www.swtechworks.com
This posting is provided AS IS with no warranties, and confers no rights.
"Vinita Sharma" <sharmavi@.mail.armstrong.edu> wrote in message
news:%23paERIR4DHA.2304@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hi,
> I have a SQL Server 2000 database. I want to copy the database to a
> different SQL server 2000. The destination sever does not at all have

---

that

quote:

---

> database. How can I copy the database schema and the all the data stored

---

in

quote:

---

> the database.
> Any help regarding this will be highly appreciated.
> Thank,
> Vinita
>

---

|||Hey Vinita,
In the Enteprise manager, right click on the database and go to "All Tasks".
Then click on Detach Database... and click Ok. The database will disappear
from the list. Now simply go to the drive where the database is stored (In m
y case it's C:\Program File
s\Microsoft SQL Server\MSSQL\Data). Now select the database MDF and LDF File
s and copy them to the destination machine. Then do the same steps, right cl
ick on the database and go to "All Tasks", and now click on Attach Database.
It will make you select th
e database file and point it to the DatabaseName_Data.MDF file that you just
copied. Thats it. It should then appear on the list. If you got any questio
ns email.
Danny Cabrera
dcabrera@.medstarholdings.com
System Programmer|||Thanks a lot ...
It worked.
Vinita
"Danny" <dcabrera@.medstarholdings.com> wrote in message
news:E3310419-761C-4EE9-9696-2D54EB283D1B@.microsoft.com...

quote:

---

> Hey Vinita,
> In the Enteprise manager, right click on the database and go to "All

---

Tasks". Then click on Detach Database... and click Ok. The database will
disappear from the list. Now simply go to the drive where the database is
stored (In my case it's C:\Program Files\Microsoft SQL Server\MSSQL\Data).
Now select the database MDF and LDF Files and copy them to the destination
machine. Then do the same steps, right click on the database and go to "All
Tasks", and now click on Attach Database. It will make you select the
database file and point it to the DatabaseName_Data.MDF file that you just
copied. Thats it. It should then appear on the list. If you got any
questions email.

quote:

---

> Danny Cabrera
> dcabrera@.medstarholdings.com
> System Programmer

---