I have a SQL Server instance with 3 databases in it (SQL Server 2016 Web on Win Server 2012 r2). All databases have relations with each other. It is live and is feeding some web services.

I need to update 2 databases (or all 3) with the actual data. It takes around a week. So, I created a new VPS and a new SQL Server on it, I created same databases (with the same names and same filenames) on it and I filled them with the new data with no rush.

Now I want to learn what is the best, safest and fastest way to update the live environment.

I plan to do something like this:

  1. Detach the new DBs on the new SQL Server

  2. Detach the hdd from the new windows server.

  3. Attach the hdd to my current live server.

  4. Detach (or maybe delete ???) the old 2 or 3 databases from the live SQL Server.

  5. Attach the new databases on newly attached hdd to old live server.

I have some questions about the logins, relations between the databases, etc...

I need to interrupt the services no more than some minutes. How can I proceed?

I would appreciate with any help.

  • They sound like they're really one database instead of three.
    – duffymo
    Nov 28, 2020 at 15:36

1 Answer 1

  1. Restore new database from backup (or attach) as yourDatabaseNew
  2. Rename old database to yourDatabaseOld
  3. Rename new database to yourDatabase
  4. Drop the old database

To fix the logins problem make sure you created all logins and users on your temporary VPS. Export logins from the existing server with sp_help_revlogin

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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