Moving From LocalDB to a Real Instance

The application works on one laptop, and now more people need it. Moving from LocalDB is a database move plus an application connection change. Back up the data, prove the restored copy, and test the identity that will use it.

Inventory the Source Before Moving From LocalDB

Record the LocalDB instance name, database names, SQL Server version, file locations, and application connection strings. Identify every place that creates or attaches the database. A developer build script and a background test runner can point to different files under the same friendly name. Query the current database list from the LocalDB connection and save the result with the move plan.

I ask the application owner which data is real and which data can be regenerated. That choice affects backup, validation, and cleanup. Is there a second local copy on another laptop? A migration that moves only one file is complete only if that file holds the intended data. Do not let a convenient database name answer the question for you.

SELECT
    name,
    state_desc,
    compatibility_level,
    collation_name
FROM sys.databases
WHERE database_id > 4
ORDER BY name;

Prepare the Target Instance

Choose an installed SQL Server instance that supports the application workload, access pattern, and operations plan. Check its edition, build, default collation, storage, backup schedule, and network access. Create the required service identity and database permissions through the normal change process. Keep development privileges out of the production login. A local administrator account that worked in LocalDB is not a production identity design.

When moving from LocalDB, I like to restore into a nonproduction target first. That exposes version, collation, file path, and login issues while rollback is still easy. Query the target’s version and default collation before deciding the restore procedure. SQL Server supports restoring a database backup to a newer compatible engine, but the database is upgraded when brought online and cannot then be restored back to the older engine.

SELECT
    @@SERVERNAME AS TargetInstance,
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('Collation') AS ServerCollation;

Take and Verify a Real Backup

Use SQL Server backup from the LocalDB instance to create a database backup at an approved Windows location. Confirm the backup completed and can be read. Copy it securely to the target host and verify file integrity. The backup captures database data, not every server-level dependency. Keep the source unchanged until the target application passes validation and the cutover decision is recorded.

Restore on the target with file locations appropriate to that instance. Use RESTORE FILELISTONLY to inspect the backup’s logical file names before choosing MOVE paths. A path copied from a laptop will rarely be a sensible data path on the server. Test the restore before any final freeze. I do not accept a backup icon as proof that restore will work. A restore is the proof.

Map Users to Target Logins

Database users can survive a restore while their matching server logins do not. Inventory contained users, Windows principals, and SQL logins used by the application. Recreate the needed server-level logins under security policy and map database users to them. Avoid simply granting db_owner to clear a login error. Confirm that the app can run its real read and write paths with its final identity.

Check scheduled jobs, linked servers, credentials, and external resources too. LocalDB rarely has a large server-level estate, but application code can still depend on one. A database backup does not carry server Agent jobs or instance configuration. Record every dependency and its owner before the switch.

Compare Collation Behavior

The database keeps its database collation during backup and restore, but the target instance has its own default collation. Temporary tables, server-level metadata, or new databases can expose differences. Compare the source database collation with the target and test searches, joins, sorting, and unique constraints that depend on case or accent behavior. A case-sensitive rule hidden in code can become visible only after a cross-database join.

I check the application’s name lookups and duplicate rules early. The phrase “it worked on my laptop” is a useful bug report, but a poor collation strategy. Keep the source collation unless a deliberate conversion is planned and tested. Changing collation is its own project.

Change the Connection String When Moving From LocalDB

Move the server name, database name, authentication method, encryption settings, and timeout into environment-specific configuration. The LocalDB server name includes the localdb prefix, while the target uses the service instance or listener name. Check certificate trust and network reachability from the application host. Do not copy a development setting that skips certificate validation into production because it makes the first test pass.

Test the new connection using the application service identity. A successful SSMS connection from an administrator’s desktop does not prove the service can connect. Keep the old configuration available for rollback until the data cutover makes rollback unsafe. Know that point before users start writing to the new database.

Plan the Final Data Window

After the rehearsal, decide how new writes will stop during final backup and restore. A small application can use a short maintenance window. Record the freeze start, final backup identity, restore completion, and validation results. If the downtime target is shorter, use a supported log backup or synchronization plan that matches the source recovery model and target version. Do not improvise that plan on cutover night.

Validate row coverage using application-level checks, not only a total count. Confirm critical records, recent writes, and transaction behavior. Then switch the connection and run a controlled write test. I keep the source read-only or otherwise protected after cutover so two writable copies do not quietly diverge.

Finish Moving From LocalDB With Operations

Set up target backups, monitoring, alert delivery, access review, and patch ownership. Test a restore from the new instance after the application is live. Update deployment scripts so a fresh install points to the target environment. Remove unused LocalDB references from production configuration, but retain the source copy under the agreed rollback and retention plan.

Moving from LocalDB is finished when the application works under its final identity and the operations team can recover it. A restored database that nobody can connect to is only a well-traveled file. Put that last test on the checklist.

Related reading on this blog: Moving From Express to Standard Without Reinstalling and Check Backup Reliability.

A LocalDB move is not just a restored file, it is a working application on a managed instance.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.