Using Automated Build Studio to automate offsite backups

If you run your macro at this point you will be prompted with a message that “The executable file of the “SQLMaint” tool is not specified.   The SQLMaint executable is installed with your SQL server instance and can be found in the Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn folder.  Setup your tools to point to this file.  Note, MSSQL.1 will change depending on the you are running.

But, SQL Server is installed on another machine and that’s the way I like it!

Yup, me too.  The built in SQL Server backup operations depend on the SQLMaint executable to run backup process.  Again, this file is installed with SQL server and I could not get this file to run on machine that did not have SQL Server installed.

Instead what I did was created a batch file/SQL script combo on the machine running SQL server and scheduled a task to execute this batch file. The SQL script looks like this:

1: BACKUP DATABASE [sgmaster] TO DISK = N'C:\MSSQLData\backups\sgmaster.bak' WITH FORMAT, INIT, NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 2: GO 3: BACKUP DATABASE [sgvault] TO DISK = N'C:\MSSQLData\backups\sgvault.bak' WITH FORMAT, INIT, NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 4: GO

 

A batch file that executes this script is scheduled to run at a fixed interval and simply consists of this:

1: "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S localhost -i "C:\AdminScripts\SQL Server\backup.sql"

 

This batch file on execution creates two files in c:\MSSQLData\backups.  These files will be processed by the next step in the macro.  Note that because no username or password information is being specified here, the user set to run this task must have the appropriate rights in SQL server.  This batch file is executed by adding a windows scheduler task.

Whichever path you chose for creating your backup file.  Go ahead and run the macro item for an SQL backup, or from the SQL server execute your batch file to get a backup made of the database(s).Automated Build Studio Create Zip operation

Building the zip file

Page 2 of 4 | Previous page | Next page