April 10, 2015 at 1:31 am
Hi,
I'm playing around with file group backups a bit. Want to see if we can get our backups smaller/faster.
And also trying to get maint jobs faster.
So the plan is, is to create a separate file group/s for some databases to which we can move our archive data too.
These file groups will only change once a month. So only needs to be backup monthly.
The problem is, is that I can't get my restore sequences right. (Really feel stupid to struggle with a trivial thing like this)
Below is some scripts that I'm using - with comments (and questions within those comments)
For this script to work you need to create a directory structure like this:
C:\Temp\TempDatabases_Tests\Backups
C:\Temp\TempDatabases_Tests\ActivePartitions
C:\Temp\TempDatabases_Tests\ArchiveData
First get the database created for the test:
/*
Drop and create a blank database to do checks on.
*/
use master
go
IF EXISTS(SELECT TOP 1 * FROM SYS.databases WHERE name ='TestDatabasePartialRestore')
BEGIN
ALTER DATABASE TestDatabasePartialRestore SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE TestDatabasePartialRestore
END
IF NOT EXISTS (SELECT TOP 1 * FROM SYS.databases WHERE name ='TestDatabasePartialRestore')
BEGIN
-- Create the database using default values (for size, growth etc.) Also - add diffent filegroups so that we can test filegroup restores.
CREATE DATABASE [TestDatabasePartialRestore]
ON PRIMARY
( NAME = N'TestDatabasePartialRestore', FILENAME = N'C:\Temp\TempDatabases_Tests\ActivePartitions\TestDatabasePartialRestore.mdf' ),
FILEGROUP [ActivePrimary]
( NAME = N'TestDatabasePartialRestore_ActivePrimary', FILENAME = N'C:\Temp\TempDatabases_Tests\ActivePartitions\TestDatabasePartialRestore_ActivePrimary.ndf'),
FILEGROUP [ArchiveData]
( NAME = N'TestDatabasePartialRestore_ArchiveData', FILENAME = N'C:\Temp\TempDatabases_Tests\ArchiveData\TestDatabasePartialRestore_ArchiveData.ndf' )
LOG ON
( NAME = N'TestDatabasePartialRestore_log', FILENAME = N'C:\Temp\TempDatabases_Tests\ActivePartitions\TestDatabasePartialRestore_log.ldf')
END
Create some tables within the file groups:
go
use TestDatabasePartialRestore
go
-- Check what file groups are available.
select * from sys.filegroups
/*
SHOULD BE:
PRIMARY
ActivePrimary
ArchiveData
*/
go
-- Create two tables - one on ActivePrimary, and one on ArchiveData
Create table tblActivePrimary
(
rID int identity
,SomeTextData varchar(100)
) on [ActivePrimary]
GO
CREATE TABLE tblArchiveData
(
rID int Identity
,SomeTextData varchar(100)
)on [ArchiveData]
go
-- Shows that the tables are created in the correct Filegroups
SELECT
tbl.name as TableName
,fg.name as FileGroupName
FROM sys.tables tbl
INNER JOIN
SYS.indexes si
ON si.object_id = tbl.object_id
INNER JOIN
sys.filegroups fg
ON fg.data_space_id = si.data_space_id
GO
-- Insert some data
INSERT INTO tblActivePrimary
(
SomeTextData
)select 'Before BACKUP';
go
INSERT INTO tblArchiveData
(
SomeTextData
)SELECT 'Before Backup';
go
GO
SELECT SomeTextData FROM tblActivePrimary
SELECT SomeTextData FROM tblArchiveData
Do a Full backup:
BACKUP DATABASE [TestDatabasePartialRestore]
TO DISK = 'C:\Temp\TempDatabases_Tests\Backups\TestDatabasePartialRestore_MonthlyFullBackup.bak'
Do some TX backups:
/*
Do the Hourly TX backups
*/
BACKUP log [TestDatabasePartialRestore]
TO DISK = 'C:\Temp\TempDatabases_Tests\Backups\TestDatabasePartialRestorelog 1.trn'
GO
BACKUP log [TestDatabasePartialRestore]
TO DISK = 'C:\Temp\TempDatabases_Tests\Backups\TestDatabasePartialRestorelog 2.trn'
GO
BACKUP log [TestDatabasePartialRestore]
TO DISK = 'C:\Temp\TempDatabases_Tests\Backups\TestDatabasePartialRestorelog 3.trn'
GO
Do a full FileGroup backup on the active file group:
/*
Full FILE GROUP BACKUP - This will be done weekly.
*/
BACKUP DATABASE [TestDatabasePartialRestore]
filegroup = 'ActivePrimary'
TO DISK = 'C:\Temp\TempDatabases_Tests\Backups\TestDatabasePartialRestore_ActivePrimary_Full.fbak'
More TX Backups:
/*
More Tx backups
*/
BACKUP log [TestDatabasePartialRestore]
TO DISK = 'C:\Temp\TempDatabases_Tests\Backups\TestDatabasePartialRestorelog 4.trn'
BACKUP log [TestDatabasePartialRestore]
TO DISK = 'C:\Temp\TempDatabases_Tests\Backups\TestDatabasePartialRestorelog 5.trn'
Then lastly do a Diff FileGroup backup:
BACKUP DATABASE [TestDatabasePartialRestore]
filegroup = 'ActivePrimary'
TO DISK = 'C:\Temp\TempDatabases_Tests\Backups\TestDatabasePartialRestore_ActivePrimary_Diff.fdif'
WITH DIFFERENTIAL
Ok so for my first test, I want to restore up to the Full FileGroup backup.
RESTORE DATABASE [TestDatabasePartialRestore_FullFileGroupRestore]
FROM DISK = N'C:\Temp\TempDatabases_Tests\Backups\TestDatabasePartialRestore_MonthlyFullBackup.bak'
WITH FILE = 1,
MOVE N'TestDatabasePartialRestore' TO N'C:\Temp\TempDatabases_Tests\ActivePartitions\TestDatabasePartialRestore_FullFileGroupRestore.mdf'
,MOVE N'TestDatabasePartialRestore_ActivePrimary' TO N'C:\Temp\TempDatabases_Tests\ActivePartitions\TestDatabasePartialRestore_FullFileGroupRestore_ActivePrimary.ndf'
,MOVE N'TestDatabasePartialRestore_ArchiveData' TO N'C:\Temp\TempDatabases_Tests\ArchiveData\TestDatabasePartialRestore_FullFileGroupRestore_ArchiveData.ndf'
,MOVE N'TestDatabasePartialRestore_log' TO N'C:\Temp\TempDatabases_Tests\ActivePartitions\TestDatabasePartialRestore_FullFileGroupRestore_log.ldf'
,NORECOVERY
RESTORE DATABASE TestDatabasePartialRestore_FullFileGroupRestore
FROM DISK = 'C:\Temp\TempDatabases_Tests\Backups\TestDatabasePartialRestore_ActivePrimary_Full.fbak' with recovery
Running the code above, will restore the fileGroup backup BUT - it still needs the transaction log backups (all of them) before I can bring the database online...
Why?
What am I missing here?
April 10, 2015 at 4:46 am
Wandrag (4/10/2015)
BUT - it still needs the transaction log backups (all of them) before I can bring the database online...Why?
What am I missing here?
To bring the filegroups up to the same restore point.
Filegroup A may have been backed up 30 mins before Filegroup B 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 10, 2015 at 6:01 am
Taking filegroup backups and restoring them to a consistent point requires log backups so that the DB can be brought to a consistent point in time. It's so that any transactions which were done between the time when filegroup 1's backup was done and when filegroup 2's backup was done can be replayed.
When doing filegroup backups, the DB has to be in full recovery and there must be an unbroken chain of log backups from the earliest backup to the most recent (or to the point to which you are restoring the DB)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 10, 2015 at 7:00 am
GilaMonster (4/10/2015)
Taking filegroup backups and restoring them to a consistent point requires log backups so that the DB can be brought to a consistent point in time. It's so that any transactions which were done between the time when filegroup 1's backup was done and when filegroup 2's backup was done can be replayed.When doing filegroup backups, the DB has to be in full recovery and there must be an unbroken chain of log backups from the earliest backup to the most recent (or to the point to which you are restoring the DB)
Hi,
So if I want to restore up to the point of where TestDatabasePartialRestore_ActivePrimary_Full was taken, I need to restore all the log file backups from TestDatabasePartialRestore_MonthlyFullBackup up to TestDatabasePartialRestorelog 4?
April 10, 2015 at 7:20 am
Yes, they need to be restored as the last step in the restore process.
Another option is to make the archive filegroup readonly before you take the backup, but then you have to be *very* careful about when backups are taken and what state that filegroup is in at the time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 14, 2015 at 1:09 pm
I should already know this answer, but I guess due to the consistency property of ACID, there is no way to just restore 1 filegroup? Say, I have all my lookup tables on a file group, and then someone truncates all of those tables. I can't just restore that filegroup backup? This didn't happen, i'm just researching backup/restore options.
October 15, 2015 at 3:05 am
No. You'd have to restore the filegroup, then all log backups since the filegroup backup, including the one that contains the truncate table statements. You can't restore part of a database to an earlier point in time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2015 at 7:38 am
thanks. just confirming. someone seems to think this is feasible.
October 15, 2015 at 7:50 am
nawillia (10/15/2015)
thanks. just confirming. someone seems to think this is feasible.
Ask them to show you how to do it. :Whistling:
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2015 at 7:52 am
i would but i need my job, lol.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply