CODEWITHSUNDEEP
sql-serversql-server-2012
user1439891
user1439891

Reputation: 33

Is restoring a database on existing one (replacing) different from restoring database by deleting the existing one in MS SQL Server?

I have to restore a database of 1 TB size from Production to Development. The current Development database is of 500 GB size and I have 700 GB free space on Development server SQL drive. Now, I should be able to restore the DB to Dev as 500+700=1200 GB space available on DEV. Is this possible or should I drop the existing 500 GB Dev database and restore the Prod backup on DEV?

How come, replacing the existing database by restore, is different from dropping the existing db then restore in MS SQL Server context?

Can anybody please explain?

Upvotes: 1

Views: 2047

Answers (1)

Tibor Karaszi
Tibor Karaszi

Reputation: 389

If you restore into an existing db name, then SQL Server will delete the existing database for you and then create the new (database files) so that the restore process can copy data from the backup to the (newly created) database files. I.e., same thing as if you first delete the database.

(If the existing database file has the same logical name and file size, then the actual file deletion and creation doesn't happen since the "containers" are already there.)

Upvotes: 2

Related Questions