I get pretty excited when I encounter a SQL Server error that I’ve never seen before. Today it was Msg 3180 during a point-in-time restore.
Msg 3180, Level 16, State 1, Line 2 This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally.
Some background… About every two weeks we need to run a point-in-time restore to sync up with a timestamp on a loosely connected DB2 database on one of three different non-production systems.
These are tedious, and with a few exceptions are pretty repeatable processes.
My first sign that something was different came right after the initial WITH NORECOVERY restore. I was expecting to see the chain of ‘upgrade step’ messages following a recovery to a newer SQL Server platform.
This was the command I ran
USE MASTER RESTORE DATABASE dev_copy FROM DISK = N'E:\SOURCEDB$PROD_prod_copy_FULL_20190205_213838.bak' WITH FILE = 1, MOVE 'prod_copy' TO 'E:\data\prod_copy.MDF', MOVE 'prod_copy_log' TO 'E:\logs\prod_copy.LDF', NORECOVERY, NOUNLOAD, REPLACE, STATS = 10;
But what? No ‘upgrade step’?
10 percent processed. 20 percent processed. 30 percent processed. 40 percent processed. 50 percent processed. 60 percent processed. 70 percent processed. 80 percent processed. 90 percent processed. 100 percent processed. Processed 895120 pages for database 'dev_copy', file 'prod_copy' on file 1. Processed 3 pages for database 'dev_copy', file 'prod_copy_log' on file 1. RESTORE DATABASE successfully processed 895123 pages in 34.877 seconds (200.508 MB/sec).
The next step in the point-in-time recovery was to restore the first transaction log backup file in the log sequence:
RESTORE DATABASE dev_copy FROM DISK = N'E:\SOURCEDB$PROD_prod_copy_LOG_20190205_211500.trn' WITH FILE = 1, STANDBY = N'E:\foo_UNDO.bak', STOPAT = '2019/02/06 06:00:00.000', STATS = 10;
This set off the unusual error:
Msg 3180, Level 16, State 1, Line 2 This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally.
The reason it was new to me was that this was the first time I had run a combination point-in-time recovery in conjunction with an upgrade from SQL Server 2012 to SQL Server 2016. All my previous point in time restores were lateral, on the same version of SQL Server. All the previous restores on this instance were simple replaces, not point-in-time restores.
The workaround became a d’oh moment … just run the point-in-time restore on a SQL Server 2012 instance, then run the backup again and do a regular restore with an upgrade on the 2016 instance.
Ordering was that important.
