script out the red-gate backup and restore process

marrow · March 17, 2010 7:17AM

Dear expert,

Any way to script out/automate the whole process of red-gate backup and restore using by only one mouse click? SQL server management studio can script that out but I can't see I can do it from red-gate.

Any idea?

This is assuming that all red-gate backup files always in the fixed folder, this can let red-gate restore read the file from the same place.

Eddie D · March 18, 2010 9:55AM

Thank you for your post into the forum.

There is no single click solution to what you are seeking.

You can script out a backup and restore tasks using the Backup Wizard, Restore Wizard and Schedule Backup Job Wizard. When you reach the final step, before clicking the finish button, there is a summary of the actions to be taken. Behind the summary tab, you will find the 'Script' tab. If you select the script tab, you will find the job synatx for both the Extended Stored Procedure and Command Line Interface.

Alternatively, modify the example scripts below to suit your needs (The examples are for the Stored Procedure):

-- Backup AdventureWorks using Compression Level 2, encrypted and erasefiles set to 30 days
DECLARE @errorcode INT
DECLARE @sqlerrorcode INT
EXEC master.dbo.sqlbackup '-SQL "BACKUP DATABASE &#91;AdventureWorks&#93; TO 
DISK = ''D:\Backup\&lt;AUTO&gt;.sqb''
WITH ERASEFILES = 30, COMPRESSION = 2, THREADPRIORITY = 6, KEYSIZE = 256, PASSWORD = ''p@ssw0rd'', VERIFY " '
IF &#40;@errorcode &gt;= 500&#41; OR &#40;@sqlerrorcode &lt;&gt; 0&#41;
BEGIN
RAISERROR &#40;'SQL Backup failed with exit code: %d  SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode&#41;
END

--RESTORE DATABASE Script
DECLARE @errorcode INT
DECLARE @sqlerrorcode INT
EXECUTE master..sqlbackup N'-SQL "RESTORE DATABASE &#91;&lt;database_name&#93;  FROM DISK = ''Path to the backup file.sqb'' WITH RECOVERY, REPLACE"'
IF &#40;@errorcode &gt;= 500&#41; OR &#40;@sqlerrorcode &lt;&gt; 0&#41;
BEGIN
RAISERROR &#40;'SQL Backup failed with exit code: %d  SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode&#41;
END

To automate a backup task, simply use the Schedule Backup Job which will create the job for you. Alternatively create a new SQL Server Agent Job and modify the example syntax to meet your needs.

If you are looking to automate SQL Server Express, you will need to use the Command Line Interface(CLI) and create batch files containing the CLI syntax. Then use the Windows scheduler to call at the batch file.

SQL Backup does not have a schedule restore job wizard or similar. To automate restore task you will need to create new SQL Server Agent Job and modify the example restore syntax to meet your needs.

Many Thanks
Eddie

marrow · March 24, 2010 12:08AM

So I can simple just script that out and put it in the job, right? I am just thinking I have to run the sqlbackup command from DOS prompt.

Eddie D · March 29, 2010 1:29PM

Thank you for your reply.

To answer your question is yes. You can simply script out the commands and place into a SQL Agent job.

However, if your SQL Server Instance is Microsoft SQL Server Express Edition then you will need to use the SQL Backup Command Line Interface from the command prompt. Or create batch files and use the Windows Scheduler to create scheduled tasks. The reason is that Microsoft have removed the SQL Server Agent from this edition of SQL Server.

An example SQL Backup Command Line batch file maybe as follows:

@echo off
"C:\Program Files\Red Gate\SQL Backup 6\SQLEXP\SQLBackupC.exe" -E -I SQLEXP -SQL "BACKUP DATABASE &#91;AdventureWorks&#93; TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Backup\&lt;AUTO&gt;.sqb' WITH DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, THREADCOUNT = 2"

Many Thanks
Eddie

marrow · April 20, 2010 4:08AM

thanks for this.

basicallly I found the SQL backup, before clicking finish, has a "script" column that contain all the script out the whole thing.

I do it in this way and then let the SQL server agent start that.

Eddie D · April 20, 2010 5:09AM

Thank you for your reply.

As you have found, using the script tab on the last step of the Backup, Restore and Scheduled Backup Job wizards is another way of obtaining the scripts required.

Please note, when you use the Scheduled Backup Job wizard, a job is created in the SQL Server Agent. SQL Backup uses the SQL Agent as the trigger to call the SQL Backup Extended Stored Procedure.

However if you need to schedule a restore job, you still need to script it and then create a SQL Agent job.

Many Thanks
Eddie

script out the red-gate backup and restore process

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk