5

We are a team of mathematicians (i.e., no DBA experience).

We are migrating from an old server box (SQL Server 2012) to a new server box (SQL Server 2017). For each database on the old server, we made a backup copy, ported it over to the new server, and restored it to its full database glory from the backup file.

So far, so good.

However, there was a delay in our move-in date and we are still using the old server. As we continue to work on the old server, changes continue to accrue in those databases. So it means that the copied databases sitting on the new server are now obsolete and out-of-date. (Ironic, isn't it?)

For those databases that have changed, we know we will eventually have to make new backup copies and port those over to the new server.

When it comes time, what is the smartest way to get rid of those now-obsolete databases on the new server before restoring the latest backup copies?

We worry that with the same names, some occluding or eclipsing might occur if we are not careful. We want to make sure that the out-of-date copies on the new server are well and truly gone before restoring the latest versions.

Improve this question
2
  • 1
    The DROP DATABASE command will get rid of the databases and their files on the server. That's about as definitive as you can get, without like, formatting the drives too.
    – Erik Reasonable Rates Darling
    Commented Sep 26, 2017 at 16:50
  • If you're not keen on deleting the old database files immediately, you can detach the old databases, and move the files (so they're still available, just in case something truly disastrous happens) with EXEC sp_detach_db @dbname = N'<your_db_name>'. However, unlike the previous commenter's option, while you cannot access the old databases without someone actually issuing the commands to re-attach them, anything reversible may not count as "well and truly gone" in your book.
    – RDFozz
    Commented Sep 26, 2017 at 17:17

2 Answers 2

Reset to default
6

Just use WITH REPLACE in your restore command and it will overwrite the existing databases on the destination. Simple, and done. You can DROP first, but its not necessary.

WITH REPLACE is the T-SQL keyword that corresponds to "overwrite the existing database" in the restore database GUI (added for future readers of this thread)

Oh...and are you running an RC version of 2017? RTM isn't until next Monday 10/2...just an afterthought.

Improve this answer
3

Restoring a database over an existing database completely replaces the old database - just make sure you check off "overwrite existing database".

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.