February 19, 2009 at 1:09 pm
I create a database with a few data files, take a full database backup, remove a data file, take a differential backup, then try to restore. I am getting a message telling me that: 'The database cannot be recovered because the log was not restored'. It says I need to do additional roll-forward to bring the database online.
Why doesn't this work? How can I change the restore sequence to work?
CREATE DATABASE [TestDb_small] ON PRIMARY
( NAME = N'TestDb_small_data_0', FILENAME = N'c:\SQL_Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb_small_data_0.mdf' ),
( NAME = N'TestDb_small_data_1', FILENAME = N'c:\SQL_Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb_small_data_1.ndf' ),
FILEGROUP [TestDb_small_FG1]
( NAME = N'TestDb_small_data_2', FILENAME = N'c:\SQL_Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb_small_data_2.ndf' ),
( NAME = N'TestDb_small_data_3', FILENAME = N'c:\SQL_Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb_small_data_3.ndf' ),
FILEGROUP [TestDb_small_FG2]
( NAME = N'TestDb_small_data_4', FILENAME = N'c:\SQL_Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb_small_data_4.ndf' )
LOG ON
( NAME = N'TestDb_small_log', FILENAME = N'c:\SQL_Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb_small_log.ldf' )
go
ALTER DATABASE [TestDb_small] SET RECOVERY BULK_LOGGED
go
backup database TestDb_small to disk = 'testdb.bak' with format
go
USE TestDb_small
go
DBCC SHRINKFILE('TestDb_small_data_3', EMPTYFILE)
go
ALTER DATABASE [TestDb_small] REMOVE FILE TestDb_small_data_3
go
backup database TestDb_small to disk= 'TestDb.bak' with differential
go
use master
go
restore database TestDb_small from disk= TestDb.bak' with file=1 , norecovery , replace
go
restore database TestDb_small file= 'TestDb_small_data_0' ,file= 'TestDb_small_data_1',file= 'TestDb_small_data_2', file= 'TestDb_small_data_4' from disk= 'TestDb.bak' with file=2 , norecovery
go
restore database TestDb_small with recovery
go
February 19, 2009 at 4:13 pm
Based on the script you have here, it does not look like you have the tail of the transaction log backed up and that seems to be causing the problem. Do you have a log backup that was taken after the differential? If you do, it should work by using the code below (using your example)
CREATE DATABASE [TestDb_small] ON PRIMARY
( NAME = N'TestDb_small_data_0', FILENAME = N'c:\Data\TestDb_small_data_0.mdf' ),
( NAME = N'TestDb_small_data_1', FILENAME = N'c:\Data\TestDb_small_data_1.ndf' ),
FILEGROUP [TestDb_small_FG1]
( NAME = N'TestDb_small_data_2', FILENAME = N'c:\Data\TestDb_small_data_2.ndf' ),
( NAME = N'TestDb_small_data_3', FILENAME = N'c:\Data\TestDb_small_data_3.ndf' ),
FILEGROUP [TestDb_small_FG2]
( NAME = N'TestDb_small_data_4', FILENAME = N'c:\Data\TestDb_small_data_4.ndf' )
LOG ON
( NAME = N'TestDb_small_log', FILENAME = N'c:\Data\TestDb_small_log.ldf' )
go
ALTER DATABASE [TestDb_small] SET RECOVERY BULK_LOGGED
go
backup database TestDb_small to disk = 'c:\Data\testdb.bak' with format
go
USE TestDb_small
go
DBCC SHRINKFILE('TestDb_small_data_3', EMPTYFILE)
go
ALTER DATABASE [TestDb_small] REMOVE FILE TestDb_small_data_3
go
backup database TestDb_small to disk= 'c:\Data\testdb.bak' with differential
go
backup Log TestDb_small to disk= 'c:\Data\testdb.bak'
go
use master
go
restore database TestDb_small from disk= 'c:\Data\testdb.bak' with file=1 , norecovery , replace
go
restore database TestDb_small file= 'TestDb_small_data_0'
,file= 'TestDb_small_data_1'
,file= 'TestDb_small_data_2'
, file= 'TestDb_small_data_4'
from disk= 'c:\Data\testdb.bak'
with file=2
, norecovery
go
restore database TestDb_small
from disk= 'c:\Data\testdb.bak'
with file=3
, recovery
If you don't have a log backup, I am not completely sure where you would go from that.
February 20, 2009 at 4:51 pm
Thanks for your reply.
I did not have a log backup, but if I did your code would have worked.
I found the problem was that because I was doing a file restore of the differential backup I was not restoring the metadata required for the database to see that data file 3 had been removed.
If you don't have a log backup and you do a file restore of the differential backup like I did, there is nothing you can do but restart the restore sequence.
You have to either
a) Make this a piecemeal restore by appending "partial" to first restore statement (full database)
b) Don't do a file restore of the differential backup (instead just do: "restore database foo from disk = '"diff.bak" with recovery"). In this case the proper metadata showing the datafile was removed will be applied to the database.
February 20, 2009 at 7:52 pm
Oh, and the reason I wasn't required to have a tail-log backup was because I was in using the bulk logged recovery model.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply