Solved

How to change the "Restore As" path to default to another location?

Posted on 2008-06-13
5
275 Views
Last Modified: 2008-06-16
When restoring databases in SQL 2005, i have to manually change the path for the Data and the Log file to be stored on a seperate partition each time i do a restore.  Is there a way that i can change the default location for this, so i dont have to do this manually every time?

Right now its defaulting to this location:
C:\Program Files\Microsoft SQL Server\MSSQL\Data

I want it to default to this location:
D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
0
Comment
Question by:OC12
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
Hello OC12,

you have to specify it while restoring the db, as by default it will restore to the location from where the original file was backed up

GOD BLESS,

Aneesh R.
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
restore dtabaes dbname
from disk = 'c:\bakup.bak'
with move
'logicaldatafilename' to 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\LogicalDataFile.mdf',
move 'logicallogfilename' to 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\LogicalLogFile.mdf',
recovery
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
sorry..mistype

restore database dbname
from disk = 'c:\bakup.bak'
with move
'logicaldatafilename' to 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\LogicalDataFile.mdf',
move 'logicallogfilename' to 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\LogicalLogFile.mdf',
recovery
0
 

Author Comment

by:OC12
Comment Utility
I have about 5 users who do restores, i want to make this seemless for them so they dont have to manually type in the correct path.  Is there some setting in SQL 2005 where i can specify this new location?
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
Comment Utility
Don't think you can... using normal tools...

While the default ROOT path is configurable, it is used for a few things, and the restore browser does not have it's own default location that is immediately configurable...

HOWEVER...  try this, and maybe update a registery accordingly (on the server)

master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory'


Or, write a little utility or SP routine to perform the functions as outlined by chapmandew...

Or, have a look at http://www.codeproject.com/KB/cs/BackUp_Database.aspx  haven't tried it, but looking at it seems OK....


0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now