backup/restore files to a point-in-time

I am trying to understand sql server backup and restore procedures. The
documentation seems to indicate that it is possible to restore one or more
files up to a point in time without restoring the entire database if you
first restore the backups of the individual files and then restore each tran
log since the backup up to the point of recovery.
I tried to do this with the script below but I always seemed to be forced to
restore the primary file in addition to the other ones. When I didn't
restore the primary data file, the final restore log statement gives me the
following error: "The log in this backup set cannot be applied because it is
on a recovery path inconsistent with the database."
Is it possible to only restore the secondary files up to a point in time
without restoring the primary? If so, could someone help me understand what
I am doing wrong?
To reproduce what I am doing below, run the script first up to the select
getdate() line (around line 157). Then take the datetime and put it in the
stopat parameter at the end of the script and then run everything after the
select getdate() line.
Many thanks in advance.
---
use master
go
/*setup a test to understand backup/restore process in SQL Server 2000
1. Create a database (bk) with two filegroups (primary, fg2)
fg2 has two data files (bk_df1, bk_df2)
2. Create two identical tables (bk1, bk2) on primary and fg2 respectively
3. Before any real activity occurs, take an immediate backup of the primary
data file
4. Insert a row in each table
5. Backup the first data file in the fg2 filegroup (bk_df1)
6. Insert a row in each table
7. Backup the tran log (c:\backup_log1)
8. Insert a row in each table
9. Backup the tran log again (c:\backup_log2)
10. Insert a row in each table
11. Backup the tran log again (c:\backup_log3)
12. Insert a row in each table
13. Mark the time after this last insert. This is the point to which I
want to restore.
14. Drop the table bk2 (on fg2)
15. Start the restore process by doing the following
a. backup the active tran log
b. truncate the log using the with truncate_only option (is there a way
out of this step?)
c. restore each fg2 data file --> This doesn't work! It only works if I
first restore
the primary data file as well.
d. restore each tran log backup
e. use the recovery, stopat options on the last tran log restore
*/
IF (SELECT db_ID('bk')) IS NOT NULL
BEGIN
DROP database bk
PRINT 'DROPPED bk database'
END
go
create database bk
on primary
(name = 'bk_primary_data',
filename = 'c:\bk_primary_data.mdf',
maxsize=1000,
size=2,
filegrowth=10%
),
filegroup FG2
(name= 'bk_DF1',
filename = 'c:\bk_DF1.ndf',
size = 2,
maxsize = 1000 ,
filegrowth = 10%),
(name = 'bk_DF2',
filename= 'c:\bk_DF2.ndf',
size = 2,
maxsize = 1000,
filegrowth=10%)
log on
(name='bk_log',
filename= 'c:\bk_log.ldf',
size = 1,
maxsize = 'UNLIMITED',
filegrowth = 10%
)
GO
use bk
go
/*I know that nobody creates these kinds of tables, but I am just testing*/
create table bk1(
uid uniqueidentifier rowguidcol default newid() primary key clustered,
timestamp,
tx varchar(512)
) on [primary]
create table bk2(
uid uniqueidentifier rowguidcol default newid() primary key clustered,
timestamp,
tx varchar(512)
) on fg2
go
backup database bk file = 'bk_primary_data' to disk = 'c:\backup_primary'
with init
go
/*insert a row into each table (thus, each filegroup)*/
declare @.tx varchar(512)
select @.tx = 'insert test 1'
insert into bk1(tx)
values( @.tx )
insert into bk2( tx )
values( @.tx )
go
backup database bk file='bk_df1' to disk='c:\backup_df1' with init
go
declare @.tx varchar(512)
select @.tx = 'insert test 2'
insert into bk1(tx)
values( @.tx )
insert into bk2( tx )
values( @.tx )
go
backup log bk to disk='c:\backup_log1' with init
go
declare @.tx varchar(512)
select @.tx = 'insert test 3'
insert into bk1(tx)
values( @.tx )
insert into bk2( tx )
values( @.tx )
go
backup database bk file='bk_df2' to disk='c:\backup_df2' with init
go
declare @.tx varchar(512)
select @.tx = 'insert test 4'
insert into bk1(tx)
values( @.tx )
insert into bk2( tx )
values( @.tx )
go
backup log bk to disk='c:\backup_log2' with init
go
declare @.tx varchar(512)
select @.tx = 'insert test 5'
insert into bk1(tx)
values( @.tx )
insert into bk2( tx )
values( @.tx )
go
backup log bk to disk='c:\backup_log3' with init
go
declare @.tx varchar(512)
select @.tx = 'insert test 6'
insert into bk1(tx)
values( @.tx )
insert into bk2( tx )
values( @.tx )
go
select * from bk2
go
select getdate() /*this is the point in time
where I want to restore, see below*/
--Take value from select getdate()
-- and put it in the stopat parameter
-- at the end of this script
-- come back to this line and run the remaining statements
go
drop table bk2
/*oops, didn't want to drop that table, start the restore process*/
use master
go
backup log bk to disk = 'c:\backup_log4'
backup log bk with truncate_only
go
use master
go
/* if I don't restore the bk_primary_data file, I receive an error. But the
documentation
indicates that I should only need to restore the data files from the
secondary filegroup
to restore the data
bk2 only exists on the fg2 filegroup,
how does one restore files up to a point-in-time without also restoring all
other
(unaffected) files?
*/
restore database bk file = 'bk_primary_data' from
disk='c:\backup_primary'with norecovery
restore database bk file = 'bk_df1'from disk='c:\backup_df1'with norecovery,
file=1
restore database bk file = 'bk_df2'from disk='c:\backup_df2'with norecovery,
file=1
restore log bk from disk = 'c:\backup_log1'with norecovery
restore log bk from disk = 'c:\backup_log2'with norecovery
restore log bk from disk = 'c:\backup_log3'with norecovery
/*recover right before the drop table*/
restore log bk from disk = 'c:\backup_log4'with recovery, stopat= 'xxx'
--'see select getdate() above (line 157)
go
use bk
select * from bk1
select * from bk2Hi
With SQL Server 2000, it is an all or nothing for ther file groups. Primary
and secondary need to be restored before a transaction log can be applied.
Regards
Mike
"bkt" wrote:
> I am trying to understand sql server backup and restore procedures. The
> documentation seems to indicate that it is possible to restore one or more
> files up to a point in time without restoring the entire database if you
> first restore the backups of the individual files and then restore each tran
> log since the backup up to the point of recovery.
> I tried to do this with the script below but I always seemed to be forced to
> restore the primary file in addition to the other ones. When I didn't
> restore the primary data file, the final restore log statement gives me the
> following error: "The log in this backup set cannot be applied because it is
> on a recovery path inconsistent with the database."
> Is it possible to only restore the secondary files up to a point in time
> without restoring the primary? If so, could someone help me understand what
> I am doing wrong?
> To reproduce what I am doing below, run the script first up to the select
> getdate() line (around line 157). Then take the datetime and put it in the
> stopat parameter at the end of the script and then run everything after the
> select getdate() line.
> Many thanks in advance.
> ---
> use master
> go
> /*setup a test to understand backup/restore process in SQL Server 2000
> 1. Create a database (bk) with two filegroups (primary, fg2)
> fg2 has two data files (bk_df1, bk_df2)
> 2. Create two identical tables (bk1, bk2) on primary and fg2 respectively
> 3. Before any real activity occurs, take an immediate backup of the primary
> data file
> 4. Insert a row in each table
> 5. Backup the first data file in the fg2 filegroup (bk_df1)
> 6. Insert a row in each table
> 7. Backup the tran log (c:\backup_log1)
> 8. Insert a row in each table
> 9. Backup the tran log again (c:\backup_log2)
> 10. Insert a row in each table
> 11. Backup the tran log again (c:\backup_log3)
> 12. Insert a row in each table
> 13. Mark the time after this last insert. This is the point to which I
> want to restore.
> 14. Drop the table bk2 (on fg2)
> 15. Start the restore process by doing the following
> a. backup the active tran log
> b. truncate the log using the with truncate_only option (is there a way
> out of this step?)
> c. restore each fg2 data file --> This doesn't work! It only works if I
> first restore
> the primary data file as well.
> d. restore each tran log backup
> e. use the recovery, stopat options on the last tran log restore
> */
> IF (SELECT db_ID('bk')) IS NOT NULL
> BEGIN
> DROP database bk
> PRINT 'DROPPED bk database'
> END
> go
> create database bk
> on primary
> (name = 'bk_primary_data',
> filename = 'c:\bk_primary_data.mdf',
> maxsize=1000,
> size=2,
> filegrowth=10%
> ),
> filegroup FG2
> (name= 'bk_DF1',
> filename = 'c:\bk_DF1.ndf',
> size = 2,
> maxsize = 1000 ,
> filegrowth = 10%),
> (name = 'bk_DF2',
> filename= 'c:\bk_DF2.ndf',
> size = 2,
> maxsize = 1000,
> filegrowth=10%)
> log on
> (name='bk_log',
> filename= 'c:\bk_log.ldf',
> size = 1,
> maxsize = 'UNLIMITED',
> filegrowth = 10%
> )
> GO
> use bk
> go
> /*I know that nobody creates these kinds of tables, but I am just testing*/
> create table bk1(
> uid uniqueidentifier rowguidcol default newid() primary key clustered,
> timestamp,
> tx varchar(512)
> ) on [primary]
>
> create table bk2(
> uid uniqueidentifier rowguidcol default newid() primary key clustered,
> timestamp,
> tx varchar(512)
> ) on fg2
> go
> backup database bk file = 'bk_primary_data' to disk = 'c:\backup_primary'
> with init
> go
> /*insert a row into each table (thus, each filegroup)*/
> declare @.tx varchar(512)
> select @.tx = 'insert test 1'
> insert into bk1(tx)
> values( @.tx )
> insert into bk2( tx )
> values( @.tx )
> go
> backup database bk file='bk_df1' to disk='c:\backup_df1' with init
> go
> declare @.tx varchar(512)
> select @.tx = 'insert test 2'
> insert into bk1(tx)
> values( @.tx )
> insert into bk2( tx )
> values( @.tx )
> go
> backup log bk to disk='c:\backup_log1' with init
> go
> declare @.tx varchar(512)
> select @.tx = 'insert test 3'
> insert into bk1(tx)
> values( @.tx )
> insert into bk2( tx )
> values( @.tx )
> go
> backup database bk file='bk_df2' to disk='c:\backup_df2' with init
> go
> declare @.tx varchar(512)
> select @.tx = 'insert test 4'
> insert into bk1(tx)
> values( @.tx )
> insert into bk2( tx )
> values( @.tx )
> go
> backup log bk to disk='c:\backup_log2' with init
> go
> declare @.tx varchar(512)
> select @.tx = 'insert test 5'
> insert into bk1(tx)
> values( @.tx )
> insert into bk2( tx )
> values( @.tx )
> go
> backup log bk to disk='c:\backup_log3' with init
> go
> declare @.tx varchar(512)
> select @.tx = 'insert test 6'
> insert into bk1(tx)
> values( @.tx )
> insert into bk2( tx )
> values( @.tx )
> go
> select * from bk2
> go
> select getdate() /*this is the point in time
> where I want to restore, see below*/
> --Take value from select getdate()
> -- and put it in the stopat parameter
> -- at the end of this script
> -- come back to this line and run the remaining statements
> go
> drop table bk2
> /*oops, didn't want to drop that table, start the restore process*/
> use master
> go
> backup log bk to disk = 'c:\backup_log4'
> backup log bk with truncate_only
> go
> use master
> go
> /* if I don't restore the bk_primary_data file, I receive an error. But the
> documentation
> indicates that I should only need to restore the data files from the
> secondary filegroup
> to restore the data
> bk2 only exists on the fg2 filegroup,
> how does one restore files up to a point-in-time without also restoring all
> other
> (unaffected) files?
> */
> restore database bk file = 'bk_primary_data' from
> disk='c:\backup_primary'with norecovery
> restore database bk file = 'bk_df1'from disk='c:\backup_df1'with norecovery,
> file=1
> restore database bk file = 'bk_df2'from disk='c:\backup_df2'with norecovery,
> file=1
> restore log bk from disk = 'c:\backup_log1'with norecovery
> restore log bk from disk = 'c:\backup_log2'with norecovery
> restore log bk from disk = 'c:\backup_log3'with norecovery
> /*recover right before the drop table*/
> restore log bk from disk = 'c:\backup_log4'with recovery, stopat= 'xxx'
> --'see select getdate() above (line 157)
> go
> use bk
> select * from bk1
> select * from bk2
>