0

I exported an Azure SQL DB in the bacpac format, and imported it to a new server. All data is restored as it should be. However my tables have a timestamp (rowversion) column.

This means importing to another server changes the values in these columns as rowversion type is server dependent.

I need to keep these values the same in the new db. So whats the best way to accomplish this?

As timestamp is of type binary(8), I tried to make a binary(8) column where I copied the timestamp values into, which works. Than I tried to alter the binary(8) to timestamp, as it is basically the same? However I get this error: Failed to execute query. Error: Cannot alter column 'mycol' to be data type timestamp.

Do you have any ideas how I can make altering binary(8) to timestamp work.

Or do you have ideas on how to restore the db and keep the timestamp values the same?

user2415476
  • 211
  • 1
  • 15
  • You can't do this with a BACPAC, because that essentially re-creates the database. You can't have a new database with rowversion values copied from somewhere else, because the engine couldn't guarantee that new values wouldn't start to conflict with old ones (the new database doesn't share the old database's transaction timestamp). The safe way to do this (and I think the *only* way to do this) would be to back up the database and restore it the usual way -- with `BACKUP` and `RESTORE`. – Jeroen Mostert Feb 20 '20 at 15:00
  • Thank you. Do you know if BACKUP and RESTORE works for Azure dbs, too? At https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-full-database-backup-sql-server?view=sql-server-ver15 it seems, Azure dbs don't apply? – user2415476 Feb 20 '20 at 15:07
  • Yes, you can use Azure Blobs for that -- `BACKUP` supports [backing up to a URL](https://learn.microsoft.com/sql/relational-databases/backup-restore/sql-server-backup-to-url). I have no personal experience with Azure SQL, though, I just know it's possible (not how you set it up and what the costs are). – Jeroen Mostert Feb 20 '20 at 15:09
  • Alternatively, if it doesn't work I would copy the rowversion values in a binary(8) column and change these values with a trigger from now on (on insert/update). Do you think there is something to be said against doing that this way? – user2415476 Feb 20 '20 at 15:16
  • You'll run into the same problem as you would have if the database allowed you to insert explicit values: how are you going to ensure the "new" timestamps are always after the "old" ones, or at the very least "different"? You cannot rely on `@@DBTS` giving you correct values for that. This could work if you don't actually care about the versions as they are now -- then you could zero out all the existing values and rely on future timestamps as generated, but then of course you lose the link with the old data. – Jeroen Mostert Feb 20 '20 at 15:18

0 Answers0