Backup Error - Operating system error 1450

Hi,
Backing up 70Gb database to the network drive on the backup server. Was
working for long time until now. I think the database size is growing and
the OS cannot handle it. Error messages are as follows,
"Operating system error 1450(Insufficient system resources exist to complete
the requested service."
Looking at MS KB - http://support.microsoft.com/default.aspx/kb/304101
Not sure which server should add the registry key PoolUsageMaximum ? (On
backup server or SQL server ?)
Had set the registry key PoolUsageMaximum to 40 on backup server but no good.
Please advise !
in general, I think it's better to backup to a local disk. that way if the
network goes down during the backup, you still have a backup. when the
backup succeeds, you can copy it to the network share then delete the local
copy.
if you backup to the share only, you are toast if the network hiccups. then
you have data loss and are soon looking for a new job.
http://elsasoft.org
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:9BB1A66C-170D-49DE-9667-2AF306E75996@.microsoft.com...
> Hi,
> Backing up 70Gb database to the network drive on the backup server. Was
> working for long time until now. I think the database size is growing and
> the OS cannot handle it. Error messages are as follows,
> "Operating system error 1450(Insufficient system resources exist to
> complete
> the requested service."
> Looking at MS KB - http://support.microsoft.com/default.aspx/kb/304101
> Not sure which server should add the registry key PoolUsageMaximum ? (On
> backup server or SQL server ?)
> Had set the registry key PoolUsageMaximum to 40 on backup server but no
> good.
> Please advise !
>
>
>
>
|||I know it is highly recommended to backup SQL databases on local drive. This
working place is gradually changing the practice to do the right things but
takes times.
I really need to fix this issues right now.
Any solutions ?
"Jesse Hersch" wrote:

> in general, I think it's better to backup to a local disk. that way if the
> network goes down during the backup, you still have a backup. when the
> backup succeeds, you can copy it to the network share then delete the local
> copy.
> if you backup to the share only, you are toast if the network hiccups. then
> you have data loss and are soon looking for a new job.
> --
> http://elsasoft.org
>
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:9BB1A66C-170D-49DE-9667-2AF306E75996@.microsoft.com...
>
>

Backup Error - Operating system error 1450

Hi,
Backing up 70Gb database to the network drive on the backup server. Was
working for long time until now. I think the database size is growing and
the OS cannot handle it. Error messages are as follows,
"Operating system error 1450(Insufficient system resources exist to complete
the requested service."
Looking at MS KB - http://support.microsoft.com/default.aspx/kb/304101
Not sure which server should add the registry key PoolUsageMaximum ? (On
backup server or SQL server ?)
Had set the registry key PoolUsageMaximum to 40 on backup server but no good
.
Please advise !in general, I think it's better to backup to a local disk. that way if the
network goes down during the backup, you still have a backup. when the
backup succeeds, you can copy it to the network share then delete the local
copy.
if you backup to the share only, you are toast if the network hiccups. then
you have data loss and are soon looking for a new job.
http://elsasoft.org
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:9BB1A66C-170D-49DE-9667-2AF306E75996@.microsoft.com...
> Hi,
> Backing up 70Gb database to the network drive on the backup server. Was
> working for long time until now. I think the database size is growing and
> the OS cannot handle it. Error messages are as follows,
> "Operating system error 1450(Insufficient system resources exist to
> complete
> the requested service."
> Looking at MS KB - http://support.microsoft.com/default.aspx/kb/304101
> Not sure which server should add the registry key PoolUsageMaximum ? (On
> backup server or SQL server ?)
> Had set the registry key PoolUsageMaximum to 40 on backup server but no
> good.
> Please advise !
>
>
>
>|||I know it is highly recommended to backup SQL databases on local drive. Thi
s
working place is gradually changing the practice to do the right things but
takes times.
I really need to fix this issues right now.
Any solutions ?
"Jesse Hersch" wrote:

> in general, I think it's better to backup to a local disk. that way if th
e
> network goes down during the backup, you still have a backup. when the
> backup succeeds, you can copy it to the network share then delete the loca
l
> copy.
> if you backup to the share only, you are toast if the network hiccups. th
en
> you have data loss and are soon looking for a new job.
> --
> http://elsasoft.org
>
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:9BB1A66C-170D-49DE-9667-2AF306E75996@.microsoft.com...
>
>

Backup Error - Operating system error 1450

Hi,
Backing up 70Gb database to the network drive on the backup server. Was
working for long time until now. I think the database size is growing and
the OS cannot handle it. Error messages are as follows,
"Operating system error 1450(Insufficient system resources exist to complete
the requested service."
Looking at MS KB - http://support.microsoft.com/default.aspx/kb/304101
Not sure which server should add the registry key PoolUsageMaximum ? (On
backup server or SQL server ?)
Had set the registry key PoolUsageMaximum to 40 on backup server but no good.
Please advise !in general, I think it's better to backup to a local disk. that way if the
network goes down during the backup, you still have a backup. when the
backup succeeds, you can copy it to the network share then delete the local
copy.
if you backup to the share only, you are toast if the network hiccups. then
you have data loss and are soon looking for a new job. :)
--
http://elsasoft.org
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:9BB1A66C-170D-49DE-9667-2AF306E75996@.microsoft.com...
> Hi,
> Backing up 70Gb database to the network drive on the backup server. Was
> working for long time until now. I think the database size is growing and
> the OS cannot handle it. Error messages are as follows,
> "Operating system error 1450(Insufficient system resources exist to
> complete
> the requested service."
> Looking at MS KB - http://support.microsoft.com/default.aspx/kb/304101
> Not sure which server should add the registry key PoolUsageMaximum ? (On
> backup server or SQL server ?)
> Had set the registry key PoolUsageMaximum to 40 on backup server but no
> good.
> Please advise !
>
>
>
>|||I know it is highly recommended to backup SQL databases on local drive. This
working place is gradually changing the practice to do the right things but
takes times.
I really need to fix this issues right now.
Any solutions ?
"Jesse Hersch" wrote:
> in general, I think it's better to backup to a local disk. that way if the
> network goes down during the backup, you still have a backup. when the
> backup succeeds, you can copy it to the network share then delete the local
> copy.
> if you backup to the share only, you are toast if the network hiccups. then
> you have data loss and are soon looking for a new job. :)
> --
> http://elsasoft.org
>
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:9BB1A66C-170D-49DE-9667-2AF306E75996@.microsoft.com...
> > Hi,
> >
> > Backing up 70Gb database to the network drive on the backup server. Was
> > working for long time until now. I think the database size is growing and
> > the OS cannot handle it. Error messages are as follows,
> >
> > "Operating system error 1450(Insufficient system resources exist to
> > complete
> > the requested service."
> >
> > Looking at MS KB - http://support.microsoft.com/default.aspx/kb/304101
> >
> > Not sure which server should add the registry key PoolUsageMaximum ? (On
> > backup server or SQL server ?)
> > Had set the registry key PoolUsageMaximum to 40 on backup server but no
> > good.
> >
> > Please advise !
> >
> >
> >
> >
> >
> >
> >
>
>

Backup Error - File in Use

I am running SQL Server on a Win 2003 Server box. Large Backups are scheduled
which are about 10gigs in size. I have a batch file that executes after the
backup is completed, which moves the 10 gb file to a different drive on the
same box. On random occassions, the backups fail with the following message:
"Unable to delete preexisting D:\Path\db.dat: The process cannot access the
file because it is being used by another process."
I am not sure what kind of locks could be set on the backup dat file to
prevent it from being deleted before the next backup. The only other program
accessing the backup file is the batch file I am running, which performs the
copy operation after each backup.
Any ideas on this will help... Thanks!
Hi,
First question that arise in my mind is, why you are taking backup in one
drive and using batch cmd to move it to different drive on the same box? Why
can't you directly save your sql backup to the destination drive? With the
maintenance plan you can delete the old backups also.
Thanks
GYK
|||I should have explained this in my first post. Sorry about that. The backups
are created by a SMS 2003 service. Each time the backup runs, the service
updates the contents of the folder with the latest backups. I cannot
configure it to backup to a specific location, because the process is not
user-driven. The idea of moving it to a different drive is merely for disk
space reasons and being able to store atleast 2 previous backups on a
different drive. The batch file manages this and the actual transfer of the
latest backup file.
I hope this explains things... Thanks....
"GYK" wrote:

> Hi,
> First question that arise in my mind is, why you are taking backup in one
> drive and using batch cmd to move it to different drive on the same box? Why
> can't you directly save your sql backup to the destination drive? With the
> maintenance plan you can delete the old backups also.
> Thanks
> GYK
|||I find it really hard to believe you can not specify the location of the
backup. In any case do you have a tape backup process that at some point
copies that to tape? That is the most likely cause. If you can't figure
out how to change the location I would suggest you create your own scheduled
job that issues the backup in the correct place. Backing up the database to
the same drive is only asking for trouble.
Andrew J. Kelly SQL MVP
"bd2103" <bd2103@.discussions.microsoft.com> wrote in message
news:5FBE5C09-D670-48AC-A8C2-414AB9614702@.microsoft.com...[vbcol=seagreen]
>I should have explained this in my first post. Sorry about that. The
>backups
> are created by a SMS 2003 service. Each time the backup runs, the service
> updates the contents of the folder with the latest backups. I cannot
> configure it to backup to a specific location, because the process is not
> user-driven. The idea of moving it to a different drive is merely for disk
> space reasons and being able to store atleast 2 previous backups on a
> different drive. The batch file manages this and the actual transfer of
> the
> latest backup file.
> I hope this explains things... Thanks....
> "GYK" wrote:
|||Tape Backups are in place. That is a good point. I will check into this soon
and post about when they are scheduled. The issue of having the backup on the
same drive I think can be avoided through this process as we will be
archiving the last few backups on a different drive. Thanks for your
input......
"Andrew J. Kelly" wrote:

> I find it really hard to believe you can not specify the location of the
> backup. In any case do you have a tape backup process that at some point
> copies that to tape? That is the most likely cause. If you can't figure
> out how to change the location I would suggest you create your own scheduled
> job that issues the backup in the correct place. Backing up the database to
> the same drive is only asking for trouble.
> --
> Andrew J. Kelly SQL MVP
>
> "bd2103" <bd2103@.discussions.microsoft.com> wrote in message
> news:5FBE5C09-D670-48AC-A8C2-414AB9614702@.microsoft.com...
>
>
|||It's not only when it is scheduled. I have seen tape backup software hold
locks on files for days when they get screwed up.
Andrew J. Kelly SQL MVP
"bd2103" <bd2103@.discussions.microsoft.com> wrote in message
news:238DD2E4-535F-44BC-A382-473E9142B24B@.microsoft.com...[vbcol=seagreen]
> Tape Backups are in place. That is a good point. I will check into this
> soon
> and post about when they are scheduled. The issue of having the backup on
> the
> same drive I think can be avoided through this process as we will be
> archiving the last few backups on a different drive. Thanks for your
> input......
> "Andrew J. Kelly" wrote:
sql

Backup Error - File in Use

I am running SQL Server on a Win 2003 Server box. Large Backups are scheduled
which are about 10gigs in size. I have a batch file that executes after the
backup is completed, which moves the 10 gb file to a different drive on the
same box. On random occassions, the backups fail with the following message:
"Unable to delete preexisting D:\Path\db.dat: The process cannot access the
file because it is being used by another process."
I am not sure what kind of locks could be set on the backup dat file to
prevent it from being deleted before the next backup. The only other program
accessing the backup file is the batch file I am running, which performs the
copy operation after each backup.
Any ideas on this will help... Thanks!Hi,
First question that arise in my mind is, why you are taking backup in one
drive and using batch cmd to move it to different drive on the same box? Why
can't you directly save your sql backup to the destination drive? With the
maintenance plan you can delete the old backups also.
Thanks
GYK|||I should have explained this in my first post. Sorry about that. The backups
are created by a SMS 2003 service. Each time the backup runs, the service
updates the contents of the folder with the latest backups. I cannot
configure it to backup to a specific location, because the process is not
user-driven. The idea of moving it to a different drive is merely for disk
space reasons and being able to store atleast 2 previous backups on a
different drive. The batch file manages this and the actual transfer of the
latest backup file.
I hope this explains things... Thanks....
"GYK" wrote:
> Hi,
> First question that arise in my mind is, why you are taking backup in one
> drive and using batch cmd to move it to different drive on the same box? Why
> can't you directly save your sql backup to the destination drive? With the
> maintenance plan you can delete the old backups also.
> Thanks
> GYK|||I find it really hard to believe you can not specify the location of the
backup. In any case do you have a tape backup process that at some point
copies that to tape? That is the most likely cause. If you can't figure
out how to change the location I would suggest you create your own scheduled
job that issues the backup in the correct place. Backing up the database to
the same drive is only asking for trouble.
--
Andrew J. Kelly SQL MVP
"bd2103" <bd2103@.discussions.microsoft.com> wrote in message
news:5FBE5C09-D670-48AC-A8C2-414AB9614702@.microsoft.com...
>I should have explained this in my first post. Sorry about that. The
>backups
> are created by a SMS 2003 service. Each time the backup runs, the service
> updates the contents of the folder with the latest backups. I cannot
> configure it to backup to a specific location, because the process is not
> user-driven. The idea of moving it to a different drive is merely for disk
> space reasons and being able to store atleast 2 previous backups on a
> different drive. The batch file manages this and the actual transfer of
> the
> latest backup file.
> I hope this explains things... Thanks....
> "GYK" wrote:
>> Hi,
>> First question that arise in my mind is, why you are taking backup in one
>> drive and using batch cmd to move it to different drive on the same box?
>> Why
>> can't you directly save your sql backup to the destination drive? With
>> the
>> maintenance plan you can delete the old backups also.
>> Thanks
>> GYK|||Tape Backups are in place. That is a good point. I will check into this soon
and post about when they are scheduled. The issue of having the backup on the
same drive I think can be avoided through this process as we will be
archiving the last few backups on a different drive. Thanks for your
input......
"Andrew J. Kelly" wrote:
> I find it really hard to believe you can not specify the location of the
> backup. In any case do you have a tape backup process that at some point
> copies that to tape? That is the most likely cause. If you can't figure
> out how to change the location I would suggest you create your own scheduled
> job that issues the backup in the correct place. Backing up the database to
> the same drive is only asking for trouble.
> --
> Andrew J. Kelly SQL MVP
>
> "bd2103" <bd2103@.discussions.microsoft.com> wrote in message
> news:5FBE5C09-D670-48AC-A8C2-414AB9614702@.microsoft.com...
> >I should have explained this in my first post. Sorry about that. The
> >backups
> > are created by a SMS 2003 service. Each time the backup runs, the service
> > updates the contents of the folder with the latest backups. I cannot
> > configure it to backup to a specific location, because the process is not
> > user-driven. The idea of moving it to a different drive is merely for disk
> > space reasons and being able to store atleast 2 previous backups on a
> > different drive. The batch file manages this and the actual transfer of
> > the
> > latest backup file.
> >
> > I hope this explains things... Thanks....
> >
> > "GYK" wrote:
> >
> >> Hi,
> >>
> >> First question that arise in my mind is, why you are taking backup in one
> >> drive and using batch cmd to move it to different drive on the same box?
> >> Why
> >> can't you directly save your sql backup to the destination drive? With
> >> the
> >> maintenance plan you can delete the old backups also.
> >>
> >> Thanks
> >> GYK
>
>|||It's not only when it is scheduled. I have seen tape backup software hold
locks on files for days when they get screwed up.
--
Andrew J. Kelly SQL MVP
"bd2103" <bd2103@.discussions.microsoft.com> wrote in message
news:238DD2E4-535F-44BC-A382-473E9142B24B@.microsoft.com...
> Tape Backups are in place. That is a good point. I will check into this
> soon
> and post about when they are scheduled. The issue of having the backup on
> the
> same drive I think can be avoided through this process as we will be
> archiving the last few backups on a different drive. Thanks for your
> input......
> "Andrew J. Kelly" wrote:
>> I find it really hard to believe you can not specify the location of the
>> backup. In any case do you have a tape backup process that at some point
>> copies that to tape? That is the most likely cause. If you can't figure
>> out how to change the location I would suggest you create your own
>> scheduled
>> job that issues the backup in the correct place. Backing up the database
>> to
>> the same drive is only asking for trouble.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "bd2103" <bd2103@.discussions.microsoft.com> wrote in message
>> news:5FBE5C09-D670-48AC-A8C2-414AB9614702@.microsoft.com...
>> >I should have explained this in my first post. Sorry about that. The
>> >backups
>> > are created by a SMS 2003 service. Each time the backup runs, the
>> > service
>> > updates the contents of the folder with the latest backups. I cannot
>> > configure it to backup to a specific location, because the process is
>> > not
>> > user-driven. The idea of moving it to a different drive is merely for
>> > disk
>> > space reasons and being able to store atleast 2 previous backups on a
>> > different drive. The batch file manages this and the actual transfer of
>> > the
>> > latest backup file.
>> >
>> > I hope this explains things... Thanks....
>> >
>> > "GYK" wrote:
>> >
>> >> Hi,
>> >>
>> >> First question that arise in my mind is, why you are taking backup in
>> >> one
>> >> drive and using batch cmd to move it to different drive on the same
>> >> box?
>> >> Why
>> >> can't you directly save your sql backup to the destination drive? With
>> >> the
>> >> maintenance plan you can delete the old backups also.
>> >>
>> >> Thanks
>> >> GYK
>>

Backup Error

I am getting this error:
Internal I/O request 0x05097428: Op: Write, pBuffer: 0x07400000, Size: 851968, Position: 4294580736, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0xFFFA1A00, OffsetHigh: 0x0, m_buf: 0x07400000, m_len: 851968, m_actualBytes: 0, m_errcode: 112, BackupFile: i:\backups\warehouse_data.bak
BackupMedium::ReportIoError: write failure on backup device 'i:\backups\warehouse_data.bak'. Operating system error 112(There is not enough space on the disk.).
The command (t-sql) causing the error is:
backup database @.dbname to disk=@.backupTo with init
The available space on drive i: is approx 120Gb and the size of the database "warehouse" is apprx: 8Gb, so there is plenty of room on the target drive.
The source drive is rather short of space.
Can someone explain this ?Hi,
Is this I drive a local drive? Can you execute the below comand to check the
space free in I drive.
xp_fixedrives
If you have enogh space try executing the below command to backup from query
analyzer.
backup database <dbname> to disk='i:\dbname.bak' with init
Thanks
Hari
MCDBA
"Michael" <Michael@.discussions.microsoft.com> wrote in message
news:A033AA30-FEFB-47BF-A545-95638FC770F8@.microsoft.com...
> I am getting this error:
> Internal I/O request 0x05097428: Op: Write, pBuffer: 0x07400000, Size:
851968, Position: 4294580736, UMS: Internal: 0x103, InternalHigh: 0x0,
Offset: 0xFFFA1A00, OffsetHigh: 0x0, m_buf: 0x07400000, m_len: 851968,
m_actualBytes: 0, m_errcode: 112, BackupFile: i:\backups\warehouse_data.bak
> BackupMedium::ReportIoError: write failure on backup device
'i:\backups\warehouse_data.bak'. Operating system error 112(There is not
enough space on the disk.).
> The command (t-sql) causing the error is:
> backup database @.dbname to disk=@.backupTo with init
> The available space on drive i: is approx 120Gb and the size of the
database "warehouse" is apprx: 8Gb, so there is plenty of room on the target
drive.
> The source drive is rather short of space.
> Can someone explain this ?
>sql

Backup Error

I am getting this error:
Internal I/O request 0x05097428: Op: Write, pBuffer: 0x07400000, Size: 851968, Position: 4294580736, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0xFFFA1A00, OffsetHigh: 0x0, m_buf: 0x07400000, m_len: 851968, m_actualBytes: 0, m_errcode: 112, BackupFi
le: i:\backups\warehouse_data.bak
BackupMedium::ReportIoError: write failure on backup device 'i:\backups\warehouse_data.bak'. Operating system error 112(There is not enough space on the disk.).
The command (t-sql) causing the error is:
backup database @.dbname to disk=@.backupTo with init
The available space on drive i: is approx 120Gb and the size of the database "warehouse" is apprx: 8Gb, so there is plenty of room on the target drive.
The source drive is rather short of space.
Can someone explain this ?
Hi,
Is this I drive a local drive? Can you execute the below comand to check the
space free in I drive.
xp_fixedrives
If you have enogh space try executing the below command to backup from query
analyzer.
backup database <dbname> to disk='i:\dbname.bak' with init
Thanks
Hari
MCDBA
"Michael" <Michael@.discussions.microsoft.com> wrote in message
news:A033AA30-FEFB-47BF-A545-95638FC770F8@.microsoft.com...
> I am getting this error:
> Internal I/O request 0x05097428: Op: Write, pBuffer: 0x07400000, Size:
851968, Position: 4294580736, UMS: Internal: 0x103, InternalHigh: 0x0,
Offset: 0xFFFA1A00, OffsetHigh: 0x0, m_buf: 0x07400000, m_len: 851968,
m_actualBytes: 0, m_errcode: 112, BackupFile: i:\backups\warehouse_data.bak
> BackupMedium::ReportIoError: write failure on backup device
'i:\backups\warehouse_data.bak'. Operating system error 112(There is not
enough space on the disk.).
> The command (t-sql) causing the error is:
> backup database @.dbname to disk=@.backupTo with init
> The available space on drive i: is approx 120Gb and the size of the
database "warehouse" is apprx: 8Gb, so there is plenty of room on the target
drive.
> The source drive is rather short of space.
> Can someone explain this ?
>

Backup Error

I am getting this error:
Internal I/O request 0x05097428: Op: Write, pBuffer: 0x07400000, Size: 85196
8, Position: 4294580736, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0x
FFFA1A00, OffsetHigh: 0x0, m_buf: 0x07400000, m_len: 851968, m_actualBytes:
0, m_errcode: 112, BackupFi
le: i:\backups\warehouse_data.bak
BackupMedium::ReportIoError: write failure on backup device 'i:\backups\ware
house_data.bak'. Operating system error 112(There is not enough space on the
disk.).
The command (t-sql) causing the error is:
backup database @.dbname to disk=@.backupTo with init
The available space on drive i: is approx 120Gb and the size of the database
"warehouse" is apprx: 8Gb, so there is plenty of room on the target drive.
The source drive is rather short of space.
Can someone explain this ?Hi,
Is this I drive a local drive? Can you execute the below comand to check the
space free in I drive.
xp_fixedrives
If you have enogh space try executing the below command to backup from query
analyzer.
backup database <dbname> to disk='i:\dbname.bak' with init
Thanks
Hari
MCDBA
"Michael" <Michael@.discussions.microsoft.com> wrote in message
news:A033AA30-FEFB-47BF-A545-95638FC770F8@.microsoft.com...
> I am getting this error:
> Internal I/O request 0x05097428: Op: Write, pBuffer: 0x07400000, Size:
851968, Position: 4294580736, UMS: Internal: 0x103, InternalHigh: 0x0,
Offset: 0xFFFA1A00, OffsetHigh: 0x0, m_buf: 0x07400000, m_len: 851968,
m_actualBytes: 0, m_errcode: 112, BackupFile: i:\backups\warehouse_data.bak
> BackupMedium::ReportIoError: write failure on backup device
'i:\backups\warehouse_data.bak'. Operating system error 112(There is not
enough space on the disk.).
> The command (t-sql) causing the error is:
> backup database @.dbname to disk=@.backupTo with init
> The available space on drive i: is approx 120Gb and the size of the
database "warehouse" is apprx: 8Gb, so there is plenty of room on the target
drive.
> The source drive is rather short of space.
> Can someone explain this ?
>

Backup Device size

I am looking for a way to retrieve the size of my backup devices. When I run sp_helpdevice, the backup devices with a cntrltype = 2 (which happen to be the only ones I am interested in), all return a size = 0. Any idea how to get the size using T-SQL?
Message posted via http://www.sqlmonster.com
How about the file_size in the BackupFile system table?
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:a46bfcae27494128849870935f2de9b3@.SQLMonster.c om...
>I am looking for a way to retrieve the size of my backup devices. When I
>run sp_helpdevice, the backup devices with a cntrltype = 2 (which happen to
>be the only ones I am interested in), all return a size = 0. Any idea how
>to get the size using T-SQL?
> --
> Message posted via http://www.sqlmonster.com
|||That does not provide the size of the backup files (as in *.bak), but instead provides the files that are being backed up. I am looking for the size of the *.bak files.
Message posted via http://www.sqlmonster.com
|||OK then how about Backup_Size in sysbackuphistory?
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:5d56319a8f704287b2cbe0cc361395fc@.SQLMonster.c om...
> That does not provide the size of the backup files (as in *.bak), but
> instead provides the files that are being backed up. I am looking for the
> size of the *.bak files.
> --
> Message posted via http://www.sqlmonster.com
|||I am running SQL 2000 and do not seem to be able to find sysbackuphistory.
Message posted via http://www.sqlmonster.com
|||I am sorry I meant the BackupSet system table.
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:13e45dbcb76d4537a615b957827d5d8a@.SQLMonster.c om...
>I am running SQL 2000 and do not seem to be able to find sysbackuphistory.
> --
> Message posted via http://www.sqlmonster.com
sql

Backup Device size

I am looking for a way to retrieve the size of my backup devices. When I run
sp_helpdevice, the backup devices with a cntrltype = 2 (which happen to be
the only ones I am interested in), all return a size = 0. Any idea how to ge
t the size using T-SQL?
Message posted via http://www.droptable.comHow about the file_size in the BackupFile system table?
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:a46bfcae27494128849870935f2de9b3@.SQ
droptable.com...
>I am looking for a way to retrieve the size of my backup devices. When I
>run sp_helpdevice, the backup devices with a cntrltype = 2 (which happen to
>be the only ones I am interested in), all return a size = 0. Any idea how
>to get the size using T-SQL?
> --
> Message posted via http://www.droptable.com|||That does not provide the size of the backup files (as in *.bak), but instea
d provides the files that are being backed up. I am looking for the size of
the *.bak files.
Message posted via http://www.droptable.com|||OK then how about Backup_Size in sysbackuphistory?
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:5d56319a8f704287b2cbe0cc361395fc@.SQ
droptable.com...
> That does not provide the size of the backup files (as in *.bak), but
> instead provides the files that are being backed up. I am looking for the
> size of the *.bak files.
> --
> Message posted via http://www.droptable.com|||I am running SQL 2000 and do not seem to be able to find sysbackuphistory.
Message posted via http://www.droptable.com|||I am sorry I meant the BackupSet system table.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:13e45dbcb76d4537a615b957827d5d8a@.SQ
droptable.com...
>I am running SQL 2000 and do not seem to be able to find sysbackuphistory.
> --
> Message posted via http://www.droptable.com

Backup Device size

I am looking for a way to retrieve the size of my backup devices. When I run sp_helpdevice, the backup devices with a cntrltype = 2 (which happen to be the only ones I am interested in), all return a size = 0. Any idea how to get the size using T-SQL?
--
Message posted via http://www.sqlmonster.comHow about the file_size in the BackupFile system table?
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:a46bfcae27494128849870935f2de9b3@.SQLMonster.com...
>I am looking for a way to retrieve the size of my backup devices. When I
>run sp_helpdevice, the backup devices with a cntrltype = 2 (which happen to
>be the only ones I am interested in), all return a size = 0. Any idea how
>to get the size using T-SQL?
> --
> Message posted via http://www.sqlmonster.com|||That does not provide the size of the backup files (as in *.bak), but instead provides the files that are being backed up. I am looking for the size of the *.bak files.
--
Message posted via http://www.sqlmonster.com|||OK then how about Backup_Size in sysbackuphistory?
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:5d56319a8f704287b2cbe0cc361395fc@.SQLMonster.com...
> That does not provide the size of the backup files (as in *.bak), but
> instead provides the files that are being backed up. I am looking for the
> size of the *.bak files.
> --
> Message posted via http://www.sqlmonster.com|||I am running SQL 2000 and do not seem to be able to find sysbackuphistory.
--
Message posted via http://www.sqlmonster.com|||I am sorry I meant the BackupSet system table.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:13e45dbcb76d4537a615b957827d5d8a@.SQLMonster.com...
>I am running SQL 2000 and do not seem to be able to find sysbackuphistory.
> --
> Message posted via http://www.sqlmonster.com

Backup database size

Greetings,
My colleauges and I disagree ... and we're looking for the right answer:
Assume you have a database X. Database X has been allocated 50 GB. The
actual data consumes 20 GB of the 50 GB. Is there a way, WITHOUT using 3rd
party tools, to backup the database to disk so that it consumes 15 GB of
drive space, or even significantly less?
My claim: Any backup method native to SQL Server can only create a backup
file that will be around 20 GB. It's possible to compress the file AFTER
backing it up using a 3rd party tool.
Their claim: Their backups are 20% - 30% of the 20 GB ... so essentially the
data is being compressed during the backup.
Thanks in advance!
Mark
field027@.umn.edu"Mark" <mfield@.idonotlikespam.cce.umn.edu> wrote in message
news:#ZRnbrioDHA.2000@.TK2MSFTNGP10.phx.gbl...
> Assume you have a database X. Database X has been allocated 50 GB. The
> actual data consumes 20 GB of the 50 GB. Is there a way, WITHOUT using
3rd
> party tools, to backup the database to disk so that it consumes 15 GB of
> drive space, or even significantly less?
You could do the backup to a compressed folder on a NTFS formatted drive.|||Could they be just backing up the data and scripting the indexes, objects,
permissions etc
sp_spaceused will show you the data / index sizes
--
HTH
Ryan Waight, MCDBA, MCSE
"Mark" <mfield@.idonotlikespam.cce.umn.edu> wrote in message
news:%23ZRnbrioDHA.2000@.TK2MSFTNGP10.phx.gbl...
> Greetings,
> My colleauges and I disagree ... and we're looking for the right answer:
> Assume you have a database X. Database X has been allocated 50 GB. The
> actual data consumes 20 GB of the 50 GB. Is there a way, WITHOUT using
3rd
> party tools, to backup the database to disk so that it consumes 15 GB of
> drive space, or even significantly less?
> My claim: Any backup method native to SQL Server can only create a backup
> file that will be around 20 GB. It's possible to compress the file AFTER
> backing it up using a 3rd party tool.
> Their claim: Their backups are 20% - 30% of the 20 GB ... so essentially
the
> data is being compressed during the backup.
> Thanks in advance!
> Mark
> field027@.umn.edu
>|||You are correct. SQL Server does not compress data, but it will not backup
unused pages (extents?). To get the data under 20GB, you need some 3:rd
party app or some external compression tool.
--
Tibor Karaszi
"Mark" <mfield@.idonotlikespam.cce.umn.edu> wrote in message
news:%23ZRnbrioDHA.2000@.TK2MSFTNGP10.phx.gbl...
> Greetings,
> My colleauges and I disagree ... and we're looking for the right answer:
> Assume you have a database X. Database X has been allocated 50 GB. The
> actual data consumes 20 GB of the 50 GB. Is there a way, WITHOUT using
3rd
> party tools, to backup the database to disk so that it consumes 15 GB of
> drive space, or even significantly less?
> My claim: Any backup method native to SQL Server can only create a backup
> file that will be around 20 GB. It's possible to compress the file AFTER
> backing it up using a 3rd party tool.
> Their claim: Their backups are 20% - 30% of the 20 GB ... so essentially
the
> data is being compressed during the backup.
> Thanks in advance!
> Mark
> field027@.umn.edu
>|||You can check out the following 3rd party tools:
SQLLiteSpeed.com
SQLzip.com
Rohit

Backup database problem

I am trying to backup database on limited size drive
In options for backup I picked overwrite existing file, but for some reasons it still creates new file with the date associated with it
What strange about that is ,it strippes old file from the date and new file has the date in file nameIf you are using the Database Maintenance planner or the xp_sqlmaint procedure, they will use a file naming convention which may be different than the old backup that is on your system, so they won't touch the old file because they don't recognize it.

Also, when overwriting files, Windows applications typically create a new file and then delete the old file and rename the new file when the new file is complete. So you can still run out of disk space if you don't have enough room for two copies of the file.

blindman

backup causes file size to grow

I backed up my database using the sql command Backup database xxx to disk = 'yyy'
When it completed the file size of my database approximately tripled. Did I
just corrupt some data? The application appears to continue to run ok. I
have experienced performance problems in the past and assumed it was hardware
related. Maybe my database was just sized too small'?Which file tripled? The database is made up of by default one data file and
one log file. Was it the data or log file that grew? If it was the log
file then you need to know that log backups can not finish during a full
backup. So if your log file was small to begin with and someone did a large
(or lots of ) transactions during the full backup the log file may have had
to grow since it could not be truncated during the full backup.
--
Andrew J. Kelly SQL MVP
"doug" <doug@.discussions.microsoft.com> wrote in message
news:D9566F87-EC93-4950-9B59-0CF936AF0D74@.microsoft.com...
>I backed up my database using the sql command Backup database xxx to disk
>=> 'yyy'
> When it completed the file size of my database approximately tripled. Did
> I
> just corrupt some data? The application appears to continue to run ok. I
> have experienced performance problems in the past and assumed it was
> hardware
> related. Maybe my database was just sized too small'?|||It was the data file (.mdb) that grew. My log file (.ldf) is basically the
same size it was. I went from a 6gb database file to a 20gb database file.
Thanks in advance for any advice.
"Andrew J. Kelly" wrote:
> Which file tripled? The database is made up of by default one data file and
> one log file. Was it the data or log file that grew? If it was the log
> file then you need to know that log backups can not finish during a full
> backup. So if your log file was small to begin with and someone did a large
> (or lots of ) transactions during the full backup the log file may have had
> to grow since it could not be truncated during the full backup.
> --
> Andrew J. Kelly SQL MVP
>
> "doug" <doug@.discussions.microsoft.com> wrote in message
> news:D9566F87-EC93-4950-9B59-0CF936AF0D74@.microsoft.com...
> >I backed up my database using the sql command Backup database xxx to disk
> >=> > 'yyy'
> >
> > When it completed the file size of my database approximately tripled. Did
> > I
> > just corrupt some data? The application appears to continue to run ok. I
> > have experienced performance problems in the past and assumed it was
> > hardware
> > related. Maybe my database was just sized too small'?
>
>|||Are you positive that no one issued an ALTER DATABASE command that may have
grown the file? I have never heard of a db file growing from a backup
before. If it was due to autogrow there should have been lots of log
entries as well.
--
Andrew J. Kelly SQL MVP
"doug" <doug@.discussions.microsoft.com> wrote in message
news:68FFC5B6-9B4B-4194-A360-64FE8B2003AB@.microsoft.com...
> It was the data file (.mdb) that grew. My log file (.ldf) is basically
> the
> same size it was. I went from a 6gb database file to a 20gb database
> file.
> Thanks in advance for any advice.
> "Andrew J. Kelly" wrote:
>> Which file tripled? The database is made up of by default one data file
>> and
>> one log file. Was it the data or log file that grew? If it was the log
>> file then you need to know that log backups can not finish during a full
>> backup. So if your log file was small to begin with and someone did a
>> large
>> (or lots of ) transactions during the full backup the log file may have
>> had
>> to grow since it could not be truncated during the full backup.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "doug" <doug@.discussions.microsoft.com> wrote in message
>> news:D9566F87-EC93-4950-9B59-0CF936AF0D74@.microsoft.com...
>> >I backed up my database using the sql command Backup database xxx to
>> >disk
>> >=>> > 'yyy'
>> >
>> > When it completed the file size of my database approximately tripled.
>> > Did
>> > I
>> > just corrupt some data? The application appears to continue to run ok.
>> > I
>> > have experienced performance problems in the past and assumed it was
>> > hardware
>> > related. Maybe my database was just sized too small'?
>>|||is this sql 2005, and do you have full-text indexing? If so, expect large
backup files.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"doug" <doug@.discussions.microsoft.com> wrote in message
news:D9566F87-EC93-4950-9B59-0CF936AF0D74@.microsoft.com...
>I backed up my database using the sql command Backup database xxx to disk
>=> 'yyy'
> When it completed the file size of my database approximately tripled. Did
> I
> just corrupt some data? The application appears to continue to run ok. I
> have experienced performance problems in the past and assumed it was
> hardware
> related. Maybe my database was just sized too small'?

backup causes file size to grow

I backed up my database using the sql command Backup database xxx to disk =
'yyy'
When it completed the file size of my database approximately tripled. Did I
just corrupt some data? The application appears to continue to run ok. I
have experienced performance problems in the past and assumed it was hardwar
e
related. Maybe my database was just sized too small'?Which file tripled? The database is made up of by default one data file and
one log file. Was it the data or log file that grew? If it was the log
file then you need to know that log backups can not finish during a full
backup. So if your log file was small to begin with and someone did a large
(or lots of ) transactions during the full backup the log file may have had
to grow since it could not be truncated during the full backup.
Andrew J. Kelly SQL MVP
"doug" <doug@.discussions.microsoft.com> wrote in message
news:D9566F87-EC93-4950-9B59-0CF936AF0D74@.microsoft.com...
>I backed up my database using the sql command Backup database xxx to disk
>=
> 'yyy'
> When it completed the file size of my database approximately tripled. Did
> I
> just corrupt some data? The application appears to continue to run ok. I
> have experienced performance problems in the past and assumed it was
> hardware
> related. Maybe my database was just sized too small'?|||It was the data file (.mdb) that grew. My log file (.ldf) is basically the
same size it was. I went from a 6gb database file to a 20gb database file.
Thanks in advance for any advice.
"Andrew J. Kelly" wrote:

> Which file tripled? The database is made up of by default one data file a
nd
> one log file. Was it the data or log file that grew? If it was the log
> file then you need to know that log backups can not finish during a full
> backup. So if your log file was small to begin with and someone did a lar
ge
> (or lots of ) transactions during the full backup the log file may have ha
d
> to grow since it could not be truncated during the full backup.
> --
> Andrew J. Kelly SQL MVP
>
> "doug" <doug@.discussions.microsoft.com> wrote in message
> news:D9566F87-EC93-4950-9B59-0CF936AF0D74@.microsoft.com...
>
>|||Are you positive that no one issued an ALTER DATABASE command that may have
grown the file? I have never heard of a db file growing from a backup
before. If it was due to autogrow there should have been lots of log
entries as well.
Andrew J. Kelly SQL MVP
"doug" <doug@.discussions.microsoft.com> wrote in message
news:68FFC5B6-9B4B-4194-A360-64FE8B2003AB@.microsoft.com...[vbcol=seagreen]
> It was the data file (.mdb) that grew. My log file (.ldf) is basically
> the
> same size it was. I went from a 6gb database file to a 20gb database
> file.
> Thanks in advance for any advice.
> "Andrew J. Kelly" wrote:
>|||is this sql 2005, and do you have full-text indexing? If so, expect large
backup files.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"doug" <doug@.discussions.microsoft.com> wrote in message
news:D9566F87-EC93-4950-9B59-0CF936AF0D74@.microsoft.com...
>I backed up my database using the sql command Backup database xxx to disk
>=
> 'yyy'
> When it completed the file size of my database approximately tripled. Did
> I
> just corrupt some data? The application appears to continue to run ok. I
> have experienced performance problems in the past and assumed it was
> hardware
> related. Maybe my database was just sized too small'?

Backup and Restore issue

I have a strange case. I do a full backup this morning. The size of
the backup is about 300MB which is 4 times larger than the actual
database file. Now when I restore the backup, some of the objects (eg
some tables) are not there!!.
Now I just do another backup, the size of this backup is now about
80MB, which is normal and when I restore this backup, I got all the
tables.
What am I missing? Did someone has similar experience?
Probably earlier you would have appended to a older backup file (WITH NOINIT
which is default).
That caused the backup file to show a bigger size. While restoring you
restore the first backup file
which was taken some days back and that do not have some new tables.
This is just my assumption. If you have the backup file which you has issues
just try
RESTORE HEADERONLY FROM DISK='Backupfilename.BAK'
The above command will give you all the backup sets in the backup file.
Thanks
Hari
"akkha1234@.gmail.com" wrote:

> I have a strange case. I do a full backup this morning. The size of
> the backup is about 300MB which is 4 times larger than the actual
> database file. Now when I restore the backup, some of the objects (eg
> some tables) are not there!!.
> Now I just do another backup, the size of this backup is now about
> 80MB, which is normal and when I restore this backup, I got all the
> tables.
> What am I missing? Did someone has similar experience?
>
|||Dear Hari,
You are absolutely correct. Learn one more thing on MSSQL again.

Backup and Restore issue

I have a strange case. I do a full backup this morning. The size of
the backup is about 300MB which is 4 times larger than the actual
database file. Now when I restore the backup, some of the objects (eg
some tables) are not there!!.
Now I just do another backup, the size of this backup is now about
80MB, which is normal and when I restore this backup, I got all the
tables.
What am I missing? Did someone has similar experience?Probably earlier you would have appended to a older backup file (WITH NOINIT
which is default).
That caused the backup file to show a bigger size. While restoring you
restore the first backup file
which was taken some days back and that do not have some new tables.
This is just my assumption. If you have the backup file which you has issues
just try
RESTORE HEADERONLY FROM DISK='Backupfilename.BAK'
The above command will give you all the backup sets in the backup file.
Thanks
Hari
"akkha1234@.gmail.com" wrote:
> I have a strange case. I do a full backup this morning. The size of
> the backup is about 300MB which is 4 times larger than the actual
> database file. Now when I restore the backup, some of the objects (eg
> some tables) are not there!!.
> Now I just do another backup, the size of this backup is now about
> 80MB, which is normal and when I restore this backup, I got all the
> tables.
> What am I missing? Did someone has similar experience?
>|||Dear Hari,
You are absolutely correct. Learn one more thing on MSSQL again.|||Thats good to know...
Thanks
Hari
<akkha1234@.gmail.com> wrote in message
news:1170369045.337711.234660@.l53g2000cwa.googlegroups.com...
> Dear Hari,
> You are absolutely correct. Learn one more thing on MSSQL again.
>

Backup and Restore issue

I have a strange case. I do a full backup this morning. The size of
the backup is about 300MB which is 4 times larger than the actual
database file. Now when I restore the backup, some of the objects (eg
some tables) are not there!!.
Now I just do another backup, the size of this backup is now about
80MB, which is normal and when I restore this backup, I got all the
tables.
What am I missing? Did someone has similar experience?Probably earlier you would have appended to a older backup file (WITH NOINIT
which is default).
That caused the backup file to show a bigger size. While restoring you
restore the first backup file
which was taken some days back and that do not have some new tables.
This is just my assumption. If you have the backup file which you has issues
just try
RESTORE HEADERONLY FROM DISK='Backupfilename.BAK'
The above command will give you all the backup sets in the backup file.
Thanks
Hari
"akkha1234@.gmail.com" wrote:

> I have a strange case. I do a full backup this morning. The size of
> the backup is about 300MB which is 4 times larger than the actual
> database file. Now when I restore the backup, some of the objects (eg
> some tables) are not there!!.
> Now I just do another backup, the size of this backup is now about
> 80MB, which is normal and when I restore this backup, I got all the
> tables.
> What am I missing? Did someone has similar experience?
>|||Dear Hari,
You are absolutely correct. Learn one more thing on MSSQL again.|||Thats good to know...
Thanks
Hari
<akkha1234@.gmail.com> wrote in message
news:1170369045.337711.234660@.l53g2000cwa.googlegroups.com...
> Dear Hari,
> You are absolutely correct. Learn one more thing on MSSQL again.
>

Backup and restore

What's the best method to back up windows 2003 with 50 GB data on to an
external USB hard drive. The external hard drive size is 250 GB. Can we do
FULL back up and differential back up on to the same drive? The server with
RAID configuration has SQL server 2000 on it for now. I will be loading
exchange server on to it in few days. The client is ok with loosing weeks
worth of data (ofcourse RAID is there) .
GHOST ... etc., what type of back up and restore is best in my situation.
The second question is, on the above server the active directory is not set
up. This machine is part of a domain server running on Linux. All users
are authenicated on linux server. Can I install Windows Exchange server
2003 on to it? Any complications or pre reqs to do so.
Thanks in Advance
BVRHi
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:uZ5us4FWDHA.1640@.TK2MSFTNGP10.phx.gbl...
> What's the best method to back up windows 2003 with 50 GB data on to an
> external USB hard drive. The external hard drive size is 250 GB. Can we
do
> FULL back up and differential back up on to the same drive? The server
with
Yes you can , I suggest you get some good backup software like arcserver.
While the Windows backup will work better recovery software is needed.
Yes you can do complete and differential backups on the same drive
> RAID configuration has SQL server 2000 on it for now.
You should backup all databases before the windows backup runs, transaction
logs should be backed up every hour
>I will be loading
> exchange server on to it in few days. The client is ok with loosing weeks
> worth of data (ofcourse RAID is there) .
Can I have this client they seem very easy to please. I hope the client is
OK in losing all perfromance out of the SQL box! SQL and exchanged are both
hogs and shouldn't rn on the same machine. Next you are gong to tell me you
run File/Print sharing and IIS as well!!
> GHOST ... etc., what type of back up and restore is best in my
situation.
Archserver has a very good restore option, from CD or disk
> The second question is, on the above server the active directory is not
set
> up. This machine is part of a domain server running on Linux. All users
> are authenicated on linux server. Can I install Windows Exchange server
> 2003 on to it? Any complications or pre reqs to do so.
I wouldn't have thought SQL Server runs of Linux are you using an emulation
package, if so you would already have perfromance problems :)
> Thanks in Advance
> BVR
Suggestions, Get a big tape drive, while slower than DISK more flexible and
cheaper (TAPES vs DISK) you also get more backup/recover options and at the
end of the day what's the use of backing up if you can't restore.
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction
program
http://www.ag-software.com. Free programming tools

Backup /restore DB

hi,
I want to know if there is a way - other than backup and restore database (because restore takes too much time as the DB size grows) - to transfer all the data from one database on the server to another on a local SQLServer. it has to be done daily, so that the manager will always have an updated copy of the data on his machine.

Any help?
Thanks in advanceYou can enable Replication instead.