October 16, 2019 at 6:34 pm
Hello,
I'm attempting to use a specific backup file from a week ago that was taken from a SQL Server 2016 instance and restore it as a new database onto a different instance, which uses SQL Server 2008 R2. When I try to script this, I get the following error:
"The media family on device [.bak file location] is incorrectly formed. SQL Server cannot process this media family."
If I try using the UI and I choose the device, no database can be found in the "Database" drop-down underneath "Device."
Any ideas on how I can achieve this?
Help is greatly appreciated as always!
October 16, 2019 at 6:40 pm
it is not possible to restore a SQL database from a higher version to a lower version.
you can only restore from lower to higher versions.
you will have to use something like ImportWizard to copy all the tables and data, and separately grab the users and any permissions via a script
Lowell
October 16, 2019 at 7:56 pm
Thanks for the reply Lowell.
As far as workarounds go, I'm running into issues with using the data export wizard to populate the data on the other instance. The error message is "Length or precision specification 0 is invalid." I have over 200 different tables/views I would want to have in the destination SQL instance, and the process of manually setting length or precision values for each doesn't seem tenable.
I've seen some suggestions of using the "Generate Scripts" to generate the database and its objects and data, but I'm concerned about how long it will take to produce and execute these scripts for a fairly large database (60gb data file). Is it comparable time and resource-wise to just doing a restore, or are there risks to this approach that I should be aware of?
October 16, 2019 at 8:05 pm
I didn't do this before, but you can set up merge replication between SQL 2016 and SQL 2008. Select all tables and once snapshot is applied to subscriber/SQL 2008, you will have all data on SQL 2008 from SQL 2016.
Restore SQL 2016 backup on SQL 2008 is not allowed out of the box.
October 16, 2019 at 8:35 pm
generating the schema scripts for tables/views/procs is easy in SSMS, but don't script the data. 60 gig of data is unmanageable as flat files, you want to use The import wizard, because the SSIS portion batches the files into groups of rows as a continuous stream.
also, you want to script out the constraints separately, so you can import the data tables in any order without worrying about foreign keys, and after the data is copied over, to re-enable the constraints.
Another main annoyance then is that each table you check in the SSIS wizard also needs to check the "enable identity insert" for each table too, to preserve the existing values.
Replication would work as suggested as well, the replication process creates the tables for you, but moves the data over row-by-agonizing row, but it gets it over there eventually.
Lowell
October 18, 2019 at 9:37 am
No, is not possible to downgrade a database. There is absolutely no way you can restore or attach this database to the SQL Server 2016 instance you are trying to restore on SQL Server 2012. Your only options are:
You can use functionality called Export Data-Tier Application which generates .bacpac file consisting database schema and data.
On destination server, you can use Import Data-Tier Application option which creates and populates new database from pre-created .bacpac file
If you want just to transfer database schema, you can use Extract Data-Tier Application for creating file and Deploy Data-Tier Application for deploying created database schema.
I've tried this process on different versions of SQL Server from SQL 2014 to SQL 2012 and from SQL 2014 to SQL 2008R2 and worked well.
SQL Database Recovery Expert 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply