June 22, 2009 at 1:00 pm
Hi all -
I'm looking for a way to only back-up the structure (no data) of a database, something to mimic the "Generate Scripts..." task found when right-clicking on the database name. The solution needs to be able to run automatically every night since the structure will most likely change daily. It be great if I could save the database structure script as .txt file, so in case of an emergency, I could just copy the create statements script into a new query window and run it.
Before you ask, I only need the structure as the data is coming from an external source and it is only a development database.
I've looked into the scptxfr.exe executable, but that is only supported in SQL 2000; we have SQL 2000 dbs, 2005 dbs, and 2008 dbs in our environment, so that really isn't a viable solution. I know I could always copy the executable over to the 2005 or 2008 instance, but I'm hoping there is a better way to accomplish what I want to get accomplished.
I have also looked into the 'Transfer SQL Server Objects Task' in SSIS, but I have to copy the structures over into a new database. I guess I could create a new database (structure only), back it up, and then drop it every night, but that just seems like a lot of unnecessary steps to me. Plus, I think this task seems to be a little flaky, at least from what I've read online.
I guess I'm just wondering if anyone has tried to do the same thing as I'm trying to do and how you got it done.
Thanks in advance for your help and suggestions! =)
June 22, 2009 at 1:28 pm
sql_dump may be what you are looking for, take a look here http://www.wisdombay.com/downs/sqldump.php
it works pretty much the same way Oracle's "export rows=n" would do.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 22, 2009 at 1:30 pm
I would spend the money on Redgates SQL Compare tools. You would need the professional version to be able to automate the process daily.
What you would schedule would be a daily snapshot of the existing database. Any time you need to create a new copy of the database, you would then perform a comparison between the snapshot version and a new blank database. Run the synchronization tool and you will end up with a new database with an empty schema.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 22, 2009 at 1:56 pm
Hmm...interesting. I already have SQLcompare Professional Edition installed on my computer. I mostly just use it to run regular database compares so I can move database objects through the proper environments. I never even thought about taking a snapshot of the database, saving it, and comparing it to an empty database if I ever need one with an empty schema. Good call. Thanks for the advice! Much appreciated!
June 22, 2009 at 2:02 pm
Glad I could help - I am happy to hear that you already have the tools, now it's just a matter of setting it up.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply