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
.
