Sql Backing up Tables: plan

Showing posts with label plan. Show all posts

Thursday, March 29, 2012

Backup File Deletions not Working

Hi:
We configured a backup job in the database maintenance plan for a database
for both "Complete Backup" and the "Transaction Log". We told the job to
delete files after one day. It has not been deleting these files, though.
Why is that?
JohnHi
Files are deleted after the next backup completes successfully.
If you se a backup to delete after 1 day, and you don't run the actual
backup job for 2 days, the backup will remain on disk for 2 days, until
after the backup is successful.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:6E533D8D-A308-47F9-AC62-282987DBF849@.microsoft.com...
> Hi:
> We configured a backup job in the database maintenance plan for a database
> for both "Complete Backup" and the "Transaction Log". We told the job to
> delete files after one day. It has not been deleting these files, though.
> Why is that?
> John|||So, does that mean that the backups have not been successful?
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Files are deleted after the next backup completes successfully.
> If you se a backup to delete after 1 day, and you don't run the actual
> backup job for 2 days, the backup will remain on disk for 2 days, until
> after the backup is successful.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:6E533D8D-A308-47F9-AC62-282987DBF849@.microsoft.com...
> > Hi:
> >
> > We configured a backup job in the database maintenance plan for a database
> > for both "Complete Backup" and the "Transaction Log". We told the job to
> > delete files after one day. It has not been deleting these files, though.
> >
> > Why is that?
> >
> > John
>
>|||See this old post:-
http://groups.google.co.in/group/microsoft.public.sqlserver.server/browse_thread/thread/f24fa621edb472b/04141cc02159d2bc?lnk=st&q=maintenance+plan+not+deleting+old+files&rnum=4&hl=en#04141cc02159d2bc
Thanks
Hari
SQL Server MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:262CAB61-803A-46BF-AA4A-CF5EC4AF8672@.microsoft.com...
> So, does that mean that the backups have not been successful?
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Files are deleted after the next backup completes successfully.
>> If you se a backup to delete after 1 day, and you don't run the actual
>> backup job for 2 days, the backup will remain on disk for 2 days, until
>> after the backup is successful.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
>> message news:6E533D8D-A308-47F9-AC62-282987DBF849@.microsoft.com...
>> > Hi:
>> >
>> > We configured a backup job in the database maintenance plan for a
>> > database
>> > for both "Complete Backup" and the "Transaction Log". We told the job
>> > to
>> > delete files after one day. It has not been deleting these files,
>> > though.
>> >
>> > Why is that?
>> >
>> > John
>>|||Hi
No. Look at the backup job logs to see what is going on.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:262CAB61-803A-46BF-AA4A-CF5EC4AF8672@.microsoft.com...
> So, does that mean that the backups have not been successful?
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Files are deleted after the next backup completes successfully.
>> If you se a backup to delete after 1 day, and you don't run the actual
>> backup job for 2 days, the backup will remain on disk for 2 days, until
>> after the backup is successful.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
>> message news:6E533D8D-A308-47F9-AC62-282987DBF849@.microsoft.com...
>> > Hi:
>> >
>> > We configured a backup job in the database maintenance plan for a
>> > database
>> > for both "Complete Backup" and the "Transaction Log". We told the job
>> > to
>> > delete files after one day. It has not been deleting these files,
>> > though.
>> >
>> > Why is that?
>> >
>> > John
>>

Backup fails with ConnectionRead (WrapperRead()

For the past week my maintenance plan that backs up the
master and my main database has been failing, but only on
my database. The master backup works fine. The plan fails
when trying to execute:
BACKUP DATABASE [WebTools] TO DISK = N'G:\SQLBackup\WebTools\WebTools_db_200310290834.BAK'
WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
When I try running this command via Enterprise Manager, it
reports a syntax error "WITHINIT" (no space). I removed
all the WITH settings and re-ran it, but then it failed
with a ConnectionRead error.
Using Query Analyzer, the command runs for a few seconds
and then reports the error:
[Microsoft][ODBC SQL Server Driver][Shared Memory]
ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
THe backup, however, continues and completes successfully
according to Event Viewer.
Can anyone shed some light on this? TIA.
I'm running W2K SP4, SQL2K SP3 on a Dell 2600 with a Xeon
processor (which looks like 2 CPUs).Hello David,
I would appreciate your patience while I am looking into this issue. I'm now performing some
troubleshoots on your issue and will post my response at soon as I have update for you.
Thanks for posting to MSDN Managed Newsgroup.
Regares,
Billy Yao
Microsoft Online Partner Support|||Hi David,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with your issue.
From your description, I understand that your maintenance plan failed for database backup
errors. Therefore, you performed the backup manually but another network error occurred.
However, the backup did succeed according to your event log.
Have I fully understood you David? If there is anything I misunderstood, please feel free to let
me know.
Considering the backup did succeed and the network error is so general, I suspect that the
issue is located in the network library. It is recommended that you apply the latest MDAC 2.8 to
suppress this symptom. You can download this MDAC via:
http://www.microsoft.com/downloads/details.aspx?FamilyID=6c050fe3-c795-4b7d-b037-
185d0506396c&DisplayLang=en
If you use Named Pipes Net-Library to connect to the SQL Server database, I strongly
recommend you use the TCP/IP Net-Library instead.
For the exact steps to set the TCP/IP network library on the client where you are performing the
backup or the restore operation, see the "How to configure a client to use TCP/IP (Client
Network Utility)" chapter in SQL Server 2000 Books Online.
When you connect to an instance of SQL Server by using SQL Query Analyzer, you can force
the connection to use the TCP/IP Net-Library. To do this, type the name of the instance of SQL
Server with the tcp prefix in the SQL Server text box in the Connect to SQL Server dialog box.
This appears as follows:
tcp:SQL Server Name
Another possible cause of backup failure is that there was no space in the disk drive due to
the fact that the transaction logs were not got truncated. In this case, the logs may fill up disk
space, and you need to shrink the transaction log files first.
To resolve this problem, please follow the steps in the following articles to truncate the
transaction log
272318 INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://support.microsoft.com/?id=272318
David, please apply the suggestions above and let me know if it helps you resolve your
problem. If there is anything more I can assist you with, please feel free to post it in the group.
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks Billy.
First... I had already installed MDAC 2.8 as part of an
attempt to resolve a different problem, and disk space
wasn't an issue.
I then tried to use QA to connect via TCP to SQL Server,
but couldn't. I checked the network settings in SQL Server
and it indicated both Named Pipes and TCP/IP were
available. When I checked the log, however, it said it was
only listening on the named pipes.
I did some more digging, but nothing fixed that problem. I
wound up removing SQL Server from the system and re-
installing it from scratch. The log showed it was now
listening on TCP as well. When I tried the backups via QA
and the maintenance plan, both worked.|||Dear David,
Thank you for your good news!
I'm glad that the problem was solved by re-installing the SQL Server. I
think the cause of SQL Server not listening on TCP may be related to some
register key was revised with some unexpect reasons (such as by some
applications).
Anyway, I appreciate your logical troubleshooting and congratulations on
your finding the cause and solve the problem.
Thank you for participating our newsgroup!
- Billy

Tuesday, March 27, 2012

BACKUP failed to complete the command sp_prepexec;1

One of my SQL Servers (SQL 2000 SP4) is reporting this error in the Maintenance Plan. But shortly after it shows this error is does successfully backup my Databases (per log history), however the plan is indicating "failure".

I have no idea how to resolve this nor why it is happening.

Rob.

Hi,

it seems, that an t-sql statement is blocking.

Use the profiler and filter by object name = sp_prepexec and SPID.

actual in the KB:

HOW TO: Troubleshoot Application Performance Issues

How to monitor SQL Server 2000 blocking

tosc

BACKUP failed to complete the command sp_prepexec;1

I have no idea how to resolve this nor why it is happening.

Rob.

Hi,

it seems, that an t-sql statement is blocking.

Use the profiler and filter by object name = sp_prepexec and SPID.

actual in the KB:

HOW TO: Troubleshoot Application Performance Issues

How to monitor SQL Server 2000 blocking

tosc

Backup fail

Hi All,
I have a basic DB backup question:
I make DB backups every day at night using maintenance plan, where all DB
are saved. In 1st step the database backup is made, in 2nd step the
transaction log is saved. Sometimes, usually once a week (e.g. on Monday
1.00 am), one of the database backups failed with "... failed because DB log
is full" error. Now I make a manual backup, which is normally proceeded.
Then, the next backups work normally approx. 1 week. Situation repeates, but
not exactly every week.
After the log backup, I think it will truncate to small size. It is not
true, usual size of log is about 130 MB (database size is 80 MB). But after
a backup failes, the log size is about 270 MB.
In backup parameters the log truncate option is included, but in maintenance
plan this option is missing. So if I'll make backups manually, no problems
will occur. But I want work automatically.
What am I do to correct backup job?
Thank you in advance for your help
Vlastik
Are you saying that you only do log backup once a week? If so, I suggest you do it more frequently. I
generally do db backup once a day and log backup one per hour.
Also, log backup only empties the log file, it doesn't shrink the file. If you have autoshrink on, then the
background process can shrink the file after the log is emptied. Or, your job does an explicit shrink using
DBCC SHRINKDB or DBCC SHRINKFILE. However, there are disadvantages with shrinking the files, see:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"greybeard" <bartos@.spsmvbr.cz> wrote in message news:%23N3PuRpMEHA.3940@.tk2msftngp13.phx.gbl...
> Hi All,
> I have a basic DB backup question:
> I make DB backups every day at night using maintenance plan, where all DB
> are saved. In 1st step the database backup is made, in 2nd step the
> transaction log is saved. Sometimes, usually once a week (e.g. on Monday
> 1.00 am), one of the database backups failed with "... failed because DB log
> is full" error. Now I make a manual backup, which is normally proceeded.
> Then, the next backups work normally approx. 1 week. Situation repeates, but
> not exactly every week.
> After the log backup, I think it will truncate to small size. It is not
> true, usual size of log is about 130 MB (database size is 80 MB). But after
> a backup failes, the log size is about 270 MB.
> In backup parameters the log truncate option is included, but in maintenance
> plan this option is missing. So if I'll make backups manually, no problems
> will occur. But I want work automatically.
> What am I do to correct backup job?
> Thank you in advance for your help
> Vlastik
>
>
|||Hi, Tibor,
thank you for your response, it helps me to understand some functions of SQL 2000. Of course I've made both data and log backups daily, but something in them doesn't work.
Atfer your response, I checked the settings of my DBs and all settings are O.K. including autoshrink. I've only added a scheduled shrink daily after backup, because an automatic shrink didn't work and I don't know why. For example today, after backups and autoshrink, the database and log size were 112/149 MB. I made manual shrink and sizes changed to 84/41 MB. It's really crazy.
I checked the plan history in [msdb], but I'm not pretty enough to understand it. Database shrink was done every day. Here is a selection for database 'Kredit', which is the biggest:
database_name ;activity ;succeeded ;end_time ;error_number
Kredit ;Backup database ;False ;26.4.2004 1:00:09 ;9002
Kredit ;Backup transaction log ;True ;26.4.2004 1:31:00 ;0
Kredit ;Verify Backup ;True ;26.4.2004 1:31:22 ;0
Kredit ;Rebuild Indexes ;True ;26.4.2004 2:01:43 ;0
Kredit ;Shrink Database ;True ;26.4.2004 2:01:59 ;0
Kredit ;Backup database ;True ;27.4.2004 1:00:37 ;0
Kredit ;Verify Backup ;True ;27.4.2004 1:00:48 ;0
Kredit ;Backup transaction log ;True ;27.4.2004 1:30:49 ;0
Kredit ;Verify Backup ;True ;27.4.2004 1:31:07 ;0
Kredit ;Rebuild Indexes ;True ;27.4.2004 2:01:41 ;0
Kredit ;Shrink Database ;True ;27.4.2004 2:01:57 ;0
Kredit ;Backup database ;True ;28.4.2004 1:00:35 ;0
Kredit ;Verify Backup ;True ;28.4.2004 1:00:45 ;0
Kredit ;Backup transaction log ;True ;28.4.2004 1:30:45 ;0
Kredit ;Verify Backup ;True ;28.4.2004 1:31:00 ;0
Kredit ;Rebuild Indexes ;True ;28.4.2004 2:01:42 ;0
Kredit ;Shrink Database ;True ;28.4.2004 2:01:59 ;0
Kredit ;Backup database ;True ;29.4.2004 1:00:35 ;0
Kredit ;Verify Backup ;True ;29.4.2004 1:00:45 ;0
Kredit ;Backup transaction log ;True ;29.4.2004 1:30:43 ;0
Kredit ;Verify Backup ;True ;29.4.2004 1:30:59 ;0
Kredit ;Rebuild Indexes ;True ;29.4.2004 2:01:42 ;0
Kredit ;Shrink Database ;True ;29.4.2004 2:01:57 ;0
Kredit ;Backup database ;True ;30.4.2004 1:00:37 ;0
Kredit ;Verify Backup ;True ;30.4.2004 1:00:48 ;0
Kredit ;Backup transaction log ;True ;30.4.2004 1:30:45 ;0
Kredit ;Verify Backup ;True ;30.4.2004 1:31:00 ;0
Kredit ;Rebuild Indexes ;True ;30.4.2004 2:01:48 ;0
Kredit ;Shrink Database ;True ;30.4.2004 2:02:04 ;0
Kredit ;Backup database ;False ;3.5.2004 1:00:11 ;9002
Kredit ;Backup transaction log ;True ;3.5.2004 1:31:01 ;0
Kredit ;Verify Backup ;True ;3.5.2004 1:31:24 ;0
Kredit ;Rebuild Indexes ;True ;3.5.2004 2:01:41 ;0
Kredit ;Shrink Database ;True ;3.5.2004 2:01:58 ;0
Note, than there is no reason for failing backup on Mondays, sometimes it fails on another day. From Friday 19:00 to Monday, 5:00, no DB activity is performed.
On the disks, there is enough space for all backup files and a half disk is permanently free.
After these corrections, I hope it'll go better.
Thanks once more, tomorrow I'll write, how it continues.
Best regards, Vlastik
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> pe v diskusnm pspvku news:%23DiHakrMEHA.2592@.tk2msftngp13.phx.gbl...
> Are you saying that you only do log backup once a week? If so, I suggest you do it more frequently. I
> generally do db backup once a day and log backup one per hour.
> Also, log backup only empties the log file, it doesn't shrink the file. If you have autoshrink on, then the
> background process can shrink the file after the log is emptied. Or, your job does an explicit shrink using
> DBCC SHRINKDB or DBCC SHRINKFILE. However, there are disadvantages with shrinking the files, see:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "greybeard" <bartos@.spsmvbr.cz> wrote in message news:%23N3PuRpMEHA.3940@.tk2msftngp13.phx.gbl...
>
|||Bad results.
Today the backup was proceeded normally, but database wasn't shrunk. After
2nd manual backups and shrinking the "empty" log has 142 MB!
|||Did you check the VLF layout? (See the article I referred to.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"greybeard" <bartos@.spsmvbr.cz> wrote in message news:e7YTqu%23MEHA.3016@.tk2msftngp13.phx.gbl...
> Bad results.
> Today the backup was proceeded normally, but database wasn't shrunk. After
> 2nd manual backups and shrinking the "empty" log has 142 MB!
>
>
|||It's incredible! After 3rd manual backup and shrinking the log has
"compressed" to 8MB.
The VLF layout shows now 22 blocks, where 1st, 3rd and 22th are in use. Now
it's O.K., because shrink setting is to leave 10% of free blocks.
Why can't this work automatically and in the first try?
So, I'll try to make backups more often. Perhaps it helps.
Thank you a lot.
Rgds, Vlastik
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> pe v
diskusnm pspvku news:%23$MLkU$MEHA.2388@.TK2MSFTNGP09.phx.gbl...
> Did you check the VLF layout? (See the article I referred to.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "greybeard" <bartos@.spsmvbr.cz> wrote in message
news:e7YTqu%23MEHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
After
>
|||So, after some experiments, I've written my own program for DB backup, which
works with all database files, save and shrink them as I need. It works
fine. It's the only thing, what I've had to do before my holidays.
My program makes backup and than shrinks the trnsact. log so many times,
till its size remains constant. May be it is strange, but it works. Because
this backups is done at 0:30, I'm not afraid of complications.
Thanks for your tips, I've read all, but I wasn't satisfied with this.
Best regards
Vlastik
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> pe v
diskusnm pspvku news:%23$MLkU$MEHA.2388@.TK2MSFTNGP09.phx.gbl...
> Did you check the VLF layout? (See the article I referred to.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "greybeard" <bartos@.spsmvbr.cz> wrote in message
news:e7YTqu%23MEHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
After
>
sql

Sunday, March 25, 2012

Backup error on Microsoft CMS 2002 SP1A Database

I have set up a Database maintenance plan to perform full backup on all
databases on a daily basis. Recently I notice that it is failing to backup
the Microsoft Content Management Server 2002 SP1a Database on SQL Server
2000 with SP3a. The error log for the maintenance job is:
[21] Database Website: Database Backup...
Destination: & #91;d:\MSSQL\BACKUP\Website\Website_db_2
00403091753.BAK]
** Execution Time: 0 hrs, 0 mins, 4 secs **
[22] Database Website: Verifying Backup...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'd:\MSSQL\BACKUP\Website\Website_db_2004
03091753.BAK'. Device error or
device off-line. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE i
s
terminating abnormally.
Deleting old text reports... 0 file(s) deleted.
The file d:\MSSQL\Log\ERROR contains the following corresponding entry
2004-03-09 17:53:51.51 backup BACKUP failed to complete the command
BACKUP DATABASE [Website] TO DISK =
N'd:\MSSQL\BACKUP\Website\Website_db_200
403091753.BAK' WITH INIT ,
NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
2004-03-09 17:53:51.53 spid64 BackupDiskFile::OpenMedia: Backup device
'd:\MSSQL\BACKUP\Website\Website_db_2004
03091753.BAK' failed to open.
Operating system error = 32(error not found).
What is going on and how could I rectify this (without stopping and
service)?hi Patrick,
this seems to be a SQL related question.
Please post to an SQL related newsgroup.
Cheers,
Stefan.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:e6kihBgBEHA.688@.tk2msftngp13.phx.gbl...
> I have set up a Database maintenance plan to perform full backup on all
> databases on a daily basis. Recently I notice that it is failing to
backup
> the Microsoft Content Management Server 2002 SP1a Database on SQL Server
> 2000 with SP3a. The error log for the maintenance job is:
> [21] Database Website: Database Backup...
> Destination: & #91;d:\MSSQL\BACKUP\Website\Website_db_2
00403091753.BAK]
> ** Execution Time: 0 hrs, 0 mins, 4 secs **
> [22] Database Website: Verifying Backup...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODB
C
SQL
> Server Driver][SQL Server]Cannot open backup device
> 'd:\MSSQL\BACKUP\Website\Website_db_2004
03091753.BAK'. Device error or
> device off-line. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE
is
> terminating abnormally.
> Deleting old text reports... 0 file(s) deleted.
>
> The file d:\MSSQL\Log\ERROR contains the following corresponding entry
> 2004-03-09 17:53:51.51 backup BACKUP failed to complete the command
> BACKUP DATABASE [Website] TO DISK =
> N'd:\MSSQL\BACKUP\Website\Website_db_200
403091753.BAK' WITH INIT ,
> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
> 2004-03-09 17:53:51.53 spid64 BackupDiskFile::OpenMedia: Backup device
> 'd:\MSSQL\BACKUP\Website\Website_db_2004
03091753.BAK' failed to open.
> Operating system error = 32(error not found).
> What is going on and how could I rectify this (without stopping and
> service)?
>|||Patrick,
It sounds like it could be a rights problem, such as described in:
PRB: Unable to Back Up Database to a Network Drive Without Permissions
http://support.microsoft.com/defaul...kb;en-us;207187
Alternatively, the device is offline.
(OR the disk is full and cannot create another file. But I would have
expected a different message.)
Russell Fields
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:e6kihBgBEHA.688@.tk2msftngp13.phx.gbl...
> I have set up a Database maintenance plan to perform full backup on all
> databases on a daily basis. Recently I notice that it is failing to
backup
> the Microsoft Content Management Server 2002 SP1a Database on SQL Server
> 2000 with SP3a. The error log for the maintenance job is:
> [21] Database Website: Database Backup...
> Destination: & #91;d:\MSSQL\BACKUP\Website\Website_db_2
00403091753.BAK]
> ** Execution Time: 0 hrs, 0 mins, 4 secs **
> [22] Database Website: Verifying Backup...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODB
C
SQL
> Server Driver][SQL Server]Cannot open backup device
> 'd:\MSSQL\BACKUP\Website\Website_db_2004
03091753.BAK'. Device error or
> device off-line. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE
is
> terminating abnormally.
> Deleting old text reports... 0 file(s) deleted.
>
> The file d:\MSSQL\Log\ERROR contains the following corresponding entry
> 2004-03-09 17:53:51.51 backup BACKUP failed to complete the command
> BACKUP DATABASE [Website] TO DISK =
> N'd:\MSSQL\BACKUP\Website\Website_db_200
403091753.BAK' WITH INIT ,
> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
> 2004-03-09 17:53:51.53 spid64 BackupDiskFile::OpenMedia: Backup device
> 'd:\MSSQL\BACKUP\Website\Website_db_2004
03091753.BAK' failed to open.
> Operating system error = 32(error not found).
> What is going on and how could I rectify this (without stopping and
> service)?
>|||I don't think the two reasons apply to me, because
1) The backup is done to d:\MSSQL\Backup (a local drive), which the SQL
Server and SQL Agent service account user has permission to right to (the
service account user has local admin priviledges)
2) The database is online (it is functioning otherwise) and so is d:\
The Maintenance plan succeeded in backing up *All* other database on the
same local server running:
1) SQL Server 2000 SP3a
2) Windows 2000 with SP4
The Maintenance plan also succeeded in backing up that database for >1 week
before it become persistently failing!
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OY5grWgBEHA.1380@.TK2MSFTNGP10.phx.gbl...
> Patrick,
> It sounds like it could be a rights problem, such as described in:
> PRB: Unable to Back Up Database to a Network Drive Without Permissions
> http://support.microsoft.com/defaul...kb;en-us;207187
> Alternatively, the device is offline.
> (OR the disk is full and cannot create another file. But I would have
> expected a different message.)
> Russell Fields
> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
> news:e6kihBgBEHA.688@.tk2msftngp13.phx.gbl...
> backup
> SQL
device
>|||I just did another search and found
http://groups.google.com/groups?hl=...r />
ie%3DUTF-
8%26oe%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
I am using Symantec Anti Virus Corporate Edition Client (although will move
over to McAfee Virus Scan 7 soon), but confused as to why the DB Maintenance
plan is just failing to back up on this single database. The only thing
special about this database is that there might be people *reading* from it
whilst the backup is being one, but I should have thought that SQL Server
should cope with this OK.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OY5grWgBEHA.1380@.TK2MSFTNGP10.phx.gbl...
> Patrick,
> It sounds like it could be a rights problem, such as described in:
> PRB: Unable to Back Up Database to a Network Drive Without Permissions
> http://support.microsoft.com/defaul...kb;en-us;207187
> Alternatively, the device is offline.
> (OR the disk is full and cannot create another file. But I would have
> expected a different message.)
> Russell Fields
> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
> news:e6kihBgBEHA.688@.tk2msftngp13.phx.gbl...
> backup
> SQL
device
>|||Hi Patrick,
Thank your for using the newsgroup. It is my pleasure to help you with your
issue.
From you information, you job to maintenance the database 'Website' ran
fine in the past, however, just recently, it persistently failed.
There must be some change that different with before, so I want to confirm
that when you disable the Anti-Virus Software on your computer, do you
still meet the problem? The following link would take you to the download
of the Utility called ProcessExplorer that you can use to monitor access of
files by different processes.
To find what processes are accessing a particular file, you would need to
Search and enter the File Name for obtaining a list of the processes.
http://www.sysinternals.com/ntw2k/f...e/procexp.shtml
If it is not caused by the Anti-Virus software, you could try the following
steps to narrow down the problem. First, stop the antivirus software and:
1) In the Enterprise Manager, using the Backup Wizard to backup the
database, any problems?
2) Run in you Query Analyzer the following code (Suppose you are using sa
and you login into the Query Analyzer by the account starting the SQL Agent
Service):
Exec xp_cmdshell 'md d:\MSSQL\BACKUP\Website\aaa'
Go
Exec xp_cmdshell 'rd d:\MSSQL\BACKUP\Website\aaa'
go
BACKUP DATABASE [Website] TO DISK
=N'd:\MSSQL\BACKUP\Website\Website_db_20
0403091753.BAK' WITH INIT ,
NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
Is there any Problems and messages?
Looking forward to you reply.
Thanks
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||1) I did a serach for handle *.BAK with SysInternal's process explorer and
could not find Symantec Anti Virus trying to access the *.BAK file.
2) I could select the Website database, right click All Task->Backup
Database to do a complete backup successfully
3) For some reason if I log on as a user with DBA role (by Windows admin
group membership) into query analyser, the execution of EXEC xp_cmdshell
failed saying command not found, but more worrying is that when I execute
the backup command, I get the following error
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionRead
(WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
10 percent backed up.
Connection Broken
The SQLServer 2000 with SP3a is installed on a Windows 2000 SP4 running
Microsoft Content Management Server 2002 SP1A, ASP.NET, ASP applications and
it is a domain controller. The SQL Client Network utility is configured as
followed:
- Enabled Shared memory protocol not ticked
- enabled Protocol by order- Named Piped, TCP/IP
SQL Client was set up as above because there were timed-out issues when
using the Microsoft Content Management Server 2002 SP1A Site deployment
import scripts.
What is wrong?
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:jA66KVmBEHA.660@.cpmsftngxa06.phx.gbl...
> Hi Patrick,
> Thank your for using the newsgroup. It is my pleasure to help you with
your
> issue.
> From you information, you job to maintenance the database 'Website' ran
> fine in the past, however, just recently, it persistently failed.
> There must be some change that different with before, so I want to confirm
> that when you disable the Anti-Virus Software on your computer, do you
> still meet the problem? The following link would take you to the download
> of the Utility called ProcessExplorer that you can use to monitor access
of
> files by different processes.

> To find what processes are accessing a particular file, you would need to
> Search and enter the File Name for obtaining a list of the processes.
> http://www.sysinternals.com/ntw2k/f...e/procexp.shtml
> If it is not caused by the Anti-Virus software, you could try the
following
> steps to narrow down the problem. First, stop the antivirus software and:
> 1) In the Enterprise Manager, using the Backup Wizard to backup the
> database, any problems?
> 2) Run in you Query Analyzer the following code (Suppose you are using sa
> and you login into the Query Analyzer by the account starting the SQL
Agent
> Service):
> Exec xp_cmdshell 'md d:\MSSQL\BACKUP\Website\aaa'
> Go
> Exec xp_cmdshell 'rd d:\MSSQL\BACKUP\Website\aaa'
> go
> BACKUP DATABASE [Website] TO DISK
> =N'd:\MSSQL\BACKUP\Website\Website_db_20
0403091753.BAK' WITH INIT ,
> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
> Is there any Problems and messages?
> Looking forward to you reply.
> Thanks
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
>|||Strangely, if I change SQL Client Network utility to use TCP/IP before
Named-pipes, then the problem seems to have gone. But why is this? I
thought when SQL Server is installed locally to the server, named-pipes go
via the system kernel which is meant to be super fast?
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:uy6S1VoBEHA.2888@.TK2MSFTNGP09.phx.gbl...
> 1) I did a serach for handle *.BAK with SysInternal's process explorer and
> could not find Symantec Anti Virus trying to access the *.BAK file.
> 2) I could select the Website database, right click All Task->Backup
> Database to do a complete backup successfully
> 3) For some reason if I log on as a user with DBA role (by Windows admin
> group membership) into query analyser, the execution of EXEC xp_cmdshell
> failed saying command not found, but more worrying is that when I execute
> the backup command, I get the following error
> [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionRe
ad
> (WrapperRead()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> 10 percent backed up.
> Connection Broken
> The SQLServer 2000 with SP3a is installed on a Windows 2000 SP4 running
> Microsoft Content Management Server 2002 SP1A, ASP.NET, ASP applications
and
> it is a domain controller. The SQL Client Network utility is configured
as
> followed:
> - Enabled Shared memory protocol not ticked
> - enabled Protocol by order- Named Piped, TCP/IP
> SQL Client was set up as above because there were timed-out issues when
> using the Microsoft Content Management Server 2002 SP1A Site deployment
> import scripts.
> What is wrong?
> "Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
> news:jA66KVmBEHA.660@.cpmsftngxa06.phx.gbl...
> your
confirm
download
> of
>
to
> following
and:
sa
> Agent
rights.
>|||Hi Patrick,
Thanks for your update. You did a lot of work in troubleshooting this
problem and it seems that you have found a workaround for the this problem.
It is much helpful and appreciated.
For the xp_cmdshell, the execute permissions for xp_cmdshell default to
members of the sysadmin fixed server role, but can be granted to other
users. I just want to make sure the account to run the backup job (SQL
Agent Service Account is the account that will finally run the job, if
proxy is not used) has the permission on the specific folder.
For your question of using TCP/IP instead of the Named Pipe, please refer
to the following article:
General Network Error When You Try to Back up or Restore a SQL Server
Database on a Computer That Is Running Windows Server 2003 (also apply to
Windows Server 2000)
http://support.microsoft.com/?id=827452
Then you could use the SQL Agent starting service account to run the backup
statement successfully, you could create the maintance job. If there is
any more problems about it, I will be ready to help.
Thanks
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Backup Error - SQL 2005

Hi there!
I just posted a question about my Backup Plan 2005 SQL and I got very good
answers!, but now, I'm getting other problem, I'm checking the event viewer
and I see this error message : "BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG
is deprecated. The simple recovery model should be used to automatically
truncate the transaction log"
- I'm using Management Studio to configure the backups
- All my databases are Full Recovery Model.
- I run Full Backup of my databases every day. (1:00am)
- I run every 15 minutes backup of the LOG files
I read something on the MS website (KB 818202) and they say :
"This warning message may be logged because the NO_LOG and TRUNCATE_ONLY
options of the BACKUP statement truncate the transaction log files, and you
might need transaction logs for the full recovery of the database."
So, my doubt is:
- The error is because I'm running the backup of the logs every 15 minutes
and THAT is truncating the LOG file, so, what I have to do? When I'v created
the job on the Management Studio, I havent had the option to set "NO_LOG"
and "TRUNCATE_ONLY"...'
Can somebody give me a hand with this' please..
What I really want is recovery the full database in case something
happens...at least the most actual data...
Thanks you!
JoseCheck again the options of your log backup maintenance plan. Maybe another
administrator/job/process is doing the truncates. Â¿Can you set a profiler
trace to search for "suspicious" log backups with "truncate_only" or
"no_log"?
--
RubÃ©n GarrigÃ³s
Solid Quality Mentors
"Jose" <Jose@.discussions.microsoft.com> wrote in message
news:293D0F53-7004-4889-AF52-396116DDCF07@.microsoft.com...
> Hi there!
> I just posted a question about my Backup Plan 2005 SQL and I got very good
> answers!, but now, I'm getting other problem, I'm checking the event
> viewer
> and I see this error message : "BACKUP LOG WITH TRUNCATE_ONLY or WITH
> NO_LOG
> is deprecated. The simple recovery model should be used to automatically
> truncate the transaction log"
> - I'm using Management Studio to configure the backups
> - All my databases are Full Recovery Model.
> - I run Full Backup of my databases every day. (1:00am)
> - I run every 15 minutes backup of the LOG files
> I read something on the MS website (KB 818202) and they say :
> "This warning message may be logged because the NO_LOG and TRUNCATE_ONLY
> options of the BACKUP statement truncate the transaction log files, and
> you
> might need transaction logs for the full recovery of the database."
> So, my doubt is:
> - The error is because I'm running the backup of the logs every 15 minutes
> and THAT is truncating the LOG file, so, what I have to do? When I'v
> created
> the job on the Management Studio, I havent had the option to set "NO_LOG"
> and "TRUNCATE_ONLY"...'
> Can somebody give me a hand with this' please..
> What I really want is recovery the full database in case something
> happens...at least the most actual data...
> Thanks you!
> Jose|||I fully agree. *Somebody* is doing BACKUP LOG dbname WITH TRUNCATE_ONLY (or NO_LOG). You need to
hunt that down.
A regular log backup (where you actually do a backup) will not produce that message in the event
log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rubén Garrigós" <novalidaddress@.none.com> wrote in message
news:A6546FF2-01BB-48C4-BBDA-535B58B89019@.microsoft.com...
> Check again the options of your log backup maintenance plan. Maybe another
> administrator/job/process is doing the truncates. ¿Can you set a profiler trace to search for
> "suspicious" log backups with "truncate_only" or "no_log"?
> --
> Rubén Garrigós
> Solid Quality Mentors
> "Jose" <Jose@.discussions.microsoft.com> wrote in message
> news:293D0F53-7004-4889-AF52-396116DDCF07@.microsoft.com...
>> Hi there!
>> I just posted a question about my Backup Plan 2005 SQL and I got very good
>> answers!, but now, I'm getting other problem, I'm checking the event viewer
>> and I see this error message : "BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG
>> is deprecated. The simple recovery model should be used to automatically
>> truncate the transaction log"
>> - I'm using Management Studio to configure the backups
>> - All my databases are Full Recovery Model.
>> - I run Full Backup of my databases every day. (1:00am)
>> - I run every 15 minutes backup of the LOG files
>> I read something on the MS website (KB 818202) and they say :
>> "This warning message may be logged because the NO_LOG and TRUNCATE_ONLY
>> options of the BACKUP statement truncate the transaction log files, and you
>> might need transaction logs for the full recovery of the database."
>> So, my doubt is:
>> - The error is because I'm running the backup of the logs every 15 minutes
>> and THAT is truncating the LOG file, so, what I have to do? When I'v created
>> the job on the Management Studio, I havent had the option to set "NO_LOG"
>> and "TRUNCATE_ONLY"...'
>> Can somebody give me a hand with this' please..
>> What I really want is recovery the full database in case something
>> happens...at least the most actual data...
>> Thanks you!
>> Jose
>|||You may want to set up a profiler trace, and filter on BACKUP in the
TextData.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O53r9x9YIHA.1204@.TK2MSFTNGP03.phx.gbl...
I fully agree. *Somebody* is doing BACKUP LOG dbname WITH TRUNCATE_ONLY (or
NO_LOG). You need to
hunt that down.
A regular log backup (where you actually do a backup) will not produce that
message in the event
log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rubén Garrigós" <novalidaddress@.none.com> wrote in message
news:A6546FF2-01BB-48C4-BBDA-535B58B89019@.microsoft.com...
> Check again the options of your log backup maintenance plan. Maybe another
> administrator/job/process is doing the truncates. ¿Can you set a profiler
> trace to search for
> "suspicious" log backups with "truncate_only" or "no_log"?
> --
> Rubén Garrigós
> Solid Quality Mentors
> "Jose" <Jose@.discussions.microsoft.com> wrote in message
> news:293D0F53-7004-4889-AF52-396116DDCF07@.microsoft.com...
>> Hi there!
>> I just posted a question about my Backup Plan 2005 SQL and I got very
>> good
>> answers!, but now, I'm getting other problem, I'm checking the event
>> viewer
>> and I see this error message : "BACKUP LOG WITH TRUNCATE_ONLY or WITH
>> NO_LOG
>> is deprecated. The simple recovery model should be used to automatically
>> truncate the transaction log"
>> - I'm using Management Studio to configure the backups
>> - All my databases are Full Recovery Model.
>> - I run Full Backup of my databases every day. (1:00am)
>> - I run every 15 minutes backup of the LOG files
>> I read something on the MS website (KB 818202) and they say :
>> "This warning message may be logged because the NO_LOG and TRUNCATE_ONLY
>> options of the BACKUP statement truncate the transaction log files, and
>> you
>> might need transaction logs for the full recovery of the database."
>> So, my doubt is:
>> - The error is because I'm running the backup of the logs every 15
>> minutes
>> and THAT is truncating the LOG file, so, what I have to do? When I'v
>> created
>> the job on the Management Studio, I havent had the option to set
>> "NO_LOG"
>> and "TRUNCATE_ONLY"...'
>> Can somebody give me a hand with this' please..
>> What I really want is recovery the full database in case something
>> happens...at least the most actual data...
>> Thanks you!
>> Jose
>|||Thank you!! all of you!
I will tracert the "suspicious" man!
Have a nice day!
Jose
"Tom Moreau" wrote:
> You may want to set up a profiler trace, and filter on BACKUP in the
> TextData.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:O53r9x9YIHA.1204@.TK2MSFTNGP03.phx.gbl...
> I fully agree. *Somebody* is doing BACKUP LOG dbname WITH TRUNCATE_ONLY (or
> NO_LOG). You need to
> hunt that down.
> A regular log backup (where you actually do a backup) will not produce that
> message in the event
> log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "RubÃ©n GarrigÃ³s" <novalidaddress@.none.com> wrote in message
> news:A6546FF2-01BB-48C4-BBDA-535B58B89019@.microsoft.com...
> > Check again the options of your log backup maintenance plan. Maybe another
> > administrator/job/process is doing the truncates. Â¿Can you set a profiler
> > trace to search for
> > "suspicious" log backups with "truncate_only" or "no_log"?
> > --
> >
> > RubÃ©n GarrigÃ³s
> > Solid Quality Mentors
> >
> > "Jose" <Jose@.discussions.microsoft.com> wrote in message
> > news:293D0F53-7004-4889-AF52-396116DDCF07@.microsoft.com...
> >> Hi there!
> >> I just posted a question about my Backup Plan 2005 SQL and I got very
> >> good
> >> answers!, but now, I'm getting other problem, I'm checking the event
> >> viewer
> >> and I see this error message : "BACKUP LOG WITH TRUNCATE_ONLY or WITH
> >> NO_LOG
> >> is deprecated. The simple recovery model should be used to automatically
> >> truncate the transaction log"
> >>
> >> - I'm using Management Studio to configure the backups
> >> - All my databases are Full Recovery Model.
> >> - I run Full Backup of my databases every day. (1:00am)
> >> - I run every 15 minutes backup of the LOG files
> >>
> >> I read something on the MS website (KB 818202) and they say :
> >> "This warning message may be logged because the NO_LOG and TRUNCATE_ONLY
> >> options of the BACKUP statement truncate the transaction log files, and
> >> you
> >> might need transaction logs for the full recovery of the database."
> >>
> >> So, my doubt is:
> >> - The error is because I'm running the backup of the logs every 15
> >> minutes
> >> and THAT is truncating the LOG file, so, what I have to do? When I'v
> >> created
> >> the job on the Management Studio, I havent had the option to set
> >> "NO_LOG"
> >> and "TRUNCATE_ONLY"...'
> >>
> >> Can somebody give me a hand with this' please..
> >> What I really want is recovery the full database in case something
> >> happens...at least the most actual data...
> >>
> >> Thanks you!
> >>
> >> Jose
> >
> >
>
>

Backup error - not part of a multiple family media set

I am new to SQL 2005. I have setup and new maintanaince plan of making backup on to different paths but i encountered an error saying

Executing the query "BACKUP DATABASE [promis_05] TO DISK = N'E:\\ERP Database\\ERP Backup\\Promis_05', DISK = N'\\\\backupsrv\\ERP Backup\\Promis_05' WITH NOFORMAT, INIT, NAME = N'promis_05_backup_20061111181236', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The volume on device 'E:\\ERP Database\\ERP Backup\\Promis_05' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set.
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

We did not know what to make of this.

1) Is it bcoz i am backing up the database on 2 locations same time.

2) What is BACKUP WITH FORMAT?

3) Why won't it let me add a new file that is not part of the 'family' ?

4) How does it get to be part of the family?

Thought and ideas are highly appreciated!

When you back up to two files, you are creating a stripe set. The restriction is that all backups sent to a media set must have the same number of stripes. That's the meaning of your error.

You need to create a media set with the number of stripes you want to use. You can't add members later.

The method for creating a new media set is to use the WITH FORMAT option on your backup command. This is the equivalent of reformatting a tape for backups. It wipes out any previous data in the file(s), and sets up the headers correctly.

So, if you issue the same command in your script, adding WITH FORMAT for ONE TIME ONLY, the first time you use that media family, you should be good to go. From then on, you can just use it as your script is now.

Backup Error

I got an error with a back up maintenance plan: BACKUP failed to
complete the command BACKUP LOG [EAGLE] TO DISK = N'\
\Eaglent1\backuponeaglent1\backup\EAGLE LOG BACKUP' WITH NOINIT ,
NOUNLOAD , NAME = N'EAGLE LOG BACKUP', NOSKIP , STATS = 10,
DESCRIPTION = N'EAGLE LOG BACKUP', NOFORMAT. I also see this error
along with it: The backup data in '\\Eaglent1\backuponeaglent1\backup
\EAGLE LOG BACKUP' is incorrectly formatted. Backups cannot be
appended, but existing backup sets may still be usable.
What do I need to change to get this backup completed?
Joanne Mahoney
SDN Consultants
Jacksonville, FLMy guess is that your file EAGLE LOG BACKUP already exists, and it is not a
sql backup file, and you are appending to it.
If so, taking out any one of those, you should be good.
Quentin
"Joanne M." <joanne.e.mahoney@.gmail.com> wrote in message
news:1190401872.060395.60460@.w3g2000hsg.googlegroups.com...
>I got an error with a back up maintenance plan: BACKUP failed to
> complete the command BACKUP LOG [EAGLE] TO DISK = N'\
> \Eaglent1\backuponeaglent1\backup\EAGLE LOG BACKUP' WITH NOINIT ,
> NOUNLOAD , NAME = N'EAGLE LOG BACKUP', NOSKIP , STATS = 10,
> DESCRIPTION = N'EAGLE LOG BACKUP', NOFORMAT. I also see this error
> along with it: The backup data in '\\Eaglent1\backuponeaglent1\backup
> \EAGLE LOG BACKUP' is incorrectly formatted. Backups cannot be
> appended, but existing backup sets may still be usable.
> What do I need to change to get this backup completed?
> Joanne Mahoney
> SDN Consultants
> Jacksonville, FL
>|||Sorry for my ignorance, but what do you mean by that? Where should I
look? BTW, this is sql 2000
Joanne
On Sep 21, 3:17 pm, "Quentin Ran" <remove_qr...@.yahoo.com> wrote:
> My guess is that your file EAGLE LOG BACKUP already exists, and it is not a
> sql backup file, and you are appending to it.
> If so, taking out any one of those, you should be good.
> Quentin
> "Joanne M." <joanne.e.maho...@.gmail.com> wrote in message
> news:1190401872.060395.60460@.w3g2000hsg.googlegroups.com...
>
> >I got an error with a back up maintenance plan: BACKUP failed to
> > complete the command BACKUP LOG [EAGLE] TO DISK = N'\
> > \Eaglent1\backuponeaglent1\backup\EAGLE LOG BACKUP' WITH NOINIT ,
> > NOUNLOAD , NAME = N'EAGLE LOG BACKUP', NOSKIP , STATS = 10,
> > DESCRIPTION = N'EAGLE LOG BACKUP', NOFORMAT. I also see this error
> > along with it: The backup data in '\\Eaglent1\backuponeaglent1\backup
> > \EAGLE LOG BACKUP' is incorrectly formatted. Backups cannot be
> > appended, but existing backup sets may still be usable.
> > What do I need to change to get this backup completed?
> > Joanne Mahoney
> > SDN Consultants
> > Jacksonville, FL- Hide quoted text -
> - Show quoted text -|||The most simple: if the file is not important (or copy it to somewhere if it
is), delete it and then try again.
"Joanne M." <joanne.e.mahoney@.gmail.com> wrote in message
news:1190402838.559703.52750@.y42g2000hsy.googlegroups.com...
> Sorry for my ignorance, but what do you mean by that? Where should I
> look? BTW, this is sql 2000
> Joanne
>
> On Sep 21, 3:17 pm, "Quentin Ran" <remove_qr...@.yahoo.com> wrote:
>> My guess is that your file EAGLE LOG BACKUP already exists, and it is not
>> a
>> sql backup file, and you are appending to it.
>> If so, taking out any one of those, you should be good.
>> Quentin
>> "Joanne M." <joanne.e.maho...@.gmail.com> wrote in message
>> news:1190401872.060395.60460@.w3g2000hsg.googlegroups.com...
>>
>> >I got an error with a back up maintenance plan: BACKUP failed to
>> > complete the command BACKUP LOG [EAGLE] TO DISK = N'\
>> > \Eaglent1\backuponeaglent1\backup\EAGLE LOG BACKUP' WITH NOINIT ,
>> > NOUNLOAD , NAME = N'EAGLE LOG BACKUP', NOSKIP , STATS = 10,
>> > DESCRIPTION = N'EAGLE LOG BACKUP', NOFORMAT. I also see this error
>> > along with it: The backup data in '\\Eaglent1\backuponeaglent1\backup
>> > \EAGLE LOG BACKUP' is incorrectly formatted. Backups cannot be
>> > appended, but existing backup sets may still be usable.
>> > What do I need to change to get this backup completed?
>> > Joanne Mahoney
>> > SDN Consultants
>> > Jacksonville, FL- Hide quoted text -
>> - Show quoted text -
>

backup error

Configuration: Windows Server 2003, SQL Server 2000 SP3A on xSeries 225 with
1.5 GB RAM
The backup is part of database maintenance plan. Most of the values are set
to default. All time schedules are default.
Transaction log backups are ending normally.
I selected 3 databases for full backup. Backup is on network drive on IBM
server. Full backup of first database always ends with error, and for next
two it finishes normally.
This is the part of the ERRORLOG:
2004-08-22 02:11:45.56 spid54 BackupMedium::ReportIoError: write failure
on backup device
'\\Qs655cb5b\MYDOC\MSSQL_Backup\kpdbnova_db_200408220200.BAK'. Operating
system error 64(error not found).
2004-08-22 02:11:45.56 spid54 Internal I/O request 0x4FCE4C50: Op: Write,
pBuffer: 0x13550000, Size: 983040, Position: 19218432, UMS: Internal: 0x0,
InternalHigh: 0xF0000, Offset: 0x1254000, OffsetHigh: 0x0, m_buf:
0x13550000, m_len: 983040, m_actualBytes: 0, m_errcode: 64, BackupFile:
\\Qs655cb5b\MYDOC\MSSQL_Backup\kpdbnova_db_200408220200.BAK
2004-08-22 02:11:45.56 backup BACKUP failed to complete the command
BACKUP DATABASE [kpdbnova] TO DISK = N'\\Qs655cb5b\MYDOC\MSSQL_Backup\kpdbnova_db_200408220200.BAK' WITH INIT ,
NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
2004-08-22 02:11:45.62 spid54 BackupDiskFile::RequestDurableMedia:
failure on backup device
'\\Qs655cb5b\MYDOC\MSSQL_Backup\kpdbnova_db_200408220200.BAK'. Operating
system error 64(error not found).
--Hi -
The problem here is SQL Server is Writting the Backup file directly onto
the Network Drive. During this time there might be Packet Loss or Network
Slow in which
the Backup job has higher chances of Failing.
The Best Option would be to take the Backup on the Local Disk. And create a
job to copy the Backup job from physical/Local disk to the Network Drive.
Let me know if it works.
Thanks
"D." wrote:
> Configuration: Windows Server 2003, SQL Server 2000 SP3A on xSeries 225 with
> 1.5 GB RAM
> The backup is part of database maintenance plan. Most of the values are set
> to default. All time schedules are default.
> Transaction log backups are ending normally.
> I selected 3 databases for full backup. Backup is on network drive on IBM
> server. Full backup of first database always ends with error, and for next
> two it finishes normally.
> This is the part of the ERRORLOG:
> 2004-08-22 02:11:45.56 spid54 BackupMedium::ReportIoError: write failure
> on backup device
> '\\Qs655cb5b\MYDOC\MSSQL_Backup\kpdbnova_db_200408220200.BAK'. Operating
> system error 64(error not found).
> 2004-08-22 02:11:45.56 spid54 Internal I/O request 0x4FCE4C50: Op: Write,
> pBuffer: 0x13550000, Size: 983040, Position: 19218432, UMS: Internal: 0x0,
> InternalHigh: 0xF0000, Offset: 0x1254000, OffsetHigh: 0x0, m_buf:
> 0x13550000, m_len: 983040, m_actualBytes: 0, m_errcode: 64, BackupFile:
> \\Qs655cb5b\MYDOC\MSSQL_Backup\kpdbnova_db_200408220200.BAK
> 2004-08-22 02:11:45.56 backup BACKUP failed to complete the command
> BACKUP DATABASE [kpdbnova] TO DISK => N'\\Qs655cb5b\MYDOC\MSSQL_Backup\kpdbnova_db_200408220200.BAK' WITH INIT ,
> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
> 2004-08-22 02:11:45.62 spid54 BackupDiskFile::RequestDurableMedia:
> failure on backup device
> '\\Qs655cb5b\MYDOC\MSSQL_Backup\kpdbnova_db_200408220200.BAK'. Operating
> system error 64(error not found).
> --
>
>|||> Hi -
> The problem here is SQL Server is Writting the Backup file directly onto
> the Network Drive. During this time there might be Packet Loss or Network
> Slow in which
> the Backup job has higher chances of Failing.
> The Best Option would be to take the Backup on the Local Disk. And create
> a
> job to copy the Backup job from physical/Local disk to the Network Drive.
> Let me know if it works.
> Thanks
> "D." wrote:
I'll try to do so. Thank you.

backup error

Configuration: Windows Server 2003, SQL Server 2000 SP3A on xSeries 225 with
1.5 GB RAM
The backup is part of database maintenance plan. Most of the values are set
to default. All time schedules are default.
Transaction log backups are ending normally.
I selected 3 databases for full backup. Backup is on network drive on IBM
server. Full backup of first database always ends with error, and for next
two it finishes normally.
This is the part of the ERRORLOG:
2004-08-22 02:11:45.56 spid54 BackupMedium::ReportIoError: write failure
on backup device
'\\Qs655cb5b\MYDOC\MSSQL_Backup\kpdbnova_db_200408 220200.BAK'. Operating
system error 64(error not found).
2004-08-22 02:11:45.56 spid54 Internal I/O request 0x4FCE4C50: Op: Write,
pBuffer: 0x13550000, Size: 983040, Position: 19218432, UMS: Internal: 0x0,
InternalHigh: 0xF0000, Offset: 0x1254000, OffsetHigh: 0x0, m_buf:
0x13550000, m_len: 983040, m_actualBytes: 0, m_errcode: 64, BackupFile:
\\Qs655cb5b\MYDOC\MSSQL_Backup\kpdbnova_db_2004082 20200.BAK
2004-08-22 02:11:45.56 backup BACKUP failed to complete the command
BACKUP DATABASE [kpdbnova] TO DISK =
N'\\Qs655cb5b\MYDOC\MSSQL_Backup\kpdbnova_db_20040 8220200.BAK' WITH INIT ,
NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
2004-08-22 02:11:45.62 spid54 BackupDiskFile::RequestDurableMedia:
failure on backup device
'\\Qs655cb5b\MYDOC\MSSQL_Backup\kpdbnova_db_200408 220200.BAK'. Operating
system error 64(error not found).
Hi -
The problem here is SQL Server is Writting the Backup file directly onto
the Network Drive. During this time there might be Packet Loss or Network
Slow in which
the Backup job has higher chances of Failing.
The Best Option would be to take the Backup on the Local Disk. And create a
job to copy the Backup job from physical/Local disk to the Network Drive.
Let me know if it works.
Thanks
"D." wrote:

> Configuration: Windows Server 2003, SQL Server 2000 SP3A on xSeries 225 with
> 1.5 GB RAM
> The backup is part of database maintenance plan. Most of the values are set
> to default. All time schedules are default.
> Transaction log backups are ending normally.
> I selected 3 databases for full backup. Backup is on network drive on IBM
> server. Full backup of first database always ends with error, and for next
> two it finishes normally.
> This is the part of the ERRORLOG:
> 2004-08-22 02:11:45.56 spid54 BackupMedium::ReportIoError: write failure
> on backup device
> '\\Qs655cb5b\MYDOC\MSSQL_Backup\kpdbnova_db_200408 220200.BAK'. Operating
> system error 64(error not found).
> 2004-08-22 02:11:45.56 spid54 Internal I/O request 0x4FCE4C50: Op: Write,
> pBuffer: 0x13550000, Size: 983040, Position: 19218432, UMS: Internal: 0x0,
> InternalHigh: 0xF0000, Offset: 0x1254000, OffsetHigh: 0x0, m_buf:
> 0x13550000, m_len: 983040, m_actualBytes: 0, m_errcode: 64, BackupFile:
> \\Qs655cb5b\MYDOC\MSSQL_Backup\kpdbnova_db_2004082 20200.BAK
> 2004-08-22 02:11:45.56 backup BACKUP failed to complete the command
> BACKUP DATABASE [kpdbnova] TO DISK =
> N'\\Qs655cb5b\MYDOC\MSSQL_Backup\kpdbnova_db_20040 8220200.BAK' WITH INIT ,
> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
> 2004-08-22 02:11:45.62 spid54 BackupDiskFile::RequestDurableMedia:
> failure on backup device
> '\\Qs655cb5b\MYDOC\MSSQL_Backup\kpdbnova_db_200408 220200.BAK'. Operating
> system error 64(error not found).
> --
>
>
|||> Hi -
> The problem here is SQL Server is Writting the Backup file directly onto
> the Network Drive. During this time there might be Packet Loss or Network
> Slow in which
> the Backup job has higher chances of Failing.
> The Best Option would be to take the Backup on the Local Disk. And create
> a
> job to copy the Backup job from physical/Local disk to the Network Drive.
> Let me know if it works.
> Thanks
> "D." wrote:
I'll try to do so. Thank you.

Thursday, March 22, 2012

backup error

Configuration: Windows Server 2003, SQL Server 2000 SP3A on xSeries 225 with
1.5 GB RAM
The backup is part of database maintenance plan. Most of the values are set
to default. All time schedules are default.
Transaction log backups are ending normally.
I selected 3 databases for full backup. Backup is on network drive on IBM
server. Full backup of first database always ends with error, and for next
two it finishes normally.
This is the part of the ERRORLOG:
2004-08-22 02:11:45.56 spid54 BackupMedium::ReportIoError: write failure
on backup device
'\\Qs655cb5b\MYDOC\MSSQL_Backup\kpdbnova
_db_200408220200.BAK'. Operating
system error 64(error not found).
2004-08-22 02:11:45.56 spid54 Internal I/O request 0x4FCE4C50: Op: Write,
pBuffer: 0x13550000, Size: 983040, Position: 19218432, UMS: Internal: 0x0,
InternalHigh: 0xF0000, Offset: 0x1254000, OffsetHigh: 0x0, m_buf:
0x13550000, m_len: 983040, m_actualBytes: 0, m_errcode: 64, BackupFile:
\\Qs655cb5b\MYDOC\MSSQL_Backup\kpdbnova_
db_200408220200.BAK
2004-08-22 02:11:45.56 backup BACKUP failed to complete the command
BACKUP DATABASE [kpdbnova] TO DISK =
N'\\Qs655cb5b\MYDOC\MSSQL_Backup\kpdbnov
a_db_200408220200.BAK' WITH INIT ,
NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
2004-08-22 02:11:45.62 spid54 BackupDiskFile::RequestDurableMedia:
failure on backup device
'\\Qs655cb5b\MYDOC\MSSQL_Backup\kpdbnova
_db_200408220200.BAK'. Operating
system error 64(error not found).Hi -
The problem here is SQL Server is Writting the Backup file directly onto
the Network Drive. During this time there might be Packet Loss or Network
Slow in which
the Backup job has higher chances of Failing.
The Best Option would be to take the Backup on the Local Disk. And create a
job to copy the Backup job from physical/Local disk to the Network Drive.
Let me know if it works.
Thanks
"D." wrote:

> Configuration: Windows Server 2003, SQL Server 2000 SP3A on xSeries 225 wi
th
> 1.5 GB RAM
> The backup is part of database maintenance plan. Most of the values are se
t
> to default. All time schedules are default.
> Transaction log backups are ending normally.
> I selected 3 databases for full backup. Backup is on network drive on IBM
> server. Full backup of first database always ends with error, and for next
> two it finishes normally.
> This is the part of the ERRORLOG:
> 2004-08-22 02:11:45.56 spid54 BackupMedium::ReportIoError: write failur
e
> on backup device
> '\\Qs655cb5b\MYDOC\MSSQL_Backup\kpdbnova
_db_200408220200.BAK'. Operating
> system error 64(error not found).
> 2004-08-22 02:11:45.56 spid54 Internal I/O request 0x4FCE4C50: Op: Writ
e,
> pBuffer: 0x13550000, Size: 983040, Position: 19218432, UMS: Internal: 0x0,
> InternalHigh: 0xF0000, Offset: 0x1254000, OffsetHigh: 0x0, m_buf:
> 0x13550000, m_len: 983040, m_actualBytes: 0, m_errcode: 64, BackupFile:
> \\Qs655cb5b\MYDOC\MSSQL_Backup\kpdbnova_
db_200408220200.BAK
> 2004-08-22 02:11:45.56 backup BACKUP failed to complete the command
> BACKUP DATABASE [kpdbnova] TO DISK =
> N'\\Qs655cb5b\MYDOC\MSSQL_Backup\kpdbnov
a_db_200408220200.BAK' WITH INIT
,
> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
> 2004-08-22 02:11:45.62 spid54 BackupDiskFile::RequestDurableMedia:
> failure on backup device
> '\\Qs655cb5b\MYDOC\MSSQL_Backup\kpdbnova
_db_200408220200.BAK'. Operating
> system error 64(error not found).
> --
>
>|||> Hi -
> The problem here is SQL Server is Writting the Backup file directly onto
> the Network Drive. During this time there might be Packet Loss or Network
> Slow in which
> the Backup job has higher chances of Failing.
> The Best Option would be to take the Backup on the Local Disk. And create
> a
> job to copy the Backup job from physical/Local disk to the Network Drive.
> Let me know if it works.
> Thanks
> "D." wrote:
I'll try to do so. Thank you.

Backup devices and backup expire

We have a third party application that backs up a sql server database as
part of its overall backup plan. This is hardwired into the application, so
that it must have a backup device with a specific name. I would like to drop
backup sets from the device after a week, so that the backup device does not
grow without bounds. I have played with expiredate/retaindays but this is
not the answer.
Any suggestions would be most appreciated.
Thanks,
John
Unfortunately, you cannot remove part of a backup device, it is all or nothing. An option can be to copy the
physical file at regular intervals over to a new file name and then delete these files as they get too old.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John" <jkraeck@.NOprincetonSPAM.edu> wrote in message news:uol5hQWIEHA.308@.tk2msftngp13.phx.gbl...
> We have a third party application that backs up a sql server database as
> part of its overall backup plan. This is hardwired into the application, so
> that it must have a backup device with a specific name. I would like to drop
> backup sets from the device after a week, so that the backup device does not
> grow without bounds. I have played with expiredate/retaindays but this is
> not the answer.
> Any suggestions would be most appreciated.
> Thanks,
> John
>
|||Tibor,
Thanks for the response. Alas, I thought that might be the case.
I will look into alternate methods of working around the issue. There is
always some way out.
Cheers,
John
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23FnpQhWIEHA.2688@.tk2msftngp13.phx.gbl...
> Unfortunately, you cannot remove part of a backup device, it is all or
nothing. An option can be to copy the
> physical file at regular intervals over to a new file name and then delete
these files as they get too old.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "John" <jkraeck@.NOprincetonSPAM.edu> wrote in message
news:uol5hQWIEHA.308@.tk2msftngp13.phx.gbl...[color=darkblue]
so[color=darkblue]
drop[color=darkblue]
not[color=darkblue]
is
>

Backup devices and backup expire

We have a third party application that backs up a sql server database as
part of its overall backup plan. This is hardwired into the application, so
that it must have a backup device with a specific name. I would like to drop
backup sets from the device after a week, so that the backup device does not
grow without bounds. I have played with expiredate/retaindays but this is
not the answer.
Any suggestions would be most appreciated.
Thanks,
JohnUnfortunately, you cannot remove part of a backup device, it is all or nothi
ng. An option can be to copy the
physical file at regular intervals over to a new file name and then delete t
hese files as they get too old.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John" <jkraeck@.NOprincetonSPAM.edu> wrote in message news:uol5hQWIEHA.308@.tk2msftngp13.phx
.gbl...
> We have a third party application that backs up a sql server database as
> part of its overall backup plan. This is hardwired into the application, s
o
> that it must have a backup device with a specific name. I would like to dr
op
> backup sets from the device after a week, so that the backup device does n
ot
> grow without bounds. I have played with expiredate/retaindays but this is
> not the answer.
> Any suggestions would be most appreciated.
> Thanks,
> John
>|||Tibor,
Thanks for the response. Alas, I thought that might be the case.
I will look into alternate methods of working around the issue. There is
always some way out.
Cheers,
John
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23FnpQhWIEHA.2688@.tk2msftngp13.phx.gbl...
> Unfortunately, you cannot remove part of a backup device, it is all or
nothing. An option can be to copy the
> physical file at regular intervals over to a new file name and then delete
these files as they get too old.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "John" <jkraeck@.NOprincetonSPAM.edu> wrote in message
news:uol5hQWIEHA.308@.tk2msftngp13.phx.gbl...
so
drop
not
is
>

Backup devices and backup expire

We have a third party application that backs up a sql server database as
part of its overall backup plan. This is hardwired into the application, so
that it must have a backup device with a specific name. I would like to drop
backup sets from the device after a week, so that the backup device does not
grow without bounds. I have played with expiredate/retaindays but this is
not the answer.
Any suggestions would be most appreciated.
Thanks,
JohnUnfortunately, you cannot remove part of a backup device, it is all or nothing. An option can be to copy the
physical file at regular intervals over to a new file name and then delete these files as they get too old.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John" <jkraeck@.NOprincetonSPAM.edu> wrote in message news:uol5hQWIEHA.308@.tk2msftngp13.phx.gbl...
> We have a third party application that backs up a sql server database as
> part of its overall backup plan. This is hardwired into the application, so
> that it must have a backup device with a specific name. I would like to drop
> backup sets from the device after a week, so that the backup device does not
> grow without bounds. I have played with expiredate/retaindays but this is
> not the answer.
> Any suggestions would be most appreciated.
> Thanks,
> John
>|||Tibor,
Thanks for the response. Alas, I thought that might be the case.
I will look into alternate methods of working around the issue. There is
always some way out.
Cheers,
John
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23FnpQhWIEHA.2688@.tk2msftngp13.phx.gbl...
> Unfortunately, you cannot remove part of a backup device, it is all or
nothing. An option can be to copy the
> physical file at regular intervals over to a new file name and then delete
these files as they get too old.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "John" <jkraeck@.NOprincetonSPAM.edu> wrote in message
news:uol5hQWIEHA.308@.tk2msftngp13.phx.gbl...
> > We have a third party application that backs up a sql server database as
> > part of its overall backup plan. This is hardwired into the application,
so
> > that it must have a backup device with a specific name. I would like to
drop
> > backup sets from the device after a week, so that the backup device does
not
> > grow without bounds. I have played with expiredate/retaindays but this
is
> > not the answer.
> >
> > Any suggestions would be most appreciated.
> >
> > Thanks,
> > John
> >
> >
>

Tuesday, March 20, 2012

BACKUP db to network machine

Hi all
win 2k pro (on all machines)
sql 2k (1 machine)
in our maintance plan I have the DB backing up every 24 hours - this
backup is into the default BACKUPS folder - on the SAME machine &
drive.
I would also like the maintance plan to back up the DB accross the
network to a 2nd machine (where we keep all our backups of other
files) but in the "backup device" bit it's only listing the c:\ and
not any netword paths...
Q) How can i get it to back up across the network to a 2nd machine in
the maintance plan?
thanks
AlIN SEM simply select backup... then choose Add button and type in the unc
name ie
\\london\sqlshare\mybackup.bak and you will be good ( as long as permissions
allow.)
"Harag" <harag@.softhome.net> wrote in message
news:kd47kvcm6qrpmbda1jes9rk1pm8oqgg5bn@.4ax.com...
> Hi all
> win 2k pro (on all machines)
> sql 2k (1 machine)
> in our maintance plan I have the DB backing up every 24 hours - this
> backup is into the default BACKUPS folder - on the SAME machine &
> drive.
> I would also like the maintance plan to back up the DB accross the
> network to a 2nd machine (where we keep all our backups of other
> files) but in the "backup device" bit it's only listing the c:\ and
> not any netword paths...
> Q) How can i get it to back up across the network to a 2nd machine in
> the maintance plan?
> thanks
> Al|||Harag,
Set up a share in the destination server and use UNC pattern, as in
BACKUP DATABASE <dbname>
TO DISK = '\\destserver\d$\dbbackup.BAK'
That said, I have seen that backing across the network can slow things
down.Do the backup locally and then have some process to copy the file over.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Harag" <harag@.softhome.net> wrote in message
news:kd47kvcm6qrpmbda1jes9rk1pm8oqgg5bn@.4ax.com...
> Hi all
> win 2k pro (on all machines)
> sql 2k (1 machine)
> in our maintance plan I have the DB backing up every 24 hours - this
> backup is into the default BACKUPS folder - on the SAME machine &
> drive.
> I would also like the maintance plan to back up the DB accross the
> network to a 2nd machine (where we keep all our backups of other
> files) but in the "backup device" bit it's only listing the c:\ and
> not any netword paths...
> Q) How can i get it to back up across the network to a 2nd machine in
> the maintance plan?
> thanks
> Al

Monday, March 19, 2012

Backup databases

I'm trying to setup a back up plan for a number of databases, I initially set up one plan to include all user databases which worked fine or so I thought, when I check them a few days later I noticed that some of the databases were not appearing in the backup set, the only way I could get these to appear is to set the comp level to 90, now when we run certain applications we get an error, when I return the comp level back to 70 then the application works fine, is there a reason I can not back up any database on sql 2005 without it being a comp level 90?

Thanks inadvance

I'm guessing that the maintenance plan you've created uses some feature or syntax which didn't exist in SQL 7.0

Look over the TSQL backup commands in your maintenance plan and verify that the syntax there is compatible with SQL 7.0

|||

Hi thanks for the reply, but its not getting that far, stepping through the wizard,first it asks for the database(s) to back up, the choice being all system databases, or specific databases and I don't see any database that has not been set to comp level 90, so no tsql to check.

Thanks

|||

I see that on my system as well.

Is compatibility level 80 an option for you? Databases with that compatibility level do show up in the Wizard.

I'll check on why 70 databases are excluded, but I suspect that it has to do with what was supported at that version.

Ultimately your best option may be to write a backup script yourself.

|||

Yes thanks, not sure why I didn't think of that, but setting to comp level 80 does the trick,, thanks!

|||I have encountered the same problem when I setup the maintenance plan with the maintenance plan Wizard. Any help if I cannot set the comp. level to 80? Please advice, thanks!

Backup Database Maintenance Plan eats up too much disk space

I created a basic DB maintenance plan to routinely backup our database.
Every backup job that runs through this plan is created using a unique name,
putting the date into the back file name, as in
DBProduction_db_200401120000.bak.
The problem is this creates a new backup file each day - our backups our
faily large and after only a few days, the drive space is completely used
up.
What is the best way to solve this problem? Currently, we manually remove
the older backup files. Is there an automated way to have SQL delete files
older than a given number of days - for example, remove all files older than
3 days. When we do a log ship using a maintenance plan, it asks for this
information, but I haven't seen it when just running basic back ups and
transaction log back ups.
What about changing the default name to something like 'DailyDBBackup.bak'
so that it just overwrites previous backup everytime?> What is the best way to solve this problem? Currently, we manually remove

quote:

> the older backup files. Is there an automated way to have SQL delete

files

quote:

> older than a given number of days - for example, remove all files older

than

quote:

> 3 days.

Run through the maint wiz again and you will find just that option :-).
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Dave Slinn" <dslinn@.gms.ca> wrote in message
news:OtxsPCc5DHA.564@.TK2MSFTNGP10.phx.gbl...

quote:

> I created a basic DB maintenance plan to routinely backup our database.
> Every backup job that runs through this plan is created using a unique

name,

quote:

> putting the date into the back file name, as in
> DBProduction_db_200401120000.bak.
> The problem is this creates a new backup file each day - our backups our
> faily large and after only a few days, the drive space is completely used
> up.
> What is the best way to solve this problem? Currently, we manually remove
> the older backup files. Is there an automated way to have SQL delete

files

quote:

> older than a given number of days - for example, remove all files older

than

quote:

> 3 days. When we do a log ship using a maintenance plan, it asks for this
> information, but I haven't seen it when just running basic back ups and
> transaction log back ups.
> What about changing the default name to something like 'DailyDBBackup.bak'
> so that it just overwrites previous backup everytime?
>
>

|||D-oh! I feel so stupid...
Thanks Tibor!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eU$VRHc5DHA.360@.TK2MSFTNGP12.phx.gbl...

quote:

remove[QUOTE]
> files
> than
> Run through the maint wiz again and you will find just that option :-).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>

http://groups.google.com/groups?oi=...ublic.sqlserver

quote:

>
> "Dave Slinn" <dslinn@.gms.ca> wrote in message
> news:OtxsPCc5DHA.564@.TK2MSFTNGP10.phx.gbl...
> name,
used[QUOTE]
remove[QUOTE]
> files
> than
this[QUOTE]
'DailyDBBackup.bak'[QUOTE]
>

Thursday, March 29, 2012

Backup File Deletions not Working

Backup File Deletions not Working

Backup File Deletions not Working

Backup fails with ConnectionRead (WrapperRead()

Tuesday, March 27, 2012

BACKUP failed to complete the command sp_prepexec;1

BACKUP failed to complete the command sp_prepexec;1

Backup fail

Sunday, March 25, 2012

Backup error on Microsoft CMS 2002 SP1A Database

Backup Error - SQL 2005

Backup error - not part of a multiple family media set

Backup Error

backup error

backup error

Thursday, March 22, 2012

backup error

Backup devices and backup expire

Backup devices and backup expire

Backup devices and backup expire

Tuesday, March 20, 2012

BACKUP db to network machine

Monday, March 19, 2012

Backup databases

Backup Database Maintenance Plan eats up too much disk space

Sql Backing up Tables

Blog Archive

About Me