November 19th, 2003, 12:39 AM
Moving SQL Db from one Server to another
I have a database running on a server and its approx 50 MB. How can u mode the entire database to another server with all its tables, data and login details.
Please let me know all possible ways.
Webspiders Interweb Pvt. Ltd. & Brainus Networks
http://www.it2ecom.com | http://www.brainus.net
Server Management | Dedicated Servers | Reseller Hosting
Experienced, Qualified Techs | 24/7 Support Centers in India
Helm Premium Partners | Email : firstname.lastname@example.org
November 19th, 2003, 12:52 AM
You can use DTS to copy the entire database, including logins from the old system. Use the "copy objects and data" mode.
November 19th, 2003, 12:55 AM
Hi Doug -
I did it the same way - But It gives error and says Failed to do it ..
November 19th, 2003, 12:58 AM
Is there is any other possible way to do that.
November 19th, 2003, 04:41 AM
Detach the database, copy files to the other server and Attach.
Works better that DTS.
November 20th, 2003, 05:13 AM
Try this procedure
1) Terminate all connection to the db U want to move, setting from enterprise manager the db in 'single user mode'.
2) Keep trace of the files'names constituting your db (extention mdf (data file) ,and ldf (log file))
3) From sql query analyzer , user master, Detach the above mentioned db with the system stored procedure sp_detach_db 'dbName',true
4)From windows explorer select and copy db files to the target unit
5)From query analizer in the new instance of your db (as master), Use sp_attach_db 'dbName',
'path to data file (remember .mdf)',
'path to log file (remember .ldf)'.
Now you have moved your db from a machine to the other and it should perfectly working...
Try and ... Good Luck form Jluke-b