September 23, 2014 at 9:15 am
I think I did this long time ago but don't remember exactly how.
I am using Ola's solution for db backups by the way and its main command is something like this:
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBgrowth -Q "EXECUTE [dbo].[DatabaseBackup] @databases = 'MyDatabase1, MyDatabase2', @Directory = N'\\server\path', @BackupType = 'FULL', @BackupSoftware = 'SQLBACKUP', @Compress = 'Y', @CompressionLevel = 2, @NumberOfFiles = 5, @verify = 'N', @CleanupTime = 504, @checksum = 'N', @LogToTable = 'Y'" -b
where MyDatabase1 and MyDatabase2 are clients abover 100GB.
Now, I would like to make this dynamic, no user intervention. I already have a table where I'm keeping current and historical data of db sizes.
How can I make this job dynamic, so when a new database passes the 100GB threshold, their names will be part of @databases parameter? In other words, added automatically, based on my query.
September 24, 2014 at 7:48 am
bump!
anyone?
September 24, 2014 at 8:22 am
You'd have to write code that builds this string and passes it in. I'm not sure how you're calling this, but you'd need a wrapper that queries your databases, builds a string of those that are > 100GB, and then calls the maint proc with that string as a parameter.
September 24, 2014 at 8:42 am
Yes, or if you're feeling brave, you could change the DatabaseBackup stored procedure to include the logic in there.
John
September 24, 2014 at 9:39 am
Yeah, my bad, forgot to post the query that lists the names of databases bigger than 100GB.
But I think I got an idea of how to do it without altering Ola's main sproc, but passing @databases as parameter from the other query... working on it ...
October 2, 2014 at 9:24 am
Hope this help someone. This is how I approached and fixed this problem:
--Declare variable that will contain set with databases over 100GB
DECLARE @DBList VARCHAR(MAX)
--Set and loop to grab db values
SELECT @DBList = COALESCE(@DBList + ',', '') + DBName
FROM [dbo].[Over100GB]
ORDER BY DBName
--Actual Ola's sproc, but modified.
EXECUTE dbo.DatabaseBackup
@databases = @DBList, -- to replace by set containing over of 100GB clients.
@Directory = '\path\folder',
@BackupType = 'FULL',
@BackupSoftware = 'SQLBACKUP',
@Compress = 'Y',
@CompressionLevel = 2,
@NumberOfFiles = 5,
@verify = 'N',
@checksum = 'N',
@LogToTable = 'Y'
That is a regular T-SQL job that runs weekly.
The table called Over100GB contains the set with clients that already exceeded that threshold.
Ola's solution is fantastic, but does not include an option to segregate based on client's sizes. So above is a nice workaround if your FULL backups jobs started having some issues with any of those big clients.
October 2, 2014 at 9:30 am
Nice one. You could also query one of the system views, for example sys.master_files, to build your list of databases at runtime. That way, you know that the databases that are backed up, and only those databases, are over 100GB when the backup is made.
John
October 2, 2014 at 9:36 am
John Mitchell-245523 (10/2/2014)
Nice one. You could also query one of the system views, for example sys.master_files, to build your list of databases at runtime. That way, you know that the databases that are backed up, and only those databases, are over 100GB when the backup is made.John
That's done! 🙂
--This is the set for current values. Taken from system databases
SELECT
SD.Name
FROM sys.master_files MF
JOIN sys.databases SD
ON SD.database_id = MF.database_id
LEFT JOIN [dbo].[Over100GB] DBG
ON DBG.DBname=SD.name
WHERE type = 0 AND DBG.DBname IS NULL
GROUP BY SD.name
HAVING CONVERT(decimal(10,2), ( SUM(MF.size*8/1024) ) ) >= 90000.00 -- This is the filter for DBs over 100GB, which is 100000.00 MBs
ORDER BY Name
GO
I did not post because I though was offtopic.
Like you said, above query/job keeps the table up to date. So when the weekly backup job runs, it will run against anything that is above 100GB only.
Need to work or mimic same coalesce trick for the regular jobs, where instead of adding "," I need to concatenate and add "-", which is the minus on Ola's solution.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply