Link to home
Avatar of perksdev

asked on

How can I restore just the Data only from a Full DB Backup?

Here's the situation.  I have a database server at a colo facility and I need to transfer the data from the DB to a new server at my current location.  The database supports a web app that can't afford to have a lot of down-time.

I have setup the new server with everything we need and I've restored the DB from a Full Backup of the production DB.  I have made numerous changes to the new copy of the DB to help increase security.  Now I need to get the latest data from the production DB and apply it to the new copy of the DB.  If I pull down a Full Backup and restore it, it overwrites all of the security settings I've changed in the new DB and it kills the associations between DB Users and Server Logins.

I need to know how I can restore just the data portion of the production DB to the new DB.  It would also be great if I could choose which data to restore from the backup as I need to delete some tables, schemas and procs in the new copy of the DB, so I don't want those restored from the backup either.

I'm totally new to SQL Server 2005 Backup and Restore procedures.  I'm a developer who has been thrown into the role of DBA and I'm just sorta learning as I go.

Any guidance or help would be greatly appreciated.  If you need more information I'd be glad to share, but I don't even know where to start.  Thanks!

Adiitional Info:
SQL Server 2005
Full Recovery Mode
DB is about 7.5 GB
Avatar of chapmandew
Flag of United States of America image

What I would do, if I were you, is to make a backup of your prod db and restore it...BUT under a different name.  Once you have that db on the same server as the db you've made security changes, you can write queries easily enough to compare the data and insert/update the data that you need.  Other than that, there really isn't a good approach to comparing a lot of transactional data...other than maybe SQL Compare from red-gate...but if your tables are big, its going to drown that app.
Avatar of EugeneZ
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
also you can use SSIS (2005) DTS (2000)
GUI to export\import:
Sqlserver 2005 : right click your DB in SSMS -> eexport-> select destination sql server-> db-> object that you need ...
Avatar of perksdev


Thanks for the info.  Not sure why I didn't think of this already.  The info about the orphaned users was very helpful.