0

I have a production database running on MSSQL 2019 on a hosted platform. I am wanting to create a sandbox copy of the db with all of the data in a local MSSQL server running 2014.

Everything I've tried (backup / restore , generating scripts, export data) has error-ed out.

Does anyone have an easy button I am overlooking?

0

2 Answers 2

1

The harsh and direct answer would be no, you cannot downgrade.

You said scripting out and exporting data doesn't work, did you try to set the database compatibility mode to 2014. Then script out the database and create it on 2014 server. Then export the data?

2
  • When I try to "save or publish scripts", I get an non helpful "error " during Getting the list of objects from "db_name".
    – MattC
    Commented Feb 24, 2020 at 20:10
  • Which version of SSMS are you using to generate the scripts, and what permissions do you have when connecting? Commented Feb 24, 2020 at 20:48
1

Right answer - generate script. You can set compatible for SQL Server 2014.

Maybe you skipped it's an option?

You can do it via SSMS:

enter image description here enter image description here

3
  • When I try to open the script file, it gives me a memory warning. Script file is 6 Gb in size.
    – MattC
    Commented Feb 24, 2020 at 22:05
  • Try to export script via sqlcmd.exe. Execute in command line "sqlcmd < script.sql". Also. I work for a company that develops a sqlbackupandftp utility link . You can generate a script through it, and then create a restore job and export the script to the database. There you can also specify the compatibility level. I did not add this option in the answer, as this would be an advertisement. But it’s quite possible what it helps you.
    – Ivan
    Commented Feb 24, 2020 at 22:27
  • 6GB, just a curiosity... did you select to script the data long with it?
    – Laurent
    Commented Feb 25, 2020 at 21:18

Not the answer you're looking for? Browse other questions tagged or ask your own question.