Copy database Task failing with "Transferring columns of data type 'sql_variant' is not supported"

Sudz28 186

I have recently stood up a new Windows Server 2012 R2 server (VM) and installed SQL Server 2008 R2, in order to 'take over' for an older server (Windows Server 2008 R2) currently running SQL Server 2008 R2.

My plan is to copy over all the production databases (26 of them) over to the new server/SQL instance using the Tasks -> Copy Database option because this will let me copy all databases at once through a single process vice doing a backup/restore, which I'd have to do on each and every database one at a time. After working through some early issues I have the copy largely working, except for 5 of the 26 databases which continue to fail. The error every one of them throw on the destination machine is "ERROR : errorCode=0 description=Transferring columns of data type "sql_variant" is not supported. helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}".

Given that both instances of SQL Server are the same versions, I'm having a hard time figuring out why this error is being thrown and what the remedy to it might be. Normally I would assume it was related to the differences in the operating systems, but this seems to be an sql-specific issue. If the data type is in use in the production database, why would it be rejected by the new instance? In checking versioning, I DID notice that the prod machine is using MDAC 6.1.7601.17514 whereas the new machine is using 6.3.9600.17415, but I have no idea if this could be related to the issue. Nor do I have any idea why one is a higher version than another as I don't recall ever installing MDAC on its own so it must have come 'bundled' with something? The OS maybe?

Full disclosure, I am not a database admin by any stretch, just an IT generalist who's been given the task to move the production database to a newer version of Windows Server.

CathyJi-MSFT 21,086 Reputation points Microsoft Vendor

2021-05-17T02:59:38.783+00:00

Hi @Sudz28 ,

Any update for this thread? For your last question, did you check the suggestion from Erland? If the response from Erland helped, do "Accept Answer". If it is not, please let us know .

Accepted answer

Erland Sommarskog 100.9K Reputation points MVP

2021-05-10T21:48:19.887+00:00
The first part is easy:

SELECT 'BACKUP DATABASE ' + quotename(name) + ' TO DISK = ' + quotename('C:\temp\' + name + '.bak', '''') FROM sys.databases WHERE database_id > 4

If the databases are to be restored to the same locations on the new server, the RESTORE is just as easy. If you put the files in different folders, that's a little more work.

In any case, you should not use the Copy Database Wizard, as you have learnt the hard way. (The error message is telling you that it does not support this data type.)
Please sign in to rate this answer.
Sudz28 186 Reputation points

2021-05-11T00:04:29.757+00:00

Thanks - so the whole error about "sql_variant" is solely an issue with the Copy Database Wizard, and copying the databases via the command line as you show above will copy all 26 databases over? And will retain the sql_variant data types?
Cheers!

Sudz28 186 Reputation points

2021-05-11T12:46:52.443+00:00

Great! I have tried this as of this morning, however although the script appears to run ok (no errors thrown, Results displayed below the query appear to show it creating the various .bak files), no actual files are generated so I'm not sure what's going wrong.
Sample output from Results are like:
BACKUP DATABASE [database1name] TO DISK = 'E:\temp\database1name.bak'
BACKUP DATABASE [database2name] TO DISK = 'E:\temp\database2name.bak'
BACKUP DATABASE [database3name] TO DISK = 'E:\temp\database3name.bak'

But again, this executes and finishes in about 1 second and there aren't any files in E:\temp\ when I look.

Erland Sommarskog 100.9K Reputation points MVP

2021-05-11T20:55:40.123+00:00

and copying the databases via the command line as you show above will copy all 26 databases over?

Well,. that command does not copy the files - it creates the backups to copy.

And will retain the sql_variant data types?

Yes. When you restore a backup, you restore the database exactly as it is, since this done a low-level basis, page by page. Copy Database Wizards performs everything with SQL commands (and a client-side library) and needs to support for every little feature.

there aren't any files in E:\temp\ when I look.

You are looking at E:\temp on the machine where SQL Server is running and not your own local desktop?

And, oh, you are executing the BACKUP commands? The query I posted only generates the commands.
Sign in to comment

4 additional answers

CathyJi-MSFT 21,086 Reputation points Microsoft Vendor

2021-05-11T08:57:20.28+00:00

Hi @Sudz28 ,

>"ERROR : errorCode=0 description=Transferring columns of data type "sql_variant" is not supported.

According to the error message, data type "sql_variant" is not supported in Copy Database Wizard.

There are some limitations and restrictions in copy database wizard, such as it is not supported system database. refer to MS document Use the Copy Database Wizard to get more.

Did you try to use backups/restores or detach/reattach to migrate SQL instance. Please refer to the blog A Faster Way to Migrate SQL Server Instances to get more information.

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.
Sudz28 186 Reputation points

2021-05-11T12:31:32.247+00:00

I did do a couple of databases using backup/restore, but since I have 26 of them and the backup/restore functionality in SSMS only allows you to do a single database at a time I was hoping to do what I needed through the copy wizard. I guess that's not going to work though. I have not tried detach/reattach because I'm dealing with an existing production database that I can't take offline to play with this functionality so I haven't really looked into that. Maybe I will, just to see if it's a viable option in nothin else pans out.
Thank you for the input!

Sudz28 186 Reputation points

2021-05-11T12:32:52.987+00:00

I did do a couple of databases using backup/restore, but since I have 26 of them and the backup/restore functionality in SSMS only allows you to do a single database at a time I was hoping to do what I needed through the copy wizard. I guess that's not going to work though. I have not tried detach/reattach because I'm dealing with an existing production database that I can't take offline to play with this functionality so I haven't really looked into that. Maybe I will, just to see if it's a viable option in nothin else pans out.
Thank you for the input!

Erland Sommarskog 100.9K Reputation points MVP

2021-05-11T12:45:17.36+00:00

Yeah, using the UI for 26 databases is insane. (In fact, I don't find it a very good idea for even a single database.) That's why I gave you code to generate the commands.

Another alternative is to use a PowerShell script.

And, no, don't even think of detach/attach - that's a really poor option. (Alhough better than the SMO method in the Copy Database WIzard.)

Sudz28 186 Reputation points

2021-05-11T15:11:32.163+00:00

I was able to generate the commands and executed one successfully to the local disk - nice.

Then I mapped a network drive to the destination server and tried to get it to write the .bak file directly to THAT location (instead of a local drive) and it failed. It's throwing:
"Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'Z:\temp\database1name.bak'. Operating system error 3 (The system cannot find the path specified.).
Msg 3201, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally."

I then tried the same thing using the full URN vice a mapped drive designator, but I'm getting a different error now that Access is Denied. I tried to check what user the file was being written as and giving that user full permission to the destination share, but it's still throwing the error.

Is it not possible to write the .bak file directly to the destination server via either URN or mapped network drive?

Jeffrey Williams 1,886 Reputation points

2021-05-11T20:00:34.537+00:00

On either server - create a folder where you want to store the backup files. Once you have that location defined - then from Windows right-click on that folder and set it up as a shared folder. Grant permissions to the service account that is running SQL Server.

Then - use the UNC path to access that folder - for example: \dest_server\BackupFiles\

Erland Sommarskog 100.9K Reputation points MVP

2021-05-11T21:00:15.847+00:00

Rather than doing what Jeffery suggests, I would copy the files over to the other server.

There is no problem of granting permissions to the service account for SQL Server if it is running under a domain account, but if it is running under a local service account (which often is the case), you would have to grant permissions to the machine account for the SQL Server machine. So it is probably easier to copy the files.

Sudz28 186 Reputation points

2021-05-12T10:36:08.757+00:00

Thanks, I tried various ways of granting permissions to the folder, sharing the folder, etc., and wasn't able to get any traction. I think what I'll do is write the .bak files locally and then copy all he files across the network to the destination server, and restore them from there.
I appreciate the advice!

Sudz28 186 Reputation points

2021-05-12T10:58:50.397+00:00

Now that I have the .bak files written out for all 26 databases (it took 2.5 hours to complete the task), I will copy them over to the destination server and then restore them from that location.

Is there a quick/easy way to do this via command line like the generation of the .bak file BACKUP DATABASE commands, or do I actually need to go through the Restore wizard for each one of them? I guess it's not a big deal to 'hand jam' the individual database names and .bak file names into 26 RESTORE DATABASE commands, but figured it was worth asking the question.
You guys are great!

Jeffrey Williams 1,886 Reputation points

2021-05-12T14:11:04.087+00:00

Most likely - as Erland stated - you are running SQL Server under a local service account and would need to grant permissions to the machine account.

Jeffrey Williams 1,886 Reputation points

2021-05-12T14:19:16.6+00:00

You can use the same code that Erland provided to generate the backup commands - to generate the restore commands. Copy/Paste the results into a new query window connected to the destination instance and execute them.

It also isn't that hard to build the script - here is an example:

Declare @database varchar(50) = 'Database1' , @filename varchar(500) = 'M:\Restore\Database1.bak'; Restore Filelistonly From Disk = @filename; Restore Database @database From Disk = @filename With File = 1 , Replace , Recovery , Nounload , Stats = 5; Go

This assumes the databases are being restored to the same locations for all files - mdf/ldf/ndf and full text catalogs (if enabled). You can execute the first part - to the Restore Filelistonly to see what files are included in the backup and where they will be placed.

Erland Sommarskog 100.9K Reputation points MVP

2021-05-12T21:03:51.377+00:00

If you want to restore the database files to the same location as you had on the source server, just take that script and change BACKUP to RESTORE and TO to FROM. And possibly also the location of the backup files, if you placed them differently on the target.

If you want to move the files to new locations, well, we talk about that, if you tell us that this is the case.

Also, I assume that the databases does not currently not exist on the target server.

Sudz28 186 Reputation points

2021-05-13T16:52:59.37+00:00

testtset gg

Sudz28 186 Reputation points

2021-05-13T16:54:45.553+00:00

After creating the 26 individual .bak files on the production server, I copied them over the network to the destination server and successfully restored them on that server. I will delete/remove those same databases on the destination server next.

My plan is to stop the db on the current production server, make fresh .bak files of all databases, copy them to the destination server, restore them there, then power down the current production server. I will re-name the destination server and re-ip it so that it mirrors the current production server, reboot, and in theory everything that was pointing to the old production server will now be talking to the new server.

However, I am unsure of the process for stopping the current prod SQL instance and taking new, complete back up files of the databases. If I stop or Pause, I can no longer send commands to make the files. How does one keep the databases from ingesting new information while trying to get complete backup files for them?
Sign in to comment
Erland Sommarskog 100.9K Reputation points MVP

2021-05-13T21:21:31.267+00:00
However, I am unsure of the process for stopping the current prod SQL instance and taking new, complete back up files of the databases. If I stop or Pause, I can no longer send commands to make the files. How does one keep the databases from ingesting new information while trying to get complete backup files for them?

There are a couple of ways to skin that cat. One brutal way is do to:

DENY CONNECT SQL TO public

Now only sysadmin can log on to the server.

You can also run ALTER DATABASE db SET READ_ONLY on all databases before you start your backups. You will need to run ALTER DATABASE db SET READ_WRITE on the other server after the RESTORE.

A popular way is to set up log shipping, so that the downtime can be kept to a minimum, but if a few hours of downtime is not an issue to you, there is no need for that increased complexity.
Please sign in to rate this answer.
Sudz28 186 Reputation points

2021-05-18T17:41:17.363+00:00

So "DENY CONNECT SQL TO public" will prevent any/all external users or service accounts from accessing any of the databases? Maybe that would be preferable to issuing 26 ATLER DATABASE commands.

Erland Sommarskog 100.9K Reputation points MVP

2021-05-18T21:01:17.893+00:00

To be exact: it will prevent all logins that are not members of sysadmin to connect to the instance.

Hopefully, none of your service accounts are members of sysadmin, but, alas, this has been heard of.

Keep in mind that this will not close out SQL Server Agent, but you could stop Agent to prevent jobs from running.
Sign in to comment
Sudz28 186 Reputation points

2021-05-22T00:04:06.603+00:00
This MS forum has got to be the single worst internet forum I've ever experiences with it's formatting and it's flakiness at letting you post, which seems completely random and arbitrary. I've been trying for three days to post a reply to @Erland Sommarskog but it just won't take, so I'll try this method of posting it as an Answer.

So altering back to READ_WRITE will make the database behave as they did prior? There's only "READ ONLY" or "READ WRITE", nothing else I would need to double-check?
So to sum up on how to do this entire operation:

On original db server, set all 26 databases to READ_ONLY.

On original server, execute the BACKUP DATABASE commands to create a .bak file on the local drive for all databases.

Copy the 26 individual .bak files across the network to the same location on what will be the new DB server ('destination' server)

On destination db server, execute the RESTORE DATABASE command for all 2 .bak files.

On destination server, set all 26 databases to READ_WRITE

Power down original server.

Rename destination server to original server's name, re-ip destination server to the original server's IP address. Reboot destination server.

Once destination server is back online, ensure Sharepoint/whatever is connecting to it and can query for data/write data.

Done!
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Erland Sommarskog 100.9K Reputation points MVP

2021-05-22T09:28:24.28+00:00
This MS forum has got to be the single worst internet forum I've ever experiences with it's formatting and it's flakiness at letting you post, which seems completely random and arbitrary.

I take it that you never used the old forums - they were a lot worse in my opinion.

I occasionally have problems with posting, but those problems tend to be temporary. But, yes, I don't like this division into Comments and Answers. I have the impression the idea is that Q&A should be like Stackoverflow, that is more of a answer repository than a discussion forum, but that is not how it works in practice. And in that context the limitation of 1000 characters for Comments becomes a nuisance..

Anyway, I think your list looks fine. I have two remarks:

In point 4, I assume that "2" is a typo for "26".

For step 7, don't forget to run this on the destination server: EXEC sp_dropserver DESTINATIONNAME EXEC sp_addserver ORIGINNAME, local

You need to do this, so that @@servername gets the correct value.

Then again, it can't be a bad idea to turn off application services like Sharepoint. I haven't worked with Sharepoint, so I don't know it reacts when the database all of a sudden becomes readonly.
Please sign in to rate this answer.
Sudz28 186 Reputation points

2021-05-28T13:15:58.86+00:00

Oh boy, thanks for last tip regarding step 7 - that was NOT on my radar at all! Just to make sure I understand what you're saying, after I have copied over the .bak files and restored them/set them back to READ_WRITE and powered down the original and renamed the destination servers itself I need to run those two commands... exactly as written? Or in my case it would be more like:
EXEC sp_dropserver NEWPRODDB
EXEC sp_addserver PRODDB, local

... given that the current/original database server is named PRODDB and the new one I'm migrating to is currently called "NEWPRODDB" but will be renamed to PRODDB...
Again, many thanks!

Erland Sommarskog 100.9K Reputation points MVP

2021-05-28T21:05:57.887+00:00

Yes, that should be it. The damage if you neglect to it is not that big, but there may be code that rely on @@servername could be confused.

By the way, also check sys.server_principals to see if there are any local Windows accounts with the old server name. I assume that you are in a domain, and in such case,, I would not expect that there are any, but it is always worth checking.

Sudz28 186 Reputation points

2021-05-29T15:16:32.96+00:00

Literally at work right now doing this migration! With step 7, the re-name and re-ip of the destination server to the original server name/ip, does it matter "when" I run the EXEC sp_xxxxx commands on the database? In other words, do I run that before I perform the rename/re-ip, or after, or doesn't it matter?

Erland Sommarskog 100.9K Reputation points MVP

2021-05-29T17:36:20.12+00:00

You don't run it on the database. Your run it on the server. You should run it after you have renamed the server. With regards to changing the IP-address, it does not matter.

Sudz28 186 Reputation points

2021-05-31T14:05:05.553+00:00

Sorry, sometimes my lack of DB experience has me using incorrect terms. On the server via Server Management Studio I click 'new query' and put in the SP_xxxx commands, but it errs out. I actually do have a dash character in my server names (PROD-DB, NEWPROD-DB) and it seems as though the command doesn't like these characters. I'm guessing I may be unable to run these commands because of it?

In any case I finished the migration yesterday (minus the EXEC sp_xxxx commands of course) and thus far everything seems to have been working as it did before. Again, many thanks to you for all your help in guiding me through the process and increasing my understanding of MSQL!

Erland Sommarskog 100.9K Reputation points MVP

2021-05-31T21:39:51.887+00:00

I actually do have a dash character in my server names (PROD-DB, NEWPROD-DB) and it seems as though the command doesn't like these characters. I'm guessing I may be unable to run these commands because of it?

You need to enclose the names in quotes (or brackets).

This is the normal thing when you call stored procedures; you should embed strings in quotes. However, SQL Server permits naked strings, exactly to make the system procedures easier to use. As long as your names do not include non-identifier characters. (PROD-DB falls into three tokens.)
Sign in to comment