Restoring Your SQL Server Database After a Crash

This is part two of a two-part series on the SQL Server database that backs your Smart Service install. In part one we covered regularly backing up your SQL Server database. This article walks through restoring that backup, both as a trial run to make sure the procedure works on your machine and as the real thing when a crash happens. We use the same example file path from part one: D:\SqlBackups\SmartServiceDb.BAK.

Before You Start

Three things to verify before you touch anything:

• Identify the backup file on disk and confirm it is recent. A backup file you cannot find is the same as no backup file at all.
• If this is a real crash recovery, shut down Smart Service Desktop on every workstation and stop the Smart Service Sync Service and any iFleet sync processes. Restoring over a database with active connections fails or, worse, succeeds and leaves orphaned sessions.
• Know the recovery model your database uses. Smart Service's default is the Simple recovery model, which makes life easier on restore. The Full recovery model brings tail-log backup considerations into play.

This guide assumes SQL Server 2017 or later, including SQL Server 2019 and SQL Server 2022. The SSMS dialogs have been largely consistent across these versions.

Restore via SSMS

Open SQL Server Management Studio. From the View menu, select Object Explorer, then connect to the SQL Server instance hosting Smart Service.

Right-click Databases and select Restore Database. The Restore Database dialog opens.

Choose the Device radio option, then click the ellipsis button to the right.

Click Add, browse to the backup file, in our example D:\SqlBackups\SmartServiceDb.BAK, and click OK twice to return to the main dialog.

For a trial restore, change the Database name from SmartServiceDb to something distinct like SmartServiceRestoreTest so you do not clobber your live database. Click OK and let the restore complete. Modern hardware and a typical Smart Service database, 10 to 100 gigabytes, will finish in a few minutes. If the restore takes more than an hour, your server is overdue for an upgrade or you are looking at a much larger database than typical.

Restore via T-SQL

If you prefer scripting or you are restoring as part of an automation, T-SQL is the cleaner path. The basic statement for a trial restore:

RESTORE DATABASE SmartServiceRestoreTest FROM DISK = 'D:\SqlBackups\SmartServiceDb.BAK' WITH MOVE 'SmartServiceDb' TO 'D:\SqlData\SmartServiceRestoreTest.mdf', MOVE 'SmartServiceDb_log' TO 'D:\SqlData\SmartServiceRestoreTest_log.ldf', RECOVERY;

The MOVE clauses are required when restoring to a new database name on the same server, so the data and log files do not collide with the existing database. WITH RECOVERY brings the database online and ready to use. Use WITH NORECOVERY instead if you plan to apply additional transaction-log backups after the full restore.

For an overwrite-the-existing-database scenario, add WITH REPLACE and drop the MOVE clauses if the file paths match:

RESTORE DATABASE SmartServiceDb FROM DISK = 'D:\SqlBackups\SmartServiceDb.BAK' WITH REPLACE, RECOVERY;

Cleaning Up the Test

After a trial restore, delete the test database so it does not clutter Object Explorer or consume disk space. Right-click the test database, select Delete, and confirm the dialog shows the correct database name before clicking OK.

Always read the Delete Object dialog carefully. A dropped production database has no undo button.

Real Crash Recovery

In a real crash, you will likely see the corrupt original database still listed in Object Explorer. The clean recovery sequence:

• Shut down all clients and services. Smart Service Desktop on every workstation, the Smart Service Sync Service, iFleet sync, and any other process connected to the database.
• Note the current file paths. Right-click the corrupt database, select Properties, and write down the Path and File Name for the data and log files.
• Detach the corrupt database. Right-click, Tasks, Detach. Check the Drop Connections box and click OK. The database disappears from Object Explorer.
• Rename the files on disk. In Windows File Explorer, rename SmartServiceDb.mdf to SmartServiceDbCrashed.mdf and SmartServiceDb_log.ldf to SmartServiceDbCrashed_log.ldf.
• Attach the crashed database under its new name. Right-click Databases, Attach, Add, and select the renamed .mdf file. SSMS will flag the renamed log file with a missing-reference warning; click the ellipsis to point to the renamed .ldf file, then rename the attached database to SmartServiceDbCrashed.

Once the crashed database is renamed out of the way, run the SSMS Restore flow above but leave the destination Database name as SmartServiceDb, the original name. The restored database now picks up the original name and file paths, which means Smart Service Desktop, iFleet, and the Sync Service all reconnect without configuration changes.

Common Issues

The handful of restore errors that come up most often:

• "Exclusive access could not be obtained because the database is in use." Something is still connected. Re-verify that every workstation is closed out of Smart Service and the Sync Service is stopped. If SSMS itself has the database highlighted, click on a different node first.
• "The tail of the log for the database has not been backed up." SSMS by default offers to back up the tail log before overwriting. For a corrupt database you are about to replace anyway, check the Overwrite the existing database (WITH REPLACE) option and uncheck the tail-log box. For a database in Full recovery that you want to preserve to the last second, back up the tail log first.
• Database stuck in Restoring state. Usually means a previous restore was started with WITH NORECOVERY and never finalized. Run RESTORE DATABASE SmartServiceDb WITH RECOVERY; to bring it online.
• File path errors. If the source machine used C:\SQLData and the destination uses D:\SQLData, add WITH MOVE clauses in the T-SQL restore or use the Files page of the SSMS dialog to remap paths.
• Permission denied on the .BAK file. The SQL Server service account needs read access to the folder containing the backup. The simplest fix is to copy the backup to a folder the SQL Server service can read, such as the default backup directory.

Wrapping Up

A regularly tested restore is the only kind of restore worth having. Run the trial-restore procedure on your real backup file at least once a quarter so that when a crash happens, the recovery is muscle memory and not a stack-overflow rabbit hole. Keep the backup file on a different physical drive than the database itself, and ideally a copy off-site or in object storage.

For the backup half of the story, see our companion guide on regularly backing up your SQL Server database.

Smart Service for Field Service

If you are running a field service business and want a software stack that handles scheduling, dispatch, customer history, mobile invoicing, and recurring service contracts on top of a SQL Server backend you can manage yourself, Smart Service integrates with QuickBooks Desktop and QuickBooks Online and iFleet keeps techs in the field synced with the office. Try a free demo to see how it fits!