Export Database without data? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Export Database without data?

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-03-05 : 14:36:41

This should be easy, but I'm a little stumped.

How do I create a database without data, just the procedures, tables, index's, views, triggers, etc?

Thanks!

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-03-05 : 15:24:58

I just did the simple approach.

1. Backed up database to another called mydbclean
2. ran these scripts on it. (NOTE THE FIRST IF STATEMENT THAT CHECKS FOR TSI is so I skip tables that have the letters TSI in it
3. When done I now have a clean db I can restore from.


EXEC sp_MSForEachTable 'if not (''?'' like ''%TSI%'') BEGIN
	ALTER TABLE ? NOCHECK CONSTRAINT ALL
	END'
GO

EXEC sp_MSForEachTable 'if not (''?'' like ''%TSI%'')
BEGIN
 IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
  DELETE FROM ?
 else 
  TRUNCATE TABLE ?
END
'
GO


EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO


EXEC sp_MSForEachTable ' 
if not (''?'' like ''%TSI%'')
BEGIN
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1 
DBCC CHECKIDENT (''?'', RESEED, 0) 
END
' 
GO

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-03-07 : 18:36:40

In SSMS object explorer, right-click on the database and select Tasks -> Generate Scripts

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-03-09 : 22:41:08

Ahhhh!! Wow, talk about something being so simple that I completely overcomplicated!

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2011-03-09 : 23:32:20

from command line you can use this also: http://scriptdb.codeplex.com

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources