SQL Server – Correct Way to Restore Databases to New Server

sql serversql-server-2008sql-server-2016

We are migrating from sql server 2008 to 2017 and will do the following

  1. install sql server 2017 on the new machine
  2. make backups of all dbs
    on old server
RESTORE DATABASE DbName FROM DISK = 'C:\temp\Live\dbname.bak'
WITH
   MOVE 'DbName' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\DbName.mdf',
   MOVE 'DbName_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\DbName_log.ldf'

I would like to avoid the WITH Part if possible but that requires me to create a blank db first and set its version to 2008 before i can properly restore.

I guess what i am asking is, what is the best way to take a .bak file and restore it as db on a server that doesnt have any databases.

Prefer to do this without attach/detach

I have a large number of databases so I will be creating a script to do all of them at once.

Best Answer

I am not sure what your issue is here.

  • When you restore the database, the data (mdf) and log (ldf) files have be on disk someplace. The WITH MOVE just tells SQL where to put them.

  • You can't have a SQL server without databases, every install will have 4 databases out of the box, master, msdb, model & tembdb, they are system databases, and they each have mdf & ldf files someplace, just like the user database you want to restore.

  • As scsimon mentions in a comment, you probably do not want your SQL files on the C drive, best practice explained to me is for to have 4 drives for SQL,

    • Data Drive (mdf)
    • Log file drive (ldf)
    • Tempdb (mdf & ldf for tempdb)
    • Backups (bak & trn, which should be copied off at least nightly)
Related Question