How to schedule a backup of the SQL Server Database.

In this two-part lesson, we will configure SQL Server to run scheduled backups of our databases.

Please follow the instructions to set up SQL Server for backups and scheduling.
How to perform a Backup of SQL Server Transaction Logs and SQL Server Databases.«

After you've completed the steps from the above link, perform the following.

┌[Step 2]
├──After you have started the [SQL Server Agent].
│      ├─Click to expand.
│      ├─Right-click on [jobs] and choose [New Job].
│      ├─When the window opens.
│      ├─Give it a [Name] = SQL Server Database Backup
│      ├─Give a [Description] = My SQL Server Database Backup
│      ├─Click on [Steps] from the [Left-panel]
│      ├─At the bottom of the page, click [New].
│      ├─When the Step window opens, give it a [Name] = Start Database Backup
│      ├─From Type: choose [Transact-SQL script (T-SQL)]
└──┴─Then choose [Paste]

In the script below, change the [ServerName] to the name of the Network Server you want to back up, too

Important

Updated: June 17, 2026. A single Quote was missing from the end of the mkdir string.

select @cmdFS = 'mkDir \\ServerName\SQLServer_Backup\Database\' + FORMAT(getdate(),'yyyy') + '\' + FORMAT(CAST(getdate() AS DATE), 'MM') + '\' + convert(varchar, getdate(), 110) + '&apos;

Copy

Search Site

Search Google

As you can see, there are two single quotes after the plus; there was only 1. It has now been updated.

[SQL Script for Database Backup]

CFFCS | CarrzSynEdit: | SQL Script


-- WORKING CODE

--Script 2: Backup all non-system databases


declare @cmdFS varchar(100)
select @cmdFS = 'mkDir \\ServerName\SQLServer_Backup\Database\' + FORMAT(getdate(),'yyyy') + '\' + FORMAT(CAST(getdate() AS DATE), 'MM') + '\' + convert(varchar, getdate(), 110) + '

EXEC master.dbo.xp_cmdshell @cmdFS
--EXEC master.dbo.xp_cmdshell @cmdC

--EXEC master.dbo.xp_cmdshell @cmdL


--1. Variable declaration


DECLARE @FSpath VARCHAR(500)
DECLARE @name VARCHAR(500)
DECLARE @filename VARCHAR(256)
DECLARE @time DATETIME
DECLARE @year VARCHAR(4)
DECLARE @month VARCHAR(2)
DECLARE @day VARCHAR(2)
DECLARE @hour VARCHAR(2)
DECLARE @minute VARCHAR(2)
DECLARE @second VARCHAR(2)
 
-- 2. Setting the backup path

SET @FSpath = '\\ServerName\SQLServer_Backup\Database\' + FORMAT(getdate(),'yyyy') + '\' + FORMAT(CAST(getdate() AS DATE), 'MM') + '\' + convert(varchar, getdate(), 110)+'\'


 -- 3. Getting the time values


SELECT @time = GETDATE()
SELECT @year   = (SELECT CONVERT(VARCHAR(4), DATEPART(yy, @time)))
SELECT @month  = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mm,@time),'00')))

SELECT @day    = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(dd,@time),'00')))

SELECT @hour   = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(hh,@time),'00')))

SELECT @minute = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mi,@time),'00')))

SELECT @second = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(ss,@time),'00')))


-- 4. Defining cursor operations

--------------------------1

DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- system databases are excluded


--5. Initializing cursor operations


OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN

-- 6. Defining the filename format

   
      -- SET @fileName = @path + @name + '_' + @month +'-'+ @day +'-'+ @year + '.BAK'  


       SET @fileName = @FSpath + @name + '.BAK' 

       BACKUP DATABASE @name TO DISK = @fileName  WITH INIT

 
       FETCH NEXT FROM db_cursor INTO @name   
END   
CLOSE db_cursor   
DEALLOCATE db_cursor

Explanation of the script.

The only string above that separates [Database backup] from [Transaction Backup] is the 4th line-up.
[Transaction Log]
BACKUP LOG @name TO DISK = @fileName
[Database]
BACKUP DATABASE @name TO DISK = @fileName
FORMAT(getdate(),'yyyy') = Year (2023)
This will create a year folder for your files. Since it is create that will be the name of theMAT(CAST(getdate() AS DATE), 'MM') = li]
The name will create a Month folder (02) where all the folders below will be stored.
convert(varchar, getdate(), 110) = Month-Day-Year (02-10-2023)
Within the Year (2023) and month (02) folder, the script will create a folder for each day a backup is created. In this case, on 02-10-2023.
Conclusion of Folder-structure.
\\ServerName\SQLServer_Backup\Database\2023\02\02-10-2023\12-00

Step 3

┌[Scheduling]
├──From the left panel, click on [Schedules]
│      ├─Choose [New]
│      ├─Give it a [Name]: Database Backup
│      ├─[Schedule Type]: Recurring [x][Enabled]
│      ├─[Frequency]
│      ├─[Occurs]: Daily
│      ├─[Recurs every]: 1 day(s)
│      ├─[Daily Frequency]
│      ├─[Occurs every]: 1 days(s) (Change this to suit your server requirements)
│      ├─[Duration]
│      ├─[Start date]: 2/10/2023 - [x][No end date]:
│      ├─Read the [Description] to ensure this is set up for your server requirements.
│      ├─Type in a [password].
└──┴─Click [OK].
To test, click to expand [Jobs]
Right-click on the job name, and choose [Start Job at step...]

For errors, you may get.

Right-click on the new Job.
choose [View History]
Click on the top item to expand.
Under Message: