Sql-server – RESTORE database now in Emergency

We have a database that was basically used for application purposes but it was using "master" – bad indeed. We created a new database called school that is now being used (same structure as how master was minus a table renamed correctly). I was trying to restore a .bak file from the old db (master from sql 2008) to the new school db (school in sql 2016).

Problem is, running the script gave me a bunch of lines saying it's updating, then the restore terminated abnormally… My db was then in a recovery pending state, I tried running emergency code but it seems pretty broken and i'm not sure 1. why it failed in the first place and 2. what to do now.

Below is the script code and the error message

use school;

DECLARE @TableSchema sys.sysname = N'dbo'
DECLARE @TableName sys.sysname = N'rolerights'
DECLARE @OldTableName sys.sysname = N'rolerigths'
DECLARE @OldTableWithSchema NVARCHAR(256) = QUOTENAME(@TableSchema) + '.' + QUOTENAME(@OldTableName)
DECLARE @TableWithSchema NVARCHAR(256) = QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName)

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
             WHERE TABLE_SCHEMA = @TableSchema
             AND  TABLE_NAME = @TableName))
BEGIN
    EXEC sp_rename @TableWithSchema, @OldTableName
END

DECLARE @Table TABLE ([LogicalName] varchar(128),[PhysicalName] varchar(128), [Type] varchar, [FileGroupName] varchar(128), [Size] varchar(128), 
            [MaxSize] varchar(128), [FileId]varchar(128), [CreateLSN]varchar(128), [DropLSN]varchar(128), [UniqueId]varchar(128), [ReadOnlyLSN]varchar(128), [ReadWriteLSN]varchar(128), 
            [BackupSizeInBytes]varchar(128), [SourceBlockSize]varchar(128), [FileGroupId]varchar(128), [LogGroupGUID]varchar(128), [DifferentialBaseLSN]varchar(128), [DifferentialBaseGUID]varchar(128), 
            [IsReadOnly]varchar(128), [IsPresent]varchar(128), [TDEThumbprint]varchar(128), [SnapshotUrl]varchar(128)
)

DECLARE @Path varchar(1000)='C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\Backup\SQL2008backup.bak'
DECLARE @LogicalNameData varchar(128),@LogicalNameLog varchar(128)
INSERT INTO @table
EXEC('
RESTORE FILELISTONLY 
   FROM DISK=''' +@Path+ '''
   ')

   SET @LogicalNameData=(SELECT LogicalName FROM @Table WHERE Type='D')
   SET @LogicalNameLog=(SELECT LogicalName FROM @Table WHERE Type='L')

SELECT @LogicalNameData, @LogicalNameLog

use master;

declare @MasterData nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg0', @MasterData output
select @MasterData=substring(@MasterData, 3, 255)
select @MasterData=substring(@MasterData, 1, len(@MasterData) - charindex('\', reverse(@MasterData)))
print @MasterData
print @LogicalNameData

declare @MasterLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg2', @MasterLog output
select @MasterLog=substring(@MasterLog, 3, 255)
select @MasterLog=substring(@MasterLog, 1, len(@MasterLog) - charindex('\', reverse(@MasterLog)))
print @MasterLog
print @LogicalNameLog

declare @DefaultData nvarchar(512)
select isnull(@DefaultData, CONVERT(nvarchar(512), @MasterData))
declare @DefaultLog nvarchar(512)
select isnull(@DefaultLog, CONVERT(nvarchar(512), @MasterLog)) 

declare @NewDefaultData nvarchar(512) = @MasterData + '\' + 'school.MDF'
declare @NewDefaultLog nvarchar(512) = @MasterLog + '\' + 'school.LDF'

SET DEADLOCK_PRIORITY 10
ALTER DATABASE school
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE school FROM DISK=@Path
WITH MOVE @LogicalNameData TO @NewDefaultData,
    MOVE @LogicalNameLog TO @NewDefaultLog,
    REPLACE

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
             WHERE TABLE_SCHEMA = @TableSchema
             AND  TABLE_NAME = @OldTableName))
BEGIN
    EXEC sp_rename @OldTableWithSchema, @TableName
END

And he is the emergency script

ALTER DATABASE [school] SET EMERGENCY;
GO
ALTER DATABASE [school] set single_user
GO
DBCC CHECKDB ([school], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE [school] set multi_user
GO

And the error:

Msg 5069, Level 16, State 1, Line 3
ALTER DATABASE statement failed.
Msg 946, Level 14, State 1, Line 5
Cannot open database 'school' version 677. Upgrade the database to the latest version.
Msg 946, Level 14, State 1, Line 7
Cannot open database 'school' version 677. Upgrade the database to the latest version.
Msg 5069, Level 16, State 1, Line 7
ALTER DATABASE statement failed.

Looking at the original logs… this is why it failed. An exception was thrown about filestream garbage collection apparently…

FILESTREAM Failed to find the garbage collection table.

FILELISTONLY shows this:

master  c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\master.mdf   D   PRIMARY 26607616    35184372080640  1   0   0   00000000-0000-0000-0000-000000000000    0   0   25952256    512 1   NULL    504000000001600021  AAA025D5-B254-4195-9378-F8C77FF5BD1E    0   1   NULL    NULL
mastlog c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\mastlog.ldf  L   NULL    2883584 35184372080640  2   0   0   00000000-0000-0000-0000-000000000000    0   0   0   512 0   NULL    0   00000000-0000-0000-0000-000000000000    0   1   NULL    NULL

@Randi, I ran the headers command:

BackupName  BackupDescription   BackupType  ExpirationDate  Compressed  Position    DeviceType  UserName    ServerName  DatabaseName    DatabaseVersion DatabaseCreationDate    BackupSize  FirstLSN    LastLSN CheckpointLSN   DatabaseBackupLSN   BackupStartDate BackupFinishDate    SortOrder   CodePage    UnicodeLocaleId UnicodeComparisonStyle  CompatibilityLevel  SoftwareVendorId    SoftwareVersionMajor    SoftwareVersionMinor    SoftwareVersionBuild    MachineName Flags   BindingID   RecoveryForkID  Collation   FamilyGUID  HasBulkLoggedData   IsSnapshot  IsReadOnly  IsSingleUser    HasBackupChecksums  IsDamaged   BeginsLogChain  HasIncompleteMetaData   IsForceOffline  IsCopyOnly  FirstRecoveryForkID ForkPointLSN    RecoveryModel   DifferentialBaseLSN DifferentialBaseGUID    BackupTypeDescription   BackupSetGUID   CompressedBackupSize    Containment KeyAlgorithm    EncryptorThumbprint EncryptorType
NULL    NULL    1   NULL    0   1   2   DOM\toh MI\SQLEXPRESS   master  661 2019-08-30 10:11:26.000 26038272    504000000037200155  504000000043500001  504000000037200155  504000000001600021  2019-08-30 10:29:02.000 2019-08-30 10:29:03.000 0   0   1033    196609  100 4608    10  50  4000    MILO    528 746062B2-787F-4B47-9AE2-100D9BAF93BF    89B51B99-FCC8-4A24-AC6B-D1BDA81B4427    Latin1_General_CI_AS    BF1A4637-15C2-4C86-BE62-B7D8F4F35A7B    0   0   0   0   1   0   0   0   0   0   89B51B99-FCC8-4A24-AC6B-D1BDA81B4427    NULL    FULL    NULL    NULL    Database    0224ED6D-0356-488C-878E-5079E187F43D    26038272    0   NULL    NULL    NULL

@Randi, I changed the restore part to as follows:

RESTORE DATABASE school FROM DISK=@Path
WITH MOVE @LogicalNameData TO @NewDefaultData,
    MOVE @LogicalNameLog TO @NewDefaultLog,
    RECOVERY,
    REPLACE

Error came back the same:

Caution: Changing any part of an object name could break scripts and stored procedures.

(2 rows affected)

(1 row affected)
C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA
master
C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA
mastlog

(1 row affected)

(1 row affected)
Processed 3168 pages for database 'school', file 'master' on file 1.
Processed 4 pages for database 'school', file 'mastlog' on file 1.
Converting database 'school' from version 661 to the current version 852.
Database 'school' running the upgrade step from version 661 to version 668.
Database 'school' running the upgrade step from version 668 to version 669.
Database 'school' running the upgrade step from version 669 to version 670.
Database 'school' running the upgrade step from version 670 to version 671.
Database 'school' running the upgrade step from version 671 to version 672.
Database 'school' running the upgrade step from version 672 to version 673.
Database 'school' running the upgrade step from version 673 to version 674.
Database 'school' running the upgrade step from version 674 to version 675.
Database 'school' running the upgrade step from version 675 to version 676.
Database 'school' running the upgrade step from version 676 to version 677.
Database 'school' running the upgrade step from version 677 to version 679.
Msg 3013, Level 16, State 1, Line 71
RESTORE DATABASE is terminating abnormally.
Caution: Changing any part of an object name could break scripts and stored procedures.
Msg 5570, Level 23, State 3, Line 71
FILESTREAM Failed to find the garbage collection table.

The error seems to be due to restoring the master database from sql server 2008 to sql server 2016 / 2017

One of the exceptions raised:

During upgrade, database raised exception 5570, severity 23, state 3,
address 00007FFD62FDB150. Use the exception number to determine the
cause

.