Copying a SQL Server Database: Backup and Restore vs Copy Database Wizard

2019-06-07 · mssql, backup, restore, migration

Somebody asks me for a fresh copy of production almost every week: a test refresh, a new server, a private copy for a developer. SSMS puts the Copy Database Wizard right in the context menu, so it looks like the obvious choice, but I use backup and restore nearly every time. Here are both, where the wizard falls down, and the cleanup you need after either.

Before you start: you need sysadmin (or dbcreator plus BACKUP DATABASE rights on the source), a share both servers can reach, and enough destination space for the files at their current sizes, not their used size. A 200 GB file with 20 GB of data still restores as 200 GB.

Back up with COPY_ONLY

A normal full backup resets the differential base, which would break the production differential chain. COPY_ONLY avoids that:

BACKUP DATABASE [Sales]
TO DISK = N'\\backupsrv\transfer\Sales_copy.bak'
WITH COPY_ONLY, COMPRESSION, CHECKSUM, INIT, STATS = 5;

On SQL Server 2016 and later you can back up straight into a URL in Azure Blob Storage if the destination is in another datacentre, but a file share is the usual case.

Restore with MOVE

The destination server rarely has the same drive letters. RESTORE FILELISTONLY lists the logical file names you need for the MOVE clauses:

RESTORE FILELISTONLY FROM DISK = N'\\backupsrv\transfer\Sales_copy.bak';

Then restore under a new name if the destination already has a Sales database, or under the same name otherwise:

RESTORE DATABASE [Sales_Test]
FROM DISK = N'\\backupsrv\transfer\Sales_copy.bak'
WITH MOVE N'Sales' TO N'D:\SQLData\Sales_Test.mdf',
     MOVE N'Sales_log' TO N'L:\SQLLogs\Sales_Test.ldf',
     REPLACE, STATS = 5;

REPLACE is required when overwriting an existing database whose name does not match the backup, and it also skips the tail-log backup check. I only type REPLACE after reading the server name in the connection tab twice. On a test server it's what you want; on production it's how people lose data.

Restores between versions only go forward: a 2014 backup restores on 2016 or 2017, never the other way round. The restored database keeps its old compatibility level; raise it explicitly if you want new optimizer behaviour.

The Copy Database Wizard, and when it's acceptable

Right-click the database → Tasks → Copy Database. The wizard offers two transfer methods:

Detach and attach: takes the source database offline, copies the files through the SSIS package it generates, and attaches them at the destination. Fast, but the source is unavailable during the copy.
SQL Management Object (SMO): scripts the schema and bulk copies the data while the source stays online. Slow for anything above a few gigabytes, and it recreates objects one by one, so the copy is not a byte-identical clone.

Under the hood the wizard creates an SSIS package and an Agent job on the destination server, so the destination needs Integration Services installed, the Agent running, and the Agent service account needs access to both servers' file systems. It can optionally copy logins and Agent jobs, which is the one thing backup/restore does not do for you.

My rule: use the wizard when you are moving a small database to a new server together with its logins and jobs, and you can accept the downtime of detach/attach. For everything else, backup and restore.

Orphaned users

After a restore on another instance, the database users still reference the SID of the login on the old server. Windows logins usually line up automatically because the SID comes from Active Directory; SQL logins do not.

USE Sales_Test;
EXEC sp_change_users_login 'Report';

For each orphan, if the login exists on the new server:

ALTER USER [AppUser] WITH LOGIN = [AppUser];

If the login does not exist yet, create it with the same SID as on the source to avoid this step in future refreshes:

-- on the source
SELECT name, sid, password_hash FROM sys.sql_logins WHERE name = 'AppUser';
-- on the destination
CREATE LOGIN [AppUser] WITH PASSWORD = 0x0200...  HASHED, SID = 0xA1B2...;

Microsoft's sp_help_revlogin script does exactly this for all logins at once.

Cleanup on a test copy

A production copy on a test server brings settings you probably don't want:

ALTER DATABASE [Sales_Test] SET RECOVERY SIMPLE;
ALTER DATABASE [Sales_Test] SET TRUSTWORTHY OFF;
ALTER DATABASE [Sales_Test] SET COMPATIBILITY_LEVEL = 140;
EXEC Sales_Test.dbo.sp_changedbowner 'sa';
-- disable Database Mail profiles / linked servers / CDC / replication as applicable

Also check for Service Broker, CDC and full-text catalogs, all of which come along and may start doing work you did not intend on the test server.

Quick checks

SELECT name, state_desc, recovery_model_desc, compatibility_level, is_trustworthy_on
FROM sys.databases WHERE name = 'Sales_Test';
DBCC CHECKDB ('Sales_Test') WITH NO_INFOMSGS;

CHECKDB on the copy is a free integrity check of production data on hardware you do not mind loading. Then log in as the application account and run a representative query.

Errors

"Msg 3154: The backup set holds a backup of a database other than the existing 'Sales_Test' database." You are restoring over a database with a different name without REPLACE. Add it, or drop the target first.
"Msg 3201: Cannot open backup device ... Operating system error 5 (Access is denied.)" The destination SQL Server service account cannot read the share. It is the engine, not your login, that reads the file.
"Msg 1834: The file 'D:\SQLData\Sales.mdf' cannot be overwritten. It is being used by database 'Sales'." The MOVE clause was omitted or points at the paths of an existing database. Give the files new names.
Copy Database Wizard fails with "The job failed. Check the event log on the destination server computer for details" and the Agent history shows "Failed to connect to server". The Agent service account on the destination cannot authenticate to the source. Run the wizard's job under a proxy or grant the account a login on the source.

If you only learn one method, learn the boring one.