Is it possible to merge Other SQL Database ".Bak" with Existing database without overwritting or replacing

SQL Server Administration
1

I Have Client Server and Main Server.

I would like to consolidate their generated sql database ".bak" in my main server but without overwritting or replacing my existing database. it's like just inserting data to my main server.

Topic : Restoring SQL Database ".bak"

2

Short answer: no.

Longer answer: restore their backup with a different name and treat it as a "staging" database, using it to populate your Main Server.

1 Like
3

So your telling me that 1 solution here is 1 server instead on having their own SQL DB then populate get their db?

And what do you mean by staging database? it is new to me. can you explain or any link?

4

I am assuming that "Client Server" and "Main Server" are two completely separate instances.

You want to get data from "Client Server" into "Main Server".

You have a backup of "Client Server" (CS.bak)

If you restore CS.bak over "Main Server" you will overwrite "Main Server", this is not a good thing.

My suggestions is restore CS.bak to a database with a name other than "Main Server (you "stage" the data in a separate database) to the same instance that contains "Main Server"

You then write insert/merge statements from "stage" to "Main Server" to consolidate the data.

1 Like
6

I Can't imagine how to insert when i separate database.

7

You can use three part naming to achieve this, for example you can run select statements against tables in other databases on the same instance (permissions allowing) by

SELECT * FROM databasename.schemaname.tablename

Just extend this to inserts

1 Like
8

Thank you! I will try this one.

9

One think, any suggestion for me. B'coz right now i developed system software for my company and it will deploy for 500 gasoline station. Any idea what should i do about their DB.

Do i need to install MS SQL Server each station or any possible way to lessen my issue about consolidating their data?

10

Is this for the same 1 company? Do they all have good connectivity to internet? It depends on many factors

1 Like
11

Yes for 1 company only.

What do you mean by internet connectivity? they will connect to our server which is my "Main Server" so that no issue of restoring and duplicating? Am i right of what you mean?

12

So this software tou wrote for them does not sound like a web application but a client side app.
2 options

  1. Local database, where would this be installed? At the gas station? You would need some way of identifying the store by storing storeId to differentiate one stores data from the other when processing backup file. This approach of backup and restore sounds very fragile and too complicated. What version of sql server, how will you manage licensing etc? What backup plans do you have?
  2. Centralized database at main office or on the cloud.
    This would be the better option but it depends on how good internet they have for the app to connect to database centralized.
1 Like
13

TBH I don't have any background on using Local Database.

But 1 think im thinking right now is connecting to our main office server. In short 1 database for all 500 gas station.

But how? by using remote desktop.

Can you help me for better option?

14

No by using a connection string that points to the main server. If you wrote it as a web application then they just go pseudogas.com

15

Can you give me example of what your're trying to say having internet then pointing the connection string to the main server? Sorry for late reply i'm been busy this past few days its so hard to understand this kind of scenario. huhuhu

16

This is what I mean. AS the name implies, it is the settings that allow your application to connect to the SQL server. So you have 500 gas stations and one sql server located at the main office. In order for these 500 office locations' application, if installed at each gas station location, will require connection string, or connection detail, shown below, in order to read and write data. for this data stream to travel to the central office, you need some sort of connection, be it internet, dial up, satellite etc.

https://www.connectionstrings.com/sql-server/

Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;
Pwd=myPassword;

1 Like
17

Thank you! It helps a lot.

cheers.