?
Solved

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

Posted on 2008-06-13
5
Medium Priority
?
300 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 21781516
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
ID: 21781555
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
ID: 21781556
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
ID: 21781658
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 1500 total points
ID: 21782766
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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

770 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