Exporting a db for futur re-load 3

[REK2]

Hi All,
I would like to know how to export my database, including the connexion, roles, permissions, etc in a way that I could then reload with DTS on another machine without having to recreate the index and everything else.

Thanks

 

[SemperFiDownUnda]

you could disconnect the database, do a OS copy then reattach the database then move the copies and attach them.

That or you could just do a backup and restore with the MOVE option.

 

[REK2]

Wow.. that was quite easy! Not more than that?
So I have disconnected the db, copy the mdf and log file and simply re-attach it.. Now when I will attach this db on another SQL Server machine, it will be as simple as this? Only attach the db and everything will be loaded on this machine exactly like the one I've disconnected and copy?

Well thanks alot for the info. This is greatly appreciated.

 

[tlbroadbent]

Well, not quite so simple. You will very likely encounter problems with orphaned database users. These can occur in two ways.

1) The login for the user doesn't exist on the new server.
2) The SID for the login differs between servers.

Check the following to make sure you can resolve the orphaned user issue or no one will be able to use the database.

faq183-2153

HOW TO: Move Databases Between Computers That Are Running SQL Server

http://support.microsoft.com/default.aspx?kbid=314546

HOW TO: Resolve Permission Issues When You Move a Database Between Servers That Are Running SQL Server

http://support.microsoft.com/default.aspx?kbid=240872

Creating Logins for Databases Restored to a Replacement SQL Server

http://www.sql-server-performance.com/dd_creating_logins.asp

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[REK2]

Hum... seem's that this was to easy!! Ok thanks alot for you're input, I have to go to sleep before I cant make a difference between a database and a web browser...

I will get on your reply tomorow morning and give you my feedback

Thanks again

 

[SemperFiDownUnda]

Terry you're a walking library of info.....all cataloged and indexed!
/me pins a star on Terry's forehead

 

[tlbroadbent]

Not a library... Just a few good bookmarks. :-D

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[SQLBaby]

Not a liberary only... a free university which is giving a valuable knowledge to every one without any cost. we all love you terry

naveed

 

[REK2]

Terry,
Thanks a bunch, effectivly it was not as easy as I touth! I had this permission problems and "No trusted connection" or something problem. I've check out the links you provided and manage to have it work.

Thanks again.

Cheers