Backup File I/o Error

Guys,

Apologies if this is in the wrong section. I have a SQL 2000 database which I backup to a folder on the hard disk using the following command in a Server Agent:

BACKUP DATABASE [Multilog] TO DISK = N'C:\MSSQL7\BACKUP\multilog_backup' WITH INIT , NOUNLOAD , NAME = N'Multilog backup', NOSKIP , STATS = 10, NOFORMAT

Recently it has been failing with the following error message:

Nonrecoverable I/O error occurred on file 'C:\Multilog Database Files\Data\MultiLog_data.MDF'. [SQLSTATE 42000] (Error 3271) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

Any idea what could be causing this?

Any help would be greatly appreciated.

Sean.

Try some of the method(s) in the following kb:

http://support.microsoft.com/default.aspx/kb/904804

Is this an SP4 server? Looks like it may have been upgraded from SQL 7.0, is that correct?

|||

Chad,

Just noticed the following error on the logs:

I/O error 23(Data error (cyclic redundancy check).) detected during read of BUF pointer = 0x14243d80, page ptr = 0x1607c000, pageid = (0x1:0x1ae), dbid = 5, status = 0x801, file = C:\Multilog Database Files\Data\MultiLog_data.MDF..

Would this indicate a fault with the actual database file? Any idea what could be done to check and fix?

|||

Yeah, that would indicate a data integrity issue within your DB file. Try running a DBCC CHECKDB on the database in question and see what you get for output. Depending on the output of that, you could try using the FIX options with the CHECKDB command, rebuilding indexes, etc.

Backup Failure

I've encountered an error while performing a database backup. I can't seem t
o
find this error anywhere:
BACKUP failed to complete the command master.dbo.sp_sqlexec;1
Does anyone have any insight into this error?
Thank You!How do you execute the backup? For troubleshooting purposes, try executing a
BACKUP command from
Query analyzer.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:6DCC9B4B-4EE8-428A-842B-7E191FFE5AB6@.microsoft.com...
> I've encountered an error while performing a database backup. I can't seem
to
> find this error anywhere:
> BACKUP failed to complete the command master.dbo.sp_sqlexec;1
> Does anyone have any insight into this error?
> Thank You!|||The backup is executed as a scheduled job. This job has been running for
months without any issues - then suddenly...
When run manually, works just fine...
"Tibor Karaszi" wrote:

> How do you execute the backup? For troubleshooting purposes, try executing
a BACKUP command from
> Query analyzer.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
> news:6DCC9B4B-4EE8-428A-842B-7E191FFE5AB6@.microsoft.com...
>|||Is it a TSQL job? How many jobsteps does the job contain? Where did you get
the error message from?
The job output of a job step in the job?
If you are uncertain, define a different output file for each jobstep and se
e exactly which jobstep
this message is from. Then post the TSQL commands you have in that jobstep.
I'm asking because the
error you posted doesn't look like an error that the BACKUP command would pr
oduce.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:D2BB3DBC-BFC5-4581-877B-6BE65F7E0E23@.microsoft.com...
> The backup is executed as a scheduled job. This job has been running for
> months without any issues - then suddenly...
> When run manually, works just fine...
> "Tibor Karaszi" wrote:
>

Backup fails using SMO with big databases

Hello
I'm seeing the backup using SMO fails with big databases (>15GB). I
have a 2GB database and I can see the command go thru. in Profiler but
after it's about 50-70% done, I get an exception saying backup failed.
There is no details on the SQLServer log.

Here's the code snippet:

bk.Initialize = m_backupInit;
bk.PercentCompleteNotification = 10;
bk.PercentComplete += new
PercentCompleteEventHandler(bk_PercentComplete);
bk.SqlBackup(m_Server);

Here's the command as captured by SQL-Profiler:
BACKUP DATABASE [Test_MODEL] TO DISK = N'D:\Temp
\Test_Model_Backup.dat' WITH NOFORMAT, INIT, NOSKIP, REWIND,
NOUNLOAD, STATS = 10

Has anyone else seen this. The wierd thing is it works using old SQL-
DMO.

thanks
SunitOn Feb 8, 11:42 pm, "sjoshi" <sjo...@.ingr.comwrote:

Quote:

Originally Posted by

Hello
I'm seeing the backup using SMO fails with big databases (>15GB). I
have a 2GB database and I can see the command go thru. in Profiler but
after it's about 50-70% done, I get an exception saying backup failed.
There is no details on the SQLServer log.
>
Here's the code snippet:
>
bk.Initialize = m_backupInit;
bk.PercentCompleteNotification = 10;
bk.PercentComplete += new
PercentCompleteEventHandler(bk_PercentComplete);
bk.SqlBackup(m_Server);
>
Here's the command as captured by SQL-Profiler:
BACKUP DATABASE [Test_MODEL] TO DISK = N'D:\Temp
\Test_Model_Backup.dat' WITH NOFORMAT, INIT, NOSKIP, REWIND,
NOUNLOAD, STATS = 10
>
Has anyone else seen this. The wierd thing is it works using old SQL-
DMO.
>
thanks
Sunit

I would check the event log as well, there may more information there.
I have too encountered situations which are similair, however, never
where there was absolutely no information in any of the typical log
locations. 15 gig is not paticulary large, I am sure there must be
more to it. What is the result when you execute via OSQL prompt?
Cheers,
Gsql

Backup fails for big databases

Hello

I'm seeing that the backup using SMO fails with big databases (>15GB).
I have a 20GB database and I can see the command go thru. in Profiler
but after it's about 50-70% done, I get an exception saying backup
failed. There is no details on the SQLServer log.

Here's the code snippet:

bk.Initialize = m_backupInit;
bk.PercentCompleteNotification = 10;
bk.PercentComplete += new
PercentCompleteEventHandler(bk_PercentComplete);
bk.SqlBackup(m_Server);

Here's the command as captured by SQL-Profiler:
BACKUP DATABASE [Test_MODEL] TO DISK = N'D:\Temp
\Test_Model_Backup.dat' WITH NOFORMAT, INIT, NOSKIP, REWIND,
NOUNLOAD, STATS = 10

Has anyone else seen this. The wierd thing is it works using old SQL-
DMO.

thanks
Sunit

Hi,

the standard timeout for the command is as far as I can remember 600 seconds, you can extend this by setting the following command:

Server svr = new Server(".");
svr.ConnectionContext.StatementTimeout = 6000;

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||That seems to have fixed it. Thanks a lot.

Sunit

BACKUP failed to complete the command sp_prepexec;1

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

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

Rob.

Hi,

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

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

actual in the KB:

HOW TO: Troubleshoot Application Performance Issues

How to monitor SQL Server 2000 blocking

tosc

BACKUP failed to complete the command sp_prepexec;1

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

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

Rob.

Hi,

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

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

actual in the KB:

HOW TO: Troubleshoot Application Performance Issues

How to monitor SQL Server 2000 blocking

tosc

BACKUP failed to complete the command sp_execute;1

Does anyone know why message appears and how to correct problem?
During a database backup the message of "BACKUP failed to complete the
command sp_execute;1" appears in the SQL log. The backup seems to complete
sucessful. The backup command is as follow:
BACKUP DATABASE @.dbname TO DISK = \\ServerA\Backup\BackupXDB.dat WITH
DIFFERENTIAL, INIT
as you may not the backup is being directed to a network drive
SQL Env: Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
Thanks in advance
Did you try to apply the differential backup to see if it's valid? Also, if
you run the same command from Query Analyzer, do you get the same error?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"SQLDBA" <SQLDBA@.discussions.microsoft.com> wrote in message
news:4649BE64-B000-4692-A59D-B7DA0E1F1C5D@.microsoft.com...
> Does anyone know why message appears and how to correct problem?
> During a database backup the message of "BACKUP failed to complete the
> command sp_execute;1" appears in the SQL log. The backup seems to
complete
> sucessful. The backup command is as follow:
> BACKUP DATABASE @.dbname TO DISK = \\ServerA\Backup\BackupXDB.dat WITH
> DIFFERENTIAL, INIT
> as you may not the backup is being directed to a network drive
> SQL Env: Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
> Thanks in advance
>

Backup failed to complete the command backup database

Backup failed to complete the command backup database [ ] TO VIRTUAL DEVICE = ' { 853D3FC0 - 45EA -85B1 - 54F0EA379CAC } 24 ' WITH SNAPSHOT , BUFFERCOUNT = 1 , BLOCKSIZE = 1024

Raghu could pls post the error you get while executing this command ? ? ? anything from the error log ?

Thanks

Deepak

sql

Backup Failed to complete command SP_EXECUTESQL;1

hi,
in my event log I have the alarm Backup failed to complete command
sp_executesql;1
The point is that I didn't find any scheduled backup jobs and I didn't find
any *.bak file relative to the DB mentioned in the alarm. (I can see the DB
file name in the text window at the bottom of the detail in Windows event
log )
I suspect that it's hidden in a stored procedure or somewhere else. How can
I find it?
I have two DB on the system pointed by this alarm and they're quite large 20
& 25gig so if a backup file was create I think I wouldn't have difficulty
to find them.
I didn't find any infos on the net about this alarm so if someone can give
me some the help me to search deeper.
Regards
MarcDo you have any third party products installed on the server, that perform
database backups? Worth checking under Start > Programs
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"marc quirion" <mquirion@.videotron.ca> wrote in message
news:SfKFe.92489$mv2.1552703@.weber.videotron.net...
hi,
in my event log I have the alarm Backup failed to complete command
sp_executesql;1
The point is that I didn't find any scheduled backup jobs and I didn't find
any *.bak file relative to the DB mentioned in the alarm. (I can see the DB
file name in the text window at the bottom of the detail in Windows event
log )
I suspect that it's hidden in a stored procedure or somewhere else. How can
I find it?
I have two DB on the system pointed by this alarm and they're quite large 20
& 25gig so if a backup file was create I think I wouldn't have difficulty
to find them.
I didn't find any infos on the net about this alarm so if someone can give
me some the help me to search deeper.
Regards
Marc

Backup Error

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

Backup Device vs backup command

Hi,

Will backup get completed quickly (the time taken for backing up large databases) on backup device or using backup command? If backup device, why?

You are talking about 2 different things here. You use the backup command to backup the DB and the backup device to store the backed up files.

Unless I dont understand your question.

|||Hi Dinakar,

I need to know which one completes backup faster, backup device or by using backup command. I heard that backup device completes backup quicker than using backup command.

Thanks|||

It's the same procedure, whether you commit it via a GUI (window menu->next->next...) or command prompt (BACKUP command) it makes no difference.

In both cases you use a backup device to backup your database. If you are a learner you'll probably use the user friendly windows menu. If you are advanced you'll probably use the SQL command: BACKUP

|||

Have you checked the same on your environment?

IMHO I don't see much difference in between and it makes a lot of difference betwen normal disks & RAID based ones.

BTW do you have any issues in your environment for such behaviour, if so let us know.

|||Hi Satya,

Im using RAID disks, when backing up 200GB of database, backup device completes faster than backup command. A 30 min difference..

Thanks|||If SQL Server stripes physical devices that have different input/output (I/O) throughputs, SQL Server optimizes for speed. This means that faster devices receive more backup data that is written to disk than the slower device in the same period of time. In SQL Server 2000, regardless of the I/O throughput differences, SQL Server tries to distribute the backup data evenly to the devices. In this case, the slower disk may become a backup bottleneck in terms of performance. If increasing performance is your primary goal, you must avoid using the slow disk in striping and use disks with comparable throughputs instead.

Backup databases and Checkpoint

A simple question.
I have a database that's being used by several developers. Prior to making
a backup
using the backup command, should I issue a checkpoint command to write
memory
pages to disk? My database is in simple recovery mode. I understand that
uncommitted
changes are not backed up (as I would expect).
If I don't need to checkpoint, then under what circumstance is it used?No need to issue a checkpoint prior to making a backup regardless of the
recovery mode of the database. If there is any uncommitted transaction, SQL
Server will undo any changes (i.e. rollback the transaction) when it recover
s
the database upon restore. And if there are dirty pages of a committed
transaction (i.e. the data page does not reflect the state of the committed
transaction), SQL Server will roll forward these pages during a recovery.
A checkpoint flushes all dirty pages (including both data and log) of a
database to disk, and is parimarily used to shorten the recovery time when
the database is recovered, particularly during the SQL instance startup.
Linchi
"Dodo Lurker" wrote:

> A simple question.
> I have a database that's being used by several developers. Prior to makin
g
> a backup
> using the backup command, should I issue a checkpoint command to write
> memory
> pages to disk? My database is in simple recovery mode. I understand th
at
> uncommitted
> changes are not backed up (as I would expect).
> If I don't need to checkpoint, then under what circumstance is it used?
>
>|||Thank you
Say I just want to issue the checkpoint so any "dirty" pages are written to
disk that may have been
part of a committed transaction. You're saying that the backup will have
those in-memory changes applied to the backup?
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:7E8E6183-B338-45B8-8543-984660531A42@.microsoft.com...
> No need to issue a checkpoint prior to making a backup regardless of the
> recovery mode of the database. If there is any uncommitted transaction,
SQL
> Server will undo any changes (i.e. rollback the transaction) when it
recovers
> the database upon restore. And if there are dirty pages of a committed
> transaction (i.e. the data page does not reflect the state of the
committed[vbcol=seagreen]
> transaction), SQL Server will roll forward these pages during a recovery.
> A checkpoint flushes all dirty pages (including both data and log) of a
> database to disk, and is parimarily used to shorten the recovery time when
> the database is recovered, particularly during the SQL instance startup.
> Linchi
> "Dodo Lurker" wrote:
>
making[vbcol=seagreen]
that[vbcol=seagreen]|||> Say I just want to issue the checkpoint so any "dirty" pages are written to">
> disk that may have been
> part of a committed transaction. You're saying that the backup will have
> those in-memory changes applied to the backup?
Yes. And, as I remember, SQL Server does a checkpoint as a part of the datab
ase backup (at the
starting point of the backup). The important point about backup is that a da
tabase backup will
include the committed transactions and all in-flight transactions will be ro
lled backup (if you do
the restore of that backup using the RECOVERY option).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dodo Lurker" <none@.noemailplease> wrote in message
news:sJidnWEYmrDr32HZnZ2dnUVZ_r2dnZ2d@.co
mcast.com...
> Thank you
> Say I just want to issue the checkpoint so any "dirty" pages are written t
o
> disk that may have been
> part of a committed transaction. You're saying that the backup will have
> those in-memory changes applied to the backup?
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:7E8E6183-B338-45B8-8543-984660531A42@.microsoft.com...
> SQL
> recovers
> committed
> making
> that
>|||If you read the "Checkpoint" information in the Books Online, you will see
this:
"Before a database backup, the Database Engine automatically performs a
checkpoint so that all changes to the database pages are contained in the
backup. "
HTH
Kalen Delaney, SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:y9adnb3C0uOACmbZnZ2dnUVZ_sednZ2d@.co
mcast.com...
>A simple question.
> I have a database that's being used by several developers. Prior to
> making
> a backup
> using the backup command, should I issue a checkpoint command to write
> memory
> pages to disk? My database is in simple recovery mode. I understand
> that
> uncommitted
> changes are not backed up (as I would expect).
> If I don't need to checkpoint, then under what circumstance is it used?
>sql

Backup databases and Checkpoint

A simple question.
I have a database that's being used by several developers. Prior to making
a backup
using the backup command, should I issue a checkpoint command to write
memory
pages to disk? My database is in simple recovery mode. I understand that
uncommitted
changes are not backed up (as I would expect).
If I don't need to checkpoint, then under what circumstance is it used?No need to issue a checkpoint prior to making a backup regardless of the
recovery mode of the database. If there is any uncommitted transaction, SQL
Server will undo any changes (i.e. rollback the transaction) when it recovers
the database upon restore. And if there are dirty pages of a committed
transaction (i.e. the data page does not reflect the state of the committed
transaction), SQL Server will roll forward these pages during a recovery.
A checkpoint flushes all dirty pages (including both data and log) of a
database to disk, and is parimarily used to shorten the recovery time when
the database is recovered, particularly during the SQL instance startup.
Linchi
"Dodo Lurker" wrote:
> A simple question.
> I have a database that's being used by several developers. Prior to making
> a backup
> using the backup command, should I issue a checkpoint command to write
> memory
> pages to disk? My database is in simple recovery mode. I understand that
> uncommitted
> changes are not backed up (as I would expect).
> If I don't need to checkpoint, then under what circumstance is it used?
>
>|||Thank you
Say I just want to issue the checkpoint so any "dirty" pages are written to
disk that may have been
part of a committed transaction. You're saying that the backup will have
those in-memory changes applied to the backup?
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:7E8E6183-B338-45B8-8543-984660531A42@.microsoft.com...
> No need to issue a checkpoint prior to making a backup regardless of the
> recovery mode of the database. If there is any uncommitted transaction,
SQL
> Server will undo any changes (i.e. rollback the transaction) when it
recovers
> the database upon restore. And if there are dirty pages of a committed
> transaction (i.e. the data page does not reflect the state of the
committed
> transaction), SQL Server will roll forward these pages during a recovery.
> A checkpoint flushes all dirty pages (including both data and log) of a
> database to disk, and is parimarily used to shorten the recovery time when
> the database is recovered, particularly during the SQL instance startup.
> Linchi
> "Dodo Lurker" wrote:
> > A simple question.
> >
> > I have a database that's being used by several developers. Prior to
making
> > a backup
> > using the backup command, should I issue a checkpoint command to write
> > memory
> > pages to disk? My database is in simple recovery mode. I understand
that
> > uncommitted
> > changes are not backed up (as I would expect).
> >
> > If I don't need to checkpoint, then under what circumstance is it used?
> >
> >
> >|||> Say I just want to issue the checkpoint so any "dirty" pages are written to
> disk that may have been
> part of a committed transaction. You're saying that the backup will have
> those in-memory changes applied to the backup?
Yes. And, as I remember, SQL Server does a checkpoint as a part of the database backup (at the
starting point of the backup). The important point about backup is that a database backup will
include the committed transactions and all in-flight transactions will be rolled backup (if you do
the restore of that backup using the RECOVERY option).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dodo Lurker" <none@.noemailplease> wrote in message
news:sJidnWEYmrDr32HZnZ2dnUVZ_r2dnZ2d@.comcast.com...
> Thank you
> Say I just want to issue the checkpoint so any "dirty" pages are written to
> disk that may have been
> part of a committed transaction. You're saying that the backup will have
> those in-memory changes applied to the backup?
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:7E8E6183-B338-45B8-8543-984660531A42@.microsoft.com...
>> No need to issue a checkpoint prior to making a backup regardless of the
>> recovery mode of the database. If there is any uncommitted transaction,
> SQL
>> Server will undo any changes (i.e. rollback the transaction) when it
> recovers
>> the database upon restore. And if there are dirty pages of a committed
>> transaction (i.e. the data page does not reflect the state of the
> committed
>> transaction), SQL Server will roll forward these pages during a recovery.
>> A checkpoint flushes all dirty pages (including both data and log) of a
>> database to disk, and is parimarily used to shorten the recovery time when
>> the database is recovered, particularly during the SQL instance startup.
>> Linchi
>> "Dodo Lurker" wrote:
>> > A simple question.
>> >
>> > I have a database that's being used by several developers. Prior to
> making
>> > a backup
>> > using the backup command, should I issue a checkpoint command to write
>> > memory
>> > pages to disk? My database is in simple recovery mode. I understand
> that
>> > uncommitted
>> > changes are not backed up (as I would expect).
>> >
>> > If I don't need to checkpoint, then under what circumstance is it used?
>> >
>> >
>> >
>|||If you read the "Checkpoint" information in the Books Online, you will see
this:
"Before a database backup, the Database Engine automatically performs a
checkpoint so that all changes to the database pages are contained in the
backup. "
--
HTH
Kalen Delaney, SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:y9adnb3C0uOACmbZnZ2dnUVZ_sednZ2d@.comcast.com...
>A simple question.
> I have a database that's being used by several developers. Prior to
> making
> a backup
> using the backup command, should I issue a checkpoint command to write
> memory
> pages to disk? My database is in simple recovery mode. I understand
> that
> uncommitted
> changes are not backed up (as I would expect).
> If I don't need to checkpoint, then under what circumstance is it used?
>

Backup database with SQL to backup directory

I had write out a sql command to backup the database.

BACKUP DATABASE NorthWind
to disk = 'c:\NorthWind_20031113.bak'
with name = 'NorthWind_backup'

However, I intend to backup the database to the SQL server default backup directory, "\MSSQL7\Backup". How can I do that? Given that I don't know the SQL server were installed in C drive or D drive.

Another question was, can I do a integrity check using the sql command on the backup copy that i had created??

I'll be appreciate on the help that you offered.
Thanks.I tried this and it works, ie put the backup into the MSSQL/Backup folder

BACKUP DATABASE NorthWind
to disk = 'NorthWind_20031113.bak'
with name = 'NorthWind_backup'

Trick seems to be to not to specify a location and by default it used the backup folder

Mark|||Thank for your reply. I had try on the solution you given, however, the backup copy was placed to my "winnt\system32" directory. I had try both on my own pc and to backup remotely on the database server. Both of the trial was same where the backup copy being placed to the window system directory.|||The Holy book says :

If a relative path name is entered for a backup to disk, the backup file is placed in the default backup directory. This directory is set during installation and stored in the BackupDirectory registry key under

KEY_LOCAL_MACHINE\Software\Microsoft\ MSSQLServer\MSSQLServer.

Reffering again from the Holy Book for your second question

RESTORE VERIFYONLY
Verifies the backup but does not restore the backup. Checks to see that the backup set is complete and that all volumes are readable. However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. If the backup is valid, Microsoft SQL Server 2000 returns the message: "The backup set is valid."

Syntax
RESTORE VERIFYONLY
FROM < backup_device > [ ,...n ]
[ WITH
[ FILE = file_number ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] LOADHISTORY ]
[ [ , ] PASSWORD = { password | @.password_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @.mediapassword_variable } ]
[ [ , ] { NOREWIND | REWIND } ]
]

< backup_device > ::=
{
{ 'logical_backup_device_name' | @.logical_backup_device_name_var }
| { DISK | TAPE } =
{ 'physical_backup_device_name' | @.physical_backup_device_name_var }
}|||If a relative path name is entered for a backup to disk, the backup file is placed in the default backup directory. This directory is set during installation and stored in the BackupDirectory registry key under

KEY_LOCAL_MACHINE\Software\Microsoft\ MSSQLServer\MSSQLServer.

I also found the statement above at the "Holy Book", however, i can't really understand what it means by relative path name. If I had to go to registry to read for the path, then as I know I can't do it.
Actually now I'm writing a simple program to do the database backup at the client pc and the database server only have MSDE but no enterprise manager. So, I can't check for the backup path from the registry value cause my program was running at the client pc.
I'm really grateful for the help you offered. Thanks for the answer for the second question.

:D|||Relative path means ... specifying no drive or directory .. just the filename|||I had tried it before, but the backup copy were being create at the system directory, "c:\winnt\system32" insteed of the SQL Backup folder.|||what does the BackupDirectory registry key under

KEY_LOCAL_MACHINE\Software\Microsoft\ MSSQLServer\MSSQLServer point to ?

You can see it by opening regedit and navigating to the key|||I check on it already, is pointing to the sql path. D:\MSSQL7\Backup\|||Originally posted by coffytan
I check on it already, is pointing to the sql path. D:\MSSQL7\Backup\

Still backup gos to systerm Winnt ,, wierd !!!!!!!!

Backup Database hangs

SQL Server 2000

Backup database command hangs. The command I am using is:

BACKUP DATABASE @.DBName TO DISK = @.filename WITH INIT

Size of the database is 140GB and we have 210 GB free disk space.
sp_who status shows runnable.

Any help would be appreciated.

Thanks.How do you execute the command (analyzer?) and what do you mean when you say "Backup database command hangs."? How did you find out it hangs?|||Command is execeuted thru SQL Server Agent as..

BACKUP DATABASE [Assentor] TO DISK = 'D:\DB_BAK\AssentorDB2.BAK' WITH INIT , NOUNLOAD , NAME = N'Assentor backup', SKIP , STATS = 10, DESCRIPTION = 'Daily Assentor DB Backup', FORMAT , MEDIANAME = N'Assentor DB Backup', MEDIADESCRIPTION = N'Assentor DB Backup'

Generally it takes 2 hrs. to complete the backup, but since last few days it just hangs and sits there for hours. I tried to kill the job, it shows killed/rollback and takes hours to release the spid.

Thanks.|||Any ideas? Last backup job which kicked of at 12:00 noon is still in runnable state. I can provide more info. if someone needs to help me out.

Thanks|||Find the spid of the backup process, and see if anything is blocking the backup. If nothing is blocking the backup, see if the CPU and PHYSICAL_IO numbers are going up. That would indicate that the process is running, if slowly. If he numbers are not going up, check the waittype, waitresource, and lastwaittype columns for any resource contention problems.|||Four options (that I know of):

1. Create multiple filegroups and start backing up on a filegroup level;
2. Move to a third-party backup solution (SQLLiteSpeed)
3. Move to SAN
4. Combination of the three above|||When the spid appears to hang, substitute the spid for the ?? then run:SELECT cpu, physical_io FROM master.dbo.sysprocesses WHERE ?? = spid
WAITFOR DELAY '1:00'
SELECT cpu, physical_io FROM master.dbo.sysprocesses WHERE ?? = spidIf the numbers change (at all), then the spid is still running.

If the numbers don't change, you need to check the disk to see if the backup is growing. Sometimes the spid doing the backup can hang for a long time if the database dump is undergoing a "grow" operation.

-PatP|||Is it 1 hour or 1 minute?|||That should be only one minute, but maybe I goofed. I don't have any easy way to test it at the moment.

If the counters don't change for a minute, then I'd be willing to consider the task "hung" for most purposes.

-PatP|||The whole problem is the backup file is not created at all in this whole process. OR let me put it this way, IF a backup file already exists, ABCbackup.bak with date and time stamp of 10/19/2004 7:30 AM, IT SHOULD CHANGE as soon as I start another backup on 10/20/2004 7:30 AM and the size should start growing. BUT it doesn't happend that way.

Now that I killed the backup job, it shows KILLED/ROLLBACK state for almost 14 hrs. and is blocking the new backup job which kicked of today morning at 7:30 AM.

Thanks.|||The whole problem is the backup file is not created at all in this whole process. OR let me put it this way, IF a backup file already exists, ABCbackup.bak with date and time stamp of 10/19/2004 7:30 AM, IT SHOULD CHANGE as soon as I start another backup on 10/20/2004 7:30 AM and the size should start growing. BUT it doesn't happend that way.

Now that I killed the backup job, it shows KILLED/ROLLBACK state for almost 14 hrs. and is blocking the new backup job which kicked of today morning at 7:30 AM.

80 0 runnable ASSENTOR\MSSQL_Srv_Agent NYC-SRA-02 0 Assentor KILLED/ROLLBACK
80 3 runnable ASSENTOR\MSSQL_Srv_Agent NYC-SRA-02 0 NULL KILLED/ROLLBACK

Thanks.|||I opened a case with Microsoft. Will keep you updated on this issue...

Backup Database ?

Does the backup database command truncate the transaction log to the point
of the backup?
nivek
No
You must backup the log separately.
If you're using Simple Recovery model, use the backup log... with
truncate_only and then dbcc shrinkfile
to release disk back to the OS.
"nivek" <eckart_612@.hotmail.com> wrote in message
news:rI2dnf7GAuFAeqPfRVn-rQ@.centurytel.net...
> Does the backup database command truncate the transaction log to the point
> of the backup?
> --
> nivek
>
>
>

Backup Database ?

Does the backup database command truncate the transaction log to the point
of the backup?
--
nivekNo
You must backup the log separately.
If you're using Simple Recovery model, use the backup log... with
truncate_only and then dbcc shrinkfile
to release disk back to the OS.
"nivek" <eckart_612@.hotmail.com> wrote in message
news:rI2dnf7GAuFAeqPfRVn-rQ@.centurytel.net...
> Does the backup database command truncate the transaction log to the point
> of the backup?
> --
> nivek
>
>
>

Backup Database ?

Does the backup database command truncate the transaction log to the point
of the backup?
nivekNo
You must backup the log separately.
If you're using Simple Recovery model, use the backup log... with
truncate_only and then dbcc shrinkfile
to release disk back to the OS.
"nivek" <eckart_612@.hotmail.com> wrote in message
news:rI2dnf7GAuFAeqPfRVn-rQ@.centurytel.net...
> Does the backup database command truncate the transaction log to the point
> of the backup?
> --
> nivek
>
>
>

Backup data from MSSQL

Is there any way to backup(like method or command to run) data that were
entered into the database? Like saving all the data into another file(.eg
..txt), so that i'm able to clear certain tables in the database in a weekly
or monthly basis. As my system handles lots of data a day, so i need to
backup and clear the database to ensure that the system had enough space and
able to run fast enough. I know that there are few types of backup available
in the books i read up like 'Database backup', 'Differential database
backup', 'File and/or File group backup' and 'Transaction log backup', but i
cannot find any methods on how to use them. And i also didn't find the full
explaination for these backup. Which of these are better? Or are there any
better backup available?
Thanx..
Hi,
There are 2 options:-
1. Use BACKUP DATABASE command to backup your entire database This includes
all tables, procedures, views, indexes ,etc....(all objects
2. Use DTS or BCP OUT commands to export the contents of a table into a TXT
file. Later if need you can use DTS or BCP IN to import
the data back
BACKUP DATABASE
Backups the entire database into a .BAK file. After that you can delete the
huge transaction table with old data. If you require the data later you
could
use RESTORE DATABASe command to restore the database.
Execute the below command from Query analyzer to backup:-
BACKUP DATABASE <dbname> to DISK='D:\backup\dbname.BAK' with INIT -- With
INIT will overwrite the old backup file every time
COmmand to RESTORE
RESTORE DATABASE <dbname> from DISK='d:\backup\dbname.bak' -- More more
options see SQL Server books online
Thanks
Hari
SQL Server MVP
"yingying" <yingying@.discussions.microsoft.com> wrote in message
news:18D9692D-9F32-4187-AF78-63AD5D963EBD@.microsoft.com...
> Is there any way to backup(like method or command to run) data that were
> entered into the database? Like saving all the data into another file(.eg
> .txt), so that i'm able to clear certain tables in the database in a
> weekly
> or monthly basis. As my system handles lots of data a day, so i need to
> backup and clear the database to ensure that the system had enough space
> and
> able to run fast enough. I know that there are few types of backup
> available
> in the books i read up like 'Database backup', 'Differential database
> backup', 'File and/or File group backup' and 'Transaction log backup', but
> i
> cannot find any methods on how to use them. And i also didn't find the
> full
> explaination for these backup. Which of these are better? Or are there any
> better backup available?
> Thanx..

Backup data from MSSQL

Is there any way to backup(like method or command to run) data that were
entered into the database' Like saving all the data into another file(.eg
.txt), so that i'm able to clear certain tables in the database in a weekly
or monthly basis. As my system handles lots of data a day, so i need to
backup and clear the database to ensure that the system had enough space and
able to run fast enough. I know that there are few types of backup available
in the books i read up like 'Database backup', 'Differential database
backup', 'File and/or File group backup' and 'Transaction log backup', but i
cannot find any methods on how to use them. And i also didn't find the full
explaination for these backup. Which of these are better? Or are there any
better backup available?
Thanx..Hi,
There are 2 options:-
1. Use BACKUP DATABASE command to backup your entire database This includes
all tables, procedures, views, indexes ,etc....(all objects
2. Use DTS or BCP OUT commands to export the contents of a table into a TXT
file. Later if need you can use DTS or BCP IN to import
the data back
BACKUP DATABASE
--
Backups the entire database into a .BAK file. After that you can delete the
huge transaction table with old data. If you require the data later you
could
use RESTORE DATABASe command to restore the database.
Execute the below command from Query analyzer to backup:-
BACKUP DATABASE <dbname> to DISK='D:\backup\dbname.BAK' with INIT -- With
INIT will overwrite the old backup file every time
COmmand to RESTORE
RESTORE DATABASE <dbname> from DISK='d:\backup\dbname.bak' -- More more
options see SQL Server books online
Thanks
Hari
SQL Server MVP
"yingying" <yingying@.discussions.microsoft.com> wrote in message
news:18D9692D-9F32-4187-AF78-63AD5D963EBD@.microsoft.com...
> Is there any way to backup(like method or command to run) data that were
> entered into the database' Like saving all the data into another file(.eg
> .txt), so that i'm able to clear certain tables in the database in a
> weekly
> or monthly basis. As my system handles lots of data a day, so i need to
> backup and clear the database to ensure that the system had enough space
> and
> able to run fast enough. I know that there are few types of backup
> available
> in the books i read up like 'Database backup', 'Differential database
> backup', 'File and/or File group backup' and 'Transaction log backup', but
> i
> cannot find any methods on how to use them. And i also didn't find the
> full
> explaination for these backup. Which of these are better? Or are there any
> better backup available?
> Thanx..