4

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?

4
  • This belongs on Database Administrators SE. Flagged for migration. Commented Jun 18, 2012 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
    Commented Jun 18, 2012 at 12:58
  • @dougajmcdonald: I am in fact using the GUI - if you supply that information in an answer I'll accept it =) Commented Jun 18, 2012 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. Commented Jun 18, 2012 at 13:43

1 Answer 1

7
restore database YourDatabase
from disk = 'C:\db.bak'
with 
    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 and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.