Hello, I am having a problem with backup devices, and
hope someone knows a solution.
I have created a backup device on my 100GB D: drive
called Mustang_Backup. The file is located at
D:\MSSQL\BACKUP\Mustang_Backup.BAK.
My maintenance plan is a full backup on Sunday and a
differential on all other days. These are working
correctly.
My server also has a 14 day retention setting.
My problem is that the backup device is not removing the
expired backups. I now have a 75GB backup file for a 3GB
database. I am concerned that the backups are going to
fill the entire drive and then start to fail.
Does anyone know of a way to delete or remove the expired
backups in a device?
Thanks for your help,
SteveYou can't keep the different backups in one device if you want to remove
some of them after a certain time. It's all or nothing in a single device.
If your using the MP then have it create different files for each backup and
it should work as expected.
--
Andrew J. Kelly
SQL Server MVP
"steve" <sgent001@.hotmail.com> wrote in message
news:055b01c35556$78041df0$a301280a@.phx.gbl...
> Hello, I am having a problem with backup devices, and
> hope someone knows a solution.
> I have created a backup device on my 100GB D: drive
> called Mustang_Backup. The file is located at
> D:\MSSQL\BACKUP\Mustang_Backup.BAK.
> My maintenance plan is a full backup on Sunday and a
> differential on all other days. These are working
> correctly.
> My server also has a 14 day retention setting.
> My problem is that the backup device is not removing the
> expired backups. I now have a 75GB backup file for a 3GB
> database. I am concerned that the backups are going to
> fill the entire drive and then start to fail.
> Does anyone know of a way to delete or remove the expired
> backups in a device?
> Thanks for your help,
> Stevesql
Showing posts with label solution. Show all posts
Showing posts with label solution. Show all posts
Thursday, March 22, 2012
Tuesday, March 20, 2012
Backup DB on compressed folder
Hi I have a very strange case here.
Currently our db server is short of space and as a temp solution database is backed up to a compressed folder. OS is W2K advanced Edition and SQL is Enterprise version. Database is 62GB now. every time Db backup fails after backup running for 2hrs and hal
f. the error msg says that "18210 :
BackupMedium::ReportIoError: write failure on backup device 'd:\mssql\backup\DataDMP01.BAK'. Operating system error 33(The process cannot access the file because another process has locked a portion of the file.)."
we have tried another file name or rebooting the server, but none helps.
I doubt that compressed fold causes the problem or exceeds the file size limit, but can not find any clue on it.
Pls help.
thanks for reply.
George
Compressed drives are not supported for sql server databases and to the best
of my knowledge backups either. If you are low on disk space I would
suggest using a product such as SQL LiteSpeed.
Andrew J. Kelly SQL MVP
"george" <georgey@.cryptologic.com> wrote in message
news:D916C3F2-92F3-405C-9951-39D3E1D095CC@.microsoft.com...
> Hi I have a very strange case here.
> Currently our db server is short of space and as a temp solution database
is backed up to a compressed folder. OS is W2K advanced Edition and SQL is
Enterprise version. Database is 62GB now. every time Db backup fails after
backup running for 2hrs and half. the error msg says that "18210 :
> BackupMedium::ReportIoError: write failure on backup device
'd:\mssql\backup\DataDMP01.BAK'. Operating system error 33(The process
cannot access the file because another process has locked a portion of the
file.)."
> we have tried another file name or rebooting the server, but none helps.
> I doubt that compressed fold causes the problem or exceeds the file size
limit, but can not find any clue on it.
> Pls help.
> thanks for reply.
> George
Currently our db server is short of space and as a temp solution database is backed up to a compressed folder. OS is W2K advanced Edition and SQL is Enterprise version. Database is 62GB now. every time Db backup fails after backup running for 2hrs and hal
f. the error msg says that "18210 :
BackupMedium::ReportIoError: write failure on backup device 'd:\mssql\backup\DataDMP01.BAK'. Operating system error 33(The process cannot access the file because another process has locked a portion of the file.)."
we have tried another file name or rebooting the server, but none helps.
I doubt that compressed fold causes the problem or exceeds the file size limit, but can not find any clue on it.
Pls help.
thanks for reply.
George
Compressed drives are not supported for sql server databases and to the best
of my knowledge backups either. If you are low on disk space I would
suggest using a product such as SQL LiteSpeed.
Andrew J. Kelly SQL MVP
"george" <georgey@.cryptologic.com> wrote in message
news:D916C3F2-92F3-405C-9951-39D3E1D095CC@.microsoft.com...
> Hi I have a very strange case here.
> Currently our db server is short of space and as a temp solution database
is backed up to a compressed folder. OS is W2K advanced Edition and SQL is
Enterprise version. Database is 62GB now. every time Db backup fails after
backup running for 2hrs and half. the error msg says that "18210 :
> BackupMedium::ReportIoError: write failure on backup device
'd:\mssql\backup\DataDMP01.BAK'. Operating system error 33(The process
cannot access the file because another process has locked a portion of the
file.)."
> we have tried another file name or rebooting the server, but none helps.
> I doubt that compressed fold causes the problem or exceeds the file size
limit, but can not find any clue on it.
> Pls help.
> thanks for reply.
> George
Backup DB on compressed folder
Hi I have a very strange case here.
Currently our db server is short of space and as a temp solution database is
backed up to a compressed folder. OS is W2K advanced Edition and SQL is Ent
erprise version. Database is 62GB now. every time Db backup fails after back
up running for 2hrs and hal
f. the error msg says that "18210 :
BackupMedium::ReportIoError: write failure on backup device 'd:\mssql\backup
\DataDMP01.BAK'. Operating system error 33(The process cannot access the fil
e because another process has locked a portion of the file.)."
we have tried another file name or rebooting the server, but none helps.
I doubt that compressed fold causes the problem or exceeds the file size lim
it, but can not find any clue on it.
Pls help.
thanks for reply.
GeorgeCompressed drives are not supported for sql server databases and to the best
of my knowledge backups either. If you are low on disk space I would
suggest using a product such as SQL LiteSpeed.
Andrew J. Kelly SQL MVP
"george" <georgey@.cryptologic.com> wrote in message
news:D916C3F2-92F3-405C-9951-39D3E1D095CC@.microsoft.com...
> Hi I have a very strange case here.
> Currently our db server is short of space and as a temp solution database
is backed up to a compressed folder. OS is W2K advanced Edition and SQL is
Enterprise version. Database is 62GB now. every time Db backup fails after
backup running for 2hrs and half. the error msg says that "18210 :
> BackupMedium::ReportIoError: write failure on backup device
'd:\mssql\backup\DataDMP01.BAK'. Operating system error 33(The process
cannot access the file because another process has locked a portion of the
file.)."
> we have tried another file name or rebooting the server, but none helps.
> I doubt that compressed fold causes the problem or exceeds the file size
limit, but can not find any clue on it.
> Pls help.
> thanks for reply.
> George
Currently our db server is short of space and as a temp solution database is
backed up to a compressed folder. OS is W2K advanced Edition and SQL is Ent
erprise version. Database is 62GB now. every time Db backup fails after back
up running for 2hrs and hal
f. the error msg says that "18210 :
BackupMedium::ReportIoError: write failure on backup device 'd:\mssql\backup
\DataDMP01.BAK'. Operating system error 33(The process cannot access the fil
e because another process has locked a portion of the file.)."
we have tried another file name or rebooting the server, but none helps.
I doubt that compressed fold causes the problem or exceeds the file size lim
it, but can not find any clue on it.
Pls help.
thanks for reply.
GeorgeCompressed drives are not supported for sql server databases and to the best
of my knowledge backups either. If you are low on disk space I would
suggest using a product such as SQL LiteSpeed.
Andrew J. Kelly SQL MVP
"george" <georgey@.cryptologic.com> wrote in message
news:D916C3F2-92F3-405C-9951-39D3E1D095CC@.microsoft.com...
> Hi I have a very strange case here.
> Currently our db server is short of space and as a temp solution database
is backed up to a compressed folder. OS is W2K advanced Edition and SQL is
Enterprise version. Database is 62GB now. every time Db backup fails after
backup running for 2hrs and half. the error msg says that "18210 :
> BackupMedium::ReportIoError: write failure on backup device
'd:\mssql\backup\DataDMP01.BAK'. Operating system error 33(The process
cannot access the file because another process has locked a portion of the
file.)."
> we have tried another file name or rebooting the server, but none helps.
> I doubt that compressed fold causes the problem or exceeds the file size
limit, but can not find any clue on it.
> Pls help.
> thanks for reply.
> George
Sunday, February 12, 2012
backuk technic sql server 2000
Hello,
i have two questions :
1 - it's a better solution to save a database SQL with
log technic than a full save ' For a good restoration.
Without speak of all data restore possibility , just the
technic of restore.
2 - it's true that it's necessary to have the double disk
space to restore a full database ? Ex : 30 Go free space
to restore a database of 15 Go
Thanks for responseIt is depend what kind of restore possibility do you want.
If you will make only FULL BACKUP with Recovery Model Simple(SQL 2K) or
'Truncate Log on chk. point' checked(SQL 7)
you will not able to restore DB on any point of time. Aslo you will lose all
changes since last FULL BACKUP in case of DB corruption.
With Tr.Log BACKUP for example you will make FULL BACKUP once per some
period of time. Let say every night.
And then LOG BACKUP every let say hour.
So you will have the chain:
FULL BACKUP
LOG BACKUP
LOG NACKUP
LOG BACKUP
...
FULL BACKUP
In this scenario in case of DB corruption and if Log will corrupted too, you
will lose only data since last LOG BACKUP.
So, if you'll make LOG BACKUP hourly, maximun for one hour.
Regards
---
All information provided above AS IS.
"dtc" <anonymous@.discussions.microsoft.com> wrote in message
news:03fe01c39f18$6edb8ea0$a101280a@.phx.gbl...
> Hello,
> i have two questions :
> 1 - it's a better solution to save a database SQL with
> log technic than a full save ' For a good restoration.
> Without speak of all data restore possibility , just the
> technic of restore.
> 2 - it's true that it's necessary to have the double disk
> space to restore a full database ? Ex : 30 Go free space
> to restore a database of 15 Go
> Thanks for response
i have two questions :
1 - it's a better solution to save a database SQL with
log technic than a full save ' For a good restoration.
Without speak of all data restore possibility , just the
technic of restore.
2 - it's true that it's necessary to have the double disk
space to restore a full database ? Ex : 30 Go free space
to restore a database of 15 Go
Thanks for responseIt is depend what kind of restore possibility do you want.
If you will make only FULL BACKUP with Recovery Model Simple(SQL 2K) or
'Truncate Log on chk. point' checked(SQL 7)
you will not able to restore DB on any point of time. Aslo you will lose all
changes since last FULL BACKUP in case of DB corruption.
With Tr.Log BACKUP for example you will make FULL BACKUP once per some
period of time. Let say every night.
And then LOG BACKUP every let say hour.
So you will have the chain:
FULL BACKUP
LOG BACKUP
LOG NACKUP
LOG BACKUP
...
FULL BACKUP
In this scenario in case of DB corruption and if Log will corrupted too, you
will lose only data since last LOG BACKUP.
So, if you'll make LOG BACKUP hourly, maximun for one hour.
Regards
---
All information provided above AS IS.
"dtc" <anonymous@.discussions.microsoft.com> wrote in message
news:03fe01c39f18$6edb8ea0$a101280a@.phx.gbl...
> Hello,
> i have two questions :
> 1 - it's a better solution to save a database SQL with
> log technic than a full save ' For a good restoration.
> Without speak of all data restore possibility , just the
> technic of restore.
> 2 - it's true that it's necessary to have the double disk
> space to restore a full database ? Ex : 30 Go free space
> to restore a database of 15 Go
> Thanks for response
Backp Solution
Hi ,
What is the best Backup plan that can be give for a sql databse that is on a very high usage.
Will a six hour backup will decrease the performance of the SQL server...
SipinIt depends on a lot of things:
Type of db: OLTP vs. OLAP
Size of db: 2GB vs. 1TB
"High usage databases" I am assuming means an OLTP type of database not an OLAP db. With an OLTP db your data will be changing rapidly and I would recommend doing hourly transaction log backups with a daily full backup (if possible, depending on the size of the db).
If the size of the database is relatively small, (like 2 - 10 GB) doing a full backup once every 6 hours is okay. But if the database is larger in size I do not recommend doing full backups during transaction hours.
Realize that if you decide to go with backup every 6 hours, you run the risk of losing up to 6 hrs worth of work if something bad happens, as to hourly tlog backups you will only lose 1hrs worth of work. There is a price to pay however for hourly tlog backups, and that is more maintenance, and it will take longer to do a full recovery and then apply several tlogs.
Consider both the business and technical side of backups
Good luck
I hope this helps
What is the best Backup plan that can be give for a sql databse that is on a very high usage.
Will a six hour backup will decrease the performance of the SQL server...
SipinIt depends on a lot of things:
Type of db: OLTP vs. OLAP
Size of db: 2GB vs. 1TB
"High usage databases" I am assuming means an OLTP type of database not an OLAP db. With an OLTP db your data will be changing rapidly and I would recommend doing hourly transaction log backups with a daily full backup (if possible, depending on the size of the db).
If the size of the database is relatively small, (like 2 - 10 GB) doing a full backup once every 6 hours is okay. But if the database is larger in size I do not recommend doing full backups during transaction hours.
Realize that if you decide to go with backup every 6 hours, you run the risk of losing up to 6 hrs worth of work if something bad happens, as to hourly tlog backups you will only lose 1hrs worth of work. There is a price to pay however for hourly tlog backups, and that is more maintenance, and it will take longer to do a full recovery and then apply several tlogs.
Consider both the business and technical side of backups
Good luck
I hope this helps
Subscribe to:
Posts (Atom)