Backup/restore just stored procedures

How can I backup and restore just stored procedures. I know in the old
Enterprise Manager it was easy to script just all stored procedures so I
could do it that way but in the new Management Studio Stored Procedures are
now one of the object types that can be selected.
Any ways to get this done?
Regards,
Gary Blakely
You can script sps in SSMS as well. I would look again.
Andrew J. Kelly SQL MVP
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>
|||Hello,
In SSMS, expand the databases, right click above the database name..
Tasks -- Choose Generate SQL Script and follow based on the wizard.
Thanks
Hari
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>
|||Use F7 maybe when in the stored procedures folder? The right pain will then
be a selectable list of all sprocs that you can generate code for.
TheSQLGuru
President
Indicium Resources, Inc.
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>
|||F7. Yes. that's it.
Thanks
Gary
Regards,
Gary Blakely
Dean Blakely & Associates
www.deanblakely.com
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:OL5V$5CrHHA.4180@.TK2MSFTNGP04.phx.gbl...
> Use F7 maybe when in the stored procedures folder? The right pain will
> then be a selectable list of all sprocs that you can generate code for.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
>

Backup/restore just stored procedures

How can I backup and restore just stored procedures. I know in the old
Enterprise Manager it was easy to script just all stored procedures so I
could do it that way but in the new Management Studio Stored Procedures are
now one of the object types that can be selected.
Any ways to get this done?
Regards,
Gary BlakelyYou can script sps in SSMS as well. I would look again.
Andrew J. Kelly SQL MVP
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>|||Hello,
In SSMS, expand the databases, right click above the database name..
Tasks -- Choose Generate SQL Script and follow based on the wizard.
Thanks
Hari
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>|||Use F7 maybe when in the stored procedures folder? The right pain will then
be a selectable list of all sprocs that you can generate code for.
TheSQLGuru
President
Indicium Resources, Inc.
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>|||F7. Yes. that's it.
Thanks
Gary
Regards,
Gary Blakely
Dean Blakely & Associates
www.deanblakely.com
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:OL5V$5CrHHA.4180@.TK2MSFTNGP04.phx.gbl...
> Use F7 maybe when in the stored procedures folder? The right pain will
> then be a selectable list of all sprocs that you can generate code for.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
>

Backup/restore just stored procedures

How can I backup and restore just stored procedures. I know in the old
Enterprise Manager it was easy to script just all stored procedures so I
could do it that way but in the new Management Studio Stored Procedures are
now one of the object types that can be selected.
Any ways to get this done?
--
Regards,
Gary BlakelyYou can script sps in SSMS as well. I would look again.
--
Andrew J. Kelly SQL MVP
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>|||Hello,
In SSMS, expand the databases, right click above the database name..
Tasks -- Choose Generate SQL Script and follow based on the wizard.
Thanks
Hari
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>|||Use F7 maybe when in the stored procedures folder? The right pain will then
be a selectable list of all sprocs that you can generate code for.
--
TheSQLGuru
President
Indicium Resources, Inc.
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>|||F7. Yes. that's it.
Thanks
Gary
--
Regards,
Gary Blakely
Dean Blakely & Associates
www.deanblakely.com
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:OL5V$5CrHHA.4180@.TK2MSFTNGP04.phx.gbl...
> Use F7 maybe when in the stored procedures folder? The right pain will
> then be a selectable list of all sprocs that you can generate code for.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
>> How can I backup and restore just stored procedures. I know in the old
>> Enterprise Manager it was easy to script just all stored procedures so I
>> could do it that way but in the new Management Studio Stored Procedures
>> are now one of the object types that can be selected.
>> Any ways to get this done?
>> --
>> Regards,
>> Gary Blakely
>>
>

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 bk2
Hi
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
>

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 tr
an
> 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 th
e
> 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 wh
at
> 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 th
e
> stopat parameter at the end of the script and then run everything after th
e
> 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 prima
ry
> 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 t
he
> 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 a
ll
> 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 norecover
y,
> file=1
> restore database bk file = 'bk_df2'from disk='c:\backup_df2'with norecover
y,
> 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
>

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
>

Backup troubles

Hi,
I need some help with a logic for a important document I need to create. Its
regarding my Backup procedures that require me to test my database backups
every 3 months. The problem occurs that I do not have another server to test
out the database. So my solution was the following:
* Backup DB
* Restore DB redirected towards another DB
Now the problem is that QA doesnt think that is enough. I noticed that SQL
2005 (starting since SQL 2000) has this option which alot of 3rd party
Backup application use (my case backup exec 10d) "consistency check".
I am wondering if I set a consistency check at Backup and then at restore
with consistency check, could I absolutely prove that both databases are
EXACTLY the same.
Example using MD5 for comparing 2 files.
"Consistency check is the MD5 for the DB"
Thank you so much,
George S.
Hi
The restoration of the database will only prove that you can backup and
restore that backup i.e. the backup was not corrupted. It does not mean to
say that the restored database is the one that you backed up i.e. the backup
was not subsequently overwritten with a different backup. Therefore the issue
is one for protecting the backup and making sure it is not tampered with. A
checksum of the backup file may help, but then it doesn't mean that someone
has not tampered with where that is stored either!! If checking consistency
is the same as a DBCC CHECKDB it is not a comparison of the two databases.
HTH
John
"George Spiro" wrote:

> Hi,
> I need some help with a logic for a important document I need to create. Its
> regarding my Backup procedures that require me to test my database backups
> every 3 months. The problem occurs that I do not have another server to test
> out the database. So my solution was the following:
> * Backup DB
> * Restore DB redirected towards another DB
> Now the problem is that QA doesnt think that is enough. I noticed that SQL
> 2005 (starting since SQL 2000) has this option which alot of 3rd party
> Backup application use (my case backup exec 10d) "consistency check".
> I am wondering if I set a consistency check at Backup and then at restore
> with consistency check, could I absolutely prove that both databases are
> EXACTLY the same.
> Example using MD5 for comparing 2 files.
> "Consistency check is the MD5 for the DB"
> Thank you so much,
> George S.
>
>
|||So what exactly is the Check consistency option on SQL 2000 / 2005?
G.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6D092594-641C-46D3-92ED-78F3D0E13678@.microsoft.com...[vbcol=seagreen]
> Hi
> The restoration of the database will only prove that you can backup and
> restore that backup i.e. the backup was not corrupted. It does not mean to
> say that the restored database is the one that you backed up i.e. the
> backup
> was not subsequently overwritten with a different backup. Therefore the
> issue
> is one for protecting the backup and making sure it is not tampered with.
> A
> checksum of the backup file may help, but then it doesn't mean that
> someone
> has not tampered with where that is stored either!! If checking
> consistency
> is the same as a DBCC CHECKDB it is not a comparison of the two databases.
> HTH
> John
> "George Spiro" wrote:
|||Hi George
I don't use backup exec so I can not be sure, but the sales literature does
talk about performing DBCC commands, I don't think that it is anything that
will help you, although you may want to check with Symantic directly.
John
"George Spiro" wrote:

> So what exactly is the Check consistency option on SQL 2000 / 2005?
> G.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:6D092594-641C-46D3-92ED-78F3D0E13678@.microsoft.com...
>
>

Backup troubles

Hi,
I need some help with a logic for a important document I need to create. Its
regarding my Backup procedures that require me to test my database backups
every 3 months. The problem occurs that I do not have another server to test
out the database. So my solution was the following:
* Backup DB
* Restore DB redirected towards another DB
Now the problem is that QA doesnt think that is enough. I noticed that SQL
2005 (starting since SQL 2000) has this option which alot of 3rd party
Backup application use (my case backup exec 10d) "consistency check".
I am wondering if I set a consistency check at Backup and then at restore
with consistency check, could I absolutely prove that both databases are
EXACTLY the same.
Example using MD5 for comparing 2 files.
"Consistency check is the MD5 for the DB"
Thank you so much,
George S.Hi
The restoration of the database will only prove that you can backup and
restore that backup i.e. the backup was not corrupted. It does not mean to
say that the restored database is the one that you backed up i.e. the backup
was not subsequently overwritten with a different backup. Therefore the issue
is one for protecting the backup and making sure it is not tampered with. A
checksum of the backup file may help, but then it doesn't mean that someone
has not tampered with where that is stored either!! If checking consistency
is the same as a DBCC CHECKDB it is not a comparison of the two databases.
HTH
John
"George Spiro" wrote:
> Hi,
> I need some help with a logic for a important document I need to create. Its
> regarding my Backup procedures that require me to test my database backups
> every 3 months. The problem occurs that I do not have another server to test
> out the database. So my solution was the following:
> * Backup DB
> * Restore DB redirected towards another DB
> Now the problem is that QA doesnt think that is enough. I noticed that SQL
> 2005 (starting since SQL 2000) has this option which alot of 3rd party
> Backup application use (my case backup exec 10d) "consistency check".
> I am wondering if I set a consistency check at Backup and then at restore
> with consistency check, could I absolutely prove that both databases are
> EXACTLY the same.
> Example using MD5 for comparing 2 files.
> "Consistency check is the MD5 for the DB"
> Thank you so much,
> George S.
>
>|||So what exactly is the Check consistency option on SQL 2000 / 2005?
G.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6D092594-641C-46D3-92ED-78F3D0E13678@.microsoft.com...
> Hi
> The restoration of the database will only prove that you can backup and
> restore that backup i.e. the backup was not corrupted. It does not mean to
> say that the restored database is the one that you backed up i.e. the
> backup
> was not subsequently overwritten with a different backup. Therefore the
> issue
> is one for protecting the backup and making sure it is not tampered with.
> A
> checksum of the backup file may help, but then it doesn't mean that
> someone
> has not tampered with where that is stored either!! If checking
> consistency
> is the same as a DBCC CHECKDB it is not a comparison of the two databases.
> HTH
> John
> "George Spiro" wrote:
>> Hi,
>> I need some help with a logic for a important document I need to create.
>> Its
>> regarding my Backup procedures that require me to test my database
>> backups
>> every 3 months. The problem occurs that I do not have another server to
>> test
>> out the database. So my solution was the following:
>> * Backup DB
>> * Restore DB redirected towards another DB
>> Now the problem is that QA doesnt think that is enough. I noticed that
>> SQL
>> 2005 (starting since SQL 2000) has this option which alot of 3rd party
>> Backup application use (my case backup exec 10d) "consistency check".
>> I am wondering if I set a consistency check at Backup and then at restore
>> with consistency check, could I absolutely prove that both databases are
>> EXACTLY the same.
>> Example using MD5 for comparing 2 files.
>> "Consistency check is the MD5 for the DB"
>> Thank you so much,
>> George S.
>>|||Hi George
I don't use backup exec so I can not be sure, but the sales literature does
talk about performing DBCC commands, I don't think that it is anything that
will help you, although you may want to check with Symantic directly.
John
"George Spiro" wrote:
> So what exactly is the Check consistency option on SQL 2000 / 2005?
> G.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:6D092594-641C-46D3-92ED-78F3D0E13678@.microsoft.com...
> > Hi
> >
> > The restoration of the database will only prove that you can backup and
> > restore that backup i.e. the backup was not corrupted. It does not mean to
> > say that the restored database is the one that you backed up i.e. the
> > backup
> > was not subsequently overwritten with a different backup. Therefore the
> > issue
> > is one for protecting the backup and making sure it is not tampered with.
> > A
> > checksum of the backup file may help, but then it doesn't mean that
> > someone
> > has not tampered with where that is stored either!! If checking
> > consistency
> > is the same as a DBCC CHECKDB it is not a comparison of the two databases.
> >
> > HTH
> >
> > John
> >
> > "George Spiro" wrote:
> >
> >> Hi,
> >>
> >> I need some help with a logic for a important document I need to create.
> >> Its
> >> regarding my Backup procedures that require me to test my database
> >> backups
> >> every 3 months. The problem occurs that I do not have another server to
> >> test
> >> out the database. So my solution was the following:
> >>
> >> * Backup DB
> >> * Restore DB redirected towards another DB
> >>
> >> Now the problem is that QA doesnt think that is enough. I noticed that
> >> SQL
> >> 2005 (starting since SQL 2000) has this option which alot of 3rd party
> >> Backup application use (my case backup exec 10d) "consistency check".
> >>
> >> I am wondering if I set a consistency check at Backup and then at restore
> >> with consistency check, could I absolutely prove that both databases are
> >> EXACTLY the same.
> >>
> >> Example using MD5 for comparing 2 files.
> >> "Consistency check is the MD5 for the DB"
> >>
> >> Thank you so much,
> >>
> >> George S.
> >>
> >>
> >>
>
>

Backup troubles

Hi,
I need some help with a logic for a important document I need to create. Its
regarding my Backup procedures that require me to test my database backups
every 3 months. The problem occurs that I do not have another server to test
out the database. So my solution was the following:
* Backup DB
* Restore DB redirected towards another DB
Now the problem is that QA doesnt think that is enough. I noticed that SQL
2005 (starting since SQL 2000) has this option which alot of 3rd party
Backup application use (my case backup exec 10d) "consistency check".
I am wondering if I set a consistency check at Backup and then at restore
with consistency check, could I absolutely prove that both databases are
EXACTLY the same.
Example using MD5 for comparing 2 files.
"Consistency check is the MD5 for the DB"
Thank you so much,
George S.
Hi
The restoration of the database will only prove that you can backup and
restore that backup i.e. the backup was not corrupted. It does not mean to
say that the restored database is the one that you backed up i.e. the backup
was not subsequently overwritten with a different backup. Therefore the issue
is one for protecting the backup and making sure it is not tampered with. A
checksum of the backup file may help, but then it doesn't mean that someone
has not tampered with where that is stored either!! If checking consistency
is the same as a DBCC CHECKDB it is not a comparison of the two databases.
HTH
John
"George Spiro" wrote:

> Hi,
> I need some help with a logic for a important document I need to create. Its
> regarding my Backup procedures that require me to test my database backups
> every 3 months. The problem occurs that I do not have another server to test
> out the database. So my solution was the following:
> * Backup DB
> * Restore DB redirected towards another DB
> Now the problem is that QA doesnt think that is enough. I noticed that SQL
> 2005 (starting since SQL 2000) has this option which alot of 3rd party
> Backup application use (my case backup exec 10d) "consistency check".
> I am wondering if I set a consistency check at Backup and then at restore
> with consistency check, could I absolutely prove that both databases are
> EXACTLY the same.
> Example using MD5 for comparing 2 files.
> "Consistency check is the MD5 for the DB"
> Thank you so much,
> George S.
>
>
|||So what exactly is the Check consistency option on SQL 2000 / 2005?
G.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6D092594-641C-46D3-92ED-78F3D0E13678@.microsoft.com...[vbcol=seagreen]
> Hi
> The restoration of the database will only prove that you can backup and
> restore that backup i.e. the backup was not corrupted. It does not mean to
> say that the restored database is the one that you backed up i.e. the
> backup
> was not subsequently overwritten with a different backup. Therefore the
> issue
> is one for protecting the backup and making sure it is not tampered with.
> A
> checksum of the backup file may help, but then it doesn't mean that
> someone
> has not tampered with where that is stored either!! If checking
> consistency
> is the same as a DBCC CHECKDB it is not a comparison of the two databases.
> HTH
> John
> "George Spiro" wrote:
|||Hi George
I don't use backup exec so I can not be sure, but the sales literature does
talk about performing DBCC commands, I don't think that it is anything that
will help you, although you may want to check with Symantic directly.
John
"George Spiro" wrote:

> So what exactly is the Check consistency option on SQL 2000 / 2005?
> G.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:6D092594-641C-46D3-92ED-78F3D0E13678@.microsoft.com...
>
>

Backup troubles

Hi,
I need some help with a logic for a important document I need to create. Its
regarding my Backup procedures that require me to test my database backups
every 3 months. The problem occurs that I do not have another server to test
out the database. So my solution was the following:
* Backup DB
* Restore DB redirected towards another DB
Now the problem is that QA doesnt think that is enough. I noticed that SQL
2005 (starting since SQL 2000) has this option which alot of 3rd party
Backup application use (my case backup exec 10d) "consistency check".
I am wondering if I set a consistency check at Backup and then at restore
with consistency check, could I absolutely prove that both databases are
EXACTLY the same.
Example using MD5 for comparing 2 files.
"Consistency check is the MD5 for the DB"
Thank you so much,
George S.Hi
The restoration of the database will only prove that you can backup and
restore that backup i.e. the backup was not corrupted. It does not mean to
say that the restored database is the one that you backed up i.e. the backup
was not subsequently overwritten with a different backup. Therefore the issu
e
is one for protecting the backup and making sure it is not tampered with. A
checksum of the backup file may help, but then it doesn't mean that someone
has not tampered with where that is stored either!! If checking consistency
is the same as a DBCC CHECKDB it is not a comparison of the two databases.
HTH
John
"George Spiro" wrote:

> Hi,
> I need some help with a logic for a important document I need to create. I
ts
> regarding my Backup procedures that require me to test my database backups
> every 3 months. The problem occurs that I do not have another server to te
st
> out the database. So my solution was the following:
> * Backup DB
> * Restore DB redirected towards another DB
> Now the problem is that QA doesnt think that is enough. I noticed that SQL
> 2005 (starting since SQL 2000) has this option which alot of 3rd party
> Backup application use (my case backup exec 10d) "consistency check".
> I am wondering if I set a consistency check at Backup and then at restore
> with consistency check, could I absolutely prove that both databases are
> EXACTLY the same.
> Example using MD5 for comparing 2 files.
> "Consistency check is the MD5 for the DB"
> Thank you so much,
> George S.
>
>|||So what exactly is the Check consistency option on SQL 2000 / 2005?
G.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6D092594-641C-46D3-92ED-78F3D0E13678@.microsoft.com...[vbcol=seagreen]
> Hi
> The restoration of the database will only prove that you can backup and
> restore that backup i.e. the backup was not corrupted. It does not mean to
> say that the restored database is the one that you backed up i.e. the
> backup
> was not subsequently overwritten with a different backup. Therefore the
> issue
> is one for protecting the backup and making sure it is not tampered with.
> A
> checksum of the backup file may help, but then it doesn't mean that
> someone
> has not tampered with where that is stored either!! If checking
> consistency
> is the same as a DBCC CHECKDB it is not a comparison of the two databases.
> HTH
> John
> "George Spiro" wrote:
>|||Hi George
I don't use backup exec so I can not be sure, but the sales literature does
talk about performing DBCC commands, I don't think that it is anything that
will help you, although you may want to check with Symantic directly.
John
"George Spiro" wrote:

> So what exactly is the Check consistency option on SQL 2000 / 2005?
> G.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:6D092594-641C-46D3-92ED-78F3D0E13678@.microsoft.com...
>
>

backup to restore a blank database

Hi,
I want to backup the tables, stored procedures, and views from a database
without the data in tables. So, I can restore it to a different development
server.
What is the correct syntax on backup and restore for it?
Thanks.There's no TSQL command to backup only DDL. Use the scripting options instead. You didn't mention
version, so I assume 2005. Right-click the database, All Tasks, and from here you can get to the
scripting wizard. Also, there has been some improvements to the scripting in sp2.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:BD2FD877-52C4-4266-A4C4-F10B7E79801A@.microsoft.com...
> Hi,
> I want to backup the tables, stored procedures, and views from a database
> without the data in tables. So, I can restore it to a different development
> server.
> What is the correct syntax on backup and restore for it?
> Thanks.
>|||Thanks, Tibor.
Opps... :) I am trying to get an empty database from 2000 as well as 2005...
Thanks.|||Well, both Enterprise Manager as well as Management Studio has scripting options.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:9D7E379E-2505-4C24-B74C-2D98410B8336@.microsoft.com...
> Thanks, Tibor.
> Opps... :) I am trying to get an empty database from 2000 as well as 2005...
> Thanks.|||the script option will only do one at a time. :)
i wish to get all objects in one step, if tables or indexed views, i don't
want any data.|||Open the script wizard, this let you script several objects.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:0A62DB80-8A95-4256-826F-CF31F4D72ED7@.microsoft.com...
> the script option will only do one at a time. :)
> i wish to get all objects in one step, if tables or indexed views, i don't
> want any data.