March 13, 2008 at 12:33 pm
Hi,
First off all, let me explain what I'm trying to do:
In my case, I have to maintain high-availability on the client datawarehouse.
The datawarehouse consists of 3 filegroups, which contains data from the staging-, dwh- and datamartlayer.
The datawarehouse ETL-proces is scheduled once in every 24hours. Now I'd like to keep the data of the datamartlayer (seperate filegroup) when the ETL-proces crashes. So the datamart will be available, in case of a crash of the ETL-proces. for the end-users. (worst-case it's a day old, but that's no problem).
My thought about implementing this case, resulted in the next scenario:
1. Switch to full recovery model
2. Backup filegroup to disk
3. Backup log
Start ETL --> on failure go to step 4
4. Restore filegroup
5. Restore log
6. Report error to system administrator
Now I've got a very frustating issue at restoring the filegroup.
I can make a copy and restore the filegroup only, but if there are any transactions executed between the backup and restore action, I'll get an message that the LSN= greater then the one expected.
This is my code for now (test database):
A record will be added in a filegroup, the filegroup will be backupped and after the backup the record will be deleted. The restore action fails when there's no new backup created after the delete transaction. However, when I create a backup after that transaction, the restore action becomes succesful, but the record is still deleted, because the latest actions are restored.
Code:
--create testdatabase
USE MASTER
DROP DATABASE VINCENT
go
create database vincent
USE [master]
GO
ALTER DATABASE [vincent] ADD FILEGROUP [kba]
GO
ALTER DATABASE [vincent]
ADD FILE (NAME = N'vincent_kba',
FILENAME = N'D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\vincent_kba.ndf' ,
SIZE = 3072KB ,
FILEGROWTH = 1024KB
)
TO FILEGROUP [kba]
GO
--create test table
use vincent
create table test (id int) on kba
--insert dummy record
insert into VINCENT.DBO.test select 2
--restricted mode
USE [master]
GO
ALTER DATABASE [vincent] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [vincent] SET RESTRICTED_USER
GO
--selection will return '2'
SELECT * FROM VINCENT.DBO.TEST
-----
--backup whole db (loq req.)
BACKUP DATABASE vincent to disk=N'D:\KobaltDWH\Data\TEST.bak'
WITH NOFORMAT, INIT, NAME = N'TEST' --, NO_LOG, COPY_ONLY
--backup filegroup
BACKUP DATABASE vincent FILEGROUP = N'KBA' to disk=N'D:\KobaltDWH\Data\TESTKBA.bak'
WITH NOFORMAT, INIT, NAME = N'TEST KBA' , COPY_ONLY
-- Back up the currently active transaction log.
BACKUP LOG VINCENT TO disk=N'D:\KobaltDWH\Data\TESTLOG.bak' with init
use master
--Delete dummy record
DELETE FROM VINCENT.DBO.TEST
--Log again because else poin in time error occures
BACKUP LOG VINCENT TO disk=N'D:\KobaltDWH\Data\TESTLOG.bak' with noinit
--Take file offline
ALTER DATABASE VINCENT MODIFY FILE (name='vincent_kba', OFFLINE)
--Restore filegroup
RESTORE DATABASE vincent FILE = N'vincent_kba' FROM DISK = N'D:\KobaltDWH\Data\TESTKBA.bak'
WITH FILE =1, NOrecovery, No_log, replace
--Restore log, I think here's something wrong
restore LOG VINCENT from disk=N'D:\KobaltDWH\Data\TESTLOG.bak' with file=1
restore LOG VINCENT from disk=N'D:\KobaltDWH\Data\TESTLOG.bak' with file=2
--RESTORE LOG vincent FROM disk=N'D:\KobaltDWH\Data\TESTLOG.bak' with norecovery
--WITH NORECOVERY
--restore database vincent with recovery
select * from vincent.dbo.test
--returns zero records for now (should return the dummy record)
GO
--set db back in multi_user mode
ALTER DATABASE [vincent] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [vincent] SET MULTI_USER
GO
Please help me out, I've absolute now options left.
Thanks in advance!
Vincent
On the targetserver there's an database that consists of
Today I tried everything to
March 18, 2008 at 5:26 am
Hello Vincent,
This is pretty interesting and tricky.
After breaking my head for 1 hrs. and practally doing your exercise finally I got the solution for this.
Just execute the following command for log with norecovery option before executing OFFLINE command.
BACKUP LOG VINCENT TO DISK = N'D:\kOBALTdwh\Data\TESTLOG.bak' with noinit, norecovery
i.e. Log again step need to be added with norecovery option
Why this is happening:
Here we are taking database into restore mode without taking the tlog backup as taking filegroup offline will keep the database open and when you are trying to restore the backup it will stuck due to LSN missmatch.
Bottom Line:
For SQL Server 2005 take the tlog bakcup with norecovery before restoring any file or filegroup backup.
Hope this will clarify all your doubts.
Cheers!!!
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 19, 2008 at 7:57 am
At first, many thanks for your reply. I hope you didn't break your head that hard:)
I changed my script in:
Code:
--create testdatabase
USE MASTER
DROP DATABASE VINCENT
go
create database vincent
USE [master]
GO
ALTER DATABASE [vincent] ADD FILEGROUP [kba]
GO
USE [master]
GO
ALTER DATABASE [vincent]
ADD FILE ( NAME = N'vincent_kba',
FILENAME = N'D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\vincent_kba.ndf' ,
SIZE = 3072KB ,
FILEGROWTH = 1024KB
)
TO FILEGROUP [kba]
GO
GO
--create test table
use vincent
create table test (id int) on kba
--insert dummy record
insert into VINCENT.DBO.test select 2
--restricted mode
USE [master]
GO
ALTER DATABASE [vincent] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [vincent] SET RESTRICTED_USER
GO
--selection will return '2'
SELECT * FROM VINCENT.DBO.TEST
-----
--backup whole db (loq req.)
BACKUP DATABASE vincent to disk=N'D:\KobaltDWH\Data\TEST.bak'
WITH NOFORMAT, INIT, NAME = N'TEST' --, NO_LOG, COPY_ONLY
--backup filegroup
BACKUP DATABASE vincent FILEGROUP = N'KBA' to disk=N'D:\KobaltDWH\Data\TESTKBA.bak'
WITH NOFORMAT, INIT, NAME = N'TEST KBA' , COPY_ONLY
-- Back up the currently active transaction log.
BACKUP LOG VINCENT TO DISK = N'D:\kOBALTdwh\Data\TESTLOG.bak' with init --, recovery
use master
--Delete dummy record
DELETE FROM VINCENT.DBO.TEST
--Log again because else point in time error occures
BACKUP LOG VINCENT TO DISK = N'D:\kOBALTdwh\Data\TESTLOG.bak' with noinit
BACKUP LOG VINCENT TO DISK = N'D:\kOBALTdwh\Data\TESTLOG.bak' with noinit, norecovery
--Take file offline
ALTER DATABASE VINCENT MODIFY FILE (name='vincent_kba', OFFLINE)
--Restore filegroup
RESTORE DATABASE vincent FILE = N'vincent_kba' FROM DISK = N'D:\KobaltDWH\Data\TESTKBA.bak'
WITH FILE =1, NOrecovery, No_log, replace
--Restore log, I think here's something wrong
restore LOG VINCENT from disk=N'D:\KobaltDWH\Data\TESTLOG.bak' with file=1
--restore LOG VINCENT from disk=N'D:\KobaltDWH\Data\TESTLOG.bak' with file=2
RESTORE LOG vincent FROM disk=N'D:\KobaltDWH\Data\TESTLOG.bak' with recovery
--WITH NORECOVERY
restore database vincent with recovery
select * from vincent.dbo.test
--returns zero records for now (should return the dummy record)
GO
--set db back in multi_user mode
ALTER DATABASE [vincent] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [vincent] SET MULTI_USER
GO
It gives me the following output:
(1 row(s) affected)
(1 row(s) affected)
Processed 160 pages for database 'vincent', file 'vincent' on file 1.
Processed 16 pages for database 'vincent', file 'vincent_kba' on file 1.
Processed 5 pages for database 'vincent', file 'vincent_log' on file 1.
BACKUP DATABASE successfully processed 181 pages in 0.067 seconds (22.054 MB/sec).
Processed 16 pages for database 'vincent', file 'vincent_kba' on file 1.
Processed 1 pages for database 'vincent', file 'vincent_log' on file 1.
BACKUP DATABASE...FILE= successfully processed 17 pages in 0.030 seconds (4.625 MB/sec).
Processed 6 pages for database 'VINCENT', file 'vincent_log' on file 1.
BACKUP LOG successfully processed 6 pages in 0.018 seconds (2.645 MB/sec).
(1 row(s) affected)
Processed 1 pages for database 'VINCENT', file 'vincent_log' on file 2.
BACKUP LOG successfully processed 1 pages in 0.021 seconds (0.024 MB/sec).
Processed 1 pages for database 'VINCENT', file 'vincent_log' on file 3.
BACKUP LOG successfully processed 1 pages in 0.041 seconds (0.037 MB/sec).
Processed 16 pages for database 'vincent', file 'vincent_kba' on file 1.
RESTORE DATABASE ... FILE= successfully processed 16 pages in 0.048 seconds (2.730 MB/sec).
Processed 0 pages for database 'VINCENT', file 'vincent_kba' on file 1.
Processed 6 pages for database 'VINCENT', file 'vincent_log' on file 1.
The roll forward start point is now at log sequence number (LSN) 50000000020400001. Additional roll forward past LSN 50000000020800001 is required to complete the restore sequence.
This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous messages indicate reasons why recovery cannot occur at this point.
RESTORE LOG successfully processed 6 pages in 0.011 seconds (4.328 MB/sec).
The roll forward start point is now at log sequence number (LSN) 50000000020400001. Additional roll forward past LSN 50000000020800001 is required to complete the restore sequence.
This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous messages indicate reasons why recovery cannot occur at this point.
RESTORE LOG successfully processed 0 pages in 0.005 seconds (0.000 MB/sec).
Msg 4303, Level 16, State 1, Line 40
The roll forward start point is now at log sequence number (LSN) 50000000020400001. Additional roll forward past LSN 50000000020800001 is required to complete the restore sequence.
Msg 3013, Level 16, State 1, Line 40
RESTORE DATABASE is terminating abnormally.
Msg 927, Level 14, State 2, Line 42
Database 'vincent' cannot be opened. It is in the middle of a restore.
Msg 5052, Level 16, State 1, Line 2
ALTER DATABASE is not permitted while a database is in the Restoring state.
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.
Msg 5052, Level 16, State 1, Line 1
ALTER DATABASE is not permitted while a database is in the Restoring state.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
-- Something is going wrong with the LSN position. I think the delete command is causing the
What am I doing wrong? Is it the delete statement? But I just want to keep that transaction out off the log.
Thanks in advance.
Vincent
February 17, 2010 at 1:09 am
Pro's,
Today I've got a PM about this subject.
I worked out some sample for a PIT-restore. I'd like to share this to you all:
Poin in time (Marked transaction)
-- MARKED TRANSACTION
--GO BACK TO INITIAL STATE
USE AdventureWorksDW
IF EXISTS (SELECT 1 FROM SYS.OBJECTS WHERE NAME = 'TEST')
BEGIN
DROP TABLE DBO.TEST
END
--SPECIFY DATABASE [FULL RECOVERY MODEL REQUIRED]
GO
USE [master]
GO
ALTER DATABASE [AdventureWorksDW] SET RECOVERY FULL WITH NO_WAIT
GO
--INITIAL FULL BACKUP (C DRIVE I.E.)
BACKUP DATABASE [AdventureWorksDW] TO DISK = N'c:\BackupFullAdventureworks_20100119.bak' WITH NOFORMAT, NOINIT,
NAME = N'AdventureWorksDW-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- Log Backup
BACKUP LOG [AdventureWorksDW] TO DISK = N'c:\BackuplOGAdventureworks_20100119.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorksDW-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- EXECUTE TRANSACTION 1
USE AdventureWorksDW
BEGIN TRANSACTION X WITH MARK 'X'
CREATE TABLE TEST (ID VARCHAR(100))
INSERT INTO TEST VALUES ('SAMPLE 1')
COMMIT TRANSACTION X
GO
-- 2ND Log Backup
BACKUP LOG [AdventureWorksDW] TO DISK = N'c:\BackuplOGAdventureworks_20100119.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorksDW-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- SELECT (TO SHOW THAT SAMPLE 1 IS AVAILABLE')
select * FROM AdventureWorksDW.dbo.TEST
GO
--- EXECUTE TRANSACTION 2 - THAT WILL BE ROLLBACKED (USE MARK)
BEGIN TRANSACTION Y WITH MARK 'Y'
INSERT INTO TEST VALUES ('SAMPLE 2')
COMMIT TRANSACTION Y
GO
-- SELECT (TO SHOW THAT SAMPLE 1 + 2 ARE AVAILABLE')
select * FROM AdventureWorksDW.dbo.TEST
GO
-- BACKUP TAIL OF THE LOG
USE MASTER
BACKUP LOG [AdventureWorksDW] TO DISK = N'c:\BackuplOGtAILAdventureworks_20100119.bak' WITH NORECOVERY
GO
-- RESTORE FULL BACKUP
USE MASTER
RESTORE DATABASE [AdventureWorksDW] FROM DISK = N'c:\BackupFullAdventureworks_20100119.bak' WITH FILE = 1, NOUNLOAD, STATS = 10, norecovery
-- Restore TILL MARKED TRANSACTION
GO
--RESTORE LOG [AdventureWorksDW] FROM DISK = N'c:\BackupLogAdventureworks_20100119.bak' WITH FILE = 1, RECOVERY, STOPAT = '20100119 08:19';
RESTORE LOG [AdventureWorksDW] FROM DISK = N'c:\BackupLogAdventureworks_20100119.bak' WITH FILE = 1, NORECOVERY
RESTORE LOG [AdventureWorksDW] FROM DISK = N'c:\BackupLogAdventureworks_20100119.bak' WITH FILE = 2, RECOVERY, STOPBEFOREMARK='Y'
-- FINAL RESTORE
GO
RESTORE DATABASE [AdventureWorksDW] WITH RECOVERY
GO
-- SHOW THAT ONLY SAMPLE 1 IS AVAILABLE
USE AdventureWorksDW
select * FROM TEST
February 17, 2010 at 1:11 am
Same but now untill datetime:
-- RESTORE FULL BACKUP
USE MASTER
RESTORE DATABASE [AdventureWorksDW] FROM DISK = N'c:\BackupFullAdventureworks_20100119.bak' WITH FILE = 1, NOUNLOAD, STATS = 10, norecovery
GO
RESTORE LOG [AdventureWorksDW] FROM DISK = N'c:\BackupLogAdventureworks_20100119.bak' WITH FILE = 1, NORECOVERY
RESTORE LOG [AdventureWorksDW] FROM DISK = N'c:\BackupLogAdventureworks_20100119.bak' WITH FILE = 1, RECOVERY, STOPAT = '20100119 '; -- VUL HIER DATUM IN
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply