0

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.

Improve this question
3
  • 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
    Commented Sep 19, 2019 at 16:26
  • there is no 'issue' per say, i guess i was just confirming that above is the best way/command
    – Zoinky
    Commented Sep 19, 2019 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.
    – S3S
    Commented Sep 19, 2019 at 16:27

1 Answer 1

Reset to default
0

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)
Improve this answer
2
  • 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
    Commented Sep 19, 2019 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
    Commented Sep 19, 2019 at 16:35

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.