Why not backup and restore?

You should definitely use backup and restore if you can - it is the surest way to take a database from one server and move or copy it to another server. However, you may not always be able to use the backup and restore approach because of various limitations imposed by the scenario you have to work with.

Why not use the SQL Server 2005 copy database feature?

Again the answer is yes, use the copy database feature that SQL Server 2005 Management Studio provides if and when you can. However, there are two limitations: first, you need to connect to both servers, the source server and the destination, at the same time; second, the copy database feature is only available in SQL Server 2005.

So, what if you can't use backup/restore or SQL Server's copy database feature?

In cases when you can not use backup/restore, or the copy database feature of SQL Server 2005 you can take the "scripting way". Simply script the whole database, take the script and execute it on the destination server to create a copy of the database. But wait, SQL Server only scripts the schema of the database it does not script the data! Well, for that there is xSQL Object and xSQL Data Compare, both of those products allow you to script the whole database both schema and data in one place - all you need to do is save the script and execute it on the target server at your convenience. There is more, since the data scripts may get to be huge in cases your database contains many rows a simple check box allows you to use bulk inserts instead.

How much does this cost?

The scripting feature in xSQL Object and xSQL Data Compare is free and never expires! You do not need to activate or register those products to be able to use the scripting capabilities.

Click here to go back to the SQL Server content index page.