SQL 2008 how to backup only store procedures wihtout data or log files

We have 200gb+  SQL 2008 DB with 300+ store procedures and 300+ tables.
what is the best way for me to create backup of this database without saving  any transactional data?

Just backup up  tables, store procedures and triggers so that I can quickly restore only DB Structure with store procedures without any data?
leop1212Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee SavidgeCommented:
As far as I know you can't do that. You would need to script out your database, stored procedures, views, functions, users, indexes, etc.

Interesting question though and I hope someone has a better answr than me.
leop1212Author Commented:
well,
I appreciate the comments but than doesn't answer my question.
RayData AnalystCommented:
Lee did "sort of" answer your question.  

Strictly speaking a backup is not what you want.  You want a 'Shell' or 'Template' of your DB.  You achieve that via generating a script that creates the required 'pieces' of the DB.  So . . .

What you are asking for is essentially scripting out the DB since you do not care about the data.  In simple terms, scripting means creating a 'query' that will create the pieces you wish.  This could be a simple script of only stored procedures or of the SPs and data tables (without data) and/or triggers, views, etc.  I do not wish to re-invent the wheel, so I will simply refer you to the technet article via the links below.

This article covers the concept . . .
https://technet.microsoft.com/en-us/library/ms191299(v=sql.105).aspx

This article covers more specific 'how to' information . . .
https://technet.microsoft.com/en-us/library/ms178078%28v=sql.105%29.aspx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

leop1212Author Commented:
Ray,
your answer make much more sense.
let us try to start from another end.
say I made a full backup of my DB and restored it to a test DB.
now I have test database with all structure and store procedure which I want to backup   + data which I want to purge.
could you give me an example of the script which will delete and purge data from all tables and than pack the database?
Lee SavidgeCommented:
Are you sure you want to purge everything? For example, does the database contain data about any fields held in the system, or users perhaps?
leop1212Author Commented:
i want to delete data in all table expect system tables.
leop1212Author Commented:
the only cavity is that there are data constrains preventing deletion on many tables. is there a easy way to buy pass it?
Brian CroweDatabase AdministratorCommented:
There is no "easy" way to do this.  You would have to:
remove all foreign-key constraints
cycle through and truncate each table
recreate all foreign-key constraints

Your best bet going forward is to source control your database and use that to recreate a shell if needed.
RayData AnalystCommented:
If you read those links, you will be able to generate scripts that recreate the structure and stored procedures and triggers, etc.

After that you can use import/export to fill in any "static" data From existing DB.

Posted via phone
Vitor MontalvãoMSSQL Senior EngineerCommented:
Generating scripts for your databases is the correct and faster thing to do. A backup/restore/purge can take hours. Executing a script will take you few seconds.
Remember that you only want the schema and backup takes data. Script is a text file with all database objects definition and  you can run it in any SQL Server instance (at least with the same SQL Server version).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.