December 13, 2012 at 10:16 am
I'm running server 2008 R2. How do you do a database restore with just structure and no Data?
I've looked online and either I haven't looked in the right places or it's something that's easy and i'm missing.
Do I go through the wizard and right before I finish I script out the restore and then add some extra commands for structure only?
December 13, 2012 at 10:56 am
forceman29 (12/13/2012)
I'm running server 2008 R2. How do you do a database restore with just structure and no Data?I've looked online and either I haven't looked in the right places or it's something that's easy and i'm missing.
Do I go through the wizard and right before I finish I script out the restore and then add some extra commands for structure only?
Restore without data is kind of an oxymoron. The point of restore is to restore the database. 😀
If you just want the ddl you will need to generate a script to create your database. You can use the script wizard to make this pretty simple. Just right click the database in object explorer -> Tasks -> Generate Scripts...
This will bring up a wizard that will let you specify what object(s) you want to script. Just click everything and it will make a massive script for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2012 at 11:15 am
Thank you Sean. I will give that a go.
Yeah that's what I initially thought a restore but no Data (then what's the point), I just didn't know of a better way to articulate it then they did (unfortunately). I knew what they were asking just knowing how to do and say how to do it is my problem 🙂
December 13, 2012 at 11:39 am
A suggestion: Ask about "lookup tables". Restore-no-data, does it include things like a table of US states and Canadian provinces? Are there tables that will need to have data left in them, and they just want to purge things like customers+orders (or whatever is applicable), or do they really want ALL data gone?
Most databases have some lookup tables that aren't really thought of as "data" by the business unit. Make sure you're clear on anything like that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 14, 2012 at 3:33 am
funny play 😛 😀 (to make the table empty)
use this in every database
select 'truncate table ' + name + '; ' from sys.objects where type = 'U'
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 14, 2012 at 7:40 am
Bhuvnesh (12/14/2012)
use this in every database
select 'truncate table ' + name + '; ' from sys.objects where type = 'U'
of course this will fail misserably on every table that has foreign keys referencing it. Would be much easier to do the scripting that others have mentioned.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply