Solved

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

Posted on 2008-06-13
5
285 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 51

Accepted Solution

by:
Mark Wills earned 500 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Question about DB Schema 27 56
SQL Syntax: How to force case sensitive query? 2 29
error in my cursor 5 31
SQL invalid column name 5 12
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

773 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