CODEWITHSUNDEEP
sqlsql-serverdatabase
user811433
user811433

Reputation: 4149

New database in SQL Server is getting created with tables from another database on the same server

I have SQL Server installed on my machine with a few databases in it. Now I am creating a new database on the same server: Databases -> right click -> New database.

When I open the database it is getting created with tables from another database on the same server! Did any one see this behavior/problem before? Any possible solution?

Upvotes: 0

Views: 344

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175934

It is completely normal. New databases are based on system model database which is template for them.

Model database

When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database

Is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.

Read also about other system databases and their usage model/msdb/tempdb/model/resource

Upvotes: 1

Bacon Bits
Bacon Bits

Reputation: 32180

New databases are created as a copy of the model system database.

By default, this database is empty, but there is nothing stopping a user with the proper permissions from adding tables or data to the that database. Indeed, if you want to control how new databases are created such as default compatibility levels, default languages, default function, data types, etc. then you can do so by creating them in the model database.

Upvotes: 1

Related Questions