home
training courses
why choose us?
solutions
support
company
LearnItFirst User Forum
Welcome Guest
Search
|
New Posts
|
Members
|
Log In
|
Register
SQL Server Forum - LearnItFirst.com
»
SQL Server Scripts, Code Samples and SSMS Custom Reports
»
All SQL Server Versions
»
View backup history, size and predict size of database for future
View backup history, size and predict size of database for future
Options
Previous Topic
·
Next Topic
Scott Whigham
Posted:
Tuesday, August 29, 2006 9:07:13 AM
Rank: Super Mod
Joined: 3/20/2006
Posts: 460
Points: 1,002
Where do you live?: Dallas, TX
Code:
CREATE FUNCTION dbo.GetBackupHistory(@DbName SYSNAME)
RETURNS TABLE
AS
/*
Author: Scott Whigham from
http://www.LearnSqlServer.com/
Description: This script returns the list of all backups taken of @DbName including the sizes (MB and GB)
and duration (in seconds and minutes).
Misc Notes: One way, in the absence of other information, to predict future sizing of a database is to look at
how the database backups have grown. If you'll take the full backups and look at the growth percentage
over several months, that can give you a relatively accurate portrayal of your percent growth for future months.
Versions: SQL Server 2005, 2000, 7.0
Creation Date: August 29, 2006
For more scripts like this one, visit
http://forums.learnsqlserver.com/codesamples.aspx
*/
RETURN (
SELECT bs.database_name AS DbName
, bs.type AS BackupType -- 'D' is a FULL backup, 'L' is log, and 'I' is DIFFERENTIAL (of course!)
, CAST ( bs.backup_size / 1024.0 / 1024 AS DECIMAL(10, 2) ) AS BackupSize_MB
, CAST ( bs.backup_size / 1024.0 / 1024 / 1024 AS DECIMAL(10, 2) ) AS BackupSize_GB
, bs.backup_start_date AS Backup_StartDate
, bs.backup_finish_date AS Backup_EndDate
, DATEDIFF ( ss, bs.backup_start_date, bs.backup_finish_date ) AS TookHowLong_Seconds
-- We don't use DATEDIFF(minute...) here because of how integer-only math works
, CAST ( DATEDIFF ( ss, bs.backup_start_date, bs.backup_finish_date ) / 60.0 AS DECIMAL(10,2) ) AS TookHowLong_Minutes
, bs.name AS BackupName
, bs.description AS BackupDescription
, bs.[user_name] AS UserNameTakingBackup
, CAST(bs.software_major_version AS VARCHAR) + '.' + CAST(bs.software_minor_version AS VARCHAR) + '.'
+ CAST(bs.software_build_version AS VARCHAR) AS SoftwareBuildVersion
, bs.server_name AS ServerName
, bs.machine_name AS MachineName
, bs.collation_name AS Collation
, bmf.physical_device_name AS DeviceName
FROM msdb.dbo.backupset bs JOIN msdb.dbo.backupmediafamily bmf
ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = @DbName
)
GO
SELECT * FROM GetBackupHistory('msdb') ORDER BY Backup_StartDate DESC
Back to top
Users browsing this topic
Guest
Forum Jump
SQL Server Database Administration
- General SQL Database Question & Answer
- Backup, Recovery and Disaster Recovery
- SQL Server Security
- Integration Services (SSIS) and DTS
Transact-SQL Programming
- DML (SELECT, INSERT, UPDATE, DELETE) Questions
- Stored Procedures, Triggers, & Functions
SQL Server Scripts, Code Samples and SSMS Custom Reports
- All SQL Server Versions
- SQL Server 2005/2008
- SQL Server Management Studio Custom Reports
Customer Service
- Video Requests
You
cannot
post new topics in this forum.
You
cannot
reply to topics in this forum.
You
cannot
delete your posts in this forum.
You
cannot
edit your posts in this forum.
You
cannot
create polls in this forum.
You
cannot
vote in polls in this forum.
SQL Server 2005 DBA Training Videos
SQL Server 2008 DBA Training Videos
Watch this topic
RSS Feed
Email this topic
Print this topic
Threaded
Normal