Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2008-06-13
5
Medium Priority
?
310 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
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 53

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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

584 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