Skip to content

Instantly share code, notes, and snippets.

@bvanskiver
Last active July 24, 2017 22:48
Show Gist options
  • Save bvanskiver/bc262afed673b2a38b25487d19c065f4 to your computer and use it in GitHub Desktop.
Save bvanskiver/bc262afed673b2a38b25487d19c065f4 to your computer and use it in GitHub Desktop.
-- Define the variables
declare @databases table (rowid int identity primary key, dbname varchar(500))
declare @rowid int, @dbname varchar(500), @sql varchar(2000)
-- Get the database names from the system function
insert into @databases (dbname)
select db_name
from msdb.smart_admin.fn_backup_db_config (null)
where is_managed_backup_enabled = 0
and is_dropped = 0
-- Iterate through databases
select @rowid = min(rowid)
from @databases
while @rowid is not null
begin
set @dbname = (select dbname From @databases Where rowid = @rowid)
begin
set @sql = 'exec msdb.smart_admin.sp_set_db_backup @database_name= '''+'' + @dbname+ ''+''',
@retention_days=7,@credential_name=<credential_name>,@encryption_algorithm = NO_ENCRYPTION,@enable_backup=1;'
execute (@sql)
end
select @rowid = min(rowid)
from @databases
where rowid > @rowid
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment