How To Change The Default Backup Data Directory In SQL Server 2000?

Dec 29, 2006

Hi,

I had SQL Server 2000 installed like below:

Installation Directory = C:Program FilesMicrosoft SQL Server

Default data directory = D:

Default log directory = D:

I found out that the data is stored in D:Program FilesMicrosoft SQL ServerMSSQLdata and backup is done in D:Program FilesMicrosoft SQL ServerMSSQLackup

However, Microsoft says we should never put the ..ackup 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_MACHINESOFTWAREMicrosoftMSSQLServerMSSQLServer

..BackupDirectory=C:Program FilesMicrosoft SQL ServerMSSQLackup (<-- note it is pointing to C: not D:)

..DefaultData=D:

Can somebody help please?

View 3 Replies


ADVERTISEMENT

Change Backup Default Directory

Aug 30, 2002

WE HAVE RECENTLY MOVED THE MSSQLBACKUP FOLDER TO EMC DRIVES E:, BUT SQL IS STILL DEFAULTING BACKUPS TO THE ORIGINAL DEFAULT LOCATION WHICH WAS ON THE D: DRIVE. DOES ANYONE KNOW HOW TO CHANGE THIS? I KNOW THE ORIGINIAL LOCATION IS STORED IN THE REGISTRY, BUT I DON'T KNOW IF I CAN CHANGE THE REGISTRY OR IF THERE IS A DIFFERENT PLACE. CAN SOMEONE PLEASE HELP.

THANKS

View 2 Replies View Related

How To Change Default Data Directory For SQL 2005

Sep 29, 2006

Hi, I've installed SQL 2005 and failed to change the default datalocation during installation. Now I want to change it. Can I do thiswithout having to uninstall and reinstall? And furthermore, is there asystem stored procedure I can use to move existing data and log files(master, msdb, AdventureWorks, etc.)? I seem to remember doing justthis with SQL 2000 some time back.Thanks,Eric

View 2 Replies View Related

How To Change Default Database 'root Directory' ?

Feb 27, 2006

Hi folks,

Can you please tell me how should I change the default root directory (used for db storage) in SQL 2005 Std. Edition. ?

Currently its configured to:
C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL
and I want to change the drive letter here.

Thanks in advance.
Cyk

View 4 Replies View Related

SQL Server Default Data Directory

Jun 2, 2006

I would like to know how to change the default data directory in SQL Server Express. I have a partitioned hard disk with SQL Installed on the C: drive. I would like to move the data to d: drive. I have just installed SQL and do not have any databases. I have installed SQL with advanced services.

Thanks in advance for your response.

View 1 Replies View Related

HOW TO FIND BACKUP FILE IN DEFAULT DIRECTORY

Dec 17, 2007



I am getting a backup file in default directory by using this script







Code Block
BACKUP DATABASE CPHONE
TO DISK = 'CPHONE.Bak' -- send to default pathaway
WITH FORMAT,
NAME = 'Full Backup of CPHONE'






Is there any way to find where is the default path of the SQL server, in order to show to the user from a VB program in which pathway the database back up is stored




View 4 Replies View Related

Creating A Database In MSSql 2000, But NOT In The Default Directory

Sep 28, 2002

I am trying to find a solution to a thorny problem I have encountered...

I am a newbie in Sql Server 2000, but have quite a bit of experience in databases, both on PC and mainframe.

I am currently writing a program, which needs, among other things, to create Sql Server databases, to be located NOT in the default Mssql2000 directory, but in the user's home directory.

I am using an ADO connection and trying to execute it, as follows:

The text of the relevant part of the program goes like this:

Dim strCreateDatabase As String
Dim MyCn As ADODB.Connection

Set MyCn = New ADODB.Connection

With MyCn
.ConnectionTimeout = 25
.Provider = "sqloledb"
.Properties("Data Source").Value = "MOP"
.Properties("Initial Catalog").Value = ""
.Properties("Integrated Security").Value = "SSPI"
.Open
End With

strCreateDatabase = "Create Database Rimon on (" _
& "Name = Rimon, Filename = '\mopusersMR_DiazsalpmsdataRimon_Data.mdf', " _
& "Size = 20, MaxSize = 100, FileGrowth = 5) " _
& "Log on (Name = 'Rimon', Filename = 'c:usersMR_DiazsalpmsdataRimon_Log.ldf', " _
& "Size = 2, MaxSize = 10, FileGrowth = 1)"

MyCn.Execute strCreateDatabase


Both the name of the Sql Server and the Win2K server is MOP.

All the directories exist, and I have full access to them.

When I try to run this command, I get the following message:

File
'\mopusersMR_DiazsalpmsdataRimon_Data.mdf' is on a network device not supported for database files.

When I omit this location, it works just fine.

If I run it on the server, using another directory, it works just fine.

Do you have any idea what I am doing wrong?

Thanks in advance for any help!
Rimon Rafiah

View 1 Replies View Related

How To Set Default Data && Log Directory

Mar 10, 2005

I tried to add volume to default data & log directory of "new data default location" of database setting at SQL server properties, but it never be saved, when I get in properties again, the setting is disappear.
Does somebody could advise me how to save it?


Thank you
Ting

View 1 Replies View Related

How To Change Default Backup Destination To The Other Folders ?

Aug 4, 2007

By default,when I would like to back up any database by SSMS.
default destination on disk will be C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackup   every time 
 Although I everytime change it to my backup folder.
 
Please let me know how to change it.

View 1 Replies View Related

Default Data Directory And Access Upsizing

Apr 15, 2006

I've created a number of very large databases in Ms Access that I would like to upsize to SQL Server Express. I changed the default data directory location to point to a separate drive with over 400GB of free space on my home pc. However, everytime I try to upsize or link databases using the MS Access Upsizing Wizard the data is saved in the Data directory on my current hard drive and is not saved in the default location I specified. If I create a new database in SQL Express it is saved to the new location. Any ideas on how to force upsizing or uplinked data to go to the new default data location?

View 1 Replies View Related

How Can I Change The Default Save-As/Save Directory

Jun 26, 2007

I am new to sql sever management studio express, but a long time query analyzer user. This is a very basic question.



I want to change the default directory in sql server management studio express so that when I go to save a query, it is already pointed to the correct one. Where do I change that?



Thanks,

Nanci





View 2 Replies View Related

How To Get The Default SA Account Password And How To Change It In Sql 2000 Personal Edition ?

May 9, 2005

hi, all:
         I installed a Sql 2000 Personal Edition in my Laptop,now I want to change my Sql 2000 sa account password.
         but I can not find where I can get the default password for sa account and how to change it .
 
         thanks

View 1 Replies View Related

T-SQL (SS2K8) :: Getting Directory Information For Data / Log / TempDB / Backup

Nov 18, 2014

Is there an Admin script that yields the following directories?

Data
Data Root
Log
TempDB
Shared Feature
Backup

View 4 Replies View Related

SQL Server 2008 :: Count Number Of Backup Files Deleted From A Default Backup Location

Mar 13, 2015

I've written a custom script to delete backup files from location. But unable to modify now to count the number of files are deleted. How to modify the script...

/* Script to delete older than N days backup from a specific directory */

USE [db_admin]
GO
IF OBJECT_ID('usp_DeleteBackup', 'P') IS NOT NULL
DROP PROC usp_DeleteBackup
GO

[Code] .....

View 2 Replies View Related

DB Design :: Cannot Change Datetime Default Data Type

Nov 17, 2015

Ilve install sql server 2012 in my pc and i want to change datetime default format. How can i do this and please i dont want to take the result from select convert() or select cast or something like this. I ve want to take the format i want writing query select datecolumn from table. 

Now the format i have is: 2015-11-16 09:04:00.000

And i want this format: 16.11.2015 09:04:00

Is any way to convert automaticaly by select only column? or can i change at all once? or must write function to when i select the column can change automatic ? or another thing, i ve see in column properties something like formula. In computed column specification in formula i wrote this: 

((CONVERT([varchar](10),getdate(),(104))+' ')+CONVERT([varchar](10),getdate(),(108)))

And I take the format i want automaticaly but i get the current date for all rows and i cant edit or insert that column anymore. So, how to change the format of date time but no from select query.

View 4 Replies View Related

How To Backup Data In Sql Server 2000

Mar 4, 2008

Hai friends,
I am using sql server 2000 as a Backend. I have stored nearly 50 user tables.Now i need backup that(export) , and i need to put that in another pc where sql server 2000 installed(import).
i have a "master" database.
do u have any simple steps , pl reply to me....
THANK U in ADVANCE
Ambrose
5 March 2000 

View 4 Replies View Related

How Do I Change The Default Database In Sql Server

Dec 19, 2007

Does anyone know how to change the default database in sql server 2005, I keep getting an error.

View 1 Replies View Related

Change The Server Name Does Not Default Usenames

Sep 18, 2007

Hi :
I am in the process of testing server name on which sqlserver is running.In this process i could sucessfully rename the server and bring the database online.I have renamed the server name in the system tables by using sp_dropserver and addserver .But still in the security logins i can see the agent logins not reflecting the change .
Oldservername SQLServer2005SQLAgentUser$oldservername$MSSQLSERVER.How can i modify these names.
thanks
Ganesh

View 2 Replies View Related

SQL Server 2000 And Active Directory

Nov 17, 2006

It's me again guys. First let me say that I truly appreciate the knowledge that I see on this forum. Now on to my question.

How can I use SQL Server to import the active accounts from our Exchange Server? Thanks.

Everything I know I learned from the web.

View 7 Replies View Related

Please Help Me! How Do You Change The Default Server In Visual C# Express?

Jun 14, 2006

I have had visual C# Express edition for a while, and had successfully used SQL Server 2005 express successfully since it was first released. I downloaded the new version with advanced services, created a new instance, and tried to use it. VC# wont connect cause it is still trying to connect to my old instance which no longer exists. Could someone please tell me how to make my new instance the one to log on to?

Thanks in advance,

Kent

View 3 Replies View Related

Searching Active Directory With SQL Server 2000

Nov 3, 2004

I was wondering how exactly one can search the Active Directory with SQL Server 2000. There is all kind of stuff on the Internet about how to connect them and how useful it is to connect them together - plus how you can use Active Directory to query SQL Server, but I`ve never encountered any article where they explain how SQL Server can search the Active Directory.
I`m thinking about how one has their user-database in Active Directory with all users in groups, but only uses groups in SQL Server. How exactly can SQL Server figure out how a user belongs to a group?
Does Active Directory tell SQL Server about that when the user tries to access something from SQL Server, or does SQL Server already knows which group is accessing it as soon as the user authenticates in Active Directory?

View 7 Replies View Related

Accessing Active Directory Using SQL Server 2000

Jul 20, 2005

Hello All,I have a linked ADSI Server to our company Active Directory andeverything is fine. I'm running queries and such using LDAP. BUT howcan I aquire a list of attributes for the classes and categories. Suchas if i wanted to see a user's telephone number and email address foruse in a corporate database? Is this possible?Thanks

View 4 Replies View Related

SQL Server 2000 And Active Directory Groups

Mar 19, 2008

Hi all,

just want to confirm this:

We have a department and want to grant them access to various databases on a sql server 2000 instance. Can we create an AD group for these users and just add the group as a login to SQL Server? Or do we have to create a db user for each member of the group and map the logins one by one?

Thanks

View 1 Replies View Related

How Do I Change The Default Setting From Connecting To SQL Server 2005?

May 2, 2006

Hi there!
My provider does not yet support SQL Server 2005, they are still on SQL Server 2000. I put together a very basic login page, but it is crashing everytime I access it because of this problem, and sadly I am not advanced enough as yet to know how to remedy it! Can you help?
Here is the error page, some of it; - Any ideas gratefully received!
Russ.

Server Error in '/' Application.


An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)Source Error:



An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

View 1 Replies View Related

Connot Create Default Directory??????????

Apr 23, 2007

I went ahead and installed RS even though it was clear Microsoft install was confused (re: preivous post). Now I get to experience the thrill of configuration hell.



Report Server Virtual Directory had a green check. Unfortunately the Name block says <Not Set>. When I try to create a New... default directory I get the error message: "The virtual directory could not be created. The previously set virtual directory will still be used."



Does anyone know where the <Not Set> virtual directory is stored on the computer?











addl info:



ReportServicesConfigUI.WMIProvider.WMIProviderException: The virtual directory specified already exists. Specify a different name.


at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.CreateVirtualDirectory(String virtualDirectory, String path)



(Vista Ultimate)

View 6 Replies View Related

DB Engine :: Default Directory For Backups? 

Oct 9, 2015

I'm requesting of our dba that he create a database with recovery simple for my peer and I to start using. I'm asking him to give us db owner on this db so we can create schemas, tables, views, procs, do table inserts, deletes etc etc. what sql permission (if any) would allow my peer and I to do a backup once in a while to the default sql directory for backups?  And for that matter a restore from there. 

View 7 Replies View Related

SQL Server 2000 && Active Directory: Problems Querying

Jul 20, 2005

Hello --I'm trying to get SQL Server 2000 on a Windows 2000 Server to be ableto query an Active Directory. We've got two domain servers one Win2000and one Win2003. However, I'm having problems:I've run the following query to setup the linked server:sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces','ADSDSOObject', 'adsdatasource'Using Enterprise Manager I've changed the login details for the ADSIlinked server to:Be made using the following security context:Username: MyDomainAdministratorPassword: ****I've then tried to run the following query in Query Analyzer:SELECT * FROMOpenQuery(ADSI,'<LDAP://CN=Users,DC=MyDomain,DC=com>;(&(objectCategory=Person)(objectClass=User));name,ad spath')But get the following error message:Could not execute query against OLE DB provider 'ADSDSOObject'.Does anyone have any ideas on what this message means or how to getthis working?Thanks--James Allanjames [at] allan-home.co.uk

View 2 Replies View Related

Cannot See A Newly Installed SQL Server 2000 (No Active Directory)

Jul 20, 2005

We have just installed a SQL Server 2000 (SP 3A) onto a computer thathas Windows-2003 Server on it. Now, we cannot get access to thatdatabase server from other computers. Seem like this may be an issuewith Active Directory.Our network consists of Windows-2000 Servers (SP 4) and Windows-2000workstations and Windows-XP workstations. We also have SQL Server 2000(SP2) in three Windows-2000 servers. All work fine. Recently, we get anew server that has already had Windows-2003 Server pre-installed.After we have installed SQL Server 2000 (SP 3A) on the new server, wefind that we cannot get access to that new database server from othercomputers.I have tried connecting to that new database server using QueryAnalyzer (using both the sa account and Windows Authentication), and Iget this error message:Unable to connect to server NEWDBSRV:Server: Msg 17, Level 16, State 1[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does notexist or access denied.I have tried connecting to that new database server using ISQL (usingboth the sa account and Windows Authentication), and I get this errormessage:DB-Library: Unable to connect: SQL Server is unavailable or does notexist.Unable to connect: SQL Server does not exist or network accessdenied.Net-Library error 53: ConnectionOpen (Connect()).I have also tried to register the new database server using EnterpriseManager (again, using both the sa account and Windows Authentication),and I get this error message:NEWDBSRV - SQL Server does not exist or access denied.ConnectionOpen(Connect())The only way that I can get access to the new database server is toopen Enterprise Manager directly on the new server.I have compared the properties of the new database server and those ofthe other existing database servers. The only differences are:-Other database servers are running Windows-2000. The new databaseserver is running Windows-2003.-The new database server is missing the "Active Directory" page-tagwhen I check its properties using Enterprise Manager. We use ActiveDirectory in our network, and the Active Directory is being run from aWindows-2000 server (that is currently also a database server).According to our network administrator, he has already put the newserver into our Active Directory. He doesn't know why it is missingthe Active Directory page-tag. He believes this has something to dowith its having Windows-2003 that is different from other servers(that have Windows-2000).Thanks in advance for any info.Jay Chan

View 2 Replies View Related

Authenticating Against LDAP/Active Directory With SQL Server 2000 SP2

Jun 4, 2007

Hello everyone,

We have a custom application that connects remotely to a SQL Server 2000 (SP2) database. We would like our application to validate a user's login against Active Directory.

So far I have been able to get a lookup working, but I can not find documentation on how to validate the password from within SQL Server. I found a lot of notes on using ASP.NET objects, or VB, C#, etc, but for this solution it must be done entirely in SQL. It would also be nice if this were SQL Server 2005; we could just embed the C# code and call it as a SQL stored proc, but unfortunately we are left with the constraint that we cannot upgrade this DB at this time.


Linked server 'ADSI' is set up with the sp_addlinkedserver command:

EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active
Directory Services 2.5', @provider=N'ADsDSOObject', @datasrc=N'adsdatasource'


Two table functions in our test DB (trying to test both ways I've found in docs):

ALTER FUNCTION [dbo].[GetAuthenticatedUserViaLDAP]
(
-- Add the parameters for the function here
@userId nvarchar(50),
@password nvarchar(50)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT [SAMAccountName], CN [Name], SN [Last Name], ST State
FROM OPENQUERY( ADSI,
'<LDAP://DC=company,DC=com>;((objectClass=user));SAMAccountName,cn,sn,st')
WHERE [SAMAccountName] = @userId
)

ALTER FUNCTION [dbo].[GetAuthenticatedUser]
(
-- Add the parameters for the function here
@userId nvarchar(50),
@password nvarchar(50)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT [SAMAccountName], [Name], SN [Last Name], ST State
FROM OPENQUERY( ADSI,
'SELECT SAMAccountName, Name, SN, ST
FROM ''LDAP://bdsserver1/ CN=users,DC=company,DC=com''
WHERE objectCategory = ''Person''
AND objectClass = ''user'' ')
WHERE [SAMAccountName] = @userId
)


So calling either of these table functions from our custom application gives the same result:

select * from dbo.GetAuthenticatedUser('astonaker','abc')
OR
select * from dbo.GetAuthenticatedUserViaLDAP('astonaker','abc')


ResultSet:

'astonaker', 'Anthony', 'Stonaker' 'NULL'


So I can at least tell if a given user exists or not, but I have no visibility into whether the password they entered into our application is valid in LDAP.

I don't want to pass unencrypted passwords through the network, but then I don't know how to encrypt/compare these passwords without using the .NET Connection or DirectoryEntry, etc objects.

Any thoughts/suggestions are greatly appreciated!

View 5 Replies View Related

Changing Default Directory For Stored Procedures

Oct 21, 2006

I want to change the default directory for my stored procedures. It is extremely inconvenient for me that they are placed in documents and settings... I cannot find a place in the Tools where I can change it.

Thanks.

View 5 Replies View Related

DirectReports-- How To Extract This Active Directory Attribute Value In MS SQL 2000 Server

Nov 22, 2007

hi,
I am trying to run a query analyzer via query against ad. Below is the error I am getting. How to extract the directReports attribute values from windows 2003 active directory


SELECT * FROM OPENQUERY( ADSI,
'SELECT directReports FROM ''LDAP://192.168.1.24/dc=xxx,dc=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user'' order by name')

Err: Server: Msg 7346, Level 16, State 2, Line 1
Could not get the data of the row from the OLE DB provider 'ADsDSOObject'. Could not convert the data value due
to reasons other than sign mismatch or overflow.

plz do the need full

thanks & regards
venkey

View 3 Replies View Related

How To Change The Root Directory?

May 23, 2007

Hello,



I was wondering if it were possible to change the Root Directory on an SQL Server 2005 install?



expl: current = C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL



desired: D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL

View 7 Replies View Related

Can Windows 2003 Server Backup Utility Be Used To Backup A SQL 2000 Dbase

Dec 28, 2007

Windows 2003 backup utility uses the shadow copy option that allows it to copy open files.
Therefore, can I use this utility to backup the .mdf and .ldf files for my SQL 2000 database?
I can then attach the .mdf files if I need to restore the database to another server.
Can anyone tell me if this is safe? I've tried it and it worked but I'm worried there maybe some lurking danger in using this approach.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved