How to export data from a SQL Server database with hundreds of tables and timestamp columns?

Question

I have a SQL Server database with hundreds of tables and they all have timestamp columns. I don't have access to backups or the server's file system. To use the export data wizard, I have to go into each table's mapping and set the timestamp column destination to Ignore. It's very time consuming. Using SSMS 2017 (17.3).

Any ideas on how to export the data with minimum effort?

Answer 1

If the account that the SQL Service is running under has access to any network shares, then you can use database backup to a shared folder, and restore from there as well.

BACKUP DATABASE [dbname] TO DISK = '\\servername\sharedfoldername\dbname.bak'
RESTORE DATABASE [dbname] FROM DISK = '\\servername\sharedfoldername\dbname.bak'

Or you can use bcp.exe. Use SSMS Object Explorer to script out all the tables, and execute the script on the desired database Use TSQL to build out all the bcp.exe commands, something like this:

select 'bcp.exe ' + quotename(object_schema_name(object_id)) + '.' + quotename(name) + ' out "\\server\someshare\' + name + '.bcp" -n -S ' + @@servername + ' -d "sourceDb" -T '  from sys.tables order by name;
select 'bcp.exe ' + quotename(object_schema_name(object_id)) + '.' + quotename(name) + ' in "\\server\someshare\' + name + '.bcp" -S "DestinationInstance" -d "destinationDb" -T '  from sys.tables order by name;

Answer 2

The SQL Server Import/Export Wizard basically creates an SSIS package. It gives you the option to save that package, instead of running it.

An SSIS package is essentially a big XML file.

I would generate a package to move two tables, twice - once with the first table's timestamp column not set to "Ignore", and then with the first table's timestamp column set to "Ignore". Save both packages, open them in a text editor, and compare them. There will be a number of different ID value (from different runs of the process), but the only substantial difference should be the setting for the timestamp column.

Based on how that's different in the two files, make a copy of the second file, and try modifying the settings for the second table to ignore the timestamp column. Test run the file, to see if it works; if it does, generate the code for all the tables, and shift all the timestamp columns to "Ignore".

NOTE: I assume that there will be some easy way to identify the timestamp column in each table (maybe they're all conveniently named timestamp), and that you can employ some sort of regular expression to find and replace all of them; otherwise, this would be about as painful as manually turning them off.

I'll admit, the couple of time I had to do this with a similar database (to copy all the data to the same data structure in an older version of SQL Server), I just went ahead and manually reset the values; it was annoying, but I had a good idea of the time involved, and anything else would have been likely to take longer.