Lots of our clients use Azure SQL Server, but not many realise that much of the configuation of a database can be changed using standard T-SQL commands using C#, SSMS, etc. rather than needing to go into the Portal. This also means they can be easily automated.

Changing Performance

For example, it is straight forward to dial the performance of a database up or down using a single command. We often use this to turn databases down, to save money, during evenings and weekends.


See full docs - ALTER DATABASE docs.

Copying a database

Another version useful feature is the ability to make a copy of a database and mount it under a new name. This is ideal for creating testing or development databases from a copy of a production database.

Below is an example C# script that I use for refreshing a mydb-dev database from the production mydb database. The dev database uses a cheaper / lower powerful instance S0. You may need to tweak the CommandTimeout if the database is particularly large.

using (var db = new SqlConnection("Server=tcp:mydb.windows.net"))
    var cmd = new SqlCommand { Connection = db, CommandTimeout = 180 };

    Console.WriteLine("Making a new copy of the database");
    cmd.CommandText = "CREATE DATABASE [mydb-dev-copy] AS COPY OF [mydb] ( SERVICE_OBJECTIVE = 'S0' )";

    // Wait whilst Azure makes the copy, checking the progress
    cmd.CommandText = "SELECT state FROM sys.databases where name = 'mydb-dev-copy'";
    while (true)
        var state = (byte) cmd.ExecuteScalar();
        if (state == 0) // Online
        if (state == 7) // Copying

        throw new Exception("Database copy failed: " + state.ToString());

    Console.WriteLine("Drop the dev database");
    cmd.CommandText = "DROP DATABASE [mydb-dev]";

    Console.WriteLine("Rename copy to dev database");
    cmd.CommandText = "ALTER DATABASE [mydb-dev-copy] MODIFY NAME = [mydb-dev]";