A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Monday, January 27, 2014

Biml - Backup Database Task

Biml - Backup Database Task

You can create an SSIS package that executes the Backup Database Task with Biml using only a few lines. As a helpful reminder, all of the database maintenance tasks are going to require an ADO.NET connection manager.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <AdoNetConnection Name="CM_ADO_DB" ConnectionString="Data Source=localhost\dev2012;Integrated Security=SSPI;Connect Timeout=30;Database=msdb;" Provider="SQL"  />
    </Connections>
    <Packages>
        <Package ConstraintMode="Linear" Name="Task_BackUpDatabase">
            <Tasks>
                <BackupDatabase 
                    BackupAction="Full" 
                    ConnectionName="CM_ADO_DB" 
                    DatabaseSelectionMode="User" 
                    Name="BKP User Databases">
                    <AutomaticBackupDestination 
                        BackupFileExtension="bak" 
                        CreateSubfolder="true" 
                        Folder="C:\backup" 
                    />
                </BackupDatabase>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Results

Right click on that script, after adjusting the connection string, and you should end up with a package like this.

1 comment:

robert.rubocki said...

This was helpful, thanks! It may be worth mentioning the Backup Database task requires an ADO connection.