OK, then the question is, when I create a backup of a database (using SSMS), what determines the format of the backup? Is it the database engine the database currently resides or is it the version of SSMS that is being used to create the backup? And similarly, what determines the restore format, namely the database engine you are restoring to or the version of SSMS you are using to restore the database?


>A DB compatibility level only controls SQL Server engine behavior. What is affected is clearly stated in BOL ALTER DATABASE Compatibility Level topic for the latest SQL Version or sp_dbcmptlevel procedure description otherwise.
>MS SQL Server supports only forward compatibility of backups for 2 versions. The backward compatibility of backups is not supported at all. IOW, SQL 2008 backups cannot be restored to SQL 2005.
>If you want to move data in that direction, you have to use some other means.
>
>>
>>I have a SQL Server 2005 database on my server. I backup the database and copy the file down to my development PC where I am running SQL Server 2008 and I restore the database here. Now when I look at the database properties and compatibility level, it shows as SQL Server 2005 (90), which is what I would expect.
>>
>>My question is then how come, if the compatibility level is 2005, why can't I backup and then restore from my development PC back to SQL 2005 on the server? And, if I do want to perform this kind of activity, then what do I have to do to be able to do this? Do I need to install SQL 2005 on my development PC also and restore using SSMS 2005?
>>
>>Also, how does SSMS come into play on this? I prefer to work with SSMS 2008 due to the extra features (such as intellisense) that it offers.
>>