{"id":546,"date":"2007-05-15T14:20:45","date_gmt":"2007-05-15T13:20:45","guid":{"rendered":"http:\/\/www.craigmurphy.com\/blog\/?p=546"},"modified":"2010-06-23T09:29:37","modified_gmt":"2010-06-23T08:29:37","slug":"moving-sql-server-2005-express-databases-to-sql-server-2000","status":"publish","type":"post","link":"http:\/\/www.craigmurphy.com\/blog\/?p=546","title":{"rendered":"Moving SQL Server 2005 Express databases to SQL Server 2000"},"content":{"rendered":"

23\/06\/2010 UPDATE<\/strong>
\nThe database publishing wizard is integrated within Visual Studio 2008\/2010.<\/p>\n

Further information can be found here<\/a>.\n<\/p><\/blockquote>\n

As you might well imagine, the on disk structure for SQL Server 2005 differs from that of SQL Server 2000.<\/p>\n

Indeed, restoring a SQL Server 2005 [Express] database backup for use with SQL Server 2000 isn’t really the done thing, as this post<\/a> confirms.<\/p>\n

After a little head-scratching with the SQL Server 2005 Express data export and scripting options, I deemed it necessary to create a SQL script that was not only capable of creating the database structure, but was also able to create all the INSERT<\/strong> statements necessary to recreate the data too. And it had to create SQL suitable for SQL Server 2000’s dialect…<\/p>\n

Further head-scratching led me to the Microsoft SQL Server Database Publishing Wizard<\/a>. It does exactly what I needed and allowed me to move a SQL Server 2005 database back down to SQL Server 2000, as these screenshots confirm:<\/p>\n


\n
\n<\/p>\n

So, in a nutshell, here’s what I did:<\/p>\n

    \n
  1. Ran the Database Publishing Wizard against my SQL Server 2005 Express database.<\/li>\n
  2. Created a SQL Server 2000-compliant SQL script that contained all the SQL statements required to create the database. The SQL script also created all the INSERT statements required to populate the tables in the database.<\/li>\n
  3. Created a new blank database in SQL Server 2000.<\/li>\n
  4. Ran the SQL script from step 2 against the SQL Server 2000 database – using the Query Analyser.<\/li>\n<\/ol>\n

    Of course, there’s often more than one way to skin a cat, your mileage may vary.<\/p>\n

    Technorati Tags: SQL Server<\/a>, SQL Server 2005<\/a>, SQL Server 2000<\/a>, SQL Server 2005 Express<\/a>, data migration<\/a>, SQL Script<\/a>, schema and data<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

    23\/06\/2010 UPDATE The database publishing wizard is integrated within Visual Studio 2008\/2010. Further information can be found here. As you might well imagine, the on disk structure for SQL Server 2005 differs from that of SQL Server 2000. Indeed, restoring a SQL Server 2005 [Express] database backup for use with SQL Server 2000 isn’t really … Continue reading Moving SQL Server 2005 Express databases to SQL Server 2000<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[150,152,151,146,148,147,149],"class_list":["post-546","post","type-post","status-publish","format-standard","hentry","category-development","tag-data-migration","tag-schema-and-data","tag-sql-script","tag-sql-server","tag-sql-server-2000","tag-sql-server-2005","tag-sql-server-2005-express"],"_links":{"self":[{"href":"http:\/\/www.craigmurphy.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/546","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.craigmurphy.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.craigmurphy.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.craigmurphy.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.craigmurphy.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=546"}],"version-history":[{"count":2,"href":"http:\/\/www.craigmurphy.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/546\/revisions"}],"predecessor-version":[{"id":1741,"href":"http:\/\/www.craigmurphy.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/546\/revisions\/1741"}],"wp:attachment":[{"href":"http:\/\/www.craigmurphy.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=546"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.craigmurphy.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=546"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.craigmurphy.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=546"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}