What good is a filegroup (or a read_write_filegroup) backup?  
Author Message
Brney





PostPosted: Fri Feb 09 01:57:17 CST 2007 Top

SQL Server >> What good is a filegroup (or a read_write_filegroup) backup?

I have a VLDB (2 TB) that I'm trying some new backup/restore scenarios on. I
can split it up into 5 filegroups where 1 filegroup is read-write and the
other 4 are read-only.

I have set up my backups so that I backup the read-only filegroups only once
and then my read-write filegroup nightly.

Further investigation shows that I can only restore these backups to the
exact database that they were taken from. What happens if I have a
catastrophic event and lose the server? I can't recreate the database and
then load it from my filegroup backups! What good is that?!

Am I missing something here?

Thanks in advance!

SQL Server87  
 
 
Uri





PostPosted: Fri Feb 09 01:57:17 CST 2007 Top

SQL Server >> What good is a filegroup (or a read_write_filegroup) backup? apf
I can't recreate the database and
> then load it from my filegroup backups! What good is that?!

no, but you will have FULL backup of the database. In case of colapse you do
restore FULL database and then FILEGROUP






>I have a VLDB (2 TB) that I'm trying some new backup/restore scenarios on.
>I
> can split it up into 5 filegroups where 1 filegroup is read-write and the
> other 4 are read-only.
>
> I have set up my backups so that I backup the read-only filegroups only
> once
> and then my read-write filegroup nightly.
>
> Further investigation shows that I can only restore these backups to the
> exact database that they were taken from. What happens if I have a
> catastrophic event and lose the server? I can't recreate the database and
> then load it from my filegroup backups! What good is that?!
>
> Am I missing something here?
>
> Thanks in advance!


 
 
apf





PostPosted: Fri Feb 09 07:11:00 CST 2007 Top

SQL Server >> What good is a filegroup (or a read_write_filegroup) backup? I see. I will try that - thanks for your help!
 
 
Tibor





PostPosted: Fri Feb 09 07:32:01 CST 2007 Top

SQL Server >> What good is a filegroup (or a read_write_filegroup) backup? I just tried your scenario and it work just fine for me. I restore the (recent) read/write backup.
Then I restore the older backup of the read only filegroup. Perhaps you missed the PARTIAL option?
Script (will drop databases x and x2 if exists):

USE master
GO
IF DB_ID('x') IS NOT NULL DROP DATABASE x
GO
IF DB_ID('x2') IS NOT NULL DROP DATABASE x2
GO
CREATE DATABASE [x] ON PRIMARY
( NAME = N'x', FILENAME = N'C:\x.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ),
FILEGROUP [STUFF]
( NAME = N'x_stuff', FILENAME = N'C:\x_stuff.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'x_log', FILENAME = N'C:\x_log.ldf' , SIZE = 3072KB , FILEGROWTH = 10%)
GO

CREATE TABLE x..t(c1 int) ON "PRIMARY"
INSERT INTO x..t VALUES(1)
CREATE TABLE x..t_stuff(c1 int) ON STUFF
INSERT INTO x..t_stuff VALUES(1)
GO
ALTER DATABASE x MODIFY FILEGROUP STUFF READONLY
GO
BACKUP DATABASE x FILEGROUP = 'STUFF' TO DISK = 'C:\x_fg_STUFF.BAK' WITH INIT
GO
INSERT INTO x..t VALUES(2)
GO
BACKUP DATABASE x READ_WRITE_FILEGROUPS TO DISK = 'C:\x_fg_rw.BAK' WITH INIT
GO

--Restore the primary (read/write) filegroup
RESTORE DATABASE x2 FROM DISK = 'C:\x_fg_rw.BAK'
WITH
PARTIAL
,MOVE 'x' TO 'C:\x2.mdf'
,MOVE 'x_stuff' TO 'C:\x2_stuff.mdf'
,MOVE 'x_log' TO 'C:\x2_log.mdf'
,REPLACE

EXEC sp_helpdb 'x2'

SELECT * FROM x2..t --OK
GO
SELECT * FROM x2..t_stuff --Fails
GO

--Restore the other filegroup
RESTORE DATABASE x2 FROM DISK = 'C:\x_fg_STUFF.BAK'
WITH
MOVE 'x' TO 'C:\x2.mdf'
,MOVE 'x_stuff' TO 'C:\x2_stuff.mdf'
,MOVE 'x_log' TO 'C:\x2_log.mdf'

SELECT * FROM x2..t --OK
GO
SELECT * FROM x2..t_stuff --Fails
GO



--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi




>I have a VLDB (2 TB) that I'm trying some new backup/restore scenarios on. I
> can split it up into 5 filegroups where 1 filegroup is read-write and the
> other 4 are read-only.
>
> I have set up my backups so that I backup the read-only filegroups only once
> and then my read-write filegroup nightly.
>
> Further investigation shows that I can only restore these backups to the
> exact database that they were taken from. What happens if I have a
> catastrophic event and lose the server? I can't recreate the database and
> then load it from my filegroup backups! What good is that?!
>
> Am I missing something here?
>
> Thanks in advance!

 
 
Uri





PostPosted: Sun Feb 11 04:18:54 CST 2007 Top

SQL Server >> What good is a filegroup (or a read_write_filegroup) backup? Tibor
> --Restore the other filegroup
> RESTORE DATABASE x2 FROM DISK = 'C:\x_fg_STUFF.BAK'
> WITH
> MOVE 'x' TO 'C:\x2.mdf'
> ,MOVE 'x_stuff' TO 'C:\x2_stuff.mdf'
> ,MOVE 'x_log' TO 'C:\x2_log.mdf'
>
> SELECT * FROM x2..t --OK
> GO
> SELECT * FROM x2..t_stuff --Fails
> GO

Should be OK,right?

SELECT * FROM x2..t_stuff --OK



>I just tried your scenario and it work just fine for me. I restore the
>(recent) read/write backup. Then I restore the older backup of the read
>only filegroup. Perhaps you missed the PARTIAL option?
> Script (will drop databases x and x2 if exists):
>
> USE master
> GO
> IF DB_ID('x') IS NOT NULL DROP DATABASE x
> GO
> IF DB_ID('x2') IS NOT NULL DROP DATABASE x2
> GO
> CREATE DATABASE [x] ON PRIMARY
> ( NAME = N'x', FILENAME = N'C:\x.mdf' , SIZE = 3072KB , FILEGROWTH =
> 1024KB ),
> FILEGROUP [STUFF]
> ( NAME = N'x_stuff', FILENAME = N'C:\x_stuff.ndf' , SIZE = 3072KB ,
> FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'x_log', FILENAME = N'C:\x_log.ldf' , SIZE = 3072KB , FILEGROWTH
> = 10%)
> GO
>
> CREATE TABLE x..t(c1 int) ON "PRIMARY"
> INSERT INTO x..t VALUES(1)
> CREATE TABLE x..t_stuff(c1 int) ON STUFF
> INSERT INTO x..t_stuff VALUES(1)
> GO
> ALTER DATABASE x MODIFY FILEGROUP STUFF READONLY
> GO
> BACKUP DATABASE x FILEGROUP = 'STUFF' TO DISK = 'C:\x_fg_STUFF.BAK' WITH
> INIT
> GO
> INSERT INTO x..t VALUES(2)
> GO
> BACKUP DATABASE x READ_WRITE_FILEGROUPS TO DISK = 'C:\x_fg_rw.BAK' WITH
> INIT
> GO
>
> --Restore the primary (read/write) filegroup
> RESTORE DATABASE x2 FROM DISK = 'C:\x_fg_rw.BAK'
> WITH
> PARTIAL
> ,MOVE 'x' TO 'C:\x2.mdf'
> ,MOVE 'x_stuff' TO 'C:\x2_stuff.mdf'
> ,MOVE 'x_log' TO 'C:\x2_log.mdf'
> ,REPLACE
>
> EXEC sp_helpdb 'x2'
>
> SELECT * FROM x2..t --OK
> GO
> SELECT * FROM x2..t_stuff --Fails
> GO
>
> --Restore the other filegroup
> RESTORE DATABASE x2 FROM DISK = 'C:\x_fg_STUFF.BAK'
> WITH
> MOVE 'x' TO 'C:\x2.mdf'
> ,MOVE 'x_stuff' TO 'C:\x2_stuff.mdf'
> ,MOVE 'x_log' TO 'C:\x2_log.mdf'
>
> SELECT * FROM x2..t --OK
> GO
> SELECT * FROM x2..t_stuff --Fails
> GO
>
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>


>>I have a VLDB (2 TB) that I'm trying some new backup/restore scenarios on.
>>I
>> can split it up into 5 filegroups where 1 filegroup is read-write and the
>> other 4 are read-only.
>>
>> I have set up my backups so that I backup the read-only filegroups only
>> once
>> and then my read-write filegroup nightly.
>>
>> Further investigation shows that I can only restore these backups to the
>> exact database that they were taken from. What happens if I have a
>> catastrophic event and lose the server? I can't recreate the database
>> and
>> then load it from my filegroup backups! What good is that?!
>>
>> Am I missing something here?
>>
>> Thanks in advance!
>


 
 
Tibor





PostPosted: Sun Feb 11 04:23:45 CST 2007 Top

SQL Server >> What good is a filegroup (or a read_write_filegroup) backup? Thanks Uri. Yes, the very last SELECT from t_stuff does not fail. :-)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



> Tibor
>> --Restore the other filegroup
>> RESTORE DATABASE x2 FROM DISK = 'C:\x_fg_STUFF.BAK'
>> WITH
>> MOVE 'x' TO 'C:\x2.mdf'
>> ,MOVE 'x_stuff' TO 'C:\x2_stuff.mdf'
>> ,MOVE 'x_log' TO 'C:\x2_log.mdf'
>>
>> SELECT * FROM x2..t --OK
>> GO
>> SELECT * FROM x2..t_stuff --Fails
>> GO
>
> Should be OK,right?
>
> SELECT * FROM x2..t_stuff --OK
>


>>I just tried your scenario and it work just fine for me. I restore the
>>(recent) read/write backup. Then I restore the older backup of the read
>>only filegroup. Perhaps you missed the PARTIAL option?
>> Script (will drop databases x and x2 if exists):
>>
>> USE master
>> GO
>> IF DB_ID('x') IS NOT NULL DROP DATABASE x
>> GO
>> IF DB_ID('x2') IS NOT NULL DROP DATABASE x2
>> GO
>> CREATE DATABASE [x] ON PRIMARY
>> ( NAME = N'x', FILENAME = N'C:\x.mdf' , SIZE = 3072KB , FILEGROWTH =
>> 1024KB ),
>> FILEGROUP [STUFF]
>> ( NAME = N'x_stuff', FILENAME = N'C:\x_stuff.ndf' , SIZE = 3072KB ,
>> FILEGROWTH = 1024KB )
>> LOG ON
>> ( NAME = N'x_log', FILENAME = N'C:\x_log.ldf' , SIZE = 3072KB , FILEGROWTH
>> = 10%)
>> GO
>>
>> CREATE TABLE x..t(c1 int) ON "PRIMARY"
>> INSERT INTO x..t VALUES(1)
>> CREATE TABLE x..t_stuff(c1 int) ON STUFF
>> INSERT INTO x..t_stuff VALUES(1)
>> GO
>> ALTER DATABASE x MODIFY FILEGROUP STUFF READONLY
>> GO
>> BACKUP DATABASE x FILEGROUP = 'STUFF' TO DISK = 'C:\x_fg_STUFF.BAK' WITH
>> INIT
>> GO
>> INSERT INTO x..t VALUES(2)
>> GO
>> BACKUP DATABASE x READ_WRITE_FILEGROUPS TO DISK = 'C:\x_fg_rw.BAK' WITH
>> INIT
>> GO
>>
>> --Restore the primary (read/write) filegroup
>> RESTORE DATABASE x2 FROM DISK = 'C:\x_fg_rw.BAK'
>> WITH
>> PARTIAL
>> ,MOVE 'x' TO 'C:\x2.mdf'
>> ,MOVE 'x_stuff' TO 'C:\x2_stuff.mdf'
>> ,MOVE 'x_log' TO 'C:\x2_log.mdf'
>> ,REPLACE
>>
>> EXEC sp_helpdb 'x2'
>>
>> SELECT * FROM x2..t --OK
>> GO
>> SELECT * FROM x2..t_stuff --Fails
>> GO
>>
>> --Restore the other filegroup
>> RESTORE DATABASE x2 FROM DISK = 'C:\x_fg_STUFF.BAK'
>> WITH
>> MOVE 'x' TO 'C:\x2.mdf'
>> ,MOVE 'x_stuff' TO 'C:\x2_stuff.mdf'
>> ,MOVE 'x_log' TO 'C:\x2_log.mdf'
>>
>> SELECT * FROM x2..t --OK
>> GO
>> SELECT * FROM x2..t_stuff --Fails
>> GO
>>
>>
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>>


>>>I have a VLDB (2 TB) that I'm trying some new backup/restore scenarios on.
>>>I
>>> can split it up into 5 filegroups where 1 filegroup is read-write and the
>>> other 4 are read-only.
>>>
>>> I have set up my backups so that I backup the read-only filegroups only
>>> once
>>> and then my read-write filegroup nightly.
>>>
>>> Further investigation shows that I can only restore these backups to the
>>> exact database that they were taken from. What happens if I have a
>>> catastrophic event and lose the server? I can't recreate the database
>>> and
>>> then load it from my filegroup backups! What good is that?!
>>>
>>> Am I missing something here?
>>>
>>> Thanks in advance!
>>
>
>