How to Do SQL Backups Using the Continuous Client
This article will help you complete a SQL backup from SQL Management Studio using the SQL Agent.
- Go to SQL Management Studio on your computer and connect to your SQL environment as a SA user.
- Click + to expand SQL Server Agent, right-click Jobs, then click New Job.
- In the General tab, give your job a name.
- In the Steps section, click New…
- 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.