Just like any data store, SQL Server requires maintenance. This is true if the database is managing a Fortune 100 company or your desktop collection of CDs. This means your database must be backed up periodically. Just how often depends on the value of the data, how often it changes, and how often you have access to the database. Without stating the obvious, this is a very serious subject for some companies and not much of an issue for others. Regardless, you should learn how to back up SQL Server databases. SQL Server Management Studio makes this pretty easy. One easy way to back up a database is to simply "detach" the database (from master), copy the file to a safe location, and "attach" it again. This can be done easily with SQL Server Management Studio but requires that all connections be dropped beforehandor the detach does it for you. Getting into SQL Server Management Studio is easyjust choose it from the Start/Run menu and open a connection to your server instance as SA or using a Windows login that's in the Administrators group. From there, all of the maintenance functions are pretty easy to access. Attaching a .MDF Database FileTo attach any database to master:
If the .MDF file is from an older version, SQL Server validates it and converts it to SQL Server 2005. In this case, the files will be altered and cannot be accessed by the previous version. Note that you can't attach a database whose name already exists or whose files are already attached to masteron any SQL Server. If you import an older version, the database is marked as being compatible with the old version (not 9.0the SQL Server 2005 version). This means that many of the SQL Server 2005 features and new T-SQL syntax cannot be used against the newly attached databasenot until you set the compatibility level to 9.0 (or to the latest version). This is accomplished by opening the database Properties page (right-click on the database in the Object Explorer and choose "Properties"), navigating to the Options page, and setting the Compatibility level, as shown in Figure 2.91. Figure 2.91. Setting the database compatibility level and other options.Setting the Auto Close OptionWhile we're on the options page, note that you have the option of setting how the server behaves when the last connection is closed (as when SQL Server Express is deployed to a user's system). In this case, it makes sense to set the Auto Close option to True. When True, the database is shut down cleanly and its resources are freed after the last user logs off. This makes it easy (and safe) to copy, delete, or uninstall the database and the applications that use it. Importing and Exporting DataIf SQL Server does anything well, it's importing and exporting data. Given years of experience with customers wanting to bring existing data into SQL Server, Microsoft has created a highly respected data import engine to handle virtually any type of data from virtually any source. All of this functionality has been refined and improved over the years. The SQL Server 2005 version now calls it "SSIS" for SQL Server Integration Services. It's the successor to its very popular DTS (Data Transformation Services). I'm not going to bore you with a walk-through, as the SSIS wizard launched from the same SQL Server Management Studio database "Tasks" option menu is very intuitive. Backing Up SQL Server DatabasesSQL Server Management Studio can also run a "real" backup of your database without disconnecting or disrupting anyone. This means you can safely (and fully) back up a live production database with dozens of connections open and active. To do so:
Note that SQL Server can save backups incrementally, so instead of overlaying each backup with the previous, you can simply create delta backups and restore back to any specific point in time. Again, check with BOL or your DBA for details. Note that this backup does not back up the log, as I'll discuss in the next section. |