migration frm MSSQL to MSSQL | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

migration frm MSSQL to MSSQL

Is it possible to migrate databases between MSSql server2000 and MSsql server 2000.Pls suggest if any tools available or any script .
I need to be sure that I’m understanding your query correctly.
You want to move or copy a database from one SQL Server 2000 Server to another one ? The most popular way is to use DTS (Data Transformation Services). This tool is included as part of the SQL Server client tools during installation.
It is used though the Enterprise Manager interface. It gets tricky to explain on a forum like this exactly how to use these tools for your specific circumstance. It is best if you read up about DTS from relevant SQL Server literature, much of which can be found online. Keep in mind that if the database that you wish to move is quite large (more than a few GB), then restoring a database backup from the source server onto the destination server, or detaching and moving the physical data files, is probably more efficient than a DTS package. Also note that if there are scheduled tasks on the source server that run tasks on that database, these tasks will need to be recreated on the destination server as they will not automaticaly move with the database.
In addition, if you are using Mixed Mode authentication on both servers, you may need to recreate or change some Security Logins on the destination server. To help you further in this regard; perhaps you could provide more information such as: The size of the database, the authentication modes used (NT or Mixed mode),
SQL Agent jobs dependent on the database, etc.

Yes and its simple by using BACKUP from source server and RESTORE on target server, if not you can use COPY DATABASE WIZARD to transfer the databases. Refer to the books online for BACKUP, RESTORE and COPY DATABASE WIZARD topics. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Hi!Thanks for your response.But my problem is something different.My company wants to migrate the data from a source server to destination server.The source machine is running win 2000 and dest machine has win2003.I have to migrate the entire database server consisting of approx 400 db’s to the destination machine.The management team want me to write some scripts in a job which can do the database ransfer and logins/permissions transfer which can be run each time we need to migtrate the databases.Also only user database is to be migrated and no system databases.and finlly after migration it shud compare the sourc and give a detail report of success, failure, location , size etc.Hw can this be possible.pls suggest
You can script backup and copy backup files to folder on another server. Then you can have another script for restoring dbs on a new server. For migrating logins see:http://support.microsoft.com/default.aspx?scid=kb;en-us;246133