Database Reference
In-Depth Information
Figure 5-14.
Setting the recovery model
Performing Database Backups
Backing up the transaction log is quite simple, and you can do it with a single line of SQL code. You cannot back up the
log, however, unless you have already backed up the whole database at least once. You must do this even if you are not
going to use the database backup, but you can reuse the same backup file multiple times by clearing its contents using
the
INIT
option. Listing 5-2 shows a full database backup, followed by a transaction log backup to the same file.
Listing 5-2.
Backing Up the Log File
BACKUP DATABASE [DWWeatherTracker] TO DISK=N'C:\_BISolutions\DWWeatherTracker.bak'
GO
BACKUP LOG [DWWeatherTracker] TO DISK=N'C:\_BISolutions\DWWeatherTracker.bak' WITH INIT
When you back up the transaction log, it clears out entries in the log file, freeing up space for more log
entries. Performing regular backups of the transaction log keeps the transaction log file from growing on your
hard drive. But, remember that the backup file
.bak
) increases in size unless you clear this file each time you
perform a backup. Initializing the backup file clears it, allowing you to maintain a smaller backup file. The
WITH
INIT
command triggers initialization (Listing 5-2).
Shrinking Log Files
Occasionally, when you do a very large import, the transaction log file may temporarily need to expand. SQL
Server does this for you automatically, but it does not automatically shrink the file back to its original sizes
afterwards. You manually shrink the file by first backing up the transaction log to clear out its contents, and then
using the shrink file option to reduce its size. The code in Listing 5-3 does just that.
Listing 5-3.
Shrinking the Log File
USE [DWWeatherTracker]
GO
BACKUP LOG [DWWeatherTracker] TO DISK=N'C:\_BISolutions\DWWeatherTracker.bak' WITH INIT
GO
DBCC SHRINKFILE (N'DWWeatherTracker_log' , 0, TRUNCATEONLY)
GO
■
In previous versions of SQL, Microsoft included a
TRUNCATE_ONLY
option for clearing the log file entries
without having to back them up. But, this caused problems that could introduce corruption into your database if
not handled properly. Therefore, since SQL 2008, Microsoft removed the ability to clear log file entries with the
Note
