Copying databases between two SQL Servers

Hi all,
This is the situation. We have some new SQL Servers being installed. The
existing SQL servers will be retired. The existing servers run Windows2000
Standard Edition with SP4 and obviously SQLServer2000 (SP3). The new servers
are running Windows2003 (SP1) Server (Standard Edition) with SQLServer2000
(SP3). All of the SQLServers are part of our AD domain. The SQLSERVER
service and the SQLAGENT servers run as domain accounts. These domain
accounts are members of the local administrator group on each of the SQL
servers.
The problem we are having is that when the DBA attempts to copy the
databases from the Win2000/SQLServer to one of the Win2003 servers the dts
package fails with the following
Step Error Sourse: Microsoft Data Transformation Services (DTS) Package
Step Error Description: Unspecified Error
Step Error Code: 80004005
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Under more info it says
Failed to create the share OMWWIZD
We have gone through kb article 274663 which convers this but as far as we
can see we meet all the requirements for this to work, so we are a little
stumped at this point. We thought it may be something to do with Win2003
SP1, so we built a Win2003 server with no service packs and tried to copy
some databases and we received the exact same error.
I can manually create a share between the Win2000 and Win2003 servers using
the same domain account the SQLServer process use. Once manually created I
can create and delete files, so at a filesystem level the interopability
between the servers appears fine. Running out of ideas at this point.
If anyone has any suggestions they would be greatly welcomed.
regards
CraigHi,
Easy approches to copy the databases are:-
1. Backup the production database using (BACKUP DATABASE command)
2. COpy the backup file to test server
3. Restore the database in test server (RESTORE DATABASE)
See Backup and Restore commad in books online.
Note:
you can also detach and attach databases. See SP_DETACH_DB and SP_ATTACH_DB
Thanks
Hari
"Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
news:ukO7n%23qhFHA.320@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> This is the situation. We have some new SQL Servers being installed. The
> existing SQL servers will be retired. The existing servers run Windows2000
> Standard Edition with SP4 and obviously SQLServer2000 (SP3). The new
> servers are running Windows2003 (SP1) Server (Standard Edition) with
> SQLServer2000 (SP3). All of the SQLServers are part of our AD domain. The
> SQLSERVER service and the SQLAGENT servers run as domain accounts. These
> domain accounts are members of the local administrator group on each of
> the SQL servers.
> The problem we are having is that when the DBA attempts to copy the
> databases from the Win2000/SQLServer to one of the Win2003 servers the dts
> package fails with the following
> Step Error Sourse: Microsoft Data Transformation Services (DTS) Package
> Step Error Description: Unspecified Error
> Step Error Code: 80004005
> Step Error Help File:sqldts80.hlp
> Step Error Help Context ID:1100
> Under more info it says
> Failed to create the share OMWWIZD
> We have gone through kb article 274663 which convers this but as far as we
> can see we meet all the requirements for this to work, so we are a little
> stumped at this point. We thought it may be something to do with Win2003
> SP1, so we built a Win2003 server with no service packs and tried to copy
> some databases and we received the exact same error.
> I can manually create a share between the Win2000 and Win2003 servers
> using the same domain account the SQLServer process use. Once manually
> created I can create and delete files, so at a filesystem level the
> interopability between the servers appears fine. Running out of ideas at
> this point.
> If anyone has any suggestions they would be greatly welcomed.
> regards
> Craig
>|||In additon to Hari's resonse you can also take a look at
sp_detach_db,sp_attach_db,sp_attach_sing
le_file_db
system stored procedures
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eJz5barhFHA.3608@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Easy approches to copy the databases are:-
> 1. Backup the production database using (BACKUP DATABASE command)
> 2. COpy the backup file to test server
> 3. Restore the database in test server (RESTORE DATABASE)
>
> See Backup and Restore commad in books online.
>
> Note:
>
> you can also detach and attach databases. See SP_DETACH_DB and
SP_ATTACH_DB
> --
> Thanks
> Hari
>
> "Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
> news:ukO7n%23qhFHA.320@.TK2MSFTNGP09.phx.gbl...
Windows2000[vbcol=seagreen]
The[vbcol=seagreen]
dts[vbcol=seagreen]
we[vbcol=seagreen]
little[vbcol=seagreen]
copy[vbcol=seagreen]
>|||Hi Uri and Hari,
thanks for your replies.
Yes, doing a backup/restore is one of the options I suggested to our DBA but
he is reluctant to do that because he says (and I'm no DBA) he loses all the
ownership rigts and has to recreate SQLServer accounts and so on. To me that
doesn't sound to much effort, we are not a big company so there's probably
only a hanfull of accounts, however he is the DBA so I have to go with his
recomedations pertaining to the databases.
One thing I have just found out is that when this server was originally
built it was named server-a. Later on and after Sqlserver was installed they
renamed the server to server-b. Server-a and server-b. Just wondering if
this is part of the problem?
Anyway we will explore a few other avenues and if all else fails we will
have to revert to the ol backup/restore option. I will direct our DBA to
those stored procedures you two have mentioned.
thanks for the assistance
Craig
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Oxcc5prhFHA.2372@.TK2MSFTNGP14.phx.gbl...
> In additon to Hari's resonse you can also take a look at
> sp_detach_db,sp_attach_db,sp_attach_sing
le_file_db
> system stored procedures
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eJz5barhFHA.3608@.TK2MSFTNGP12.phx.gbl...
> SP_ATTACH_DB
> Windows2000
> The
> dts
> we
> little
> copy
>|||Craig
Your DBA is not quite correct.
There are two stored procedures that provided by Microsoft to migrate
users/logins with their original SID
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)
IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @.login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SET @.tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
sp_help_revlogin
"Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
news:%23AC4mwzhFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Hi Uri and Hari,
> thanks for your replies.
> Yes, doing a backup/restore is one of the options I suggested to our DBA
but
> he is reluctant to do that because he says (and I'm no DBA) he loses all
the
> ownership rigts and has to recreate SQLServer accounts and so on. To me
that
> doesn't sound to much effort, we are not a big company so there's probably
> only a hanfull of accounts, however he is the DBA so I have to go with his
> recomedations pertaining to the databases.
> One thing I have just found out is that when this server was originally
> built it was named server-a. Later on and after Sqlserver was installed
they
> renamed the server to server-b. Server-a and server-b. Just wondering if
> this is part of the problem?
> Anyway we will explore a few other avenues and if all else fails we will
> have to revert to the ol backup/restore option. I will direct our DBA to
> those stored procedures you two have mentioned.
> thanks for the assistance
> Craig
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:Oxcc5prhFHA.2372@.TK2MSFTNGP14.phx.gbl...
of[vbcol=seagreen]
the[vbcol=seagreen]
Package[vbcol=seagreen]
as[vbcol=seagreen]
manually[vbcol=seagreen]
>|||Hi Uri,
ok, thanks for the information. I'll pass it on to him. To be fair to him
he's really an Oracle DBA who has had SQLServer dumped on him and he hasn't
had a great deal of training on it yet.
One other thing has come to light and as of yet I am not to sure if it is
the cause or not, we still need to do some more testing. To cut a long and
boring story short we have one AD domain, let's call it oz.com.au. AD is DNS
intergrated. At some point we created some sub domains within abc.com,
namely dev.abc.com and test.abc.com. These new subdomains also have their
own unique ip address range
abc.com 192.168.1.x
dev.abc.com 192.168.2.x
test.abc.com 192.168.3.x
So the fully qualified domain names for our servers would look like this
server1.abc.com
server2.abc.com
server3.dev.abc.com
server4.dev.abc.com
server5.test.abc.com
server6.test.abc.com
however we only have one single AD domain, in this case abc.com.
What we have now found is that we can copy databases between two servers who
share the same FQDN domain part. ie. server1.abc.com to server2.abc.com
works, however copying between two servers who do not share the same domain
part does not and generates the error mentioned in the original post. ie.
server1.abc.com to server3.dev.abc.com
The odd thing is that this setup used to work under Win2000, but obviously
something in Win2003 is different. Might have to bounce this off some people
in the windows groups.
If anyone has any suggestions I'm all ears.
Craig
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23MVyg$2hFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Craig
> Your DBA is not quite correct.
> There are two stored procedures that provided by Microsoft to migrate
> users/logins with their original SID
>
> USE master
> GO
> IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
> DROP PROCEDURE sp_hexadecimal
> GO
> CREATE PROCEDURE sp_hexadecimal
> @.binvalue varbinary(256),
> @.hexvalue varchar(256) OUTPUT
> AS
> DECLARE @.charvalue varchar(256)
> DECLARE @.i int
> DECLARE @.length int
> DECLARE @.hexstring char(16)
> SELECT @.charvalue = '0x'
> SELECT @.i = 1
> SELECT @.length = DATALENGTH (@.binvalue)
> SELECT @.hexstring = '0123456789ABCDEF'
> WHILE (@.i <= @.length)
> BEGIN
> DECLARE @.tempint int
> DECLARE @.firstint int
> DECLARE @.secondint int
> SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
> SELECT @.firstint = FLOOR(@.tempint/16)
> SELECT @.secondint = @.tempint - (@.firstint*16)
> SELECT @.charvalue = @.charvalue +
> SUBSTRING(@.hexstring, @.firstint+1, 1) +
> SUBSTRING(@.hexstring, @.secondint+1, 1)
> SELECT @.i = @.i + 1
> END
> SELECT @.hexvalue = @.charvalue
> GO
> IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> DROP PROCEDURE sp_help_revlogin
> GO
> CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> DECLARE @.name sysname
> DECLARE @.xstatus int
> DECLARE @.binpwd varbinary (256)
> DECLARE @.txtpwd sysname
> DECLARE @.tmpstr varchar (256)
> DECLARE @.SID_varbinary varbinary(85)
> DECLARE @.SID_string varchar(256)
> IF (@.login_name IS NULL)
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name <> 'sa'
> ELSE
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name = @.login_name
> OPEN login_curs
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> IF (@.@.fetch_status = -1)
> BEGIN
> PRINT 'No login(s) found.'
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN -1
> END
> SET @.tmpstr = '/* sp_help_revlogin script '
> PRINT @.tmpstr
> SET @.tmpstr = '** Generated '
> + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
> PRINT @.tmpstr
> PRINT ''
> PRINT 'DECLARE @.pwd sysname'
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> PRINT ''
> SET @.tmpstr = '-- Login: ' + @.name
> PRINT @.tmpstr
> IF (@.xstatus & 4) = 4
> BEGIN -- NT authenticated account/group
> IF (@.xstatus & 1) = 1
> BEGIN -- NT login is denied access
> SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> ELSE BEGIN -- NT login has access
> SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> END
> ELSE BEGIN -- SQL Server authentication
> IF (@.binpwd IS NOT NULL)
> BEGIN -- Non-null password
> EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> IF (@.xstatus & 2048) = 2048
> SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
> ELSE
> SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
> PRINT @.tmpstr
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> ELSE BEGIN
> -- Null password
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> IF (@.xstatus & 2048) = 2048
> -- login upgraded from 6.5
> SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> ELSE
> SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> PRINT @.tmpstr
> END
> END
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> END
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN 0
> GO
> sp_help_revlogin
> "Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
> news:%23AC4mwzhFHA.2484@.TK2MSFTNGP15.phx.gbl...
> but
> the
> that
> they
> of
> the
> Package
> as
> manually
>|||Hi all,
ok, it turned out that the fully qualified hostnames were in fact causing
the problem. As soon as we put the machines in the same internet domain name
space the DTS copies worked. I assume that something within SQLServer/DTS
assumes that if the FQDN contain different domain parts then therefore they
are in different AD domains. The fix was to simply change the
- FQDN to the same domain
- Copy the databases
- Revert the servers's FQDN back.
Thanks to all who offered various solutions. Now off to the MS support site
to see if there is something I should be configuring in AD when I do this. I
wouldn't have thought so but it looks like there is.
Regards
Craig
"Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
news:%232V5e$3hFHA.3912@.tk2msftngp13.phx.gbl...
> Hi Uri,
> ok, thanks for the information. I'll pass it on to him. To be fair to him
> he's really an Oracle DBA who has had SQLServer dumped on him and he
> hasn't had a great deal of training on it yet.
> One other thing has come to light and as of yet I am not to sure if it is
> the cause or not, we still need to do some more testing. To cut a long and
> boring story short we have one AD domain, let's call it oz.com.au. AD is
> DNS intergrated. At some point we created some sub domains within abc.com,
> namely dev.abc.com and test.abc.com. These new subdomains also have their
> own unique ip address range
> abc.com 192.168.1.x
> dev.abc.com 192.168.2.x
> test.abc.com 192.168.3.x
> So the fully qualified domain names for our servers would look like this
> server1.abc.com
> server2.abc.com
> server3.dev.abc.com
> server4.dev.abc.com
> server5.test.abc.com
> server6.test.abc.com
> however we only have one single AD domain, in this case abc.com.
> What we have now found is that we can copy databases between two servers
> who share the same FQDN domain part. ie. server1.abc.com to
> server2.abc.com works, however copying between two servers who do not
> share the same domain part does not and generates the error mentioned in
> the original post. ie. server1.abc.com to server3.dev.abc.com
> The odd thing is that this setup used to work under Win2000, but obviously
> something in Win2003 is different. Might have to bounce this off some
> people in the windows groups.
> If anyone has any suggestions I'm all ears.
> Craig
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23MVyg$2hFHA.2484@.TK2MSFTNGP15.phx.gbl...
>