Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I try to create new database in SQL Server 2008 R2, it was created completely, but it is not a blank and empty database, there are many tables that come from another database. I also tried specifying a different name for its .mdf and .ldf file, but nothing changed!

Would you please help me to know where is the problem?

Also, is there any command that would reset my database as a blank db?

share|improve this question

migrated from stackoverflow.com Aug 12 '13 at 18:14

This question came from our site for professional and enthusiast programmers.

add comment

2 Answers 2

New databases are cloned from database model. Probably some accident polluted your model database (maybe a DDL script run in the wrong database). You should probably clean it up.

share|improve this answer
    
Thank you so much. But how can I do that(clean it up)? –  user2675769 Aug 12 '13 at 17:06
    
By deleting the stuff in it that you do not want to keep. Either manually or by scripting deletes for everything. –  usr Aug 12 '13 at 17:43
add comment

Below is the significance of Model database from BOL :

The model database is used as the template for all databases created on an instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system. The entire contents of the model database, including database options, are copied to the new database. Some of the settings of model are also used for creating a new tempdb during start up, so the model database must always exist on a SQL Server system.

Check for model.sys.tables looking for tables where is_ms_shipped = 0 to find out user created tables and then :

  • Check the table contents.
  • Check with your dev/users/colleagues, etc just to make sure they are not used or are critical to your application functionality.
  • Back up the model database.
  • Rename or Drop the tables.
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.