I have found BCP to be more effective than some of the tools for getting data into an earlier version of SQL Server, and for pulling data from RDS. (Thanks @ivan_posdeev.)
I first generate the schema by right-clicking on the database in SQL Server Management Studio, Tasks, Generate scripts. Tick all objects, in advanced objects ensure everything you need is going to be scripted (stats, indexes, etc), untick "USE database" if your destination database has a different name, set the compatibility to your destination database version, and generate a file that creates your schema. Create a database on your destination and run this file on it (using osql
, sqlcmd
or the GUI).
To move the data, run the following query twice on the source database, first commenting the second column to generate the batch file to extract data, then commenting the first column to generate the import batch file to run on your destination. (You need to add your source and destination servers, instance names, output and input file directories, usernames and passwords. To use integrated security replace the -U
and -P
options with just -T
.)
This supports Unicode, if you don't need that, change the -N
switch in both statements to -n
.
SELECT
'bcp SOURCEDATABASE.' + s.Name + '.' + t.NAME + ' out d:\dbdump\' + s.Name + '.' + t.NAME + '.dat -N -S SOURCESERVER\INSTANCE -UUSER -PPASSWORD'
-- 'bcp DESTINATIONDATASE.' + s.Name + '.' + t.NAME + ' in d:\dbdump\' + s.Name + '.' + t.NAME + '.dat -N -S DESTINATIONSERVER\INSTANCE -UUSER -PPASSWORD -E -h TABLOCK -b 1000 -e d:\dbdump\' + s.Name + '.' + t.NAME + '.ERRORS.dat'
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
ORDER BY
s.Name, t.NAME
After running check files named schema.tablename.ERRORS.dat - will include any rows that failed, empty if none did.
MSDN reference for BCP here, more friendly walk through of the BCP options here.
I have found this vastly superior to generating scripts, and any of the tools I've tried. It also runs on RDS databases (which don't permit backups). The data files generated are 30% the size of the SQL scripts, running them takes a fraction of the time, and is much more reliable. (The scripts generated by SQL Server for scripting data always tripped up, sometimes predictably sometimes not, the SQL generated was not compatible with 2008R2 (eg, used nvarchar(0)
, often didn't complete for no discernible reason, etc. BCP also replicates any constraint violations, such as referential integrity.).