May 9, 2017 at 8:58 pm
I have found myself in a situation where I need to downgrade a database from SQL Server 2016 to SQL Server 2012. I know there is third party tools such as Redgate, Apex etc, but I have chosen to just script out the database to SQL Server 2012. I am running the script from command line with the following command:
sqlcmd -S SERVERNAME\SQLSERVERINSTANCE -U sa -P password -D DBNAME -V 14 -i F:\myscript.sql
The problem I face is that it is a rather large database(165GB scripted is 276GB) and I cant simply open it up in a text editor and fix the sql. I am getting the following error message:
Msg 102, Level 15, State 1, Server SERVERNAME\SQLSERVERINSTANCE, Line 82 Incorrect Syntax near '0.0000'.
My question is: is there a command line parameter that will bypass this error message or enter a default value? I have certainly examined all the sqlcmd parameters and came up with nothing. Or, is there something else I can do to bypass this message? As you can see by the sqlcmd above I have tried -V 14. It seems to me that it is attempting to insert a value of the wrong type or something.
May 10, 2017 at 3:24 am
Why not script out the database objects and all, users tables , sprocs, triggers etc.
Then use the Import data wizard to copy the data across. Why do you need to down grade.
May 10, 2017 at 6:49 am
Script just the structure, not the data. Then use SSIS or anything similar to move the data from one database to the other.
You can't downgrade a database. For that scenario, you can only copy it.
May 10, 2017 at 7:10 am
Don't script out the data. Script out the database structure, bcp (or SSIS) for the data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 10, 2017 at 7:52 am
Thank you all for the quick replies.
Using SSIS would be great but the SQL Server Instances are not on the same network. The front end application is designed for a LAN, so the scenario is that I copy the data(typically this is done using .bak) and then restore on my local system to mimick the application environment.
May 10, 2017 at 8:02 am
dirk.dromgoole 30656 - Wednesday, May 10, 2017 7:52 AMThank you all for the quick replies.Using SSIS would be great but the SQL Server Instances are not on the same network. The front end application is designed for a LAN, so the scenario is that I copy the data(typically this is done using .bak) and then restore on my local system to mimick the application environment.
If you can move the database from one place to the other, you can use SSIS. Otherwise, just go with flat files and bcp/bulk insert.
May 10, 2017 at 8:22 am
Well, an SSIS may be possible but I feel like that might take a very long time being as though the DB is 175GB. After scripting and compressing, the file was a little over 6GB so the file transfer was not too bad. That seriously might take days to copy the DB over the interweb.
I am looking into the flat files using bcp/bulk insert now. Thanks!
May 10, 2017 at 8:36 am
SSIS and bcp/bulk insert should be about the same speed, and faster than lots and lots of INSERT INTO statements, as they have the bulk-loading optimisations.
175GB isn't that large for a DB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 10, 2017 at 8:48 am
With that size, flat files might be a better option as you won't be using both databases while transferring the data through the network.
May 10, 2017 at 8:53 am
Back up the database and send by internet, sneakerweb or whatever. Then restore it next to the database created from your scripts, and bcp/SSIS the data from one database to the other.
John
May 10, 2017 at 9:51 am
The database is too large to restore to SQL Server 2016 Express Edition. The maximum database size is 10GB. I would need Enterprise or Standard.
May 10, 2017 at 10:04 am
You cannot restore a SQL 2016 database to a SQL 2012 server.
You can re-create an empty database on 2012, and transfer the data into it from 2016. There are any number of ways to transfer the data.
You can also download SQL 2016 Developer, which does not have the 10 GB limit
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 10, 2017 at 10:08 am
dirk.dromgoole 30656 - Wednesday, May 10, 2017 9:51 AMThe database is too large to restore to SQL Server 2016 Express Edition. The maximum database size is 10GB. I would need Enterprise or Standard.
If this is for development or QA purposes, I believe that you can use Developer Edition which is free with subscription to Visual Studio Dev Essentials.
May 10, 2017 at 11:27 am
Ahhh yes! I believe I can install the Developer Edition so I can restore the backup and then I can use SSIS, Generate Scripts or some other transfer mechanism much easier to move over to SQL Server 2012. I would say this is the best path forward. Thank you for all the help folks! Sorry, I didn't realize the Developer edition basically has an endless database size.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply