MS SQL Server - Move DBs from One Instance to Another

Author

Move DBs from One Instance to Another

Jim

2006-11-29, 7:13 pm

SQL Server 2000
Windows Server 2003 SP1

We have 54 databases on this SQL Server and plan to move them all,
maintaining permissions and everything.

What is the best way to do this?

Thanks,
Jim

Aaron Bertrand [SQL Server MVP]

2006-11-29, 7:13 pm

Here is a post from Andrew Kelly:

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/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB
Wizard issues

"Jim" <please.reply@group> wrote in message
news:uYbmH6$EHHA.536@TK2MSFTNGP02.phx.gbl...
> SQL Server 2000
> Windows Server 2003 SP1
>
> We have 54 databases on this SQL Server and plan to move them all,
> maintaining permissions and everything.
>
> What is the best way to do this?
>
> Thanks,
> Jim
>

Kevin3NF

2006-11-29, 7:13 pm

IMHO, easiest if you can have them offline is detach/copy/attach

--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm

Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com

"Jim" <please.reply@group> wrote in message
news:uYbmH6$EHHA.536@TK2MSFTNGP02.phx.gbl...
> SQL Server 2000
> Windows Server 2003 SP1
>
> We have 54 databases on this SQL Server and plan to move them all,
> maintaining permissions and everything.
>
> What is the best way to do this?
>
> Thanks,
> Jim
>

sajberek@gmail.com

2006-11-29, 7:13 pm

You should also backup a master database and restore it to the
destination server. This way you should keep your logins in the
destination server.

Aaron Bertrand [SQL Server MVP]

2006-11-29, 7:13 pm

> You should also backup a master database and restore it to the
> destination server. This way you should keep your logins in the
> destination server.

Be cautious that @@version matches exactly on the two instances.

Hari Prasad

2006-11-30, 12:12 am

Hi,

Ensure that you have the same SQL Server service pack level in both old as
well as new server. If both SP are same you can restore the Master db from
source
to new server.

Thanks
Hari

<sajberek@gmail.com> wrote in message
news:1164839740.220233.125570@n67g2000cwd.googlegroups.com...
> You should also backup a master database and restore it to the
> destination server. This way you should keep your logins in the
> destination server.
>

Jim

2006-12-04, 7:12 pm

Thanks for your input, everyone! I will be looking into all of this.
--Jim

"Jim" <please.reply@group> wrote in message
news:uYbmH6$EHHA.536@TK2MSFTNGP02.phx.gbl...
> SQL Server 2000
> Windows Server 2003 SP1
>
> We have 54 databases on this SQL Server and plan to move them all,
> maintaining permissions and everything.
>
> What is the best way to do this?
>
> Thanks,
> Jim
>

Sponsored Links