Using Hangfire, connection string given in Startup.cs throws Cannot attach file as database error

Question

I'm utilizing Hangfire in my ASP .Net MVC Web App, it had installed successfully. I'd like to use the same LocalDb to store queued jobs for Hangfire to dequeue and process as I've used to stored data. However I'm running into the below error when I provided its connectionString or name defined in Web.config in Startp.cs. I've had no trouble adding, deleting updating data in the same localDb before hangfire.

Cannot attach the file 'c:\users\jerry_dev\documents\visual studio 2013\Projects\Hangfire.Highlighter\Hangfire.Highlighter\App_Data\aspnet-Hangfire.Highlighter-20150113085546.mdf' as database 'aspnet-Hangfire.Highlighter-20150113085546'.

Startup.cs:

public void Configuration(IAppBuilder app)
        {
            ConfigureAuth(app);
            app.UseHangfire(config =>
            {
                string hangfireConnectionString = @"Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\aspnet-Hangfire.Highlighter-20150113085546.mdf;Initial Catalog=aspnet-Hangfire.Highlighter-20150113085546;Integrated Security=True";
                config.UseSqlServerStorage(hangfireConnectionString);
                config.UseServer();
            });
        }

My project Solution is named "Hangfire.Highlighter"

Web.config:

<connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\aspnet-Hangfire.Highlighter-20150113085546.mdf;Initial Catalog=aspnet-Hangfire.Highlighter-20150113085546;Integrated Security=True" providerName="System.Data.SqlClient" />
</connectionStrings>

Clinkstone

check your App_Data folder and make sure that the 'aspnet-Hangfire.Highlighter-20150113085546' db exists. – Lumen

That's weird, there's no db in /App_Data – Clinkstone

Add it, and you should be fine. – Lumen

Actually, after I enabled "Show All Files" I see that there's a "HighlighterDb.mdf" – Clinkstone

Change the connection string to point to that then! – Lumen

The database 'HighlighterDb' cannot be opened because it is version 782. This server supports version 706 and earlier. A downgrade path is not supported. Could not open new database 'HighlighterDb'. CREATE DATABASE is aborted. Cannot attach the file 'c:\users\jerry_dev\documents\visual studio 2013\Projects\Hangfire.Highlighter\Hangfire.Highlighter\App_Data\HighlighterDb.mdf' as database 'HighlighterDb'. – Clinkstone

Answer 1

I know this is old - but its been 9 months and I pulled my hair out over this as well - and decided to do a write-up on it here.

My solution was to just create a quick and dirty DbContext, point it to the proper connection string, and call Database.CreateIfNotExists in the constructor:

public class HangfireContext : DbContext
{
    public HangfireContext() : base("name=HangfireContext")
    {
        Database.SetInitializer<HangfireContext>(null);
        Database.CreateIfNotExists();
    }
}

In the HangfireBootstrapper.Start() method I do something like this:

public void Start()
{
    lock (_lockObject)
    {
        if (_started) return;
        _started = true;

        HostingEnvironment.RegisterObject(this);

        //This will create the DB if it doesn't exist
        var db = new HangfireContext();

        GlobalConfiguration.Configuration.UseSqlServerStorage("HangfireContext");

       // See the next section on why we set the ServerName
        var options = new BackgroundJobServerOptions()
        {
            ServerName = ConfigurationManager.AppSettings["HangfireServerName"]
        };

        _backgroundJobServer = new BackgroundJobServer(options);

        var jobStarter = DependencyResolver.Current.GetService<JobBootstrapper>();

        //See the Recurring Jobs + SimpleInjector section
        jobStarter.Bootstrap();

    }
}

Not sure why Hangfire has such a hard time with LocalDb - maybe it can only handle full-blown SQL instances? Either way this works for me, new team members, and new dev/staging/prod instances that get stood up.

Truda

A simple and clean solution for creating the database. Thanks. – Crawley

This works for me, Thanks! However, Hangfire should create the db file. – Arrowwood

Almost worked for me. If anyone else is struggling with this solution, see my answer: https://mcmap.net/q/900628/-using-hangfire-connection-string-given-in-startup-cs-throws-cannot-attach-file-as-database-error – Memorialize

Answer 2

I too know this is old, but ran into this recently. Here is my fix:

1. In Visual Studio, go to 'View -> SQL Server Object Explorer'
2. Connect to the Data Source if it isn't already connected. In the example above it was '(LocalDb)\v11.0'
3. Right click on 'Databases' -> 'Add New Database'
4. Fill Database name = Ex: 'aspnet-Hangfire.Highlighter-20150113085546' or whatever you've named the database in the connection string.
5. Fill Database location = This should be the Data Directory in your application, 'App_Data' for the MVC project.

This fixed the issue in my case.

Nainsook

Answer 3

Jack's answer didn't work for me, because I ran into this problem: No connection string named could be found in the application config file

I got it to work with the following modifications:

1. Remove "name=" from the string in the base initializer. Thanks to: https://mcmap.net/q/627528/-no-connection-string-named-could-be-found-in-the-application-config-file
2. This moves the error to the call of UseSqlServerStorage. So instead of passing "HangfireContext" to it, I just copy the connection string from the dummy database context.

Complete setup code:

public class HangfireContext : DbContext
{
    public HangfireContext() : base("HangfireContext")  // Remove "name="
    {
        Database.SetInitializer<HangfireContext>(null);
        Database.CreateIfNotExists();
    }
}

public partial class Startup
{
    public static void ConfigureHangfire(IAppBuilder app)
    {
        var db = new HangfireContext();

        GlobalConfiguration.Configuration.UseSqlServerStorage(db.Database.Connection.ConnectionString);  // Copy connection string

        app.UseHangfireDashboard();
        app.UseHangfireServer();
    }
}

Memorialize

Answer 4

Is the DB already created? Can you try using a different conneciton string format?something like this, "Server=.;Database=HangFire.Highlighter;Trusted_Connection=True;"

Dawn

I don't see any DB under App_Data/. How do I create the DB if it's not there? – Clinkstone

This should have been posted as a comment on the question, and not as an answer… – Confer

Answer 5

Answer as per AspNetCore 3.1 and Hangfire 1.7.17

Hangfire should create all the tables provided there is an existing database with the specified database name. If you want to use LocalDb, you can use the following registrations (see below).

services
    .AddHangfire(
        (serviceProvider, config) =>
        {
            //read settings or hardcode connection string, but this is cleaner
            var configuration = serviceProvider.GetService<IConfiguration>();
            var connectionString = configuration.GetValue<string>("Hangfire:ConnectionString");
            var sqlServerStorageOptions =
               new SqlServerStorageOptions
               {
                    CommandBatchMaxTimeout = TimeSpan.FromMinutes(5),
                    SlidingInvisibilityTimeout = TimeSpan.FromMinutes(5),
                    QueuePollInterval = TimeSpan.Zero,
                    UseRecommendedIsolationLevel = true,
                    DisableGlobalLocks = true
               };
            config
                .SetDataCompatibilityLevel(CompatibilityLevel.Version_170)
                .UseSimpleAssemblyNameTypeSerializer()
                .UseRecommendedSerializerSettings();
                .UseSqlServerStorage(connectionString, sqlServerStorageOptions);
        });

The connection string is read, in my example, from the appsettings, so it'd look like this

"Hangfire": {
  "ConnectionString": "Data Source=(localdb)\\MsSqlLocalDb; Database=Hangfire;"
}

Again, notice how the connection string has the name of the database (e.g: Hangfire) that MUST exist in localdb. If you remove the Database=xxx parameter altogether, it'll pick the master database by default and create all the tables there.

Want