How to make a distibutable .bak file?

Question

I am trying to restore 2 databases. One is adventureworks database downloaded from msdn(its .bak file), and another one is created by me from one databases from my old server. Both have .bak extension, are in the same folder where the new instance where they should be restored, has all the access privileges. Now while restoring adventureworks database everything goes alright. While trying to restore the other .bak file made by me I get an access error, it wants access to its original server's DATA folder. Here is the error Now, I want to know how to create .bak backup in order to restore it to any other server without this error.

Thanks for your time!

Answer 1

SQL Server won't automatically restore a database to the folder where the .BAK is located. This would be a disaster waiting to happen (say, that drive is out of space, or is encrypted or compressed, or SQL Server only has read access). Instead, it will try to place the files using the same configuration they had on the system they were backed up from. As Tibor alluded to in a comment below, you certainly could standardize all of your users' installs to have SQL Server installed to the same data folders as the source system, or at least all have the same data folder available to write to.

Barring that, you need to use RESTORE ... WITH MOVE in order to place the data and log files in the right folder for this instance. First verify what that is:

EXEC master.sys.sp_helpfile;

That will give you two rows with paths like:

filename
------------------------
C:\some\path\master.mdf
C:\some\path\mastlog.ldf

Now run the following from the backup file:

RESTORE FILELISTONLY FROM DISK = 'C:\path\file.bak';

This will give you a row for each file in the backup, corresponding to each data/log file you'll need to restore (and move). For example:

LogicalName
-----------
AdventureWorks2016_Data
AdventureWorks2017_Log

Take the C:\some\path\ part from the first query, and the logical names from the second query, into a statement like this:

RESTORE DATABASE AdventureWorks
  FROM DISK = 'C:\path\file.bak'
  WITH REPLACE, RECOVERY, 
    MOVE 'AdventureWorks2016_Data' TO 'C:\some\path\aw.mdf',
    MOVE 'AdventureWorks2016_Log'  TO 'C:\some\path\aw.ldf';

There are likely complications when your backup includes things like filestream or In-Memory OLTP.

But if your goal is to make a "distributable" backup that will do all that work for the end user, I don't think a traditional SQL Server backup is what you are looking for. Maybe you should look into database projects or providing scripts instead of backups.