SIEM, Log Management, Security, Compliance, Server Monitoring and Uptime Monitoring Software
Table of Contents

SQL Server Shrink and Backup Template

The SQL Server Shrink and Backup Template enables you to automate SQL Server shrink and backup database functions. This template is typically used by DBAs to shrink truncated databases and generate daily SQL Server backups.

How to configure the SQL Server Shrink and Backup Template:

  • From the Menu Bar, select File | New. The Create New Object View displays.
  • From the Create New Object View, expand Templates | Network and Application Monitors then select SQL Server Task. The New Template Properties View displays.
  • The Template Properties view contains 4 tabs.

The Options Tab

  • Use the Data Providers drop-down to select the data provider you would like to monitor.
Note
If you have not yet configured the database connection, create a new Data Provider with the role set to None. For more information see: Data Providers

How to a shrink SQL Server database:

  • Use the Enabled check box to enable the database shrink function.
  • Use the Type drop-down to select Database.
  • Use the Reorganize files before releasing unused space check box to reorganize files before releasing unused space then, optionally specify the percentage of available space to retain in the database for future tables and rows.
Shrink SQL Server Database Properties View
Shrink SQL Server Database Properties View

How to shrink a SQL Server database file:

  • Use the Enabled check box to enable the database shrink function.
  • Use the Type drop-down to select File.
  • Use the Data check box to shrink the data file (.mdf).
  • Use the Log check box to shrink the transaction log file (.ldf).
  • Use the Shrink action controls to:
OptionDescription
Release unused spaceCause any unused space in the files to be released to the operating system and shrink the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages.
Reorganize pages before releasing unused spaceEquivalent to executing DBCC SHRINKFILE specifying the target file size. When this option is selected, the user must specify a target file size in the Shrink file to box.
Shrink file toSpecifies the target file size for the shrink operation. The size cannot be less than the current allocated space or more than the total extents allocated to the file. Entering a value beyond the minimum or the maximum will revert to the min or the max once the focus is changed or when any of the buttons on the toolbar are clicked.
Empty file by migrating the data to other files in the same filegroupMigrate all data from the specified file. This option allows the file to be dropped using the ALTER DATABASE statement. This option is equivalent to executing DBCC SHRINKFILE with the EMPTYFILE option.
Shrink SQL Server Database File Properties View
Shrink SQL Server Database File Properties View

How to backup a SQL Server database:

  • Use the Enabled check box to enable the database backup function.
  • Use the Type drop-down to select either a Full or Differential backup.
Important
You must perform at least one full database backup before you can perform a differential or a transaction log backup.
  • Use the Copy-only backup checkbox to create a copy-only backup. A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. For more information, see Copy-Only Backups (SQL Server). A copy-only backup isn't available for the Differential backup type.
  • Use the Filename control to specify the filename to save the backup too.
    For more information see: Back Up Database (General Page)
  • Use the Overwrite media drop-down to select either Existing or New.
    For more information see: Back Up Database (Media Options Page)
  • Use the Backup set name text box to specify the backup set name. The system automatically suggests a default name based on the database name and the backup type.
  • Use the Backup set description text box to specify a description of the backup set.
  • Use the Backup set will expire text box to specify the number of days that must elapse before this backup set expires and can be overwritten. This value can be from 0 to 99999 days; a value of 0 days means that the backup set will never expire.
  • Use the Compression drop-down to select the compression options.
    For more information see: Back Up Database (Backup Options Page)
Backup SQL Server Database Properties View
Backup SQL Server Database Properties View

Related Topics

Shrink a database

Shrink a file

Network and Application Monitor Templates