Changing Path Of Data && Log Files On The Fly
Dec 4, 2004
We have a SQL Server setup as a publisher to 15 subscribers. We need to change the path of the data & log files to a new drive (added a new harddisk). We plan to take a cold backup of the database and shift the data & log files to the new drive. Then we just attach the data & log files from the new path.
Will this disturb my existing replication Setup?
Is the the correct procedure for changing the path of the existing data & log files?
What is the appropriate method for shifting data & log file of a live database to a different location (directory/drive) ?
thanks in advance
View 2 Replies
ADVERTISEMENT
Jan 28, 2008
Hi All,
Can plz any one help me, acutally as i know sys.files table gives you the acutally physical location of the database fiels with name as well like this
C:sqldatax.mdf
but what i m looking is, is there any qury or script that will give me only the path of the data,log, index files like this
C:sqldata
Thanks and looking forward.
-MALIK
View 5 Replies
View Related
Oct 1, 2014
I have a Windows Server 2012 R2 2 node cluster with SQL Server 2014 FCI installed. Data files are on a separate Windows Server 2012 R2 file server. Data files share has been permissioned to the SQL Server service and SQL Server Agent service accounts as Full Control. NTFS Permissions are Full Control.
When I try to attach a database
CREATE DATABASE AdventureWorksDW2012
ON (FILENAME = 'apricotmssql_VIOLETMSSQL12.MSSQLSERVERMSSQLDATAAdventureWorksDW2012_Data.mdf')
FOR ATTACHI get this error:
Msg 5120, Level 16, State 101, Line 4
Unable to open the physical file "apricotmssql_VIOLETMSSQL12.MSSQLSERVERMSSQLDATAAdventureWorksDW2012_Data.mdf". Operating system error 5: "5(Access is denied.)".
If I log into the file server (called APRICOT) and look at the NTFS permissions they all look good. I have also reapplied the NTFS permissions from the root folder down.
EDIT
If I log on to one of the nodes in the cluster as the SQL Server service account and navigate to apricotmssql_VIOLETMSSQL12.MSSQLSERVERMSSQLDATA and copy and paste the data file, it works fine.
EDIT2:
If I log on to the file server and Enable Inheritance at the root level, then Replace all child objects with inheritable permission entries from this object, I get this error:
User Account Control settings on all nodes and the file server are set to Never notify
View 0 Replies
View Related
May 26, 2004
Hi,
I have a sql server 7 running on a machine with two disk partitions (D: and E:).
The data files xxx.mdf and xxx.ldf are stored in D:, which has very few space available. I want to copy these files to E: but I get an error saying that it is not possible to change the source file of a database. Is it possible to do it or do i have to create another data file in E: and keep the old one in D:?
Thanks in advance,
browser
View 3 Replies
View Related
Nov 14, 2007
Hey guys,
I uploaded my website from VWD Express to ipower. My database did not work, which I have learned upon some research should have not been a shock to me, but most definetely was. When I contacted tech support they told my that my web.config files were pointing to my local paths. That I should change them and I should be fine. Elsewhere, in the knowledge base I found the strConnect that I am under the impression I need to use.
strConnect = "Provider=SQLOLEDB; Server=SQL-A1; UID=account_username; PWD=account_password; Database=user_db_name" oSQLServer.Connect strServer,strLogin,strPwd
The problem is, I'm a complete newbie, learning as I go and I can't find anything on where or how I insert this into my web.config to correct my paths. IPower will not help with coding. I know my current connection string is in web.config in the appsettings, that's about as far as my knowledge goes.
So, could anyone show me how I go about putting this into my web.config? Examples are very helpful :]
On a side note, I've been reading that this is a common problem among newbies like myself. So, is changing these files actually going to work, or am I wasting my time? Also, before I get knee-deep into it, is the database-publishing kit put out by MS the way to go here?
Thanks for any and all replies, you guys are always awesome.
View 6 Replies
View Related
Apr 30, 2008
Before I launch into a long description of the issue, I was wondering whether its possible to dynamically amend the file path held in a configuration string? (I'm looking at the Package Configurations Organizer)
Basically recreating the /CONF switch in the dtexec utility
So a parent package could somehow tell a child package to pull its configuration from a selection of different xml config files at run time... I couldn't see a variable in the child package to set from the former
My only idea at the moment is to replace the Execute Package Task with an Execute Process Task, and directly invoke the child using dtexec & an expression to dynamically set the /CONF switch..
thanks
James
View 3 Replies
View Related
Jan 22, 2007
Can someone tell me why path to the configuration file couldn't be relative to the package? When I try to use relative path (i.e. do not specify full path to the configuration file) package then run from BIDS looks for configuration file in the solution folder instead of package's folder (project folder).
Is there ane way to force package use same folder to search for caonfiguration files?
Thanks
View 6 Replies
View Related
Nov 5, 2007
Hi,
Trying to dynamically set the connectionstring property of the excel source.
This is what I enter instead of the hardcoded excel file paths:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::FileName] + ";Extended Properties=Excel 8.0;HDR=YES"
I get this error, every time I set the delay validation property of the dataflow tab to true.
Cannot detach from one or more processes.
The object invoked has disconnected from its clients
Do you want to terminate them instead?
Thanks
View 3 Replies
View Related
Mar 19, 2008
Hi,Is there a way to read a trace file with fn_trace_gettable ifthat trace file resides in a path that contains a space? For example,test.trc is accessible with fn_trace_gettable if it resides in c: races, but throws an error if the trace file is in c:my traces.Thanks.-Nada
View 1 Replies
View Related
Sep 1, 2006
I have two, probably related, problems:I have a very simple site. It will be used on the local intranet only. I want to use the windows authentication so users do not have to log on. The aspnetdb has been created using the configuration tool. If I use Server Explorer in vs to work on the site on my local machine, I can open aspnetdb, look at its tables, etc. If I use Server Explorer in vs to work on the site on the server, aspnetdb will not open and gives this error message: ..."network path that is not supported for database files"...Other sites on the same server give the same message, but the sites work. I think this is because they can read the file but not write it; but that is a guess. We have tried everything; my head is ready to explode. We are using SQLServerExpress. It resides on the server's C drive (as does our data database). The web site, and hence the aspnetdb are on the F: drive of the same machine. Does SQLServerExpress treat the F: drive as a UNC drive? If so, are we in deep doo doo? I'm at a loss.As far as logging in: If I debug from vs, the login works beautifully both on my local machine and running from the server using vs. Login does not work when accessing the site from iexplorer. Mystified by that one, too. I tried to implement profiles and they crash, too; that led me to consider a write problem.Don't know what to do; any help is appreciated.
View 1 Replies
View Related
Nov 23, 2005
Hello,
Completely new to this stuff, so sorry if I'm asking
something that is painfully simple to resolve!!! Also, not sure whether
there are standard formats for code, errors etc.
OK. When I create a Login.aspx file in VWD Express on my local Hard Disk Drive, the file works and runs correctly, allowing
me to log in etc.
However, when I do exactly the same thing using a remote Network Drive connection to an Intranet server, when you enter the
Username and Password, and Click the Log In Button, the following error message appears:-
The file "[Network Drive]AuthorsApp_Dataaspnetdb.mdf" is on a network path that is not supported for database files.An
attempt to attach an auto-named database for file
S:AuthorsApp_Dataaspnetdb.mdf failed. A database with the same name
exists, or specified file cannot be opened, or it is located on UNC
share.
Then I get the following information - which I'm sure is supposed to help, but doesn't!!!
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:
The file "S:AuthorsApp_Dataaspnetdb.mdf" is on a network path that
is not supported for database files.An attempt to attach an
auto-named database for file S:AuthorsApp_Dataaspnetdb.mdf failed. A
database with the same name exists, or specified file cannot be opened,
or it is located on UNC share.
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.
Stack Trace:
[SqlException (0x80131904): The file "S:AuthorsApp_Dataaspnetdb.mdf" is on a network path that is not supported for database files.An attempt to attach an auto-named database for file S:AuthorsApp_Dataaspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +115 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +56 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +1083 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +272 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +687 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +82 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +558 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +126 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +651 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +160 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +122 System.Data.SqlClient.SqlConnection.Open() +229 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +114 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +225 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +157 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +68 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +100 System.Web.UI.WebControls.Login.AuthenticateUsingMembershipProvider(AuthenticateEventArgs e) +100 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +113 System.Web.UI.WebControls.Login.AttemptLogin() +178 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +134 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +56 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +107 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +178 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +31 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +72 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3838
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
All assistance greatly appreciated!!!
View 1 Replies
View Related
Dec 13, 2013
I'm getting a rule check fail "Long path names to files on SQL Server Installation media failed" when installing SQL 2012 Standard edition from a network share.
View 8 Replies
View Related
Oct 27, 2015
I have created a FTP Task which is returning the value, "Error: 0x0 at FTP Task, SFTP Task: Invalid local path."
In my DB Table, the table contains a field for the location of files to be Ftp'd which is: C:/temp/ftp/OB/*
I believe my settings are correct and am curious to know if the above string should work or if I need a different syntax for the local variable.
View 0 Replies
View Related
Apr 23, 2015
SET NOCOUNT ON
Declare @daysOld int,@deletedate nvarchar(19) ,@strDir varchar(250)
declare @cmd11 nvarchar(2000)
declare @mainBackupDir varchar(2000),
@result1 nvarchar(max);
[Code] ....
View 1 Replies
View Related
Jun 3, 2015
I wrote the below script to print all folders and files located in the share path. How to extend my script to mention by adding another column whether the file is a folder/file , sort of 0 or 1.
declare @chkdirectory1 varchar(4000) = 'shared_pathfolder';
declare @finalserver3 varchar(4000);
create table #tmp (directory_name varchar(4000))
SET @finalserver3 = '''"DIR ' + @chkdirectory1 + ' /B"''';
--select @finalserver3
--SELECT @finalServer
DECLARE @ExecCmd varchar(100)
--SELECT @ExecCmd = 'EXEC master.dbo.xp_cmdshell ' + char(50) + 'mkdir D:'+ CONVERT(varchar(8), getdate(), 112) + '' + char(50)
SET @ExecCmd = 'EXEC master.dbo.xp_cmdshell ' + @finalserver3
--SELECT @ExecCmd
exec(@ExecCmd)
drop table #tmp
View 0 Replies
View Related
Jul 29, 2015
In SQL Studio, I can go to the restore window and the click "verify backup media". This would check the restore plan listed in this window and check if some of the file are missed.Is there any way to reach this with TQSL? I know there is a "restore verify" command, but this will only verify one backup/file and not the complete restore path. I'm looking for a TSQL solution which is able to control that all necessary backup files are still present on the file System and not moved or deleted (e.g through cleanup task).
View 3 Replies
View Related
Aug 31, 2007
Hi
After install the mdf and log files are stored in the Program Files directory tree.
How do I change the place were they are stored to a path of my own choice, so that new DBs get created there too?
I've looked though the docs, but I can't find this.
Thanks
John
View 1 Replies
View Related
May 14, 2007
Hi, all experts here,
Thank you very much for your kind attention.
I am trying to modify the files path (primary file, log file) of databases, but it looks like I am not able to mofidy their files path directly from the database property dialogue? Would please any experts here give me some ideas on what else can I try to figure it out? Thanks a lot in advance and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
View 5 Replies
View Related
Sep 2, 2015
Currently have a single hard coded file path to the SSRS config file which parses the file and provides the reporting services web service url. My question is how would i run this same query against 100s of servers that may or may not share the same file path as the one hard coded ?
Is there a way to query the registry to find the location of the config file of any server ? which could be on D, E, F, H, etc.
I know I can string together the address followed by "reports" and named instance if needed, but some instances may not have used the default virtual directory name (Reports).
Am I going about this the hard way ? Is there a location where the web service url exists in a table ? I could not locate anything in the Reporting service database. Basically need to inventory all of my reporting services url's.
View 2 Replies
View Related
Apr 16, 2008
When trying to install Business Contact Manager (BCM) for Outlook 2007, the setup failed and I was refered to a log file in my Local Settings/Temp folder. The log actually says that Business Contact Manager was installed sucessfully! BCM is supposed to install SQL Express 2005 as an instance or as instance if SQL Express is already installed. There is an MSSMLBIZ instance in Services..
Who can I send the Log File to for analysis and the fix feedback?
When I first went into Computer Management and clicked on Services and Applications in the left panel, the error message appeared "Snap-in failed to intialize. Name: SQL Server Configuration Manager CLSID:{CA9F8727-31DF-41D2-975C-887D84903967} This message diappeared when I clicked on Services and Applications again. Under Services, there are 3 SQL services - one is an application that was uninstalled 3-4 weeks ago and I disabled this service. The other 2 are: SQL Server (MSSMLBIZ) and the other one is SQL Server (SQLEXPRESS) When I tried to start either of the last 2, the message appeared: Services "Could not start the SQL Server (MSSMLBIZ) service on Local Computer. Error 3: The system cannot find the path specified. Under Program Files/Microsoft SQL Server/MSSGL.1 folder is mostly empty. So, it seems like the Path in the Registry is not valid and that nothing is being installed in the MSSQL.1 folder. If so, how do I fix this?
How do I get the BCM SQL instance to install and run properly? what do the messages in Services mean and how do I resolve these.
Thank you!
Gary
View 3 Replies
View Related
Oct 4, 2007
After updating TempDB path to a wrong path (without file name only folder name) the service is not starting. How can i sovle this and start the service
thanks
Leena
View 13 Replies
View Related
Jan 9, 2015
I proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.
View 2 Replies
View Related
Dec 7, 2000
When a new database is created in enterprise manager the path for the database and transaction logs are defaulted to d:mssql7data or wherever you installed SQL Server to initially. Is there a way to change the defaults to place the data on one drive and the logs on a different drive?
View 1 Replies
View Related
Apr 24, 2014
I have some T-SQL that generates HTML code by using the FOR XML PATH functionality. Once the HTML is built, I am using the following T-SQL to convert it to XML so I am not limited by the output
SELECT @vHTML AS [processing-instruction(x)]
FOR XML PATH('')
where @vHTML is varchar(MAX)
Now, when I run my code in VB.NET and use the ExecuteXMLReader() command, I get nothing back into the XML Reader. I noticed that I needed the XMLDATA directive also, but when I add it, I get the following error
Msg 6860, Level 16, State 1, Line 247
FOR XML directive XMLDATA is not allowed with ROOT directive or row tag name specified.
View 1 Replies
View Related
Aug 29, 2006
Hello, This might be a simple thing, but I'm not able to find out the solution.
We have an application that accesses data from a SQL database. This works as long as the exe is located on the local drive of the user, however if the exe is stored on a unc path, we get the following execption thrown:
"System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
The action that failed was:
Demand
The type of the first permission that failed was:
System.Data.SqlClient.SqlClientPermission
The Zone of the assembly that failed was:
Intranet"
On our developer machines we have a control panel for .net 2.0 configuration and can get it to work by setting the intranet zone to full trust. However the users don't have this control panel. 2 questions:
a) Is there a way to get this control panel on the users station?
and
b) Is there a beter way to make this work?
Thanks, any help would be appreciated.
Kelly
View 2 Replies
View Related
Mar 9, 2006
Hi All,
I would like to create a Integration Service for import a MS Access file to SQL server, and this service will be start at every night, but the MS Access data source path is different in every day, such as "c:20060201.mdb" or "c:20060202.mdb" and so on...
how can i to define the data source path on Integration Service by every night automatically.
Thx for your help.
Laputa
View 4 Replies
View Related
Oct 24, 2007
During initial setup of SQL Server 2005 Standard Addition, I was not able to configure an alternate path (non C: ) for the Data component. Can anyone advise on the procedure to set the Data path to non default during initial setup.
Thanks in advance,
Paul
View 7 Replies
View Related
Jun 18, 2007
I have a data flow task with a single source and destination task. I'm having the source task creating a table from a variable expression and the destination table also created from a variable expression. I'm running this under 3 scenarios in which each scenario has a different source and destination table. They are different in name but close in table structure with the exception of one column being different. The Metadata for the source flow path seems to be "sticky" in that it is not modifying the source table structure in the flow to account for this different column. I'm not sure how to adjust this. Any ideas? I've modified several properties in the task and data flow but nothing seems to make this adjustment in run-time.
View 1 Replies
View Related
Feb 28, 2008
Hi, all experts here,
Do we always have to use SCD component for the loading of data into data warehouse to handle changes of rows?
I am looking forward to hearing from you and thank you very much in advance for your help.
With best regards,
View 4 Replies
View Related
Aug 17, 2006
How can I get
dbname
ip
domain name
Data path
using TSQL.
how can I stop services using TSQL
Emad
View 8 Replies
View Related
Apr 16, 2014
I have an source file and i have to load it into the data base by changing datatype of the columns in ssis
View 1 Replies
View Related
Oct 3, 2007
I have a fairly simple data flow task that loads data from one table (OLE DB Source) into another table (OLE DB Destination). The data type for one of the pairs of columns is nVarChar(120) and it contains version information that looks like a decimal. When I run the export, the destination has a trailing zero added after the decimal point as if it were a numeric column which invalidates our comparisons (string 1.0 is not the same as string 1.00). There is no cast or convert done to this column, it is a straight copy. Any ideas what could be causing this or how to fix?
View 6 Replies
View Related
Aug 29, 2006
I have created a SSIS package that transfer data from a Foxpro database to an instance of SQL Server 2005 Express. I used the wizard to create the package but I load and execute the package within a custom application that I have written in C#.
The way the custom application is intended to work is that the user can have the database in any location on the computer and all he has to do is specify the location then the application programatically changes the location of the source on the package that it has loaded and then execute it. When I initially run the package the first time (using the original path), it works fine and transfers the data. However, every subsequent time I run the application and specify a different path, the database on the SQL Server side gets created as expected but the data is not transfered!
Where am I going wrong? Do I need to save the package after I modify the source then reload and run it again or do i need to change something else in the Data Flow to make this work?
View 4 Replies
View Related
