Copying existing sqlserver to a new server

Hi all,
Anyone know how to copy whole sqlserver from one server to another?
Since our old server has limitation of its harddisk space, we need to
replace it to a new one. I read some articles about this by using
attach/detach or backup/restore. But I heard that it may cause orphan users.
How can I avoid this? or anyone know article teach people to do this step by
step? Please help. Thanks
Best Rdgs
Ellis
Ellis,
This topic is covered almost daily here, you might want to search google
groups for the answer.
Anyway, I will cover it briefly.
1) Build your new server with the same directory structure as the old.
2) Install SQL Server to the same location on the new server as the old.
3) Install same service pack level, stop the MSSQLServer service on new
4) Stop MSSQLServer on the old server
5) Copy over the data files directory to the new server
6) Start the MSSQLServer service on new server
7) exec sp_dropserver '<oldname>'
8) exec sp_addserver '<newserver>','local'
9) Bounce MSSSQLServer service
http://vyaskn.tripod.com/moving_sql_server.htm
Here's some info compiled by Andrew Kelly (sorry about formatting):
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB
Wizard issues
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Ellis Yu wrote:
> Hi all,
> Anyone know how to copy whole sqlserver from one server to another?
> Since our old server has limitation of its harddisk space, we need to
> replace it to a new one. I read some articles about this by using
> attach/detach or backup/restore. But I heard that it may cause orphan users.
> How can I avoid this? or anyone know article teach people to do this step by
> step? Please help. Thanks
> Best Rdgs
> Ellis
>

Copying existing sqlserver to a new server

Hi all,
Anyone know how to copy whole sqlserver from one server to another?
Since our old server has limitation of its harddisk space, we need to
replace it to a new one. I read some articles about this by using
attach/detach or backup/restore. But I heard that it may cause orphan users.
How can I avoid this? or anyone know article teach people to do this step by
step? Please help. Thanks
Best Rdgs
EllisEllis,
This topic is covered almost daily here, you might want to search google
groups for the answer.
Anyway, I will cover it briefly.
1) Build your new server with the same directory structure as the old.
2) Install SQL Server to the same location on the new server as the old.
3) Install same service pack level, stop the MSSQLServer service on new
4) Stop MSSQLServer on the old server
5) Copy over the data files directory to the new server
6) Start the MSSQLServer service on new server
7) exec sp_dropserver '<oldname>'
8) exec sp_addserver '<newserver>','local'
9) Bounce MSSSQLServer service
http://vyaskn.tripod.com/moving_sql_server.htm
Here's some info compiled by Andrew Kelly (sorry about formatting):
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB
Wizard issues
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Ellis Yu wrote:
> Hi all,
> Anyone know how to copy whole sqlserver from one server to another
?
> Since our old server has limitation of its harddisk space, we need to
> replace it to a new one. I read some articles about this by using
> attach/detach or backup/restore. But I heard that it may cause orphan user
s.
> How can I avoid this? or anyone know article teach people to do this step
by
> step? Please help. Thanks
> Best Rdgs
> Ellis
>

Copying existing sqlserver to a new server

Hi all,
Anyone know how to copy whole sqlserver from one server to another?
Since our old server has limitation of its harddisk space, we need to
replace it to a new one. I read some articles about this by using
attach/detach or backup/restore. But I heard that it may cause orphan users.
How can I avoid this? or anyone know article teach people to do this step by
step? Please help. Thanks
Best Rdgs
EllisEllis,
This topic is covered almost daily here, you might want to search google
groups for the answer.
Anyway, I will cover it briefly.
1) Build your new server with the same directory structure as the old.
2) Install SQL Server to the same location on the new server as the old.
3) Install same service pack level, stop the MSSQLServer service on new
4) Stop MSSQLServer on the old server
5) Copy over the data files directory to the new server
6) Start the MSSQLServer service on new server
7) exec sp_dropserver '<oldname>'
8) exec sp_addserver '<newserver>','local'
9) Bounce MSSSQLServer service
http://vyaskn.tripod.com/moving_sql_server.htm
Here's some info compiled by Andrew Kelly (sorry about formatting):
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB
Wizard issues
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Ellis Yu wrote:
> Hi all,
> Anyone know how to copy whole sqlserver from one server to another?
> Since our old server has limitation of its harddisk space, we need to
> replace it to a new one. I read some articles about this by using
> attach/detach or backup/restore. But I heard that it may cause orphan users.
> How can I avoid this? or anyone know article teach people to do this step by
> step? Please help. Thanks
> Best Rdgs
> Ellis
>

Copy subscription fails

I understand that there is a limitation in windows for compressing 2gb +. I
am trying to create an msf by "copying a subscription". As an alternative
method I have tried to copy the subscription database using the "copy
database" wizard it gives me an error that is supposedly suppost to be a
permissions problem. Has anyone in the group been able to figure out where
the permissions are to be set to copy a database between two servers. I've
already tried the domain administrator in which both machines are members
of.
there error you are probably getting is because an account or dbuser is not
present in the target database.
repeat the copy and post back here with the exact error message you are
getting.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Sam" <sam@.info.com> wrote in message
news:10f89dua28ama86@.corp.supernews.com...
> I understand that there is a limitation in windows for compressing 2gb +.
I
> am trying to create an msf by "copying a subscription". As an alternative
> method I have tried to copy the subscription database using the "copy
> database" wizard it gives me an error that is supposedly suppost to be a
> permissions problem. Has anyone in the group been able to figure out where
> the permissions are to be set to copy a database between two servers.
I've
> already tried the domain administrator in which both machines are members
> of.
>
|||I figured out how to copy a database. Thank you for your help. But the
original issue was to avoid using the copy subscription method which is
limited to 2gb. The copy database wizard does not allow me to copy a
replication database. How do I get a subscription on another machine without
using snapshot folders. I loved the msf files because you can manage it
easily with novice users but like I said before it has a 2gb limit. What is
the simplest way to move the subscriptions without a snapshot folder (merge
replication)?
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uIpE6MRaEHA.3112@.tk2msftngp13.phx.gbl...
> there error you are probably getting is because an account or dbuser is
not[vbcol=seagreen]
> present in the target database.
> repeat the copy and post back here with the exact error message you are
> getting.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Sam" <sam@.info.com> wrote in message
> news:10f89dua28ama86@.corp.supernews.com...
+.[vbcol=seagreen]
> I
alternative[vbcol=seagreen]
where[vbcol=seagreen]
> I've
members
>
|||back up the publication database, copy the backup file to the subscriber and
restore it there. Then do a nosync subscribption.
A small snapshot will be generated and distributed to your subscription with
procs, triggers, and metadata merge needs to work.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Sam" <sam@.info.com> wrote in message
news:10faqndd1576a3d@.corp.supernews.com...
> I figured out how to copy a database. Thank you for your help. But the
> original issue was to avoid using the copy subscription method which is
> limited to 2gb. The copy database wizard does not allow me to copy a
> replication database. How do I get a subscription on another machine
without
> using snapshot folders. I loved the msf files because you can manage it
> easily with novice users but like I said before it has a 2gb limit. What
is
> the simplest way to move the subscriptions without a snapshot folder
(merge[vbcol=seagreen]
> replication)?
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:uIpE6MRaEHA.3112@.tk2msftngp13.phx.gbl...
> not
> +.
> alternative
a[vbcol=seagreen]
> where

> members
>
|||I tried what you've told me and it worked great so far. However nothing is
too simple I got the following message:
The merge process could not initialize the subscription.
{?=call sp_MSaddanonymousreplica (?,?,?,?,?,?)}
Invalid subscription type is specified. A subscription to publication 'XXX'
already exists in the database with a different subscription
I tried to drop the subscription but said it doesn't exist
sp_droppullsubscription
@.publisher='BUSHWACKER\TEST',@.publisher_db='XXX',
@.publication='XXX'
Server: Msg 14135, Level 11, State 1, Procedure sp_droppullsubscription,
Line 143
There is no subscription on Publisher 'BUSHWACKER\TEST', publisher database
'XXX', publication 'XXX'.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uH#hwylaEHA.3692@.TK2MSFTNGP09.phx.gbl...
> back up the publication database, copy the backup file to the subscriber
and
> restore it there. Then do a nosync subscribption.
> A small snapshot will be generated and distributed to your subscription
with[vbcol=seagreen]
> procs, triggers, and metadata merge needs to work.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Sam" <sam@.info.com> wrote in message
> news:10faqndd1576a3d@.corp.supernews.com...
> without
> is
> (merge
is[vbcol=seagreen]
are[vbcol=seagreen]
2gb[vbcol=seagreen]
"copy[vbcol=seagreen]
be[vbcol=seagreen]
> a
servers.
>
>