SQL: Copy database from newer MS-SQL to older version..

1 post / 0 new
benzzon's picture
SQL: Copy database from newer MS-SQL to older version..

1. Use SQL SSMS and generate script of selected database objects. Pick all tables, views, procs, but
   select only limited nr of users and roles.
2. If using SQL 2012 SSMS, use the "advanced" button to select script for SQL 2005 and also
   review all settings, for instance select to create indexes and triggers etc.
   Also be sure to select the option to create "dependency objects".
   Make sure NOT to include logins, this could affect logins on target server in unwanted ways!

3. Run the script on the empty database on the target server.
   Review any errors, and if possible make a "database compare" to see what objects that could not be created.
4. Make backup of the empty target-database..
5. Run BCP by using bat-files (see example on http://www.benzzon.se/?q=forum_sqlserver)
   (BCP seems to work better than import with SSMS..)

6. Run select queries in "source-db" and "target-db" that return table-names and number of records.
   Save the results as 2 "rpt"-files, and use WinMerge to compare them.

   Query below can be used to create queries to count rows in tables:
   select 'select ' + '''' + so.name + '''' + ',count(*) from ' + su.name + '.' + so.name
    from sysobjects so left outer join sysusers su on so.uid=su.uid  where so.type = 'u' order by so.name