I run the code below for playing the new Database Recovery Advisor
(from http://blogs.msdn.com/b/managingsql/archive/2011/07/13/recovery-advisor-an-introduction.aspx). As my SQL Server 2012 instance name is a named instance, I have made some changes. But I get an error message when running the restore command: Error 3154: The backup set holds a backup of a database other than the existing database 'fork_scenario'.
CREATE DATABASE fork_scenario
GO
USE fork_scenario
GO
CREATE TABLE t (c INT)
GO
--My instance name is SQL2012, so changed the default name MSSQLSERVER to SQL2012
BACKUP DATABASE [fork_scenario] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\fork_scenario.bak'
WITH NOFORMAT
,NOINIT
,NAME = N'fork_scenario-Full Database Backup'
,SKIP
,NOREWIND
,NOUNLOAD
,STATS = 10
GO
INSERT INTO t
VALUES (1)
BACKUP DATABASE [fork_scenario] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\fork_scenario.bak'
WITH DIFFERENTIAL
,NOFORMAT
,NOINIT
,NAME = N'fork_scenario-Differential Database Backup'
,SKIP
,NOREWIND
,NOUNLOAD
,STATS = 10
GO
INSERT INTO t
VALUES (2)
--wait for a minute
BACKUP LOG [fork_scenario] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\fork_scenario.bak'
WITH NOFORMAT
,NOINIT
,NAME = N'backup_fork-Transaction Log Backup'
,SKIP
,NOREWIND
,NOUNLOAD
,STATS = 10
GO
--Recover to the differential backup.
USE [master]
ALTER DATABASE [fork_scenario]
SET SINGLE_USER
WITH
ROLLBACK IMMEDIATE
GO
RESTORE DATABASE [fork_scenario]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\fork_scenario.bak'
WITH FILE = 1
,NORECOVERY
,NOUNLOAD
,REPLACE
,STATS = 5
GO
RESTORE DATABASE [fork_scenario]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\fork_scenario.bak'
WITH FILE = 2
,NOUNLOAD
,STATS = 5
GO
ALTER DATABASE [fork_scenario]
SET MULTI_USER
GO
USE fork_scenario
GO
INSERT INTO t
VALUES (3)
BACKUP LOG [fork_scenario] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\fork_scenario.bak'
WITH NOFORMAT
,NOINIT
,NAME = N'fork_scenario-Transaction Log Backup'
,SKIP
,NOREWIND
,NOUNLOAD
,STATS = 10
GO
Reason:
The file position numbers are not the same as the those for backup. They need to be the same.
Solution:
In my case, the two file position numbers for full and differential backups are 12 and 13. The problem is fixed when I change the file position numbers as below. :
RESTORE DATABASE [fork_scenario] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\fork_scenario.bak' WITH FILE = 12, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5
GO
RESTORE DATABASE [fork_scenario] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\fork_scenario.bak' WITH FILE = 13, NOUNLOAD, STATS = 5
GO