# Azure SQL Database serverless: retain the service tier when recreating a database

> Recently I started using Azure SQL Database without server For my integration tests. But before each of my integration tests I have to delete my database and then…

- Author: Jérôme Giacomini
- Language: en
- Canonical URL: [Azure SQL Database serverless: retain the service tier when recreating a database](https://jeromegiacomini.net/articles/2020/02/10/azure-sql-database-serverless-retain-the-service-tier-when-recreating-a-database)
- Published: 2020-02-10
- Last modified: 2026-09-05
- Topics: .NET, C#, Azure, SQL

Recently I started using [Azure SQL Database without server](https://jeromegiacomini.net/Blog/2020/01/20/azure-sql-database-serverless/) For my integration tests.
But before each of my integration tests I have to delete my database and then recreate it so that it is independent.

But if I use the SQL DROP DATABLE function I lose all my Azure performance/pricing configuration.

## The Solution

My solution to deleting is:

Delete foreign keys
then delete the tables one by one

To do so I retrieve the list of tables through the INFORMATION table\_here

```csharp

void EnsureDeleted()
{
   var tableNames = RawSqlQuery("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES;");

   while (tableNames.Count != 0)
   {
      List<string> toRemove = new List<string>();
      // Cette table est ajouté automatiquement par Azure Database et on ne peux pas la supprimer.

      tableNames.Remove("database_firewall_rules");
      foreach (var tableName in tableNames)
      {
         // génère un script qui permet de supprimer la FK.
         var scriptsToRemoveFk = RawSqlQuery($@"
                        SELECT
                        'ALTER TABLE ' + OBJECT_SCHEMA_NAME(k.parent_object_id) +
                        '.[' + OBJECT_NAME(k.parent_object_id) +
                        '] DROP CONSTRAINT ' + k.name
                        FROM sys.foreign_keys k
                        WHERE referenced_object_id = object_id('{tableName}')");

         foreach (var script in scriptsToRemoveFk)
         {
            _nuContext.Database.ExecuteSqlCommand(script);
         }

         #pragma warning disable EF1000 // Possible SQL injection vulnerability.
         _nuContext.Database.ExecuteSqlCommand("DROP TABLE [" + tableName + "]");
         #pragma warning restore EF1000 // Possible SQL injection vulnerability.
         toRemove.Add(tableName);
      }
      foreach (var tableName in toRemove)
      {
         tableNames.Remove(tableName);
      }
   }
}
```


**The method of retrieving a string list from’a SQL query.**

```csharp

        private List<string> RawSqlQuery(string query)
        {
            using (var command = _nuContext.Database.GetDbConnection().CreateCommand())
            {
                command.CommandText = query;
                command.CommandType = CommandType.Text;

                _nuContext.Database.OpenConnection();

                using (var result = command.ExecuteReader())
                {
                    var entities = new List<string>();
                    while (result.Read())
                    {
                        entities.Add(result.GetString(0));
                    }

                    return entities;
                }
            }
        }
```


Happy coding !