copying databases from one server (win2K) to another (win2k3)

Hello All,
I am a SQL server newbie, so please try to answer in simple terms. I
am trying to copy databases from a SQL 2000 (Win2k) to another SQL
2000 (Win2k3). I am trying to use the Copy Database Wizard and end up
getting the familiar "Failed to create OMWWIZE share" error.
I've set up my source server to start up with the 'System Account' and
my destination server to start up with my Windows account that has
administrative privileges on that machine.
After reading up on several froums, SQL Server Books online...I came
to know that backup/restore is a wonderful option, but I don't have
the same drive specifications on the two servers and also need some
step by step instructions on how to back up the databases on the
source server and restore the same in the destination server.
Thanks to you all in advance for the help.
Ram.I think that wizard causes more problems than it solves. Have a look at
these:
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
Andrew J. Kelly SQL MVP
"RAM GANESAN" <ramganesan@.gmail.com> wrote in message
news:2dca7102.0407301057.445d4d17@.posting.google.com...
> Hello All,
> I am a SQL server newbie, so please try to answer in simple terms. I
> am trying to copy databases from a SQL 2000 (Win2k) to another SQL
> 2000 (Win2k3). I am trying to use the Copy Database Wizard and end up
> getting the familiar "Failed to create OMWWIZE share" error.
> I've set up my source server to start up with the 'System Account' and
> my destination server to start up with my Windows account that has
> administrative privileges on that machine.
> After reading up on several froums, SQL Server Books online...I came
> to know that backup/restore is a wonderful option, but I don't have
> the same drive specifications on the two servers and also need some
> step by step instructions on how to back up the databases on the
> source server and restore the same in the destination server.
> Thanks to you all in advance for the help.
> Ram.|||Easiest way is probably to restore a backup to the other server
or detach/attach
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?kbid=314546
INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/defaul...b;EN-US;q224071
For tips on using the Copy database wizard
INF: Understanding and Troubleshooting the Copy Database Wizard in SQL
Server 2000
http://support.microsoft.com/defaul...b;en-us;Q274463
Also check out
INF: How To Transfer Logins and Passwords Between SQL Servers
http://support.microsoft.com/defaul...b;en-us;Q246133
PRB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/defaul...b;en-us;Q168001
INF: How to Resolve Permission Issues When a Database is Moved Between SQL
Servers
http://support.microsoft.com/defaul...b;en-us;Q240872
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://support.microsoft.com/defaul...b;en-us;Q274188
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"RAM GANESAN" <ramganesan@.gmail.com> wrote in message
news:2dca7102.0407301057.445d4d17@.posting.google.com...
> Hello All,
> I am a SQL server newbie, so please try to answer in simple terms. I
> am trying to copy databases from a SQL 2000 (Win2k) to another SQL
> 2000 (Win2k3). I am trying to use the Copy Database Wizard and end up
> getting the familiar "Failed to create OMWWIZE share" error.
> I've set up my source server to start up with the 'System Account' and
> my destination server to start up with my Windows account that has
> administrative privileges on that machine.
> After reading up on several froums, SQL Server Books online...I came
> to know that backup/restore is a wonderful option, but I don't have
> the same drive specifications on the two servers and also need some
> step by step instructions on how to back up the databases on the
> source server and restore the same in the destination server.
> Thanks to you all in advance for the help.
> Ram.

copying databases from one server (win2K) to another (win2k3)

Hello All,
I am a SQL server newbie, so please try to answer in simple terms. I
am trying to copy databases from a SQL 2000 (Win2k) to another SQL
2000 (Win2k3). I am trying to use the Copy Database Wizard and end up
getting the familiar "Failed to create OMWWIZE share" error.
I've set up my source server to start up with the 'System Account' and
my destination server to start up with my Windows account that has
administrative privileges on that machine.
After reading up on several froums, SQL Server Books online...I came
to know that backup/restore is a wonderful option, but I don't have
the same drive specifications on the two servers and also need some
step by step instructions on how to back up the databases on the
source server and restore the same in the destination server.
Thanks to you all in advance for the help.
Ram.
I think that wizard causes more problems than it solves. Have a look at
these:
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
Andrew J. Kelly SQL MVP
"RAM GANESAN" <ramganesan@.gmail.com> wrote in message
news:2dca7102.0407301057.445d4d17@.posting.google.c om...
> Hello All,
> I am a SQL server newbie, so please try to answer in simple terms. I
> am trying to copy databases from a SQL 2000 (Win2k) to another SQL
> 2000 (Win2k3). I am trying to use the Copy Database Wizard and end up
> getting the familiar "Failed to create OMWWIZE share" error.
> I've set up my source server to start up with the 'System Account' and
> my destination server to start up with my Windows account that has
> administrative privileges on that machine.
> After reading up on several froums, SQL Server Books online...I came
> to know that backup/restore is a wonderful option, but I don't have
> the same drive specifications on the two servers and also need some
> step by step instructions on how to back up the databases on the
> source server and restore the same in the destination server.
> Thanks to you all in advance for the help.
> Ram.
|||Easiest way is probably to restore a backup to the other server
or detach/attach
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?kbid=314546
INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/default...;EN-US;q224071
For tips on using the Copy database wizard
INF: Understanding and Troubleshooting the Copy Database Wizard in SQL
Server 2000
http://support.microsoft.com/default...;en-us;Q274463
Also check out
INF: How To Transfer Logins and Passwords Between SQL Servers
http://support.microsoft.com/default...;en-us;Q246133
PRB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/default...;en-us;Q168001
INF: How to Resolve Permission Issues When a Database is Moved Between SQL
Servers
http://support.microsoft.com/default...;en-us;Q240872
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://support.microsoft.com/default...;en-us;Q274188
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"RAM GANESAN" <ramganesan@.gmail.com> wrote in message
news:2dca7102.0407301057.445d4d17@.posting.google.c om...
> Hello All,
> I am a SQL server newbie, so please try to answer in simple terms. I
> am trying to copy databases from a SQL 2000 (Win2k) to another SQL
> 2000 (Win2k3). I am trying to use the Copy Database Wizard and end up
> getting the familiar "Failed to create OMWWIZE share" error.
> I've set up my source server to start up with the 'System Account' and
> my destination server to start up with my Windows account that has
> administrative privileges on that machine.
> After reading up on several froums, SQL Server Books online...I came
> to know that backup/restore is a wonderful option, but I don't have
> the same drive specifications on the two servers and also need some
> step by step instructions on how to back up the databases on the
> source server and restore the same in the destination server.
> Thanks to you all in advance for the help.
> Ram.

Copying data from another DB on server problem

What iam trying to do is put the table name from one db into a variable and another one into another variable and pass them into my statement basicaly trying to bulk copy data from one table in a db and insert it into another db on the same server based on a condition

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClientComment]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)

Declare @.BDFR varchar(20), @.BDTO varchar(20), @.EQID varchar(20)
set @.BDFR = 'Commander' + '.dbo.' + 'ClientComment'
set @.BDTO = 'Test_Commander' + '.dbo.' + 'ClientComment'
set @.EQID = '80_300_005'
insert into @.BDTO
select * from @.BDFR where Eqid = @.EQIDplz help .......|||You need to use dynamic SQL to do what you're envisioning.

Beware that dynamic SQL breaks most of the commonly accepted rules about security/permissions/etc because of the way that it works... The dynamic SQL is permission checked based on the user that is running the SQL.

With that said, I'd use something like:DECLARE @.BDFR varchar(20), @.BDTO varchar(20), @.EQID varchar(20)

SET @.BDFR = 'Commander' + '.dbo.' + 'ClientComment'
SET @.BDTO = 'Test_Commander' + '.dbo.' + 'ClientComment'
SET @.EQID = '80_300_005'

EXECUTE ('insert into ' + @.BDTO
+ ' select * from ' + @.BDFR + ' where Eqid = @.EQID')-PatP

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.
>
>