Relate two databases

I am developing an application which consists of two separate products
that both use SQL Server 2000 to store data (database A and database
B). In the application logic we define relations between objects in
these products.
Is it possible to ensure that when restoring a database from a backup,
the other database is automatically restored at the same time to keep
data consistent between the two databases?
What I want to avoid is having a DBA restoring one database and
forgetting the other, thereby creating inconsistencies.
Kind regards,
Martin
Hi,
You can write a script to restore both the databases in order. So instead of
doing a manual restore you could just run the
restore script which loads both the database.
Some thing like:-
Alter database <db1> set single_user with rollback immediate
go
Restore database <db1> from disk='c:\backup\file_name.bak'
go
Alter database <db1> set Multi_user
go
Alter database <db2> set single_user with rollback immediate
go
Restore database <db2> from disk='c:\backup\file_name.bak'
go
Alter database <db2> set Multi_user
Thanks
Hari
MCDBA
"Martin Perfelian" <zonk99@.hotmail.com> wrote in message
news:b9c2729.0408110037.738ea8c2@.posting.google.co m...
> I am developing an application which consists of two separate products
> that both use SQL Server 2000 to store data (database A and database
> B). In the application logic we define relations between objects in
> these products.
> Is it possible to ensure that when restoring a database from a backup,
> the other database is automatically restored at the same time to keep
> data consistent between the two databases?
> What I want to avoid is having a DBA restoring one database and
> forgetting the other, thereby creating inconsistencies.
> Kind regards,
> Martin
|||Even tho Hari describes the correct method, you may still have problems...
Databases are backed up independently of one another. You must use the logs
and probably, roll both forward to the same point in time, otherwise you may
still get mis-matches
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Martin Perfelian" <zonk99@.hotmail.com> wrote in message
news:b9c2729.0408110037.738ea8c2@.posting.google.co m...
> I am developing an application which consists of two separate products
> that both use SQL Server 2000 to store data (database A and database
> B). In the application logic we define relations between objects in
> these products.
> Is it possible to ensure that when restoring a database from a backup,
> the other database is automatically restored at the same time to keep
> data consistent between the two databases?
> What I want to avoid is having a DBA restoring one database and
> forgetting the other, thereby creating inconsistencies.
> Kind regards,
> Martin

Relate two databases

I am developing an application which consists of two separate products
that both use SQL Server 2000 to store data (database A and database
B). In the application logic we define relations between objects in
these products.
Is it possible to ensure that when restoring a database from a backup,
the other database is automatically restored at the same time to keep
data consistent between the two databases?
What I want to avoid is having a DBA restoring one database and
forgetting the other, thereby creating inconsistencies.
Kind regards,
MartinHi,
You can write a script to restore both the databases in order. So instead of
doing a manual restore you could just run the
restore script which loads both the database.
Some thing like:-
Alter database <db1> set single_user with rollback immediate
go
Restore database <db1> from disk='c:\backup\file_name.bak'
go
Alter database <db1> set Multi_user
go
Alter database <db2> set single_user with rollback immediate
go
Restore database <db2> from disk='c:\backup\file_name.bak'
go
Alter database <db2> set Multi_user
Thanks
Hari
MCDBA
"Martin Perfelian" <zonk99@.hotmail.com> wrote in message
news:b9c2729.0408110037.738ea8c2@.posting.google.com...
> I am developing an application which consists of two separate products
> that both use SQL Server 2000 to store data (database A and database
> B). In the application logic we define relations between objects in
> these products.
> Is it possible to ensure that when restoring a database from a backup,
> the other database is automatically restored at the same time to keep
> data consistent between the two databases?
> What I want to avoid is having a DBA restoring one database and
> forgetting the other, thereby creating inconsistencies.
> Kind regards,
> Martin|||Even tho Hari describes the correct method, you may still have problems...
Databases are backed up independently of one another. You must use the logs
and probably, roll both forward to the same point in time, otherwise you may
still get mis-matches
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Martin Perfelian" <zonk99@.hotmail.com> wrote in message
news:b9c2729.0408110037.738ea8c2@.posting.google.com...
> I am developing an application which consists of two separate products
> that both use SQL Server 2000 to store data (database A and database
> B). In the application logic we define relations between objects in
> these products.
> Is it possible to ensure that when restoring a database from a backup,
> the other database is automatically restored at the same time to keep
> data consistent between the two databases?
> What I want to avoid is having a DBA restoring one database and
> forgetting the other, thereby creating inconsistencies.
> Kind regards,
> Martinsql

Relate two databases

I am developing an application which consists of two separate products
that both use SQL Server 2000 to store data (database A and database
B). In the application logic we define relations between objects in
these products.
Is it possible to ensure that when restoring a database from a backup,
the other database is automatically restored at the same time to keep
data consistent between the two databases?
What I want to avoid is having a DBA restoring one database and
forgetting the other, thereby creating inconsistencies.
Kind regards,
MartinHi,
You can write a script to restore both the databases in order. So instead of
doing a manual restore you could just run the
restore script which loads both the database.
Some thing like:-
Alter database <db1> set single_user with rollback immediate
go
Restore database <db1> from disk='c:\backup\file_name.bak'
go
Alter database <db1> set Multi_user
go
Alter database <db2> set single_user with rollback immediate
go
Restore database <db2> from disk='c:\backup\file_name.bak'
go
Alter database <db2> set Multi_user
Thanks
Hari
MCDBA
"Martin Perfelian" <zonk99@.hotmail.com> wrote in message
news:b9c2729.0408110037.738ea8c2@.posting.google.com...
> I am developing an application which consists of two separate products
> that both use SQL Server 2000 to store data (database A and database
> B). In the application logic we define relations between objects in
> these products.
> Is it possible to ensure that when restoring a database from a backup,
> the other database is automatically restored at the same time to keep
> data consistent between the two databases?
> What I want to avoid is having a DBA restoring one database and
> forgetting the other, thereby creating inconsistencies.
> Kind regards,
> Martin|||Even tho Hari describes the correct method, you may still have problems...
Databases are backed up independently of one another. You must use the logs
and probably, roll both forward to the same point in time, otherwise you may
still get mis-matches
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Martin Perfelian" <zonk99@.hotmail.com> wrote in message
news:b9c2729.0408110037.738ea8c2@.posting.google.com...
> I am developing an application which consists of two separate products
> that both use SQL Server 2000 to store data (database A and database
> B). In the application logic we define relations between objects in
> these products.
> Is it possible to ensure that when restoring a database from a backup,
> the other database is automatically restored at the same time to keep
> data consistent between the two databases?
> What I want to avoid is having a DBA restoring one database and
> forgetting the other, thereby creating inconsistencies.
> Kind regards,
> Martin

regular backups

Hi,
Is it possible to make regular back ups of a database in such fashion that
it is a bit like a version control system.. where you store differences
(incremental?) but in such way that backups are made daily or weekly and you
can decide to restore a backup from 20 days ago?
In other words, a 'history' of backups should be kept so I can go back 5
backup sessions ago and restore something that's 5 days old. If this is
possible, how can I pick the 'date' from which to backup from?
Using SQL Server 8/2000
Lisa
Lisa,
have a look at creating a maintenance plan. You can select to keep x backups
made at whatever frequency you require and it'll round-robin the files. The
backups have a filename indicating their backup time. SQL Server doesn't do
incremental backups (not even in SQL 2005 AFAIR), but does differential and
transaction log backups which are covered in books-on-line (BOL) and might
be what you require.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Registry to store configuration values ?

I try to store a SSIS-configuration value in the registry.

But it's failed. I get a warning, that the registry-key is not found.

What is the syntax ? I have searched in BO,MSDN and Google/Internet ==> nothing.

There is no example.

The syntax of how to specify the name of the registry key is straightforward. See BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/212e52e7-27a7-48bb-8bad-34aa290d2003.htm

If you're asking how to define the syntax of a configuration (i.e. The thing that should be IN the registry key) then that's a bit more difficult. A quick scan of BOl didn't bring anything up. The way I always do it is to create an XML COnfiguration file from within SSIS Designer and then copy and paste the resultant configuration into the registry key.

Hope that halps.

-Jamie

|||

Hey Jamie,

there is no example:

Configuration Type Option = Registry Entry
Specify configuration settings directly
Use to specify settings directly.

Value Description
Registry entry
Type the registry key that contains the configuration information.

I think it is very easy, but I don't know how to type the registry key!

With regedit I have defined a new entry:

HKEY_LOCAL_MACHINE\SOFTWARE\SSIS\Var

Var is a string and contains for example the servername.

What is the syntax in the SSIS-Configuration-Wizard?

You create an XML-Configuration file then you copy and paste the result into the registry key?

The registry key can store only one proberty. The XML-Configuration can store many proberties. You have an example?

Thanks

Loom

|||

It sounds to me as though you have done everything that you need to do. What error are you getting when you try and usee it?

-Jamie

|||

In the german version I get this warning:

Warnung: 0x80012015 bei Package: Der Konfigurationsregistrierungsschlüssel 'HKEY_LOCAL_MACHINE\SOFTWARE\SSIS\Var' wurde nicht gefunden. Für einen Konfigurationseintrag ist ein nicht verfügbarer Registrierungsschlüssel angegeben. überprüfen Sie die Registrierung, und stellen Sie sicher, dass der Schlüssel vorhanden ist.

Translation:

The Configuration-Registry-Key 'HKEY_LOCAL_MACHINE\SOFTWARE\SSIS\Var' was not found.

Loom

|||

The key must be defined in HKCU.

The Value must be stored in the Value key.

HKCU\Configuration\FirstConfig\Value

HKCU\Configuration\SecondConfig\Value

The ConfigString is Configuration\FirstConfig , this is the string you put into the Configuration String field in the Configuration Wizard.

The Registry Configuration assumes the HKCU and Value sections. Everything in between can be whatever you want.

HTH,

K

|||

Kirk,

Is this information in BOL anywhere? If it isn't, it should be.

-Jamie

|||Nah, don't think so. I couldn't find it anywhere...|||

Bingo!

I have tried everything, but not the value key....

To store the configuration in the registry has an advantage compared with an environment-variable. A change in the registry automaticly change your configuration.

A change of an environment-variable has no effect in the BI-Studio. The only way to refresh the variable is: Close the BI-Studio and then reopen it.

And this is a problem for a developer. Environment-variables are not the first choice for switching between Dev / Test / Pro - Servers.

Or I have missed something?

Loom

|||

You're right that you need to shut down BIDS for the EV Config to take effect, but, why does that matter? You can simply change the value of the configured property as though the configuration had been applied.

Again, it's a preference thing. That's why there are several options.

Environment Variables are the only option for Indirect Configurations.

K

|||

There is another option for Indirect Configuration: The SQL-Server option.

The SQL-Server use a normal connection-manager and you can configure the proberties of this connection-manager.

In the FirstConfig you can change the ServerName and the InitialCatalog of the connection-manager.

In the SecondConfig you use the SQL-Server-Option with the changed ServerName/InitialCatalog.

In my opinion it is an indirect configuration too.

Loom

|||

Yep, got that right. Very flexible, yes.

K

|||

Loom wrote:

Bingo!

I have tried everything, but not the value key....

To store the configuration in the registry has an advantage compared with an environment-variable. A change in the registry automaticly change your configuration.

A change of an environment-variable has no effect in the BI-Studio. The only way to refresh the variable is: Close the BI-Studio and then reopen it.

And this is a problem for a developer. Environment-variables are not the first choice for switching between Dev / Test / Pro - Servers.

Or I have missed something?

Loom

Its a matter of opinion isn't it? there isn't one right answer. Personally I think the issue about having to close BIDS isn't really a problem as long as you know about the issue.

I use indirect configurations exclusively. That's just my choice.

-Jamie

|||

I try to use this option to change the "conenction string" property of a Flat file connection, but nothing happens.

What is the correct format of registry Key string to fill the box?

I try :

HKEY_LOCAL_MACHINE\SOFTWARE\ProjectName\Connections\ConnectionString1

and

[HKEY_LOCAL_MACHINE\SOFTWARE\ProjectName\Connections\ConnectionString1]

and

"HKEY_LOCAL_MACHINE\SOFTWARE\ProjectName\Connections\ConnectionString1"

The value Key "ConnectionString1" is a String Value that contains the address of file like: C:\input files\Test.csv

Don't Work!!!! What′s wrong?

Thanks

|||

In the registry you have this entry:

HKEY_LOCAL_MACHINE\SOFTWARE\ProjectName\Connections\ConnectionString1\value

In Value you store your connection string

In the SSIS-Configuration the reference is:

SOFTWARE\ProjectName\Connections\ConnectionString1

Loom

Registry to store configuration values ?

I try to store a SSIS-configuration value in the registry.

But it's failed. I get a warning, that the registry-key is not found.

What is the syntax ? I have searched in BO,MSDN and Google/Internet ==> nothing.

There is no example.

The syntax of how to specify the name of the registry key is straightforward. See BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/212e52e7-27a7-48bb-8bad-34aa290d2003.htm

If you're asking how to define the syntax of a configuration (i.e. The thing that should be IN the registry key) then that's a bit more difficult. A quick scan of BOl didn't bring anything up. The way I always do it is to create an XML COnfiguration file from within SSIS Designer and then copy and paste the resultant configuration into the registry key.

Hope that halps.

-Jamie

|||

Hey Jamie,

there is no example:

Configuration Type Option = Registry Entry
Specify configuration settings directly
Use to specify settings directly.

Value Description
Registry entry
Type the registry key that contains the configuration information.

I think it is very easy, but I don't know how to type the registry key!

With regedit I have defined a new entry:

HKEY_LOCAL_MACHINE\SOFTWARE\SSIS\Var

Var is a string and contains for example the servername.

What is the syntax in the SSIS-Configuration-Wizard?

You create an XML-Configuration file then you copy and paste the result into the registry key?

The registry key can store only one proberty. The XML-Configuration can store many proberties. You have an example?

Thanks

Loom

|||

It sounds to me as though you have done everything that you need to do. What error are you getting when you try and usee it?

-Jamie

|||

In the german version I get this warning:

Warnung: 0x80012015 bei Package: Der Konfigurationsregistrierungsschlüssel 'HKEY_LOCAL_MACHINE\SOFTWARE\SSIS\Var' wurde nicht gefunden. Für einen Konfigurationseintrag ist ein nicht verfügbarer Registrierungsschlüssel angegeben. überprüfen Sie die Registrierung, und stellen Sie sicher, dass der Schlüssel vorhanden ist.

Translation:

The Configuration-Registry-Key 'HKEY_LOCAL_MACHINE\SOFTWARE\SSIS\Var' was not found.

Loom

|||

The key must be defined in HKCU.

The Value must be stored in the Value key.

HKCU\Configuration\FirstConfig\Value

HKCU\Configuration\SecondConfig\Value

The ConfigString is Configuration\FirstConfig , this is the string you put into the Configuration String field in the Configuration Wizard.

The Registry Configuration assumes the HKCU and Value sections. Everything in between can be whatever you want.

HTH,

K

|||

Kirk,

Is this information in BOL anywhere? If it isn't, it should be.

-Jamie

|||Nah, don't think so. I couldn't find it anywhere...|||

Bingo!

I have tried everything, but not the value key....

To store the configuration in the registry has an advantage compared with an environment-variable. A change in the registry automaticly change your configuration.

A change of an environment-variable has no effect in the BI-Studio. The only way to refresh the variable is: Close the BI-Studio and then reopen it.

And this is a problem for a developer. Environment-variables are not the first choice for switching between Dev / Test / Pro - Servers.

Or I have missed something?

Loom

|||

You're right that you need to shut down BIDS for the EV Config to take effect, but, why does that matter? You can simply change the value of the configured property as though the configuration had been applied.

Again, it's a preference thing. That's why there are several options.

Environment Variables are the only option for Indirect Configurations.

K

|||

There is another option for Indirect Configuration: The SQL-Server option.

The SQL-Server use a normal connection-manager and you can configure the proberties of this connection-manager.

In the FirstConfig you can change the ServerName and the InitialCatalog of the connection-manager.

In the SecondConfig you use the SQL-Server-Option with the changed ServerName/InitialCatalog.

In my opinion it is an indirect configuration too.

Loom

|||

Yep, got that right. Very flexible, yes.

K

|||

Loom wrote:

Bingo!

I have tried everything, but not the value key....

To store the configuration in the registry has an advantage compared with an environment-variable. A change in the registry automaticly change your configuration.

A change of an environment-variable has no effect in the BI-Studio. The only way to refresh the variable is: Close the BI-Studio and then reopen it.

And this is a problem for a developer. Environment-variables are not the first choice for switching between Dev / Test / Pro - Servers.

Or I have missed something?

Loom

Its a matter of opinion isn't it? there isn't one right answer. Personally I think the issue about having to close BIDS isn't really a problem as long as you know about the issue.

I use indirect configurations exclusively. That's just my choice.

-Jamie

|||

I try to use this option to change the "conenction string" property of a Flat file connection, but nothing happens.

What is the correct format of registry Key string to fill the box?

I try :

HKEY_LOCAL_MACHINE\SOFTWARE\ProjectName\Connections\ConnectionString1

and

[HKEY_LOCAL_MACHINE\SOFTWARE\ProjectName\Connections\ConnectionString1]

and

"HKEY_LOCAL_MACHINE\SOFTWARE\ProjectName\Connections\ConnectionString1"

The value Key "ConnectionString1" is a String Value that contains the address of file like: C:\input files\Test.csv

Don't Work!!!! What′s wrong?

Thanks

|||

In the registry you have this entry:

HKEY_LOCAL_MACHINE\SOFTWARE\ProjectName\Connections\ConnectionString1\value

In Value you store your connection string

In the SSIS-Configuration the reference is:

SOFTWARE\ProjectName\Connections\ConnectionString1

Loom

Registry to store configuration values ?

I try to store a SSIS-configuration value in the registry.

But it's failed. I get a warning, that the registry-key is not found.

What is the syntax ? I have searched in BO,MSDN and Google/Internet ==> nothing.

There is no example.

The syntax of how to specify the name of the registry key is straightforward. See BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/212e52e7-27a7-48bb-8bad-34aa290d2003.htm

If you're asking how to define the syntax of a configuration (i.e. The thing that should be IN the registry key) then that's a bit more difficult. A quick scan of BOl didn't bring anything up. The way I always do it is to create an XML COnfiguration file from within SSIS Designer and then copy and paste the resultant configuration into the registry key.

Hope that halps.

-Jamie

|||

Hey Jamie,

there is no example:

Configuration Type Option = Registry Entry
Specify configuration settings directly
Use to specify settings directly.

Value Description
Registry entry
Type the registry key that contains the configuration information.

I think it is very easy, but I don't know how to type the registry key!

With regedit I have defined a new entry:

HKEY_LOCAL_MACHINE\SOFTWARE\SSIS\Var

Var is a string and contains for example the servername.

What is the syntax in the SSIS-Configuration-Wizard?

You create an XML-Configuration file then you copy and paste the result into the registry key?

The registry key can store only one proberty. The XML-Configuration can store many proberties. You have an example?

Thanks

Loom

|||

It sounds to me as though you have done everything that you need to do. What error are you getting when you try and usee it?

-Jamie

|||

In the german version I get this warning:

Warnung: 0x80012015 bei Package: Der Konfigurationsregistrierungsschlüssel 'HKEY_LOCAL_MACHINE\SOFTWARE\SSIS\Var' wurde nicht gefunden. Für einen Konfigurationseintrag ist ein nicht verfügbarer Registrierungsschlüssel angegeben. überprüfen Sie die Registrierung, und stellen Sie sicher, dass der Schlüssel vorhanden ist.

Translation:

The Configuration-Registry-Key 'HKEY_LOCAL_MACHINE\SOFTWARE\SSIS\Var' was not found.

Loom

|||

The key must be defined in HKCU.

The Value must be stored in the Value key.

HKCU\Configuration\FirstConfig\Value

HKCU\Configuration\SecondConfig\Value

The ConfigString is Configuration\FirstConfig , this is the string you put into the Configuration String field in the Configuration Wizard.

The Registry Configuration assumes the HKCU and Value sections. Everything in between can be whatever you want.

HTH,

K

|||

Kirk,

Is this information in BOL anywhere? If it isn't, it should be.

-Jamie

|||Nah, don't think so. I couldn't find it anywhere...|||

Bingo!

I have tried everything, but not the value key....

To store the configuration in the registry has an advantage compared with an environment-variable. A change in the registry automaticly change your configuration.

A change of an environment-variable has no effect in the BI-Studio. The only way to refresh the variable is: Close the BI-Studio and then reopen it.

And this is a problem for a developer. Environment-variables are not the first choice for switching between Dev / Test / Pro - Servers.

Or I have missed something?

Loom

|||

You're right that you need to shut down BIDS for the EV Config to take effect, but, why does that matter? You can simply change the value of the configured property as though the configuration had been applied.

Again, it's a preference thing. That's why there are several options.

Environment Variables are the only option for Indirect Configurations.

K

|||

There is another option for Indirect Configuration: The SQL-Server option.

The SQL-Server use a normal connection-manager and you can configure the proberties of this connection-manager.

In the FirstConfig you can change the ServerName and the InitialCatalog of the connection-manager.

In the SecondConfig you use the SQL-Server-Option with the changed ServerName/InitialCatalog.

In my opinion it is an indirect configuration too.

Loom

|||

Yep, got that right. Very flexible, yes.

K

|||

Loom wrote:

Bingo!

I have tried everything, but not the value key....

To store the configuration in the registry has an advantage compared with an environment-variable. A change in the registry automaticly change your configuration.

A change of an environment-variable has no effect in the BI-Studio. The only way to refresh the variable is: Close the BI-Studio and then reopen it.

And this is a problem for a developer. Environment-variables are not the first choice for switching between Dev / Test / Pro - Servers.

Or I have missed something?

Loom

Its a matter of opinion isn't it? there isn't one right answer. Personally I think the issue about having to close BIDS isn't really a problem as long as you know about the issue.

I use indirect configurations exclusively. That's just my choice.

-Jamie

|||

I try to use this option to change the "conenction string" property of a Flat file connection, but nothing happens.

What is the correct format of registry Key string to fill the box?

I try :

HKEY_LOCAL_MACHINE\SOFTWARE\ProjectName\Connections\ConnectionString1

and

[HKEY_LOCAL_MACHINE\SOFTWARE\ProjectName\Connections\ConnectionString1]

and

"HKEY_LOCAL_MACHINE\SOFTWARE\ProjectName\Connections\ConnectionString1"

The value Key "ConnectionString1" is a String Value that contains the address of file like: C:\input files\Test.csv

Don't Work!!!! What′s wrong?

Thanks

|||

In the registry you have this entry:

HKEY_LOCAL_MACHINE\SOFTWARE\ProjectName\Connections\ConnectionString1\value

In Value you store your connection string

In the SSIS-Configuration the reference is:

SOFTWARE\ProjectName\Connections\ConnectionString1

Loom