4

I am successfully doing a daily restore of a backup from an earlier version onto MS SQL 2017 using T-SQL.

However during the restore the database gets upgraded to the current version.

This is a problem as I wish to modify the database and then do a backup which then gets transferred and restored to another system which is using the same earlier version of MS SQL.

Is it possible to run the RESTORE without the database getting upgraded?

Have been looking through T-SQL documentation and not found this referred to.

The T-SQL code I am using is:

USE master
GO
ALTER DATABASE Polly SET SINGLE_USER
with ROLLBACK IMMEDIATE
GO

RESTORE DATABASE Polly FROM DISK = 'C:\data\Polly.bak';
GO

USE Polly
DELETE FROM SytLog;
GO

ALTER DATABASE Polly SET MULTI_USER
GO

If anyone has general improvements to above I am happy for suggestions.

When the above runs I get: Database 'Polly' running the upgrade step from version 782 to version 801. ... Database 'Polly' running the upgrade step from version 868 to version 869.

Would like to see no upgrade steps, but only if database still usable.

LeighMS
  • 43
  • 1
  • 4
  • 1
    `without the database getting upgraded` why? You can set the database's compatibility level if you want it to be treated as an older version. Preventing the upgrade would only make sense if you wanted to take those database files and attach them to an older version. – Panagiotis Kanavos Feb 14 '19 at 11:30
  • Check [the remarks on compatibility levels](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017#remarks). If the backup came from SQL Server 2008 or later, it remains the same. – Panagiotis Kanavos Feb 14 '19 at 11:36
  • @PanagiotisKanavos - thats what the OP wants to do though, change it and backup/restore back to a previous version. – Andrew Feb 14 '19 at 11:38
  • No, this isn't possible. Once a database is attached to a newer version of SQL Server, you can't move it back to older. The only option in this case is to export/import the data. If you need to modify the database and move it back to the older server, you need to install a server with the same old version and do the restores there! – Andrey Nikolov Feb 14 '19 at 11:38
  • 1
    @AndreyNikolov - that's the answer that needs to be posted, can you convert it to one? – Andrew Feb 14 '19 at 11:40
  • @Andrew that's a different impossible thing - downgrading. There are duplicates about this – Panagiotis Kanavos Feb 14 '19 at 11:41
  • 1
    Possible duplicate of [SQL Server database backup restore on lower version](https://stackoverflow.com/questions/6183139/sql-server-database-backup-restore-on-lower-version) – Panagiotis Kanavos Feb 14 '19 at 11:42
  • Panagiotis Kanavos- don't want it upgrading for exactly the reason you say, it is modified to shrink it down and then backed up and restored onto a different system that uses an older version which has and old Express version. This is part of a system where the software uses an older Express MS SQL install to operate. Need to shrink the database below 10GB so Express can take it. – LeighMS Feb 14 '19 at 11:44
  • Unless I have completely misunderstood, I can alter compatibility level on the database and then it won't update the restored datbase. ALTER DATABASE Polly SET COMPATIBILITY_LEVEL = 110 Assuming source server version is 2012. – LeighMS Feb 14 '19 at 11:55
  • @LeighMS COMPATIBILITY_LEVEL does not garantee that database files will be unchanged with RESTORE. – Alex Yu Feb 14 '19 at 11:58
  • Using a product called Red Gate enables BAK files to be interrogated for different versions irrespective of whether the SQL server is running an older version. although getting newer databases to restore to older versions is not pain free. – jimmy8ball Feb 14 '19 at 14:00

4 Answers4

2

When the above runs I get: Database 'Polly' running the upgrade step from version 782 to version 801. ... Database 'Polly' running the upgrade step from version 868 to version 869.

Would like to see no upgrade steps, but only if database still usable.

This is not possible. Every version of SQL Server has its data and log files structure that differs between server versions. And if you restore or attach database from lower version db files are one-way updated to have a structure that the current version of SQL Server needs.

It's impossible to not upgrade because the current version of server needs that new structure for db files.

All speculations around compatibility level / read_only property will not help at all, current server will never run with files that are not of the structure it needs.

In case of readonly database its files will be upgrade but the database will remain readonly.

Compatibility level has nothing to do with database version (version of db files) at all. It just tell to server what version of query optimizer should be used, what legacy syntax can still be parsed, etc.

sepupic
  • 8,409
  • 1
  • 9
  • 20
  • Have tried playing round with compatibility and read_only, and as you probably know, it did not work. From my perspective the best solution is to find installation media for the version of MS SQL that matches with what is installed by the software. Given the size of it I am not taken by the idea of SQL dumps. Seems a clumsy solution, and might not achieve it as the database has a complex structure of heavily interlinked tables. – LeighMS Feb 15 '19 at 15:18
  • >>>Given the size of it I am not taken by the idea of SQL dumps<<< What do you mean by "dumps"? Do you want to estimate backup size given database size? – sepupic Feb 15 '19 at 15:25
0

No, this isn't possible. Once a database is attached to a newer version of SQL Server, you can't move it back to older. The only option in this case is to export/import the data. If you need to modify the database and move it back to the older server, you need to install a server with the same old version and do the restores there!

Andrey Nikolov
  • 12,967
  • 3
  • 20
  • 32
  • I don't mind installing older version of SQl Server. Am currently running on evaluation but intend to get an licence. Would a licence purchased now be applicable to SQL 2012? I do find the MS licensing information pretty complicated. However, before I do this might just try dropping the table, recreating, setting compaibility level to 110 and restoring to it and see what happens. If this doesn't crack it try it as the read-only, which is fine for this purpose, and restore. Failing these then get rid of 2107 and go back to 2012. – LeighMS Feb 14 '19 at 13:46
  • Depending on your use case, Developer Edition might be appropriate! – Ben Thul Feb 14 '19 at 13:51
  • @BenThul Nope! You are not allowed to restore production data on Dev edition for inspecting and eventually restoring them back on prod. Plus, Dev edition in older SQL Server versions are paid ($50 or something, but still have to buy it). – Andrey Nikolov Feb 14 '19 at 14:27
  • @LeighMS I can't tell for sure. Downgrade rights are not my field. As far as I know, you can downgrade up to two levels, i.e. from 2017 to 2016 and then to 2014, so probably you can't use this trick for 2012. But I'm not sure about that. – Andrey Nikolov Feb 14 '19 at 14:32
  • @AndreyNikolov - while this process is being developed, Developer Edition send right to me. The upside is that it can be upgraded to whatever edition once the process is nature. That said, if also wonder why restoring the database on the same instance as a different database name isn't an option. It's (presumably) already licensed... – Ben Thul Feb 14 '19 at 14:38
  • @BenThul In the real world it is almost impossible to find an organization, where you can get permission to restore a DB on Prod to check it or modify it. Highly unlikely, but it is possible, though – Andrey Nikolov Feb 14 '19 at 16:01
  • Agreed. But I'm also not sure if the final destination is prod or not! – Ben Thul Feb 14 '19 at 16:10
-1

Microsoft documentation states:

If you restore a SQL Server 2005 (9.x) or higher database to SQL Server 2017, the database is automatically upgraded.

Although I would try in case of desperate need to create read-only database and restore it.

But this is kind of black magic not directly supported by vendor.

Alex Yu
  • 3,412
  • 1
  • 25
  • 38
-1

The compatibility level is important. Even when the compatibility level of the database needs to be changed, under certain conditions the backup can’t be simply restored.

Restoring a database backup created on a SQL Server 2008 R2 to a SQL Server 2012 instance goes smoothly, but if a SQL Server 2000 backup is tried to be restored on a SQL Server 2012 you cannot directly do it.

karel
  • 5,489
  • 46
  • 45
  • 50
Milad
  • 117
  • 1
  • 8