4

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?

Improve this question

2 Answers 2

Reset to default
6

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.

Improve this answer
2
  • Thank you so much. But how can I do that(clean it up)?
    – user2675769
    Commented Aug 12, 2013 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
    Commented Aug 12, 2013 at 17:43
5

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.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.