Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
 Posted - 2010-12-06 : 15:10:42
|
| Hi All,On a client server there is a database of 140GB size. The server has very limited free disk space of 85GB only. I have drop logs but still I only have 85GB free space. Getting more disk space is not an option. I have to take a full backup of the database only once on this server. Taking backup on a network drive is the only option. So First I map the network drive from windows explorer (\\ntc64\f$) where the backup needs to be taken. Then I login as a domain account to the server and connect to SQL managment studio also using the same domain account. When I run the following query:BACKUP DATABASE PD2TO DISK = '\\ntc64\f$\NT32_DB.BAK'WITH INIT I got the following error.Msg 3201, Level 16, State 1, Line 2Cannot open backup device '\\ntc64\f$\NT32_DB.BAK'. Operating system error 5(Access is denied.).Msg 3013, Level 16, State 1, Line 2BACKUP DATABASE is terminating abnormally.I can ask my my client to make security changes (if any) but they need to know in detail (in steps) since the network person is unavailable and they have limited resource to make the changes (if any). Can someone help please.Thanks,Zee |
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2010-12-06 : 16:00:20
|
| Can someone pleaseeeee help. |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-12-06 : 16:15:06
|
| \\ntc64\f$ is an administrative share, so your server would only be able to backup to that share if the SQL Server service account was an administrator on \\ntc64.Create a normal share on \\ntc64 and give the SQL Server service account access to that share.CODO ERGO SUM |
|
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2010-12-06 : 21:41:21
|
| I ran the following query--Start of QueryBACKUP DATABASE PriceStratProduction2TO DISK = '\\ntcorp64\dbbackup\NTCORP32_DB.BAK'WITH INIT--End of QueryAfter 4 hours and 6 minutes I got the following message in query analyzer while displaying that the query completed with erros. A 104GB .BAK backup file has been created to the destination location on the F drive which has 35.6 GB free. What happened here? Can someone propmtly help pleaseeee.Msg 3271, Level 16, State 1, Line 2A nonrecoverable I/O error occurred on file "\\ntcorp64\dbbackup\NTCORP32_DB.BAK:" 64(The specified network name is no longer available.).Msg 3013, Level 16, State 1, Line 2BACKUP DATABASE is terminating abnormally.Thanks a million for prompt help.Zee |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-07 : 03:44:08
|
May be no help in your case, but we only ever backup to local drives and then MOVE the file to a network share. Backing up directly to a network share is at the mercy of the network stability etc. and if the network is down you get NO backup  We use RoboCopy (Part of Windows 2008 Server, available for download for earlier versions) to move the files - it will retry if network goes offline etc. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-12-07 : 10:25:45
|
quote:
Originally posted by zeeshan13
I ran the following query--Start of QueryBACKUP DATABASE PriceStratProduction2TO DISK = '\\ntcorp64\dbbackup\NTCORP32_DB.BAK'WITH INIT--End of QueryAfter 4 hours and 6 minutes I got the following message in query analyzer while displaying that the query completed with erros. A 104GB .BAK backup file has been created to the destination location on the F drive which has 35.6 GB free. What happened here? Can someone propmtly help pleaseeee.Msg 3271, Level 16, State 1, Line 2A nonrecoverable I/O error occurred on file "\\ntcorp64\dbbackup\NTCORP32_DB.BAK:" 64(The specified network name is no longer available.).Msg 3013, Level 16, State 1, Line 2BACKUP DATABASE is terminating abnormally.Thanks a million for prompt help.Zee
That indicates an inturruption on your network that caused the database server to lose connection to the server it was backing up to. It could also indicate that the remote server was shutdown or rebooted.4 hours seems like a long time for the backup. You should consider upgrading both servers network connections to 1 GB/sec is they are not already at that speed.Also, you should consider installing backup compression software, like Litespeed or Redgate Backup, to reduce the size of the backup file, speed up the backup, and reduce the amount of data that has to be sent over the network.CODO ERGO SUM |
|
|
|
MrTexasFreedom
Starting Member
13 Posts |
Posted - 2010-12-08 : 19:36:48
|
quote:
Originally posted by zeeshan13
Can someone pleaseeeee help.
Sir,I spent approximately an hour and a half researching this issue yesterday. The end of the tunnel was nowhere in sight. I decided to drive to a store, buy a 2tb external USB2 hard drive, and connect it to the server. The backup generated a 200gb file. I then copied that over to a regular desktop machine, ran 7zip on it to break it down to something like 20gb, in 650mb segments. Then I transferred those segments over my network, unzipped, and restored on the target machine where I needed to move the database.I was pretty furious with microsoft over this inelegance regarding network mounted drives. In MacOS X, Linux, and Solaris, a network mounted drive is transparent. I'm eager to get off this platform at the earliest opportunity.So, buying an external USB drive will probably get you out of this situation faster than trying to figure out how to convince Microsoft SQL Server to use a network mounted drive.good luck,MrTexasFreedom |
|
|
|
|
|
|
|
|
SQL Server 2005 taking backup over network drive?