This article will help you complete a SQL backup from SQL Management Studio using the SQL Agent.


1. Go to SQL Management Studio on your computer and connect to your SQL environment as a SA user. 


2. Click + to expand SQL Server Agent, right-click Jobs, then click New Job.



3. In the General tab, give your job a name.


4.In the Steps section, click New...


5.In the New Job Step window, give the job a Step Name. In the command window, post the script below, then click OK.




=================================================================================

--Script 3: Backup all



--1. Variable declaration


DECLARE @path VARCHAR(500)

DECLARE @name VARCHAR(500)

DECLARE @filename VARCHAR(256)


-- 2. Setting the backup path


SET @path = 'C:\Backup\'   -- this can be any path you would like but make sure the folder exists in the location you pick


-- 4. Defining cursor operations



DECLARE db_cursor CURSOR READ_ONLY FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN ('master','model','msdb','tempdb')


--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 + '.BAK'


BACKUP DATABASE @name TO DISK = @fileName WITH INIT


FETCH NEXT FROM db_cursor INTO @name


END


CLOSE db_cursor


DEALLOCATE db_cursor

================================================================================


6. In the window, click New..


7. In the New Job Schedule window, give your schedule a name and input the details you’d like for the backup schedule. Click OK.


8. Set up alerts and notifications as required, then click OK.


9. Once this is complete, follow the steps on the page Folder Selection to make sure you add the SQL Backup Folder to your backup.


For more information about doing a full database backup in SQL Server, check out this article.


Please contact support if you require any assistance with the configuration of SQL backups.