backup file being reported as corrupt

I am backing up my databases every night. The system admin has a process running where he copies any new files on the drive to a tape drive for backup.

Twice now he has told me that a backup file was corrupt. In both cases, my jobs and processes are still running clean. DBCC checkdb returns no errors. Where is the best place / tool to check for corruption in tables?

Corruption was occurring in the backup process and was not related to SQL Server itself.

backup file being reported as corrupt

I am backing up my databases every night. The system admin has a process running where he copies any new files on the drive to a tape drive for backup.

Twice now he has told me that a backup file was corrupt. In both cases, my jobs and processes are still running clean. DBCC checkdb returns no errors. Where is the best place / tool to check for corruption in tables?

Corruption was occurring in the backup process and was not related to SQL Server itself.

Backup failure

All,
I am receiving the following error when I try to backup to tape from SQL using backup under the Management folder in Enterprise manager. Logged in as administrator. Can backup the pubs, Northwind and master databases with no problem.
The database is approx. 8.5GB and the tape can hold 40GB formatted. Using option to overwrite and initialize.
Microsoft SQL-DMO (ODBC SQLState: 42000)
Write on '\\.\Tape0' failed, status = 1117. See the SQL Server error log for more details, backup database is terminating abnormally.
In the error log;
Internal I/O request 0x075CB728: op: write, pBuffer: 0x07d3000, size: 65536, position: 11141120, UMS: internal: 0xC0000185, internalhigh: 0x10000, offset: 0xAA0000, offsethigh: 0x0, m_buf: 0x07d30000, m_len: 65536, m_actualbytes: 65536, m_errcode: 1117, b
ackupfile: \\.\Tape0
Any thought would be appreciated.
Regards,
Dave
can you back it up to disk instead of to tape?
can you backup a large file from disk to tape?
I will do it this way because backup done faster and eliminating the hassle
of dealing with the tape drive.
my 0.02
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:C1CCFEFC-AD93-4715-919F-C468273BD974@.microsoft.com...
> All,
> I am receiving the following error when I try to backup to tape from SQL
using backup under the Management folder in Enterprise manager. Logged in as
administrator. Can backup the pubs, Northwind and master databases with no
problem.
> The database is approx. 8.5GB and the tape can hold 40GB formatted. Using
option to overwrite and initialize.
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> Write on '\\.\Tape0' failed, status = 1117. See the SQL Server error log
for more details, backup database is terminating abnormally.
> In the error log;
> Internal I/O request 0x075CB728: op: write, pBuffer: 0x07d3000, size:
65536, position: 11141120, UMS: internal: 0xC0000185, internalhigh: 0x10000,
offset: 0xAA0000, offsethigh: 0x0, m_buf: 0x07d30000, m_len: 65536,
m_actualbytes: 65536, m_errcode: 1117, backupfile: \\.\Tape0
> Any thought would be appreciated.
> Regards,
> Dave

Backup failure

All,
I am receiving the following error when I try to backup to tape from SQL usi
ng backup under the Management folder in Enterprise manager. Logged in as ad
ministrator. Can backup the pubs, Northwind and master databases with no pro
blem.
The database is approx. 8.5GB and the tape can hold 40GB formatted. Using op
tion to overwrite and initialize.
Microsoft SQL-DMO (ODBC SQLState: 42000)
Write on '\\.\Tape0' failed, status = 1117. See the SQL Server error log for
more details, backup database is terminating abnormally.
In the error log;
Internal I/O request 0x075CB728: op: write, pBuffer: 0x07d3000, size: 65536,
position: 11141120, UMS: internal: 0xC0000185, internalhigh: 0x10000, offse
t: 0xAA0000, offsethigh: 0x0, m_buf: 0x07d30000, m_len: 65536, m_actualbytes
: 65536, m_errcode: 1117, b
ackupfile: \\.\Tape0
Any thought would be appreciated.
Regards,
Davecan you back it up to disk instead of to tape?
can you backup a large file from disk to tape?
I will do it this way because backup done faster and eliminating the hassle
of dealing with the tape drive.
my 0.02
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:C1CCFEFC-AD93-4715-919F-C468273BD974@.microsoft.com...
> All,
> I am receiving the following error when I try to backup to tape from SQL
using backup under the Management folder in Enterprise manager. Logged in as
administrator. Can backup the pubs, Northwind and master databases with no
problem.
> The database is approx. 8.5GB and the tape can hold 40GB formatted. Using
option to overwrite and initialize.
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> Write on '\\.\Tape0' failed, status = 1117. See the SQL Server error log
for more details, backup database is terminating abnormally.
> In the error log;
> Internal I/O request 0x075CB728: op: write, pBuffer: 0x07d3000, size:
65536, position: 11141120, UMS: internal: 0xC0000185, internalhigh: 0x10000,
offset: 0xAA0000, offsethigh: 0x0, m_buf: 0x07d30000, m_len: 65536,
m_actualbytes: 65536, m_errcode: 1117, backupfile: \\.\Tape0
> Any thought would be appreciated.
> Regards,
> Davesql

Backup Error

After applying Win2K SP3 earlier this month, our Veritas
tape backups started failing with the following errors:
BackupMedium::ReportIoError: write failure on backup
device 'VNBU0-2000-2912'. Operating system error 995(The
I/O operation has been aborted because of either a thread
exit or an application request.).
Internal I/O request 0x4431FAE8: Op: Write, pBuffer:
0x28A70200, Size: 512, Position: 0, UMS: Internal:
0x65E5A030, InternalHigh: 0x0, Offset: 0x1, OffsetHigh:
0x4862F628, m_buf: 0x00000012, m_len: 1, m_actualBytes: 0,
m_errcode: 995, BackupFile: VNBU0-2000-2912
I also started getting the following error reported around
the same time of day:
The SQL Server cannot obtain a LOCK resource at this time.
Rerun your statement when there are fewer active users or
ask the system administrator to check the SQL Server lock
and memory configuration..
I am able to do a regular dump to disk with no errors.
Any ideas on resolution here?
Thanks for your time.
MilesI think veritas.com will have more helpful info.
Are you using BackupExec to backup SQL databases?
"Miles" <miles.schor@.echostar.com> wrote in message
news:016b01c36742$ecf70520$a401280a@.phx.gbl...
> After applying Win2K SP3 earlier this month, our Veritas
> tape backups started failing with the following errors:
> BackupMedium::ReportIoError: write failure on backup
> device 'VNBU0-2000-2912'. Operating system error 995(The
> I/O operation has been aborted because of either a thread
> exit or an application request.).
> Internal I/O request 0x4431FAE8: Op: Write, pBuffer:
> 0x28A70200, Size: 512, Position: 0, UMS: Internal:
> 0x65E5A030, InternalHigh: 0x0, Offset: 0x1, OffsetHigh:
> 0x4862F628, m_buf: 0x00000012, m_len: 1, m_actualBytes: 0,
> m_errcode: 995, BackupFile: VNBU0-2000-2912
> I also started getting the following error reported around
> the same time of day:
> The SQL Server cannot obtain a LOCK resource at this time.
> Rerun your statement when there are fewer active users or
> ask the system administrator to check the SQL Server lock
> and memory configuration..
> I am able to do a regular dump to disk with no errors.
> Any ideas on resolution here?
> Thanks for your time.
> Miles

backup database (sql server backup) and files (nt backup)

Hi!
pls. advise, I need to backup some files on hard drive
using NT backup utility and SQL Server database (using sql
server db backups) to one tape. If I backup first some
folders and files with nt backup utility and then sql
server database, the sql server database cannot be
restored ("not a valid sql server backup"). In the other
case (first backed up sql server db then files using nt
backup) I am getting message "inconsistent media, do not
append to this tape"
How can I solve this?
You must use the SQL Server BACKUP command to create a backup file on disk
(or directly to tape) first. Then you can copy this file to tape. You can
not backup the sql files directly with a native NT backup.
Andrew J. Kelly SQL MVP
"Gabriel" <anonymous@.discussions.microsoft.com> wrote in message
news:12d3001c418b3$612a9610$a001280a@.phx.gbl...
> Hi!
> pls. advise, I need to backup some files on hard drive
> using NT backup utility and SQL Server database (using sql
> server db backups) to one tape. If I backup first some
> folders and files with nt backup utility and then sql
> server database, the sql server database cannot be
> restored ("not a valid sql server backup"). In the other
> case (first backed up sql server db then files using nt
> backup) I am getting message "inconsistent media, do not
> append to this tape"
> How can I solve this?

backup database (sql server backup) and files (nt backup)

Hi!
pls. advise, I need to backup some files on hard drive
using NT backup utility and SQL Server database (using sql
server db backups) to one tape. If I backup first some
folders and files with nt backup utility and then sql
server database, the sql server database cannot be
restored ("not a valid sql server backup"). In the other
case (first backed up sql server db then files using nt
backup) I am getting message "inconsistent media, do not
append to this tape"
How can I solve this?You must use the SQL Server BACKUP command to create a backup file on disk
(or directly to tape) first. Then you can copy this file to tape. You can
not backup the sql files directly with a native NT backup.
Andrew J. Kelly SQL MVP
"Gabriel" <anonymous@.discussions.microsoft.com> wrote in message
news:12d3001c418b3$612a9610$a001280a@.phx
.gbl...
> Hi!
> pls. advise, I need to backup some files on hard drive
> using NT backup utility and SQL Server database (using sql
> server db backups) to one tape. If I backup first some
> folders and files with nt backup utility and then sql
> server database, the sql server database cannot be
> restored ("not a valid sql server backup"). In the other
> case (first backed up sql server db then files using nt
> backup) I am getting message "inconsistent media, do not
> append to this tape"
> How can I solve this?

Backup copy_only on 2000

Hi,
We are currently doing weekly full backups and hourly log backups, all to
tape.
However, sometimes we need to move production data to a test system, and
would like to do this using a full backup.
However if we do a full backup, this of course invalidates future
transaction log backups, as they will be based on the off-schedule full
backup.
Is there a way to instruct SQL Server 2000 to create the full backup without
affecting the currently used backup strategy, so future transaction log
backups will still be valid even if I get rid of the temporary full backup
made manually?
Ie:
Full A
Log A
Log B
Log C
Full B
Log D
Log E.
Is there any way to make the Full Backup B, so that a Point in time recovery
can make use of the sequeuce Full A, Log A, Log B, Log C, Log D, Log E?
From what I understand, SQL Server 2005 supports this using the WITH
COPY_ONLY, but this is not available on 2000.
Thanks in advance
Thomas Turn Jensen> However if we do a full backup, this of course invalidates future
> transaction log backups, as they will be based on the off-schedule full
> backup.
No, a full backup does not empty the log, i.e., does not break the log backup chain sequence. So, it
is perfectly possible to skip a full backup during restore, as long as you have an unbroken chain of
log backups since the db backup that you *do* restore.
> From what I understand, SQL Server 2005 supports this using the WITH
> COPY_ONLY, but this is not available on 2000.
COPY_ONLY is not needed for above scenario. Below are the two scenarios where COPY_ONLY is needed:
1. You do diff backups. Somebody want to have a copy of your db through a full backup. If that isn't
taken with COPY_ONLY, the following diff backups would be based on that db backup.
2. You do log backups. Somebody want an up-to-date copy of the database, and do this though a log
backup, without breaking the chain of log backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Thomas Turn Jensen" <ThomasTurnJensen@.discussions.microsoft.com> wrote in message
news:F1BE0189-A225-44C3-8537-118214E131AF@.microsoft.com...
> Hi,
> We are currently doing weekly full backups and hourly log backups, all to
> tape.
> However, sometimes we need to move production data to a test system, and
> would like to do this using a full backup.
> However if we do a full backup, this of course invalidates future
> transaction log backups, as they will be based on the off-schedule full
> backup.
> Is there a way to instruct SQL Server 2000 to create the full backup without
> affecting the currently used backup strategy, so future transaction log
> backups will still be valid even if I get rid of the temporary full backup
> made manually?
> Ie:
> Full A
> Log A
> Log B
> Log C
> Full B
> Log D
> Log E.
> Is there any way to make the Full Backup B, so that a Point in time recovery
> can make use of the sequeuce Full A, Log A, Log B, Log C, Log D, Log E?
> From what I understand, SQL Server 2005 supports this using the WITH
> COPY_ONLY, but this is not available on 2000.
> Thanks in advance
> Thomas Turn Jensen|||Very interesting information, thanks.
The two local vendors we have talked to has told us not to do the
intermittent full backups because they would invalidate the transaction logs.
Thank you very much.
"Tibor Karaszi" wrote:
> > However if we do a full backup, this of course invalidates future
> > transaction log backups, as they will be based on the off-schedule full
> > backup.
> No, a full backup does not empty the log, i.e., does not break the log backup chain sequence. So, it
> is perfectly possible to skip a full backup during restore, as long as you have an unbroken chain of
> log backups since the db backup that you *do* restore.
>
> > From what I understand, SQL Server 2005 supports this using the WITH
> > COPY_ONLY, but this is not available on 2000.
> COPY_ONLY is not needed for above scenario. Below are the two scenarios where COPY_ONLY is needed:
> 1. You do diff backups. Somebody want to have a copy of your db through a full backup. If that isn't
> taken with COPY_ONLY, the following diff backups would be based on that db backup.
> 2. You do log backups. Somebody want an up-to-date copy of the database, and do this though a log
> backup, without breaking the chain of log backups.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Thomas Turn Jensen" <ThomasTurnJensen@.discussions.microsoft.com> wrote in message
> news:F1BE0189-A225-44C3-8537-118214E131AF@.microsoft.com...
> > Hi,
> >
> > We are currently doing weekly full backups and hourly log backups, all to
> > tape.
> >
> > However, sometimes we need to move production data to a test system, and
> > would like to do this using a full backup.
> >
> > However if we do a full backup, this of course invalidates future
> > transaction log backups, as they will be based on the off-schedule full
> > backup.
> >
> > Is there a way to instruct SQL Server 2000 to create the full backup without
> > affecting the currently used backup strategy, so future transaction log
> > backups will still be valid even if I get rid of the temporary full backup
> > made manually?
> >
> > Ie:
> > Full A
> > Log A
> > Log B
> > Log C
> > Full B
> > Log D
> > Log E.
> >
> > Is there any way to make the Full Backup B, so that a Point in time recovery
> > can make use of the sequeuce Full A, Log A, Log B, Log C, Log D, Log E?
> >
> > From what I understand, SQL Server 2005 supports this using the WITH
> > COPY_ONLY, but this is not available on 2000.
> >
> > Thanks in advance
> > Thomas Turn Jensen
>

Backup Agent for SQL Server ?

We perform full backup every night and then copy the BAK files to tape.
The backup vendor suggests us purchasing backup agent for SQL Server. We
would like to know what is the advantage of those agents ?
What will happen if we don't use the backup agent but just copy the MDF file
?
Thanks> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
A SQL Server backup agent provides centralized administration of SQL Server
backups and backup media. A SQL Server backup agent is usually an ad-on to
a general backup solution and can integrate with other backups as well to
facilitate administration and management.
> What will happen if we don't use the backup agent but just copy the MDF
> file ?
Copying database files (mdf, ndf, ldf) directly cannot be done while the SQL
Server service is running. If you copy these without an agent, you have no
integrity and the copies are useless for recovery.
Your current approach (BACKUP DATABASE and copy to tape) is fine and allows
you to quickly restore (assume the disk backup file is available) and also
allows you store tape backups off-site for archive or disaster recovery
purposes. The downside is that you need to have additional disk space
available for the disk backups, although third party tools are available for
backup compression.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:Ouj0YWD0HHA.3772@.TK2MSFTNGP06.phx.gbl...
> We perform full backup every night and then copy the BAK files to tape.
>
> What will happen if we don't use the backup agent but just copy the MDF
> file ?
> Thanks
>|||In article <Ouj0YWD0HHA.3772@.TK2MSFTNGP06.phx.gbl>,
Peter@.discussions.microsoft.com says...
> We perform full backup every night and then copy the BAK files to tape.
> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
> What will happen if we don't use the backup agent but just copy the MDF file
> ?
> Thanks
>
>
Just to add to Dan's comments. We run BackupExec here, but the same
issue applies to any backup program. Databases present a special case
because they are in effect always open, which means that a "normal"
backup will not see them -- it cannot get the exclusive control needed
to allow the backup to happen. So in essence what add-on products such
as Exchange Agent, Sql Agent, Advanced Open File Option (all from
Symantec for Backup Exec) do is to track the changes being made during
the backup and effectively merge them in. Often the Volume Copy Shadow
service is involved. If you look at the messages in the sql log and
agent log from around the time your backup runs, you should see some
events related to databases you did not know even existed that you might
find interesting.
The backup to disk and then to tape works fine. Just be sure to time
coordinate with your backup solution or recovery may get messy
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu

Backup Agent for SQL Server ?

We perform full backup every night and then copy the BAK files to tape.
The backup vendor suggests us purchasing backup agent for SQL Server. We
would like to know what is the advantage of those agents ?
What will happen if we don't use the backup agent but just copy the MDF file
?
Thanks> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
A SQL Server backup agent provides centralized administration of SQL Server
backups and backup media. A SQL Server backup agent is usually an ad-on to
a general backup solution and can integrate with other backups as well to
facilitate administration and management.

> What will happen if we don't use the backup agent but just copy the MDF
> file ?
Copying database files (mdf, ndf, ldf) directly cannot be done while the SQL
Server service is running. If you copy these without an agent, you have no
integrity and the copies are useless for recovery.
Your current approach (BACKUP DATABASE and copy to tape) is fine and allows
you to quickly restore (assume the disk backup file is available) and also
allows you store tape backups off-site for archive or disaster recovery
purposes. The downside is that you need to have additional disk space
available for the disk backups, although third party tools are available for
backup compression.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:Ouj0YWD0HHA.3772@.TK2MSFTNGP06.phx.gbl...
> We perform full backup every night and then copy the BAK files to tape.
>

> What will happen if we don't use the backup agent but just copy the MDF
> file ?
> Thanks
>|||In article <Ouj0YWD0HHA.3772@.TK2MSFTNGP06.phx.gbl>,
Peter@.discussions.microsoft.com says...
> We perform full backup every night and then copy the BAK files to tape.
> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
> What will happen if we don't use the backup agent but just copy the MDF fi
le
> ?
> Thanks
>
>
Just to add to Dan's comments. We run BackupExec here, but the same
issue applies to any backup program. Databases present a special case
because they are in effect always open, which means that a "normal"
backup will not see them -- it cannot get the exclusive control needed
to allow the backup to happen. So in essence what add-on products such
as Exchange Agent, Sql Agent, Advanced Open File Option (all from
Symantec for Backup Exec) do is to track the changes being made during
the backup and effectively merge them in. Often the Volume Copy Shadow
service is involved. If you look at the messages in the sql log and
agent log from around the time your backup runs, you should see some
events related to databases you did not know even existed that you might
find interesting.
The backup to disk and then to tape works fine. Just be sure to time
coordinate with your backup solution or recovery may get messy
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu

Backup Agent for SQL Server

We perform full backup every night and then copy the BAK files to tape.
The backup vendor suggests us purchasing backup agent for SQL Server. We
would like to know what is the advantage of those agents ?
What will happen if we don't use the backup agent but just copy the MDF file
?
ThanksOn Jul 27, 5:20 am, "Peter" <Pe...@.discussions.microsoft.com> wrote:
> We perform full backup every night and then copy the BAK files to tape.
> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
> What will happen if we don't use the backup agent but just copy the MDF fi
le
> ?
> Thanks
We use a third party backup tool that greatly compresses the database,
and thus speeds our backups tremendously. In regards to the backup
agents, one example I can think of, but don't personally have
experience with is the Veritas SQL Backup agent. The only thing I can
tell you is that our backup team decided to uninstall that particular
agent and simply backup the .bak files as you mentioned.

Backup Agent for SQL Server

We perform full backup every night and then copy the BAK files to tape.
The backup vendor suggests us purchasing backup agent for SQL Server. We
would like to know what is the advantage of those agents ?
What will happen if we don't use the backup agent but just copy the MDF file
?
ThanksOn Jul 27, 5:20 am, "Peter" <Pe...@.discussions.microsoft.com> wrote:
> We perform full backup every night and then copy the BAK files to tape.
> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
> What will happen if we don't use the backup agent but just copy the MDF file
> ?
> Thanks
We use a third party backup tool that greatly compresses the database,
and thus speeds our backups tremendously. In regards to the backup
agents, one example I can think of, but don't personally have
experience with is the Veritas SQL Backup agent. The only thing I can
tell you is that our backup team decided to uninstall that particular
agent and simply backup the .bak files as you mentioned.

Backup a DB to different tapes in SQL Server 2000

Hello,
I know That it is possible to backup a DB to different
tapes because tape storage capacity is not enough to save
that DB. Which T-SQL command is used ?
Thank you.You simply start the backup to tape... when the tape is full, you will get a
console message asking you to mount another tape and respond. The response
message will be in the console message that SQL Sends to you.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Belma Canik" <bcanik@.fintek.com.tr> wrote in message
news:08bd01c371f2$4014dfc0$a301280a@.phx.gbl...
> Hello,
> I know That it is possible to backup a DB to different
> tapes because tape storage capacity is not enough to save
> that DB. Which T-SQL command is used ?
> Thank you.
>

Backup

Hi
I am having problem with schedule tape backup in maintenance plan sql server
2005<o:p></o:p>
I have made a schedule for tape backup on Ultrium 2 LTO drive for all
databases. Backup succesfully completes but the tape could not be unloaded
even after pressing the eject button on the tape drive. I could not find any
tape unload/rewind option in the maintenace plan.<o:p></o:p>
Is there any option avilable in sql server 2005 to unload the tape in the
maintenance plan schedule backup, if so pls let me know the same at your
earliest and that will be a great help for me<o:p></o:p>
Thanks with regards
VijayHi
"Vijay" wrote:
> Hi
> I am having problem with schedule tape backup in maintenance plan sql server
> 2005<o:p></o:p>
> I have made a schedule for tape backup on Ultrium 2 LTO drive for all
> databases. Backup succesfully completes but the tape could not be unloaded
> even after pressing the eject button on the tape drive. I could not find any
> tape unload/rewind option in the maintenace plan.<o:p></o:p>
> Is there any option avilable in sql server 2005 to unload the tape in the
> maintenance plan schedule backup, if so pls let me know the same at your
> earliest and that will be a great help for me<o:p></o:p>
> Thanks with regards
> Vijay
>
I don't know of any option, but you could add a job step that did a RESTORE
REWINDONLY at the end of the job. See Books inline for more.
John

Backup

I'm not sure if I'm doing this correctly to ensure recovery in case of a
disaster. I have a SQL 2000 Sp4 server, this server has not tape drive
attached to it. What I'm doing is during the night I run a backup through a
maintenance job in enterprise manager to the BACKUP directory in the SQL file
structure. On another server that has a tape drive I'm backing up this
BACKUP directory to tape. Should I be backing up anything else to ensure
disaster recovery.In summary, ensure that you are backing-up the system databases (there is an
option in the maintenance plan wizard to do this) as well as your user
databases. Make the maintenance plan for the system databases separate from
your plans for the user databases. Also ensure that if you are doing tran log
backups on the databases that have full recovery model, that you have the
backups for the simple model databases in a different plan or you will get an
error in the tran log backup job (even though the tran log backups will
succeed).
If you can backup to a remote server using UNC paths if your mssqlserver
service uses a service account (that has the necessary rights to the file
system on the remote server), that would be preferable. If you cannot
however, then your current backup strategy is adequate.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"George Schneider" wrote:
> I'm not sure if I'm doing this correctly to ensure recovery in case of a
> disaster. I have a SQL 2000 Sp4 server, this server has not tape drive
> attached to it. What I'm doing is during the night I run a backup through a
> maintenance job in enterprise manager to the BACKUP directory in the SQL file
> structure. On another server that has a tape drive I'm backing up this
> BACKUP directory to tape. Should I be backing up anything else to ensure
> disaster recovery.
>|||I currently have all databses under on Maintenance plan. Why is it
neccessary to seperate the system and user db's into two?
"AndyP" wrote:
> In summary, ensure that you are backing-up the system databases (there is an
> option in the maintenance plan wizard to do this) as well as your user
> databases. Make the maintenance plan for the system databases separate from
> your plans for the user databases. Also ensure that if you are doing tran log
> backups on the databases that have full recovery model, that you have the
> backups for the simple model databases in a different plan or you will get an
> error in the tran log backup job (even though the tran log backups will
> succeed).
> If you can backup to a remote server using UNC paths if your mssqlserver
> service uses a service account (that has the necessary rights to the file
> system on the remote server), that would be preferable. If you cannot
> however, then your current backup strategy is adequate.
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "George Schneider" wrote:
> > I'm not sure if I'm doing this correctly to ensure recovery in case of a
> > disaster. I have a SQL 2000 Sp4 server, this server has not tape drive
> > attached to it. What I'm doing is during the night I run a backup through a
> > maintenance job in enterprise manager to the BACKUP directory in the SQL file
> > structure. On another server that has a tape drive I'm backing up this
> > BACKUP directory to tape. Should I be backing up anything else to ensure
> > disaster recovery.
> >
> >|||Your plans will fail in an ungraceful manner if you have database in both full and simple recovery
mode and you try to do log backups in such a plan.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:0C3B4830-8855-45BF-8CC4-84C27E5EF174@.microsoft.com...
>I currently have all databses under on Maintenance plan. Why is it
> neccessary to seperate the system and user db's into two?
> "AndyP" wrote:
>> In summary, ensure that you are backing-up the system databases (there is an
>> option in the maintenance plan wizard to do this) as well as your user
>> databases. Make the maintenance plan for the system databases separate from
>> your plans for the user databases. Also ensure that if you are doing tran log
>> backups on the databases that have full recovery model, that you have the
>> backups for the simple model databases in a different plan or you will get an
>> error in the tran log backup job (even though the tran log backups will
>> succeed).
>> If you can backup to a remote server using UNC paths if your mssqlserver
>> service uses a service account (that has the necessary rights to the file
>> system on the remote server), that would be preferable. If you cannot
>> however, then your current backup strategy is adequate.
>>
>> --
>> AndyP,
>> Sr. Database Administrator,
>> MCDBA 2003
>>
>> "George Schneider" wrote:
>> > I'm not sure if I'm doing this correctly to ensure recovery in case of a
>> > disaster. I have a SQL 2000 Sp4 server, this server has not tape drive
>> > attached to it. What I'm doing is during the night I run a backup through a
>> > maintenance job in enterprise manager to the BACKUP directory in the SQL file
>> > structure. On another server that has a tape drive I'm backing up this
>> > BACKUP directory to tape. Should I be backing up anything else to ensure
>> > disaster recovery.
>> >
>> >

Backup

Hi,
I've received this message when trying to backup the database to a tape.
'"he file on device '\\.\Tape0' is not a valid Microsoft Tape Format backup
set. BACKUP DATABASE is terminating adnormally."
What is the cause & how can it be solve? Please help. Thanks
SayAre you using the tapes for anything other than backing up
from SQL Server to tape? You can get this error when the
tapes have backups other than SQL Server backups on them.
If you are storing other windows backups on the tape, you
would need to do native SQL backups to disk and then backup
these backup files on disk to tape.
-Sue
On Mon, 18 Apr 2005 00:55:01 -0700, Say
<Say@.discussions.microsoft.com> wrote:

>Hi,
> I've received this message when trying to backup the database to a tape.
>'"he file on device '\\.\Tape0' is not a valid Microsoft Tape Format backup
>set. BACKUP DATABASE is terminating adnormally."
> What is the cause & how can it be solve? Please help. Thanks
>Say

Backup

I'm not sure if I'm doing this correctly to ensure recovery in case of a
disaster. I have a SQL 2000 Sp4 server, this server has not tape drive
attached to it. What I'm doing is during the night I run a backup through
a
maintenance job in enterprise manager to the BACKUP directory in the SQL fil
e
structure. On another server that has a tape drive I'm backing up this
BACKUP directory to tape. Should I be backing up anything else to ensure
disaster recovery.In summary, ensure that you are backing-up the system databases (there is an
option in the maintenance plan wizard to do this) as well as your user
databases. Make the maintenance plan for the system databases separate from
your plans for the user databases. Also ensure that if you are doing tran lo
g
backups on the databases that have full recovery model, that you have the
backups for the simple model databases in a different plan or you will get a
n
error in the tran log backup job (even though the tran log backups will
succeed).
If you can backup to a remote server using UNC paths if your mssqlserver
service uses a service account (that has the necessary rights to the file
system on the remote server), that would be preferable. If you cannot
however, then your current backup strategy is adequate.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"George Schneider" wrote:

> I'm not sure if I'm doing this correctly to ensure recovery in case of a
> disaster. I have a SQL 2000 Sp4 server, this server has not tape drive
> attached to it. What I'm doing is during the night I run a backup throug
h a
> maintenance job in enterprise manager to the BACKUP directory in the SQL f
ile
> structure. On another server that has a tape drive I'm backing up this
> BACKUP directory to tape. Should I be backing up anything else to ensure
> disaster recovery.
>|||I currently have all databses under on Maintenance plan. Why is it
neccessary to seperate the system and user db's into two?
"AndyP" wrote:
[vbcol=seagreen]
> In summary, ensure that you are backing-up the system databases (there is
an
> option in the maintenance plan wizard to do this) as well as your user
> databases. Make the maintenance plan for the system databases separate fro
m
> your plans for the user databases. Also ensure that if you are doing tran
log
> backups on the databases that have full recovery model, that you have the
> backups for the simple model databases in a different plan or you will get
an
> error in the tran log backup job (even though the tran log backups will
> succeed).
> If you can backup to a remote server using UNC paths if your mssqlserver
> service uses a service account (that has the necessary rights to the file
> system on the remote server), that would be preferable. If you cannot
> however, then your current backup strategy is adequate.
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "George Schneider" wrote:
>|||Your plans will fail in an ungraceful manner if you have database in both fu
ll and simple recovery
mode and you try to do log backups in such a plan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:0C3B4830-8855-45BF-8CC4-84C27E5EF174@.microsoft.com...[vbcol=seagreen]
>I currently have all databses under on Maintenance plan. Why is it
> neccessary to seperate the system and user db's into two?
> "AndyP" wrote:
>

Backup

Hi
I am having problem with schedule tape backup in maintenance plan sql server
2005<o:p></o:p>
I have made a schedule for tape backup on Ultrium 2 LTO drive for all
databases. Backup succesfully completes but the tape could not be unloaded
even after pressing the eject button on the tape drive. I could not find any
tape unload/rewind option in the maintenace plan.<o:p></o:p>
Is there any option avilable in sql server 2005 to unload the tape in the
maintenance plan schedule backup, if so pls let me know the same at your
earliest and that will be a great help for me<o:p></o:p>
Thanks with regards
VijayHi
"Vijay" wrote:

> Hi
> I am having problem with schedule tape backup in maintenance plan sql serv
er
> 2005<o:p></o:p>
> I have made a schedule for tape backup on Ultrium 2 LTO drive for all
> databases. Backup succesfully completes but the tape could not be unloaded
> even after pressing the eject button on the tape drive. I could not find a
ny
> tape unload/rewind option in the maintenace plan.<o:p></o:p>
> Is there any option avilable in sql server 2005 to unload the tape in the
> maintenance plan schedule backup, if so pls let me know the same at your
> earliest and that will be a great help for me<o:p></o:p>
> Thanks with regards
> Vijay
>
I don't know of any option, but you could add a job step that did a RESTORE
REWINDONLY at the end of the job. See Books inline for more.
John

Backup

Hi
I am having problem with schedule tape backup in maintenance plan sql server
2005<o:p></o:p>
I have made a schedule for tape backup on Ultrium 2 LTO drive for all
databases. Backup succesfully completes but the tape could not be unloaded
even after pressing the eject button on the tape drive. I could not find any
tape unload/rewind option in the maintenace plan.<o:p></o:p>
Is there any option avilable in sql server 2005 to unload the tape in the
maintenance plan schedule backup, if so pls let me know the same at your
earliest and that will be a great help for me<o:p></o:p>
Thanks with regards
Vijay
Hi
"Vijay" wrote:

> Hi
> I am having problem with schedule tape backup in maintenance plan sql server
> 2005<o:p></o:p>
> I have made a schedule for tape backup on Ultrium 2 LTO drive for all
> databases. Backup succesfully completes but the tape could not be unloaded
> even after pressing the eject button on the tape drive. I could not find any
> tape unload/rewind option in the maintenace plan.<o:p></o:p>
> Is there any option avilable in sql server 2005 to unload the tape in the
> maintenance plan schedule backup, if so pls let me know the same at your
> earliest and that will be a great help for me<o:p></o:p>
> Thanks with regards
> Vijay
>
I don't know of any option, but you could add a job step that did a RESTORE
REWINDONLY at the end of the job. See Books inline for more.
John

Backup

I'm not sure if I'm doing this correctly to ensure recovery in case of a
disaster. I have a SQL 2000 Sp4 server, this server has not tape drive
attached to it. What I'm doing is during the night I run a backup through a
maintenance job in enterprise manager to the BACKUP directory in the SQL file
structure. On another server that has a tape drive I'm backing up this
BACKUP directory to tape. Should I be backing up anything else to ensure
disaster recovery.
In summary, ensure that you are backing-up the system databases (there is an
option in the maintenance plan wizard to do this) as well as your user
databases. Make the maintenance plan for the system databases separate from
your plans for the user databases. Also ensure that if you are doing tran log
backups on the databases that have full recovery model, that you have the
backups for the simple model databases in a different plan or you will get an
error in the tran log backup job (even though the tran log backups will
succeed).
If you can backup to a remote server using UNC paths if your mssqlserver
service uses a service account (that has the necessary rights to the file
system on the remote server), that would be preferable. If you cannot
however, then your current backup strategy is adequate.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"George Schneider" wrote:

> I'm not sure if I'm doing this correctly to ensure recovery in case of a
> disaster. I have a SQL 2000 Sp4 server, this server has not tape drive
> attached to it. What I'm doing is during the night I run a backup through a
> maintenance job in enterprise manager to the BACKUP directory in the SQL file
> structure. On another server that has a tape drive I'm backing up this
> BACKUP directory to tape. Should I be backing up anything else to ensure
> disaster recovery.
>
|||I currently have all databses under on Maintenance plan. Why is it
neccessary to seperate the system and user db's into two?
"AndyP" wrote:
[vbcol=seagreen]
> In summary, ensure that you are backing-up the system databases (there is an
> option in the maintenance plan wizard to do this) as well as your user
> databases. Make the maintenance plan for the system databases separate from
> your plans for the user databases. Also ensure that if you are doing tran log
> backups on the databases that have full recovery model, that you have the
> backups for the simple model databases in a different plan or you will get an
> error in the tran log backup job (even though the tran log backups will
> succeed).
> If you can backup to a remote server using UNC paths if your mssqlserver
> service uses a service account (that has the necessary rights to the file
> system on the remote server), that would be preferable. If you cannot
> however, then your current backup strategy is adequate.
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "George Schneider" wrote:
|||Your plans will fail in an ungraceful manner if you have database in both full and simple recovery
mode and you try to do log backups in such a plan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:0C3B4830-8855-45BF-8CC4-84C27E5EF174@.microsoft.com...[vbcol=seagreen]
>I currently have all databses under on Maintenance plan. Why is it
> neccessary to seperate the system and user db's into two?
> "AndyP" wrote: