SQL Server Data Export – Handling Hundreds of Tables with Timestamps

exportimportsql serverssms

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?

Best Answer

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;
Related Question