NetBackup for SQL Server Agent

The NetBackup for SQL Agent automatically uses the accurate licensing method when capacity licensing is used. After a backup is complete using the MS-SQL-Server policy type, the agent captures the size of data that the policy protects. The data is reported to the master server.

Licensing for SQL is specific to the MS-SQL-Server policy type. NetBackup collects the data for any SQL backup that can be restored, not including transaction logs. The backup selection is defined in the SQL Instances and Databases tab in the policy.

The following SQL queries are used to gather file size information.

Get size of entire database

Given the database name, this query gets the file size in MB, not including the transaction log:

USE <dbname>;
SELECT CAST(SUM(dbfile.size) AS FLOAT) / 128.0 AS FileSizeInMB
FROM sys.database_files AS dbfile
WHERE dbfile.drop_lsn IS NULL
AND dbfile.type <> 1;

Get size of entire database for skip read-only file groups option

Given the database name, this query gets the file size in MB for skip ReadOnly file groups option:

USE <database_name>;
SELECT
sysFG.name AS FileGroupName,
SUM(CAST(dbfile.size AS float) / CAST(128 AS float)) AS FileSizeInMB
FROM
sys.database_files AS dbfile
INNER JOIN
sys.filegroups AS sysFG 
ON 
dbfile.data_space_id = sysFG.data_space_id 
WHERE 
sysFG.is_read_only = 0 and drop_lsn is null 
GROUP BY 
sysFG.name;

Get the size of file groups for a partial backup

Given the database name, this query gets the file size in MB for file groups that are specified in the partial list:

USE <database_name>; 
SELECT
sysFG.name AS FileGroupName,
SUM(CAST(dbfile.size AS float) / 128.0) AS FileSizeInMB
FROM 
sys.database_files AS dbfile 
INNER JOIN 
sys.filegroups AS sysFG 
ON 
dbfile.data_space_id = sysFG.data_space_id 
WHERE 
drop_lsn is null 
and sysFG.name in (<delimited fg name>, ...)
GROUP BY 
sysFG.name;

Get size of file and corresponding file group name

Given the file name (object name) and database name, this query gets the file size in MB and the corresponding file group name:

USE <database name>;
SELECT 
sysFG.name AS FileGroupName,
(CAST(dbfile.size AS float) / 128.0) AS FileSizeInMB
FROM
sys.database_files AS dbfile
INNER JOIN
sys.filegroups AS sysFG
ON
dbfile.data_space_id = sysFG.data_space_id
WHERE
dbfile.name = N'<file name>' and drop_lsn is null

Get size of file group

Given the file name (object name) and database name, this query gets the file group size in MB:

USE <database name>;
SELECT
SUM(CAST(dbfile.size AS float) / 128.0) AS FileSizeInMB
FROM
sys.database_files AS dbfile
INNER JOIN
sys.filegroups AS sysFG
ON
dbfile.data_space_id = sysFG.data_space_id
WHERE
sysFG.name = N'<filegroup name>' and drop_lsn is null

NetBackup for SQL Server Agent

Feedback

Feedback