Sql-server – Login failed errors while DB refresh in progress

error logloginssql server

We have a 2.5TB refresh in Production, I have automated the refresh of latest production backup to lower environment every Sunday night. And during this activity, SQL error log is filled with

Login failed for user 'xxxx'. Reason: Failed to open the explicitly specified database 'xxxx'.

The options to stop these I could think of is:

  1. Ask app team to stop connecting to DB during refresh – they have lot of process, so they are not ready.
  2. Recycle error log post refresh – I need to search the entire log file for any messages during refresh (non login failed errors)
  3. Disable the login – that creates alerts too.

What is the best approach? Any thoughts? Or am I missing something stupid?

Unfortunately, due to other apps that connect to this instance, I can't:

  • disable logging of failed logins entirely, or
  • block all connections (via the firewall, etc)

Best Answer

One option might be to change the login for this user to have a different default database:

ALTER LOGIN [xxxx]
WITH DEFAULT_DATABASE = [tempdb];

This way at least the connections will succeed and the logging of errors will stop (the apps will likely have mixed results, mostly failures, with whatever they are trying to do of course).

And then you can change it back to the usual one after the refresh is done.

Related Question