How to change the default database folder

When we restore an .bak to SqlServer 2005, it will place the .mdb file in
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
How can I change this default database folder?By default the restore database command will try to restore the database
files to their original locations when the database was backed up. If you ar
e
using Management Studio on the Restore Database window select the Options ta
b
and update the 'Restore As' section as needed.
If you already restored the database you can still move the files to some
other location by using, for example, detach, move the database files and th
e
attach the database again.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"ad" wrote:

> When we restore an .bak to SqlServer 2005, it will place the .mdb file in
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
> How can I change this default database folder?
>
>|||You can change default database locations setting from SSMS. Go to Instance'
s Server Properties\Database Settings. There, you'll see "Database default
locations" at the bottom of the window.
Ekrem nsoy
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:ud5iIjINIHA.5040@.TK2MSFTNGP04.phx.gbl...
> When we restore an .bak to SqlServer 2005, it will place the .mdb file in
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
> How can I change this default database folder?
>

How to change the default database folder

When we restore an .bak to SqlServer 2005, it will place the .mdb file in
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
How can I change this default database folder?By default the restore database command will try to restore the database
files to their original locations when the database was backed up. If you are
using Management Studio on the Restore Database window select the Options tab
and update the 'Restore As' section as needed.
If you already restored the database you can still move the files to some
other location by using, for example, detach, move the database files and the
attach the database again.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"ad" wrote:
> When we restore an .bak to SqlServer 2005, it will place the .mdb file in
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
> How can I change this default database folder?
>
>|||You can change default database locations setting from SSMS. Go to Instance'
s Server Properties\Database Settings. There, you'll see "Database default
locations" at the bottom of the window.
--
Ekrem Önsoy
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:ud5iIjINIHA.5040@.TK2MSFTNGP04.phx.gbl...
> When we restore an .bak to SqlServer 2005, it will place the .mdb file in
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
> How can I change this default database folder?
>

How to change the default database folder

When we restore an .bak to SqlServer 2005, it will place the .mdb file in
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
How can I change this default database folder?
By default the restore database command will try to restore the database
files to their original locations when the database was backed up. If you are
using Management Studio on the Restore Database window select the Options tab
and update the 'Restore As' section as needed.
If you already restored the database you can still move the files to some
other location by using, for example, detach, move the database files and the
attach the database again.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"ad" wrote:

> When we restore an .bak to SqlServer 2005, it will place the .mdb file in
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
> How can I change this default database folder?
>
>
|||You can change default database locations setting from SSMS. Go to Instance'
s Server Properties\Database Settings. There, you'll see "Database default
locations" at the bottom of the window.
Ekrem nsoy
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:ud5iIjINIHA.5040@.TK2MSFTNGP04.phx.gbl...
> When we restore an .bak to SqlServer 2005, it will place the .mdb file in
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
> How can I change this default database folder?
>

How to change the default backup data directory in SQL Server 2000?

Hi,

I had SQL Server 2000 installed like below:

Installation Directory = C:\Program Files\Microsoft SQL Server

Default data directory = D:\

Default log directory = D:\

I found out that the data is stored in D:\Program Files\Microsoft SQL Server\MSSQL\data and backup is done in D:\Program Files\Microsoft SQL Server\MSSQL\backup

However, Microsoft says we should never put the ..\backup and ..\data folders in the same partition.

My question is: How can I move the path of the backup directory?

I checked the registy but this is what I found:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer

..BackupDirectory=C:\Program Files\Microsoft SQL Server\MSSQL\backup (<-- note it is pointing to C:\ not D:\)

..DefaultData=D:\

Can somebody help please?

http://search.msn.com/results.aspx?q=Introduction+to+Microsoft+SQL+Server%e2%84%a2+2000+Reporting+Services

Beneficial or non-beneficial link above

http://support.microsoft.com/kb/185663/

|||

Hi,

As you reach to the correct part change the values of .BackupDirectory=<pathyouwanttoSoreBackUp>

or/and if you are using Schedule/Maintanance Plan you may specify the path their too.

Hemantgiri S. Goswami

How to change location of log files

Hey all,
Can somebody please give me some guidance on how to change the location of
the LogFiles after the server has been working for quite some time? We have
the RSS running everything from C:\ and we're running out of space there,
primarily because of the RS logs. Where would I change this so that the
files could go to somewhere else on my server that has plenty of space?
Further, are there any other logs or other constantly growing file that I
might also want to relocate?
I tried searching through the RSS help and didn't come up with anything on
relocating these LogFiles.
Please advise.
ThanksDid you ever get an answer or figure out how to do this? I have the same
issue.
Thanks.
"rjseh2001" wrote:
> Hey all,
> Can somebody please give me some guidance on how to change the location of
> the LogFiles after the server has been working for quite some time? We have
> the RSS running everything from C:\ and we're running out of space there,
> primarily because of the RS logs. Where would I change this so that the
> files could go to somewhere else on my server that has plenty of space?
> Further, are there any other logs or other constantly growing file that I
> might also want to relocate?
> I tried searching through the RSS help and didn't come up with anything on
> relocating these LogFiles.
> Please advise.
> Thanks|||Nope, never got a response.
"J Noble" wrote:
> Did you ever get an answer or figure out how to do this? I have the same
> issue.
> Thanks.
> "rjseh2001" wrote:
> > Hey all,
> >
> > Can somebody please give me some guidance on how to change the location of
> > the LogFiles after the server has been working for quite some time? We have
> > the RSS running everything from C:\ and we're running out of space there,
> > primarily because of the RS logs. Where would I change this so that the
> > files could go to somewhere else on my server that has plenty of space?
> > Further, are there any other logs or other constantly growing file that I
> > might also want to relocate?
> >
> > I tried searching through the RSS help and didn't come up with anything on
> > relocating these LogFiles.
> >
> > Please advise.
> > Thanks

How to change error log location

I have been trying to change the location of the SQL 2005 error log. It's logging to the default location at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

I want to move the log location to the E drive because it can get quite large.

I already changed the setting in the SQL Server Error and Usage config tool, but this did not seem to make a difference.

Can someone tell me please how to do this?

Thank You,

Ed

I don't believe there is a suported way of doing this.

The SQL Server Error and Usage not for the standard SQL Log, but for the log relating to fatal errors and usage information see "Error and Usage Report Settings" in BOl for more information.

|||YOu can schedule a job to recycle the error log and step2 to move the archive files to another drive where you have more storage.|||

The errorlog location is controlled by the -e startup parameter, which is read from the registry at startup.

You can change the default location from the SQL Server Configuration Manager. Open the properties for the SQL Server Service and find the Startup Parameters entry on the Advanced page.

By default the entry looks something like this:

-dD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

Change the path after -e to be anything you'd like.

how to change default system database location after install of ssql express?

don't like the default location being in c:\program files\Microsoft SQL Server\MSSQL.1\If you want to move the system databases you will have to suhtdown the service, move the system database files and change the appropiate settings in the registry. Then you will be able to start the service again.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\Parameters

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||thank you very much. I was not able to find parameters under mssql.1 but under .3

How to change default backup destination to the other folders ?

By default,when I would like to back up any database by SSMS.

default destination on disk will be C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ every time

Although I everytime change it to my backup folder.

Please let me know how to change it.

I believe you can either write a query or create a procedure:

e.g.

CREATE PROCEDURE mybackup

AS BACKUP DATABASE [databasename]TO DISK = N'c:\SQL\Backup\databasename.bak'WITH RETAINDAYS = 1, NOFORMAT, NOINIT,NAME = N'databasename-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GO

How to Bulk Insert string data into a money colum?

How to Bulk Insert string data into a money colum?

Format files work great to import string data into a char column.

But I cannot convert a char column to either a money or numeric data type.

I get datatype errors when bulk inserting string data into a money column.

The string data is 22 characters long, no decimal point, trailing negative sign.

Here is an example: 000000000000007898384-

My goal: Bulk Insert the above sample string into a money column.

Often, for situations like this, it is useful to first import into a 'staging' table (same datatypes as import data),

AND then execute 'clean-up' routines

BEFORE moving the data into the production tables.

This allows correcting alot of data anomolies that cannot be handled with a format file.

|||

Thanks, Arnie, for the quick response. What clean-up routines did you have in mind?

Using Enterprise Manager to change the destination column's data type from "char" to "money" or "int" won't work.

Are you talking about concatenating a decimal point to the end of the source string, in hopes that the decimal point will help me get past the conversion issue?

|||

John,

Something like this could work for your situation:

Code Snippet

DECLARE
@.MyString varchar(25),
@.MyMoney money

SET @.MyString = '000000000000007898384-'

IF right( @.MyString, 1 ) = '-'
SET @.MyMoney = cast( ( '-' + left( @.MyString, ( len( @.MyString ) - 1 ))) AS money )
ELSE
SET @.MyMoney = cast( @.MyString AS money )

SELECT @.MyMoney

-7898384.0000

|||

So, it looks like you're putting the negative sign at the front as needed and leaving the positive numbers alone, before casting the result as money. This is fine.

My SQL table has 23 columns and millions of rows.

Column 10 is char, 22 characters wide. Negative signs are trailing.

How would I apply the code to all the rows of column 10 in that table?

|||

You would build a CASE structure (something like this):

DECLARE @.MyTable table
( RowID int IDENTITY,
Column10 varchar(50)
)

INSERT INTO @.MyTable VALUES ( '000000000000007898384-' )
INSERT INTO @.MyTable VALUES ( '000000000000007898385' )

SELECT
RowID,
MyMoney = cast( stuff( CASE
WHEN right( Column10, 1 ) = '-'
THEN ( '-' + left( Column10, ( len( Column10 ) - 1 )))
ELSE Column10
END
, len( CASE
WHEN right( Column10, 1 ) = '-'
THEN ( '-' + left( Column10, ( len( Column10 ) - 1 )))
ELSE Column10
END ) -1
, 0, '.' ) AS money )
FROM @.MyTable

RowID MyMoney
--
1 -78983.8400
2 78983.8500

For illustration purposes, I've also added a decimal point two places from the right. If you don't need that, then remove the STUFF() function. (I thought that just 'might' come up next...)

You 'should' be able to use a query like this to INSERT the staging table data into your production table.

|||

Ok, Arnie, I'll give it a shot. Thanks again.

John

|||

My brain wasn't operating on all cylinders this morning.

You can do without the STUFF() function -just divide by 100 if you need the partial money, remove the [ / 100 ] if you have whole money.

Code Snippet

SELECT
RowID,
MyMoney = cast( CASE
WHEN right( Column10, 1 ) = '-'
THEN ( '-' + left( Column10, ( len( Column10 ) - 1 )))
ELSE Column10
END AS money ) / 100

FROM @.MyTable

|||This is great, Arnie, thanks.