Backup database from CLR Function

Question

I'm trying to use simple CLR Function to backup sql database.

[SqlFunction(DataAccess = DataAccessKind.Read)]
public static int BackupDb()
{
    using (var conn = new SqlConnection("context connection=true"))
    {
        conn.Open();
        var cmd = new SqlCommand(
            @"backup database MyDatabase to disk='d:\temp\MyDatabase.bak' WITH INIT, STATS=10", conn);
        cmd.ExecuteNonQuery();
    }
    return 0;
}

But this code gives exception Msg 6522, Level 16, State 1, Line 1 A .NET Framework error occurred during execution of user-defined routine or aggregate "BackupDb": System.Data.SqlClient.SqlException: Invalid use of a side-effecting operator 'BACKUP DATABASE' within a function. System.Data.SqlClient.SqlException: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at UserDefinedFunctions.BackupDb()

You can't make changes to the database from within a function. It's as simple as that. And backing up a database does make changes to that database (notably in how it then deals with sections of the transaction log) — Damien_The_Unbeliever
Thank you, I created [SqlProcedure] instead of function and it works fine now. If you write this comment as answer I will accept it. — Milos

Damien_The_Unbeliever Damien_The_Unbeliever · Accepted Answer · 2013-10-23T11:51:49

You can't make changes to the database from within a function. It's as simple as that. And backing up a database does make changes to that database (notably in how it then deals with sections of the transaction log)

If you want something that a) can go inside SQL Server, b) lets you write c# code, and c) let's you make changes to the database, the sole object that fits that definition is a CLR Stored Procedure

Backup database from CLR Function

1 Answers