Most failed SQL Server recoveries for web apps are not media failures. They are cutover mistakes: the database comes online, ASP.NET reconnects, and you discover orphaned users, a stale compatibility level, or a connection string still aimed at the broken copy. On shared Windows hosting and Windows VPS alike, a restore is an application event—not only a DBA task.

This checklist assumes current SQL Server (2025 GA in production environments; 2022 still common on many hosts). The engine commands are familiar; the hosting-floor discipline around IIS, connection strings, and login mapping is what keeps the site up after the RESTORE finishes.

#Backups that actually restore

Point-in-time recovery only works if full, differential, and log backups form a continuous chain. For typical ASP.NET OLTP databases, a practical baseline is a nightly full backup, differentials every few hours, and transaction log backups every 5–15 minutes when the recovery model is FULL. SIMPLE recovery is fine for disposable staging databases; it is a poor default for customer data you may need to roll back to 10:42 a.m.

  • Run RESTORE VERIFYONLY on a schedule against recent full backups—not only BACKUP DATABASE.
  • Store backup files off the data volume; a full disk that kills the database often kills local backups too.
  • Keep at least one full backup older than your last successful schema migration or large data load.
  • Document the logical file names (MOVE targets) once; restores to a side-by-side name fail when paths are guessed under pressure.

#Restore to a side database first

Prefer restoring beside production (for example, AppDb_Restore) before you touch the live name. That lets you check row counts, smoke-test EF Core queries, and fix logins without holding an exclusive lock on the name your app pool is hammering. When you must overwrite in place, coordinate a short maintenance window and stop write traffic first.

tsql 
-- Example: restore beside production, then remap a SQL login
RESTORE DATABASE [AppDb_Restore]
  FROM DISK = N'D:\Backups\AppDb_full.bak'
  WITH MOVE N'AppDb' TO N'D:\SQLData\AppDb_Restore.mdf',
       MOVE N'AppDb_log' TO N'D:\SQLData\AppDb_Restore_log.ldf',
       RECOVERY, REPLACE;

-- After restore, fix orphaned user (SQL auth)
ALTER USER [app_user] WITH LOGIN = [app_user];

-- Confirm
SELECT dp.name AS db_user, sp.name AS server_login
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.name = N'app_user';

Windows auth principals usually rematch by SID when the same domain account exists on the instance. SQL-authenticated users orphan whenever the database moves across instances or the server login was recreated with a new SID—common after a host migration or a “just recreate the login” shortcut. ALTER USER … WITH LOGIN is the modern fix; do not leave the app on dbo as a workaround.

#Cut over the ASP.NET app cleanly

Drain writes before the final rename or connection-string swap. On IIS, app_offline.htm in the site root is still the simplest hard stop for ASP.NET Framework and ASP.NET Core in-process apps: drop the file, wait for the app domain to unload, restore or rename, update config, remove the file. For longer work, stop the app pool instead of killing w3wp from Task Manager.

xml 
<!-- Web.config / appsettings transform target: point at restored DB -->
<connectionStrings>
  <add name="DefaultConnection"
       connectionString="Server=SQLHOST;Database=AppDb;User ID=app_user;Password=***;Encrypt=True;TrustServerCertificate=False;Connect Timeout=30;Application Name=MyWebApp;"
       providerName="Microsoft.Data.SqlClient" />
</connectionStrings>
  • Set Application Name in the connection string so sp_whoisactive and Query Store show your app, not a generic .NET pool.
  • Keep Connect Timeout modest (15–30s); a hung restore should fail the request, not pin every thread in the pool.
  • After cutover, recycle the app pool once so pooled connections drop the old database context.
  • If you use EF Core migrations, confirm __EFMigrationsHistory on the restored database matches the deployed assembly before traffic returns.

#Post-restore checks that catch silent breakage

A successful RESTORE is not a successful recovery. Validate compatibility level, recovery model, and auto-stats settings if you cloned from an older instance. On SQL Server 2022 and 2025, confirm Query Store is on for production web databases so the first slow hour after restore is diagnosable. Run a short read/write smoke test under the same SQL login the app uses—not as sysadmin.

  • SELECT name, compatibility_level, recovery_model_desc FROM sys.databases WHERE name = N'AppDb';
  • Hit login, a primary list page, and one write path (cart, form post, admin save) before removing maintenance messaging.
  • Watch for login failures and error 18456 in the SQL error log immediately after cutover—almost always mapping or password drift.
  • If you restored with NORECOVERY for log shipping drills, do not point IIS at that name; the app will fail in confusing ways.

Practical takeaway: treat restore as a three-step release—verify backup chain, restore beside production and fix logins, then cut over IIS with a drained app pool and a single connection-string change. Script the T-SQL and the app_offline steps once per application so the 2 a.m. run is muscle memory, not invention. On a Windows host running SQL Server 2022 or 2025 beside IIS, that discipline matters more than any single backup checkbox.