backup to MSSQL 2012 from mssql 2016

Question

backup to MSSQL 2012 from mssql 2016

Edvard Korsbæk

SSCertifiable

Points: 5991
More actions
October 23, 2017 at 5:30 am

#325900

I do not think it's possible, but i could really use it.
Best regards
Edvard Korsbæk

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

Edvard KorsbÃ¦k - Monday, October 23, 2017 5:30 AM
I do not think it's possible, but i could really use it.
Best regards
Edvard KorsbÃ¦k

Depends what you're after. If it's a SQL Server 2016 database, then it's a no go. If it's in compatibility 110 (SQL Server 2012), or lower, then you can.

This does not mean change the compatibility of your database so you can!

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Edvard Korsbæk SSCertifiable Points: 5991 More actions · Answer 2

Thom A - Monday, October 23, 2017 5:47 AM
Depends what you're after. If it's a SQL Server 2016 database, then it's a no go. If it's in compatibility 110 (SQL Server 2012), or lower, then you can.
This does not mean change the compatibility of your database so you can!

-was in compability level 100, so it must be possible.

thanks!

jasona.work SSC Guru Points: 50082 More actions · Answer 3

Presuming you're trying to take a backup of a database on SQL 2016 to restore to SQL 2012, no, it is not possible regardless of the compatibility setting. All that impacts is how SQL handles certain features within TSQL code (ex: With the correct compatibility level setting, you could still use =* or *= for joins.)

Edvard Korsbæk SSCertifiable Points: 5991 More actions · Answer 4

I remember, that there is a way to save tables and content as queries somewhere in the mssql manager - I found tasks|generate scripts, but it does only make the tables. Where is it, I can make the records as INSERT queries?

Best regards

Edvard Korsbæk

Sue_H SSC Guru Points: 90860 More actions · Answer 5

Edvard KorsbÃ¦k - Monday, October 23, 2017 11:54 AM
I remember, that there is a way to save tables and content as queries somewhere in the mssql manager - I found tasks|generate scripts, but it does only make the tables. Where is it, I can make the records as INSERT queries?
Best regards
Edvard KorsbÃ¦k

You can change the scripting options to include data.
When you so through the tasks/generate scripts, you get to the Set Scripting Options page. On that page click on the Advanced button.
Under Advanced options, in the General section, scroll down to Types of Data to Script. Select Schema and Data if you want the tables and the data.

Sue

Edvard Korsbæk SSCertifiable Points: 5991 More actions · Answer 6

Edvard Korsbæk

SSCertifiable

Points: 5991

October 23, 2017 at 1:00 pm

#1964848

T H A N K S!

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 7

Edvard KorsbÃ¦k - Monday, October 23, 2017 1:00 PM
T H A N K S!

It's horribly slow because every row is a single Insert/Values. It would be better/faster for larger tables to export the data, say as a TAB delimited text file, and import it on the other side. For that matter, a linked server to pull the data across would be faster than the monster/bloated script that SSMS will generate for data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Edvard Korsbæk SSCertifiable Points: 5991 More actions · Answer 8

Of cause it's slow, but as it's only an example db with less than 50.000 records, its manageable. What really impressed me was, that a couple of encrypted fields used by my login system went through without any problems.
My laptop is perhaps 10 years old, and I am a bit surprised over how much it's able to after i changed to an SSD hard disk.
Even the SQL manager works without delay, and my program runs trouble free

Thanks for all help this Day - I go to my customer in a few hours.

Best regards

Edvard Korsbæk

Henrik Staun Poulsen SSCertifiable Points: 6549 More actions · Answer 9

Jeff Moden - Monday, October 23, 2017 8:17 PM
Edvard KorsbÃ¦k - Monday, October 23, 2017 1:00 PM
T H A N K S!
It's horribly slow because every row is a single Insert/Values. It would be better/faster for larger tables to export the data, say as a TAB delimited text file, and import it on the other side. For that matter, a linked server to pull the data across would be faster than the monster/bloated script that SSMS will generate for data.

It would have been useful to have the same "Script INSERT statements" on the individual tables too.
It is too well hidden here.
Thank you very much for posting the solution; I did not know it existed.