I think most people setup the drives on their Development servers to match their Production servers - this is so that restores go smoothly and files go where they're supposed to, things match up nicely, etc. Unfortunately, when you create a full backup all the backup file contains is the logical name of all the files - no physical paths. This means that if the database doesn't exist on the destination server yet, the engine has absolutely no idea where to put the files or what to name them.
The following script can be executed in the context of the source (original) database that was backed up to script out the MOVE statements if your plan is to put the files in the same place on the destination server.
scriptRestoreWithMove.sql - run this in the context of the source database
SET NOCOUNT ON DECLARE @MoveOption AS TABLE ([Id] INT IDENTITY(1,1) ,[MoveOption] VARCHAR(MAX)) PRINT 'RESTORE DATABASE ' + DB_NAME(DB_ID()) -- Edit this to match where you're restoring from PRINT 'FROM DISK = ''' + DB_NAME(DB_ID()) + '.bak''' PRINT 'WITH' INSERT INTO @MoveOption ([MoveOption]) SELECT 'MOVE ''' + a.name + ''' TO ''' + a.FILENAME + '''' AS [MOVE OPTION] FROM dbo.sysfiles a DECLARE @LastId INT = 0 ,@MoveOptionText VARCHAR(MAX) WHILE EXISTS (SELECT TOP 1 1 FROM @MoveOption WHERE [Id] > @LastId) BEGIN SELECT TOP 1 @MoveOptionText = [MoveOption] ,@LastId = [Id] FROM @MoveOption WHERE [Id] > @LastId ORDER BY [Id] ASC PRINT CASE WHEN @LastId = 1 THEN '' ELSE ',' END + @MoveOptionText END PRINT 'GO' PRINT 'ALTER DATABASE ' + DB_NAME(DB_ID()) + ' SET MULTI_USER' PRINT 'GO'
And the output looks like this:
RESTORE DATABASE AdventureWorksDW FROM DISK = 'AdventureWorksDW.bak' WITH MOVE 'AdventureWorksDW_Data' TO 'D:\AdventureWorksDW\AdventureWorksDW_Data.mdf' ,MOVE 'AdventureWorksDW_Log' TO 'T:\AdventureWorksDW\AdventureWorksDW_Log.LDF' GO ALTER DATABASE AdventureWorksDW SET MULTI_USER GO
If you end up wanting to do something similar with existing databases, make sure to add a command before the RESTORE to set SINGLE_USER mode.