Hi,
I want to take backup of my database over a network drive, the network drive is on a box which is on different domain,
Primary Server: IMM01
Database: DDR
Domain: PNR
User: PNRkpuser
Backup Drive: BMM02
Shared Folder: \BMM02BackupFolder
Domain: BNR
Thanks,
Imran.
I tying to test my backup to a network drive but it fails.
Please help ..thanks
this is the script -- example from BOL:
BACKUP DATABASE pubs
TO DISK '\radrisMSSQLBACKUPpubtest.bak'
this is the error:
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\radrisMSSQLBACKUPpubtest.bak'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
The database server is on computer P4.
Computer P3 has a shared folder with full access.
Computer P5 on which I have my Backup Device and will execute the Backup Database Command using Query Analyzer.
On P5 I created the Backup Device:
USE master
EXEC sp_addumpdevice 'disk', 'myDevice',
'\P3 emp est.bak'
Then I executed the following on P5 Query Analyzer:
Backup Database myDatabase TO myDevice
But I get this error:
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'myDevice'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Any idea? Thanks and 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?
How do I backup SQL Server databases to disk over my network?
View 4 Replies View Relatedi try to create a dump device on a drive remote from the sql server(6.5),using machinenamefolderdump.dat. it creates the dump but when i tried to initial the backup it gives and error system that the device is offline. thanks in advance
View 1 Replies View RelatedIs it possible to have individual backup file for each DB, possibly in the form of DB_Name_YYYYMMDD.BAK while backing up on – NETWORK?
------------------------
I think, therefore I am - Rene Descartes
hello group,a few days ago i used the following backup statement and everthingworked fine:sp_addumpdevice 'disk','pipedump','\192.x.x.xLData est.bak'Backup database Test to pipedumpThe same statement is not working today!!!!!What's the reason?Please dont tell me that check if the network path exists or not! I'vechecked the path and permissions. Everything is ok!Thanks in advanceDebian*** Sent via Developersdex http://www.developersdex.com ***
View 11 Replies View RelatedI would like to backup my databases to a network share (NAS) instead of local disk using Maintenance Plans created by Enterprise Manager. I have successfully used a UNC path to target the destination network share but have not been to figure out how to submit a logon to the network share before the backup is executed.
The SQL Server instance is running in the context of the local system account.
Can I insert a step in the SQL SQL job that is created by the Maintenance Plans that changes the Windows account that the backup runs under? If yes what command syntax would I use in the inserted step or is there another way to accomplish that I'm attempting to do?
thanks
HP
Hello,
I am trying to backup a database with a command like:
BACKUP DATABASE my DataBase TO DISK = '\bkSystemkDiskBackup1.bak'
but I get the error 'Cannot open backup device '\bkSystemkDiskBackup1.bak'. Device error or device off-line.
The bkDisk folder is shared, with Everyone full-control access (it's a test environment)
MS SQL 2000.
We get an error when we are trying to run a backup of the SQL db here, we just got a new ISP to this company and al the routers and the like has been reconfigured (if that might be the problem). Would apreciate a speedy help with this one.
Here is the error message we get
un OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147467259 (80004005) Error string: [DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 11 (B) Error string: [DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005) Error string: Connection failure Error source: Microsoft OLE DB Provider for SQL Server Help file: Help conte... Process Exit Code 1. The step failed.
I attempted to backup my database to a shared drive using the UNC path and it gave me access denied. Any thoughts?
View 2 Replies View RelatedWe're trying to backup some dbs to a network drive but fail.
I read that the 'a/c to run SQLAgent must be a domain user a/c with
proper permission to access the network drive.'
What is proper permission? Read, Write ?
Only the a/c that runs SQLAgent needs the proper permission, the job's owner doesn't?!?
Thanks in advance,
Ben
Hi guys.
Has anyone ever done SQL DB backup directly onto a maped network drive?
We've got a really huge DB 100GB+ that just wont fit into the local Hard Disk for backup.
Thank you in advance for your help.
Cheers,
Stephen
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?
Thanks!
Scott
Hi all,
I have scheduled a backup on a remote machine. Everyday I FTP these backups from the remote machine to the local drive.
A local drive is maped to the remote machine in Windows NT Explorer but SQLSERVER7 Enterprise Manager can't see this drive so that I could do the backup to it.
Can any one please tell me how to make this work?
Thanks in advance.
Attaullah
If I dump 20GB DB to hard disk on same Server it takes approx. 2 hrs(give or take). If I dump same DB to tape, also approx 2 hrs. If I restore to same or different server on same hub it takes approx 2 hrs. However, if I dump to different server on same hup it takes 10 - 12 hrs.
E.G.
Dump 20 GB DB from server A to disk or tape on Server A -- 2 hrs.
Restore to Server A or Server B from disk on Server A -- 2 hrs.
Dump 20 GB DB from Server A to Server C -- 12 hrs. Same hub.
Restore Server D from Server C -- 2 hrs.
All 100mbps cards on fiber. Same hub.
Any suggestions as to why the difference when dumping across the network as opposed ro restoring across the network???
Bill
Hi there,
Does anyone know if it's possible to perform a MSSQL7 database backup on a network drive instead of the local drives ?
...and if yes, how to proceed ?
It seems to be possible under MSSQL2000.
Many thanks for your help in advance,
Matt
Thanks for the help on the previous thread.
It seems to me that either by accident or design, SQL Server tends to steer you away from backing up directly over the network.
Are there reasons for not doing this because you obviously don't want to leave your backups on a local drive in case the drive fails?
Some possibilties that I can think of are:-
1. Local drives have faster access times and SQL backups can get quite large. I did a quick test and found that a netwrok backup takes 2 to 3 times longer than it does on a local drive.
2. Backing up on the netwrok could hog too much bandwidth. I haven't tested this and would be surprised it it's true.
3. There could be some reason that you don't want the Server and Agent services running under a domain account but want to leave them on the Local System account. I am not aware of any such reasons by the way.
4. Local drives persumably have a slightly higher availability than network drives. If the server is running, the drive should be available.
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 have a problem when i restore my .DAT_BAK file. I am getting error like "The backup set holds a backup of a database other than existing database. Restore Database is terminating abnormally".
I tried by using
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH MOVE 'VZAI_DATA' TO D:PROGRAM FILES..MSSQLTEST.MDF',
MOVE 'VZAI_LOG' TO D:PROGRAM FILES..MSSQLTEST.LDF',
REPLACE
And also i tried like
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH REPLACE
When i use like this,
RESTORE FILELISTONLY FROM DISK = 'D:DATAMYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.
Can i anyone please help me out?
Thanks in Advance,
Anand Rajagopal
I have inherited a SQLS erver 2000 instance where the client neverimplemented a backup startegy for the "master" and "msdb" databases.MSDB is now showing errors and my only option for a restore is from atape backup of the server.Any thoughts..
View 1 Replies View RelatedI 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 SQL 2005 running on OS 2003. I am trying to take backup on a network drive (NAS box).
I have logged into the OS2003 machine as ADSadministrator and the same for SQL 2005. I have given full control to ADSadministrator on the network drive of the NAS box.
When I try to take a backup, I get the following error :
Operating system error = 5(Access is denied)
Any idea what is wrong?
Thanks in advance.
anirban
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...
Hi,
I am using an Enterprise Manager and i have created a Database Maintainance Plan, can anybody guide me as to how to create backup on a remote machine.
I also mapped that device(say M/c B) onto my server box(M/c A) but i am not able to see that particular location in my backup activity screen.
Can this be done in the first place ?
Regards,
Tahir
Hello,
I am experiencing an issue with SQL Server 2000 Maintenance Plan.
DB Backup job fails to delete old backup files from the file server (I am backing up to the network share - actually, a DFS).
Backup part of the maintenance plan/job succeeds, but then cleanup part fails.
I made sure that service account under which SQL Server Agent is running, has sufficient privileges over the network share by logging in and successfully deleting files in question.
I was not able to locate any log entries either on the SQL Server machine or on the file server machine that would indicate the root of the problem. Even though I turned on auditing for Delete operations for the destination folder, its subfolders and files, I could not find anything in the Security event log.
I would appreciate any ideas on how to troubleshoot and correct this problem.
Thanks in advance,
Hi there
I'm getting this message on my third automated backup of the transaction logs of the day. Both databases are in full recovery mode, both successfully backed up at 01.00. The transaction logs backed up perfectly happily at 01:30 and 05:30, but failed at 09:30.
The only difference between 05:30 and 09:30's backups is that the log files were shrunk at 08:15 (the databases in question are the ones that sit under ILM2007, and keeping the log files small keeps the system running better).
Is it possible that shrinking the log files causes the database to think that there hasn't been a full database backup?
Thanks
Jane
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
Echo Rename log file to old.log
rename %3\%computername%_kaman_full_sqldb_backup.log %computername%_kaman_full_sqldb_backup_old.log
echo backup SQL Databases on server will start now
osql -E -n -d %1 -i %2kaman_full_sqldb_backup.sql -h-1 -o %3\%Computername%_kaman_full_sqldb_backup.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.
Any help would be appreciated. Thanks, dbmsql
I'm using the methods of the Microsoft.SqlServer.Management.Smo namespace within a .NET application to create a backup file from a SQLEXPRESS database. I can then restore the database from that backup device using methods in the same namespace. Here is a snippet from the restore code:
srv = New Server("MYPCSQLEXPRESS")
db = srv.Databases("washmaster")
Dim bdi As New BackupDeviceItem(BackupFileName, DeviceType.File)
Dim recoverymod As RecoveryModel
recoverymod = db.DatabaseOptions.RecoveryModel
rs.NoRecovery = False
rs.Devices.Add(bdi)
rs.Database = "washmaster"
rs.ReplaceDatabase = True
srv.KillAllProcesses("washmaster")
rs.SqlRestore(srv)
This works great as long as I used one of the backup files that I created directly on the disk. However, my application has a utility that allows the user to copy the backup files onto another drive, such as a CD or a thumb drive and when I try to restore from the copy of the backup, I get the following exception:
....Cannot open backup device..[filename]...Operating system error 5(Access is denied.)
The reason I get this error is that the "NETWORK SERVICE" account was removed from the file permissions when the file was copied.
How can I copy a backup to another drive and preserve the "NETWORK SERVICE" account? If I can't do that, is it wise to try to add the account back to the file before using it to restore or is there a better way?
Thanks,
SJonesy
We have a valid full backup of a database. We know it is valid, we have restored it twice from the network with no problems, but we do not have access to the network location from our sandbox environment.
The .bak file is sizable at about 9GB. The .bak file resides on our internal network, in a SAN drive. No problems there. When we copy the file from there into a sandbox environment to attempt the restore in the sandbox environment it gets corrupted. We've tried three different times and all three different times it gets corrupted. First time we copied the file over to the sandbox the restore went up to 50% and failed. The second time we copied the file again and attempted the restore again it failed at 70%.
The third time it failed at 60%. The error message we get during the restore is "...Read on ... failed: 13(The data is invalid) Msg 3013, Level 16, State 1, Line 1 Restore database is terminating abnormally."
Now some background here. To move the file our network team is doing this: they have this .vmdk file that they mount out in our production environment (which has access to the network location where the .bak file is), copy the file into the drive, then move the .vmdk file into the sandbox environment(which does not have access to the network location), mount the drive in the sandbox environment, and then I have access to the .bak file from within the sandbox environment.
Something in the process of using the .vmdk file to move the .bak file from production into the sandbox is causing the file to get corrupted.
I created the db with the attached script and I am able to access ituntil I reboot the server. I've tried enabling flag 1807 via the SQLserver service and the startup parameters of the instance. In allcases the database always come up suspect after a reboot. There wasone instance where I was able to recover, but I am not sure how thathappened.Does anyone have an idea of how I can reboot the server without thedatabase becomming suspect?USE MASTERGODBCC TRACEON(1807)GO--DBCC TRACEOFF(1807)--DBCC TRACESTATUS(1807)GOCREATE DATABASE ReadyNAS ON( NAME = ReadyNAS_Data,FILENAME = '\NAS1NASDiskSQL ServerReadyNASReadyNAS_Data.mdf',SIZE = 100MB,MAXSIZE = 20GB,FILEGROWTH = 20MB)LOG ON ( NAME = ReadyNAS_Log,FILENAME = '\NAS1NASDiskSQL ServerReadyNASReadyNAS_Log.ldf',SIZE = 20MB,MAXSIZE = 100MB,FILEGROWTH = 10MB)
View 5 Replies View Related