sql backup files: windows

Showing posts with label windows. Show all posts

Tuesday, March 27, 2012

Backup SQL server using msbackup

Guys, I want to be able to use MSBackup to create a backup of my Windows
2000 server with SQL 2000 server running. I need to be able to include the
SQL databases etc in this backup but obviously the files are open when the
SQL server is running and can not be backed up so I need to stop the service
before the backup and restart it after. Can anyone tell me how best to do
this? I do not want to have to install any other applications on this server
so I am hoping that the various Windows default programs will alow me to
achive this goal.
Thanks for your help with this.Gerry
For SQL Server you will better of to use BACKUP command of T-SQL
"Gerry Armstrong" <gerrya@.hotmail.com> wrote in message
news:120bh7gpkhdsbc5@.corp.supernews.com...
> Guys, I want to be able to use MSBackup to create a backup of my Windows
> 2000 server with SQL 2000 server running. I need to be able to include the
> SQL databases etc in this backup but obviously the files are open when the
> SQL server is running and can not be backed up so I need to stop the
> service before the backup and restart it after. Can anyone tell me how
> best to do this? I do not want to have to install any other applications
> on this server so I am hoping that the various Windows default programs
> will alow me to achive this goal.
> Thanks for your help with this.
>|||Uri, thanks for the response but I do not know much about SQL so this is a
bit over my head, can you explain a bit more what you mean by that? I assume
you are referring to a SQL command and therefore I will have to create a
script of some sort... I want to use MSBackup to create a single source of
recovery for the entire server inclusive of the SQL databases. I was hoping
to create a schedule that would stop the SQL server, start the MSBackup,
then start the SQL server again after the backup is complete. Any
suggetsions on this? Maybe I could use batch files?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O1YrqnUPGHA.812@.TK2MSFTNGP10.phx.gbl...
> Gerry
> For SQL Server you will better of to use BACKUP command of T-SQL
>
> "Gerry Armstrong" <gerrya@.hotmail.com> wrote in message
> news:120bh7gpkhdsbc5@.corp.supernews.com...
>> Guys, I want to be able to use MSBackup to create a backup of my Windows
>> 2000 server with SQL 2000 server running. I need to be able to include
>> the SQL databases etc in this backup but obviously the files are open
>> when the SQL server is running and can not be backed up so I need to stop
>> the service before the backup and restart it after. Can anyone tell me
>> how best to do this? I do not want to have to install any other
>> applications on this server so I am hoping that the various Windows
>> default programs will alow me to achive this goal.
>> Thanks for your help with this.
>|||MSBackup cannot back up a working SQL Server by itself. More importantly,
it cannot restore a working SQL Server by itself. It cannot get a
consistent picture of the multiple files that comprise a SQL database with
the system working. SQL includes a utility to create a transactionally
consistant self-describing backup that can be stored on one or more disk
files. Typical best practice is to back up the SQL Server to a disk (local
or network) and then back up those files to some external archive system
such as a tape library.
If you are responsible for restoring the SQL host sytem and database engine
in case of failure or disaster, I strongly suggest you read the section in
BOL (Books-On-Line) on Backuping Up and Restoring Databases (It's under
"Administering SQL Server" in the TOC). SQL backup and recovery is
different from ordinary file-system type restore. Expecting SQL to recover
just like a non-SQL system will lead to failure, disappointment, and
potential unemployment.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Gerry Armstrong" <gerrya@.hotmail.com> wrote in message
news:120bk4crsfj5s9b@.corp.supernews.com...
> Uri, thanks for the response but I do not know much about SQL so this is a
> bit over my head, can you explain a bit more what you mean by that? I
> assume you are referring to a SQL command and therefore I will have to
> create a script of some sort... I want to use MSBackup to create a single
> source of recovery for the entire server inclusive of the SQL databases. I
> was hoping to create a schedule that would stop the SQL server, start the
> MSBackup, then start the SQL server again after the backup is complete.
> Any suggetsions on this? Maybe I could use batch files?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O1YrqnUPGHA.812@.TK2MSFTNGP10.phx.gbl...
>> Gerry
>> For SQL Server you will better of to use BACKUP command of T-SQL
>>
>> "Gerry Armstrong" <gerrya@.hotmail.com> wrote in message
>> news:120bh7gpkhdsbc5@.corp.supernews.com...
>> Guys, I want to be able to use MSBackup to create a backup of my Windows
>> 2000 server with SQL 2000 server running. I need to be able to include
>> the SQL databases etc in this backup but obviously the files are open
>> when the SQL server is running and can not be backed up so I need to
>> stop the service before the backup and restart it after. Can anyone tell
>> me how best to do this? I do not want to have to install any other
>> applications on this server so I am hoping that the various Windows
>> default programs will alow me to achive this goal.
>> Thanks for your help with this.
>>
>|||Gerry Armstrong wrote:
> Guys, I want to be able to use MSBackup to create a backup of my Windows
> 2000 server with SQL 2000 server running. I need to be able to include the
> SQL databases etc in this backup but obviously the files are open when the
> SQL server is running and can not be backed up so I need to stop the service
> before the backup and restart it after. Can anyone tell me how best to do
> this? I do not want to have to install any other applications on this server
> so I am hoping that the various Windows default programs will alow me to
> achive this goal.
> Thanks for your help with this.
>
Hi Gerry
if you really need to backup using msbackup, and assuming that you've
got a default instance, run:
net stop sqlserveragent
net stop mssqlserver
to stop, and net start will get them back up and running again.|||Ok, judging from these responses I will be suggesting to the customer that
they purchase a backup product that will include the SQL agent. The main
issue here is that the customer is small and there is no-one onsight with
enough knowledge to be able to do anything technical so I am trying to find
the simplest most straightforward way of providing them with a backup that
will also be the easiest means of disaster recovery. I assumed that if the
SQL was not running and the server could access all the files that I would
be able to do a full backup and in the event of failure do a full restore
and the databases will work exactly as they were before the backup was
started but maybe my assumption is wrong like most assumptions.....
"Tim Purcell" <tim.purcell@.mungos.f2s.com> wrote in message
news:du4n3t$1gq$1@.news.freedom2surf.net...
> Gerry Armstrong wrote:
>> Guys, I want to be able to use MSBackup to create a backup of my Windows
>> 2000 server with SQL 2000 server running. I need to be able to include
>> the SQL databases etc in this backup but obviously the files are open
>> when the SQL server is running and can not be backed up so I need to stop
>> the service before the backup and restart it after. Can anyone tell me
>> how best to do this? I do not want to have to install any other
>> applications on this server so I am hoping that the various Windows
>> default programs will alow me to achive this goal.
>> Thanks for your help with this.
>
> Hi Gerry
> if you really need to backup using msbackup, and assuming that you've got
> a default instance, run:
> net stop sqlserveragent
> net stop mssqlserver
> to stop, and net start will get them back up and running again.|||In theory that should work. But I'd never trust that. Create SQL Server Agent jobs for your clients
(possibly using the Database Maintenance Wizard) which backup the database to files and then pick up
those files using ntbackup.
You can of course safeguard with *also* stopping SQL Server and grab the raw database file. Restore
from that is *likely* to work, and if it doesn't, you have the SQL server backups as a fall-back.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gerry Armstrong" <gerrya@.hotmail.com> wrote in message news:120bs57po9qave9@.corp.supernews.com...
> Ok, judging from these responses I will be suggesting to the customer that they purchase a backup
> product that will include the SQL agent. The main issue here is that the customer is small and
> there is no-one onsight with enough knowledge to be able to do anything technical so I am trying
> to find the simplest most straightforward way of providing them with a backup that will also be
> the easiest means of disaster recovery. I assumed that if the SQL was not running and the server
> could access all the files that I would be able to do a full backup and in the event of failure do
> a full restore and the databases will work exactly as they were before the backup was started but
> maybe my assumption is wrong like most assumptions.....
> "Tim Purcell" <tim.purcell@.mungos.f2s.com> wrote in message
> news:du4n3t$1gq$1@.news.freedom2surf.net...
>> Gerry Armstrong wrote:
>> Guys, I want to be able to use MSBackup to create a backup of my Windows 2000 server with SQL
>> 2000 server running. I need to be able to include the SQL databases etc in this backup but
>> obviously the files are open when the SQL server is running and can not be backed up so I need
>> to stop the service before the backup and restart it after. Can anyone tell me how best to do
>> this? I do not want to have to install any other applications on this server so I am hoping that
>> the various Windows default programs will alow me to achive this goal.
>> Thanks for your help with this.
>>
>> Hi Gerry
>> if you really need to backup using msbackup, and assuming that you've got a default instance,
>> run:
>> net stop sqlserveragent
>> net stop mssqlserver
>> to stop, and net start will get them back up and running again.
>|||As Tibor said, I wouldn't trust a quiesced system to be restorable. I also
do not recommend purchasing a third-party SQL backup solution with a SQL
agent. I do recommend reading about the system you are giving advice on.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Gerry Armstrong" <gerrya@.hotmail.com> wrote in message
news:120bs57po9qave9@.corp.supernews.com...
> Ok, judging from these responses I will be suggesting to the customer that
> they purchase a backup product that will include the SQL agent. The main
> issue here is that the customer is small and there is no-one onsight with
> enough knowledge to be able to do anything technical so I am trying to
> find the simplest most straightforward way of providing them with a backup
> that will also be the easiest means of disaster recovery. I assumed that
> if the SQL was not running and the server could access all the files that
> I would be able to do a full backup and in the event of failure do a full
> restore and the databases will work exactly as they were before the backup
> was started but maybe my assumption is wrong like most assumptions.....
> "Tim Purcell" <tim.purcell@.mungos.f2s.com> wrote in message
> news:du4n3t$1gq$1@.news.freedom2surf.net...
>> Gerry Armstrong wrote:
>> Guys, I want to be able to use MSBackup to create a backup of my Windows
>> 2000 server with SQL 2000 server running. I need to be able to include
>> the SQL databases etc in this backup but obviously the files are open
>> when the SQL server is running and can not be backed up so I need to
>> stop the service before the backup and restart it after. Can anyone tell
>> me how best to do this? I do not want to have to install any other
>> applications on this server so I am hoping that the various Windows
>> default programs will alow me to achive this goal.
>> Thanks for your help with this.
>>
>> Hi Gerry
>> if you really need to backup using msbackup, and assuming that you've got
>> a default instance, run:
>> net stop sqlserveragent
>> net stop mssqlserver
>> to stop, and net start will get them back up and running again.
>|||Ok Guys, I take all your suggestions under advisment... guess I got more
reading to do as if I don't have enough going on in my head as yet.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ecr9qSWPGHA.2624@.TK2MSFTNGP12.phx.gbl...
> In theory that should work. But I'd never trust that. Create SQL Server
> Agent jobs for your clients (possibly using the Database Maintenance
> Wizard) which backup the database to files and then pick up those files
> using ntbackup.
> You can of course safeguard with *also* stopping SQL Server and grab the
> raw database file. Restore from that is *likely* to work, and if it
> doesn't, you have the SQL server backups as a fall-back.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Gerry Armstrong" <gerrya@.hotmail.com> wrote in message
> news:120bs57po9qave9@.corp.supernews.com...
>> Ok, judging from these responses I will be suggesting to the customer
>> that they purchase a backup product that will include the SQL agent. The
>> main issue here is that the customer is small and there is no-one onsight
>> with enough knowledge to be able to do anything technical so I am trying
>> to find the simplest most straightforward way of providing them with a
>> backup that will also be the easiest means of disaster recovery. I
>> assumed that if the SQL was not running and the server could access all
>> the files that I would be able to do a full backup and in the event of
>> failure do a full restore and the databases will work exactly as they
>> were before the backup was started but maybe my assumption is wrong like
>> most assumptions.....
>> "Tim Purcell" <tim.purcell@.mungos.f2s.com> wrote in message
>> news:du4n3t$1gq$1@.news.freedom2surf.net...
>> Gerry Armstrong wrote:
>> Guys, I want to be able to use MSBackup to create a backup of my
>> Windows 2000 server with SQL 2000 server running. I need to be able to
>> include the SQL databases etc in this backup but obviously the files
>> are open when the SQL server is running and can not be backed up so I
>> need to stop the service before the backup and restart it after. Can
>> anyone tell me how best to do this? I do not want to have to install
>> any other applications on this server so I am hoping that the various
>> Windows default programs will alow me to achive this goal.
>> Thanks for your help with this.
>>
>> Hi Gerry
>> if you really need to backup using msbackup, and assuming that you've
>> got a default instance, run:
>> net stop sqlserveragent
>> net stop mssqlserver
>> to stop, and net start will get them back up and running again.
>>
>

Backup SQL Files

Hi,

We are about to install MSSQL Server 2000, on a Windows XP Home
Machine. However, we have servers we could set routine backups of files
to be done to. What what be the best way of doing this?

Is there functionality in SQL Server 2000, where we can say dump all
data definitions, accounts, and data to files on this drive at regular
intervals?

What other suggestions do you have apart from obviously the usual RAID,
and Tape Drive stuff?

Thanks

DavidDavid (david.goodyear@.gmail.com) writes:
> We are about to install MSSQL Server 2000, on a Windows XP Home
> Machine. However, we have servers we could set routine backups of files
> to be done to. What what be the best way of doing this?
> Is there functionality in SQL Server 2000, where we can say dump all
> data definitions, accounts, and data to files on this drive at regular
> intervals?

You could set up a job that runs from SQL Server Agent that backups
the database to a disk somewhere using the BACKUP command.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||What "flavour" of SQL Server 2000 (e.g. Enterprise, Developer) will you
be installing on this machine? I only ask because certain flavours
won't let you install the server components on an XP machine...

Anyway, you can use the Database Maintenance Plan Wizard to set up a
regular full backup of a database. This will preserve all the data in
the database (including the schema), objects such as functions and
stored procedures and the user accounts you have defined in the
database.

In addition to backing up any user defined databases, you should also
consider doing a backup of the system databases (master, msdb, model)
via the Database Maintenance Plan Wizard. Doing this should allow you
to have a backup of the server logins, SQL Server Agent jobs,
maintenance plans etc.

Hope that helps a bit

Backup Sql database to mapped network drive on another server prob

Until recently (stopped working this week) I have been able to backup my sql
database (on a windows 2000 server) to a mapped network drive(mapped to
another 2000 server). I am a user on both servers (administrator). I can see
the the drive in explorer. I can access the files.
I cannot see the drive in enterprise manager. Can someone help? Thanks.
> I cannot see the drive in enterprise manager. Can someone help? Thanks.
Backup to a network drive must be done with T-SQL Backup command (check it
in Books OnLine). Use UNC names, not mapped drives.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||I understand that's one way to do it and I appreciate your answer. As I said
earlier, I was backing up to a network drive manually and through the
maintenance. I have been doing this for the last 6 months. Something got
change and I don't know where to start. Any suggestions as to what I can take
a look would be appreciated.
"BarbS" wrote:

> Until recently (stopped working this week) I have been able to backup my sql
> database (on a windows 2000 server) to a mapped network drive(mapped to
> another 2000 server). I am a user on both servers (administrator). I can see
> the the drive in explorer. I can access the files.
> I cannot see the drive in enterprise manager. Can someone help? Thanks.
|||It's not just one way to do it , it is the recommended way to do it. What
you see as a user means nothing unless that account is the same one that SQL
Server runs under. It is that account that does the backup regardless of
who initiates it or how they initiate the backup. Mapped drives can easily
get blown away or become unusable when changes are made on either end. So
if you want to continue using a mapped drive (which no one will recommend)
you need to log on to the server (database server) under the account that
sql server is running under and make sure the drive is mapped properly and
it still has the correct permissions to see and write to that share. If not
then the backup can not work properly.
Andrew J. Kelly SQL MVP
"BarbS" <BarbS@.discussions.microsoft.com> wrote in message
news:4FFF5A0E-3592-428D-A732-58378275F3D2@.microsoft.com...[vbcol=seagreen]
>I understand that's one way to do it and I appreciate your answer. As I
>said
> earlier, I was backing up to a network drive manually and through the
> maintenance. I have been doing this for the last 6 months. Something got
> change and I don't know where to start. Any suggestions as to what I can
> take
> a look would be appreciated.
>
> "BarbS" wrote:

Backup Sql database to mapped network drive on another server prob

Until recently (stopped working this week) I have been able to backup my sql
database (on a Windows 2000 server) to a mapped network drive(mapped to
another 2000 server). I am a user on both servers (administrator). I can see
the the drive in explorer. I can access the files.
I cannot see the drive in enterprise manager. Can someone help? Thanks.> I cannot see the drive in enterprise manager. Can someone help? Thanks.
Backup to a network drive must be done with T-SQL Backup command (check it
in Books OnLine). Use UNC names, not mapped drives.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||I understand that's one way to do it and I appreciate your answer. As I said
earlier, I was backing up to a network drive manually and through the
maintenance. I have been doing this for the last 6 months. Something got
change and I don't know where to start. Any suggestions as to what I can tak
e
a look would be appreciated.
"BarbS" wrote:

> Until recently (stopped working this week) I have been able to backup my s
ql
> database (on a Windows 2000 server) to a mapped network drive(mapped to
> another 2000 server). I am a user on both servers (administrator). I can s
ee
> the the drive in explorer. I can access the files.
> I cannot see the drive in enterprise manager. Can someone help? Thanks.|||It's not just one way to do it , it is the recommended way to do it. What
you see as a user means nothing unless that account is the same one that SQL
Server runs under. It is that account that does the backup regardless of
who initiates it or how they initiate the backup. Mapped drives can easily
get blown away or become unusable when changes are made on either end. So
if you want to continue using a mapped drive (which no one will recommend)
you need to log on to the server (database server) under the account that
sql server is running under and make sure the drive is mapped properly and
it still has the correct permissions to see and write to that share. If not
then the backup can not work properly.
Andrew J. Kelly SQL MVP
"BarbS" <BarbS@.discussions.microsoft.com> wrote in message
news:4FFF5A0E-3592-428D-A732-58378275F3D2@.microsoft.com...[vbcol=seagreen]
>I understand that's one way to do it and I appreciate your answer. As I
>said
> earlier, I was backing up to a network drive manually and through the
> maintenance. I have been doing this for the last 6 months. Something got
> change and I don't know where to start. Any suggestions as to what I can
> take
> a look would be appreciated.
>
> "BarbS" wrote:
>

Tuesday, March 20, 2012

Backup Restore question

Hi,
SQL Server 2000 SP3 running on Windows 2000 server
Current Backups:
Daily full backup at 10 PM done by Veritas Netbackup
New Backups that will be setup:
Daily Full backup at 3 AM in the morning
Transaction Log backups every hour.
Now my question is, during a restore from the full backup done at 3 AM how
would the restore from transaction logs be affected by the full backup taking
place at 10 PM?
Thanks in advance.You cannot do a restore to a database that is having a backup done at the
same time - and why would you?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:5C8C825F-D498-48ED-A143-4EAD1FB0066C@.microsoft.com...
Hi,
SQL Server 2000 SP3 running on Windows 2000 server
Current Backups:
Daily full backup at 10 PM done by Veritas Netbackup
New Backups that will be setup:
Daily Full backup at 3 AM in the morning
Transaction Log backups every hour.
Now my question is, during a restore from the full backup done at 3 AM how
would the restore from transaction logs be affected by the full backup
taking
place at 10 PM?
Thanks in advance.|||Also, you will have to the use the previous full backup if you want to
restore the 10 p.m. transaction log backup
Ash
"sharman" wrote:
> Hi,
> SQL Server 2000 SP3 running on Windows 2000 server
> Current Backups:
> Daily full backup at 10 PM done by Veritas Netbackup
> New Backups that will be setup:
> Daily Full backup at 3 AM in the morning
> Transaction Log backups every hour.
> Now my question is, during a restore from the full backup done at 3 AM how
> would the restore from transaction logs be affected by the full backup taking
> place at 10 PM?
> Thanks in advance.
>|||Sorry for not making myself clear. I want to know if I start the new full
backup and the new hourly transaction log backup would I be able to restore
at 11PM with just the 3AM full backup and then restoring the transaction logs
in sequence until 11 PM and just IGNORING the 10 PM full backup.
The reason I ask is because many times during the testing to restore from
the 10 PM backups (that is done by the third party software), I get an error
message and I do not trust that full backup. Therefore I want to set up these
new backups through Enterprise Manager( I have done it this way earlier and I
find them very trustworthy)
Thanks.
"Tom Moreau" wrote:
> You cannot do a restore to a database that is having a backup done at the
> same time - and why would you?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:5C8C825F-D498-48ED-A143-4EAD1FB0066C@.microsoft.com...
> Hi,
> SQL Server 2000 SP3 running on Windows 2000 server
> Current Backups:
> Daily full backup at 10 PM done by Veritas Netbackup
> New Backups that will be setup:
> Daily Full backup at 3 AM in the morning
> Transaction Log backups every hour.
> Now my question is, during a restore from the full backup done at 3 AM how
> would the restore from transaction logs be affected by the full backup
> taking
> place at 10 PM?
> Thanks in advance.
>
>|||Yes, you don't have to use the most recent full backup. You can use a
previous full backup and then all of the logs taken after that point in
time.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:F39EAE37-615B-4AF7-BA67-13C56F34AB0B@.microsoft.com...
Sorry for not making myself clear. I want to know if I start the new full
backup and the new hourly transaction log backup would I be able to restore
at 11PM with just the 3AM full backup and then restoring the transaction
logs
in sequence until 11 PM and just IGNORING the 10 PM full backup.
The reason I ask is because many times during the testing to restore from
the 10 PM backups (that is done by the third party software), I get an error
message and I do not trust that full backup. Therefore I want to set up
these
new backups through Enterprise Manager( I have done it this way earlier and
I
find them very trustworthy)
Thanks.
"Tom Moreau" wrote:
> You cannot do a restore to a database that is having a backup done at the
> same time - and why would you?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:5C8C825F-D498-48ED-A143-4EAD1FB0066C@.microsoft.com...
> Hi,
> SQL Server 2000 SP3 running on Windows 2000 server
> Current Backups:
> Daily full backup at 10 PM done by Veritas Netbackup
> New Backups that will be setup:
> Daily Full backup at 3 AM in the morning
> Transaction Log backups every hour.
> Now my question is, during a restore from the full backup done at 3 AM
> how
> would the restore from transaction logs be affected by the full backup
> taking
> place at 10 PM?
> Thanks in advance.
>
>

Monday, March 19, 2012

Backup procedure

Hello there!
I have SQLServer 2000 running on Windows 2000 advanced server.
I want to schedule daily full backup.
Do I have to stop SQLServer service before it starts backup?
Or can users have full access to the database while it's doing the backup?
Thanks in advanceRead the chapter on Backup and Restore in BOL (Books On-Line). This will
explain some of the unique considerations and options for backing up a SQL
server installation.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rexford" <anonymous@.discussions.microsoft.com> wrote in message
news:B5E24755-6290-44CD-9FC2-99309FB5C9D4@.microsoft.com...
> Hello there!
> I have SQLServer 2000 running on Windows 2000 advanced server.
> I want to schedule daily full backup.
> Do I have to stop SQLServer service before it starts backup?
> Or can users have full access to the database while it's doing the backup?
> Thanks in advance|||Hi,
It is not at all required to shutdown SQL server while doing a full database
backup. But try to perform the full database backup during the time zone
where user access is less.
Thanks
Hari
MCDBA
"Rexford" <anonymous@.discussions.microsoft.com> wrote in message
news:B5E24755-6290-44CD-9FC2-99309FB5C9D4@.microsoft.com...
> Hello there!
> I have SQLServer 2000 running on Windows 2000 advanced server.
> I want to schedule daily full backup.
> Do I have to stop SQLServer service before it starts backup?
> Or can users have full access to the database while it's doing the backup?
> Thanks in advance

Backup procedure

Hello there
I have SQLServer 2000 running on Windows 2000 advanced server
I want to schedule daily full backup
Do I have to stop SQLServer service before it starts backup
Or can users have full access to the database while it's doing the backup
Thanks in advanceRead the chapter on Backup and Restore in BOL (Books On-Line). This will
explain some of the unique considerations and options for backing up a SQL
server installation.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rexford" <anonymous@.discussions.microsoft.com> wrote in message
news:B5E24755-6290-44CD-9FC2-99309FB5C9D4@.microsoft.com...
> Hello there!
> I have SQLServer 2000 running on Windows 2000 advanced server.
> I want to schedule daily full backup.
> Do I have to stop SQLServer service before it starts backup?
> Or can users have full access to the database while it's doing the backup?
> Thanks in advance|||Hi,
It is not at all required to shutdown SQL server while doing a full database
backup. But try to perform the full database backup during the time zone
where user access is less.
Thanks
Hari
MCDBA
"Rexford" <anonymous@.discussions.microsoft.com> wrote in message
news:B5E24755-6290-44CD-9FC2-99309FB5C9D4@.microsoft.com...
> Hello there!
> I have SQLServer 2000 running on Windows 2000 advanced server.
> I want to schedule daily full backup.
> Do I have to stop SQLServer service before it starts backup?
> Or can users have full access to the database while it's doing the backup?
> Thanks in advance

Sunday, March 11, 2012

Backup Problem

Hello Everyone,
We were moving a 2-node DB cluster (Active/Active on SQL 2000 Ent /
Windows 2003 Ent) from one subnet to another. Everything went well
(apparently) until we realized that our backups are failing (both the
existing jobs and manual backups) after the change in ip/subnet was
done. All backup attempts return with errrors similar to these:
=== 18204 :
BackupDiskFile::OpenMedia: Backup device 'F:\temp\database_abc_041005'
failed to open. Operating system error = 32(The process cannot access
the file because it is being used by another process.).
=== 3041 :
BACKUP failed to complete the command BACKUP DATABASE [database_abcr]
TO DISK = N'F:\temp\database_abc_041005' WITH NOINIT , NOUNLOAD ,
NAME = N'database_abc backup', NOSKIP , STATS = 10, NOFORMAT
===
When the manual backup is initiated without Verification, no (popup)
error is generated (but the error above is still logged on the event
log) and the backup says it ran successfully. When the backup file is
checked, the size is Zero (0) kb.
There is no apparent problem in connecting to both the two instances
of the server and applications are working okay, it's just the backup
problem that is bugging us.
drive Q = quorum
drive F = one of the shared drives with 20Gb+ remaining
The database that we are attempting to backup is just about 11Gb in
size.
Any help is appreciated.
Thanks.
AramidJust a quick question - were you backing up to a shared network drive
previously? What if you try backing up to the fully qualified UNC Path
instead?
"aramid" <aramid@.hotmail.com> wrote in message
news:s4rj511nm4jrf8at18grbsnio4jseoc7t8@.4ax.com...
> Hello Everyone,
> We were moving a 2-node DB cluster (Active/Active on SQL 2000 Ent /
> Windows 2003 Ent) from one subnet to another. Everything went well
> (apparently) until we realized that our backups are failing (both the
> existing jobs and manual backups) after the change in ip/subnet was
> done. All backup attempts return with errrors similar to these:
> ===> 18204 :
> BackupDiskFile::OpenMedia: Backup device 'F:\temp\database_abc_041005'
> failed to open. Operating system error = 32(The process cannot access
> the file because it is being used by another process.).
> ===> 3041 :
> BACKUP failed to complete the command BACKUP DATABASE [database_abcr]
> TO DISK = N'F:\temp\database_abc_041005' WITH NOINIT , NOUNLOAD ,
> NAME = N'database_abc backup', NOSKIP , STATS = 10, NOFORMAT
> ===>
> When the manual backup is initiated without Verification, no (popup)
> error is generated (but the error above is still logged on the event
> log) and the backup says it ran successfully. When the backup file is
> checked, the size is Zero (0) kb.
> There is no apparent problem in connecting to both the two instances
> of the server and applications are working okay, it's just the backup
> problem that is bugging us.
> drive Q = quorum
> drive F = one of the shared drives with 20Gb+ remaining
> The database that we are attempting to backup is just about 11Gb in
> size.
>
> Any help is appreciated.
> Thanks.
>
> Aramid
>|||Thanks for your reply, Michael.
We are not backing up to any shared network drive, just to this drive,
which is one of the logical drives on an external disk array attached
to the cluster.
Aramid
On Sun, 10 Apr 2005 23:56:13 -0400, "Michael C#" <xyz@.abcdef.com>
wrote:
>Just a quick question - were you backing up to a shared network drive
>previously? What if you try backing up to the fully qualified UNC Path
>instead?
>"aramid" <aramid@.hotmail.com> wrote in message
>news:s4rj511nm4jrf8at18grbsnio4jseoc7t8@.4ax.com...
>> Hello Everyone,
>> We were moving a 2-node DB cluster (Active/Active on SQL 2000 Ent /
>> Windows 2003 Ent) from one subnet to another. Everything went well
>> (apparently) until we realized that our backups are failing (both the
>> existing jobs and manual backups) after the change in ip/subnet was
>> done. All backup attempts return with errrors similar to these:
>> ===>> 18204 :
>> BackupDiskFile::OpenMedia: Backup device 'F:\temp\database_abc_041005'
>> failed to open. Operating system error = 32(The process cannot access
>> the file because it is being used by another process.).
>> ===>> 3041 :
>> BACKUP failed to complete the command BACKUP DATABASE [database_abcr]
>> TO DISK = N'F:\temp\database_abc_041005' WITH NOINIT , NOUNLOAD ,
>> NAME = N'database_abc backup', NOSKIP , STATS = 10, NOFORMAT
>> ===>>
>> When the manual backup is initiated without Verification, no (popup)
>> error is generated (but the error above is still logged on the event
>> log) and the backup says it ran successfully. When the backup file is
>> checked, the size is Zero (0) kb.
>> There is no apparent problem in connecting to both the two instances
>> of the server and applications are working okay, it's just the backup
>> problem that is bugging us.
>> drive Q = quorum
>> drive F = one of the shared drives with 20Gb+ remaining
>> The database that we are attempting to backup is just about 11Gb in
>> size.
>>
>> Any help is appreciated.
>> Thanks.
>>
>> Aramid
>>
>|||aramid
It seems that is permission or OS issue . We have recently moved to the
cluster and what i did in the backup script only changed the D:\ Driver
path to N:\ driver path. Run SQL Server Profile to see what is going on.
Have you searched on internet for those errors?
"aramid" <aramid@.hotmail.com> wrote in message
news:0ouj51dm2esqntk6pegt6f37m36f4g8brj@.4ax.com...
> Thanks for your reply, Michael.
> We are not backing up to any shared network drive, just to this drive,
> which is one of the logical drives on an external disk array attached
> to the cluster.
> Aramid
> On Sun, 10 Apr 2005 23:56:13 -0400, "Michael C#" <xyz@.abcdef.com>
> wrote:
> >Just a quick question - were you backing up to a shared network drive
> >previously? What if you try backing up to the fully qualified UNC Path
> >instead?
> >
> >"aramid" <aramid@.hotmail.com> wrote in message
> >news:s4rj511nm4jrf8at18grbsnio4jseoc7t8@.4ax.com...
> >> Hello Everyone,
> >>
> >> We were moving a 2-node DB cluster (Active/Active on SQL 2000 Ent /
> >> Windows 2003 Ent) from one subnet to another. Everything went well
> >> (apparently) until we realized that our backups are failing (both the
> >> existing jobs and manual backups) after the change in ip/subnet was
> >> done. All backup attempts return with errrors similar to these:
> >>
> >> ===> >> 18204 :
> >> BackupDiskFile::OpenMedia: Backup device 'F:\temp\database_abc_041005'
> >> failed to open. Operating system error = 32(The process cannot access
> >> the file because it is being used by another process.).
> >> ===> >> 3041 :
> >> BACKUP failed to complete the command BACKUP DATABASE [database_abcr]
> >> TO DISK = N'F:\temp\database_abc_041005' WITH NOINIT , NOUNLOAD ,
> >> NAME = N'database_abc backup', NOSKIP , STATS = 10, NOFORMAT
> >> ===> >>
> >>
> >> When the manual backup is initiated without Verification, no (popup)
> >> error is generated (but the error above is still logged on the event
> >> log) and the backup says it ran successfully. When the backup file is
> >> checked, the size is Zero (0) kb.
> >>
> >> There is no apparent problem in connecting to both the two instances
> >> of the server and applications are working okay, it's just the backup
> >> problem that is bugging us.
> >>
> >> drive Q = quorum
> >> drive F = one of the shared drives with 20Gb+ remaining
> >>
> >> The database that we are attempting to backup is just about 11Gb in
> >> size.
> >>
> >>
> >> Any help is appreciated.
> >>
> >> Thanks.
> >>
> >>
> >> Aramid
> >>
> >>
> >
>|||SQL Server say that the backup file is used by some other process. I'd get a tool with which I can
check who is using the file...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"aramid" <aramid@.hotmail.com> wrote in message news:s4rj511nm4jrf8at18grbsnio4jseoc7t8@.4ax.com...
> Hello Everyone,
> We were moving a 2-node DB cluster (Active/Active on SQL 2000 Ent /
> Windows 2003 Ent) from one subnet to another. Everything went well
> (apparently) until we realized that our backups are failing (both the
> existing jobs and manual backups) after the change in ip/subnet was
> done. All backup attempts return with errrors similar to these:
> ===> 18204 :
> BackupDiskFile::OpenMedia: Backup device 'F:\temp\database_abc_041005'
> failed to open. Operating system error = 32(The process cannot access
> the file because it is being used by another process.).
> ===> 3041 :
> BACKUP failed to complete the command BACKUP DATABASE [database_abcr]
> TO DISK = N'F:\temp\database_abc_041005' WITH NOINIT , NOUNLOAD ,
> NAME = N'database_abc backup', NOSKIP , STATS = 10, NOFORMAT
> ===>
> When the manual backup is initiated without Verification, no (popup)
> error is generated (but the error above is still logged on the event
> log) and the backup says it ran successfully. When the backup file is
> checked, the size is Zero (0) kb.
> There is no apparent problem in connecting to both the two instances
> of the server and applications are working okay, it's just the backup
> problem that is bugging us.
> drive Q = quorum
> drive F = one of the shared drives with 20Gb+ remaining
> The database that we are attempting to backup is just about 11Gb in
> size.
>
> Any help is appreciated.
> Thanks.
>
> Aramid
>

Backup Problem

Friday, February 24, 2012

Backup ODBC on Windows 2000 Server?

howto backup ODBC configure and data on windows 2000 server help me!
thanks.urr?
Are you wanting to backup SQL Server?
Are you wanting to backup any data that you can find an ODBC driver for (that would be fun)?
Are you wanting to confuse me?|||If you want to backup the ODBC configuration of the server, including its ODBC drivers, DSNs, FSNs, etc, make a full backup of the whole server. Although it is possible to "clone" the ODBC configuration onto a new server, that is not a wise idea.

-PatP|||ODBC configuration is kept in registry which is what you need to back up.|||ODBC configuration is kept in registry which is what you need to back up.Yes, and restoring a registry onto another machine has always been a reciepe for disaster in my book. Even doing selected hives or even keys is dangerous unless the destination machine has EVERYTHING referenced in the source machine!

-PatP|||OK, who pissed Pat off?? Raise your hand!|||Sorry if I sound testy, I certainly didn't mean that. The problem with telling someone that isn't very familiar with the registry to do much of anything with it, is that there is huge risk.

Based on the question, my guess is that they aren't familiar with the even what the registry is, much less how to work on it. If they wipe out a hive (yes, I've seen that done!), they can make a machine unbootable. That's pretty risky stuff, just to save 5 minutes of ODBC setup.

Even if they just have the wrong MDAC or Oracle version, that can make it useless to restore the DSNs from one machine to another. I guess that I've had to fix too many machines where someone was trying to save five minutes and it cost me a day of travel and three hours worth of work.

-PatP

Backup not showing in schedule

I am using Sql Server7 and Windows NT.
I am try to do some testing and am trying to set up 3 backups: 1 full, 1
differential and 1 transaction log.
I set up the Full backup and it showed up fine in the Jobs window of EM.
I then have tried about 6 times to do the Differential and it seems to
take file, but it never shows up in the Jobs window.
I am trying to set it up using "Northwind Database-Differential Backup"
(I also tried "Northwind Database-Dif Backup") backing it up to the
"Northwind_Differential" device. I set it up for "Database
Differential", to run daily every 6 hours from 6:00 AM to 6:00:59 PM.
It seems to take it fine, but it never shows up on the Jobs window in EM.
Anyone know what I am doing wrong?
Thanks,
Tom.Perhaps an EM refresh issue?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message news:3FBAF429.3080806@.deltanet.com...
> I am using Sql Server7 and Windows NT.
> I am try to do some testing and am trying to set up 3 backups: 1 full, 1
> differential and 1 transaction log.
> I set up the Full backup and it showed up fine in the Jobs window of EM.
> I then have tried about 6 times to do the Differential and it seems to
> take file, but it never shows up in the Jobs window.
> I am trying to set it up using "Northwind Database-Differential Backup"
> (I also tried "Northwind Database-Dif Backup") backing it up to the
> "Northwind_Differential" device. I set it up for "Database
> Differential", to run daily every 6 hours from 6:00 AM to 6:00:59 PM.
> It seems to take it fine, but it never shows up on the Jobs window in EM.
> Anyone know what I am doing wrong?
> Thanks,
> Tom.
>|||Could be. It now works. I had set up 3 copies of the differential
which didn't show up right away (even after reboot, if I remember
correctly). Then all of a sudden all three were there.
I'm sure I missed something.
Tibor Karaszi wrote:
> Perhaps an EM refresh issue?
>

Monday, February 13, 2012

Backup job succeeds but not actually backing up

I have a server with Windows 2000 SP4+hotfixes, SQL Server
2000 SE SP3A+hotfix. The server has 4 user databases (the
largest being around 500 megs). 3 of the databases backup
fine but 1 database will not take a full backup or a
transaction log backup from the maintenance plan. The job
runs and succeeds but the file is not where it should be
(nor anywhere else).
The development server with the same configuration does
not have this same problem, and it takes about 7 seconds
to backup the database in question.
I have monitored the directory that the file should be
created in while the job is running, and the file does
create and stays there for the 5 to 7 seconds that the job
is running and then it dissappears.
I have also used File Monitor from sysinternals and
monitored the system while a backup is being run and
sqlservr.exe and sqlmaint.exe are the only things that
touch the .BAK file. Sqlservr.exe reads and writes to
the .BAK file numerous times and the last thing that
happens is sqlservr.exe opens the .BAK file, then Deletes
the .BAK file, and then closes the .BAK file. No other
applications besides sqlservr.exe and sqlmaint.exe touch
the .BAK file. The index numbers in File Monitor do not
skip any numbers.
Here are the lines from sysinternals File Monitor where
the backup is deleted:
1646 6:32:56 PM sqlservr.exe:3688 OPEN
E:\Microsoft SQL
Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
SUCCESS Options: Open Access: All
1647 6:32:56 PM sqlservr.exe:3688 DELETE
E:\Microsoft SQL
Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
SUCCESS
1648 6:32:56 PM sqlservr.exe:3688 CLOSE
E:\Microsoft SQL
Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
SUCCESS
The error message in the errorlog is:
BACKUP failed to complete the command BACKUP LOG [t_prod]
TO DISK = N'E:\Microsoft SQL
Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK'
WITH INIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT
Disk space is not an issue as there is over 60 gigs free
and all the databases together barely total a 1 gig.
Backups from a script that run through a job (without the
maint plan wizard) succeed about 75% of the time but about
25% of the time the job fails with the same errors as the
maint plan fails with. I'm still trying to get File
Monitor running at the time a backup from a script fails,
but haven't been luckly enough to catch it at the right
time yet.
Any information or suggestions to resolve this problem are
greatly appreaciated as I've been trying to figure this
out for too long. If there is any other information that
would be useful to post, please let me know.
Thanks,
Trent.any clues from the NT's system or application error log
files?
this may be dumb thing to ask, but make sure that backup
retention option is properly set in db maint plan.
>--Original Message--
>I have a server with Windows 2000 SP4+hotfixes, SQL
Server
>2000 SE SP3A+hotfix. The server has 4 user databases
(the
>largest being around 500 megs). 3 of the databases
backup
>fine but 1 database will not take a full backup or a
>transaction log backup from the maintenance plan. The
job
>runs and succeeds but the file is not where it should be
>(nor anywhere else).
>The development server with the same configuration does
>not have this same problem, and it takes about 7 seconds
>to backup the database in question.
>I have monitored the directory that the file should be
>created in while the job is running, and the file does
>create and stays there for the 5 to 7 seconds that the
job
>is running and then it dissappears.
>I have also used File Monitor from sysinternals and
>monitored the system while a backup is being run and
>sqlservr.exe and sqlmaint.exe are the only things that
>touch the .BAK file. Sqlservr.exe reads and writes to
>the .BAK file numerous times and the last thing that
>happens is sqlservr.exe opens the .BAK file, then Deletes
>the .BAK file, and then closes the .BAK file. No other
>applications besides sqlservr.exe and sqlmaint.exe touch
>the .BAK file. The index numbers in File Monitor do not
>skip any numbers.
>Here are the lines from sysinternals File Monitor where
>the backup is deleted:
>1646 6:32:56 PM sqlservr.exe:3688 OPEN
> E:\Microsoft SQL
>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS Options: Open Access: All
>1647 6:32:56 PM sqlservr.exe:3688 DELETE
> E:\Microsoft SQL
>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS
>1648 6:32:56 PM sqlservr.exe:3688 CLOSE
> E:\Microsoft SQL
>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS
>
>The error message in the errorlog is:
>BACKUP failed to complete the command BACKUP LOG [t_prod]
>TO DISK = N'E:\Microsoft SQL
>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK'
>WITH INIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT
>Disk space is not an issue as there is over 60 gigs free
>and all the databases together barely total a 1 gig.
>Backups from a script that run through a job (without the
>maint plan wizard) succeed about 75% of the time but
about
>25% of the time the job fails with the same errors as the
>maint plan fails with. I'm still trying to get File
>Monitor running at the time a backup from a script fails,
>but haven't been luckly enough to catch it at the right
>time yet.
>Any information or suggestions to resolve this problem
are
>greatly appreaciated as I've been trying to figure this
>out for too long. If there is any other information that
>would be useful to post, please let me know.
>Thanks,
>Trent.
>.
>|||There is no errors in the SQL errorlog, NT system or
application error logs unless I turn on the option to
verify the integrity of the backup upon completion. Then
the error message is:
Operating system error = 2 (the system cannot find the
file specified)
OR
Operating system error = 32 (The process cannot access the
file because it is being used by another process).
It's intermittent which of the above 2 errors will come
up. The os error = 32 was the reason I ran File Monitor,
but it didn't lead me to the cause as nothing but SQL
Server appeared to be using the file.
Trent.
>--Original Message--
>any clues from the NT's system or application error log
>files?
>this may be dumb thing to ask, but make sure that backup
>retention option is properly set in db maint plan.
>>--Original Message--
>>I have a server with Windows 2000 SP4+hotfixes, SQL
>Server
>>2000 SE SP3A+hotfix. The server has 4 user databases
>(the
>>largest being around 500 megs). 3 of the databases
>backup
>>fine but 1 database will not take a full backup or a
>>transaction log backup from the maintenance plan. The
>job
>>runs and succeeds but the file is not where it should be
>>(nor anywhere else).
>>The development server with the same configuration does
>>not have this same problem, and it takes about 7 seconds
>>to backup the database in question.
>>I have monitored the directory that the file should be
>>created in while the job is running, and the file does
>>create and stays there for the 5 to 7 seconds that the
>job
>>is running and then it dissappears.
>>I have also used File Monitor from sysinternals and
>>monitored the system while a backup is being run and
>>sqlservr.exe and sqlmaint.exe are the only things that
>>touch the .BAK file. Sqlservr.exe reads and writes to
>>the .BAK file numerous times and the last thing that
>>happens is sqlservr.exe opens the .BAK file, then
Deletes
>>the .BAK file, and then closes the .BAK file. No other
>>applications besides sqlservr.exe and sqlmaint.exe touch
>>the .BAK file. The index numbers in File Monitor do not
>>skip any numbers.
>>Here are the lines from sysinternals File Monitor where
>>the backup is deleted:
>>1646 6:32:56 PM sqlservr.exe:3688 OPEN
>> E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
>> SUCCESS Options: Open Access: All
>>1647 6:32:56 PM sqlservr.exe:3688 DELETE
>> E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
>> SUCCESS
>>1648 6:32:56 PM sqlservr.exe:3688 CLOSE
>> E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
>> SUCCESS
>>
>>The error message in the errorlog is:
>>BACKUP failed to complete the command BACKUP LOG
[t_prod]
>>TO DISK = N'E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
'
>>WITH INIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT
>>Disk space is not an issue as there is over 60 gigs free
>>and all the databases together barely total a 1 gig.
>>Backups from a script that run through a job (without
the
>>maint plan wizard) succeed about 75% of the time but
>about
>>25% of the time the job fails with the same errors as
the
>>maint plan fails with. I'm still trying to get File
>>Monitor running at the time a backup from a script
fails,
>>but haven't been luckly enough to catch it at the right
>>time yet.
>>Any information or suggestions to resolve this problem
>are
>>greatly appreaciated as I've been trying to figure this
>>out for too long. If there is any other information
that
>>would be useful to post, please let me know.
>>Thanks,
>>Trent.
>>.
>.
>|||Sorry, forgot to comment on the retention. It has always
been set at 5 days but I have tried turning the retention
off and it makes no difference.
Trent.
>--Original Message--
>any clues from the NT's system or application error log
>files?
>this may be dumb thing to ask, but make sure that backup
>retention option is properly set in db maint plan.
>>--Original Message--
>>I have a server with Windows 2000 SP4+hotfixes, SQL
>Server
>>2000 SE SP3A+hotfix. The server has 4 user databases
>(the
>>largest being around 500 megs). 3 of the databases
>backup
>>fine but 1 database will not take a full backup or a
>>transaction log backup from the maintenance plan. The
>job
>>runs and succeeds but the file is not where it should be
>>(nor anywhere else).
>>The development server with the same configuration does
>>not have this same problem, and it takes about 7 seconds
>>to backup the database in question.
>>I have monitored the directory that the file should be
>>created in while the job is running, and the file does
>>create and stays there for the 5 to 7 seconds that the
>job
>>is running and then it dissappears.
>>I have also used File Monitor from sysinternals and
>>monitored the system while a backup is being run and
>>sqlservr.exe and sqlmaint.exe are the only things that
>>touch the .BAK file. Sqlservr.exe reads and writes to
>>the .BAK file numerous times and the last thing that
>>happens is sqlservr.exe opens the .BAK file, then
Deletes
>>the .BAK file, and then closes the .BAK file. No other
>>applications besides sqlservr.exe and sqlmaint.exe touch
>>the .BAK file. The index numbers in File Monitor do not
>>skip any numbers.
>>Here are the lines from sysinternals File Monitor where
>>the backup is deleted:
>>1646 6:32:56 PM sqlservr.exe:3688 OPEN
>> E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
>> SUCCESS Options: Open Access: All
>>1647 6:32:56 PM sqlservr.exe:3688 DELETE
>> E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
>> SUCCESS
>>1648 6:32:56 PM sqlservr.exe:3688 CLOSE
>> E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
>> SUCCESS
>>
>>The error message in the errorlog is:
>>BACKUP failed to complete the command BACKUP LOG
[t_prod]
>>TO DISK = N'E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
'
>>WITH INIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT
>>Disk space is not an issue as there is over 60 gigs free
>>and all the databases together barely total a 1 gig.
>>Backups from a script that run through a job (without
the
>>maint plan wizard) succeed about 75% of the time but
>about
>>25% of the time the job fails with the same errors as
the
>>maint plan fails with. I'm still trying to get File
>>Monitor running at the time a backup from a script
fails,
>>but haven't been luckly enough to catch it at the right
>>time yet.
>>Any information or suggestions to resolve this problem
>are
>>greatly appreaciated as I've been trying to figure this
>>out for too long. If there is any other information
that
>>would be useful to post, please let me know.
>>Thanks,
>>Trent.
>>.
>.
>|||Trent and Others!
I am experiencing the same behavior on my SQL Server 7.0
running on NT (lastest service packs and patched on
both.) I too have witnessed the BAK file created and then
deleted. I do not have the maintance plan configured to
deleting old .BAK files. I do receive this error message
when I create a backup from the "backup" option (not the
database maintenance plan.) This is the message I get:
10 percent backed up. [SQLSTATE 01000] (Message 3211) 20
percent backed up. [SQLSTATE 01000] (Message 3211)
ConnectionRead (WrapperRead()). [SQLSTATE 01000] (Message
258) General network error. Check your network
documentation. [SQLSTATE 08S01] (Error 11) 30 percent
backed up. [SQLSTATE 01000] (Message 3211). The step
failed.
NOTE: I am backing up to the server harddrive(NO NETWORK
INVOLVED!!!)with 20 gigs free. This database is only
199mb in size. I suspect the "general network error" is
bogus.
Please help!
>--Original Message--
>I have a server with Windows 2000 SP4+hotfixes, SQL
Server
>2000 SE SP3A+hotfix. The server has 4 user databases
(the
>largest being around 500 megs). 3 of the databases
backup
>fine but 1 database will not take a full backup or a
>transaction log backup from the maintenance plan. The
job
>runs and succeeds but the file is not where it should be
>(nor anywhere else).
>The development server with the same configuration does
>not have this same problem, and it takes about 7 seconds
>to backup the database in question.
>I have monitored the directory that the file should be
>created in while the job is running, and the file does
>create and stays there for the 5 to 7 seconds that the
job
>is running and then it dissappears.
>I have also used File Monitor from sysinternals and
>monitored the system while a backup is being run and
>sqlservr.exe and sqlmaint.exe are the only things that
>touch the .BAK file. Sqlservr.exe reads and writes to
>the .BAK file numerous times and the last thing that
>happens is sqlservr.exe opens the .BAK file, then Deletes
>the .BAK file, and then closes the .BAK file. No other
>applications besides sqlservr.exe and sqlmaint.exe touch
>the .BAK file. The index numbers in File Monitor do not
>skip any numbers.
>Here are the lines from sysinternals File Monitor where
>the backup is deleted:
>1646 6:32:56 PM sqlservr.exe:3688 OPEN
> E:\Microsoft SQL
>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS Options: Open Access: All
>1647 6:32:56 PM sqlservr.exe:3688 DELETE
> E:\Microsoft SQL
>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS
>1648 6:32:56 PM sqlservr.exe:3688 CLOSE
> E:\Microsoft SQL
>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS
>
>The error message in the errorlog is:
>BACKUP failed to complete the command BACKUP LOG [t_prod]
>TO DISK = N'E:\Microsoft SQL
>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK'
>WITH INIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT
>Disk space is not an issue as there is over 60 gigs free
>and all the databases together barely total a 1 gig.
>Backups from a script that run through a job (without the
>maint plan wizard) succeed about 75% of the time but
about
>25% of the time the job fails with the same errors as the
>maint plan fails with. I'm still trying to get File
>Monitor running at the time a backup from a script fails,
>but haven't been luckly enough to catch it at the right
>time yet.
>Any information or suggestions to resolve this problem
are
>greatly appreaciated as I've been trying to figure this
>out for too long. If there is any other information that
>would be useful to post, please let me know.
>Thanks,
>Trent.
>.
>|||I don't know if this will make any difference or not, but
do you specify the filename as the destination for the
backup, or do you create a backup device first?
Also, have you run a DBCC consistency chekc on the
database you're attempting to backup? Maybe the actual
failure is on the read from the DB side of the backup
process?
Just some early in the morning thoughts that may or may
not help.
Matthew Bando
BandoM@.CSCTechnologies (remove).com
>--Original Message--
>Sorry, forgot to comment on the retention. It has
always
>been set at 5 days but I have tried turning the
retention
>off and it makes no difference.
>Trent.
>>--Original Message--
>>any clues from the NT's system or application error log
>>files?
>>this may be dumb thing to ask, but make sure that
backup
>>retention option is properly set in db maint plan.
>>--Original Message--
>>I have a server with Windows 2000 SP4+hotfixes, SQL
>>Server
>>2000 SE SP3A+hotfix. The server has 4 user databases
>>(the
>>largest being around 500 megs). 3 of the databases
>>backup
>>fine but 1 database will not take a full backup or a
>>transaction log backup from the maintenance plan. The
>>job
>>runs and succeeds but the file is not where it should
be
>>(nor anywhere else).
>>The development server with the same configuration
does
>>not have this same problem, and it takes about 7
seconds
>>to backup the database in question.
>>I have monitored the directory that the file should be
>>created in while the job is running, and the file does
>>create and stays there for the 5 to 7 seconds that the
>>job
>>is running and then it dissappears.
>>I have also used File Monitor from sysinternals and
>>monitored the system while a backup is being run and
>>sqlservr.exe and sqlmaint.exe are the only things that
>>touch the .BAK file. Sqlservr.exe reads and writes to
>>the .BAK file numerous times and the last thing that
>>happens is sqlservr.exe opens the .BAK file, then
>Deletes
>>the .BAK file, and then closes the .BAK file. No
other
>>applications besides sqlservr.exe and sqlmaint.exe
touch
>>the .BAK file. The index numbers in File Monitor do
not
>>skip any numbers.
>>Here are the lines from sysinternals File Monitor
where
>>the backup is deleted:
>>1646 6:32:56 PM sqlservr.exe:3688 OPEN
>> E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.B
AK
>> SUCCESS Options: Open Access: All
>>1647 6:32:56 PM sqlservr.exe:3688 DELETE
>> E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.B
AK
>> SUCCESS
>>1648 6:32:56 PM sqlservr.exe:3688 CLOSE
>> E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.B
AK
>> SUCCESS
>>
>>The error message in the errorlog is:
>>BACKUP failed to complete the command BACKUP LOG
>[t_prod]
>>TO DISK = N'E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.B
AK
>'
>>WITH INIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT
>>Disk space is not an issue as there is over 60 gigs
free
>>and all the databases together barely total a 1 gig.
>>Backups from a script that run through a job (without
>the
>>maint plan wizard) succeed about 75% of the time but
>>about
>>25% of the time the job fails with the same errors as
>the
>>maint plan fails with. I'm still trying to get File
>>Monitor running at the time a backup from a script
>fails,
>>but haven't been luckly enough to catch it at the
right
>>time yet.
>>Any information or suggestions to resolve this problem
>>are
>>greatly appreaciated as I've been trying to figure
this
>>out for too long. If there is any other information
>that
>>would be useful to post, please let me know.
>>Thanks,
>>Trent.
>>.
>>.
>.
>|||I have noticed this same message occasionaly (about 25% of
the time) when backing up from QA or when scheduling a
backup command in a job. I found a hit on technet but it
kinda just let me to believe it was a named pipes bug and
not the actual root of the problem.
Here is the article I was talking about:
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%
3B827452
Trent.
>--Original Message--
>Trent and Others!
>I am experiencing the same behavior on my SQL Server 7.0
>running on NT (lastest service packs and patched on
>both.) I too have witnessed the BAK file created and
then
>deleted. I do not have the maintance plan configured to
>deleting old .BAK files. I do receive this error message
>when I create a backup from the "backup" option (not the
>database maintenance plan.) This is the message I get:
>10 percent backed up. [SQLSTATE 01000] (Message 3211) 20
>percent backed up. [SQLSTATE 01000] (Message 3211)
>ConnectionRead (WrapperRead()). [SQLSTATE 01000] (Message
>258) General network error. Check your network
>documentation. [SQLSTATE 08S01] (Error 11) 30 percent
>backed up. [SQLSTATE 01000] (Message 3211). The step
>failed.
>NOTE: I am backing up to the server harddrive(NO NETWORK
>INVOLVED!!!)with 20 gigs free. This database is only
>199mb in size. I suspect the "general network error" is
>bogus.
>Please help!
>>--Original Message--
>>I have a server with Windows 2000 SP4+hotfixes, SQL
>Server
>>2000 SE SP3A+hotfix. The server has 4 user databases
>(the
>>largest being around 500 megs). 3 of the databases
>backup
>>fine but 1 database will not take a full backup or a
>>transaction log backup from the maintenance plan. The
>job
>>runs and succeeds but the file is not where it should be
>>(nor anywhere else).
>>The development server with the same configuration does
>>not have this same problem, and it takes about 7 seconds
>>to backup the database in question.
>>I have monitored the directory that the file should be
>>created in while the job is running, and the file does
>>create and stays there for the 5 to 7 seconds that the
>job
>>is running and then it dissappears.
>>I have also used File Monitor from sysinternals and
>>monitored the system while a backup is being run and
>>sqlservr.exe and sqlmaint.exe are the only things that
>>touch the .BAK file. Sqlservr.exe reads and writes to
>>the .BAK file numerous times and the last thing that
>>happens is sqlservr.exe opens the .BAK file, then
Deletes
>>the .BAK file, and then closes the .BAK file. No other
>>applications besides sqlservr.exe and sqlmaint.exe touch
>>the .BAK file. The index numbers in File Monitor do not
>>skip any numbers.
>>Here are the lines from sysinternals File Monitor where
>>the backup is deleted:
>>1646 6:32:56 PM sqlservr.exe:3688 OPEN
>> E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
>> SUCCESS Options: Open Access: All
>>1647 6:32:56 PM sqlservr.exe:3688 DELETE
>> E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
>> SUCCESS
>>1648 6:32:56 PM sqlservr.exe:3688 CLOSE
>> E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
>> SUCCESS
>>
>>The error message in the errorlog is:
>>BACKUP failed to complete the command BACKUP LOG
[t_prod]
>>TO DISK = N'E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
'
>>WITH INIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT
>>Disk space is not an issue as there is over 60 gigs free
>>and all the databases together barely total a 1 gig.
>>Backups from a script that run through a job (without
the
>>maint plan wizard) succeed about 75% of the time but
>about
>>25% of the time the job fails with the same errors as
the
>>maint plan fails with. I'm still trying to get File
>>Monitor running at the time a backup from a script
fails,
>>but haven't been luckly enough to catch it at the right
>>time yet.
>>Any information or suggestions to resolve this problem
>are
>>greatly appreaciated as I've been trying to figure this
>>out for too long. If there is any other information
that
>>would be useful to post, please let me know.
>>Thanks,
>>Trent.
>>.
>.
>|||Hi Matthew,
Thanks for the reply. I always backup to a filename,
mainly because that's just the way I normally do it. I
have experimented with backing up to a device and it does
seem to work (although I never extensively tested it) but
it's starting to look like my only option.
I run DBCC checks weekly and they never find any errors (I
double checked the last 6 weeks of dbcc checks to make
sure they didn't find any errors)
Trent.
>--Original Message--
>I don't know if this will make any difference or not, but
>do you specify the filename as the destination for the
>backup, or do you create a backup device first?
>Also, have you run a DBCC consistency chekc on the
>database you're attempting to backup? Maybe the actual
>failure is on the read from the DB side of the backup
>process?
>Just some early in the morning thoughts that may or may
>not help.
>Matthew Bando
>BandoM@.CSCTechnologies (remove).com
>
>>--Original Message--
>>Sorry, forgot to comment on the retention. It has
>always
>>been set at 5 days but I have tried turning the
>retention
>>off and it makes no difference.
>>Trent.
>>--Original Message--
>>any clues from the NT's system or application error log
>>files?
>>this may be dumb thing to ask, but make sure that
>backup
>>retention option is properly set in db maint plan.
>>--Original Message--
>>I have a server with Windows 2000 SP4+hotfixes, SQL
>>Server
>>2000 SE SP3A+hotfix. The server has 4 user databases
>>(the
>>largest being around 500 megs). 3 of the databases
>>backup
>>fine but 1 database will not take a full backup or a
>>transaction log backup from the maintenance plan. The
>>job
>>runs and succeeds but the file is not where it should
>be
>>(nor anywhere else).
>>The development server with the same configuration
>does
>>not have this same problem, and it takes about 7
>seconds
>>to backup the database in question.
>>I have monitored the directory that the file should be
>>created in while the job is running, and the file does
>>create and stays there for the 5 to 7 seconds that the
>>job
>>is running and then it dissappears.
>>I have also used File Monitor from sysinternals and
>>monitored the system while a backup is being run and
>>sqlservr.exe and sqlmaint.exe are the only things that
>>touch the .BAK file. Sqlservr.exe reads and writes to
>>the .BAK file numerous times and the last thing that
>>happens is sqlservr.exe opens the .BAK file, then
>>Deletes
>>the .BAK file, and then closes the .BAK file. No
>other
>>applications besides sqlservr.exe and sqlmaint.exe
>touch
>>the .BAK file. The index numbers in File Monitor do
>not
>>skip any numbers.
>>Here are the lines from sysinternals File Monitor
>where
>>the backup is deleted:
>>1646 6:32:56 PM sqlservr.exe:3688
OPEN
>> E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.B
>AK
>> SUCCESS Options: Open Access: All
>>1647 6:32:56 PM sqlservr.exe:3688
DELETE
>> E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.B
>AK
>> SUCCESS
>>1648 6:32:56 PM sqlservr.exe:3688
CLOSE
>> E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.B
>AK
>> SUCCESS
>>
>>The error message in the errorlog is:
>>BACKUP failed to complete the command BACKUP LOG
>>[t_prod]
>>TO DISK = N'E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.B
>AK
>>'
>>WITH INIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT
>>Disk space is not an issue as there is over 60 gigs
>free
>>and all the databases together barely total a 1 gig.
>>Backups from a script that run through a job (without
>>the
>>maint plan wizard) succeed about 75% of the time but
>>about
>>25% of the time the job fails with the same errors as
>>the
>>maint plan fails with. I'm still trying to get File
>>Monitor running at the time a backup from a script
>>fails,
>>but haven't been luckly enough to catch it at the
>right
>>time yet.
>>Any information or suggestions to resolve this problem
>>are
>>greatly appreaciated as I've been trying to figure
>this
>>out for too long. If there is any other information
>>that
>>would be useful to post, please let me know.
>>Thanks,
>>Trent.
>>.
>>.
>>.
>.
>|||All I can say is that it's very odd. Sometimes things
that should be equivalent (like backing up to a file
directly or backing up to a device that is a file) aren't
equivalent.
It will be interestign to see if you switch to always
using a device, if you eventually see the same error.
Matthew Bando
BandoM@.CSCTechnologies (remove) .com
>--Original Message--
>Hi Matthew,
>Thanks for the reply. I always backup to a filename,
>mainly because that's just the way I normally do it. I
>have experimented with backing up to a device and it
does
>seem to work (although I never extensively tested it)
but
>it's starting to look like my only option.
>I run DBCC checks weekly and they never find any errors
(I
>double checked the last 6 weeks of dbcc checks to make
>sure they didn't find any errors)
>Trent.
>>--Original Message--
>>I don't know if this will make any difference or not,
but
>>do you specify the filename as the destination for the
>>backup, or do you create a backup device first?
>>Also, have you run a DBCC consistency chekc on the
>>database you're attempting to backup? Maybe the actual
>>failure is on the read from the DB side of the backup
>>process?
>>Just some early in the morning thoughts that may or may
>>not help.
>>Matthew Bando
>>BandoM@.CSCTechnologies (remove).com
>>
>>--Original Message--
>>Sorry, forgot to comment on the retention. It has
>>always
>>been set at 5 days but I have tried turning the
>>retention
>>off and it makes no difference.
>>Trent.
>>--Original Message--
>>any clues from the NT's system or application error
log
>>files?
>>this may be dumb thing to ask, but make sure that
>>backup
>>retention option is properly set in db maint plan.
>>--Original Message--
>>I have a server with Windows 2000 SP4+hotfixes, SQL
>>Server
>>2000 SE SP3A+hotfix. The server has 4 user
databases
>>(the
>>largest being around 500 megs). 3 of the databases
>>backup
>>fine but 1 database will not take a full backup or a
>>transaction log backup from the maintenance plan.
The
>>job
>>runs and succeeds but the file is not where it
should
>>be
>>(nor anywhere else).
>>The development server with the same configuration
>>does
>>not have this same problem, and it takes about 7
>>seconds
>>to backup the database in question.
>>I have monitored the directory that the file should
be
>>created in while the job is running, and the file
does
>>create and stays there for the 5 to 7 seconds that
the
>>job
>>is running and then it dissappears.
>>I have also used File Monitor from sysinternals and
>>monitored the system while a backup is being run and
>>sqlservr.exe and sqlmaint.exe are the only things
that
>>touch the .BAK file. Sqlservr.exe reads and writes
to
>>the .BAK file numerous times and the last thing that
>>happens is sqlservr.exe opens the .BAK file, then
>>Deletes
>>the .BAK file, and then closes the .BAK file. No
>>other
>>applications besides sqlservr.exe and sqlmaint.exe
>>touch
>>the .BAK file. The index numbers in File Monitor do
>>not
>>skip any numbers.
>>Here are the lines from sysinternals File Monitor
>>where
>>the backup is deleted:
>>1646 6:32:56 PM sqlservr.exe:3688
> OPEN
>> E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832
.B
>>AK
>> SUCCESS Options: Open Access: All
>>1647 6:32:56 PM sqlservr.exe:3688
> DELETE
>> E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832
.B
>>AK
>> SUCCESS
>>1648 6:32:56 PM sqlservr.exe:3688
> CLOSE
>> E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832
.B
>>AK
>> SUCCESS
>>
>>The error message in the errorlog is:
>>BACKUP failed to complete the command BACKUP LOG
>>[t_prod]
>>TO DISK = N'E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832
.B
>>AK
>>'
>>WITH INIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT
>>Disk space is not an issue as there is over 60 gigs
>>free
>>and all the databases together barely total a 1 gig.
>>Backups from a script that run through a job
(without
>>the
>>maint plan wizard) succeed about 75% of the time but
>>about
>>25% of the time the job fails with the same errors
as
>>the
>>maint plan fails with. I'm still trying to get File
>>Monitor running at the time a backup from a script
>>fails,
>>but haven't been luckly enough to catch it at the
>>right
>>time yet.
>>Any information or suggestions to resolve this
problem
>>are
>>greatly appreaciated as I've been trying to figure
>>this
>>out for too long. If there is any other information
>>that
>>would be useful to post, please let me know.
>>Thanks,
>>Trent.
>>.
>>.
>>.
>>.
>.
>|||Do you have some sort of virus scanner/open file manager active on that
server? I vaguely recall that such tools used to give us trouble with
certain versions of the scanner, the OS and SQL Server.
"Trent" <anonymous@.discussions.microsoft.com> wrote in message
news:0f9601c3b395$e9042d90$a001280a@.phx.gbl...
> I have a server with Windows 2000 SP4+hotfixes, SQL Server
> 2000 SE SP3A+hotfix. The server has 4 user databases (the
> largest being around 500 megs). 3 of the databases backup
> fine but 1 database will not take a full backup or a
> transaction log backup from the maintenance plan. The job
> runs and succeeds but the file is not where it should be
> (nor anywhere else).
> The development server with the same configuration does
> not have this same problem, and it takes about 7 seconds
> to backup the database in question.
> I have monitored the directory that the file should be
> created in while the job is running, and the file does
> create and stays there for the 5 to 7 seconds that the job
> is running and then it dissappears.
> I have also used File Monitor from sysinternals and
> monitored the system while a backup is being run and
> sqlservr.exe and sqlmaint.exe are the only things that
> touch the .BAK file. Sqlservr.exe reads and writes to
> the .BAK file numerous times and the last thing that
> happens is sqlservr.exe opens the .BAK file, then Deletes
> the .BAK file, and then closes the .BAK file. No other
> applications besides sqlservr.exe and sqlmaint.exe touch
> the .BAK file. The index numbers in File Monitor do not
> skip any numbers.
> Here are the lines from sysinternals File Monitor where
> the backup is deleted:
> 1646 6:32:56 PM sqlservr.exe:3688 OPEN
> E:\Microsoft SQL
> Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS Options: Open Access: All
> 1647 6:32:56 PM sqlservr.exe:3688 DELETE
> E:\Microsoft SQL
> Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS
> 1648 6:32:56 PM sqlservr.exe:3688 CLOSE
> E:\Microsoft SQL
> Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS
>
> The error message in the errorlog is:
> BACKUP failed to complete the command BACKUP LOG [t_prod]
> TO DISK = N'E:\Microsoft SQL
> Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK'
> WITH INIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT
> Disk space is not an issue as there is over 60 gigs free
> and all the databases together barely total a 1 gig.
> Backups from a script that run through a job (without the
> maint plan wizard) succeed about 75% of the time but about
> 25% of the time the job fails with the same errors as the
> maint plan fails with. I'm still trying to get File
> Monitor running at the time a backup from a script fails,
> but haven't been luckly enough to catch it at the right
> time yet.
> Any information or suggestions to resolve this problem are
> greatly appreaciated as I've been trying to figure this
> out for too long. If there is any other information that
> would be useful to post, please let me know.
> Thanks,
> Trent.|||I received a responds to my posting and the tech made the
point that really made sense to me. I knew transactions
rolled back. But he mentioned that a failed backup job
rolls back.
I decided to create a backup job manually. It worked!!!
Then I scheduled it through the job interfaced and it ran
as scheduled.
To clarify this to you:
Backup Job FAILS when I do the following steps:
Create a backup device, i.e. otg_backupdevice
Right click on the new backup device (otg_backupdevice)
and select "Backup a Database"
The SQL Server backup wizard window appears, select the
database (otg) to backup, add the backup device
(otg_backupdevice) and schedule the backup to occur once
a day at 12:00 A.M.
Then ran the job from the jobs window
The job fails.
Backup Job Succeeds when I do the following steps:
Create a backup device, i.e. otg_backupdevice
Go to the job window
Right-click and select new job
Name the job - OTG backup to device
Click on the "Steps" tab and click on "New Step"
Select OTG as the database
In the command window I enter "backup database otg to
otg_backupdevice"
Click on the "Schedule" tab and schedule it to backup at
10:00 am
The job suceeds
I suspect there is a problem with the backup wizard'
Kathy
>--Original Message--
>Hi Matthew,
>Thanks for the reply. I always backup to a filename,
>mainly because that's just the way I normally do it. I
>have experimented with backing up to a device and it
does
>seem to work (although I never extensively tested it)
but
>it's starting to look like my only option.
>I run DBCC checks weekly and they never find any errors
(I
>double checked the last 6 weeks of dbcc checks to make
>sure they didn't find any errors)
>Trent.
>>--Original Message--
>>I don't know if this will make any difference or not,
but
>>do you specify the filename as the destination for the
>>backup, or do you create a backup device first?
>>Also, have you run a DBCC consistency chekc on the
>>database you're attempting to backup? Maybe the actual
>>failure is on the read from the DB side of the backup
>>process?
>>Just some early in the morning thoughts that may or may
>>not help.
>>Matthew Bando
>>BandoM@.CSCTechnologies (remove).com
>>
>>--Original Message--
>>Sorry, forgot to comment on the retention. It has
>>always
>>been set at 5 days but I have tried turning the
>>retention
>>off and it makes no difference.
>>Trent.
>>--Original Message--
>>any clues from the NT's system or application error
log
>>files?
>>this may be dumb thing to ask, but make sure that
>>backup
>>retention option is properly set in db maint plan.
>>--Original Message--
>>I have a server with Windows 2000 SP4+hotfixes, SQL
>>Server
>>2000 SE SP3A+hotfix. The server has 4 user
databases
>>(the
>>largest being around 500 megs). 3 of the databases
>>backup
>>fine but 1 database will not take a full backup or a
>>transaction log backup from the maintenance plan.
The
>>job
>>runs and succeeds but the file is not where it
should
>>be
>>(nor anywhere else).
>>The development server with the same configuration
>>does
>>not have this same problem, and it takes about 7
>>seconds
>>to backup the database in question.
>>I have monitored the directory that the file should
be
>>created in while the job is running, and the file
does
>>create and stays there for the 5 to 7 seconds that
the
>>job
>>is running and then it dissappears.
>>I have also used File Monitor from sysinternals and
>>monitored the system while a backup is being run and
>>sqlservr.exe and sqlmaint.exe are the only things
that
>>touch the .BAK file. Sqlservr.exe reads and writes
to
>>the .BAK file numerous times and the last thing that
>>happens is sqlservr.exe opens the .BAK file, then
>>Deletes
>>the .BAK file, and then closes the .BAK file. No
>>other
>>applications besides sqlservr.exe and sqlmaint.exe
>>touch
>>the .BAK file. The index numbers in File Monitor do
>>not
>>skip any numbers.
>>Here are the lines from sysinternals File Monitor
>>where
>>the backup is deleted:
>>1646 6:32:56 PM sqlservr.exe:3688
> OPEN
>> E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832
.B
>>AK
>> SUCCESS Options: Open Access: All
>>1647 6:32:56 PM sqlservr.exe:3688
> DELETE
>> E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832
.B
>>AK
>> SUCCESS
>>1648 6:32:56 PM sqlservr.exe:3688
> CLOSE
>> E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832
.B
>>AK
>> SUCCESS
>>
>>The error message in the errorlog is:
>>BACKUP failed to complete the command BACKUP LOG
>>[t_prod]
>>TO DISK = N'E:\Microsoft SQL
>>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832
.B
>>AK
>>'
>>WITH INIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT
>>Disk space is not an issue as there is over 60 gigs
>>free
>>and all the databases together barely total a 1 gig.
>>Backups from a script that run through a job
(without
>>the
>>maint plan wizard) succeed about 75% of the time but
>>about
>>25% of the time the job fails with the same errors
as
>>the
>>maint plan fails with. I'm still trying to get File
>>Monitor running at the time a backup from a script
>>fails,
>>but haven't been luckly enough to catch it at the
>>right
>>time yet.
>>Any information or suggestions to resolve this
problem
>>are
>>greatly appreaciated as I've been trying to figure
>>this
>>out for too long. If there is any other information
>>that
>>would be useful to post, please let me know.
>>Thanks,
>>Trent.
>>.
>>.
>>.
>>.
>.
>|||Just wanted to say a quick thank you to all that replied
with suggestions. I finally stumbled accross the problem
when looking for something else. Someone had entered an
invalid named pipes alias in the client network utility on
the server. Deleting this alias solved all my problems
and my backups (full and trans log) have not failed since
removing this alias.
Trent.
>--Original Message--
>I have a server with Windows 2000 SP4+hotfixes, SQL
Server
>2000 SE SP3A+hotfix. The server has 4 user databases
(the
>largest being around 500 megs). 3 of the databases
backup
>fine but 1 database will not take a full backup or a
>transaction log backup from the maintenance plan. The
job
>runs and succeeds but the file is not where it should be
>(nor anywhere else).
>The development server with the same configuration does
>not have this same problem, and it takes about 7 seconds
>to backup the database in question.
>I have monitored the directory that the file should be
>created in while the job is running, and the file does
>create and stays there for the 5 to 7 seconds that the
job
>is running and then it dissappears.
>I have also used File Monitor from sysinternals and
>monitored the system while a backup is being run and
>sqlservr.exe and sqlmaint.exe are the only things that
>touch the .BAK file. Sqlservr.exe reads and writes to
>the .BAK file numerous times and the last thing that
>happens is sqlservr.exe opens the .BAK file, then Deletes
>the .BAK file, and then closes the .BAK file. No other
>applications besides sqlservr.exe and sqlmaint.exe touch
>the .BAK file. The index numbers in File Monitor do not
>skip any numbers.
>Here are the lines from sysinternals File Monitor where
>the backup is deleted:
>1646 6:32:56 PM sqlservr.exe:3688 OPEN
> E:\Microsoft SQL
>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS Options: Open Access: All
>1647 6:32:56 PM sqlservr.exe:3688 DELETE
> E:\Microsoft SQL
>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS
>1648 6:32:56 PM sqlservr.exe:3688 CLOSE
> E:\Microsoft SQL
>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS
>
>The error message in the errorlog is:
>BACKUP failed to complete the command BACKUP LOG [t_prod]
>TO DISK = N'E:\Microsoft SQL
>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK'
>WITH INIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT
>Disk space is not an issue as there is over 60 gigs free
>and all the databases together barely total a 1 gig.
>Backups from a script that run through a job (without the
>maint plan wizard) succeed about 75% of the time but
about
>25% of the time the job fails with the same errors as the
>maint plan fails with. I'm still trying to get File
>Monitor running at the time a backup from a script fails,
>but haven't been luckly enough to catch it at the right
>time yet.
>Any information or suggestions to resolve this problem
are
>greatly appreaciated as I've been trying to figure this
>out for too long. If there is any other information that
>would be useful to post, please let me know.
>Thanks,
>Trent.
>.
>|||For the job, if you right-click it, select "history" and (important) check "show step details", do
you see any error messages that help? I assume that the result from the job execution as failed, of
course.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kathy" <anonymous@.discussions.microsoft.com> wrote in message
news:011f01c3b5f2$c460b0d0$a501280a@.phx.gbl...
> I received a responds to my posting and the tech made the
> point that really made sense to me. I knew transactions
> rolled back. But he mentioned that a failed backup job
> rolls back.
> I decided to create a backup job manually. It worked!!!
> Then I scheduled it through the job interfaced and it ran
> as scheduled.
> To clarify this to you:
> Backup Job FAILS when I do the following steps:
> Create a backup device, i.e. otg_backupdevice
> Right click on the new backup device (otg_backupdevice)
> and select "Backup a Database"
> The SQL Server backup wizard window appears, select the
> database (otg) to backup, add the backup device
> (otg_backupdevice) and schedule the backup to occur once
> a day at 12:00 A.M.
> Then ran the job from the jobs window
> The job fails.
> Backup Job Succeeds when I do the following steps:
> Create a backup device, i.e. otg_backupdevice
> Go to the job window
> Right-click and select new job
> Name the job - OTG backup to device
> Click on the "Steps" tab and click on "New Step"
> Select OTG as the database
> In the command window I enter "backup database otg to
> otg_backupdevice"
> Click on the "Schedule" tab and schedule it to backup at
> 10:00 am
> The job suceeds
> I suspect there is a problem with the backup wizard'
>
> Kathy
> >--Original Message--
> >Hi Matthew,
> >
> >Thanks for the reply. I always backup to a filename,
> >mainly because that's just the way I normally do it. I
> >have experimented with backing up to a device and it
> does
> >seem to work (although I never extensively tested it)
> but
> >it's starting to look like my only option.
> >
> >I run DBCC checks weekly and they never find any errors
> (I
> >double checked the last 6 weeks of dbcc checks to make
> >sure they didn't find any errors)
> >
> >Trent.
> >>--Original Message--
> >>I don't know if this will make any difference or not,
> but
> >>do you specify the filename as the destination for the
> >>backup, or do you create a backup device first?
> >>
> >>Also, have you run a DBCC consistency chekc on the
> >>database you're attempting to backup? Maybe the actual
> >>failure is on the read from the DB side of the backup
> >>process?
> >>
> >>Just some early in the morning thoughts that may or may
> >>not help.
> >>
> >>Matthew Bando
> >>BandoM@.CSCTechnologies (remove).com
> >>
> >>
> >>--Original Message--
> >>Sorry, forgot to comment on the retention. It has
> >>always
> >>been set at 5 days but I have tried turning the
> >>retention
> >>off and it makes no difference.
> >>
> >>Trent.
> >>--Original Message--
> >>any clues from the NT's system or application error
> log
> >>files?
> >>
> >>this may be dumb thing to ask, but make sure that
> >>backup
> >>retention option is properly set in db maint plan.
> >>
> >>--Original Message--
> >>I have a server with Windows 2000 SP4+hotfixes, SQL
> >>Server
> >>2000 SE SP3A+hotfix. The server has 4 user
> databases
> >>(the
> >>largest being around 500 megs). 3 of the databases
> >>backup
> >>fine but 1 database will not take a full backup or a
> >>transaction log backup from the maintenance plan.
> The
> >>job
> >>runs and succeeds but the file is not where it
> should
> >>be
> >>(nor anywhere else).
> >>
> >>The development server with the same configuration
> >>does
> >>not have this same problem, and it takes about 7
> >>seconds
> >>to backup the database in question.
> >>
> >>I have monitored the directory that the file should
> be
> >>created in while the job is running, and the file
> does
> >>create and stays there for the 5 to 7 seconds that
> the
> >>job
> >>is running and then it dissappears.
> >>
> >>I have also used File Monitor from sysinternals and
> >>monitored the system while a backup is being run and
> >>sqlservr.exe and sqlmaint.exe are the only things
> that
> >>touch the .BAK file. Sqlservr.exe reads and writes
> to
> >>the .BAK file numerous times and the last thing that
> >>happens is sqlservr.exe opens the .BAK file, then
> >>Deletes
> >>the .BAK file, and then closes the .BAK file. No
> >>other
> >>applications besides sqlservr.exe and sqlmaint.exe
> >>touch
> >>the .BAK file. The index numbers in File Monitor do
> >>not
> >>skip any numbers.
> >>
> >>Here are the lines from sysinternals File Monitor
> >>where
> >>the backup is deleted:
> >>1646 6:32:56 PM sqlservr.exe:3688
> > OPEN
> >> E:\Microsoft SQL
> >>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832
> .B
> >>AK
> >> SUCCESS Options: Open Access: All
> >>1647 6:32:56 PM sqlservr.exe:3688
> > DELETE
> >> E:\Microsoft SQL
> >>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832
> .B
> >>AK
> >> SUCCESS
> >>1648 6:32:56 PM sqlservr.exe:3688
> > CLOSE
> >> E:\Microsoft SQL
> >>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832
> .B
> >>AK
> >> SUCCESS
> >>
> >>
> >>The error message in the errorlog is:
> >>BACKUP failed to complete the command BACKUP LOG
> >>[t_prod]
> >>TO DISK = N'E:\Microsoft SQL
> >>Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832
> .B
> >>AK
> >>'
> >>
> >>WITH INIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT
> >>
> >>Disk space is not an issue as there is over 60 gigs
> >>free
> >>and all the databases together barely total a 1 gig.
> >>
> >>Backups from a script that run through a job
> (without
> >>the
> >>maint plan wizard) succeed about 75% of the time but
> >>about
> >>25% of the time the job fails with the same errors
> as
> >>the
> >>maint plan fails with. I'm still trying to get File
> >>Monitor running at the time a backup from a script
> >>fails,
> >>but haven't been luckly enough to catch it at the
> >>right
> >>time yet.
> >>
> >>Any information or suggestions to resolve this
> problem
> >>are
> >>greatly appreaciated as I've been trying to figure
> >>this
> >>out for too long. If there is any other information
> >>that
> >>would be useful to post, please let me know.
> >>
> >>Thanks,
> >>Trent.
> >>.
> >>
> >>.
> >>
> >>.
> >>
> >>.
> >>
> >.
> >

Tuesday, March 27, 2012

Backup SQL server using msbackup

Backup SQL Files

Backup Sql database to mapped network drive on another server prob

Backup Sql database to mapped network drive on another server prob

Tuesday, March 20, 2012

Backup Restore question

Monday, March 19, 2012

Backup procedure

Backup procedure

Sunday, March 11, 2012

Backup Problem

Backup Problem

Backup Problem

Friday, February 24, 2012

Backup ODBC on Windows 2000 Server?

Backup not showing in schedule

Monday, February 13, 2012

Backup job succeeds but not actually backing up

sql backup files

Blog Archive

About Me