October 29, 2007 at 2:49 pm
When I try to restore a FULL db backup of a 200 GB database across the network I get the following error message:
Server: Msg 3203, Level 16, State 1, Line 15
Read on 'dbbackup.BAK' failed, status = 1130. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 15
RESTORE DATABASE is terminating abnormally.
Microsoft has asked to add MemoryPoolSize, and other things that had to be modified in the Registy of the SQL Server where the restore is taking place but to no avail. We were able to perform this restore 2 weeks ago. No I cannot restore this database at all. The obvious for me is the network but the network people say the network is fine.
So, have you seen this type of behaviour before?
Thanks
October 30, 2007 at 5:17 am
yes its a compulsion u have to have a physical connectivity through pair to pair wire connection if you r working on LAN or just copy your data from hard drive to usb drive because most of the time network is good but your physical media does not support like your NIC card your wired media etc.
if u still willing to do this the chance of data corruption would be high .
Solution 2)
just install sql server 2000 where you have 200 GB data base
restore database and deteach the database just copy the MDF file
to the desired server and attached .
through this method u can get ur actuall result.
Regards
Syed Muhammad Naveed
Database Administrator
October 30, 2007 at 11:27 am
For a 200Gig database, I suggest using SQL LiteSpeed. If the database is 200Gig native, the backup file may be as small as 30 Gig with SQL Lite Speed. (depending on the type of data being stored) You can download an evaluation copy for 14 days to see if this helps.
I've never had much success restoring a large database over the network. Even a large copy takes a significant amount of time or fails. Most likely the restore isn't failing, but the copying of the data over the network during the restore process is the issue. I use robocopy to copy the file locally (robocopy allows you to resume the copy in the event of failure without having to start from the beginning.)
Most times, space is the issue and the server can't accomodate the 200gig file and the 200 gig database simultaneously. This is where SQL LiteSpeed is most useful.
The reduction in restore time and disk space savings is normally cost effective enough for mgmt to approve the cost of SQL LiteSpeed. In addition, with the backup 1/8 the size of a native SQL Server backup, you are able to house more versions of the database on line without needing to resort to tape in the event of failure.
(I am in no way affiliated with SQL LiteSpeed or robocopy. Just love the products)
October 30, 2007 at 1:36 pm
Thanks so much. I had always heard about SQLLiteSpeed but never considered it. The reallity is that we have been able to do this restore until last week. Suddenly it stopped working. Microsoft is working with us. I will post the solution they give us. And I will check the SQLLiteSpeed. Thanks again.
October 30, 2007 at 2:02 pm
:ermm: Maybe virus scanning software has been installed/patched and hasn't been configured to exclude the files you're using. In this case, the files would be scanned for viruses as they are opened and transferred As you can imagine, this is not good on any database, let alone a 200Gb db.
Good Luck.
October 31, 2007 at 4:28 am
I suggest you look at your processes to see if you can get the backup file on to the server where you wantto do the restore. If you are using a SAN, get the LUN containing the backup attached to your server. This will make the restore process much more reliable.
Also get your network people to look at the LAN, to see if anything has happened to degrade network performance. Maybe there is a faulty card on a server that is polluting the network traffic. Even a card running in promiscuious mode can do this. Maybe a card on the filestore server or your server has a fault.
If this does not resolve it then you could be down to guesswork. A few years back at my old place we DBAs sometimes saw what we thought was a network dropout but our network people told us everything was OK. One of our programmers wrote a program to issue a PING every 5 minutes and log the results - after a few days we showed the network people a log with a few faulty PING responses they had to admit was a network problem and eventually fixed it.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 17, 2009 at 8:37 am
I am experiencing a similar issue, although we are using SQL 2005 SP3, Windows 2003.
We are attempting to refresh our Dev database from Prod. The backup is about 700 GB. We do not use any compression or 3rd party tools for backups (although we are evaluating some). Regardless if we try to copy the backup file from Prod (SAN attached storage for backups) to Dev (also SAN attached) OR issue a Restore statement, the results are the same (Not enough server storage is available to process this command)...results below are from a Restore:
Msg 3203, Level 16, State 2, Line 1
Read on "\\servername\J$\MSSQL\Backup\filename_db_200906110130.BAK" failed: 1130(Not enough server storage is available to process this command.)
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally
***************************************************************
The drive we are attempting to restore to has about 1 TB free space. The backup is about 700 GB.
Any ideas? Thank you!
KU
June 22, 2009 at 4:17 pm
SequelDBA (6/17/2009)
I am experiencing a similar issue, although we are using SQL 2005 SP3, Windows 2003.We are attempting to refresh our Dev database from Prod. The backup is about 700 GB. We do not use any compression or 3rd party tools for backups (although we are evaluating some). Regardless if we try to copy the backup file from Prod (SAN attached storage for backups) to Dev (also SAN attached) OR issue a Restore statement, the results are the same (Not enough server storage is available to process this command)...results below are from a Restore:
Msg 3203, Level 16, State 2, Line 1
Read on "\\servername\J$\MSSQL\Backup\filename_db_200906110130.BAK" failed: 1130(Not enough server storage is available to process this command.)
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally
***************************************************************
The drive we are attempting to restore to has about 1 TB free space. The backup is about 700 GB.
Any ideas? Thank you!
I think you're issue is different than the original TS. maybe I'm wrong. Without knowing your actual restore command used, I would say you probably are restoring it to the wrong drive hence the "not enough disk space" message.
For sql server to do a restore, it needs approx the same amount + a little bit more. I'm not sure how much the little bit more equates to, but for a 700GB restore, I think you'll need at least 750gb or 800gb.
Make sure you are restoring to the correct drive with
Regular Sql to specifically create the data files in the new location.
restore database MyDatabase
from disk = '\\server\backup\myback.bak"
with move "logicalname" to "NewDriveLetter:\sqldata\sql.mdf",
move "logicalname" to "NewDriveLetter:\sqldata\sql.ldf"
As with some of the posters above, I do not recommend restoring over the network unless it is a really small file, the restore times over the network are horrible, plus the fact, one small hiccup, you have to start all over. I would recommending finding space on the server and copying it there and then restoring it.
-ST
June 22, 2009 at 4:19 pm
Maybe the network card is getting saturated? Maybe the backup file is bad. I would try making a backup of northwind or create a new database and then backup it up. Now trying do a network restore and see if it works using this backup. This will at least confirm if security or possible something else may have change.
-ST
September 2, 2009 at 1:51 pm
SequelDBA -- We have seen the same thing on a client's system. The Db to restore remotely is 189 Gb. The source (where the BAK file is located) has over 1 TB of free space and the destination where the db will be restored has almost 1 TB of free space. The DB is only 241 Gb (MDF) and 9.8 Gb (LDF) when restored, so this is NOT a disk space issue.
Here is the error we get (filenames changed to protect innocent files):
BackupMedium::ReportIoError: read failure on backup device '\\Serverdoc\Product$\Progra~1\Micros~1\MSSQL\Backup\DbName.bak'. Operating system error 1130(Not enough server storage is available to process this command.).
Emphasis added.
I key in on two things from the error message. First, the error we get during the restore is on the READ, not the write. If it was a disk space problem the error actually tells you "not enough space on disk" which is a write error.
Second, the message says "not enough server storage." We have all been conditioned to equate "storage" to mean "disk space" as this is typically the case. However, I have read multiple posts where this ends up being a memory problem, not a disk space problem. In fact, if you look at Books Online for SQL Server 2005, the error message constant for error 1130 is "ERROR_NOT_ENOUGH_SERVER_MEMORY." I don't know if it is memory on the source or destination server, but in either case, both are only using 2 Gb or less of their 4 Gb of RAM, even with /PAE set.
Any addtional ideas about how to definitively diagnose this as a problem of the source server, destination server, or network would be appreciated.
For us, sometimes the failure will happen for a string of days and sometimes it will work. We might get 4 failures, success, failure.
September 2, 2009 at 2:55 pm
SequelDBA -- We have seen the same thing on a client's system. The Db to restore remotely is 189 Gb. The source (where the BAK file is located) has over 1 TB of free space and the destination where the db will be restored has almost 1 TB of free space. The DB is only 241 Gb (MDF) and 9.8 Gb (LDF) when restored, so this is NOT a disk space issue.
Here is the error we get (filenames changed to protect innocent files):
BackupMedium::ReportIoError: read failure on backup device '\\Serverdoc\Product$\Progra~1\Micros~1\MSSQL\Backup\DbName.bak'. Operating system error 1130(Not enough server storage is available to process this command.).
Emphasis added.
I key in on two things from the error message. First, the error we get during the restore is on the READ, not the write. If it was a disk space problem the error actually tells you "not enough space on disk" which is a write error.
Second, the message says "not enough server storage." We have all been conditioned to equate "storage" to mean "disk space" as this is typically the case. However, I have read multiple posts where this ends up being a memory problem, not a disk space problem. In fact, if you look at Books Online for SQL Server 2005, the error message constant for error 1130 is "ERROR_NOT_ENOUGH_SERVER_MEMORY." I don't know if it is memory on the source or destination server, but in either case, both are only using 2 Gb or less of their 4 Gb of RAM, even with /PAE set.
Any addtional ideas about how to definitively diagnose this as a problem of the source server, destination server, or network would be appreciated.
For us, sometimes the failure will happen for a string of days and sometimes it will work. We might get 4 failures, success, failure.
September 3, 2009 at 6:50 am
You might be right on. Memory utilization would be something to investigate. We had to make this restore happen. I had a PM hounding me, so I bought a 2 TB external, hooked it to the server, copied the prod backup(700 GB) over the network to the external, created an empty DB and ran a restore, truncated and shrinked the DB(to 100GB), ran a backup, and then used the backup to create separate environments for development on our dev server.
Needless to say this took hours to complete. If/when I have this requirement and receive the same error, I would consider opening a ticket with MSFT.
KU
September 3, 2009 at 7:00 am
Steve T
************************************************************************************
RESTORE TO EXTERNAL (attached as "K")
***********************************************************************************
restore database xxxxxx from disk = '\\xxxxxxxxx\k$\xxxxxxx_full' with
move 'rrrrrrrr_Data' to 'K:\MSSQL.4\MSSQL\file1.mdf',
move rrrrrrrrrrr_Data' to 'K:\MSSQL.4\MSSQL\file2.ndf',
move 'rrrrrrrrrr_data3' to 'K:\MSSQL.4\MSSQL\file3.ndf',
move rrrrrrrrrrrr_Data' to 'K:\MSSQL.4\MSSQL\file4.ndf',
move 'rrrrrrrrr' to 'K:\MSSQL.4\MSSQL\file5.ndf',
move 'rrrrrrrrrr' to 'K:\MSSQL.4\MSSQL\file6.ndf',
move 'rrrrrrrrrrrrrr' to 'K:\MSSQL.4\MSSQL\file7.ndf',
move 'rrrrrrrrrrrrr' to 'K:\MSSQL.4\MSSQL\file8.ndf',
move 'rrrrrrrrrrrrrrr_Log' to 'K:\MSSQL.4\MSSQL\logs_log1.ldf',
move rrrrrrrrrrrrrrrrr_Log' to 'K:\MSSQL.4\MSSQL\logs_log2.ldf',
replace
KU
September 3, 2009 at 9:56 am
Sorry i could not get to this yesterday.
How we solved it was with MSFT on the phone but not the SQL division but the OS support. Apparently the problem was a DLL on the OS side that had to be upgraded and something on the network card on the backup server.
I will try to stay tune if you have any question. Ah... for us it was SQL 2000 SP4.
I hope this helps and sorry I could not get to this before.
Best luck.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply