Relationships between tables in different SQL databases

There are several databases (currently in Access) that are being moved to SQL Server. Would prefer keeping those databases separate in SQL Server. How do you do something similar to Access's "link" capabilities and relate tables in different physical databases? For example, relate the authors of a document in a document database to persons in a people database where AuthorID in Docs.DocAuthors.AuthorID is related to People.Persons.PersonID

It is normal in SQL Server to combine all of the data for an application into a single database.

SQL Server does not have the size limitations of Access.

You can keep security separate (if that is your need) with the use of Schemas. Refer to Books Online, Topics:

Schema

User-Schema Separation

|||

Found out the SQL doesn't support cross-database foreign keys. Using schemas to separate related tables requires extra typing when programming and requires tedious program updates if a table changes schema. Will stick to keeping all tables in one database and under the default "dbo" schema.

Not sure if SQL has a similar "dbLink" API similar to Oracle's "dbLink" to SQL. Still looking for equivalent "dbLink" in SQL.

|||Refer to Books Online, Topic: 'Linked Servers'

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

re-install Windows, how to restore databases?

I have SQL Server 2000 running on Windows 2000 Professional, and need
to upgrade this to Windows 2000 Server. In SQL, I have 64 separate
databases used for client development work. After upgrading Windows,
and re-installing SQL Server, how can I reconnect all of the existing
databases without too much work? I'd rather not have to backup, add,
and restore each one individually.How about scripting it. Write some TSQL using a cursor to loop sysdatabases and backup each
databases using dynamic SQL. Then use a similar way to restore based on the files in that directory.
For the restore part, you can use some of the code I have at:
http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<rgurganus@.citidc.com> wrote in message
news:1124991326.785503.210230@.f14g2000cwb.googlegroups.com...
>I have SQL Server 2000 running on Windows 2000 Professional, and need
> to upgrade this to Windows 2000 Server. In SQL, I have 64 separate
> databases used for client development work. After upgrading Windows,
> and re-installing SQL Server, how can I reconnect all of the existing
> databases without too much work? I'd rather not have to backup, add,
> and restore each one individually.
>|||Hi,
You can detach the databases and Copy both Data and log files.
After reinstalling win2000 you can attach it back
regards,
--
Herbert
"rgurganus@.citidc.com" wrote:
> I have SQL Server 2000 running on Windows 2000 Professional, and need
> to upgrade this to Windows 2000 Server. In SQL, I have 64 separate
> databases used for client development work. After upgrading Windows,
> and re-installing SQL Server, how can I reconnect all of the existing
> databases without too much work? I'd rather not have to backup, add,
> and restore each one individually.
>|||Yes, but with 64+ different databases, I was hoping to avoid detatching
each one, and re-attaching each one. I'm coming out of a MySQL
background, where this would be a piece of cake. You'd just copy the
whole data directory where all the database files are, and restart the
service. Oh well...|||Did you read my post?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<rgurganus@.citidc.com> wrote in message news:1125068039.740731.98460@.g49g2000cwa.googlegroups.com...
> Yes, but with 64+ different databases, I was hoping to avoid detatching
> each one, and re-attaching each one. I'm coming out of a MySQL
> background, where this would be a piece of cake. You'd just copy the
> whole data directory where all the database files are, and restart the
> service. Oh well...
>|||Yes, thanks. That may help. I'm just giving into dislike and bashing
of MS SQL in general. Systems should make things easier, not more
difficult. :(