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.
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.