Database Migration

I have a SQL database called "Acts" on SQL2000 server that
was created by installation of an application. On that SQL
server we have created Logins for this particular "Acts"
database so users can use the application. Management has
now asked us to move this database to another server with
a different server name. My question is can I just
backup "Acts" database on the old server and restore it on
the new server. Will that bring over the logins ID and
their info or if it doesn't what would I have to do to get
that info. Or would I have to backup the entire SQL2000
databases(master, acts, model,msdb,norhtwind,pubs,tempdb)
from my old server and than restore all these databases on
my new server.
Any help is appreciated...
thanks,
JayBackup and restore is a good method to migrate a database. You can also use
sp_detach_db and sp_attach_db and copy the actual database files.
Regardless of which method you choose, you will need to create the logins
for the 'Acts' users on your new server and then link them to the users in
your newly migrated database. Look up sp_change_users_login in BOL for
details.
I strongly discourage migrating system databases between servers. If there
are any scheduled jobs, you can script them and apply them to the new server
much easier than you can fix a migrated MSDB database.
--
Geoff N. Hiten
SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Jay" <desiguys@.hotmail.com> wrote in message
news:0df101c35769$5b6d53e0$a301280a@.phx.gbl...
> I have a SQL database called "Acts" on SQL2000 server that
> was created by installation of an application. On that SQL
> server we have created Logins for this particular "Acts"
> database so users can use the application. Management has
> now asked us to move this database to another server with
> a different server name. My question is can I just
> backup "Acts" database on the old server and restore it on
> the new server. Will that bring over the logins ID and
> their info or if it doesn't what would I have to do to get
> that info. Or would I have to backup the entire SQL2000
> databases(master, acts, model,msdb,norhtwind,pubs,tempdb)
> from my old server and than restore all these databases on
> my new server.
>
> Any help is appreciated...
> thanks,
> Jay
>|||These should help:
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/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly
SQL Server MVP
"Jay" <desiguys@.hotmail.com> wrote in message
news:0df101c35769$5b6d53e0$a301280a@.phx.gbl...
> I have a SQL database called "Acts" on SQL2000 server that
> was created by installation of an application. On that SQL
> server we have created Logins for this particular "Acts"
> database so users can use the application. Management has
> now asked us to move this database to another server with
> a different server name. My question is can I just
> backup "Acts" database on the old server and restore it on
> the new server. Will that bring over the logins ID and
> their info or if it doesn't what would I have to do to get
> that info. Or would I have to backup the entire SQL2000
> databases(master, acts, model,msdb,norhtwind,pubs,tempdb)
> from my old server and than restore all these databases on
> my new server.
>
> Any help is appreciated...
> thanks,
> Jay
>