1

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

3
  • 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 '15 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 '15 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 '15 at 14:37
2

Since you are using SQL Server 20141 you could look at using DBCC CLONEDATABASE to create a statistics-only copy of the warehouse database.

See support.microsoft.com for details on the command; however it essentially boils down to:

DBCC CLONEDATABASE (warehouse1, warehouse2);

From the page linked above:

All files in the target database will inherit the size and growth settings from the model database. File name convention: The file names for the destination database will follow the source_file_name _underscore_random number convention. If the generated file name already exists in the destination folder, DBCC CLONEDATABASE will fail.

You could wrap this in code that drops the current clone, if one exists, then creates the new clone. This could be added to a SQL Server Agent Job that executes nightly, or weekly, as required.


1 - you'll need service pack 2 or above for SQL Server 2014

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy