Administrative Functions

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 File

To attach any database to master:

1.	Right-click on the Databases icon in the Object Explorer and choose "Attach".
2.	This opens a dialog that prompts you for the path of the database to attach. Click the Add button to open a file finder dialog, as shown in Figure 2.89. Point to the .MDF file to attach. Figure 2.89. Attaching a database with SQL Server Management Studio.
3.	Choose an .MDF file from the list. No, the file cannot be on a network share. SQL Server does not permit that. Note that the .MDF file might also have an .LDF (log file) in the same directory (or elsewhere). This is captured automatically. Some databases are stored in several .MDF filesall of which appear in the Attach Databases dialog once you choose a root .MDF, as shown in Figure 2.90. Figure 2.90. Attaching a multi-file database with its log file.
4.	Once you've selected the database(s) to attach, click OK. SQL Server takes it from there.

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 Option

While 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 Data

If 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 Databases

SQL 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:

1.	Drill into the Object Explorer in SQL Server Management Studio, select the database to back up, and right-click. At this point, you can choose to detach or back up the selected database from the Tasks list. In this case, we're going to back it up. If you choose to detach an .MDF, drill down to the physical file location so you'll be able to copy it to a safe location.
2.	Once you've chosen "Backup...", the Back Up Database dialog prompts you for a number of options. While there are many options that your DBA can explain, let's just make a simple backup.
3.	Fill in the name of your database (it should already be set), and choose a backup type. In this case, we're doing a full backup. You can also perform a "differential" backup that records only the changes made since the last backup.
4.	Choose "Database" as the backup component, choose a name for the Backup set, fill in a description, and choose 0 for the expiration date (which is no expiration).
5.	For the disk location, you're going to want to click on Add to point to where you want the backup file saved. Choose a convenient location because you're also going to want to move that backup file off-site to protect it. At this point, your dialog should look like Figure 2.92. Figure 2.92. Completing the Back Up Database dialog.
6.	Click OK to proceed with the backup.
7.	Once the database is backed up to the specified file location, you'll get a promptafter which you can copy the file to a safe location.

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.