Showing posts with label restoration. Show all posts
Showing posts with label restoration. Show all posts

Wednesday, March 7, 2012

DataBase Restoration Problem (SQL Server 7)

I'm restoring a database daily. However until last two days, I'm getting the following error (this happens in Enterprise Manager and also when I use the Query Analyzer):

Server: Msg 5149, Level 16, State 1, Line 1
MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file.
Server: Msg 3140, Level 16, State 1, Line 1
Could not adjust the space allocation for file 'corporate'.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.

The server has sufficient amount of space. Where data resides there is 13.5GB free and where the log resides there is 4.75BG free.
The backup file itself is 3.3GB.

When peaking into the backup file, it looks good:
data file being 4.4GB and the log 300MB

What could be the problem? The server is running Windows 2000 Server, RAID Solution and SQL Server 7.0 Service Pack #4I just want to also add, that on another server, where there is less space available, the backup restores fine. This is very strange.|||If you are restoring to the 'PRIMARY' filegroup, or another filegroup with lots of member files, it may be that the filegroup is 'full' (from the point of view of Sql Server).

Unfortunately this may correspond to different situations for which error messages are often not particularly helpful (the issue being due to some combination of the autogrowth algorithm, how auto growth may be constrained at a user level % / fixed increment, actual disk availability, etc., etc.). Such issues are more commonly seen in autogrow situations occurring during maintenence and / or production batching (but reasonably may occur in DB restore situations as well). Generally the error means that the disk(s) where the database(s) is / are located is / are full and /or the files of your database(s) cannot grow any more on their disk(s) (given the fixed increment or % setting constraint); or autogrowth of files may have been disabled. On DB creation (and hence mdf, ldf, sgf, etc. file creation) one either specifies or takes defaults for the initial size for each file. Similarly, one either specifies or takes defaults for how each file will increase via growth increment settings. Each time a file 'fills', it increases its size by the growth increment. When there are multiple files in a filegroup, (as is typically the case with the primary filegroup), filegroups do not autogrow until all the files are 'full'. Autogrowth then generally is initiated in a round robin type fashion. You may wish to manually check and increase the sizes of, and / or change the growth options of files that are members of the filegroup involved, as may be appropriate. If possible, I would be inclined to perform some experimentation on an identical (or very similarly configured) dev server to get a better idea of what the exact issue is in your case (before traumatizing the production server).|||Do you happen to be using fat32 as your file system (as opposed to ntfs) ? If so, fat32 has a 4 gb limit on file sizes.|||rnealejr>>FAT32 was the problem. I've checked and the drive was FAT32. The moment we converted to NTSF, the problem disappeared. Thanks a bunch.

Database Restoration

Hi!
Could anyone please advice me on whether I could restore
database from a map drive ? This is due to space
limitation of the existing server...
Any other suggestions would be much appreciated..
TIAIt generally work (I've seen some issues with backup/restore over the
network, mainly for larger backups). Worth a try. Make sure that the SQL
Server service account has permissions on the share, and address using UNC
naming.
--
Tibor Karaszi
"NG" <angiecmf@.hotmail.com> wrote in message
news:0a0001c3a1ed$b56a27a0$a601280a@.phx.gbl...
> Hi!
> Could anyone please advice me on whether I could restore
> database from a map drive ? This is due to space
> limitation of the existing server...
> Any other suggestions would be much appreciated..
> TIA|||Could you provide me the steps cause i have tried it and
could not view the map drive from Enterprise Manager...
on NT4 platform MSSQL 7.0 server.
TIA
>--Original Message--
>It generally work (I've seen some issues with
backup/restore over the
>network, mainly for larger backups). Worth a try. Make
sure that the SQL
>Server service account has permissions on the share, and
address using UNC
>naming.
>--
>Tibor Karaszi
>
>"NG" <angiecmf@.hotmail.com> wrote in message
>news:0a0001c3a1ed$b56a27a0$a601280a@.phx.gbl...
>> Hi!
>> Could anyone please advice me on whether I could restore
>> database from a map drive ? This is due to space
>> limitation of the existing server...
>> Any other suggestions would be much appreciated..
>> TIA
>
>.
>|||Don't use mapped drives, just enter the path as an UNC path:
\\servername\sharename\filename
--
Tibor Karaszi
"NG" <angiecmf@.hotmail.com> wrote in message
news:09bf01c3a272$9ec2e4d0$a301280a@.phx.gbl...
> Could you provide me the steps cause i have tried it and
> could not view the map drive from Enterprise Manager...
> on NT4 platform MSSQL 7.0 server.
> TIA
> >--Original Message--
> >It generally work (I've seen some issues with
> backup/restore over the
> >network, mainly for larger backups). Worth a try. Make
> sure that the SQL
> >Server service account has permissions on the share, and
> address using UNC
> >naming.
> >
> >--
> >Tibor Karaszi
> >
> >
> >"NG" <angiecmf@.hotmail.com> wrote in message
> >news:0a0001c3a1ed$b56a27a0$a601280a@.phx.gbl...
> >> Hi!
> >>
> >> Could anyone please advice me on whether I could restore
> >> database from a map drive ? This is due to space
> >> limitation of the existing server...
> >> Any other suggestions would be much appreciated..
> >> TIA
> >
> >
> >.
> >|||i encountered error "Unable to verify the existence of the
backup file location". Do you want to use the backup file
location anyway? and
Microsoft SQL-DMO (ODBC SQLState: 42000)
"Cannot open backup
device '\\Etax_media\temp\tna_sen.bak'. Device error or
device off-line. Backup or restore operation terminating
abnormally"
Please help! Thanks
>--Original Message--
>Don't use mapped drives, just enter the path as an UNC
path:
>\\servername\sharename\filename
>--
>Tibor Karaszi
>
>"NG" <angiecmf@.hotmail.com> wrote in message
>news:09bf01c3a272$9ec2e4d0$a301280a@.phx.gbl...
>> Could you provide me the steps cause i have tried it and
>> could not view the map drive from Enterprise Manager...
>> on NT4 platform MSSQL 7.0 server.
>> TIA
>> >--Original Message--
>> >It generally work (I've seen some issues with
>> backup/restore over the
>> >network, mainly for larger backups). Worth a try. Make
>> sure that the SQL
>> >Server service account has permissions on the share,
and
>> address using UNC
>> >naming.
>> >
>> >--
>> >Tibor Karaszi
>> >
>> >
>> >"NG" <angiecmf@.hotmail.com> wrote in message
>> >news:0a0001c3a1ed$b56a27a0$a601280a@.phx.gbl...
>> >> Hi!
>> >>
>> >> Could anyone please advice me on whether I could
restore
>> >> database from a map drive ? This is due to space
>> >> limitation of the existing server...
>> >> Any other suggestions would be much appreciated..
>> >> TIA
>> >
>> >
>> >.
>> >
>
>.
>|||The first message is some tool (or rather, API) message, not an engine
message. The second is an engine message. It seems that the path is
incorrect or that the SQL Server service account don't have permissions on
the share.
--
Tibor Karaszi
"NG" <angiecmf@.hotmail.com> wrote in message
news:0aff01c3a2ab$d4f047d0$a401280a@.phx.gbl...
> i encountered error "Unable to verify the existence of the
> backup file location". Do you want to use the backup file
> location anyway? and
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> "Cannot open backup
> device '\\Etax_media\temp\tna_sen.bak'. Device error or
> device off-line. Backup or restore operation terminating
> abnormally"
> Please help! Thanks
> >--Original Message--
> >Don't use mapped drives, just enter the path as an UNC
> path:
> >\\servername\sharename\filename
> >
> >--
> >Tibor Karaszi
> >
> >
> >"NG" <angiecmf@.hotmail.com> wrote in message
> >news:09bf01c3a272$9ec2e4d0$a301280a@.phx.gbl...
> >> Could you provide me the steps cause i have tried it and
> >> could not view the map drive from Enterprise Manager...
> >> on NT4 platform MSSQL 7.0 server.
> >> TIA
> >>
> >> >--Original Message--
> >> >It generally work (I've seen some issues with
> >> backup/restore over the
> >> >network, mainly for larger backups). Worth a try. Make
> >> sure that the SQL
> >> >Server service account has permissions on the share,
> and
> >> address using UNC
> >> >naming.
> >> >
> >> >--
> >> >Tibor Karaszi
> >> >
> >> >
> >> >"NG" <angiecmf@.hotmail.com> wrote in message
> >> >news:0a0001c3a1ed$b56a27a0$a601280a@.phx.gbl...
> >> >> Hi!
> >> >>
> >> >> Could anyone please advice me on whether I could
> restore
> >> >> database from a map drive ? This is due to space
> >> >> limitation of the existing server...
> >> >> Any other suggestions would be much appreciated..
> >> >> TIA
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >