DB detach-attach

[techipa]

Hello All,

Can I detach the database from one server and attach it to other server in SQL 2000?

-techiPA

 

[mrdenny]

Yes you can. In the GUI, right click on the database, and select detach. Copy the files to the new server right click on Databases in the GUI and select attach.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[techipa]

Mrdenny,

Since the files are big to copy from one server to another after detach will the users loose permissions by the time the DB attaches.

 

[mrdenny]

As long as there login exists on the new server there domain logins will automatically resync with there user accounts within the database. Any users to don't have logins into the SQL Server will need to have there Windows Account granted access.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[techipa]

Thanks for the reply Denny.

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Crowley16]

if you're worried about size, then it might be better to backup your database, and then copy the backup to the new server and restore it.

the backup will be smaller than your live data files, and can be significantly smaller depending on your logging behaviour.

you will still retain your user accounts this way as well...

--------------------
Procrastinate Now!