February 10, 2009 at 2:07 am
My normal solution for copying a database from one SQL Server 2005 machine to another is to make a backup with Red Gate SQL Backup, which highly compresses the file, and then copy it to the destination and restore it there. Clearly, this does not work with SQL Server 2008 going to a SQL Server 2005 machine.
I know several other ways to effectively copy a database from 2008 to 2005 (scripting it out including insert statements, scripting out the schema and then using SSIS to move the actual data, using Red Gate SQL Compare and SQL Data compare together, and more...) but none of those use any form of compression.
In my situation, processor cycles are cheap, but bandwidth is expensive. Is there a way to copy a database from 2008 to 2005 that will minimize bandwidth and generally be fast?
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
February 11, 2009 at 8:03 am
If network bandwidth is most critical, you will most likely have to export the data to local disk, then compress, push across, uncompress at the destination, then load.
Hopefully it will be fast enough.
jg
February 11, 2009 at 3:46 pm
john g (2/11/2009)
If network bandwidth is most critical, you will most likely have to export the data to local disk, then compress, push across, uncompress at the destination, then load.Hopefully it will be fast enough.
jg
John;
That is precisely what we do when dealing with SQL Server 2005 on both sides. In this case, since the source is SQL Server 2008 the backup and mdb files are incompatible and the databases are too large to try to send them to Access as an intermediate stage.
Any suggestions on how to do it when dealing with 2008 on side and 2005 on the other?
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
February 11, 2009 at 4:17 pm
By exporting the data, I meant exporting each table to flat files - delimited or fixed, compressing and copying those - not compressing and copying the database files.
If the databases are too big to do that, the only other way I can think of would be to install an instance or virtual of 2008 on the 2005 server. The compressed database files can then be pushed across, loaded to 2008 and then use a linked server or SSIS to push to 2005. The 2008 instance would only have to be running during the load and data transfer.
just some random thoughts...might jog a good idea that fits your environment
jg
February 12, 2009 at 7:41 am
When I need moved databases from ms sql 2000 to ms sql 2005 I do dettach and attach i works well,
may be it help you...
but create backup and restore doesnt...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply