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'
   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.

  • What is the issue using with. Unless your file path is same in source and destination you have use with and move. Did you look psdbatools which is PowerSell Module that has bunch of commands to help you migrate large number of databases? – SqlWorldWide Sep 19 '19 at 16:26
  • there is no 'issue' per say, i guess i was just confirming that above is the best way/command – Zoinky Sep 19 '19 at 16:27
  • 4
    Also, I'd suggest putting your data and log files on something other than C:. The last thing you want to happen is to fill up the drive your OS is on. – scsimon Sep 19 '19 at 16:27

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)
  • gotcha, as i commented above, i thought maybe this was not the correct way, i assumed that i could do it without the WITH because i assumed by default it would stick them in the default path – Zoinky Sep 19 '19 at 16:32
  • It will if you use the restore database GUI, to create the code. In any case you should make sure the destination is where you want the files to be when the database is restored. – James Jenkins Sep 19 '19 at 16:35

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.