help with restore filegroup

hi !
I need help with restore a filegroup. Below are steps
which I made, what is wrong '
Steps:
- create a new filegroup "SE" and assign to it a few
tables. - OK
- make a backup new created filegroup "SE" - OK
- make a backup log file - OK
- drop one table "A1" from filegroup "SE" - OK
- make a backup log file (necessary to restore)- OK
- make a restore filegroup "SE" and log files - OK
my question is why after restore filegroup I haven't
droppped table "A1" '
I readed a documentation on microsoft - I think that all
is done ok.
Maybe I missing some details or something else '
thank for any answer, and sorry for my english The table A! should not be there after the file group restore. Are you
saying that it is there? If so, can you please post the CREATE DATABASE,
CREATE TABLE, ALTER DATABASE, BACKUP and RESTORE statements with which we
can reproduce this? It is always easier to talk about these things when
having statements available instead of guessing what you are doing (and
possibly how you are clicking etc in Enterprise Manager).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"rafal" <anonymous@.discussions.microsoft.com> wrote in message
news:198701c410c8$0e2a66e0$3501280a@.phx.gbl...
> hi !
> I need help with restore a filegroup. Below are steps
> which I made, what is wrong '
> Steps:
> - create a new filegroup "SE" and assign to it a few
> tables. - OK
> - make a backup new created filegroup "SE" - OK
> - make a backup log file - OK
> - drop one table "A1" from filegroup "SE" - OK
> - make a backup log file (necessary to restore)- OK
> - make a restore filegroup "SE" and log files - OK
> my question is why after restore filegroup I haven't
> droppped table "A1" '
> I readed a documentation on microsoft - I think that all
> is done ok.
> Maybe I missing some details or something else '
> thank for any answer, and sorry for my english |||hi again
thanks for answer Tibor,
Where is my problem ? - I have a database where are some
tables. A few from them grow up much more than rest. My
problem is to backup often tables where is bigger increase
of rows. Then I created a group and assigned to it these
tables with big increase of rows.
What I want to do ? - I want to backup only one group
which I created.
Here is the example code that I execute in QueryAnalyzer.
All of them are executed without errors but result is not
satisfactory
Steps:
1. create database:
CREATE DATABASE TEST
ON PRIMARY
( NAME = TEST_dat,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\testDat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
FILEGROUP TestGroup1
( NAME = TESTGROUP_dat,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\TestGroupDat.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'TEST_log',
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\TestLog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
2. use database
use test
3. create tables in database
first
create table A1 (
id int,
name char(50),
age int
)
ON 'TestGroup1'
second
create table A2(
id int,
field char(50)
)
ON 'PRIMARY'
4. insert example data
insert a2 values (1,'third')
insert a1 values (1,'Czesiek',12)
insert a1 values (2,'Wieseik',23)
insert a1 values (3,'Misiek',42)
5. backup interesting group
backup database test filegroup = 'testgroup1'
to disk = 'c:\testdb\gr1.bak'
6.backup log
backup log test
to disk ='c:\testdb\testlog.log'
7. delete data on table a1 (simulate losing data)
delete from a1 where age > 12
8. backup log (needed to restore filegroup)
backup log test
to disk = 'c:\testdb\beforRestore.log'
9. restore group
restore database test
file = 'testgroup_dat',
filegroup ='testgroup1'
from disk = 'c:\testdb\gr1.bak'
10. restore log
restore log test
from disk = 'c:\testdb\testlog.log'
with norecovery
11.restore last log
restore log test
from disk = 'c:\testdb\beforrestore.log'
with recovery
Is it done in a good order or not ?
Why after restore group and all log files I haven't
deleted in step 7 data ?
any suggestion ?
where is bug ?
thanks, rafal|||I'm not 100% certain on what you want to achieve, but I guess that you want
to undo the deletions. However,
you cannot use filegroup backup for this. When you do a restore of a partial
backup (file or filegroup), you
have to restore all log backups taken after that point in time, including th
at last one. No point in time, as
SQL server wouldn't know how to synchronize the work done against the filegr
oup (which you do not restore in
full) to the other part of the database. This is all documented in Books Onl
ine. If you want to communicate
this further, please use my below script. I have revised it a bit, so you do
n't have to create directories,
and also so you can execute it all in one go.
DROP DATABASE TEST
GO
CREATE DATABASE TEST
ON PRIMARY
( NAME = TEST_dat,
FILENAME = 'c:\testDat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
FILEGROUP TestGroup1
( NAME = TESTGROUP_dat,
FILENAME = 'c:\TestGroupDat.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'TEST_log',
FILENAME = 'c:\TestLog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
--2. use database
USE test
--3. create tables in databasefirst
create table A1 ( id int, name char(50), age int ) ON 'TestGroup1'
--second
create table A2( id int, field char(50) ) ON 'PRIMARY'
--4. insert example data
insert a2 values (1,'third')
insert a1 values (1,'Czesiek',12)
insert a1 values (2,'Wieseik',23)
insert a1 values (3,'Misiek',42)
--5. backup interesting group
backup database test filegroup = 'testgroup1' to disk = 'c:\gr1.bak' WITH IN
IT
--backup database test to disk = 'c:\gr1.bak' WITH INIT
--6.backup log
backup log test to disk ='c:\testlog.log' WITH INIT
--7. delete data on table a1 (simulate losing data)
BEGIN TRAN x WITH MARK 'x'
delete from a1 where age > 12
COMMIT TRAN
--8. backup log (needed to restore filegroup)
backup log test to disk = 'c:\beforRestore.log' WITH INIT
--8.5 use master
USE master
--9. restore group
restore database test filegroup ='testgroup1' from disk = 'c:\gr1.bak'
--restore database test file = 'testgroup_dat', filegroup ='testgroup1' fro
m disk = 'c:\gr1.bak'
--restore database test from disk = 'c:\gr1.bak' with norecovery
--10. restore log
restore log test from disk = 'c:\testlog.log' with norecovery
--11.restore last log
restore log test from disk = 'c:\beforrestore.log' with recovery
, STOPBEFOREMARK = 'x'
GO
--Is the data still there?
SELECT * FROM test..a1
SELECT * FROM test..a2
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"rafal" <anonymous@.discussions.microsoft.com> wrote in message news:1c4501c411a5$12e74940$3
a01280a@.phx.gbl...
> hi again
> thanks for answer Tibor,
> Where is my problem ? - I have a database where are some
> tables. A few from them grow up much more than rest. My
> problem is to backup often tables where is bigger increase
> of rows. Then I created a group and assigned to it these
> tables with big increase of rows.
> What I want to do ? - I want to backup only one group
> which I created.
> Here is the example code that I execute in QueryAnalyzer.
> All of them are executed without errors but result is not
> satisfactory
> Steps:
> 1. create database:
> CREATE DATABASE TEST
> ON PRIMARY
> ( NAME = TEST_dat,
> FILENAME = 'c:\program files\microsoft sql
> server\mssql\data\testDat.mdf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 15% ),
> FILEGROUP TestGroup1
> ( NAME = TESTGROUP_dat,
> FILENAME = 'c:\program files\microsoft sql
> server\mssql\data\TestGroupDat.ndf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 5 )
> LOG ON
> ( NAME = 'TEST_log',
> FILENAME = 'c:\program files\microsoft sql
> server\mssql\data\TestLog.ldf',
> SIZE = 5MB,
> MAXSIZE = 25MB,
> FILEGROWTH = 5MB )
> 2. use database
> use test
> 3. create tables in database
> first
> create table A1 (
> id int,
> name char(50),
> age int
> )
> ON 'TestGroup1'
> second
> create table A2(
> id int,
> field char(50)
> )
> ON 'PRIMARY'
> 4. insert example data
> insert a2 values (1,'third')
> insert a1 values (1,'Czesiek',12)
> insert a1 values (2,'Wieseik',23)
> insert a1 values (3,'Misiek',42)
> 5. backup interesting group
> backup database test filegroup = 'testgroup1'
> to disk = 'c:\testdb\gr1.bak'
> 6.backup log
> backup log test
> to disk ='c:\testdb\testlog.log'
> 7. delete data on table a1 (simulate losing data)
> delete from a1 where age > 12
> 8. backup log (needed to restore filegroup)
> backup log test
> to disk = 'c:\testdb\beforRestore.log'
> 9. restore group
> restore database test
> file = 'testgroup_dat',
> filegroup ='testgroup1'
> from disk = 'c:\testdb\gr1.bak'
> 10. restore log
> restore log test
> from disk = 'c:\testdb\testlog.log'
> with norecovery
> 11.restore last log
> restore log test
> from disk = 'c:\testdb\beforrestore.log'
> with recovery
>
> Is it done in a good order or not ?
> Why after restore group and all log files I haven't
> deleted in step 7 data ?
> any suggestion ?
> where is bug ?
> thanks, rafal