I have restored a .bak
file from my SQL Server 2008 R2 to my 2017 instance (both Developer editions) and it seems to work fine.
Does it really restore all the user defined objects? And are all those objects work same as the way they work in 2008 R2 instance?
Yes, SQL Server 2017 has a forward compatibility with backups created in SQL Server 2008R2. The restore process restores all user and system objects and then perform an upgrade of them to a current version.
Important: the database will still be in an old compatibility level - 100
, which corresponds to SQL Server 2008R2. In order to get all new optimizations and features available, consider to change your database to the latest compatibility level. For SQL Server 2017 it is: 140
.
ALTER DATABASE [DbName] SET COMPATIBILITY_LEVEL = 140;
This will also enable query optimizer and t-sql enhancements which were introduced since 2008R2.
And then update statistics
USE [DbName]
EXEC SP_UPDATESTATS
Worth to mention that compatibility change is a backward reversal, while backup created in SQL Server 2017 cannot be restored in 2008R2.