current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a data warehouse which isn't huge only about 16Gb in a Microsoft SQL Server Standard Edition 2014 database. However, now that it forms part of our production system I don't like developing on it because it can affect our end users.

Therefore what I would like to do is automate a nightly process to:

  1. Backup the structure of warehouse1
  2. Restore the structure to a warehouse2 on the same server

So in effect I end up with a mirror of warehouse1 database structure in Warehouse2 database. I can then use this database for development.

As all of the data is retrieved from other locations I don't want to back the data up I just want to do the structure.

Does this seem like the most appropriate method of achieving what I want to do or is there an alternative? If this does sound right would you be able to suggest how I would go about achieving this?

Thanks in advance,

Andrew

share|improve this question

migrated from stackoverflow.com Aug 14 at 1:23

This question came from our site for professional and enthusiast programmers.
1  
Since this is going to be a dev database why not just do a backup and restore to your dev environment. I can't imagine wanting to constantly overwrite your dev work with production. Hard to make any progress like that. –  Sean Lange Aug 13 at 13:52
    
Thanks Sean, The reason for wanting to overwrite the dev site is because its the structure that is developing not the data. –  user3455191 Aug 13 at 14:31
    
I assume the structure would be changing but wouldn't the changes be happening in dev instead of production? –  Sean Lange Aug 13 at 14:37

1 Answer 1

up vote 0 down vote

Given only the single server for production. Since you didn't mention a dev environment.
If I were me, I'd run an export of the warehouse1 structure. No data to go with it.
Then I'd invest some time to generate testdata. They don't need to be live data. They'd have to represent usecases.
Given the structure and testdata, I'd have everything I need for development. Never mind the tiny datasizes.

UPDATE STATISTICS testtable WITH ROWCOUNT = 50000000, PAGECOUNT = 500000

That and estimated execution plans are very good friends of mine. ;)

Now that the warehouse2 is all set and done for development, you can then run schema compares between warehouse1 and warehouse2. There are tons of tools out there that will help with the schema compare.

Now about the backups. Bulk log recovery might be the beste choice for you to start with. I assume you have no other choice than to backup in nighttimes. Then again you sure have thought about point in time and disaster recovery scenarios. ;)
I'd be careful advising standard backup concepts, after all you know best what your warehouse1 looks like.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.