Restore data from SQL Server backup

Question

Monthly I need to sync a SQL Server database. Basically the source database generates a .bak file, then I take that file and restore it to the destination database.

That worked for the first few times, now the destination database changed because there were some users added, and some tables created.

I thought to open the .bak file, query the destination DB, removing the duplicates and then do and INSERT with only the new data.

Is that something reasonable?

How can I read/open a .bak file?

Also, I have been following this instructions

Basically it's my problem, but it use robocopy, as well jobs and I can't use them now.

It use this code to restore the backup:

PRINT '*** Restore full backup of DB $(DB) ***'
:CONNECT $(TGT)
GO
USE [master]
GO
IF EXISTS (SELECT * FROM sys.databases WHERE name = '$(DB)')
BEGIN
 ALTER DATABASE $(DB) 
 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
 DROP DATABASE $(DB)
END
RESTORE DATABASE $(DB)
FROM disk = '$(RESTORE_PATH)\$(DB).bak'
WITH RECOVERY, NOUNLOAD, STATS = 10, REPLACE,
 MOVE '$(DATAFILENAME)' TO '$(RESTORE_DATA_PATH)\$(DATAFILENAME).mdf',
 MOVE '$(LOGFILENAME)' TO '$(RESTORE_DATA_PATH)\$(LOGFILENAME).ldf'
GO

From what I remember from SQL Server, it will restore the entire database. Is it possible to restore only one table?

Thanks

• TomTom: As short answer: you can not. You can restore the database, not selected data from it.
• Critical Error: Check out IDERA's SQL Safe Backup. It's a great backup tool for SQL Server, and it allows for virtual DBs based on a backup file(s). It might be overkill, but I highly recommend it. idera.com/productssolutions/sqlserver/sqlsafebackup
• McNets: Have you considered to publish your db?
• radicaled: The idea is not use extra software to do it. Another possibility I have been reading is to use a scratch DB, restore the DB there and then copy the information.

Answer 1

Is it possible to restore only one table?

No.

the destination database changed because there were some users added, and some tables

If the additions can't be added with a simple script you run after the restore, they should be in a separate database. You can use Views or Synonyms to reference tables across databases.

Answer 2

My suggestion for this would be to separate your concerns regarding the destination database.

Basically, on target server, have a database that you restore to and can overwrite as you will, no changes are allowed here.

On the target server you have a second database where your users can create new tables, views, stored procedures as they want. They can use three part naming [database].[schema].[object] to access items in the original database.

This will allow you to do a blind restore from source onto target without wiping out anything that they are working on. Their custom/test stuff remains in it's own database.

Users is a harder issue, but you could just add a step to add them back at the end of the restore job. Or have a stored procedure in the database you allow them to access that does the necessary and then you just call that at the end of your restore.

Answer 3

If you just wanna restore a table and users, you can export the information by generating a Sql Script from the database choosing (only schema or in your case data and schema). Follow the steps in this tutorial:

https://dzone.com/articles/generate-database-scripts-with-data-in-sql-server

Hope it helps.

Answer 4

... I need to sync a SQL Server database ... generates a .bak file, then I take that file and restore it to the destination database.

... destination database changed ... some tables created.

The first step of your restore process appears to be to drop the target database so what does it matter what's happened to it since it was last "refreshed" in this way? The tables that were created in the target database will be dropped along with the rest of the target database.

I suspect the problem here is that Developers have created things in the target database and need them for their Application to work. If that's the case then it's up to them to them to ensure that they have a way up "re-upgrading" the database after you've refreshed it to put all their new stuff back again.

• Jonathan Fite: You are correct about the area of responsibility, but it's a more professional approach to offer options and help come to a solution that works for everyone. You could easily end up with the developer team trying to do an end-run around the data team if they feel they cannot get help when they need it.
• Phill W.: I absolutely agree. Unfortunately, over the years, I've worked with too many Developers who seem to expect me to have a long, white beard, carry a glowing staff, and be able to fix all of their "Data problems" with just the wave of a hand. They've taken little, if any, interest in, and no responsibility for, "their" Databases, viewing them as arcane things into which they should be able to just throw data, any-old how, and still expect the whole to remain performant.