Each new version of SQL Server includes a tremendous amount of new functionality, much of which requires new keywords and also changes certain behaviors that existed in earlier versions. To provide maximum backward compatibility, Microsoft allows you to set the compatibility level of a database to one of the following modes: 90, 80, 70, 65, or 60. Compatibility levels 65 and 60 are being deprecated, and 60 is not supported by SQL Server Management Studio or SMO. All newly created databases in SQL Server 2005 have a compatibility level of 90 unless you change the level for the model database. A database that has been upgraded or attached from an older version will have its compatibility level set to the version from which the database was upgraded.
All the examples and
EXEC sp_dbcmptlevel ' <database name> ';
To change to a different compatibility level, run the procedure using a second argument of one of the possible modes:
EXEC sp_dbcmptlevel '<database name>', <compatibility-level>;
Not all changes in behavior from older versions of SQL Server can be
For a complete list of the behavioral differences between the compatibility levels and the new keywords, see the online documentation for the sp_dbcmptlevel procedure.
A database is a collection of objects such as tables, views, and stored procedures. Although a typical SQL Server installation has many databases, it always includes the following three: master, model , and tempdb . (An installation usually also includes msdb, but that database can be removed.) Every database has its own transaction log; integrity constraints among objects keep a database logically consistent.
Databases are stored in operating system files in a one-to-many relationship. Each database has at least one file for data and one file for the transaction log. You can easily increase and decrease the
Chapter 5. Logging and Recovery
In the previous chapter, I told you about the data files that are created to hold information in a Microsoft SQL Server database. Every database also has at least one file that stores its transaction log. I made reference to SQL Server transaction logs and log files in Chapter 4, but I did not really go into detail about how a log file is different from a data file and exactly how SQL Server uses its log files. In this chapter, I'll tell you about the structure of SQL Server log files and how they're managed when transaction information is logged. I'll explain how SQL Server log files grow and when and how a log file can be reduced in size. Finally, we'll look at how log files are used during SQL Server backup and restore operations and how they are affected by your database's recovery model.