HostPapa Device Backup: How to do SQL Backups using the Continuous Client

In this article, we will show you how to complete a SQL backup from SQL Management Studio using the SQL Agent. Follow these steps:

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

    HostPapa Device Backup: How to Do SQL Backups Using the Continuous Client

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

    SQL backup with HostPapa Device Backup

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

    How to Do SQL Backups Using the Continuous Client

  4. In the Steps section, click New…

    How to Do SQL Backups Using the Continuous Client 1

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

    How to Do SQL Backups Using the Continuous Client 2

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

–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

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

  1. In the window, click New.

    How to Do SQL Backups Using the Continuous Client 4

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

    How to Do SQL Backups Using the Continuous Client 6

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

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

If you need help with your HostPapa Device Backup, please open a support ticket from your dashboard.

Related Articles

Get online with our affordable web hosting

Get online with our affordable web hosting

Learn more now
HostPapa Mustache