0

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?

  • If you are able to manually set the timestamp column to ignore, can you adjust the timestamp column programmatically so as to ignore that datum? Just wondering about your approach for the 'Ignore'. – RLF Nov 11 '17 at 21:23
  • The ignore is a manual step in the wizard. How are you suggesting to do this programmatically? – Tony_Henrich Nov 11 '17 at 21:27
  • 1
    I would script out all the tables you want, then bcp.exe out the data, and then bcp.exe in. bcp.exe should ignore the timestamp values on the import. You should be able to use TSQL to build all of your bcp.exe statements. – Greg Nov 12 '17 at 2:02
  • Greg bcp requires access to the file system which I said I don't have access to. – Tony_Henrich Nov 13 '17 at 5:31
  • @Tony_Henrich - Tony, actually bcp can write to a file share which can point to another server (or even your computer) which you do have access to. Do you have the ability to run xp_cmdshell on the source server? – Scott Hodgin Nov 13 '17 at 12:49
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;
  • Backing to network drive actually worked. I tried before with other machines and it didn't. Maybe sql server service is running under a domain user. – Tony_Henrich Nov 15 '17 at 2:57
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.