How I Automated SQL Server Backups to a Network Share and Deleted Old Backups Automatically

Managing SQL Server backups used to be a headache for me—especially when dealing with a network share. I needed a way to automate daily backups while ensuring old backups didn’t pile up and eat up storage space. Manually deleting old files was not an option, so I set out to create a fully automated solution using SQL Server Agent and PowerShell.

In this blog post, I’ll share how I successfully set up daily SQL Server backups to a network share and automatically deleted backups older than 7 days. Whether you’re a database administrator or a developer looking to streamline your backup process, this guide will help you do it efficiently. Let’s dive in!

Running the T-SQL Script below will automatically backup your databases and delete backups older than 7 days;

Just make sure to change the Network Shared folder directory, username and password, and the path according to your network settings.

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @Cmd NVARCHAR(500)

-- Enable xp_cmdshell (if not already enabled)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

-- Mapped the shared folder 
EXEC xp_cmdshell 'net use s: \\123.123.123.2\SharedFolder /user:whatever\username password'

-- specify database backup directory
SET @path = 'S:\Backup\' 
 
 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

-- Backup databases except system databases
DECLARE db_cursor CURSOR READ_ONLY FOR  
SELECT name 
FROM master.sys.databases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
   SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
   BACKUP DATABASE @name TO DISK = @fileName  
 
   FETCH NEXT FROM db_cursor INTO @name   
END   

 
CLOSE db_cursor   
DEALLOCATE db_cursor

-- Delete backups older than 7 days
SET @Cmd = 'powershell.exe -Command "Get-ChildItem -Path ''' + @path + '*.bak''' + 
           ' | Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-7)} | Remove-Item -Force"'
EXEC xp_cmdshell @Cmd

If you want to run this daily, just create a new SQL Server agent Job.