Partial Restore

Question

Post reply

Partial Restore

Aubergine Blue

SSChasing Mays

Points: 656
More actions
September 20, 2005 at 8:35 am

#110775

I have a testing SQL server and a live server, I am guessing this gets asked a lot, but my search didn't pick up anything.
On my test server I would like to restore the live database onto it, which works fine, but there are some new views and tables I have created on test server which I do not want to overwrite. Is there a way to just restore everything without overwriting the new stuff?

Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Restore under a new name, then use your change management in place to deploy to that new db.

Aubergine Blue SSChasing Mays Points: 656 More actions · Answer 2

Thanks, that's great with tables, but I have problems with keeping the views.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

What???

The will create an exact copy of the production db. Then using your in-house change management, redeploy the code to that db just like you would in the production deploy...

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

Sharon,

Remi's suggestion is good. You could also use DTS to transfer objects between SQL Server databases and choose just the objects you want to put into your test database.

Greg

Aubergine Blue SSChasing Mays Points: 656 More actions · Answer 5

Thanks, but I tried to DTS views, and they just turn into tables.

I know I can recreate the views with scripts, but I just wanted a slicker way of doing it I guess....

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Views don't turn into table in dts transfer... are you copying related objects too??

READ THIS :

Change Management

This is just a start. Once you got the principle, move to automated change management .

Greg Charles SSC-Forever Points: 45403 More actions · Answer 7

Sharon,

To prevent views from being imported as tables, you should use "copy objects and data between SQL servers" instead of the option "Copy table(s) and View(s) from source database" in the Import/Export wizard or use a Copy SQL Server Objects task in a DTS package.

Greg

homebrew01 SSC Guru Points: 55454 More actions · Answer 8

Depending on the scope of the number of objects in training you want to keep, you can script out those that you want to keep, then apply the scripts after the restore. We do this in one case, because we only need a few things from Training saved, and everything else will get replaced.

Aubergine Blue SSChasing Mays Points: 656 More actions · Answer 9

Thank you, what do you think about using Replication?

Restoring fully to another DB, and then using replication to fill the test db with relevant data, without overwriting my new views/tables?

homebrew01 SSC Guru Points: 55454 More actions · Answer 10

Technically, you could use replication, choosing the "Don't initialize - subscriber already has data" option. But I bet it would get messy, and it wouldn't be my first choice. Log shipping might be better ?

I don't understand the scale of what you're trying to do... the # of tables, DB size, timing.

Sathish Kumar. Rajaram Mr or Mrs. 500 Points: 521 More actions · Answer 11

Hi

Try this suggestion. Maintain the additional views and tables in a seperate DB and access the data like dbname.user.table / views. Since you were accessing the data in the restored DB with qualifiers, moving them to production will not be a problem.

So you will have a two DB's, one with the production data restored other with the additional tables / views.

Sathish

S A T ...
Sathish's Blog