Filegroup Restore

I need to start using filegroups, because i have a very large Database.
I want to use the primary for the system tables and to have on filegroup by
year.
Example:
Year 2002 tables goes to filegroup 2002
Year 2003 tables goes to filegroup 2003
Year 2004 tables goes to filegroup 2004
If we acidently delete records on the 2004 tables,
is posssible to only restore the filegroup 2004 and the last Log backup?It doesn't work that way. If you deleted the rows in that table, and have co
mmitted, filegroup
backup will not help you. This is because a filegroup restore work in the wa
y that you restore the
filegroup. Then all subsequent log backups until now. And you have already c
ommitted the delete...
However, you can restore an fg from a full backup into a new database (the d
esired user fg along
with the PRIMARY fg). You do this using the PARTIAL option of the RESTORE co
mmand. Then you can copy
the desired data to your production database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jos Paulo Coelho" <JosPauloCoelho@.discussions.microsoft.com> wrote in mess
age
news:09FE6709-6219-4B81-AFA5-AF4D54BC1C14@.microsoft.com...
>I need to start using filegroups, because i have a very large Database.
> I want to use the primary for the system tables and to have on filegroup b
y
> year.
> Example:
> Year 2002 tables goes to filegroup 2002
> Year 2003 tables goes to filegroup 2003
> Year 2004 tables goes to filegroup 2004
> If we acidently delete records on the 2004 tables,
> is posssible to only restore the filegroup 2004 and the last Log backup?
>|||Thanks for your reply.
I understant that i can't only restore 2004 fg.
My idea now is to have a backup of 2002 and 2003 on tape on a safeplace.
And during the Year, i will do backups of the 2004 fg and the log.
Then if in the middle of the year, i acidentely delete some records.
I will restore 2002, 2003 fg and the last 2004 and the last log.
This will work, right?|||Please re-read my earlier reply. You cannot go back in time for a part of th
e database using
filegroup backup/restore.IMO, your most viable option is what I mentioned ea
rlier and the PARTIAL
option of the RESTORE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jos Paulo Coelho" <JosPauloCoelho@.discussions.microsoft.com> wrote in mess
age
news:94F4FCAC-3E42-4391-9AD7-B9BA7F79896D@.microsoft.com...
> Thanks for your reply.
> I understant that i can't only restore 2004 fg.
> My idea now is to have a backup of 2002 and 2003 on tape on a safeplace.
> And during the Year, i will do backups of the 2004 fg and the log.
> Then if in the middle of the year, i acidentely delete some records.
> I will restore 2002, 2003 fg and the last 2004 and the last log.
> This will work, right?
>|||Thanks again for your reply, I'm on my own on this.
There is no clear documentation about this
But i have tested and it seams that it works.
Can you check this.
Here is the script:
USE MASTER
GO
CREATE DATABASE SALES
GO
ALTER DATABASE SALES ADD FILEGROUP F2002
ALTER DATABASE SALES ADD FILEGROUP F2003
ALTER DATABASE SALES ADD FILEGROUP F2004
GO
ALTER DATABASE SALES ADD FILE
(NAME='2002',
FILENAME='c:\2002.dat1')
TO FILEGROUP F2002
go
ALTER DATABASE SALES ADD FILE
(NAME='2003',
FILENAME='c:\2003.dat1')
TO FILEGROUP F2003
go
ALTER DATABASE SALES ADD FILE
(NAME='2004',
FILENAME='c:\2004.dat1')
TO FILEGROUP F2004
go
use SALES
CREATE TABLE T_2002 (id int) ON F2002
CREATE TABLE T_2003 (id int) ON F2003
CREATE TABLE T_2004 (id int) ON F2004
INSERT INTO T_2002 VALUES (1)
INSERT INTO T_2003 VALUES (1)
INSERT INTO T_2004 VALUES (1)
GO
--BACKUP FOR TAPES--
--
USE master
BACKUP DATABASE SALES
FILE = 'Sales',
FILEGROUP = 'PRIMARY'
TO disk ='C:\a\Sales_PRIMARY.bak'
BACKUP DATABASE SALES
FILE = '2002',
FILEGROUP = 'F2002'
TO disk ='C:\a\Sales_F2002.bak'
BACKUP DATABASE SALES
FILE = '2003',
FILEGROUP = 'F2003'
TO disk ='C:\a\Sales_F2003.bak'
--RECORDS OK--
--
INSERT INTO T_2004 VALUES (2)
INSERT INTO T_2004 VALUES (3)
INSERT INTO T_2004 VALUES (4)
BACKUP DATABASE SALES
FILE = '2004',
FILEGROUP = 'F2004'
TO disk ='C:\a\Sales_F2004.bak'
BACKUP LOG SALES TO disk = 'C:\a\Sales_log.log'
GO
--Acidental Delete--
--
use Sales
delete from T_2004
--TAIL LOG BACKUP
BACKUP LOG SALES TO disk = 'C:\a\Sales_log2.log' WITH NO_TRUNCATE
---
---
---
--MUST DO ALL THE RESTORE--
---
USE master
RESTORE DATABASE SALES
FILE = 'Sales',
FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\a\Sales_PRIMARY.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2002',
FILEGROUP = 'F2002'
FROM DISK = 'C:\a\Sales_F2002.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2003',
FILEGROUP = 'F2003'
FROM DISK = 'C:\a\Sales_F2003.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2004',
FILEGROUP = 'F2004'
FROM DISK = 'C:\a\Sales_F2004.bak'
WITH noRECOVERY
RESTORE LOG SALES FROM disk = 'C:\a\Sales_log.log' WITH RECOVERY|||Yes, your script work, but you did indeed restore all parts of the database
up to the point in time
prior to the accidental DELETE. I thought that you wanted to restore only a
part of the database,
the part where you did the accidental delete? Perhaps I misunderstood you.
I've modified your script slightly below, so you can run it several times wi
thout needing to clean
up. Please continue on my modified script if you want to elaborate further.
:-)
drop database sales
go
USE MASTER
GO
CREATE DATABASE SALES
GO
ALTER DATABASE SALES ADD FILEGROUP F2002
ALTER DATABASE SALES ADD FILEGROUP F2003
ALTER DATABASE SALES ADD FILEGROUP F2004
GO
ALTER DATABASE SALES ADD FILE
(NAME='2002',
FILENAME='c:\2002.dat1')
TO FILEGROUP F2002
go
ALTER DATABASE SALES ADD FILE
(NAME='2003',
FILENAME='c:\2003.dat1')
TO FILEGROUP F2003
go
ALTER DATABASE SALES ADD FILE
(NAME='2004',
FILENAME='c:\2004.dat1')
TO FILEGROUP F2004
go
CREATE TABLE Sales..T_2002 (id int) ON F2002
CREATE TABLE Sales..T_2003 (id int) ON F2003
CREATE TABLE Sales..T_2004 (id int) ON F2004
INSERT INTO Sales..T_2002 VALUES (1)
INSERT INTO Sales..T_2003 VALUES (1)
INSERT INTO Sales..T_2004 VALUES (1)
GO
--BACKUP FOR TAPES--
--
BACKUP DATABASE SALES
FILE = 'Sales',
FILEGROUP = 'PRIMARY'
TO disk ='C:\a\Sales_PRIMARY.bak'
WITH INIT
BACKUP DATABASE SALES
FILE = '2002',
FILEGROUP = 'F2002'
TO disk ='C:\a\Sales_F2002.bak'
WITH INIT
BACKUP DATABASE SALES
FILE = '2003',
FILEGROUP = 'F2003'
TO disk ='C:\a\Sales_F2003.bak'
WITH INIT
--RECORDS OK--
--
INSERT INTO sales..T_2004 VALUES (2)
INSERT INTO sales..T_2004 VALUES (3)
INSERT INTO sales..T_2004 VALUES (4)
select * from sales..T_2004
BACKUP DATABASE SALES
FILE = '2004',
FILEGROUP = 'F2004'
TO disk ='C:\a\Sales_F2004.bak'
WITH INIT
BACKUP LOG SALES TO disk = 'C:\a\Sales_log.log' WITH INIT
GO
--Acidental Delete--
--
delete from sales..T_2004
INSERT INTO sales..T_2003 VALUES (2)
--TAIL LOG BACKUP
BACKUP LOG SALES TO disk = 'C:\a\Sales_log2.log' WITH NO_TRUNCATE
---
---
---
--MUST DO ALL THE RESTORE--
---
RESTORE DATABASE SALES
FILE = 'Sales',
FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\a\Sales_PRIMARY.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2002',
FILEGROUP = 'F2002'
FROM DISK = 'C:\a\Sales_F2002.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2003',
FILEGROUP = 'F2003'
FROM DISK = 'C:\a\Sales_F2003.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2004',
FILEGROUP = 'F2004'
FROM DISK = 'C:\a\Sales_F2004.bak'
WITH noRECOVERY
RESTORE LOG SALES FROM disk = 'C:\a\Sales_log.log' WITH RECOVERY
SELECT * FROM sales..T_2004
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jos Paulo Coelho" <JosPauloCoelho@.discussions.microsoft.com> wrote in mess
age
news:16AC95DA-C148-4CF9-A338-D9C2F11F0B8B@.microsoft.com...
> Thanks again for your reply, I'm on my own on this.
> There is no clear documentation about this
> But i have tested and it seams that it works.
> Can you check this.
> Here is the script:
> USE MASTER
> GO
> CREATE DATABASE SALES
> GO
> ALTER DATABASE SALES ADD FILEGROUP F2002
> ALTER DATABASE SALES ADD FILEGROUP F2003
> ALTER DATABASE SALES ADD FILEGROUP F2004
> GO
> ALTER DATABASE SALES ADD FILE
> (NAME='2002',
> FILENAME='c:\2002.dat1')
> TO FILEGROUP F2002
> go
> ALTER DATABASE SALES ADD FILE
> (NAME='2003',
> FILENAME='c:\2003.dat1')
> TO FILEGROUP F2003
> go
> ALTER DATABASE SALES ADD FILE
> (NAME='2004',
> FILENAME='c:\2004.dat1')
> TO FILEGROUP F2004
> go
> use SALES
> CREATE TABLE T_2002 (id int) ON F2002
> CREATE TABLE T_2003 (id int) ON F2003
> CREATE TABLE T_2004 (id int) ON F2004
> INSERT INTO T_2002 VALUES (1)
> INSERT INTO T_2003 VALUES (1)
> INSERT INTO T_2004 VALUES (1)
> GO
> --
> --BACKUP FOR TAPES--
> --
> USE master
> BACKUP DATABASE SALES
> FILE = 'Sales',
> FILEGROUP = 'PRIMARY'
> TO disk ='C:\a\Sales_PRIMARY.bak'
> BACKUP DATABASE SALES
> FILE = '2002',
> FILEGROUP = 'F2002'
> TO disk ='C:\a\Sales_F2002.bak'
> BACKUP DATABASE SALES
> FILE = '2003',
> FILEGROUP = 'F2003'
> TO disk ='C:\a\Sales_F2003.bak'
> --
> --RECORDS OK--
> --
> INSERT INTO T_2004 VALUES (2)
> INSERT INTO T_2004 VALUES (3)
> INSERT INTO T_2004 VALUES (4)
> BACKUP DATABASE SALES
> FILE = '2004',
> FILEGROUP = 'F2004'
> TO disk ='C:\a\Sales_F2004.bak'
> BACKUP LOG SALES TO disk = 'C:\a\Sales_log.log'
> GO
> --
> --Acidental Delete--
> --
> use Sales
> delete from T_2004
> --TAIL LOG BACKUP
> BACKUP LOG SALES TO disk = 'C:\a\Sales_log2.log' WITH NO_TRUNCATE
> ---
> ---
> ---
> --MUST DO ALL THE RESTORE--
> ---
> USE master
> RESTORE DATABASE SALES
> FILE = 'Sales',
> FILEGROUP = 'PRIMARY'
> FROM DISK = 'C:\a\Sales_PRIMARY.bak'
> WITH noRECOVERY
> RESTORE DATABASE SALES
> FILE = '2002',
> FILEGROUP = 'F2002'
> FROM DISK = 'C:\a\Sales_F2002.bak'
> WITH noRECOVERY
> RESTORE DATABASE SALES
> FILE = '2003',
> FILEGROUP = 'F2003'
> FROM DISK = 'C:\a\Sales_F2003.bak'
> WITH noRECOVERY
> RESTORE DATABASE SALES
> FILE = '2004',
> FILEGROUP = 'F2004'
> FROM DISK = 'C:\a\Sales_F2004.bak'
> WITH noRECOVERY
> RESTORE LOG SALES FROM disk = 'C:\a\Sales_log.log' WITH RECOVERY
>
>|||Yes, on the bigining that was my idea.
But i saw that that was not possible.
At least with this script, i can do one backup of the past year and store it
on one tape on a safe place.
And daily just do a backup of the current year.
with this i will save time and disk space of doing full Backups.
This is possibles, right?
Can you point out some links regarding Filegroups Backups?
Thanks for your help.|||Well, with filegroups backup, you need to apply all subsequent transaction l
og backups after the
filegroup backup occurred. Say you do a fg backup Jan 1 2002. Then at Feb 23
2004 you want to
restore that filegroup backup. After restoring that filegroup backup, you ne
ed to restore all
transaction log backups you have dine since Jan 1 2002 until Feb 23 2004! Un
til all those
transaction log backups has been restored, the database is *not* available.
So, make sure that you
*really* test these scenarios well. What I've learned about filegroup backup
s, I have learned from
Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jos Paulo Coelho" <JosPauloCoelho@.discussions.microsoft.com> wrote in mess
age
news:86095755-1364-49F6-8450-51A26DC1DC56@.microsoft.com...
> Yes, on the bigining that was my idea.
> But i saw that that was not possible.
> At least with this script, i can do one backup of the past year and store
it
> on one tape on a safe place.
> And daily just do a backup of the current year.
> with this i will save time and disk space of doing full Backups.
> This is possibles, right?
> Can you point out some links regarding Filegroups Backups?
> Thanks for your help.
>|||I will pay attention to that.
Thanks for your help.