1

I've been dealing for trying to migrate a really big database to an earlier SQL Server with multiple ways, I started doing a .bak file, but I found that it is not compatible and it should be the same SQL Server version.

Then I chose the task - generate Script, to create a .sql file with all schema and data, but the file was 24gb big! Even though the file was really big by using the sqlcmd I managed to execute it. But it never finished executing successfully, it threw multiple types of errors, like:

Msg 156, Level 15, State 1:
Incorrect syntax near the keyword '...'

Msg 105, Level 15, State 1
Unclosed quotation mark after the character string

Then I found this comment with 2 solutions https://stackoverflow.com/a/27623706/3192041, I tried the first one but still throwing the second error, I tried the second one and It worked! everything was now running smoothly, but then I got another error...

This error:

Sqlcmd: Error: Internal error at ReadText (Reason: An attempt was made to move the file pointer before the beginning of the file).

So now the issue has something to do with sqlcmd command??

Should I continue trying to migrate the database with the generated script? is there a better way of this and making it compatible with an earlier SQL Server version?

Things to clarify

I first created a script only with schema information, but when I tried to generate a separate script for data only the SSMS was throwing an error. So with this way I can't or I don't know how to export all data with an easy way. I know you could export data for each of the tables, but the database has more than 200 tables and this is not viable.

Also the script takes more than one hour, and maybe a lot more than that time if the process would finish correctly.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
AlfredoDaAs
  • 145
  • 1
  • 14
  • Have you tried [bacpac](https://stackoverflow.com/a/30593611/529282) instead? – Martheen Apr 01 '20 at 01:19
  • yes, but I got two errors in the results, exporting database and validating schema errors. – AlfredoDaAs Apr 01 '20 at 01:25
  • the error message is: one or more unsupported elements were found in the schema used as part of data package. – AlfredoDaAs Apr 01 '20 at 01:26
  • Can you add the complete error message on the question? – Martheen Apr 01 '20 at 01:29
  • One or more unsupported elements were found in the schema used as part of a data package. Error SQL71564: Error validating element [UserKPI_old]: The element [UserKPI_old] has been orphaned from its login and cannot be deployed. Error SQL71564: Error validating element [MalcolmKPI_old]: The element [MalcolmKPI_old] has been orphaned from its login and cannot be deployed. Error SQL71564: Error validating element [VaKPI_old]: The element [VaKPI_old] has been orphaned from its login and cannot be deployed. Error SQL71564: Error validating element [AndronKPI_old]: The element... And so on... – AlfredoDaAs Apr 01 '20 at 01:33
  • Ah, try [mapping those users](https://stackoverflow.com/a/45005153/529282) – Martheen Apr 01 '20 at 01:41
  • In my case, I found I already have new users for each of the old users, so I'm deleting all "_old" users. I still have another error, but first I'll fix this. – AlfredoDaAs Apr 01 '20 at 01:48
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/210704/discussion-between-martheen-and-alfredodaas). – Martheen Apr 01 '20 at 01:51

1 Answers1

1

Finally after also trying with a bacpac file, that also didn't let me to create because of a bunch of errors of windows users, external object references, and more...

The best answer to solve this, is by creating a .dacpac file. The dacpac file from SSMS 2012 to the latest versions, you can now include the data of all your tables.

And to solve the incompatibility issue, you can use the AllowIncompatiblePlatform property to allow deployment to different versions of SQL Server when publishing to the target server.

so first you need to extract using the SqlPackage.exe from your bin folder of the sql server, in my case this is the folder: C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin

then run the command with the Extract action:

SqlPackage /Action:Extract /SourceDatabaseName:"<database-name>" /SourceServerName:"<server-name>" /SourceUser:"sa" /SourcePassword:"<password>" /TargetFile:"<dacpac-file-path>" /p:ExtractAllTableData=True

Then in the other server run this command in correct bin folder of the SqlPackage.exe program:

SqlPackage  /Action:Publish /SourceFile:" <dacpac file path>\filename.dacpac" /TargetDatabaseName:"<database name>" /TargetServerName:"<ServerName>" /TargetUser:"<username>" /TargetPassword:"<password>(if needed)" /p:AllowIncompatiblePlatform=true /p:CreateNewDatabase=true

And If you want to create the database from scrath.

/p:CreateNewDatabase=true

I hope this helps anyone with this problem, with big databases, and importing from a bigger sql server version.

AlfredoDaAs
  • 145
  • 1
  • 14
  • 1
    We got 'Skipping checking untrusted constraint' on every constraint on the import into sql2014, but this appears to have worked. Thanks! – Noah B Jun 16 '20 at 17:25