Unable To Access VM Network Drive In SQL During Backup Generated By Stored Procedure
Mar 12, 2007
I get the following error in a log file created in the osql command:
Msg 3201, Level 16, State 1, Server KAC2KGS2, Procedure usp_Kaman_Full_SqlDB_Backup, Line 150
Cannot open backup device 'x:Servername_master_sqlbu_200703101930.bkf'.
Device error or device off-line. See the SQL Server error log for more
details.
Msg 3013, Level 16, State 1, Server KAC2KGS2, Procedure usp_Kaman_Full_SqlDB_Backup, Line 150
BACKUP DATABASE is terminating abnormally.
The device is established in a CMD file right before the osql command is started that starts my stored procedure. The CMD in this file is:
for /f "tokens=15 delims=." %%i in ('ipconfig^|find "IP Address"^|find "192.168"') do set SUBNET=%%i
:loop
if exist x: net use x: /del
net use x: \192.168.%SUBNET%.1ackup
if not ERRORLEVEL 1 (
goto continue
) else (
echo FAILED TO CONNECT TO BACKUP SERVER >> "%SystemDrive%LogFiles\%Computername%.log"
sleep 60
goto loop
)
:continue
echo IP ADDRESS OBTAINED
Echo delete old log file if it exists
if exist %3\%computername%_kaman_full_sqldb_backup_old.log del /Q %3\%computername%_kaman_full_sqldb_backup_old.log
In the stored procedure I try to use the x: drive and that does not work. I have tried obtaining the \192.168.x.x address and that only works on some of my servers. All are running SQL 2000, some are using Win2K and Win2003. It does not seem to matter. One of them that is using Win2003 only fails occationally.
I notice when I do a
exec master..xp_cmdshell 'x:'
The system cannot find the drive specified.
Yet, when I go to the server there is an x drive.
I am using the sqlserv user to run the job and that use is an administrator on the local machine. This is a virtual machine. We add the X before the osql and drop it after the command finishes.
I have a network drive that is mapped as Z: on my local machine. When creating a new database, that drive does not show up in the list of available paths. If I try to hardcode the drive/path info, I get an error message: "The system cannot find the path specified."
I recently created a program that connects to a Microsoft SQL database that was stored on my computer and it worked fine. As soon as I tried to connect to the same database via a network drive I got an error stating that "The file Y:Filename.mdf is on a network path that is not supported for database files.". I can't seem to get it to work, if anybody has any ideas what I'm doing wrong I would appreciate your help.
I have creted a backup device on network drive and getting the following error when I run the backup command as a job.SQL server and the agent are running on a domain account which has full permission on the network drive.Any help is greatly appreciated. Thanks!
"Cannot open backup device 'mccdiffbkpond2build'. Device error or device off-line. See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3201) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed"
I am setting up a backup on one of my DB's and the network drive does not appear as an location that I can backup too. Only the C drive is present. However, on the server I do have a drive mapped to the network drive, so it should be available. Has anyone encountered this issue before?
HI i am trying to take a backup on Network drive but i am getting following error message. althought when i try to type the address in window explorer. i can access the specific folder. so that there shouldn't be network security issue.... and even thought the backup device name is correct. when i try to take backup on local disk, it work's fine....
Executed as user: NT AUTHORITYSYSTEM. Cannot open backup device 'FullBackup(\0.0.0.0SQL_BackupsFullBackupsFullBackup.bak)'. Operating system error 5(error not found). [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
I am using SQL Server 2005 Express Edition. I am planning on upgrading to either Workgroup or Standard edition. While working with Express Edition I did find out that this version does not support backup to a network drive. My question is:
Is it possible to backup directly to a network drive using a Workgroup/ Standard edition of SQL Server rather than backing up to a local drive and then 'copy and paste' to a network drive like the Express Edition.My other question is :
Given a database limit of 50GB, which one of the editions, Workgroup or Standard, will be appropriate for use.Thank you for your time and patience. Regards Berly Sam
Hi, I have created an sql server 2005 maintenance plan for a daily backup. The plan has two 'Back up database task' i.e. one backup on the local drive while the second on a network drive. When the plan is executed, a backup is created on the local drive but not on the network drive. If i check the log, it says "Access Denied" whereas i have full access to the network drive with complete permissions to read, write and delete. Can anyone help me understand how to take a backup on both a local and network drive at the same time using a maintenance plan ? I shall be obliged... Regards...
I have 2 different servers that run nightly backup jobs to a network drive using the UNC format. I am noticing the larger databases encountering the following errors on a regular basis
Error: 18210, Severity: 16, State: 1. '\fnfssql3SQLBackupsMNSQL05<database>.BAK'. Operating system error 64(The specified network name is no longer available.). BACKUP failed to complete the command BACKUP DATABASE <database>. Check the backup application log for detailed messages.
There are 5 databases on this server all backed up by the same job and 3 of them wortk fine and 2 of them fail so it is not a permissions problem. Have there been any problems with backing up to a network drive in 2005?
i have taken full database backup on tape drive one week ago,by automated scripts ,now when i check the maintenance plan history , it says the task failed and gives a message that unable to open the \. ape0 ,with error
([Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device '\. ape0'. Device error or device off-line. See the SQL Server error log for more details. [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally. )
I have two drives in SQL Server Box. C and D drives. I am using below script to move mdf and log files from C: drive to D: Drive. However, I keep getting error :Â Directory lookup for the file "D:PackagesTestVGR.mdf" failed with the operating system error 5(Access is denied.).
USE MASTER; GO -- Take database in single user mode -- if you are facing errors -- This may terminate your active transactions for database ALTER DATABASE TestVGR
I have a stored procedure that I want to test for performance , however, I cannot view the estimated query plan execution. I am not using any nested subqueries. Any ideas why the plan will not generate?
I have an SSIS package which accesses a map UNC path drive. It works fine when I run SSIS from Visual Studio. However, when I run this from a SQL Server job, it gives an error.
I am not allowed to use xp_cmdshell due to security reasons. If someone can point me to right direction, that would be greatly appricated.
Hi,When i use to work on Windows 2000 advance server and Sql 2000I was able to do network restoration by using a mapped drive where mybackup use to be lying.What i did was I assigned administrator rightsto my login and in Services (mssqlserver)i added my login.Then I was able to access any mapped drive from my network in Sqlserver 2000.Now i have switched my OS to Windows Server 2003.What I want to know is , is their any other setting that has to donefor network restoration , because after following above steps then to Iam not able to access mapped drive in SQL SERVER 2000.So can anyone help me to know what setting should i do in Windows 2003server or in SQL Server 2000 to do a network restoration of database.Thanks in advanceTV
Hi I have a simple ftp task that downloads some files from an ftp to a local directory. That package run as a sheduled job from the sql 2005 server without a problem so far.
Now I whant to have the same task but to download the files to different mapped network drives , so I mapped the drives then I changed the paths to the config file but since then the job fails...
is it possible to do that or I did something wrong?
Hello, I need help with the setup of my sql server express 2005 and more specifically, with the location of the database files. Normally, database files are located on the c: drive. In my situation, being in a high school environnement, I need to have the database files on the network drive ... lets call it g:drive. The reason why it cannot be on c:drive is because this drive is protected using DeepFreeze. Now, I was able to change the location of the new drive as I went into the properties window of the sql server instance but when I try to create this new database, sql gives me grief ;-| I am from the group of people that thinks that to anything, there is a solution and am hoping that to this problem, there is hope of a solution. Can anyone help? Thank you
I am using SQL2K server. I have a scheduled job which invokes a DTS package. One of the tasks in the DTS package invokes an OS command (from a .bat file) to map a network drive to b made available (dynamically, referring to a mapped drive letter, say "Y" or "Z") in successive tasks to refer to a source or destination TEXT file(s). Basically, the DTS package must be able to refer to the TEXT files (source/destination, depending on the task I have) located somewhere in the network. [ I use the NET USE command in the .bat file to map the network drive.)
The DTS package works stand-alone from SQL Enterprise Manager. But, when the package is invoked from a scheduled job, the step containing the DTS package fails - the task which maps the network drive fails - access violation /session error... How do I handle the batch job to overcome this problem? I appreciate tips/solutions from anyone.... Thanks. K.K.Rengan
I know , it is not going to work , just wondering if anyone could give any reasons for that. Whether it was intentional constraint or just internally compact edition was designed in particular way which makes such a usage not possible. It is a pity that it doesn't work that way as it would be much easier transitional path for many Visual Foxpro , MS Access applications. In my case I just want READ-ONLY database either for multi-user access via shared drive or stand-alone on local drive.
I've implemented an SQL-CLR stored procedure which makes an HTTP connection using the WebClient class to a server where the processing on the web server takes 10 seconds.
If I fire off a bunch of requests to the stored procedure, e.g. 80 in 1 second I noticed that SQL Server 2005 (Express Edition) only allows 2 concurrent network connections at a time.
I can confirm this by looking at the timing of the results when they come back, basically I get 2 results every 10 seconds and by doing a 'netstat -a' on the web server I notice that there are never more than 2 HTTP connections from the SQL Server at a time.
Is this some sort of sheduling policy in terms of the number of concurrent external network accesses allowed by CLR objects running in SQL Server? Or some side effect of the way the WebClient class blocks in terms of waiting for a network response?
I was expecting all 80 of the requests to block waiting on a network response almost immediately and then for all 80 of them to pretty much complete and return 10-11 seconds later.
[Microsoft.SqlServer.Server.SqlProcedure]
public static int CreditAuthorisation(string Name, decimal Amount)
{
WebClient client = new WebClient();
string result = client.DownloadString("http://someserver/Test.aspx");
In 6.5 , I was NOT able to use mapped network drive for new database device if the MSSQLSERVER service is running under local system account, but if I changed it to using a domain a/c which has the authority to create files on mapped drive, I was able to do it.
But if I tried it in 7.0, it never work and got msg: Server: Msg 5105, Level 16, State 2, Line 1 Device activation error. The physical file name 'e:est2.mdf' may be incorrect. Server: Msg 1802, Level 16, State 1, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check previous errors.
Is it something that's normal for 6.5 and 7.0 ???? i.e. we can only create databases on LOCAL drives ? I am just thinking of in case we run of space locally, we can temporarily make use of remote mapped network drive. (say at time of upgrading)
I'm hoping that someone can help me. I have a SQL 2005 SSIS package that will run Friday mornings to empty/load a table with data from another database. On Friday evenings I'll need to run another package, but want to make sure the table load completed prior to launch. For this I planned to use a file watcher task, however I cannot for the life of me figure out how to output a 'done' semaphore, from the morning job, to a networked drive.
A file system task will not work because there is not a 'create file' option. I do not have an existing file that I can rename either.
I tried an execute process task running cmd.exe with the following argument:
This fails because UNC paths are not recognized. (The package executes from another server so I cannot use a local path, nor am I allowed to set-up a local share.)
Can someone offer an alternative suggestion? I'm really hoping this is easier than I'm making it.
We have a package that is using a ForEach loop container to access files on a network drive. For some reason I am getting a message that the ForEach enumerator is empty and did not find any files that matched the pattern. For the pattern I left the default *.* for testing purposes. I have specified the file folder as \remoteserverfilesharesubfolder and also as \remoteserverc$filesharesubfolder and have gotten the same message. However when I map a network drive and set the file folder to the network drive it finds the files. Is this a permissions issue?
After I finish processing the file I want to move it to a new directory. Once this is deployed in production, the package will not be running under a domain account and probably won't have access to the network folder. Is there any way to specifiy in the connection manager itself that it should use a specific account to access the folder?
I am having an Access database on a shared network drive which has read/write access rights on the that shared network drive. When I try to Access data through the linked server it gives me gives me a message box saying you do not have permissions to view the data. Also if i try to use xp_cmdshell to copy over the mdb file to my local drive it say 'Access denied'
But when I copy (through command prompt) the same file to another network drive or my local drive where I have full control the linked server can connect sucessfully.
The problem is the i cannot have 'full control' permissions on shared drive where my database resides.
Hi! What is the fastest whay to take the resultset from an temporary table (# table) and write it to an remote destination (mapped network drive) initiated from stored proc?
Similar question if I take the resultset and convert it to xml (using FOR XML clause) and want it written to disk.
when my snapshot folder is on a network drive, on which "simple filesharing" is enabled, i can access that folder without typing a user and password and configuring login security settings.
but in fact the network drive on which the snapshot folder will be located will do have a login and password. (when i go to it with windows explorer, i have to enter my login and password).
won't this cause problems for the replication? as i don't see where to enter net network login when i configure the replication via the wizard.
hi everyone ive written a stored procedure which returns a value depending on the i/p parameterive executed it in the object browser but i cant extract it from the stored proc to the front end create procedure checking ( @username varchar(20), @password varchar(20) ,@result int out)asif @username ='admin'set @result = 1else set @result = 0 return @resultGOand this is what i wrote in the code behind SqlCommand check = new SqlCommand("checking",con);//checking is the stored procedure check.CommandType = CommandType.StoredProcedure; check.Parameters.Add(new SqlParameter("@username", SqlDbType.VarChar)).Value = TxtUsername.Value; check.Parameters.Add (new SqlParameter("@password", SqlDbType.VarChar)).Value = TxtPassword.Value; check.Parameters.Add(new SqlParameter("@result", SqlDbType.Int)).Value = 0;int status = ( Convert.toint32 )check.executenonquery();// the line which is prolly causing the error , can u tell me what should be written here? the status returns -1
I tried to create a stored procedure but instead of opening up to a new stored procedure it displays an exist stored procedure. I erased the code and typed in my code now i received this error message.
MS SQL-DMO (ODBC SQLState:42000) Error 2729: Procedure ‘spUpdate_date_time’ group number 1 already exists in the database. Choose another procedure name