Schedule to Backup SQL Server Database

How to schedule a backup of SQL Server Database.
In this two-part lesson, we will configure SQL Server to perform a scheduled backup 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.

1. 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]
   On 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 backup, too


-- 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

Explaintion of the script.

1. 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
   
2. FORMAT(getdate(),'yyyy') = Year (2023)
This will place a year folder for your files to be placed in. Since it is 2023, that will be the name of the folder.
3. FORMAT(CAST(getdate() AS DATE), 'MM') = Month (02)
This will create a Month folder (02) where all the below folders will be stored.
4. 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.
5. Conclusion of Folder-structure.
\\ServerName\SQLServer_Backup\Database\2023\02\02-10-2023\12-00

Step 3

1. Scheduling
From the Left-panel click on [Schdules]
Choose [New]
Give it a [Name]: Database Backup
[Schdule 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.
Click [OK].

2. To test, click to expand [Jobs]
   Right-click on the job name, and choose [Start job at step...]

For errors, you may get.

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