Retore SQL Server database to different file name and location

The RESTORE ... WITH MOVE option will let you determine what to name the database files and also what location these files will be created in.  Before using this option you need to know the logical names for these files as well as know where SQL Server will restore the files if you do not use the WITH MOVE option.

If another database already exists that uses the same file names you are trying to restore and the database is online the restore will fail.  But if the database is not online for some reason and the files are not open, the restore will overwrite these files if you do not use the WITH MOVE option, so be careful you do not accidently overwrite good database files.

Also, when using the WITH MOVE option you need to make sure the account used for the SQL Server engine has permissions to create these files in the folder you specify.

T-SQL

Determine contents of backup

So the first thing you need to do is determine the logical names and the physical location of the files.  This can be done by using the RESTORE FILELISTONLY command.  This will give you the logical and physical names.

Here is an example:

RESTORE FILELISTONLY FROM DISK = 'C:\AdventureWorks.BAK'
GO

This gives us these results:

Restore full backup WITH MOVE
So let's say we want to restore this database, but we want to put the data file in the "G:\SQLData" folder and the transaction log file in the "H:\SQLLog" folder.  The command would be like the following:

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH MOVE 'AdventureWorks_Data' TO 'G:\SQLData\AdventureWorks_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'H:\SQLLog\AdventureWorks_Log.ldf'
GO

Restore full and transaction log backup WITH MOVE
The WITH MOVE only needs to be specified for the first restore, because after this the database will be in a "restoring" state.  The second restore will just write the contents to this new location that is being used.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH MOVE 'AdventureWorks_Data' TO 'G:\SQLData\AdventureWorks_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'H:\SQLLog\AdventureWorks_Log.ldf',
NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
GO

SQL Server Management Studio

To restore using SSMS do the following, on the options page for the restore, change the "Restore As:" values for each file as shown below.  The below restores to the root folders, but you could change these to go to G:\SQLData\ and H:\SQLLog\ as needed.