Migrating a database to SQL Server hosting

The best way to move a SQL Server database to a new host is a native backup and restore: BACKUP DATABASE ... WITH COPY_ONLY, CHECKSUM on the old server, copy the .bak, RESTORE DATABASE ... WITH MOVE on the new one. It is exact, it is fast, and it keeps everything inside the database - schema, data, users, permissions, statistics. It only works upwards, from the same or an older SQL Server version, and only if the database fits the target edition. When it does not apply - going to an older version, coming from Azure SQL Database, or arriving from MySQL or PostgreSQL - the alternatives are a BACPAC, generated scripts, or a schema conversion plus a data load. This guide covers how to choose, the checks to run before you start, each method in turn, and the clean-up that every migration needs afterwards.

Choosing a method#

The decision is mostly made by two questions. Is the source SQL Server at the same or an older version than the target? Then backup and restore. Is it newer, or Azure SQL Database, which cannot produce a .bak file at all? Then a BACPAC. Generated scripts are for small databases where you want to read or edit what is being moved, and conversion tools are for leaving another engine behind.

Checks before you move anything#

Five minutes of queries on the source saves a failed restore at midnight.

Version. Run SELECT @@VERSION; on the source. A backup restores only onto the same or a newer major version: 2022 is version 16, 2019 is 15, 2017 is 14, 2016 is 13. SQL Server 2022 restores backups from SQL Server 2008 onwards; a database still on 2005 or older needs an intermediate hop through a version that accepts it.

Size. If the target is Express, each database is limited to 10 GB of data files (the log does not count). Check what you actually use, not the allocated file size:

SELECT name, type_desc,       size / 128.0                              AS allocated_mb,       FILEPROPERTY(name, 'SpaceUsed') / 128.0   AS used_mbFROM sys.database_files;

A database with 14 GB allocated and 6 GB used will restore fine onto Express only if the data files are first shrunk below 10 GB, because a restore recreates files at their original size. Shrink the data file on the source (or on a restored copy) before the final backup, rebuild indexes afterwards, and be aware a shrink fragments indexes heavily. If you genuinely use more than 10 GB, archive old data first or choose an edition without the cap - SQL Server Express hosting covers where that line falls.

Edition features. Some features tie a database to an edition. This view lists any in use:

SELECT feature_name FROM sys.dm_db_persisted_sku_features;

Since SQL Server 2016 Service Pack 1, most programmability features - partitioning, columnstore, data compression, in-memory OLTP - are available in every edition, so on a modern source this usually returns nothing. If it lists Transparent Data Encryption, that database will not restore on Express; decrypt it at the source first.

Dependencies outside the database. List what lives on the old server rather than in the database: SQL Server Agent jobs, linked servers, logins, Database Mail profiles, server-level triggers, and any code that refers to other databases by three-part name (otherdb.dbo.Table). None of these travel with a backup. Agent jobs deserve special attention when the target is Express, which has no Agent - each job becomes a scheduled sqlcmd call somewhere else, as sqlcmd and bcp shows.

Method 1: backup and restore#

On the source, take a copy-only backup so you do not disturb its existing backup chain:

BACKUP DATABASE [shop]TO DISK = N'D:\Backups\shop-migrate.bak'WITH COPY_ONLY, CHECKSUM, INIT, STATS = 10;RESTORE VERIFYONLY FROM DISK = N'D:\Backups\shop-migrate.bak' WITH CHECKSUM;

Copy the file to the new server, into a directory the SQL Server process can read. RESTORE resolves the path on the server, not on your workstation, so uploading the file is a separate step: SFTP, the host's file manager, or whatever the host provides. On RE:NODE every server has SFTP and the file manager; if you are unsure which folder the engine can read, ask support before uploading a large file.

Then read the logical file names and restore with each file moved to the target's data directory. A Windows source has Windows paths baked into the backup, and on a Linux target they must all be moved:

RESTORE FILELISTONLY FROM DISK = N'/var/opt/mssql/data/shop-migrate.bak';RESTORE DATABASE [shop]FROM DISK = N'/var/opt/mssql/data/shop-migrate.bak'WITH MOVE N'shop'     TO N'/var/opt/mssql/data/shop.mdf',     MOVE N'shop_log' TO N'/var/opt/mssql/data/shop_log.ldf',     RECOVERY, CHECKSUM, STATS = 10;

SELECT SERVERPROPERTY('InstanceDefaultDataPath'); gives the right directory if you do not know it. Once restored, the backup file can be deleted from the server - on a plan with limited disk, a 9 GB .bak beside a 9 GB database is the quickest way to fill it. SQL Server backup and restore covers the restore options and errors in more depth.

A hosted server may already have a database created for you. You can restore over it with REPLACE, or restore under your original name and point things at that instead. If your sa login has the pre-created database as its default and you restore under another name, change the default so tools open the right one:

ALTER LOGIN [sa] WITH DEFAULT_DATABASE = [shop];

Method 2: BACPAC with SqlPackage#

A BACPAC is a zip file holding the database schema as a model plus the data of every table in bulk-copy format. It is produced by connecting to the source as a client, so it needs no file access to either server, works from Azure SQL Database, and can move a database to an older version as long as it uses no features the older version lacks.

$ sqlpackage /Action:Export \    /SourceConnectionString:"Server=old.example.net,1433;Database=shop;User Id=sa;Password=...;TrustServerCertificate=True" \    /TargetFile:shop.bacpac$ sqlpackage /Action:Import \    /SourceFile:shop.bacpac \    /TargetConnectionString:"Server=db.example.net,14330;Database=shop;User Id=sa;Password=...;TrustServerCertificate=True"

The import creates the database, or fills an existing one that is completely empty. In SSMS, the same operations are under the database's Tasks menu as Export Data-tier Application, and under the Databases node as Import Data-tier Application.

The trade-offs are real:

• Consistency. An export reads tables one after another with no single snapshot. If writes happen during the export, the BACPAC can contain an order without its lines. Stop the application, or export from a restored copy of a backup.
• Speed. It is several times slower than a native backup and restore, and the import rebuilds every index. Fine for a few gigabytes, tedious for tens.
• Validation. The export validates the schema first and refuses databases it cannot represent - most often because of references to other databases, or objects that no longer compile. The error names the objects; fix or drop them and run it again.
• What is not carried. Statistics, Query Store data, the transaction log and the backup history do not travel. Logins do not either, but contained users and database users do.

SqlPackage connects with the same encryption defaults as other modern Microsoft drivers, so a server with a self-signed certificate needs TrustServerCertificate=True in the connection string, as above. SQL Server connection strings covers the encryption keywords.

Method 3: generated scripts and bcp#

For a small database, SSMS can write the whole thing out as T-SQL: right-click the database, Tasks, Generate Scripts. Choose the objects, then under Advanced set "Types of data to script" to "Schema and data" and "Script for Server Version" to the target's version. The result is one .sql file of CREATE statements followed by INSERTs.

It is readable, editable and version-neutral, which makes it the right tool for a database of a few hundred megabytes that you want to tidy on the way across. It is the wrong tool for anything big: a script with millions of single-row INSERT statements runs slowly and SSMS struggles to even open it. Run large scripts with sqlcmd -i rather than in a query window.

For larger databases, combine the two: script only the schema, run it on the target, then move the data table by table with bcp in native format, which is fast and exact. Load parent tables before children, or disable foreign keys during the load and re-enable them WITH CHECK afterwards so they are trusted again.

Coming from MySQL or PostgreSQL#

Moving from another engine is a conversion, not a copy. For MySQL, Oracle, Access, Db2 and SAP ASE, Microsoft's free SQL Server Migration Assistant (SSMA) converts the schema, flags what it cannot convert, and copies the data. There is no SSMA for PostgreSQL; there you convert the schema by hand or with your framework's migrations, then load data through CSV and bcp, or with a small script that reads from one connection and bulk-copies into the other.

The type mapping is mostly mechanical:

The text types deserve the most thought, because MySQL and PostgreSQL are UTF-8 throughout and SQL Server's varchar is not, unless the column uses a UTF-8 collation. SQL Server collations and Unicode explains the choice. The SQL itself needs work too: LIMIT becomes TOP or OFFSET ... FETCH, backtick and double-quote identifiers become square brackets, RETURNING becomes OUTPUT, and upserts become MERGE or an update-then-insert. T-SQL basics for app developers collects the differences that bite. If the application uses an ORM, switching its provider and generating the schema from migrations is usually faster than converting DDL by hand.

Cutting over with little downtime#

For a small database, the simplest cutover is the honest one: put the application in maintenance mode, take the final backup, restore, change the connection string, bring the application back. For a 5 GB database on decent links that is a window of minutes.

When that is too long, use a full backup plus a differential. Restore the full backup ahead of time without recovering it, so it waits for more:

-- Days before: restore the full backup, leave it waitingRESTORE DATABASE [shop] FROM DISK = N'/var/opt/mssql/data/shop-full.bak'WITH MOVE N'shop' TO N'/var/opt/mssql/data/shop.mdf',     MOVE N'shop_log' TO N'/var/opt/mssql/data/shop_log.ldf',     NORECOVERY;-- At cutover: stop writes, take a differential on the source, thenRESTORE DATABASE [shop] FROM DISK = N'/var/opt/mssql/data/shop-diff.bak'WITH RECOVERY;

The differential holds only what changed since the full backup, so the downtime is the time to take, copy and apply that. Note that the full backup on the source must not be COPY_ONLY for this, because a differential is always based on the last normal full backup. If the source is in the full recovery model, log backups can shrink the gap further. Either way, rehearse the whole sequence once against a scratch database before the real night. Migrations without downtime covers the application side: schema changes that both versions of the code can live with.

After the restore: the clean-up list#

Every migrated database needs the same few things before the application points at it:

1. Fix orphaned users. SQL logins on the new server have new SIDs, so restored users do not match them. Create each login, then ALTER USER [app] WITH LOGIN = [app];. To move logins with their passwords and SIDs intact, Microsoft's sp_help_revlogin script generates the CREATE LOGIN statements on the source. SQL Server logins, users and roles explains the model.
2. Check the compatibility level. A restored database keeps the level it had, which may be 110 or 130 on a 2022 server. Test the application at that level first, then raise it: ALTER DATABASE [shop] SET COMPATIBILITY_LEVEL = 160;. With Query Store on, you can compare plans before and after and force the old plan for any query that regresses; SQL Server Query Store and slow queries shows how.
3. Update statistics. EXEC sp_updatestats; in the restored database, so the optimiser is not working from figures gathered on different hardware and a different version's estimator.
4. Check integrity. DBCC CHECKDB WITH NO_INFOMSGS; once, to start with a known-good database.
5. Set page verification. Databases that began life on very old versions may still use TORN_PAGE_DETECTION. ALTER DATABASE [shop] SET PAGE_VERIFY CHECKSUM; is the modern setting.
6. Check the recovery model and the log size, which may have arrived oversized from the source.
7. Update connection strings: new host, the port after a comma, SQL authentication, and the encryption settings.

FAQ#

Can I restore a SQL Server 2022 backup on SQL Server 2019?

No. Backups only restore onto the same or a newer version, and no option or trace flag changes that. Export a BACPAC instead, or script the schema and move the data with bcp, after checking the database uses nothing that 2019 lacks.

How do I migrate from Azure SQL Database to a hosted SQL Server?

Export a BACPAC from Azure SQL Database, through the portal or SqlPackage, and import it on the target. Azure SQL Database cannot produce a native .bak file, so backup and restore is not available from it.

My database is 12 GB. Can it run on SQL Server Express?

Not as it is. Express caps the data in each database at 10 GB. Check how much is actually used rather than allocated, archive or delete old data, and shrink the data file below the limit before backing up. If the data really exceeds 10 GB, you need a paid edition.

Do I need to move the transaction log file?

You move it with WITH MOVE like the data file, but its contents matter little: a restore recovers the database and the log starts afresh from there. If the source log was huge, shrink it once after the restore and set a sensible size, rather than carrying the old bloat over.

Will my stored procedures and views come across?

Yes, with backup and restore, a BACPAC or generated scripts - they are part of the database. What does not come across is anything stored at server level: logins, Agent jobs, linked servers and server triggers. List those before you start.