Backing up SQL Server 2005 to 2000

Post #277,141 by tangaroa 1/1/07 5:41:33 PM Reply	Backing up SQL Server 2005 to 2000 I'm looking for a way to extract a SQL Server 2005 database in a format that can be read by 2000, by a method that can be automated and run as a scheduled task (so "Wizards" are out). Upgrading the 2000 server is not an option since it's an MSDE instance in a 3rd-party package. The root of the problem is that SQL Server's "backup" command outputs files in some binary format which earlier versions of SQL Server cannot read. Looking at the online documentation for "backup", there does not seem to be a way to specify an earlier version of the backup file. The specific error I'm getting is this: "The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database." Googling the error brings up a bunch of MS techs saying "You can't do this, period", but it doesn't sound like an impossible task. There has to be a script somewhere to export the database in an importable format (like SQL!), but I've had no luck finding one. I also saw a suggestion to simply copy over the .mdb and .ldb files (which works for a 2000->2005 upgrade), but the 2005 system is a live server that I can't down to try this out. Does anyone have any other ideas or know of a script that I could use? This has me stumped.
Post #277,146 by crazy 1/1/07 6:09:20 PM Reply	How much data? How often will it run? Will the target system have new data that needs to be meshed? I can think of a couple of ways to do this, but need more info to determine a direction.
Post #277,235 by tangaroa 1/2/07 10:47:01 PM Reply	Re: How much data? About a gig uncompressed, transferred nightly. No need for incrementals if full backups are easier and less likely to cause corruption. It's an internal network so bandwidth is not a problem and we can completely wipe out and restore the database every backup. The MSDE2k system is only going to mirror what's on the SQL2005 system. It doesn't have any data of its own.
Post #277,149 by ChrisR 1/1/07 6:55:43 PM Reply	You could export it via DTS to some third format (Excel, MDB, text, etc...) and then import via another DTS task. Another method I've used in the past was to just use a set of SQL queries that generate insert statements as output ('INSERT INTO MyTable VALUES(....)') and then run it as a script on the target server. I did have to write the queries by hand for each table, but there's probably a way to automate it if you really need to.
Post #277,151 by tuberculosis 1/1/07 7:17:58 PM Reply	I'd go with the last strategy Do a little digging on how to query for table formats, and how to get a list of tables, constraints, indexes, etc. It is all available via sql. Use this to write a generic dump script that you can feed to the other db. Should take no more than a day of concentrated effort to do this. [link\|http://www.blackbagops.net\|Black Bag Operations Log] [link\|http://www.objectiveclips.com\|Artificial Intelligence] [link\|http://www.badpage.info/seaside/html\|Scrutinizer]
Post #277,239 by tangaroa 1/2/07 11:43:47 PM Reply	Thanks, both of you Must've forgotten to hit save after preview. Let's try that again... I finally found [link\|http://vyaskn.tripod.com/code/generate_inserts.txt\|some code] that should turn a table into SQL inserts. If it works, it should be easy enough to make a list of tables and plug it in, which should be all I need.

Welcome to IWETHEY!