Need help on transfer/copy all db objects....from 1 server to another In real time experience way

Question

Need help on transfer/copy all db objects....from 1 server to another In real time experience way

s1123

Mr or Mrs. 500

Points: 567
More actions
February 27, 2012 at 7:32 am

#269209

Hi guys
I want to transfer/copy database and logins ,views , jobs,procedures and everything from one server to another server
like 2005 to 2005 and 2005 to 2008.
Which process should I follow
1) SSIS package
2) copy database wizard
3)detach and attach
4)backup and restore..
which is good on production server??
if I use backup and restore.. will to copy all db objects(logins,views,jobs,procedures e.t.c)
I came to know Detach and attach is easy process if downtime is allowable..(and will this method transfer all db objects(db,logins,views,jobs,procedures e.t.c))
and
is copy database wizard for 2005 to 2008 different?
and
when do u use import and export?
Thanks
[/b][/b][/b]

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Backup-restore for the contents of the database, script the logins (with their SIDs) or use SSIS's transfer logins, script the jobs or use SSIS transfer jobs.

Backup-restore will transfer the same as detach-attach, the contents of the database, not logins or jobs (they're in msdter or msdb respectively)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

calvo SSChampion Points: 12930 More actions · Answer 2

Backup/restore and detach/attach will only move the database objects. Jobs, logins, operators, and other instance level objects will need to be scripted and moved separately.

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

Lowell SSC Guru Points: 323518 More actions · Answer 3

backup and restore are the best solution i think.

it will copy all objects perfectly that exist inside the database...that' distinctino is important, because you asked about:

will to copy all db objects(,logins,,views,jobs,procedures e.t.c)

jobs will not be copied. jobs exist in the msdb database, soyou either need to recreate them, or script them from the old server, adjust them for the right database name/server name/file paths, and execute the script on the new server.

logins will not be copied. users in the database will, but you'll need to script/recreate the logins, unless they are windows logins ont he same network.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

s1123 Mr or Mrs. 500 Points: 567 More actions · Answer 4

Thanx lowell,calvo,GilaMonster

to gila:: why do i need backup and restore for db and ssis for logins and jobs??

i can use ssis for databse also , right? or is it not recommended for moving db through ssis?

but what about copy database wizard??

we can transfer everything(logins,jobs,operators,procdesures e.t.c) , right??

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

satishm94 (2/27/2012)
to gila:: why do i need backup and restore for db and ssis for logins and jobs??
i can use ssis for databse also , right? or is it not recommended for moving db through ssis?

You can, but it's a lot more complex, a lot more involved and a lot greater chance that something will break. Probably take longer too. Only time I'd use that is if backup/restore wasn't an option (eg moving a DB down-version)

but what about copy database wizard??
we can transfer everything(logins,jobs,operators,procdesures e.t.c) , right??

No, copy database wizard will only copy the database. Then you'll have to separately copy logins, jobs, operators, linked servers, backup history, SSIS/DTS packages and anything else that's stored outside the database that you are copying.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

s1123 Mr or Mrs. 500 Points: 567 More actions · Answer 6

hi thanx

but can u go through this and tell me..

http://www.codeproject.com/Articles/167714/Copy-Database-Wizard-MS-SQL-Server-2005-and-later

he is saying we can transfer everything using copy database wizard..

big confusion now...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Sorry, I was thinking of the SSIS copy database task (which is not the same as the copy database wizard).

Use whatever you like, backup-restore is the usual method, it's likely the fastest, it's definitely the safest, the jobs and such can easily be transferred separately (SSIS is good for that)

If you're going to use copy database, I hope the DB is small.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

s1123 Mr or Mrs. 500 Points: 567 More actions · Answer 8

s1123

Mr or Mrs. 500

Points: 567

February 28, 2012 at 5:38 am

#1452528

oh kk

thanks