Copying a database from one server to another 1

[collierd]

Hello

This may seem like a vague question but I just want to avoid any major pitfalls

I have a database on one server (called MDW)
There are numerous functions, DTS packages and time based jobs associated with MDW
Is there an easy way to transfer all of this (or whatever is possible) from one server to another

Does anybody have an answer or can anybody point me to a useful URL/existing thread

Thanks

Damian.

 

[sunaj]

Which type of database?
If it is an sql server database, detach the database, copy the database file (.MDF) and attach the database at the new server.
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[collierd]

Sorry sunaj

Missed the most important part out
Yes it is, its sql 2000

 

[sunaj]

collierd

My mistake, I though I was posting in another forum....
Anway, try out the detach - attach. Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[collierd]

will do

thanks

 

[SQLSister]

This will copy the database and associated user defined functions, but will not properly transfer all the logins if you are using mixed mode security. It also will not copy jobs and DTS packages. Proper login copying and the jobs can be copied using DTS, but I know of no painless way to transfer DTS packages. I usually open the package and do File save as to create it on another server. Of course you will probably also have to modify the connection properties to reflect the new server anyway.

 

[miq]

hi,
but how to attach and detach ???



Bye
miq

 

[miq]

hi,
I found it sunaj. Isn't generating sql script is better option.


Bye
miq

 

[osjohnm]

Miq

execute sp_detachdb and sp_attachdb (BOL will have the syntax)

or you can move the mdf and ldf files to the new server and right click on your server in EM and say attach database but you could also run sp_attach_db once you have copied the files across.

you could also use the Database Copy Wizard that would detach and attach the data files for you, it will also move all the data, logins, stored procs as well.

John

 

[Kozimoto]

Open the instance of SQL 2000 in Microsoft Management Console so you can view the DATABASE folder. Right click on the DATABASE folder and choose COPY DATABASE WIZARD and follow the instructions. Simple...

Tips:

Make sure to use an account with the proper permissions on both servers. I use the SA account on the source and destination servers just to make sure it doesn't fail.

Also. Unencrypt stored proceedures prior to copying. They will fail if you don't.

That's all there is to copying a database in SQL 2000

 

[osjohnm]

Hi Collierd

SQLSister has a very valid point about your jobs and DTS packages. Check out thread183-78971 where Terry made a very good suggestion where he linked two servers together and inserted the dts package data from the msdb database on the one server into msdb on the other server.

John

 

[collierd]

Thanks everybody

 

[collierd]

Copied the database fine
Problem with copying DTS Packages

I have looked at Thread183-78971 which partially resolves the situation

Additional research suggests the following:

Just a reminder, I using SQL 2000 and copying from one server (LEE-NT-D) to another (LON-DWNT-01)

-- Add the opposing server
EXEC sp_addlinkedserver
'lon-dwnt-01',
N'SQL Server'
GO

--Copy the DTS packages
insert into [lon-dwnt-01].msdb.dbo.sysdtspackages
select * from msdb.dbo.sysdtspackages

The following error message is returned:

Server: Msg 18456, Level 14, State 1, Line 7
Login failed for user '\'.


Any thoughts?

Thanks

 

[osjohnm]

You need to setup a user in the linked server who has permissions on the server you are linked to. You setup a linked server but you didn't specify with what login account it must connect with.

Either right click on the linked server or execute
sp_addlinkedsrvlogin.

BOL will explain this for you but here is the syntax:

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
[ , [ @useself = ] 'useself' ]
[ , [ @locallogin = ] 'locallogin' ]
[ , [ @rmtuser = ] 'rmtuser' ]
[ , [ @rmtpassword = ] 'rmtpassword' ]

which would translate into the following

EXEC sp_addlinkedsrvlogin 'Accounts', 'false', NULL, 'SQLUser', 'Password'

Hope that helps

 

[collierd]

Confused

I am a user on LON-DWNT-01
This is set with windows authentication as it is my network logon (at no point does it prompt for any other password)
I use the following statement:

exEC sp_addlinkedsrvlogin [LON-DWNT-01], 'false', NULL, collierd, <Password>

where password is my standard network password

it comes back :

Login failed for user collierd

I have tries a null password

Does anything need reseting?
Am I feeding it with the wrong details ?

 

[collierd]

Just to add one of the other threads mentioned something about setting:

Trusted_Connection = No

How is this achieved?
Is this relevant?