Backup ALL user DBs with 1 script

Set Nocount on
Declare @dbname varchar(100) 
Declare @servername varchar(100) 
declare @d varchar(30)

Declare db Cursor For
        Select name from master.dbo.sysdatabases
        where dbid > 4 

Declare @osql varchar(1000)

select @servername = @@servername
select @d = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30), GETDATE(), 120), '-', ''), ':', ''), ' ', '_')

Open db
Fetch Next from db into @dbname
While @@Fetch_status=0
        Set @osql='EXEC master.dbo.xp_cmdshell '+''''+'sqlcmd -E -S '+@servername+' -Q"BACKUP DATABASE ['+@dbname+'] TO disk = ''''\\filer\SQL_BACKUPS_SHARE\myserver\UserDBs\'+@dbname+'_' + @d + '.bak'''' " -o"C:\SQLLogs\Agent Jobs\Backup User Databases - '+@dbname+'.log"'+''''
        EXEC (@osql) --Execute the osql statement
        Fetch Next from db into @dbname    
Close db

Deallocate db

The above script backs up all user databases and appends the backup name with the date and time. xp_cmdshell needs to be enabled for this script to work.

To purge old backups within a SQL job Step, create the following code as Type: ActiveX script with Language VBScript:

This script will delete files older than 3 days.

Option Explicit
on error resume next
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim iDaysOld

'Customize values here to fit your needs
iDaysOld = 3
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = "\\filer\SQL_BACKUPS_SHARE\MyServer\UserDBs"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files

'Walk through each file in this folder collection. 
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
End If

'Clean up
Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: