I have a database backup file (db.bak) which I want to restore and import in my SQL Server 2008 R2 instance. However, the database takes approx 1 GB of space, and my C: partition is really strained at the moment - I don't have that much space left. (Yes, I know this is bad, but there's nothing I can do about it at the moment...)

I do have some space on my D:\ partition - can I somehow restore the backup and use a location on D:\ instead? If so, how?

migrated from stackoverflow.com Jun 18 '12 at 13:21

This question came from our site for professional and enthusiast programmers.

  • This belongs on Database Administrators SE. Flagged for migration. – Thomas Stringer Jun 18 '12 at 12:44
  • 3
    Alternatively, if you're using the GUI, when you select 'Restore Database' you can go the 'Options' page and change the paths in the 'Restore the database files as' in the 'Restore As' column, to refer to the new locations you want. – dougajmcdonald Jun 18 '12 at 12:58
  • @dougajmcdonald: I am in fact using the GUI - if you supply that information in an answer I'll accept it =) – Tomas Aschan Jun 18 '12 at 13:25
  • I put the GUI portion in my answer as well, as I'm not sure if the original commenter has followed the migration here. – Thomas Stringer Jun 18 '12 at 13:43
restore database YourDatabase
from disk = 'C:\db.bak'
    move 'YourDataFile' to 'D:\YourNewDir\YourNewDataFileName.mdf',
    move 'YourLogFile' to 'D:\YourNewDir\YourNewLogFileName.ldf'

The above command will restore your database but move your database files to a different location as per your specifications. To get a list of database files contained in the backup (I just used the logical names of YourDataFile and YourLogFile), you can run the following command:

restore filelistonly
from disk = 'C:\db.bak'

If you want do to this through the GUI, then in the Restore Database dialog you can click on the item Files on the left, and you will see your database files listed. There are elipses buttons next to those file names, and you can click that to change the path. Likewise, you can just double click on the text box that has the file names themselves and change that to reflect the new path. Below is a screenshot of what I explained:

enter image description here

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.