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 standard saving folder?

Hello,

My english is not the best, but i will try my best.

I want to change the saving folder for the database from my systempartition to my datapartition (from c:\blabla to d:\mydata), but i dont know how. I can see the folder but it is in gray font and unchangeable in the server configuration tool.

Greetings Thomas

Hi Thomas,

Try detach and attach your database in new location.

Use the following:

EXEC sp_detach_db 'DBNAME'

After copy your DBFiles for new location:

Syntax:

EXEC sp_attach_db @.dbname = N'pubs',

@.filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',

@.filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf' Good Luck!Regards,

|||

To change the default database locations (for ALL future databases)

right-click on Server Name (in Object Explorer) -> Properties -> Database Settings

you'll see 2 lines under "Database default locations"

Data: PATH_NAME

Log: PATH_NAME

hope this helps

|||

Hi Jerry,

That is exactly what i was looking for, thank you very much.

Thomas

|||

I have searched for this, too. Thanks.

I have one question to add here: When browsing through the filesystem using (SQL 2005 enterpr.) I can only browse two levels within the filesystem. For example I can only go until e:\mssql\mssql but not deeper.

Can someone tell me what to do, so that I can browse into deeper folders?

How to change standard saving folder?

Hello,

My english is not the best, but i will try my best.

I want to change the saving folder for the database from my systempartition to my datapartition (from c:\blabla to d:\mydata), but i dont know how. I can see the folder but it is in gray font and unchangeable in the server configuration tool.

Greetings Thomas

Hi Thomas,

Try detach and attach your database in new location.

Use the following:

EXEC sp_detach_db 'DBNAME'

After copy your DBFiles for new location:

Syntax:

EXEC sp_attach_db @.dbname = N'pubs',

@.filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',

@.filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf' Good Luck!Regards,

|||

To change the default database locations (for ALL future databases)

right-click on Server Name (in Object Explorer) -> Properties -> Database Settings

you'll see 2 lines under "Database default locations"

Data: PATH_NAME

Log: PATH_NAME

hope this helps

|||

Hi Jerry,

That is exactly what i was looking for, thank you very much.

Thomas

|||

I have searched for this, too. Thanks.

I have one question to add here: When browsing through the filesystem using (SQL 2005 enterpr.) I can only browse two levels within the filesystem. For example I can only go until e:\mssql\mssql but not deeper.

Can someone tell me what to do, so that I can browse into deeper folders?

How To Change Database Location at runtime When Using Data Environment??

hi, i'm having this problem when i'm moving my project and database into another folder....
the report always pointing the old path database...
can somebody help me...please?
Btw i'm using CR 8.5 and vb 6 and using data environment as my report data source...Dim crRepp As CRAXDRT.Report

Set crRepp = crApp.OpenReport(App.Path & RptFile)
crRepp.Database.LogOnServer "p2ssql.dll", Server, Database,UserName,
Password)|||Hi, Thanks for replying
i already solved it with :
report.database.setdatasource [dataenv.recordset(must be open)], 3, [table index]

How to Change Data Directory to D: drive

Hi everyone,
I have a problem here. I install SQL Server and set it's Data Folder to C:
drive.
Unfortunately my C drive is running out of space and I plan to add another
drive (D: drive).
I want to move my database to D: drive, but however I cannot see D: drive in
the SQL Server Enterprise Manager
when I try to add a new database.
Could anyone help me?
Best Regards,
Deili.Hi,
Execute the command XP_FIXEDDRIVES from Query Anayzer. If you can see D
drive then you can very well move the datbases to D drive. If you are not
able to
see please check the access prev. of D Drive.
How to Move.
1. Detach all user databases (SP_detach_db)
2. Move it to D Drive
3. Attach the dataabases (SP_attach_db)
How to chage the default data folder:
In Enterprise manager , right click above server -- Properties
select "database setting" option, there you can chage the
Default data and Log directory.
Thanks
Hari
MCDBA
"news.microsoft.com" <deili@.yahoo.com> wrote in message
news:OrfWGmw1DHA.2680@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Hi everyone,
> I have a problem here. I install SQL Server and set it's Data Folder to C:
> drive.
> Unfortunately my C drive is running out of space and I plan to add another
> drive (D: drive).
> I want to move my database to D: drive, but however I cannot see D: drive

---

in

quote:

---

> the SQL Server Enterprise Manager
> when I try to add a new database.
> Could anyone help me?
> Best Regards,
> Deili.
>

---

How to Change Data Directory to D: drive

Hi everyone,
I have a problem here. I install SQL Server and set it's Data Folder to C:
drive.
Unfortunately my C drive is running out of space and I plan to add another
drive (D: drive).
I want to move my database to D: drive, but however I cannot see D: drive in
the SQL Server Enterprise Manager
when I try to add a new database.
Could anyone help me?
Best Regards,
Deili.Hi,
Execute the command XP_FIXEDDRIVES from Query Anayzer. If you can see D
drive then you can very well move the datbases to D drive. If you are not
able to
see please check the access prev. of D Drive.
How to Move.
1. Detach all user databases (SP_detach_db)
2. Move it to D Drive
3. Attach the dataabases (SP_attach_db)
How to chage the default data folder:
In Enterprise manager , right click above server -- Properties
select "database setting" option, there you can chage the
Default data and Log directory.
Thanks
Hari
MCDBA
"news.microsoft.com" <deili@.yahoo.com> wrote in message
news:OrfWGmw1DHA.2680@.TK2MSFTNGP11.phx.gbl...
> Hi everyone,
> I have a problem here. I install SQL Server and set it's Data Folder to C:
> drive.
> Unfortunately my C drive is running out of space and I plan to add another
> drive (D: drive).
> I want to move my database to D: drive, but however I cannot see D: drive
in
> the SQL Server Enterprise Manager
> when I try to add a new database.
> Could anyone help me?
> Best Regards,
> Deili.
>sql

how to Catalog Deploy

i created catalog in my local folder to do full text search.now iam going to
deploy our application in the website.so can u let me know how we can
deploy the catalog from my local machine to web server.
Thanks and Regards,
K.Mohan
Mohan,
Actually, the steps are the same for deploying SQL Server based Full Text
Search (FTS) on your local server as it is on your server's website.
Although, you may want to use following T-SQL stored procedures vs. the FT
Indexing wizard:
use pubs
go
sp_fulltext_service 'clean_up'
go
sp_fulltext_database 'enable' -- --> NOTE: Only run this ONCE per database
!!!
go
-- To Create/Remove the Existing Full-Text Table Index, Catalog
-- If Full-Text Index exists, DROP that Index,
-- If Full-Text Index does not exist, CREATE that Index.
use pubs
go
IF OBJECTPROPERTY ( object_id('pub_info'),'TableHasActiveFulltextIndex ') = 1
BEGIN
print 'Table pub_info is Full-Text Enabled, dropping Full-Text Index &
Catalog...'
EXEC sp_fulltext_table 'pub_info', 'drop'
EXEC sp_fulltext_catalog 'PubInfo', 'drop'
END
ELSE IF OBJECTPROPERTY (
object_id('pub_info'),'TableHasActiveFulltextIndex ') = 0
BEGIN
print 'Table pub_info is NOT Full-Text Enabled, creating FT Catalog,
Index & Activating...'
EXEC sp_fulltext_catalog 'PubInfo', 'create'
EXEC sp_fulltext_table 'pub_info', 'create', 'PubInfo', 'UPKCL_pubinfo'
EXEC sp_fulltext_column 'pub_info', 'pub_id', 'add'
EXEC sp_fulltext_column 'pub_info', 'pr_info', 'add'
EXEC sp_fulltext_table 'pub_info', 'activate'
END
For more details, see Full Text Indexing using T-SQL from a Profiler Trace
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!304.entry
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Mohan" <mohananu@.yahoo.com> wrote in message
news:%23CW$edRuFHA.1264@.TK2MSFTNGP12.phx.gbl...
>i created catalog in my local folder to do full text search.now iam going
>to deploy our application in the website.so can u let me know how we can
>deploy the catalog from my local machine to web server.
> Thanks and Regards,
> K.Mohan
>
|||You may be able to copy a built catalog to the web server. Have a look at
this kb article.
http://support.microsoft.com/default...b;en-us;240867
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mohan" <mohananu@.yahoo.com> wrote in message
news:%23CW$edRuFHA.1264@.TK2MSFTNGP12.phx.gbl...
> i created catalog in my local folder to do full text search.now iam going
to
> deploy our application in the website.so can u let me know how we can
> deploy the catalog from my local machine to web server.
> Thanks and Regards,
> K.Mohan
>

How to call stored procedure to table?

Hi....

I have problem and I need your help

I stored a procedure in the Projects Folder in my computer

and I want to return the procedure result in a column inside table

How I can do that???

thank you

Please any one can help me??????????

|||

Hi,

From your description, you have mentioned that you want to return the procedure result in a column.

Well, do you mean you want to select a column from your database with several rows, and then get these rows from your stored procedure, right?

If so, you can execute a procedure that return rows. To execute a stored procedure that returns row, you can run a TableAdapter query that is configured to run a stored procedure (for example, CustomersTableAdapter.Fill(CustomersDataTable)).

If your application does not use TableAdapters, call the ExecuteReader method on a command object, setting its CommandType property to StoredProcedure. ("Command object" refers to the specific command for the .NET Framework Data Provider that your application is using. For example, if your application is using the .NET Framework Data Provider for SQL Server, the command object would be SqlCommand.)

For more information, see
http://msdn2.microsoft.com/en-us/library/d7125bke(VS.80).aspx

Thanks.

How to browse for folder on SQL Server's machine?

Hi!
I know there is xp_dirtree, xp_subdirs, xp_fixeddrives procedures which
allows me to write a folder select dialog. But i have seen such dialog
somewhere in one of installers. Unfortunately I dont remember in what
installer.
I dont want to reinvent a wheel. So my question is: may be there is method
somewhere in SQL Server or in SQL-DMO or in DTS which implements a folder
selection dialog based on these stored procedures?T-SQL, DMO and DTS do not present the user with GUI objects. They provide
information, and your application must present that information is a dailog.
"Igor Solodovnikov" <IgorSolodovnikov@.discussions.microsoft.com> wrote in
message news:op.suvw7dy0n8ihmu@.iw2k.helpmicro.local...
> Hi!
> I know there is xp_dirtree, xp_subdirs, xp_fixeddrives procedures which
> allows me to write a folder select dialog. But i have seen such dialog
> somewhere in one of installers. Unfortunately I dont remember in what
> installer.
> I dont want to reinvent a wheel. So my question is: may be there is method
> somewhere in SQL Server or in SQL-DMO or in DTS which implements a folder
> selection dialog based on these stored procedures?|||Ok, thank you for information. I must ask my question another way: Do you
know any library implementing such a dialog?

> T-SQL, DMO and DTS do not present the user with GUI objects. They provide
> information, and your application must present that information is a
> dailog.
> "Igor Solodovnikov" <IgorSolodovnikov@.discussions.microsoft.com> wrote in
> message news:op.suvw7dy0n8ihmu@.iw2k.helpmicro.local...
>|||You have several options depending on what application development tool you
are using. This perhaps more a C# or Visual Basic question than SQL Server.
There is the Win32 API call SHBrowseForFolder
http://support.microsoft.com/defaul...kb;en-us;179497
How to write a managed C# wrapper for the API dialog call:
http://support.microsoft.com/defaul...kb;en-us;306285
Here is a Visual Basic sample:
http://www.vbexplorer.com/VBExplore...file_dialog.asp
I also think there is a method of the File System Object called
GetFolderName
http://msdn.microsoft.com/library/d...objectmodel.asp
"Igor Solodovnikov" <IgorSolodovnikov@.discussions.microsoft.com> wrote in
message news:op.suv0e6u4n8ihmu@.iw2k.helpmicro.local...
> Ok, thank you for information. I must ask my question another way: Do you
> know any library implementing such a dialog?
>
>|||As I mentioned in my original posting I can implement such dialog using
xp_dirtree, xp_subdirs, xp_fixeddrives procedures. But SQL Server's
Enterprise Manager already has such dialog. For example if you open
"Attach Database" dialog and then press browse [...] button Enterprise
Manager will show you "Browse For Existing File" dialog. And as you can
see all the path information in that dialog is local to SQL Server's
machine. This is what SHBrowseForFolder cannot do for you because
SHBrowseForFolder always shows paths relative to local machine.
My question is: Is there any way to reuse that Enterprise Manager's
"Browse For Existing File" dialog? Or I am obliged to reinvent it?
On Tue, 02 Aug 2005 19:30:46 +0300, JT <someone@.microsoft.com> wrote:

> You have several options depending on what application development tool
> you
> are using. This perhaps more a C# or Visual Basic question than SQL
> Server.
> There is the Win32 API call SHBrowseForFolder
> http://support.microsoft.com/defaul...kb;en-us;179497
> How to write a managed C# wrapper for the API dialog call:
> http://support.microsoft.com/defaul...kb;en-us;306285
> Here is a Visual Basic sample:
> http://www.vbexplorer.com/VBExplore...file_dialog.asp
> I also think there is a method of the File System Object called
> GetFolderName
> http://msdn.microsoft.com/library/d...objectmodel.asp
>
> "Igor Solodovnikov" <IgorSolodovnikov@.discussions.microsoft.com> wrote in
> message news:op.suv0e6u4n8ihmu@.iw2k.helpmicro.local...
>