Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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?

share|improve this question
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 Lycken 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

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

1 Answer

up vote 6 down vote 
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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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