hi,
in my event log I have the alarm Backup failed to complete command
sp_executesql;1
The point is that I didn't find any scheduled backup jobs and I didn't find
any *.bak file relative to the DB mentioned in the alarm. (I can see the DB
file name in the text window at the bottom of the detail in Windows event
log )
I suspect that it's hidden in a stored procedure or somewhere else. How can
I find it?
I have two DB on the system pointed by this alarm and they're quite large 20
& 25gig so if a backup file was create I think I wouldn't have difficulty
to find them.
I didn't find any infos on the net about this alarm so if someone can give
me some the help me to search deeper.
Regards
MarcDo you have any third party products installed on the server, that perform
database backups? Worth checking under Start > Programs
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"marc quirion" <mquirion@.videotron.ca> wrote in message
news:SfKFe.92489$mv2.1552703@.weber.videotron.net...
hi,
in my event log I have the alarm Backup failed to complete command
sp_executesql;1
The point is that I didn't find any scheduled backup jobs and I didn't find
any *.bak file relative to the DB mentioned in the alarm. (I can see the DB
file name in the text window at the bottom of the detail in Windows event
log )
I suspect that it's hidden in a stored procedure or somewhere else. How can
I find it?
I have two DB on the system pointed by this alarm and they're quite large 20
& 25gig so if a backup file was create I think I wouldn't have difficulty
to find them.
I didn't find any infos on the net about this alarm so if someone can give
me some the help me to search deeper.
Regards
Marc
Showing posts with label scheduled. Show all posts
Showing posts with label scheduled. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
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
>>
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 had a backup scheduled for a a time when people are using the website.
I got this error...
59System.Data.SqlClient.SqlException: General network error. Check your
network documentation
Shouldn't you be able to backup while people are using the database ?
It is about 4.5 Gigs in size -- would that make a difference ?
Thanks,
CraigThere is a timeout property that you need to increase.
It seems like backup in your case takes for a while and client
just doesn't wait til end of it.
Connection timeou
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlconnectionclassconnectionstringtopic.asp
Command timeou
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlcommandclasscommandtimeouttopic.asp
Regards.
"Craig HB" wrote:
> I had a backup scheduled for a a time when people are using the website.
> I got this error...
> 59System.Data.SqlClient.SqlException: General network error. Check your
> network documentation
> Shouldn't you be able to backup while people are using the database ?
> It is about 4.5 Gigs in size -- would that make a difference ?
> Thanks,
> Craig|||Hi
Yes you should be able to backup. General network error may mean that the
client is timing out, therefore you may want to increase the timeout or use a
scheduled task instead.
John
"Craig HB" wrote:
> I had a backup scheduled for a a time when people are using the website.
> I got this error...
> 59System.Data.SqlClient.SqlException: General network error. Check your
> network documentation
> Shouldn't you be able to backup while people are using the database ?
> It is about 4.5 Gigs in size -- would that make a difference ?
> Thanks,
> Craig|||Hi,
Yes, Backup is an online operation. Database size is not an issue for the
backup. Could you install SP3a or SP 4 (if not AWE enabled)
and try to do a backup again from Server machine Query analyzer (USE the
below command).
Backup database <dbname> to disk='d:\backup\dbname.bak' with init,stats=10
Thanks
Hari
SQL Server MVP
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:532C1A36-2C2E-4F06-B0F0-7F2342D144E6@.microsoft.com...
>I had a backup scheduled for a a time when people are using the website.
> I got this error...
> 59System.Data.SqlClient.SqlException: General network error. Check your
> network documentation
> Shouldn't you be able to backup while people are using the database ?
> It is about 4.5 Gigs in size -- would that make a difference ?
> Thanks,
> Craig
I got this error...
59System.Data.SqlClient.SqlException: General network error. Check your
network documentation
Shouldn't you be able to backup while people are using the database ?
It is about 4.5 Gigs in size -- would that make a difference ?
Thanks,
CraigThere is a timeout property that you need to increase.
It seems like backup in your case takes for a while and client
just doesn't wait til end of it.
Connection timeou
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlconnectionclassconnectionstringtopic.asp
Command timeou
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlcommandclasscommandtimeouttopic.asp
Regards.
"Craig HB" wrote:
> I had a backup scheduled for a a time when people are using the website.
> I got this error...
> 59System.Data.SqlClient.SqlException: General network error. Check your
> network documentation
> Shouldn't you be able to backup while people are using the database ?
> It is about 4.5 Gigs in size -- would that make a difference ?
> Thanks,
> Craig|||Hi
Yes you should be able to backup. General network error may mean that the
client is timing out, therefore you may want to increase the timeout or use a
scheduled task instead.
John
"Craig HB" wrote:
> I had a backup scheduled for a a time when people are using the website.
> I got this error...
> 59System.Data.SqlClient.SqlException: General network error. Check your
> network documentation
> Shouldn't you be able to backup while people are using the database ?
> It is about 4.5 Gigs in size -- would that make a difference ?
> Thanks,
> Craig|||Hi,
Yes, Backup is an online operation. Database size is not an issue for the
backup. Could you install SP3a or SP 4 (if not AWE enabled)
and try to do a backup again from Server machine Query analyzer (USE the
below command).
Backup database <dbname> to disk='d:\backup\dbname.bak' with init,stats=10
Thanks
Hari
SQL Server MVP
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:532C1A36-2C2E-4F06-B0F0-7F2342D144E6@.microsoft.com...
>I had a backup scheduled for a a time when people are using the website.
> I got this error...
> 59System.Data.SqlClient.SqlException: General network error. Check your
> network documentation
> Shouldn't you be able to backup while people are using the database ?
> It is about 4.5 Gigs in size -- would that make a difference ?
> Thanks,
> Craig
Backup Error
Hi Guys,
I am facing a problem with sql server 2000 sp3 backup to network.This backuP
is as part of scheduled backup to another server in the same network and was
working fine for the last 3 yrs But Recently the backUP is failing with
following errors
BackupMedium::ReportIoError: write failure on backup device
'\\192.168.30.231\dbbackups\rptserver\Revenues_db_200602280100.BAK'.
Operating system error 121(The semaphore timeout period has expired.).
Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
m_actualBytes: 0, m_errcode: 121, BackupFile:
\\192.168.30.231\dbbackups\rptserver\Revenues_db_200602280100.BAK
I have formateed the destination drive to where backup is taking place but
still same error.
the backup file size is 8.5 GB.
Pls Aedvice.Not to alarm you but I found something out on the web about this strange
error...
http://www.thescripts.com/forum/thread78842.html
might worth the look...
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> a écrit
dans le message de news:
281CE432-4B50-44CE-A299-19653E1AEF37@.microsoft.com...
> Hi Guys,
> I am facing a problem with sql server 2000 sp3 backup to network.This
> backuP
> is as part of scheduled backup to another server in the same network and
> was
> working fine for the last 3 yrs But Recently the backUP is failing with
> following errors
> BackupMedium::ReportIoError: write failure on backup device
> '\\192.168.30.231\dbbackups\rptserver\Revenues_db_200602280100.BAK'.
> Operating system error 121(The semaphore timeout period has expired.).
> Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
> 983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
> Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 121, BackupFile:
> \\192.168.30.231\dbbackups\rptserver\Revenues_db_200602280100.BAK
> I have formateed the destination drive to where backup is taking place but
> still same error.
> the backup file size is 8.5 GB.
> Pls Aedvice.|||my daily backup failed one night after I applied one of monthly patches from
MS. Removing the patch fixed the problem. I called MS. At first they
asked me to update the drivers but I did everything before I called them.
Then they blamed the hardware but I threw back I was using a Compaq server
with compatible h/w to run the system per MS. Later they showed me to
generate a server dump and sent it to MS.
After more than a week with lot of calls, and 3 dumps sent to MS, they said
it was a bug but won't fix it since it just happen to this server with this
controller.
p.s. my server is Compaq DL380, windows 2000, sql 2000 sp4.
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
message news:281CE432-4B50-44CE-A299-19653E1AEF37@.microsoft.com...
> Hi Guys,
> I am facing a problem with sql server 2000 sp3 backup to network.This
> backuP
> is as part of scheduled backup to another server in the same network and
> was
> working fine for the last 3 yrs But Recently the backUP is failing with
> following errors
> BackupMedium::ReportIoError: write failure on backup device
> '\\192.168.30.231\dbbackups\rptserver\Revenues_db_200602280100.BAK'.
> Operating system error 121(The semaphore timeout period has expired.).
> Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
> 983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
> Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 121, BackupFile:
> \\192.168.30.231\dbbackups\rptserver\Revenues_db_200602280100.BAK
> I have formateed the destination drive to where backup is taking place but
> still same error.
> the backup file size is 8.5 GB.
> Pls Aedvice.
I am facing a problem with sql server 2000 sp3 backup to network.This backuP
is as part of scheduled backup to another server in the same network and was
working fine for the last 3 yrs But Recently the backUP is failing with
following errors
BackupMedium::ReportIoError: write failure on backup device
'\\192.168.30.231\dbbackups\rptserver\Revenues_db_200602280100.BAK'.
Operating system error 121(The semaphore timeout period has expired.).
Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
m_actualBytes: 0, m_errcode: 121, BackupFile:
\\192.168.30.231\dbbackups\rptserver\Revenues_db_200602280100.BAK
I have formateed the destination drive to where backup is taking place but
still same error.
the backup file size is 8.5 GB.
Pls Aedvice.Not to alarm you but I found something out on the web about this strange
error...
http://www.thescripts.com/forum/thread78842.html
might worth the look...
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> a écrit
dans le message de news:
281CE432-4B50-44CE-A299-19653E1AEF37@.microsoft.com...
> Hi Guys,
> I am facing a problem with sql server 2000 sp3 backup to network.This
> backuP
> is as part of scheduled backup to another server in the same network and
> was
> working fine for the last 3 yrs But Recently the backUP is failing with
> following errors
> BackupMedium::ReportIoError: write failure on backup device
> '\\192.168.30.231\dbbackups\rptserver\Revenues_db_200602280100.BAK'.
> Operating system error 121(The semaphore timeout period has expired.).
> Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
> 983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
> Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 121, BackupFile:
> \\192.168.30.231\dbbackups\rptserver\Revenues_db_200602280100.BAK
> I have formateed the destination drive to where backup is taking place but
> still same error.
> the backup file size is 8.5 GB.
> Pls Aedvice.|||my daily backup failed one night after I applied one of monthly patches from
MS. Removing the patch fixed the problem. I called MS. At first they
asked me to update the drivers but I did everything before I called them.
Then they blamed the hardware but I threw back I was using a Compaq server
with compatible h/w to run the system per MS. Later they showed me to
generate a server dump and sent it to MS.
After more than a week with lot of calls, and 3 dumps sent to MS, they said
it was a bug but won't fix it since it just happen to this server with this
controller.
p.s. my server is Compaq DL380, windows 2000, sql 2000 sp4.
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
message news:281CE432-4B50-44CE-A299-19653E1AEF37@.microsoft.com...
> Hi Guys,
> I am facing a problem with sql server 2000 sp3 backup to network.This
> backuP
> is as part of scheduled backup to another server in the same network and
> was
> working fine for the last 3 yrs But Recently the backUP is failing with
> following errors
> BackupMedium::ReportIoError: write failure on backup device
> '\\192.168.30.231\dbbackups\rptserver\Revenues_db_200602280100.BAK'.
> Operating system error 121(The semaphore timeout period has expired.).
> Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
> 983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
> Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 121, BackupFile:
> \\192.168.30.231\dbbackups\rptserver\Revenues_db_200602280100.BAK
> I have formateed the destination drive to where backup is taking place but
> still same error.
> the backup file size is 8.5 GB.
> Pls Aedvice.
Backup error
I have this error:
SQL Server Scheduled Task: 43, 'BackupIBMDat - tempdb' --
Status: Failed
-- Task Invoked on: 1/21/04 1:30:00 AM -- Message: Can't
open dump device '\\.\TAPE0', device error or device off
line. Please consult the SQL Server error log for more
details. (Message 3201)
please help meso does SQL Server error log have anything regarding this?
see if you can run a backup to that \\TAPE0 device. Is the spelling right
for the device name?
"Whill" <anonymous@.discussions.microsoft.com> wrote in message
news:1c8d01c3e050$a5d94130$a001280a@.phx.gbl...
> I have this error:
> SQL Server Scheduled Task: 43, 'BackupIBMDat - tempdb' --
> Status: Failed
> -- Task Invoked on: 1/21/04 1:30:00 AM -- Message: Can't
> open dump device '\\.\TAPE0', device error or device off
> line. Please consult the SQL Server error log for more
> details. (Message 3201)
> please help me
SQL Server Scheduled Task: 43, 'BackupIBMDat - tempdb' --
Status: Failed
-- Task Invoked on: 1/21/04 1:30:00 AM -- Message: Can't
open dump device '\\.\TAPE0', device error or device off
line. Please consult the SQL Server error log for more
details. (Message 3201)
please help meso does SQL Server error log have anything regarding this?
see if you can run a backup to that \\TAPE0 device. Is the spelling right
for the device name?
"Whill" <anonymous@.discussions.microsoft.com> wrote in message
news:1c8d01c3e050$a5d94130$a001280a@.phx.gbl...
> I have this error:
> SQL Server Scheduled Task: 43, 'BackupIBMDat - tempdb' --
> Status: Failed
> -- Task Invoked on: 1/21/04 1:30:00 AM -- Message: Can't
> open dump device '\\.\TAPE0', device error or device off
> line. Please consult the SQL Server error log for more
> details. (Message 3201)
> please help me
Backup Error
Hi Guys,
I am facing a problem with sql server 2000 sp3 backup to network.This backuP
is as part of scheduled backup to another server in the same network and was
working fine for the last 3 yrs But Recently the backUP is failing with
following errors
BackupMedium::ReportIoError: write failure on backup device
'\\192.168.30.231\dbbackups\rptserver\Revenues_db_ 200602280100.BAK'.
Operating system error 121(The semaphore timeout period has expired.).
Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
m_actualBytes: 0, m_errcode: 121, BackupFile:
\\192.168.30.231\dbbackups\rptserver\Revenues_db_2 00602280100.BAK
I have formateed the destination drive to where backup is taking place but
still same error.
the backup file size is 8.5 GB.
Pls Aedvice.
Not to alarm you but I found something out on the web about this strange
error...
http://www.thescripts.com/forum/thread78842.html
might worth the look...
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> a crit
dans le message de news:
281CE432-4B50-44CE-A299-19653E1AEF37@.microsoft.com...
> Hi Guys,
> I am facing a problem with sql server 2000 sp3 backup to network.This
> backuP
> is as part of scheduled backup to another server in the same network and
> was
> working fine for the last 3 yrs But Recently the backUP is failing with
> following errors
> BackupMedium::ReportIoError: write failure on backup device
> '\\192.168.30.231\dbbackups\rptserver\Revenues_db_ 200602280100.BAK'.
> Operating system error 121(The semaphore timeout period has expired.).
> Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
> 983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
> Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 121, BackupFile:
> \\192.168.30.231\dbbackups\rptserver\Revenues_db_2 00602280100.BAK
> I have formateed the destination drive to where backup is taking place but
> still same error.
> the backup file size is 8.5 GB.
> Pls Aedvice.
|||my daily backup failed one night after I applied one of monthly patches from
MS. Removing the patch fixed the problem. I called MS. At first they
asked me to update the drivers but I did everything before I called them.
Then they blamed the hardware but I threw back I was using a Compaq server
with compatible h/w to run the system per MS. Later they showed me to
generate a server dump and sent it to MS.
After more than a week with lot of calls, and 3 dumps sent to MS, they said
it was a bug but won't fix it since it just happen to this server with this
controller.
p.s. my server is Compaq DL380, windows 2000, sql 2000 sp4.
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
message news:281CE432-4B50-44CE-A299-19653E1AEF37@.microsoft.com...
> Hi Guys,
> I am facing a problem with sql server 2000 sp3 backup to network.This
> backuP
> is as part of scheduled backup to another server in the same network and
> was
> working fine for the last 3 yrs But Recently the backUP is failing with
> following errors
> BackupMedium::ReportIoError: write failure on backup device
> '\\192.168.30.231\dbbackups\rptserver\Revenues_db_ 200602280100.BAK'.
> Operating system error 121(The semaphore timeout period has expired.).
> Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
> 983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
> Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 121, BackupFile:
> \\192.168.30.231\dbbackups\rptserver\Revenues_db_2 00602280100.BAK
> I have formateed the destination drive to where backup is taking place but
> still same error.
> the backup file size is 8.5 GB.
> Pls Aedvice.
I am facing a problem with sql server 2000 sp3 backup to network.This backuP
is as part of scheduled backup to another server in the same network and was
working fine for the last 3 yrs But Recently the backUP is failing with
following errors
BackupMedium::ReportIoError: write failure on backup device
'\\192.168.30.231\dbbackups\rptserver\Revenues_db_ 200602280100.BAK'.
Operating system error 121(The semaphore timeout period has expired.).
Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
m_actualBytes: 0, m_errcode: 121, BackupFile:
\\192.168.30.231\dbbackups\rptserver\Revenues_db_2 00602280100.BAK
I have formateed the destination drive to where backup is taking place but
still same error.
the backup file size is 8.5 GB.
Pls Aedvice.
Not to alarm you but I found something out on the web about this strange
error...
http://www.thescripts.com/forum/thread78842.html
might worth the look...
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> a crit
dans le message de news:
281CE432-4B50-44CE-A299-19653E1AEF37@.microsoft.com...
> Hi Guys,
> I am facing a problem with sql server 2000 sp3 backup to network.This
> backuP
> is as part of scheduled backup to another server in the same network and
> was
> working fine for the last 3 yrs But Recently the backUP is failing with
> following errors
> BackupMedium::ReportIoError: write failure on backup device
> '\\192.168.30.231\dbbackups\rptserver\Revenues_db_ 200602280100.BAK'.
> Operating system error 121(The semaphore timeout period has expired.).
> Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
> 983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
> Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 121, BackupFile:
> \\192.168.30.231\dbbackups\rptserver\Revenues_db_2 00602280100.BAK
> I have formateed the destination drive to where backup is taking place but
> still same error.
> the backup file size is 8.5 GB.
> Pls Aedvice.
|||my daily backup failed one night after I applied one of monthly patches from
MS. Removing the patch fixed the problem. I called MS. At first they
asked me to update the drivers but I did everything before I called them.
Then they blamed the hardware but I threw back I was using a Compaq server
with compatible h/w to run the system per MS. Later they showed me to
generate a server dump and sent it to MS.
After more than a week with lot of calls, and 3 dumps sent to MS, they said
it was a bug but won't fix it since it just happen to this server with this
controller.
p.s. my server is Compaq DL380, windows 2000, sql 2000 sp4.
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
message news:281CE432-4B50-44CE-A299-19653E1AEF37@.microsoft.com...
> Hi Guys,
> I am facing a problem with sql server 2000 sp3 backup to network.This
> backuP
> is as part of scheduled backup to another server in the same network and
> was
> working fine for the last 3 yrs But Recently the backUP is failing with
> following errors
> BackupMedium::ReportIoError: write failure on backup device
> '\\192.168.30.231\dbbackups\rptserver\Revenues_db_ 200602280100.BAK'.
> Operating system error 121(The semaphore timeout period has expired.).
> Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
> 983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
> Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 121, BackupFile:
> \\192.168.30.231\dbbackups\rptserver\Revenues_db_2 00602280100.BAK
> I have formateed the destination drive to where backup is taking place but
> still same error.
> the backup file size is 8.5 GB.
> Pls Aedvice.
Thursday, March 22, 2012
BackUp error
I had a backup scheduled for a a time when people are using the website.
I got this error...
59System.Data.SqlClient.SqlException: General network error. Check your
network documentation
Shouldn't you be able to backup while people are using the database ?
It is about 4.5 Gigs in size -- would that make a difference ?
Thanks,
Craig
Hi
Yes you should be able to backup. General network error may mean that the
client is timing out, therefore you may want to increase the timeout or use a
scheduled task instead.
John
"Craig HB" wrote:
> I had a backup scheduled for a a time when people are using the website.
> I got this error...
> 59System.Data.SqlClient.SqlException: General network error. Check your
> network documentation
> Shouldn't you be able to backup while people are using the database ?
> It is about 4.5 Gigs in size -- would that make a difference ?
> Thanks,
> Craig
|||Hi,
Yes, Backup is an online operation. Database size is not an issue for the
backup. Could you install SP3a or SP 4 (if not AWE enabled)
and try to do a backup again from Server machine Query analyzer (USE the
below command).
Backup database <dbname> to disk='d:\backup\dbname.bak' with init,stats=10
Thanks
Hari
SQL Server MVP
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:532C1A36-2C2E-4F06-B0F0-7F2342D144E6@.microsoft.com...
>I had a backup scheduled for a a time when people are using the website.
> I got this error...
> 59System.Data.SqlClient.SqlException: General network error. Check your
> network documentation
> Shouldn't you be able to backup while people are using the database ?
> It is about 4.5 Gigs in size -- would that make a difference ?
> Thanks,
> Craig
I got this error...
59System.Data.SqlClient.SqlException: General network error. Check your
network documentation
Shouldn't you be able to backup while people are using the database ?
It is about 4.5 Gigs in size -- would that make a difference ?
Thanks,
Craig
Hi
Yes you should be able to backup. General network error may mean that the
client is timing out, therefore you may want to increase the timeout or use a
scheduled task instead.
John
"Craig HB" wrote:
> I had a backup scheduled for a a time when people are using the website.
> I got this error...
> 59System.Data.SqlClient.SqlException: General network error. Check your
> network documentation
> Shouldn't you be able to backup while people are using the database ?
> It is about 4.5 Gigs in size -- would that make a difference ?
> Thanks,
> Craig
|||Hi,
Yes, Backup is an online operation. Database size is not an issue for the
backup. Could you install SP3a or SP 4 (if not AWE enabled)
and try to do a backup again from Server machine Query analyzer (USE the
below command).
Backup database <dbname> to disk='d:\backup\dbname.bak' with init,stats=10
Thanks
Hari
SQL Server MVP
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:532C1A36-2C2E-4F06-B0F0-7F2342D144E6@.microsoft.com...
>I had a backup scheduled for a a time when people are using the website.
> I got this error...
> 59System.Data.SqlClient.SqlException: General network error. Check your
> network documentation
> Shouldn't you be able to backup while people are using the database ?
> It is about 4.5 Gigs in size -- would that make a difference ?
> Thanks,
> Craig
Backup Error
Hi Guys,
I am facing a problem with sql server 2000 sp3 backup to network.This backuP
is as part of scheduled backup to another server in the same network and was
working fine for the last 3 yrs But Recently the backUP is failing with
following errors
BackupMedium::ReportIoError: write failure on backup device
'\\192.168.30. 231\dbbackups\rptserver\Revenues_db_2006
02280100.BAK'.
Operating system error 121(The semaphore timeout period has expired.).
Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
m_actualBytes: 0, m_errcode: 121, BackupFile:
\\192.168.30. 231\dbbackups\rptserver\Revenues_db_2006
02280100.BAK
I have formateed the destination drive to where backup is taking place but
still same error.
the backup file size is 8.5 GB.
Pls Aedvice.Not to alarm you but I found something out on the web about this strange
error...
http://www.thescripts.com/forum/thread78842.html
might worth the look...
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> a crit
dans le message de news:
281CE432-4B50-44CE-A299-19653E1AEF37@.microsoft.com...
> Hi Guys,
> I am facing a problem with sql server 2000 sp3 backup to network.This
> backuP
> is as part of scheduled backup to another server in the same network and
> was
> working fine for the last 3 yrs But Recently the backUP is failing with
> following errors
> BackupMedium::ReportIoError: write failure on backup device
> '\\192.168.30. 231\dbbackups\rptserver\Revenues_db_2006
02280100.BAK'.
> Operating system error 121(The semaphore timeout period has expired.).
> Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
> 983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
> Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 121, BackupFile:
> \\192.168.30. 231\dbbackups\rptserver\Revenues_db_2006
02280100.BAK
> I have formateed the destination drive to where backup is taking place but
> still same error.
> the backup file size is 8.5 GB.
> Pls Aedvice.|||my daily backup failed one night after I applied one of monthly patches from
MS. Removing the patch fixed the problem. I called MS. At first they
asked me to update the drivers but I did everything before I called them.
Then they blamed the hardware but I threw back I was using a Compaq server
with compatible h/w to run the system per MS. Later they showed me to
generate a server dump and sent it to MS.
After more than a week with lot of calls, and 3 dumps sent to MS, they said
it was a bug but won't fix it since it just happen to this server with this
controller.
p.s. my server is Compaq DL380, windows 2000, sql 2000 sp4.
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
message news:281CE432-4B50-44CE-A299-19653E1AEF37@.microsoft.com...
> Hi Guys,
> I am facing a problem with sql server 2000 sp3 backup to network.This
> backuP
> is as part of scheduled backup to another server in the same network and
> was
> working fine for the last 3 yrs But Recently the backUP is failing with
> following errors
> BackupMedium::ReportIoError: write failure on backup device
> '\\192.168.30. 231\dbbackups\rptserver\Revenues_db_2006
02280100.BAK'.
> Operating system error 121(The semaphore timeout period has expired.).
> Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
> 983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
> Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 121, BackupFile:
> \\192.168.30. 231\dbbackups\rptserver\Revenues_db_2006
02280100.BAK
> I have formateed the destination drive to where backup is taking place but
> still same error.
> the backup file size is 8.5 GB.
> Pls Aedvice.
I am facing a problem with sql server 2000 sp3 backup to network.This backuP
is as part of scheduled backup to another server in the same network and was
working fine for the last 3 yrs But Recently the backUP is failing with
following errors
BackupMedium::ReportIoError: write failure on backup device
'\\192.168.30. 231\dbbackups\rptserver\Revenues_db_2006
02280100.BAK'.
Operating system error 121(The semaphore timeout period has expired.).
Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
m_actualBytes: 0, m_errcode: 121, BackupFile:
\\192.168.30. 231\dbbackups\rptserver\Revenues_db_2006
02280100.BAK
I have formateed the destination drive to where backup is taking place but
still same error.
the backup file size is 8.5 GB.
Pls Aedvice.Not to alarm you but I found something out on the web about this strange
error...
http://www.thescripts.com/forum/thread78842.html
might worth the look...
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> a crit
dans le message de news:
281CE432-4B50-44CE-A299-19653E1AEF37@.microsoft.com...
> Hi Guys,
> I am facing a problem with sql server 2000 sp3 backup to network.This
> backuP
> is as part of scheduled backup to another server in the same network and
> was
> working fine for the last 3 yrs But Recently the backUP is failing with
> following errors
> BackupMedium::ReportIoError: write failure on backup device
> '\\192.168.30. 231\dbbackups\rptserver\Revenues_db_2006
02280100.BAK'.
> Operating system error 121(The semaphore timeout period has expired.).
> Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
> 983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
> Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 121, BackupFile:
> \\192.168.30. 231\dbbackups\rptserver\Revenues_db_2006
02280100.BAK
> I have formateed the destination drive to where backup is taking place but
> still same error.
> the backup file size is 8.5 GB.
> Pls Aedvice.|||my daily backup failed one night after I applied one of monthly patches from
MS. Removing the patch fixed the problem. I called MS. At first they
asked me to update the drivers but I did everything before I called them.
Then they blamed the hardware but I threw back I was using a Compaq server
with compatible h/w to run the system per MS. Later they showed me to
generate a server dump and sent it to MS.
After more than a week with lot of calls, and 3 dumps sent to MS, they said
it was a bug but won't fix it since it just happen to this server with this
controller.
p.s. my server is Compaq DL380, windows 2000, sql 2000 sp4.
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
message news:281CE432-4B50-44CE-A299-19653E1AEF37@.microsoft.com...
> Hi Guys,
> I am facing a problem with sql server 2000 sp3 backup to network.This
> backuP
> is as part of scheduled backup to another server in the same network and
> was
> working fine for the last 3 yrs But Recently the backUP is failing with
> following errors
> BackupMedium::ReportIoError: write failure on backup device
> '\\192.168.30. 231\dbbackups\rptserver\Revenues_db_2006
02280100.BAK'.
> Operating system error 121(The semaphore timeout period has expired.).
> Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
> 983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
> Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 121, BackupFile:
> \\192.168.30. 231\dbbackups\rptserver\Revenues_db_2006
02280100.BAK
> I have formateed the destination drive to where backup is taking place but
> still same error.
> the backup file size is 8.5 GB.
> Pls Aedvice.
Backup error
I have this error:
SQL Server Scheduled Task: 43, 'BackupIBMDat - tempdb' --
Status: Failed
-- Task Invoked on: 1/21/04 1:30:00 AM -- Message: Can't
open dump device '\\.\TAPE0', device error or device off
line. Please consult the SQL Server error log for more
details. (Message 3201)
please help meso does SQL Server error log have anything regarding this?
see if you can run a backup to that \\TAPE0 device. Is the spelling right
for the device name?
"Whill" <anonymous@.discussions.microsoft.com> wrote in message
news:1c8d01c3e050$a5d94130$a001280a@.phx.gbl...
SQL Server Scheduled Task: 43, 'BackupIBMDat - tempdb' --
Status: Failed
-- Task Invoked on: 1/21/04 1:30:00 AM -- Message: Can't
open dump device '\\.\TAPE0', device error or device off
line. Please consult the SQL Server error log for more
details. (Message 3201)
please help meso does SQL Server error log have anything regarding this?
see if you can run a backup to that \\TAPE0 device. Is the spelling right
for the device name?
"Whill" <anonymous@.discussions.microsoft.com> wrote in message
news:1c8d01c3e050$a5d94130$a001280a@.phx.gbl...
quote:
> I have this error:
> SQL Server Scheduled Task: 43, 'BackupIBMDat - tempdb' --
> Status: Failed
> -- Task Invoked on: 1/21/04 1:30:00 AM -- Message: Can't
> open dump device '\\.\TAPE0', device error or device off
> line. Please consult the SQL Server error log for more
> details. (Message 3201)
> please help me
BackUp error
I had a backup scheduled for a a time when people are using the website.
I got this error...
59System.Data.SqlClient.SqlException: General network error. Check your
network documentation
Shouldn't you be able to backup while people are using the database ?
It is about 4.5 Gigs in size -- would that make a difference ?
Thanks,
CraigHi
Yes you should be able to backup. General network error may mean that the
client is timing out, therefore you may want to increase the timeout or use
a
scheduled task instead.
John
"Craig HB" wrote:
> I had a backup scheduled for a a time when people are using the website.
> I got this error...
> 59System.Data.SqlClient.SqlException: General network error. Check your
> network documentation
> Shouldn't you be able to backup while people are using the database ?
> It is about 4.5 Gigs in size -- would that make a difference ?
> Thanks,
> Craig|||Hi,
Yes, Backup is an online operation. Database size is not an issue for the
backup. Could you install SP3a or SP 4 (if not AWE enabled)
and try to do a backup again from Server machine Query analyzer (USE the
below command).
Backup database <dbname> to disk='d:\backup\dbname.bak' with init,stats=10
Thanks
Hari
SQL Server MVP
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:532C1A36-2C2E-4F06-B0F0-7F2342D144E6@.microsoft.com...
>I had a backup scheduled for a a time when people are using the website.
> I got this error...
> 59System.Data.SqlClient.SqlException: General network error. Check your
> network documentation
> Shouldn't you be able to backup while people are using the database ?
> It is about 4.5 Gigs in size -- would that make a difference ?
> Thanks,
> Craig
I got this error...
59System.Data.SqlClient.SqlException: General network error. Check your
network documentation
Shouldn't you be able to backup while people are using the database ?
It is about 4.5 Gigs in size -- would that make a difference ?
Thanks,
CraigHi
Yes you should be able to backup. General network error may mean that the
client is timing out, therefore you may want to increase the timeout or use
a
scheduled task instead.
John
"Craig HB" wrote:
> I had a backup scheduled for a a time when people are using the website.
> I got this error...
> 59System.Data.SqlClient.SqlException: General network error. Check your
> network documentation
> Shouldn't you be able to backup while people are using the database ?
> It is about 4.5 Gigs in size -- would that make a difference ?
> Thanks,
> Craig|||Hi,
Yes, Backup is an online operation. Database size is not an issue for the
backup. Could you install SP3a or SP 4 (if not AWE enabled)
and try to do a backup again from Server machine Query analyzer (USE the
below command).
Backup database <dbname> to disk='d:\backup\dbname.bak' with init,stats=10
Thanks
Hari
SQL Server MVP
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:532C1A36-2C2E-4F06-B0F0-7F2342D144E6@.microsoft.com...
>I had a backup scheduled for a a time when people are using the website.
> I got this error...
> 59System.Data.SqlClient.SqlException: General network error. Check your
> network documentation
> Shouldn't you be able to backup while people are using the database ?
> It is about 4.5 Gigs in size -- would that make a difference ?
> Thanks,
> Craig
Tuesday, March 20, 2012
Back-up DB but keep the files for 3 days?
Hi
I have created a job to backup all our database. It works fine and creates .bak files in default folder. I have scheduled the task to run every 4 hours.
I try adding cleanup history step and see if i can get it working.
I have created a job to backup all our database. It works fine and creates .bak files in default folder. I have scheduled the task to run every 4 hours.
My question is how can i modify or program this backup plan to keep the backup files for only last 3 days and delete older backup files?
Mits
That's simple. I assume you are using SQL Server 2000.
If so, in Enterprise Manager, Expand Management node, select Database Maintenance plans.
Right click the maintenance plan and select properties.
Go to the complete backup tab.
Change the "Remove files older than" option to read 3 days and click ok.
Hope that Helps.
|||A few questions for you to think about if you haven't already:
Are the backups stored on a different physical drive than the databases? If not then they absolutely should be.|||Yes, it's simple in SQL Server 2000. It's not simple at all in SQL Server 2005. We have just recently set up our first few SQL 2005 systems, and I cannot find any way to do this (remove older backup files) using the Database Maintenance Wizard, or Database Maintenance plans in general. There is a database maintenance task called "Cleanup History," but when looking deeper into this task all it does is called a system stored procedure msdb.dbo.sp_delete_backuphistory. When looking at the code for this stored procedure, it does indeed clean out the history in the msdb tables, but it doesn't reach out to the filesystem where the backup files are created and remove the older ones.
This functionality, which worked so wonderfully in SQL Server 2000 and was so central to plans involving backups staged to disk before moving them to tape, appears to have been removed in SQL Server 2005. I cannot understand why this would be so, and I hope that I am wrong and just missing something here.
I too would like to know if there is a mechanism for automatic removal of older backup files in SQL 2005.
|||Matt Fraser wrote:
Yes, it's simple in SQL Server 2000. It's not simple at all in SQL Server 2005. We have just recently set up our first few SQL 2005 systems, and I cannot find any way to do this (remove older backup files) using the Database Maintenance Wizard, or Database Maintenance plans in general. There is a database maintenance task called "Cleanup History," but when looking deeper into this task all it does is called a system stored procedure msdb.dbo.sp_delete_backuphistory. When looking at the code for this stored procedure, it does indeed clean out the history in the msdb tables, but it doesn't reach out to the filesystem where the backup files are created and remove the older ones.
This functionality, which worked so wonderfully in SQL Server 2000 and was so central to plans involving backups staged to disk before moving them to tape, appears to have been removed in SQL Server 2005. I cannot understand why this would be so, and I hope that I am wrong and just missing something here.
I too would like to know if there is a mechanism for automatic removal of older backup files in SQL 2005.
Matt,
I discovered this issue as well with SQL 2005, and I am perplexed as to why we have a way to back up databases but no way to manage the retention of old backups which can cause our disks to fill up. As a workaround I found this script on the web and use it to delete backup files older than "x" days, and it has worked flawlessly from the first time I started using it.
There are a few different ways to implement this workaround, but I have found the easiest way to is to copy the code below into a Notepad and save it as a .vbs file in the root of your backup drive, you might name the file something like DeleteOldBackups.vbs. This way you would simply create a Scheduled Task that points to this file and set the task to run either before or after all of your backups complete.
Make sure you set the directory paths and number of days to meet your needs.
Option Explicit
on error resume next
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim iDaysOld
'Delete database backup files older than 5 days
iDaysOld = 4
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = ".\databackups"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
'If database backup files are older than 5 days, delete them.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next
'Delete database log file backups older than 5 days
iDaysOld = 4
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = ".\logfilebackups"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
'If database log file backups are older than 5 days, delete them.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next
'Clean up
Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing
This has worked great for me, hopefully it will be useful to you and others as well.
|||Thank you everyone for you responses and i like the idea of script and i honestly agree with Matt Fraser.
I am using SQL Server 2005 Enterprise Edition.
I have had it working on SQL Server 2000 using a batch file using following command and this batch file was scheduled to run on sepecific time of the day. This will backup DB and Log files.
SQLMAINT -D DBName -BkUpDB E:\SQLBKUP -BkUpMedia DISK -DelBkUps 3days -VrfyBackup
The backup is taken on a shared Drive on the DB Server which will be backed up in the overall daily backup plan on tape. So everyday on tape, we will have the backup of last 3 days and we dont have to use hard disk space.Regarding disaster recovery - we literally cant have any downtime from monday to saturday, but, couple of hours on Sunday could be arranged. Backup time is about 10-15 mins.
Yes I regularly restore the backups to check the validity.
I am quiet sure that this batch file will work for SQL Server 2005, but i not particularly keen on using batch file(and if i have to, i will use it). I want to do it through Maintenance Plan.
Mits
|||
I also use script to achieve this at my customers (WMI/VB Script). It's much more flexible and you can code it to do what you want. I've done a few custom versions, if, for example, they want to keep N number of fulls, and only a certain number of days worth of transaction logs, etc.
It works very well, but as you hint at Mits, it is outside the control of SQL Server.
|||I did some digging and I found out that there is in fact a way to delete old backup files within the SQL management studio. I was told this functionality was going to be added with SP1 and apparently it was, just not where I expected to find it. I haven't had a chance to test it very much so I'm going to continue running my VB Script, but it is there.
Basically there are two ways to create backup jobs for your databases from within the SQL management studio Maintenence Plan area, manually or by using the wizard. If you choose to create your backup plans manually you will see the functionality to delete old backups right away, but here is how I found it because I always like to use the wizard instead.
In the SQL management studio under maintenence plans, right click maintenence plans and select the wizard. Follow the wizard all the way through to create your backup plan, you will not see any option to delete backup files older than "x" days, at the end save your backup plan.
Once your backup plan is visible under maintenence plans, right click it, and click on modify, this will open your backup plan in what appears to be a development environment window and you will see several boxes connected to each other with either green or blue arrows. Look down in the lower left hand corner and you will see a toolbox with about eight different components in it.
Select the component called Maintenence Cleanup History and drag it onto the page with your other boxes. Right click this new box and click edit, when it opens up you will see options to delete backup files older than x days. You can configure it, and then you will need to connect it to the other boxes and save the plan and then run it to test it. I tried it a couple of times and couldn't get it to work so I will have to keep testing it.
The ability to delete old backup files does exit, just not where I thought I would find it.
|||thank you very much andy
I try adding cleanup history step and see if i can get it working.
Mits
|||
Well done, Andy. It seems that SP1 did in fact add this functionality, but the Wizard hasn't been updated yet to include it. So, the key is to add the step for "Maintenance Cleanup Task," recognizing that it's different from "History Cleanup Task" (which is included in the Wizard).
I've added it to my maintenance plan, and in about a week's time I should know if it's working properly.
|||This appears to work, but I won't be able to tell until tonight since Microsoft has removed the ability to delete files older than x hours. Only days, weeks, months, and years. I always used hours because of variations in backup duration. If a backup had the time of 8:01 one night and 8:00 the next, I would end up with both on the drive if I picked the 1 day option. I always used 23 hours to avoid the drive filling up. It appears that option is gone. Grrrrr! It is annoying to have a feature that you use and rely on only to have it removed.
Microsoft, please add back the hours option in a service pack.
|||
I think the frustration expressed in this thread is entirely justified and I'll ensure that the tools team here sees this.
As added weight, please go to connect.microsoft.com and give this feedback - this will open issues directly in our bug database and you will get feedback from the relevant teams.
Thanks
|||Thanks for the suggestion! I didn't know about that site. I see a closed item where the response was that "hours" would come back as a duration in SP2. I voted for that and the open item about the same issue.
I also authored an item to make the cleanup task available through the wizard as it was in SQL2000.
Thanks for taking time to listen to our concerns and point me to the right forum for airing them. Your responsiveness does your employer proud.
I want to run the backup plan ever 285 mins or 4.75 hours starting at 8.15 in the morning and finishing at 9.15 at night.
I have tried but it wont let me put anything more then 60 mins or 4.75 hours.
Mits
|||
In sql2005 the retention period comes as common sql server setting.when check properties of sql server in database setting you have option to set retention period
Back-up DB but keep the files for 3 days?
Hi
I have created a job to backup all our database. It works fine and creates .bak files in default folder. I have scheduled the task to run every 4 hours.
I try adding cleanup history step and see if i can get it working.
I have created a job to backup all our database. It works fine and creates .bak files in default folder. I have scheduled the task to run every 4 hours.
My question is how can i modify or program this backup plan to keep the backup files for only last 3 days and delete older backup files?
Mits
That's simple. I assume you are using SQL Server 2000.
If so, in Enterprise Manager, Expand Management node, select Database Maintenance plans.
Right click the maintenance plan and select properties.
Go to the complete backup tab.
Change the "Remove files older than" option to read 3 days and click ok.
Hope that Helps.
|||A few questions for you to think about if you haven't already:
Are the backups stored on a different physical drive than the databases? If not then they absolutely should be.|||Yes, it's simple in SQL Server 2000. It's not simple at all in SQL Server 2005. We have just recently set up our first few SQL 2005 systems, and I cannot find any way to do this (remove older backup files) using the Database Maintenance Wizard, or Database Maintenance plans in general. There is a database maintenance task called "Cleanup History," but when looking deeper into this task all it does is called a system stored procedure msdb.dbo.sp_delete_backuphistory. When looking at the code for this stored procedure, it does indeed clean out the history in the msdb tables, but it doesn't reach out to the filesystem where the backup files are created and remove the older ones.
This functionality, which worked so wonderfully in SQL Server 2000 and was so central to plans involving backups staged to disk before moving them to tape, appears to have been removed in SQL Server 2005. I cannot understand why this would be so, and I hope that I am wrong and just missing something here.
I too would like to know if there is a mechanism for automatic removal of older backup files in SQL 2005.
|||Yes, it's simple in SQL Server 2000. It's not simple at all in SQL Server 2005. We have just recently set up our first few SQL 2005 systems, and I cannot find any way to do this (remove older backup files) using the Database Maintenance Wizard, or Database Maintenance plans in general. There is a database maintenance task called "Cleanup History," but when looking deeper into this task all it does is called a system stored procedure msdb.dbo.sp_delete_backuphistory. When looking at the code for this stored procedure, it does indeed clean out the history in the msdb tables, but it doesn't reach out to the filesystem where the backup files are created and remove the older ones.
This functionality, which worked so wonderfully in SQL Server 2000 and was so central to plans involving backups staged to disk before moving them to tape, appears to have been removed in SQL Server 2005. I cannot understand why this would be so, and I hope that I am wrong and just missing something here.
I too would like to know if there is a mechanism for automatic removal of older backup files in SQL 2005.
Matt,
I discovered this issue as well with SQL 2005, and I am perplexed as to why we have a way to back up databases but no way to manage the retention of old backups which can cause our disks to fill up. As a workaround I found this script on the web and use it to delete backup files older than "x" days, and it has worked flawlessly from the first time I started using it.
There are a few different ways to implement this workaround, but I have found the easiest way to is to copy the code below into a Notepad and save it as a .vbs file in the root of your backup drive, you might name the file something like DeleteOldBackups.vbs. This way you would simply create a Scheduled Task that points to this file and set the task to run either before or after all of your backups complete.
Make sure you set the directory paths and number of days to meet your needs.
Option Explicit
on error resume next
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim iDaysOld
'Delete database backup files older than 5 days
iDaysOld = 4
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = ".\databackups"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
'If database backup files are older than 5 days, delete them.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next
'Delete database log file backups older than 5 days
iDaysOld = 4
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = ".\logfilebackups"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
'If database log file backups are older than 5 days, delete them.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next
'Clean up
Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing
This has worked great for me, hopefully it will be useful to you and others as well.
|||Thank you everyone for you responses and i like the idea of script and i honestly agree with Matt Fraser.
I am using SQL Server 2005 Enterprise Edition.
I have had it working on SQL Server 2000 using a batch file using following command and this batch file was scheduled to run on sepecific time of the day. This will backup DB and Log files.
SQLMAINT -D DBName -BkUpDB E:\SQLBKUP -BkUpMedia DISK -DelBkUps 3days -VrfyBackup
The backup is taken on a shared Drive on the DB Server which will be backed up in the overall daily backup plan on tape. So everyday on tape, we will have the backup of last 3 days and we dont have to use hard disk space.Regarding disaster recovery - we literally cant have any downtime from monday to saturday, but, couple of hours on Sunday could be arranged. Backup time is about 10-15 mins.
Yes I regularly restore the backups to check the validity.
I am quiet sure that this batch file will work for SQL Server 2005, but i not particularly keen on using batch file(and if i have to, i will use it). I want to do it through Maintenance Plan.
Mits
|||
I also use script to achieve this at my customers (WMI/VB Script). It's much more flexible and you can code it to do what you want. I've done a few custom versions, if, for example, they want to keep N number of fulls, and only a certain number of days worth of transaction logs, etc.
It works very well, but as you hint at Mits, it is outside the control of SQL Server.
|||I did some digging and I found out that there is in fact a way to delete old backup files within the SQL management studio. I was told this functionality was going to be added with SP1 and apparently it was, just not where I expected to find it. I haven't had a chance to test it very much so I'm going to continue running my VB Script, but it is there.
Basically there are two ways to create backup jobs for your databases from within the SQL management studio Maintenence Plan area, manually or by using the wizard. If you choose to create your backup plans manually you will see the functionality to delete old backups right away, but here is how I found it because I always like to use the wizard instead.
In the SQL management studio under maintenence plans, right click maintenence plans and select the wizard. Follow the wizard all the way through to create your backup plan, you will not see any option to delete backup files older than "x" days, at the end save your backup plan.
Once your backup plan is visible under maintenence plans, right click it, and click on modify, this will open your backup plan in what appears to be a development environment window and you will see several boxes connected to each other with either green or blue arrows. Look down in the lower left hand corner and you will see a toolbox with about eight different components in it.
Select the component called Maintenence Cleanup History and drag it onto the page with your other boxes. Right click this new box and click edit, when it opens up you will see options to delete backup files older than x days. You can configure it, and then you will need to connect it to the other boxes and save the plan and then run it to test it. I tried it a couple of times and couldn't get it to work so I will have to keep testing it.
The ability to delete old backup files does exit, just not where I thought I would find it.
|||thank you very much andy
I try adding cleanup history step and see if i can get it working.
Mits
|||
Well done, Andy. It seems that SP1 did in fact add this functionality, but the Wizard hasn't been updated yet to include it. So, the key is to add the step for "Maintenance Cleanup Task," recognizing that it's different from "History Cleanup Task" (which is included in the Wizard).
I've added it to my maintenance plan, and in about a week's time I should know if it's working properly.
|||This appears to work, but I won't be able to tell until tonight since Microsoft has removed the ability to delete files older than x hours. Only days, weeks, months, and years. I always used hours because of variations in backup duration. If a backup had the time of 8:01 one night and 8:00 the next, I would end up with both on the drive if I picked the 1 day option. I always used 23 hours to avoid the drive filling up. It appears that option is gone. Grrrrr! It is annoying to have a feature that you use and rely on only to have it removed.
Microsoft, please add back the hours option in a service pack.
|||
I think the frustration expressed in this thread is entirely justified and I'll ensure that the tools team here sees this.
As added weight, please go to connect.microsoft.com and give this feedback - this will open issues directly in our bug database and you will get feedback from the relevant teams.
Thanks
|||Thanks for the suggestion! I didn't know about that site. I see a closed item where the response was that "hours" would come back as a duration in SP2. I voted for that and the open item about the same issue.
I also authored an item to make the cleanup task available through the wizard as it was in SQL2000.
Thanks for taking time to listen to our concerns and point me to the right forum for airing them. Your responsiveness does your employer proud.
I want to run the backup plan ever 285 mins or 4.75 hours starting at 8.15 in the morning and finishing at 9.15 at night.
I have tried but it wont let me put anything more then 60 mins or 4.75 hours.
Mits
|||
In sql2005 the retention period comes as common sql server setting.when check properties of sql server in database setting you have option to set retention period
sqlBack-up DB but keep the files for 3 days?
Hi
I
I try adding cleanup history step and see if i can get it working.
I
have created a job to backup all our database. It works fine and
creates .bak files in default folder. I have scheduled the task to run
every 4 hours.
My question is how can i modify or program this
backup plan to keep the backup files for only last 3 days and delete
older backup files?
Mits
That's simple. I assume you are using SQL Server 2000.
If so, in Enterprise Manager, Expand Management node, select Database Maintenance plans.
Right click the maintenance plan and select properties.
Go to the complete backup tab.
Change the "Remove files older than" option to read 3 days and click ok.
Hope that Helps.
|||A few questions for you to think about if you haven't already:
Are the backups stored on a different physical drive than the databases? If not then they absolutely should be.|||
Yes, it's simple in SQL Server 2000. It's not simple at all in SQL Server 2005. We have just recently set up our first few SQL 2005 systems, and I cannot find any way to do this (remove older backup files) using the Database Maintenance Wizard, or Database Maintenance plans in general. There is a database maintenance task called "Cleanup History," but when looking deeper into this task all it does is called a system stored procedure msdb.dbo.sp_delete_backuphistory. When looking at the code for this stored procedure, it does indeed clean out the history in the msdb tables, but it doesn't reach out to the filesystem where the backup files are created and remove the older ones.
This functionality, which worked so wonderfully in SQL Server 2000 and was so central to plans involving backups staged to disk before moving them to tape, appears to have been removed in SQL Server 2005. I cannot understand why this would be so, and I hope that I am wrong and just missing something here.
I too would like to know if there is a mechanism for automatic removal of older backup files in SQL 2005.
|||Matt Fraser wrote:
Yes, it's simple in SQL Server 2000. It's not simple at all in SQL Server 2005. We have just recently set up our first few SQL 2005 systems, and I cannot find any way to do this (remove older backup files) using the Database Maintenance Wizard, or Database Maintenance plans in general. There is a database maintenance task called "Cleanup History," but when looking deeper into this task all it does is called a system stored procedure msdb.dbo.sp_delete_backuphistory. When looking at the code for this stored procedure, it does indeed clean out the history in the msdb tables, but it doesn't reach out to the filesystem where the backup files are created and remove the older ones.
This functionality, which worked so wonderfully in SQL Server 2000 and was so central to plans involving backups staged to disk before moving them to tape, appears to have been removed in SQL Server 2005. I cannot understand why this would be so, and I hope that I am wrong and just missing something here.
I too would like to know if there is a mechanism for automatic removal of older backup files in SQL 2005.
Matt,
I discovered this issue as well with SQL 2005, and I am perplexed as to why we have a way to back up databases but no way to manage the retention of old backups which can cause our disks to fill up. As a workaround I found this script on the web and use it to delete backup files older than "x" days, and it has worked flawlessly from the first time I started using it.
There are a few different ways to implement this workaround, but I have found the easiest way to is to copy the code below into a Notepad and save it as a .vbs file in the root of your backup drive, you might name the file something like DeleteOldBackups.vbs. This way you would simply create a Scheduled Task that points to this file and set the task to run either before or after all of your backups complete.
Make sure you set the directory paths and number of days to meet your needs.
Option Explicit
on error resume next
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim iDaysOld
'Delete database backup files older than 5 days
iDaysOld = 4
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = ".\databackups"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
'If database backup files are older than 5 days, delete them.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next
'Delete database log file backups older than 5 days
iDaysOld = 4
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = ".\logfilebackups"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
'If database log file backups are older than 5 days, delete them.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next
'Clean up
Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing
This has worked great for me, hopefully it will be useful to you and others as well.
|||Thank you everyone for you responses and i like the idea of script and i honestly agree with Matt Fraser.
I am using SQL Server 2005 Enterprise Edition.
I have had it working on SQL Server 2000 using a batch file using following command and this batch file was scheduled to run on sepecific time of the day. This will backup DB and Log files.
SQLMAINT -D DBName -BkUpDB E:\SQLBKUP -BkUpMedia DISK -DelBkUps
3days -VrfyBackup
The backup is taken on a shared Drive on the DB Server which will be backed up in the overall daily backup plan on tape. So everyday on tape, we will have the backup of last 3 days and we dont have to use hard disk space.
Regarding disaster recovery - we literally cant have any downtime from monday to saturday, but, couple of hours on Sunday could be arranged. Backup time is about 10-15 mins.
Yes I regularly restore the backups to check the validity.
I am quiet sure that this batch file will work for SQL Server 2005, but i not particularly keen on using batch file(and if i have to, i will use it). I want to do it through Maintenance Plan.
Mits|||
I also use script to achieve this at my customers (WMI/VB Script). It's much more flexible and you can code it to do what you want. I've done a few custom versions, if, for example, they want to keep N number of fulls, and only a certain number of days worth of transaction logs, etc.
It works very well, but as you hint at Mits, it is outside the control of SQL Server.
|||I did some digging and I found out that there is in fact a way to delete old backup files within the SQL management studio. I was told this functionality was going to be added with SP1 and apparently it was, just not where I expected to find it. I haven't had a chance to test it very much so I'm going to continue running my VB Script, but it is there.
Basically there are two ways to create backup jobs for your databases from within the SQL management studio Maintenence Plan area, manually or by using the wizard. If you choose to create your backup plans manually you will see the functionality to delete old backups right away, but here is how I found it because I always like to use the wizard instead.
In the SQL management studio under maintenence plans, right click maintenence plans and select the wizard. Follow the wizard all the way through to create your backup plan, you will not see any option to delete backup files older than "x" days, at the end save your backup plan.
Once your backup plan is visible under maintenence plans, right click it, and click on modify, this will open your backup plan in what appears to be a development environment window and you will see several boxes connected to each other with either green or blue arrows. Look down in the lower left hand corner and you will see a toolbox with about eight different components in it.
Select the component called Maintenence Cleanup History and drag it onto the page with your other boxes. Right click this new box and click edit, when it opens up you will see options to delete backup files older than x days. You can configure it, and then you will need to connect it to the other boxes and save the plan and then run it to test it. I tried it a couple of times and couldn't get it to work so I will have to keep testing it.
The ability to delete old backup files does exit, just not where I thought I would find it.
|||thank you very much andy
I try adding cleanup history step and see if i can get it working.
Mits|||
Well done, Andy. It seems that SP1 did in fact add this functionality, but the Wizard hasn't been updated yet to include it. So, the key is to add the step for "Maintenance Cleanup Task," recognizing that it's different from "History Cleanup Task" (which is included in the Wizard).
I've added it to my maintenance plan, and in about a week's time I should know if it's working properly.
|||This appears to work, but I won't be able to tell until tonight since Microsoft has removed the ability to delete files older than x hours. Only days, weeks, months, and years. I always used hours because of variations in backup duration. If a backup had the time of 8:01 one night and 8:00 the next, I would end up with both on the drive if I picked the 1 day option. I always used 23 hours to avoid the drive filling up. It appears that option is gone. Grrrrr! It is annoying to have a feature that you use and rely on only to have it removed.
Microsoft, please add back the hours option in a service pack.
|||
I think the frustration expressed in this thread is entirely justified and I'll ensure that the tools team here sees this.
As added weight, please go to connect.microsoft.com and give this feedback - this will open issues directly in our bug database and you will get feedback from the relevant teams.
Thanks
|||Thanks for the suggestion! I didn't know about that site. I see a closed item where the response was that "hours" would come back as a duration in SP2. I voted for that and the open item about the same issue.
I also authored an item to make the cleanup task available through the wizard as it was in SQL2000.
Thanks for taking time to listen to our concerns and point me to the right forum for airing them. Your responsiveness does your employer proud.
I want to run the backup plan ever 285 mins or 4.75 hours starting at 8.15 in the morning and finishing at 9.15 at night.
I have tried but it wont let me put anything more then 60 mins or 4.75 hours.
Mits|||
In sql2005 the retention period comes as common sql server setting.when check properties of sql server in database setting you have option to set retention period
Back-up DB but keep the files for 3 days?
Hi
I
I try adding cleanup history step and see if i can get it working.
I
have created a job to backup all our database. It works fine and
creates .bak files in default folder. I have scheduled the task to run
every 4 hours.
My question is how can i modify or program this
backup plan to keep the backup files for only last 3 days and delete
older backup files?
Mits
That's simple. I assume you are using SQL Server 2000.
If so, in Enterprise Manager, Expand Management node, select Database Maintenance plans.
Right click the maintenance plan and select properties.
Go to the complete backup tab.
Change the "Remove files older than" option to read 3 days and click ok.
Hope that Helps.
|||A few questions for you to think about if you haven't already:
Are the backups stored on a different physical drive than the databases? If not then they absolutely should be.|||
Yes, it's simple in SQL Server 2000. It's not simple at all in SQL Server 2005. We have just recently set up our first few SQL 2005 systems, and I cannot find any way to do this (remove older backup files) using the Database Maintenance Wizard, or Database Maintenance plans in general. There is a database maintenance task called "Cleanup History," but when looking deeper into this task all it does is called a system stored procedure msdb.dbo.sp_delete_backuphistory. When looking at the code for this stored procedure, it does indeed clean out the history in the msdb tables, but it doesn't reach out to the filesystem where the backup files are created and remove the older ones.
This functionality, which worked so wonderfully in SQL Server 2000 and was so central to plans involving backups staged to disk before moving them to tape, appears to have been removed in SQL Server 2005. I cannot understand why this would be so, and I hope that I am wrong and just missing something here.
I too would like to know if there is a mechanism for automatic removal of older backup files in SQL 2005.
|||Yes, it's simple in SQL Server 2000. It's not simple at all in SQL Server 2005. We have just recently set up our first few SQL 2005 systems, and I cannot find any way to do this (remove older backup files) using the Database Maintenance Wizard, or Database Maintenance plans in general. There is a database maintenance task called "Cleanup History," but when looking deeper into this task all it does is called a system stored procedure msdb.dbo.sp_delete_backuphistory. When looking at the code for this stored procedure, it does indeed clean out the history in the msdb tables, but it doesn't reach out to the filesystem where the backup files are created and remove the older ones.
This functionality, which worked so wonderfully in SQL Server 2000 and was so central to plans involving backups staged to disk before moving them to tape, appears to have been removed in SQL Server 2005. I cannot understand why this would be so, and I hope that I am wrong and just missing something here.
I too would like to know if there is a mechanism for automatic removal of older backup files in SQL 2005.
Matt,
I discovered this issue as well with SQL 2005, and I am perplexed as to why we have a way to back up databases but no way to manage the retention of old backups which can cause our disks to fill up. As a workaround I found this script on the web and use it to delete backup files older than "x" days, and it has worked flawlessly from the first time I started using it.
There are a few different ways to implement this workaround, but I have found the easiest way to is to copy the code below into a Notepad and save it as a .vbs file in the root of your backup drive, you might name the file something like DeleteOldBackups.vbs. This way you would simply create a Scheduled Task that points to this file and set the task to run either before or after all of your backups complete.
Make sure you set the directory paths and number of days to meet your needs.
Option Explicit
on error resume next
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim iDaysOld
'Delete database backup files older than 5 days
iDaysOld = 4
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = ".\databackups"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
'If database backup files are older than 5 days, delete them.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next
'Delete database log file backups older than 5 days
iDaysOld = 4
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = ".\logfilebackups"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
'If database log file backups are older than 5 days, delete them.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next
'Clean up
Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing
This has worked great for me, hopefully it will be useful to you and others as well.
|||Thank you everyone for you responses and i like the idea of script and i honestly agree with Matt Fraser.
I am using SQL Server 2005 Enterprise Edition.
I have had it working on SQL Server 2000 using a batch file using following command and this batch file was scheduled to run on sepecific time of the day. This will backup DB and Log files.
SQLMAINT -D DBName -BkUpDB E:\SQLBKUP -BkUpMedia DISK -DelBkUps
3days -VrfyBackup
The backup is taken on a shared Drive on the DB Server which will be backed up in the overall daily backup plan on tape. So everyday on tape, we will have the backup of last 3 days and we dont have to use hard disk space.
Regarding disaster recovery - we literally cant have any downtime from monday to saturday, but, couple of hours on Sunday could be arranged. Backup time is about 10-15 mins.
Yes I regularly restore the backups to check the validity.
I am quiet sure that this batch file will work for SQL Server 2005, but i not particularly keen on using batch file(and if i have to, i will use it). I want to do it through Maintenance Plan.
Mits|||
I also use script to achieve this at my customers (WMI/VB Script). It's much more flexible and you can code it to do what you want. I've done a few custom versions, if, for example, they want to keep N number of fulls, and only a certain number of days worth of transaction logs, etc.
It works very well, but as you hint at Mits, it is outside the control of SQL Server.
|||I did some digging and I found out that there is in fact a way to delete old backup files within the SQL management studio. I was told this functionality was going to be added with SP1 and apparently it was, just not where I expected to find it. I haven't had a chance to test it very much so I'm going to continue running my VB Script, but it is there.
Basically there are two ways to create backup jobs for your databases from within the SQL management studio Maintenence Plan area, manually or by using the wizard. If you choose to create your backup plans manually you will see the functionality to delete old backups right away, but here is how I found it because I always like to use the wizard instead.
In the SQL management studio under maintenence plans, right click maintenence plans and select the wizard. Follow the wizard all the way through to create your backup plan, you will not see any option to delete backup files older than "x" days, at the end save your backup plan.
Once your backup plan is visible under maintenence plans, right click it, and click on modify, this will open your backup plan in what appears to be a development environment window and you will see several boxes connected to each other with either green or blue arrows. Look down in the lower left hand corner and you will see a toolbox with about eight different components in it.
Select the component called Maintenence Cleanup History and drag it onto the page with your other boxes. Right click this new box and click edit, when it opens up you will see options to delete backup files older than x days. You can configure it, and then you will need to connect it to the other boxes and save the plan and then run it to test it. I tried it a couple of times and couldn't get it to work so I will have to keep testing it.
The ability to delete old backup files does exit, just not where I thought I would find it.
|||thank you very much andy
I try adding cleanup history step and see if i can get it working.
Mits|||
Well done, Andy. It seems that SP1 did in fact add this functionality, but the Wizard hasn't been updated yet to include it. So, the key is to add the step for "Maintenance Cleanup Task," recognizing that it's different from "History Cleanup Task" (which is included in the Wizard).
I've added it to my maintenance plan, and in about a week's time I should know if it's working properly.
|||This appears to work, but I won't be able to tell until tonight since Microsoft has removed the ability to delete files older than x hours. Only days, weeks, months, and years. I always used hours because of variations in backup duration. If a backup had the time of 8:01 one night and 8:00 the next, I would end up with both on the drive if I picked the 1 day option. I always used 23 hours to avoid the drive filling up. It appears that option is gone. Grrrrr! It is annoying to have a feature that you use and rely on only to have it removed.
Microsoft, please add back the hours option in a service pack.
|||
I think the frustration expressed in this thread is entirely justified and I'll ensure that the tools team here sees this.
As added weight, please go to connect.microsoft.com and give this feedback - this will open issues directly in our bug database and you will get feedback from the relevant teams.
Thanks
|||Thanks for the suggestion! I didn't know about that site. I see a closed item where the response was that "hours" would come back as a duration in SP2. I voted for that and the open item about the same issue.
I also authored an item to make the cleanup task available through the wizard as it was in SQL2000.
Thanks for taking time to listen to our concerns and point me to the right forum for airing them. Your responsiveness does your employer proud.
I want to run the backup plan ever 285 mins or 4.75 hours starting at 8.15 in the morning and finishing at 9.15 at night.
I have tried but it wont let me put anything more then 60 mins or 4.75 hours.
Mits|||
In sql2005 the retention period comes as common sql server setting.when check properties of sql server in database setting you have option to set retention period
Sunday, March 11, 2012
backup database database remotely
i have a stored procedure that backups database on server A which i execute this SP from server B in a scheduled job. when i run this job it gives me an error:
Executed as user: domain\user_name. Could not relay results of procedure 'usp_BackupDBOnServerA' from remote server 'ServerB'. [SQLSTATE 42000] (Error 7221) [SQLSTATE 01000] (Error 7312). The step failed.
i searched MS KB, everywhere but could not find the solution or even explanation why this happenning.
both sql servers are 2000 and they are linked.It seems like an account permissions issue. I would look over the SP and make sure that the account which SQLServer service, SQLAgent service, and the scheduled job has access to the SP as well as the network share which the backup is being written too.
Does the whole backup fail or does it just not return what happened (the result or output)?|||actually there's no issue with account's permissions. and to asnwer your question the job itself fails.
but i just figured out how to solve the issue: i increased the Remote query timeout in the Server properties.
thanks for the help anyway.|||Great! Thanks for posting the answer, it helps with other people who might run into the same problem.
Executed as user: domain\user_name. Could not relay results of procedure 'usp_BackupDBOnServerA' from remote server 'ServerB'. [SQLSTATE 42000] (Error 7221) [SQLSTATE 01000] (Error 7312). The step failed.
i searched MS KB, everywhere but could not find the solution or even explanation why this happenning.
both sql servers are 2000 and they are linked.It seems like an account permissions issue. I would look over the SP and make sure that the account which SQLServer service, SQLAgent service, and the scheduled job has access to the SP as well as the network share which the backup is being written too.
Does the whole backup fail or does it just not return what happened (the result or output)?|||actually there's no issue with account's permissions. and to asnwer your question the job itself fails.
but i just figured out how to solve the issue: i increased the Remote query timeout in the Server properties.
thanks for the help anyway.|||Great! Thanks for posting the answer, it helps with other people who might run into the same problem.
Saturday, February 25, 2012
Backup and Restore Anomaly
SQL Server 2000 SP4 build 2187
Not sure what is happening with SQL Server backup Internals, I am hoping you
can answer.
A scheduled job backups up database named "Wells" to a device which points
to a physical file as step 1. Same schedule job, step 2, then restores to
database WELLSCopy. The WELLSCopy database has always existed. This has been
working for a very long time. Now, its generating the following error.
Job '0405 Backup and WELLSCopy Restore' : Step 3, 'Restore Wells Copy
Database' :
Began Executing 2006-10-19 00:59:34
Msg 3141, Sev 16: The database to be restored was named 'Wells'.
Reissue the statement using the WITH REPLACE option to overwrite the
'WELLScopy'
database. [SQLSTATE 42000]
Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE
42000]
Here is the backup command and restore command.
BACKUP DATABASE [Wells] TO Dbackup_WELLS
WITH INIT, NOUNLOAD, NAME = N'Wells', SKIP, STATS=10, NOFORMAT
restore database CHARITYDBCOPY
from dbackup_charitydb
with
move 'CHARITYDB_Data' to 'o:\Data\CHARITYDBCOPY_Data.MDF',
move 'CHARITYDB_Log' to 'o:\Data\CHARITYDBCOPY_Log.LDF'
I tried to duplicate this issue in our test environment and it restored
successfully every time. I did manage to break it by creating a new database
with a totally different name then try to restore over it and received the
error message.
BOL says it performs a safety check if the REPLACE options is not used. If
so, why has the restore work for a very long time and now decide to break.
Also, why is it allowing me to restore successfully in test without using th
e
REPLACE option.
I am so confused...
BOL...
When the REPLACE option is not specified, a safety check occurs (which
prevents overwriting a different database by accident). The safety check
ensures that the RESTORE DATABASE statement will not restore the database to
the current server if:
The database named in the RESTORE statement already exists on the current
server, and
The database name is different from the database name recorded in the backup
set.I applogize, I pasted my test restore code. Here is the correct version
RESTORE DATABASE WELLScopy
FROM Dbackup_wells
with
MOVE 'Wells_Data' TO 'e:\data\mssql\data\WELLScopy_Data.MDF',
MOVE 'WELLS_Log' TO 'e:\data\mssql\data\WELLScopy_Log.LDF'
go
"FredG" wrote:
> SQL Server 2000 SP4 build 2187
> Not sure what is happening with SQL Server backup Internals, I am hoping y
ou
> can answer.
> A scheduled job backups up database named "Wells" to a device which points
> to a physical file as step 1. Same schedule job, step 2, then restores to
> database WELLSCopy. The WELLSCopy database has always existed. This has be
en
> working for a very long time. Now, its generating the following error.
> Job '0405 Backup and WELLSCopy Restore' : Step 3, 'Restore Wells Copy
> Database' :
> Began Executing 2006-10-19 00:59:34
> Msg 3141, Sev 16: The database to be restored was named 'Wells'.
> Reissue the statement using the WITH REPLACE option to overwrite the
> 'WELLScopy'
> database. [SQLSTATE 42000]
> Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTAT
E 42000]
>
> Here is the backup command and restore command.
> BACKUP DATABASE [Wells] TO Dbackup_WELLS
> WITH INIT, NOUNLOAD, NAME = N'Wells', SKIP, STATS=10, NOFORMAT
>
> restore database CHARITYDBCOPY
> from dbackup_charitydb
> with
> move 'CHARITYDB_Data' to 'o:\Data\CHARITYDBCOPY_Data.MDF',
> move 'CHARITYDB_Log' to 'o:\Data\CHARITYDBCOPY_Log.LDF'
>
> I tried to duplicate this issue in our test environment and it restored
> successfully every time. I did manage to break it by creating a new databa
se
> with a totally different name then try to restore over it and received the
> error message.
> BOL says it performs a safety check if the REPLACE options is not used. If
> so, why has the restore work for a very long time and now decide to break.
> Also, why is it allowing me to restore successfully in test without using
the
> REPLACE option.
> I am so confused...
> BOL...
> When the REPLACE option is not specified, a safety check occurs (which
> prevents overwriting a different database by accident). The safety check
> ensures that the RESTORE DATABASE statement will not restore the database
to
> the current server if:
> The database named in the RESTORE statement already exists on the current
> server, and
> The database name is different from the database name recorded in the back
up
> set.
>|||A guess is that the source database has grown, so the backup need say 10GB f
or one of the database
files, but the existing database file only has, say, 8GB for that file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"FredG" <FredG@.discussions.microsoft.com> wrote in message
news:1D2AF5E1-9337-4F55-84EE-B4711171D7C9@.microsoft.com...
> SQL Server 2000 SP4 build 2187
> Not sure what is happening with SQL Server backup Internals, I am hoping y
ou
> can answer.
> A scheduled job backups up database named "Wells" to a device which points
> to a physical file as step 1. Same schedule job, step 2, then restores to
> database WELLSCopy. The WELLSCopy database has always existed. This has be
en
> working for a very long time. Now, its generating the following error.
> Job '0405 Backup and WELLSCopy Restore' : Step 3, 'Restore Wells Copy
> Database' :
> Began Executing 2006-10-19 00:59:34
> Msg 3141, Sev 16: The database to be restored was named 'Wells'.
> Reissue the statement using the WITH REPLACE option to overwrite the
> 'WELLScopy'
> database. [SQLSTATE 42000]
> Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTAT
E 42000]
>
> Here is the backup command and restore command.
> BACKUP DATABASE [Wells] TO Dbackup_WELLS
> WITH INIT, NOUNLOAD, NAME = N'Wells', SKIP, STATS=10, NOFORMAT
>
> restore database CHARITYDBCOPY
> from dbackup_charitydb
> with
> move 'CHARITYDB_Data' to 'o:\Data\CHARITYDBCOPY_Data.MDF',
> move 'CHARITYDB_Log' to 'o:\Data\CHARITYDBCOPY_Log.LDF'
>
> I tried to duplicate this issue in our test environment and it restored
> successfully every time. I did manage to break it by creating a new databa
se
> with a totally different name then try to restore over it and received the
> error message.
> BOL says it performs a safety check if the REPLACE options is not used. If
> so, why has the restore work for a very long time and now decide to break.
> Also, why is it allowing me to restore successfully in test without using
the
> REPLACE option.
> I am so confused...
> BOL...
> When the REPLACE option is not specified, a safety check occurs (which
> prevents overwriting a different database by accident). The safety check
> ensures that the RESTORE DATABASE statement will not restore the database
to
> the current server if:
> The database named in the RESTORE statement already exists on the current
> server, and
> The database name is different from the database name recorded in the back
up
> set.
>|||Hi Tibor,
Thanks for the repsonse. We finally found the actual problem. Someone
restored the copy data outside of the normal job. Once we dropped and
recreated the job functioned as normal. Very strange behavior I must add.
"Tibor Karaszi" wrote:
> A guess is that the source database has grown, so the backup need say 10GB
for one of the database
> files, but the existing database file only has, say, 8GB for that file.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "FredG" <FredG@.discussions.microsoft.com> wrote in message
> news:1D2AF5E1-9337-4F55-84EE-B4711171D7C9@.microsoft.com...
>
Not sure what is happening with SQL Server backup Internals, I am hoping you
can answer.
A scheduled job backups up database named "Wells" to a device which points
to a physical file as step 1. Same schedule job, step 2, then restores to
database WELLSCopy. The WELLSCopy database has always existed. This has been
working for a very long time. Now, its generating the following error.
Job '0405 Backup and WELLSCopy Restore' : Step 3, 'Restore Wells Copy
Database' :
Began Executing 2006-10-19 00:59:34
Msg 3141, Sev 16: The database to be restored was named 'Wells'.
Reissue the statement using the WITH REPLACE option to overwrite the
'WELLScopy'
database. [SQLSTATE 42000]
Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE
42000]
Here is the backup command and restore command.
BACKUP DATABASE [Wells] TO Dbackup_WELLS
WITH INIT, NOUNLOAD, NAME = N'Wells', SKIP, STATS=10, NOFORMAT
restore database CHARITYDBCOPY
from dbackup_charitydb
with
move 'CHARITYDB_Data' to 'o:\Data\CHARITYDBCOPY_Data.MDF',
move 'CHARITYDB_Log' to 'o:\Data\CHARITYDBCOPY_Log.LDF'
I tried to duplicate this issue in our test environment and it restored
successfully every time. I did manage to break it by creating a new database
with a totally different name then try to restore over it and received the
error message.
BOL says it performs a safety check if the REPLACE options is not used. If
so, why has the restore work for a very long time and now decide to break.
Also, why is it allowing me to restore successfully in test without using th
e
REPLACE option.
I am so confused...
BOL...
When the REPLACE option is not specified, a safety check occurs (which
prevents overwriting a different database by accident). The safety check
ensures that the RESTORE DATABASE statement will not restore the database to
the current server if:
The database named in the RESTORE statement already exists on the current
server, and
The database name is different from the database name recorded in the backup
set.I applogize, I pasted my test restore code. Here is the correct version
RESTORE DATABASE WELLScopy
FROM Dbackup_wells
with
MOVE 'Wells_Data' TO 'e:\data\mssql\data\WELLScopy_Data.MDF',
MOVE 'WELLS_Log' TO 'e:\data\mssql\data\WELLScopy_Log.LDF'
go
"FredG" wrote:
> SQL Server 2000 SP4 build 2187
> Not sure what is happening with SQL Server backup Internals, I am hoping y
ou
> can answer.
> A scheduled job backups up database named "Wells" to a device which points
> to a physical file as step 1. Same schedule job, step 2, then restores to
> database WELLSCopy. The WELLSCopy database has always existed. This has be
en
> working for a very long time. Now, its generating the following error.
> Job '0405 Backup and WELLSCopy Restore' : Step 3, 'Restore Wells Copy
> Database' :
> Began Executing 2006-10-19 00:59:34
> Msg 3141, Sev 16: The database to be restored was named 'Wells'.
> Reissue the statement using the WITH REPLACE option to overwrite the
> 'WELLScopy'
> database. [SQLSTATE 42000]
> Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTAT
E 42000]
>
> Here is the backup command and restore command.
> BACKUP DATABASE [Wells] TO Dbackup_WELLS
> WITH INIT, NOUNLOAD, NAME = N'Wells', SKIP, STATS=10, NOFORMAT
>
> restore database CHARITYDBCOPY
> from dbackup_charitydb
> with
> move 'CHARITYDB_Data' to 'o:\Data\CHARITYDBCOPY_Data.MDF',
> move 'CHARITYDB_Log' to 'o:\Data\CHARITYDBCOPY_Log.LDF'
>
> I tried to duplicate this issue in our test environment and it restored
> successfully every time. I did manage to break it by creating a new databa
se
> with a totally different name then try to restore over it and received the
> error message.
> BOL says it performs a safety check if the REPLACE options is not used. If
> so, why has the restore work for a very long time and now decide to break.
> Also, why is it allowing me to restore successfully in test without using
the
> REPLACE option.
> I am so confused...
> BOL...
> When the REPLACE option is not specified, a safety check occurs (which
> prevents overwriting a different database by accident). The safety check
> ensures that the RESTORE DATABASE statement will not restore the database
to
> the current server if:
> The database named in the RESTORE statement already exists on the current
> server, and
> The database name is different from the database name recorded in the back
up
> set.
>|||A guess is that the source database has grown, so the backup need say 10GB f
or one of the database
files, but the existing database file only has, say, 8GB for that file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"FredG" <FredG@.discussions.microsoft.com> wrote in message
news:1D2AF5E1-9337-4F55-84EE-B4711171D7C9@.microsoft.com...
> SQL Server 2000 SP4 build 2187
> Not sure what is happening with SQL Server backup Internals, I am hoping y
ou
> can answer.
> A scheduled job backups up database named "Wells" to a device which points
> to a physical file as step 1. Same schedule job, step 2, then restores to
> database WELLSCopy. The WELLSCopy database has always existed. This has be
en
> working for a very long time. Now, its generating the following error.
> Job '0405 Backup and WELLSCopy Restore' : Step 3, 'Restore Wells Copy
> Database' :
> Began Executing 2006-10-19 00:59:34
> Msg 3141, Sev 16: The database to be restored was named 'Wells'.
> Reissue the statement using the WITH REPLACE option to overwrite the
> 'WELLScopy'
> database. [SQLSTATE 42000]
> Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTAT
E 42000]
>
> Here is the backup command and restore command.
> BACKUP DATABASE [Wells] TO Dbackup_WELLS
> WITH INIT, NOUNLOAD, NAME = N'Wells', SKIP, STATS=10, NOFORMAT
>
> restore database CHARITYDBCOPY
> from dbackup_charitydb
> with
> move 'CHARITYDB_Data' to 'o:\Data\CHARITYDBCOPY_Data.MDF',
> move 'CHARITYDB_Log' to 'o:\Data\CHARITYDBCOPY_Log.LDF'
>
> I tried to duplicate this issue in our test environment and it restored
> successfully every time. I did manage to break it by creating a new databa
se
> with a totally different name then try to restore over it and received the
> error message.
> BOL says it performs a safety check if the REPLACE options is not used. If
> so, why has the restore work for a very long time and now decide to break.
> Also, why is it allowing me to restore successfully in test without using
the
> REPLACE option.
> I am so confused...
> BOL...
> When the REPLACE option is not specified, a safety check occurs (which
> prevents overwriting a different database by accident). The safety check
> ensures that the RESTORE DATABASE statement will not restore the database
to
> the current server if:
> The database named in the RESTORE statement already exists on the current
> server, and
> The database name is different from the database name recorded in the back
up
> set.
>|||Hi Tibor,
Thanks for the repsonse. We finally found the actual problem. Someone
restored the copy data outside of the normal job. Once we dropped and
recreated the job functioned as normal. Very strange behavior I must add.
"Tibor Karaszi" wrote:
> A guess is that the source database has grown, so the backup need say 10GB
for one of the database
> files, but the existing database file only has, say, 8GB for that file.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "FredG" <FredG@.discussions.microsoft.com> wrote in message
> news:1D2AF5E1-9337-4F55-84EE-B4711171D7C9@.microsoft.com...
>
Backup and Restore Anomaly
SQL Server 2000 SP4 build 2187
Not sure what is happening with SQL Server backup Internals, I am hoping you
can answer.
A scheduled job backups up database named "Wells" to a device which points
to a physical file as step 1. Same schedule job, step 2, then restores to
database WELLSCopy. The WELLSCopy database has always existed. This has been
working for a very long time. Now, its generating the following error.
Job '0405 Backup and WELLSCopy Restore' : Step 3, 'Restore Wells Copy
Database' :
Began Executing 2006-10-19 00:59:34
Msg 3141, Sev 16: The database to be restored was named 'Wells'.
Reissue the statement using the WITH REPLACE option to overwrite the
'WELLScopy'
database. [SQLSTATE 42000]
Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000]
Here is the backup command and restore command.
BACKUP DATABASE [Wells] TO Dbackup_WELLS
WITH INIT, NOUNLOAD, NAME = N'Wells', SKIP, STATS=10, NOFORMAT
restore database CHARITYDBCOPY
from dbackup_charitydb
with
move 'CHARITYDB_Data' to 'o:\Data\CHARITYDBCOPY_Data.MDF',
move 'CHARITYDB_Log' to 'o:\Data\CHARITYDBCOPY_Log.LDF'
I tried to duplicate this issue in our test environment and it restored
successfully every time. I did manage to break it by creating a new database
with a totally different name then try to restore over it and received the
error message.
BOL says it performs a safety check if the REPLACE options is not used. If
so, why has the restore work for a very long time and now decide to break.
Also, why is it allowing me to restore successfully in test without using the
REPLACE option.
I am so confused...
BOL...
When the REPLACE option is not specified, a safety check occurs (which
prevents overwriting a different database by accident). The safety check
ensures that the RESTORE DATABASE statement will not restore the database to
the current server if:
The database named in the RESTORE statement already exists on the current
server, and
The database name is different from the database name recorded in the backup
set.I applogize, I pasted my test restore code. Here is the correct version
RESTORE DATABASE WELLScopy
FROM Dbackup_wells
with
MOVE 'Wells_Data' TO 'e:\data\mssql\data\WELLScopy_Data.MDF',
MOVE 'WELLS_Log' TO 'e:\data\mssql\data\WELLScopy_Log.LDF'
go
"FredG" wrote:
> SQL Server 2000 SP4 build 2187
> Not sure what is happening with SQL Server backup Internals, I am hoping you
> can answer.
> A scheduled job backups up database named "Wells" to a device which points
> to a physical file as step 1. Same schedule job, step 2, then restores to
> database WELLSCopy. The WELLSCopy database has always existed. This has been
> working for a very long time. Now, its generating the following error.
> Job '0405 Backup and WELLSCopy Restore' : Step 3, 'Restore Wells Copy
> Database' :
> Began Executing 2006-10-19 00:59:34
> Msg 3141, Sev 16: The database to be restored was named 'Wells'.
> Reissue the statement using the WITH REPLACE option to overwrite the
> 'WELLScopy'
> database. [SQLSTATE 42000]
> Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000]
>
> Here is the backup command and restore command.
> BACKUP DATABASE [Wells] TO Dbackup_WELLS
> WITH INIT, NOUNLOAD, NAME = N'Wells', SKIP, STATS=10, NOFORMAT
>
> restore database CHARITYDBCOPY
> from dbackup_charitydb
> with
> move 'CHARITYDB_Data' to 'o:\Data\CHARITYDBCOPY_Data.MDF',
> move 'CHARITYDB_Log' to 'o:\Data\CHARITYDBCOPY_Log.LDF'
>
> I tried to duplicate this issue in our test environment and it restored
> successfully every time. I did manage to break it by creating a new database
> with a totally different name then try to restore over it and received the
> error message.
> BOL says it performs a safety check if the REPLACE options is not used. If
> so, why has the restore work for a very long time and now decide to break.
> Also, why is it allowing me to restore successfully in test without using the
> REPLACE option.
> I am so confused...
> BOL...
> When the REPLACE option is not specified, a safety check occurs (which
> prevents overwriting a different database by accident). The safety check
> ensures that the RESTORE DATABASE statement will not restore the database to
> the current server if:
> The database named in the RESTORE statement already exists on the current
> server, and
> The database name is different from the database name recorded in the backup
> set.
>|||A guess is that the source database has grown, so the backup need say 10GB for one of the database
files, but the existing database file only has, say, 8GB for that file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"FredG" <FredG@.discussions.microsoft.com> wrote in message
news:1D2AF5E1-9337-4F55-84EE-B4711171D7C9@.microsoft.com...
> SQL Server 2000 SP4 build 2187
> Not sure what is happening with SQL Server backup Internals, I am hoping you
> can answer.
> A scheduled job backups up database named "Wells" to a device which points
> to a physical file as step 1. Same schedule job, step 2, then restores to
> database WELLSCopy. The WELLSCopy database has always existed. This has been
> working for a very long time. Now, its generating the following error.
> Job '0405 Backup and WELLSCopy Restore' : Step 3, 'Restore Wells Copy
> Database' :
> Began Executing 2006-10-19 00:59:34
> Msg 3141, Sev 16: The database to be restored was named 'Wells'.
> Reissue the statement using the WITH REPLACE option to overwrite the
> 'WELLScopy'
> database. [SQLSTATE 42000]
> Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000]
>
> Here is the backup command and restore command.
> BACKUP DATABASE [Wells] TO Dbackup_WELLS
> WITH INIT, NOUNLOAD, NAME = N'Wells', SKIP, STATS=10, NOFORMAT
>
> restore database CHARITYDBCOPY
> from dbackup_charitydb
> with
> move 'CHARITYDB_Data' to 'o:\Data\CHARITYDBCOPY_Data.MDF',
> move 'CHARITYDB_Log' to 'o:\Data\CHARITYDBCOPY_Log.LDF'
>
> I tried to duplicate this issue in our test environment and it restored
> successfully every time. I did manage to break it by creating a new database
> with a totally different name then try to restore over it and received the
> error message.
> BOL says it performs a safety check if the REPLACE options is not used. If
> so, why has the restore work for a very long time and now decide to break.
> Also, why is it allowing me to restore successfully in test without using the
> REPLACE option.
> I am so confused...
> BOL...
> When the REPLACE option is not specified, a safety check occurs (which
> prevents overwriting a different database by accident). The safety check
> ensures that the RESTORE DATABASE statement will not restore the database to
> the current server if:
> The database named in the RESTORE statement already exists on the current
> server, and
> The database name is different from the database name recorded in the backup
> set.
>|||Hi Tibor,
Thanks for the repsonse. We finally found the actual problem. Someone
restored the copy data outside of the normal job. Once we dropped and
recreated the job functioned as normal. Very strange behavior I must add.
"Tibor Karaszi" wrote:
> A guess is that the source database has grown, so the backup need say 10GB for one of the database
> files, but the existing database file only has, say, 8GB for that file.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "FredG" <FredG@.discussions.microsoft.com> wrote in message
> news:1D2AF5E1-9337-4F55-84EE-B4711171D7C9@.microsoft.com...
> > SQL Server 2000 SP4 build 2187
> >
> > Not sure what is happening with SQL Server backup Internals, I am hoping you
> > can answer.
> >
> > A scheduled job backups up database named "Wells" to a device which points
> > to a physical file as step 1. Same schedule job, step 2, then restores to
> > database WELLSCopy. The WELLSCopy database has always existed. This has been
> > working for a very long time. Now, its generating the following error.
> >
> > Job '0405 Backup and WELLSCopy Restore' : Step 3, 'Restore Wells Copy
> > Database' :
> > Began Executing 2006-10-19 00:59:34
> > Msg 3141, Sev 16: The database to be restored was named 'Wells'.
> > Reissue the statement using the WITH REPLACE option to overwrite the
> > 'WELLScopy'
> > database. [SQLSTATE 42000]
> > Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000]
> >
> >
> > Here is the backup command and restore command.
> > BACKUP DATABASE [Wells] TO Dbackup_WELLS
> > WITH INIT, NOUNLOAD, NAME = N'Wells', SKIP, STATS=10, NOFORMAT
> >
> >
> > restore database CHARITYDBCOPY
> > from dbackup_charitydb
> > with
> > move 'CHARITYDB_Data' to 'o:\Data\CHARITYDBCOPY_Data.MDF',
> > move 'CHARITYDB_Log' to 'o:\Data\CHARITYDBCOPY_Log.LDF'
> >
> >
> > I tried to duplicate this issue in our test environment and it restored
> > successfully every time. I did manage to break it by creating a new database
> > with a totally different name then try to restore over it and received the
> > error message.
> >
> > BOL says it performs a safety check if the REPLACE options is not used. If
> > so, why has the restore work for a very long time and now decide to break.
> > Also, why is it allowing me to restore successfully in test without using the
> > REPLACE option.
> >
> > I am so confused...
> >
> > BOL...
> > When the REPLACE option is not specified, a safety check occurs (which
> > prevents overwriting a different database by accident). The safety check
> > ensures that the RESTORE DATABASE statement will not restore the database to
> > the current server if:
> > The database named in the RESTORE statement already exists on the current
> > server, and
> > The database name is different from the database name recorded in the backup
> > set.
> >
>
Not sure what is happening with SQL Server backup Internals, I am hoping you
can answer.
A scheduled job backups up database named "Wells" to a device which points
to a physical file as step 1. Same schedule job, step 2, then restores to
database WELLSCopy. The WELLSCopy database has always existed. This has been
working for a very long time. Now, its generating the following error.
Job '0405 Backup and WELLSCopy Restore' : Step 3, 'Restore Wells Copy
Database' :
Began Executing 2006-10-19 00:59:34
Msg 3141, Sev 16: The database to be restored was named 'Wells'.
Reissue the statement using the WITH REPLACE option to overwrite the
'WELLScopy'
database. [SQLSTATE 42000]
Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000]
Here is the backup command and restore command.
BACKUP DATABASE [Wells] TO Dbackup_WELLS
WITH INIT, NOUNLOAD, NAME = N'Wells', SKIP, STATS=10, NOFORMAT
restore database CHARITYDBCOPY
from dbackup_charitydb
with
move 'CHARITYDB_Data' to 'o:\Data\CHARITYDBCOPY_Data.MDF',
move 'CHARITYDB_Log' to 'o:\Data\CHARITYDBCOPY_Log.LDF'
I tried to duplicate this issue in our test environment and it restored
successfully every time. I did manage to break it by creating a new database
with a totally different name then try to restore over it and received the
error message.
BOL says it performs a safety check if the REPLACE options is not used. If
so, why has the restore work for a very long time and now decide to break.
Also, why is it allowing me to restore successfully in test without using the
REPLACE option.
I am so confused...
BOL...
When the REPLACE option is not specified, a safety check occurs (which
prevents overwriting a different database by accident). The safety check
ensures that the RESTORE DATABASE statement will not restore the database to
the current server if:
The database named in the RESTORE statement already exists on the current
server, and
The database name is different from the database name recorded in the backup
set.I applogize, I pasted my test restore code. Here is the correct version
RESTORE DATABASE WELLScopy
FROM Dbackup_wells
with
MOVE 'Wells_Data' TO 'e:\data\mssql\data\WELLScopy_Data.MDF',
MOVE 'WELLS_Log' TO 'e:\data\mssql\data\WELLScopy_Log.LDF'
go
"FredG" wrote:
> SQL Server 2000 SP4 build 2187
> Not sure what is happening with SQL Server backup Internals, I am hoping you
> can answer.
> A scheduled job backups up database named "Wells" to a device which points
> to a physical file as step 1. Same schedule job, step 2, then restores to
> database WELLSCopy. The WELLSCopy database has always existed. This has been
> working for a very long time. Now, its generating the following error.
> Job '0405 Backup and WELLSCopy Restore' : Step 3, 'Restore Wells Copy
> Database' :
> Began Executing 2006-10-19 00:59:34
> Msg 3141, Sev 16: The database to be restored was named 'Wells'.
> Reissue the statement using the WITH REPLACE option to overwrite the
> 'WELLScopy'
> database. [SQLSTATE 42000]
> Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000]
>
> Here is the backup command and restore command.
> BACKUP DATABASE [Wells] TO Dbackup_WELLS
> WITH INIT, NOUNLOAD, NAME = N'Wells', SKIP, STATS=10, NOFORMAT
>
> restore database CHARITYDBCOPY
> from dbackup_charitydb
> with
> move 'CHARITYDB_Data' to 'o:\Data\CHARITYDBCOPY_Data.MDF',
> move 'CHARITYDB_Log' to 'o:\Data\CHARITYDBCOPY_Log.LDF'
>
> I tried to duplicate this issue in our test environment and it restored
> successfully every time. I did manage to break it by creating a new database
> with a totally different name then try to restore over it and received the
> error message.
> BOL says it performs a safety check if the REPLACE options is not used. If
> so, why has the restore work for a very long time and now decide to break.
> Also, why is it allowing me to restore successfully in test without using the
> REPLACE option.
> I am so confused...
> BOL...
> When the REPLACE option is not specified, a safety check occurs (which
> prevents overwriting a different database by accident). The safety check
> ensures that the RESTORE DATABASE statement will not restore the database to
> the current server if:
> The database named in the RESTORE statement already exists on the current
> server, and
> The database name is different from the database name recorded in the backup
> set.
>|||A guess is that the source database has grown, so the backup need say 10GB for one of the database
files, but the existing database file only has, say, 8GB for that file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"FredG" <FredG@.discussions.microsoft.com> wrote in message
news:1D2AF5E1-9337-4F55-84EE-B4711171D7C9@.microsoft.com...
> SQL Server 2000 SP4 build 2187
> Not sure what is happening with SQL Server backup Internals, I am hoping you
> can answer.
> A scheduled job backups up database named "Wells" to a device which points
> to a physical file as step 1. Same schedule job, step 2, then restores to
> database WELLSCopy. The WELLSCopy database has always existed. This has been
> working for a very long time. Now, its generating the following error.
> Job '0405 Backup and WELLSCopy Restore' : Step 3, 'Restore Wells Copy
> Database' :
> Began Executing 2006-10-19 00:59:34
> Msg 3141, Sev 16: The database to be restored was named 'Wells'.
> Reissue the statement using the WITH REPLACE option to overwrite the
> 'WELLScopy'
> database. [SQLSTATE 42000]
> Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000]
>
> Here is the backup command and restore command.
> BACKUP DATABASE [Wells] TO Dbackup_WELLS
> WITH INIT, NOUNLOAD, NAME = N'Wells', SKIP, STATS=10, NOFORMAT
>
> restore database CHARITYDBCOPY
> from dbackup_charitydb
> with
> move 'CHARITYDB_Data' to 'o:\Data\CHARITYDBCOPY_Data.MDF',
> move 'CHARITYDB_Log' to 'o:\Data\CHARITYDBCOPY_Log.LDF'
>
> I tried to duplicate this issue in our test environment and it restored
> successfully every time. I did manage to break it by creating a new database
> with a totally different name then try to restore over it and received the
> error message.
> BOL says it performs a safety check if the REPLACE options is not used. If
> so, why has the restore work for a very long time and now decide to break.
> Also, why is it allowing me to restore successfully in test without using the
> REPLACE option.
> I am so confused...
> BOL...
> When the REPLACE option is not specified, a safety check occurs (which
> prevents overwriting a different database by accident). The safety check
> ensures that the RESTORE DATABASE statement will not restore the database to
> the current server if:
> The database named in the RESTORE statement already exists on the current
> server, and
> The database name is different from the database name recorded in the backup
> set.
>|||Hi Tibor,
Thanks for the repsonse. We finally found the actual problem. Someone
restored the copy data outside of the normal job. Once we dropped and
recreated the job functioned as normal. Very strange behavior I must add.
"Tibor Karaszi" wrote:
> A guess is that the source database has grown, so the backup need say 10GB for one of the database
> files, but the existing database file only has, say, 8GB for that file.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "FredG" <FredG@.discussions.microsoft.com> wrote in message
> news:1D2AF5E1-9337-4F55-84EE-B4711171D7C9@.microsoft.com...
> > SQL Server 2000 SP4 build 2187
> >
> > Not sure what is happening with SQL Server backup Internals, I am hoping you
> > can answer.
> >
> > A scheduled job backups up database named "Wells" to a device which points
> > to a physical file as step 1. Same schedule job, step 2, then restores to
> > database WELLSCopy. The WELLSCopy database has always existed. This has been
> > working for a very long time. Now, its generating the following error.
> >
> > Job '0405 Backup and WELLSCopy Restore' : Step 3, 'Restore Wells Copy
> > Database' :
> > Began Executing 2006-10-19 00:59:34
> > Msg 3141, Sev 16: The database to be restored was named 'Wells'.
> > Reissue the statement using the WITH REPLACE option to overwrite the
> > 'WELLScopy'
> > database. [SQLSTATE 42000]
> > Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000]
> >
> >
> > Here is the backup command and restore command.
> > BACKUP DATABASE [Wells] TO Dbackup_WELLS
> > WITH INIT, NOUNLOAD, NAME = N'Wells', SKIP, STATS=10, NOFORMAT
> >
> >
> > restore database CHARITYDBCOPY
> > from dbackup_charitydb
> > with
> > move 'CHARITYDB_Data' to 'o:\Data\CHARITYDBCOPY_Data.MDF',
> > move 'CHARITYDB_Log' to 'o:\Data\CHARITYDBCOPY_Log.LDF'
> >
> >
> > I tried to duplicate this issue in our test environment and it restored
> > successfully every time. I did manage to break it by creating a new database
> > with a totally different name then try to restore over it and received the
> > error message.
> >
> > BOL says it performs a safety check if the REPLACE options is not used. If
> > so, why has the restore work for a very long time and now decide to break.
> > Also, why is it allowing me to restore successfully in test without using the
> > REPLACE option.
> >
> > I am so confused...
> >
> > BOL...
> > When the REPLACE option is not specified, a safety check occurs (which
> > prevents overwriting a different database by accident). The safety check
> > ensures that the RESTORE DATABASE statement will not restore the database to
> > the current server if:
> > The database named in the RESTORE statement already exists on the current
> > server, and
> > The database name is different from the database name recorded in the backup
> > set.
> >
>
Subscribe to:
Posts (Atom)