Unpacking a SQL DB

[frudd]

Hi everyone,
I need to move / install a SQL DB on a different SQL server box, now I am familiar with both:

1. creating a backup (.bak), and then using the restore command
OR
2. using "All Tasks" --> and then "generate SQL script" and using DTS to bring in the data.

Is there a reason I should use one technique and not the other? A particular situation where one is better / quicker?
Does one have a shortfall, that I'm not picking up?

Any words wisdom are most appreciated.

 

[nigelrivett]

The backup / restore method is likely to be a lot simpler and safer. It has different problems with conflicts on the destination (wrong collation, logins missing, ...).
Wit the scripting solution you have to make sure you get all the objects and the order in which they are applied is important.

e.g. if the code pages are not available the database won't restore - if it's not the same as the system databases then you may get problems running things - with the scripting solution you may get incorrect data/structure.
If the logins are have diferent IDs you may have to remap to users.

You should already have a backup of your database so it's just a matter of copying and restoring (zip and unzip if necessary).


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[RobertT687]

Even if you are restoring 'master' on the new box I would consider dropping and recreating any users that use SQL Server Login Authentication. No matter how careful I am I always seem to have some trouble with these. NT Authentication usually isn't a problem.

DTS jobs and any scheduled jobs should be reviewed to make sure they reference the new Server.