backup file being reported as corrupt

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

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

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

backup file being reported as corrupt

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

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

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

backup error - single user mode needed?

Hi Everyone,
One of my clients is running SQL 2000 SP4 and the Data Maintenance Plan
logs shows a log with...
Database master: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
SQL Server Driver][SQL Server]Repair statement not processed. Database
needs to be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
...
[5] Database master: Database Backup...
The backup was not performed since data verification errors were found.

There are similar messages for databases model and msdb.
Questions:
- should master, model and msdb be included in the daily backup?
- how does one get into single user mode and repair this?
Thanks
Richard
Hi,
In your maintenance plan, uncheck the Attempt to repair minor problems
checkbox in Integrity tab.
With that checkbox unchecked, integrity checks will be still done on the
databases and as a good practice verify your maintenance logs
for any integroty errors. If there is any error you could manually correct
it.
Thanks
Hari
SQL Server MVP
"Richard Fagen" <no_spam@.aol.com> wrote in message
news:O3JolEukFHA.2792@.TK2MSFTNGP10.phx.gbl...
> Hi Everyone,
> One of my clients is running SQL 2000 SP4 and the Data Maintenance Plan
> logs shows a log with...
> --
> Database master: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Repair statement not processed. Database
> needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> ...
> [5] Database master: Database Backup...
> The backup was not performed since data verification errors were
> found.
>
> --
> There are similar messages for databases model and msdb.
> Questions:
> - should master, model and msdb be included in the daily backup?
> - how does one get into single user mode and repair this?
> Thanks
> Richard
|||hi Richard,
Richard Fagen wrote:
is the disk damaged?

> Questions:
> - should master, model and msdb be included in the daily backup?
usually not.. only when server modifications occur, like implementing new
jobs, alerts, logins and so on...

> - how does one get into single user mode and repair this?
http://msdn.microsoft.com/library/de...start_4nhh.asp
http://msdn.microsoft.com/library/de...kprst_4g4w.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea Montanari wrote:
> hi Richard,
> Richard Fagen wrote:
> is the disk damaged?
always forget the Maintenance Plan is that intrusive (and bugged ) ..
always prefer self written jobs :D
yep.. Hari is right
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Hari,
I'll uncheck the box and try it again.
I use SQL as part of SBS 2000/2003 and that seems to go against what
I've read in books. All the books say be sure to check that box
However, since your both SQL MVPs and Andrea has helped me out before,
I'll go with your recommendations.
Richard
p.s. the one who says to check it, Harry Brelsford, is an MVP too, but
of SBS not SQL
Hari Prasad wrote:
> Hi,
> In your maintenance plan, uncheck the Attempt to repair minor problems
> checkbox in Integrity tab.
> With that checkbox unchecked, integrity checks will be still done on the
> databases and as a good practice verify your maintenance logs
> for any integroty errors. If there is any error you could manually correct
> it.
> Thanks
> Hari
> SQL Server MVP
>
> "Richard Fagen" <no_spam@.aol.com> wrote in message
> news:O3JolEukFHA.2792@.TK2MSFTNGP10.phx.gbl...
>
>
|||Hi Andrea,

> is the disk damaged?
Not that I know of. Besides, I remotely logged into a few SBS clients
to test the data maintenance plan with backing up master, model and msdb
and they all had the same error messages.
I guess it wouldn't hurt to have everyone run a scandisk (I try to get
them to do it on their own, but they never remember) and doublecheck.

> usually not.. only when server modifications occur, like implementing new
> jobs, alerts, logins and so on...
I can manually backup master, model and msdb via the Enterprise Manager,
just not via the maintenance plan.
I hope they fix this in SQL 2005

> http://msdn.microsoft.com/library/de...start_4nhh.asp
> http://msdn.microsoft.com/library/de...kprst_4g4w.asp
Thanks for the links
Richard
|||hi Richard,
Richard Fagen wrote:
> Hi Andrea,
> Not that I know of. Besides, I remotely logged into a few SBS clients
> to test the data maintenance plan with backing up master, model and
> msdb and they all had the same error messages.
see Hari post... Maintenance Plan is "intrusive" by default and tryes to
perform activities requiring single user access...

> I guess it wouldn't hurt to have everyone run a scandisk (I try to get
> them to do it on their own, but they never remember) and doublecheck.
but ok. checking disk integrity is always neat :D

> I can manually backup master, model and msdb via the Enterprise
> Manager, just not via the maintenance plan.
again, see Hari answer...

> I hope they fix this in SQL 2005
SQLExpress will not include the Agent, so no Maintenance Plan at all... but
worse, no jobs at all, and that includes both admin/house keeping jobs as
other dataload,dml jobs
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||From Microsoft Article 290622:
BUG: Databse Manintenance Plan on System Databases faile on Intergrity
Check if "Attempt to repair minor problems" is Selected
... The database maintenance plan attempts to place the system databases
in the single user mode in order to execute CkDBRepair. However the
Master and MSDB databases cannot be placed in single user mode because of
constantly running system processes like LAZYWRITER, LOG WRITER, SQL
Agent... The repair step fails to run and the job reports a failure...
There is no fix for tis bug as yet. The workaround is to disable the
"Attempt to repair any minor problems" option in your maintenance plan.
Lyle
In article <O3JolEukFHA.2792@.TK2MSFTNGP10.phx.gbl>, no_spam@.aol.com
says...
> Hi Everyone,
> One of my clients is running SQL 2000 SP4 and the Data Maintenance Plan
> logs shows a log with...
> --
> Database master: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Repair statement not processed. Database
> needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> ...
> [5] Database master: Database Backup...
> The backup was not performed since data verification errors were found.
>
> --
> There are similar messages for databases model and msdb.
> Questions:
> - should master, model and msdb be included in the daily backup?
> - how does one get into single user mode and repair this?
> Thanks
> Richard
>
|||Hi Lyle,
Thanks for the article. Finally, a great explanation
Richard
Lyle wrote:

> From Microsoft Article 290622:
> BUG: Databse Manintenance Plan on System Databases faile on Intergrity
> Check if "Attempt to repair minor problems" is Selected
> ... The database maintenance plan attempts to place the system databases
> in the single user mode in order to execute CkDBRepair. However the
> Master and MSDB databases cannot be placed in single user mode because of
> constantly running system processes like LAZYWRITER, LOG WRITER, SQL
> Agent... The repair step fails to run and the job reports a failure...
> There is no fix for tis bug as yet. The workaround is to disable the
> "Attempt to repair any minor problems" option in your maintenance plan.
> Lyle
sql

Backup Error - File in Use

I am running SQL Server on a Win 2003 Server box. Large Backups are scheduled
which are about 10gigs in size. I have a batch file that executes after the
backup is completed, which moves the 10 gb file to a different drive on the
same box. On random occassions, the backups fail with the following message:
"Unable to delete preexisting D:\Path\db.dat: The process cannot access the
file because it is being used by another process."
I am not sure what kind of locks could be set on the backup dat file to
prevent it from being deleted before the next backup. The only other program
accessing the backup file is the batch file I am running, which performs the
copy operation after each backup.
Any ideas on this will help... Thanks!
Hi,
First question that arise in my mind is, why you are taking backup in one
drive and using batch cmd to move it to different drive on the same box? Why
can't you directly save your sql backup to the destination drive? With the
maintenance plan you can delete the old backups also.
Thanks
GYK
|||I should have explained this in my first post. Sorry about that. The backups
are created by a SMS 2003 service. Each time the backup runs, the service
updates the contents of the folder with the latest backups. I cannot
configure it to backup to a specific location, because the process is not
user-driven. The idea of moving it to a different drive is merely for disk
space reasons and being able to store atleast 2 previous backups on a
different drive. The batch file manages this and the actual transfer of the
latest backup file.
I hope this explains things... Thanks....
"GYK" wrote:

> Hi,
> First question that arise in my mind is, why you are taking backup in one
> drive and using batch cmd to move it to different drive on the same box? Why
> can't you directly save your sql backup to the destination drive? With the
> maintenance plan you can delete the old backups also.
> Thanks
> GYK
|||I find it really hard to believe you can not specify the location of the
backup. In any case do you have a tape backup process that at some point
copies that to tape? That is the most likely cause. If you can't figure
out how to change the location I would suggest you create your own scheduled
job that issues the backup in the correct place. Backing up the database to
the same drive is only asking for trouble.
Andrew J. Kelly SQL MVP
"bd2103" <bd2103@.discussions.microsoft.com> wrote in message
news:5FBE5C09-D670-48AC-A8C2-414AB9614702@.microsoft.com...[vbcol=seagreen]
>I should have explained this in my first post. Sorry about that. The
>backups
> are created by a SMS 2003 service. Each time the backup runs, the service
> updates the contents of the folder with the latest backups. I cannot
> configure it to backup to a specific location, because the process is not
> user-driven. The idea of moving it to a different drive is merely for disk
> space reasons and being able to store atleast 2 previous backups on a
> different drive. The batch file manages this and the actual transfer of
> the
> latest backup file.
> I hope this explains things... Thanks....
> "GYK" wrote:
|||Tape Backups are in place. That is a good point. I will check into this soon
and post about when they are scheduled. The issue of having the backup on the
same drive I think can be avoided through this process as we will be
archiving the last few backups on a different drive. Thanks for your
input......
"Andrew J. Kelly" wrote:

> I find it really hard to believe you can not specify the location of the
> backup. In any case do you have a tape backup process that at some point
> copies that to tape? That is the most likely cause. If you can't figure
> out how to change the location I would suggest you create your own scheduled
> job that issues the backup in the correct place. Backing up the database to
> the same drive is only asking for trouble.
> --
> Andrew J. Kelly SQL MVP
>
> "bd2103" <bd2103@.discussions.microsoft.com> wrote in message
> news:5FBE5C09-D670-48AC-A8C2-414AB9614702@.microsoft.com...
>
>
|||It's not only when it is scheduled. I have seen tape backup software hold
locks on files for days when they get screwed up.
Andrew J. Kelly SQL MVP
"bd2103" <bd2103@.discussions.microsoft.com> wrote in message
news:238DD2E4-535F-44BC-A382-473E9142B24B@.microsoft.com...[vbcol=seagreen]
> Tape Backups are in place. That is a good point. I will check into this
> soon
> and post about when they are scheduled. The issue of having the backup on
> the
> same drive I think can be avoided through this process as we will be
> archiving the last few backups on a different drive. Thanks for your
> input......
> "Andrew J. Kelly" wrote:
sql

Backup Error - File in Use

I am running SQL Server on a Win 2003 Server box. Large Backups are scheduled
which are about 10gigs in size. I have a batch file that executes after the
backup is completed, which moves the 10 gb file to a different drive on the
same box. On random occassions, the backups fail with the following message:
"Unable to delete preexisting D:\Path\db.dat: The process cannot access the
file because it is being used by another process."
I am not sure what kind of locks could be set on the backup dat file to
prevent it from being deleted before the next backup. The only other program
accessing the backup file is the batch file I am running, which performs the
copy operation after each backup.
Any ideas on this will help... Thanks!Hi,
First question that arise in my mind is, why you are taking backup in one
drive and using batch cmd to move it to different drive on the same box? Why
can't you directly save your sql backup to the destination drive? With the
maintenance plan you can delete the old backups also.
Thanks
GYK|||I should have explained this in my first post. Sorry about that. The backups
are created by a SMS 2003 service. Each time the backup runs, the service
updates the contents of the folder with the latest backups. I cannot
configure it to backup to a specific location, because the process is not
user-driven. The idea of moving it to a different drive is merely for disk
space reasons and being able to store atleast 2 previous backups on a
different drive. The batch file manages this and the actual transfer of the
latest backup file.
I hope this explains things... Thanks....
"GYK" wrote:
> Hi,
> First question that arise in my mind is, why you are taking backup in one
> drive and using batch cmd to move it to different drive on the same box? Why
> can't you directly save your sql backup to the destination drive? With the
> maintenance plan you can delete the old backups also.
> Thanks
> GYK|||I find it really hard to believe you can not specify the location of the
backup. In any case do you have a tape backup process that at some point
copies that to tape? That is the most likely cause. If you can't figure
out how to change the location I would suggest you create your own scheduled
job that issues the backup in the correct place. Backing up the database to
the same drive is only asking for trouble.
--
Andrew J. Kelly SQL MVP
"bd2103" <bd2103@.discussions.microsoft.com> wrote in message
news:5FBE5C09-D670-48AC-A8C2-414AB9614702@.microsoft.com...
>I should have explained this in my first post. Sorry about that. The
>backups
> are created by a SMS 2003 service. Each time the backup runs, the service
> updates the contents of the folder with the latest backups. I cannot
> configure it to backup to a specific location, because the process is not
> user-driven. The idea of moving it to a different drive is merely for disk
> space reasons and being able to store atleast 2 previous backups on a
> different drive. The batch file manages this and the actual transfer of
> the
> latest backup file.
> I hope this explains things... Thanks....
> "GYK" wrote:
>> Hi,
>> First question that arise in my mind is, why you are taking backup in one
>> drive and using batch cmd to move it to different drive on the same box?
>> Why
>> can't you directly save your sql backup to the destination drive? With
>> the
>> maintenance plan you can delete the old backups also.
>> Thanks
>> GYK|||Tape Backups are in place. That is a good point. I will check into this soon
and post about when they are scheduled. The issue of having the backup on the
same drive I think can be avoided through this process as we will be
archiving the last few backups on a different drive. Thanks for your
input......
"Andrew J. Kelly" wrote:
> I find it really hard to believe you can not specify the location of the
> backup. In any case do you have a tape backup process that at some point
> copies that to tape? That is the most likely cause. If you can't figure
> out how to change the location I would suggest you create your own scheduled
> job that issues the backup in the correct place. Backing up the database to
> the same drive is only asking for trouble.
> --
> Andrew J. Kelly SQL MVP
>
> "bd2103" <bd2103@.discussions.microsoft.com> wrote in message
> news:5FBE5C09-D670-48AC-A8C2-414AB9614702@.microsoft.com...
> >I should have explained this in my first post. Sorry about that. The
> >backups
> > are created by a SMS 2003 service. Each time the backup runs, the service
> > updates the contents of the folder with the latest backups. I cannot
> > configure it to backup to a specific location, because the process is not
> > user-driven. The idea of moving it to a different drive is merely for disk
> > space reasons and being able to store atleast 2 previous backups on a
> > different drive. The batch file manages this and the actual transfer of
> > the
> > latest backup file.
> >
> > I hope this explains things... Thanks....
> >
> > "GYK" wrote:
> >
> >> Hi,
> >>
> >> First question that arise in my mind is, why you are taking backup in one
> >> drive and using batch cmd to move it to different drive on the same box?
> >> Why
> >> can't you directly save your sql backup to the destination drive? With
> >> the
> >> maintenance plan you can delete the old backups also.
> >>
> >> Thanks
> >> GYK
>
>|||It's not only when it is scheduled. I have seen tape backup software hold
locks on files for days when they get screwed up.
--
Andrew J. Kelly SQL MVP
"bd2103" <bd2103@.discussions.microsoft.com> wrote in message
news:238DD2E4-535F-44BC-A382-473E9142B24B@.microsoft.com...
> Tape Backups are in place. That is a good point. I will check into this
> soon
> and post about when they are scheduled. The issue of having the backup on
> the
> same drive I think can be avoided through this process as we will be
> archiving the last few backups on a different drive. Thanks for your
> input......
> "Andrew J. Kelly" wrote:
>> I find it really hard to believe you can not specify the location of the
>> backup. In any case do you have a tape backup process that at some point
>> copies that to tape? That is the most likely cause. If you can't figure
>> out how to change the location I would suggest you create your own
>> scheduled
>> job that issues the backup in the correct place. Backing up the database
>> to
>> the same drive is only asking for trouble.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "bd2103" <bd2103@.discussions.microsoft.com> wrote in message
>> news:5FBE5C09-D670-48AC-A8C2-414AB9614702@.microsoft.com...
>> >I should have explained this in my first post. Sorry about that. The
>> >backups
>> > are created by a SMS 2003 service. Each time the backup runs, the
>> > service
>> > updates the contents of the folder with the latest backups. I cannot
>> > configure it to backup to a specific location, because the process is
>> > not
>> > user-driven. The idea of moving it to a different drive is merely for
>> > disk
>> > space reasons and being able to store atleast 2 previous backups on a
>> > different drive. The batch file manages this and the actual transfer of
>> > the
>> > latest backup file.
>> >
>> > I hope this explains things... Thanks....
>> >
>> > "GYK" wrote:
>> >
>> >> Hi,
>> >>
>> >> First question that arise in my mind is, why you are taking backup in
>> >> one
>> >> drive and using batch cmd to move it to different drive on the same
>> >> box?
>> >> Why
>> >> can't you directly save your sql backup to the destination drive? With
>> >> the
>> >> maintenance plan you can delete the old backups also.
>> >>
>> >> Thanks
>> >> GYK
>>

Backup Error

Hi
I have SQL7 running and is being backed up with ArcServ 9. The server has ArcServ SQL backup client running but the backups keep coming up with errors when trying to backup the contents of the \MSSQL\FTDATA directory. The message is "The process cannot access the file because it is being used by another process.
Is it important to backup the FTDATA directory
cheer
nuvethe arcserve is not able to backup ftdata directory cause its used by sql
server.. it might have mdf and ldf files in it..
ur already using arcserv sql backup client .. why dont u use that to backup
sql database instead of backing up the whole directory. (if u dont have
arcserve open file backup agent this directory will never be backed up)
other alternate is backup the database using sql jobs in to one of the
directory may be d:\backup and backup this directory by arcserve .
Hope this would solve your query ..
Regards,
Mayur
"Nuve" <anonymous@.discussions.microsoft.com> wrote in message
news:8B7872E2-9CBB-4E44-AC44-E1A03E9525AB@.microsoft.com...
> Hi
> I have SQL7 running and is being backed up with ArcServ 9. The server has
ArcServ SQL backup client running but the backups keep coming up with errors
when trying to backup the contents of the \MSSQL\FTDATA directory. The
message is "The process cannot access the file because it is being used by
another process."
> Is it important to backup the FTDATA directory?
> cheers
> nuve|||The ftdata directory does not have any databases in it (.mdf or .ldf files).
This is the location where the full text search indexes and catalogs are
kept.
If Arcserve will not backup open files, then you will have to bring the
databases, or the server down in order to backup that directory... and if
you are using full text search it is important. ( But can be regenerated
from the data in the tables if you must... It would just take a while.)
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Nuve" <anonymous@.discussions.microsoft.com> wrote in message
news:8B7872E2-9CBB-4E44-AC44-E1A03E9525AB@.microsoft.com...
> Hi
> I have SQL7 running and is being backed up with ArcServ 9. The server has
ArcServ SQL backup client running but the backups keep coming up with errors
when trying to backup the contents of the \MSSQL\FTDATA directory. The
message is "The process cannot access the file because it is being used by
another process."
> Is it important to backup the FTDATA directory?
> cheers
> nuve

Backup Error

Hi
I have SQL7 running and is being backed up with ArcServ 9. The server has Ar
cServ SQL backup client running but the backups keep coming up with errors w
hen trying to backup the contents of the \MSSQL\FTDATA directory. The messag
e is "The process cannot ac
cess the file because it is being used by another process."
Is it important to backup the FTDATA directory?
cheers
nuvethe arcserve is not able to backup ftdata directory cause its used by sql
server.. it might have mdf and ldf files in it..
ur already using arcserv sql backup client .. why dont u use that to backup
sql database instead of backing up the whole directory. (if u dont have
arcserve open file backup agent this directory will never be backed up)
other alternate is backup the database using sql jobs in to one of the
directory may be d:\backup and backup this directory by arcserve .
Hope this would solve your query ..
Regards,
Mayur
"Nuve" <anonymous@.discussions.microsoft.com> wrote in message
news:8B7872E2-9CBB-4E44-AC44-E1A03E9525AB@.microsoft.com...
> Hi
> I have SQL7 running and is being backed up with ArcServ 9. The server has
ArcServ SQL backup client running but the backups keep coming up with errors
when trying to backup the contents of the \MSSQL\FTDATA directory. The
message is "The process cannot access the file because it is being used by
another process."
> Is it important to backup the FTDATA directory?
> cheers
> nuve|||The ftdata directory does not have any databases in it (.mdf or .ldf files).
This is the location where the full text search indexes and catalogs are
kept.
If Arcserve will not backup open files, then you will have to bring the
databases, or the server down in order to backup that directory... and if
you are using full text search it is important. ( But can be regenerated
from the data in the tables if you must... It would just take a while.)
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Nuve" <anonymous@.discussions.microsoft.com> wrote in message
news:8B7872E2-9CBB-4E44-AC44-E1A03E9525AB@.microsoft.com...
> Hi
> I have SQL7 running and is being backed up with ArcServ 9. The server has
ArcServ SQL backup client running but the backups keep coming up with errors
when trying to backup the contents of the \MSSQL\FTDATA directory. The
message is "The process cannot access the file because it is being used by
another process."
> Is it important to backup the FTDATA directory?
> cheers
> nuvesql

Backup Device Locations Help

I am a newbie in SQL Server Administration. I decided to make the move within my company that no one else wanted.

I have SQL Server 2000 running at this time; users are connecting and using it. My question I guess pertains to the backups.

I have two drives setup for data and transactions logs (D: E:)

My backups are running but backups are being sent to the d:systems data\mssql\backup folder. once backups are comlete I normally copy the backups to a network share that is being backed up nightly to tape.

My question is this:

How do i create a device location under backup properties to point to a network share? i would like for the backups to go straight to a network drive so i do not have to copy the backups every morning when i come in.Its not good idea to perform backup over network share.
Instead schedule the job to copy those backup files to the network share using norma DOS Copy commands.

Ensure SQLAgent has required privileges to carry on this task of copying files on the network share.|||Originally posted by Satya
Its not good idea to perform backup over network share.
Instead schedule the job to copy those backup files to the network share using norma DOS Copy commands.

Ensure SQLAgent has required privileges to carry on this task of copying files on the network share.

--------------------------
Can you explain or point me to an article on using the DOS Copy command and how to use it with SQL Server? It's not something that I have used.

And what required permissions should the SQLAgent have in order to perform this type of task? the account that I created for the server itself has domain admin privileges. Would this pertain to the SQLAgent and help it perfrom the above tasks?|||Originally posted by Arra2
--------------------------
Can you explain or point me to an article on using the DOS Copy command and how to use it with SQL Server? It's not something that I have used.

And what required permissions should the SQLAgent have in order to perform this type of task? the account that I created for the server itself has domain admin privileges. Would this pertain to the SQLAgent and help it perfrom the above tasks?

easiest thing to do is type xcopy /? or copy /? at a command prompt, which will give you the syntax and definitions of the options

put that in a batch file and schedule it with 'at' or the Task Scheduler, you can also add that step to your MSSQL job if you want|||Thanks. That helps.|||Originally posted by Satya
Its not good idea to perform backup over network share.
Instead schedule the job to copy those backup files to the network share using norma DOS Copy commands.

Ensure SQLAgent has required privileges to carry on this task of copying files on the network share.

I've never heard a valid argument against network backup devices. Here's BOL excerpt:

Add a network disk backup device
This example shows a remote disk backup device. The name under which SQL Server was started must have permissions to that remote file.

USE master
EXEC sp_addumpdevice 'disk', 'networkdevice',
'\\servername\sharename\path\filename.ext'|||Though the facility is provided to perform backups over network, in a real-time environment you will have troubles such as process will block other connections while performing backups.

I agree and allow for small databases which are below 1GB in size, if not I approach other way to copy the local backup file.

As far as my experience is concerned its not a good approach on a enterprise wide network hosted SQL databases.:) :rolleyes:|||Satya,

How will a backup process possibly block other connections? I certainly respect your experience, but it makes me wonder...

Here we have a dedicated file server with 1.5T disk space, that we use to store backup files for a 1-day retention. All 120 (!!!) servers are being backed up onto this server. The disk is organized into subdirectories with names corresponding to source SQL Server names. Once all the backups are complete the tape backup kicks in. The only problems we have occasionally (a couple of times a year) is when there is a network glitch. But the jobs a written in such a way that the backup job continues processing remaining databases after creating an exception job that would contain failed backups. At the end the job would report an error and DBA on-call gets notified and resolves the issue by running the exception job . In the past 5 years that exception job run failed only once, when the network team failed to inform us that the LAN would be down during the certain time frame. Once Change Management was implemented 4 years ago we never had any issues with this approach since!|||rdjabarov

Thanks for your explanation and my intention is not to contradict your statements, I'd delivered what was experienced and you had already facted that situation and if the network and infrastructure is well built then you can test the process and deploy it.

Backup Databases

I am running SQL2000 SP4 on a Win2000 server and getting following error when
trying to perform a backup of a database within Enterprise Manager.
"Error 22272: Cannot load the DLL xpstart.dll, or one of the DLL's it
references. Reason 127(The specified procedure could not be found.). I have
looked any references to this error message, but yet to find anything on it.
Wondering if someone can assist me.
thanks,
JayHi
If backing up the datrabase from a different PC works, then I would think
re-installing the client tools may help solve this.
John
"Jay" wrote:
> I am running SQL2000 SP4 on a Win2000 server and getting following error when
> trying to perform a backup of a database within Enterprise Manager.
> "Error 22272: Cannot load the DLL xpstart.dll, or one of the DLL's it
> references. Reason 127(The specified procedure could not be found.). I have
> looked any references to this error message, but yet to find anything on it.
> Wondering if someone can assist me.
> thanks,
> Jay
>|||Jay wrote:
> I am running SQL2000 SP4 on a Win2000 server and getting following error when
> trying to perform a backup of a database within Enterprise Manager.
> "Error 22272: Cannot load the DLL xpstart.dll, or one of the DLL's it
> references. Reason 127(The specified procedure could not be found.). I have
> looked any references to this error message, but yet to find anything on it.
> Wondering if someone can assist me.
> thanks,
> Jay
>
Open up Query Analyzer and use the T-SQL BACKUP command - does it work?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Backup Databases

I am running SQL2000 SP4 on a Win2000 server and getting following error whe
n
trying to perform a backup of a database within Enterprise Manager.
"Error 22272: Cannot load the DLL xpstart.dll, or one of the DLL's it
references. Reason 127(The specified procedure could not be found.). I have
looked any references to this error message, but yet to find anything on it.
Wondering if someone can assist me.
thanks,
JayHi
If backing up the datrabase from a different PC works, then I would think
re-installing the client tools may help solve this.
John
"Jay" wrote:

> I am running SQL2000 SP4 on a Win2000 server and getting following error w
hen
> trying to perform a backup of a database within Enterprise Manager.
> "Error 22272: Cannot load the DLL xpstart.dll, or one of the DLL's it
> references. Reason 127(The specified procedure could not be found.). I ha
ve
> looked any references to this error message, but yet to find anything on i
t.
> Wondering if someone can assist me.
> thanks,
> Jay
>|||Jay wrote:
> I am running SQL2000 SP4 on a Win2000 server and getting following error w
hen
> trying to perform a backup of a database within Enterprise Manager.
> "Error 22272: Cannot load the DLL xpstart.dll, or one of the DLL's it
> references. Reason 127(The specified procedure could not be found.). I ha
ve
> looked any references to this error message, but yet to find anything on i
t.
> Wondering if someone can assist me.
> thanks,
> Jay
>
Open up Query Analyzer and use the T-SQL BACKUP command - does it work?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Backup Behaviour During Full/Diff/Log Backups Running Concurrently

If log backups are started while a full backup is already running, what if
any effect occurs to the full backup? In SQL Server 2000 I think that all
log backups are paused if a full is already running. I can't find any MS
articles that explains explicitly what the interaction/behaviour or result is
if full/diff/log backup jobs for the same database should overlap. Thanx.
-CqlboyThe backup that started first will block other backups. for example if you
start the log backup and then database full backup(log backup still running)
the full database will be blocked on log backup to finish and vice
versa...also same behavior true for differential backup.
"Cqlboy" wrote:
> If log backups are started while a full backup is already running, what if
> any effect occurs to the full backup? In SQL Server 2000 I think that all
> log backups are paused if a full is already running. I can't find any MS
> articles that explains explicitly what the interaction/behaviour or result is
> if full/diff/log backup jobs for the same database should overlap. Thanx.
> -Cqlboy|||Thanx. I've heard that if you execute a DIFF while FULL is running it will
error out. This has been fixed in 2005. I'm more interested in finding out
what if a FULL can maintain transactional consistency if a LOG backup was
performed during execution of the FULL. Log backups truncate the inactive
portion of the log. A FULL uses the transaction log as part of it's strategy
to compile the FULL backup.
"harvinder" wrote:
> The backup that started first will block other backups. for example if you
> start the log backup and then database full backup(log backup still running)
> the full database will be blocked on log backup to finish and vice
> versa...also same behavior true for differential backup.
> "Cqlboy" wrote:
> > If log backups are started while a full backup is already running, what if
> > any effect occurs to the full backup? In SQL Server 2000 I think that all
> > log backups are paused if a full is already running. I can't find any MS
> > articles that explains explicitly what the interaction/behaviour or result is
> > if full/diff/log backup jobs for the same database should overlap. Thanx.
> > -Cqlboy

Backup Behaviour During Full/Diff/Log Backups Running Concurrently

If log backups are started while a full backup is already running, what if
any effect occurs to the full backup? In SQL Server 2000 I think that all
log backups are paused if a full is already running. I can't find any MS
articles that explains explicitly what the interaction/behaviour or result i
s
if full/diff/log backup jobs for the same database should overlap. Thanx.
-CqlboyThe backup that started first will block other backups. for example if you
start the log backup and then database full backup(log backup still running)
the full database will be blocked on log backup to finish and vice
versa...also same behavior true for differential backup.
"Cqlboy" wrote:

> If log backups are started while a full backup is already running, what if
> any effect occurs to the full backup? In SQL Server 2000 I think that all
> log backups are paused if a full is already running. I can't find any MS
> articles that explains explicitly what the interaction/behaviour or result
is
> if full/diff/log backup jobs for the same database should overlap. Thanx.
> -Cqlboy

Backup Behaviour During Full/Diff/Log Backups Running Concurrently

If log backups are started while a full backup is already running, what if
any effect occurs to the full backup? In SQL Server 2000 I think that all
log backups are paused if a full is already running. I can't find any MS
articles that explains explicitly what the interaction/behaviour or result is
if full/diff/log backup jobs for the same database should overlap. Thanx.
-Cqlboy
The backup that started first will block other backups. for example if you
start the log backup and then database full backup(log backup still running)
the full database will be blocked on log backup to finish and vice
versa...also same behavior true for differential backup.
"Cqlboy" wrote:

> If log backups are started while a full backup is already running, what if
> any effect occurs to the full backup? In SQL Server 2000 I think that all
> log backups are paused if a full is already running. I can't find any MS
> articles that explains explicitly what the interaction/behaviour or result is
> if full/diff/log backup jobs for the same database should overlap. Thanx.
> -Cqlboy

Backup and restore to another database

We are running SQL Server 2000 Standard Edition
Every night, I want to take a backup of our production database and
restore it to another database that is used for reporting/DR
purposes.
My thinking was as follows:
(1) Setup a job on the source (production) to create the backup to a
named file
(2) Setup a job on the destination (DR) sometime later to restore the
backup
Is this the best way to do this?
Perhaps I could create 1 job to do both?Hi
Do you want to do that on dayly,weekly period?
Take a look at WITH MOVE option in the BOL
"PromisedOyster" <PromisedOyster@.hotmail.com> wrote in message
news:1173960621.133170.194740@.l77g2000hsb.googlegroups.com...
> We are running SQL Server 2000 Standard Edition
> Every night, I want to take a backup of our production database and
> restore it to another database that is used for reporting/DR
> purposes.
> My thinking was as follows:
> (1) Setup a job on the source (production) to create the backup to a
> named file
> (2) Setup a job on the destination (DR) sometime later to restore the
> backup
> Is this the best way to do this?
> Perhaps I could create 1 job to do both?
>|||Hello,
If your database size is not huge you could do the way you mentioned. If the
database is really big take a look into the LOGSHIPPING option.
In SQL 2000 standard you may need to manually configure logshipping. take a
look into the below URL:-
http://www.sqlmag.com/Article/Artic...rver_23231.html
Thanks
Hari
"PromisedOyster" <PromisedOyster@.hotmail.com> wrote in message
news:1173960621.133170.194740@.l77g2000hsb.googlegroups.com...
> We are running SQL Server 2000 Standard Edition
> Every night, I want to take a backup of our production database and
> restore it to another database that is used for reporting/DR
> purposes.
> My thinking was as follows:
> (1) Setup a job on the source (production) to create the backup to a
> named file
> (2) Setup a job on the destination (DR) sometime later to restore the
> backup
> Is this the best way to do this?
> Perhaps I could create 1 job to do both?
>

Backup and restore to another database

We are running SQL Server 2000 Standard Edition
Every night, I want to take a backup of our production database and
restore it to another database that is used for reporting/DR
purposes.
My thinking was as follows:
(1) Setup a job on the source (production) to create the backup to a
named file
(2) Setup a job on the destination (DR) sometime later to restore the
backup
Is this the best way to do this?
Perhaps I could create 1 job to do both?Hi
Do you want to do that on dayly,weekly period?
Take a look at WITH MOVE option in the BOL
"PromisedOyster" <PromisedOyster@.hotmail.com> wrote in message
news:1173960621.133170.194740@.l77g2000hsb.googlegroups.com...
> We are running SQL Server 2000 Standard Edition
> Every night, I want to take a backup of our production database and
> restore it to another database that is used for reporting/DR
> purposes.
> My thinking was as follows:
> (1) Setup a job on the source (production) to create the backup to a
> named file
> (2) Setup a job on the destination (DR) sometime later to restore the
> backup
> Is this the best way to do this?
> Perhaps I could create 1 job to do both?
>|||Hello,
If your database size is not huge you could do the way you mentioned. If the
database is really big take a look into the LOGSHIPPING option.
In SQL 2000 standard you may need to manually configure logshipping. take a
look into the below URL:-
http://www.sqlmag.com/Article/ArticleID/23231/sql_server_23231.html
Thanks
Hari
"PromisedOyster" <PromisedOyster@.hotmail.com> wrote in message
news:1173960621.133170.194740@.l77g2000hsb.googlegroups.com...
> We are running SQL Server 2000 Standard Edition
> Every night, I want to take a backup of our production database and
> restore it to another database that is used for reporting/DR
> purposes.
> My thinking was as follows:
> (1) Setup a job on the source (production) to create the backup to a
> named file
> (2) Setup a job on the destination (DR) sometime later to restore the
> backup
> Is this the best way to do this?
> Perhaps I could create 1 job to do both?
>

Backup and restore to another database

We are running SQL Server 2000 Standard Edition
Every night, I want to take a backup of our production database and
restore it to another database that is used for reporting/DR
purposes.
My thinking was as follows:
(1) Setup a job on the source (production) to create the backup to a
named file
(2) Setup a job on the destination (DR) sometime later to restore the
backup
Is this the best way to do this?
Perhaps I could create 1 job to do both?
Hi
Do you want to do that on dayly,weekly period?
Take a look at WITH MOVE option in the BOL
"PromisedOyster" <PromisedOyster@.hotmail.com> wrote in message
news:1173960621.133170.194740@.l77g2000hsb.googlegr oups.com...
> We are running SQL Server 2000 Standard Edition
> Every night, I want to take a backup of our production database and
> restore it to another database that is used for reporting/DR
> purposes.
> My thinking was as follows:
> (1) Setup a job on the source (production) to create the backup to a
> named file
> (2) Setup a job on the destination (DR) sometime later to restore the
> backup
> Is this the best way to do this?
> Perhaps I could create 1 job to do both?
>
|||Hello,
If your database size is not huge you could do the way you mentioned. If the
database is really big take a look into the LOGSHIPPING option.
In SQL 2000 standard you may need to manually configure logshipping. take a
look into the below URL:-
http://www.sqlmag.com/Article/ArticleID/23231/sql_server_23231.html
Thanks
Hari
"PromisedOyster" <PromisedOyster@.hotmail.com> wrote in message
news:1173960621.133170.194740@.l77g2000hsb.googlegr oups.com...
> We are running SQL Server 2000 Standard Edition
> Every night, I want to take a backup of our production database and
> restore it to another database that is used for reporting/DR
> purposes.
> My thinking was as follows:
> (1) Setup a job on the source (production) to create the backup to a
> named file
> (2) Setup a job on the destination (DR) sometime later to restore the
> backup
> Is this the best way to do this?
> Perhaps I could create 1 job to do both?
>

Backup and restore databases In my MSDE

Hi all! How can i backup databases which are running in my MSDE 2000 & then how can i restore them with all the data, Yes i know that their is no visual tool for doing this in MSDE but can we do it with scripts, if Yes then howThe SQL to backup a database in MSDE is:

BACKUP DATABASE { database_name | @.database_name_var }
TO < backup_device > [ ,...n ]
[ WITH
 [ BLOCKSIZE = { blocksize | @.blocksize_variable } ]
 [ [ , ] DESCRIPTION = { 'text' | @.text_variable } ]
 [ [ , ] DIFFERENTIAL ]
 [ [ , ] EXPIREDATE = { date | @.date_var }
 | RETAINDAYS = { days | @.days_var } ]
 [ [ , ] PASSWORD = { password | @.password_variable } ]
 [ [ , ] FORMAT | NOFORMAT ]
 [ [ , ] { INIT | NOINIT } ]
 [ [ , ] MEDIADESCRIPTION = { 'text' | @.text_variable } ]
 [ [ , ] MEDIANAME = { media_name | @.media_name_variable } ]
 [ [ , ] MEDIAPASSWORD = { mediapassword | @.mediapassword_variable } ]
 [ [ , ] NAME = { backup_set_name | @.backup_set_name_var } ]
 [ [ , ] { NOSKIP | SKIP } ]
 [ [ , ] { NOREWIND | REWIND } ]
 [ [ , ] { NOUNLOAD | UNLOAD } ]
 [ [ , ] RESTART ]
 [ [ , ] STATS [ = percentage ] ]
]

and to restore:

RESTORE DATABASE { database_name | @.database_name_var }
[ FROM < backup_device > [ ,...n ] ]
[ WITH
 [ RESTRICTED_USER ]
 [ [ , ] FILE = { file_number | @.file_number } ]
 [ [ , ] PASSWORD = { password | @.password_variable } ]
 [ [ , ] MEDIANAME = { media_name | @.media_name_variable } ]
 [ [ , ] MEDIAPASSWORD = { mediapassword | @.mediapassword_variable } ]
 [ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]
 [ ,...n ]
 [ [ , ] KEEP_REPLICATION ]
 [ [ , ] { NORECOVERY | RECOVERY | STANDBY = {undo_file_name|@.undo_file_name_var} } ]
 [ [ , ] { NOREWIND | REWIND } ]
 [ [ , ] { NOUNLOAD | UNLOAD } ]
 [ [ , ] REPLACE ]
 [ [ , ] RESTART ]
 [ [ , ] STATS [ = percentage ] ]
]

Things in square brackets are not strictly speaking required.

Backup and Restore

Running SQL Server 2K. I use transactional Replication.
It is sometimes necessary to take precautionary backups during
business hours for various reasons. However, I have noted
that backups made while replication is running can not be made
without causing an error during Restore. Is there a
workaround for this, or does Replication need to be diabled
prior to
backup?
Thanks,
Tom
Tom,
if you mean the error from the Log Reader Agent because it detects that the
Distributor is ahead of the Publisher, you can run sp_replrestart in the
publication database with no parameters. This forces replication to continue
even if the Distributor and some Subscribers may now have data that the
Publisher no longer has. If not, please can you post up the error message.
HTH,
Paul Ibison
|||exactly what is the error you get during the restore?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
<tkane@.yamner.com> wrote in message
news:c14f01c4385d$32a6e950$a401280a@.phx.gbl...
> Running SQL Server 2K. I use transactional Replication.
> It is sometimes necessary to take precautionary backups during
> business hours for various reasons. However, I have noted
> that backups made while replication is running can not be made
> without causing an error during Restore. Is there a
> workaround for this, or does Replication need to be diabled
> prior to
> backup?
> Thanks,
> Tom

Backup and Restor MS SQL Server 2005

I have a community server running in a hiring host, and I also install it on localhost. I would like to know how to backup the database from hosting and restore it to the site running on my localhost so that two sites is the same and in case there is an error from the hosting I can recover my database.

I can connect to my hosting database easily using SQL server Studio 2005, but the hosting does not offer backup through control panel. Could you please instruct me how to backup and restore database by SQL Server Management Studio.

Thank you very much,

Use Microsoft SQL Server Management Studio->Management->Maintenance plans to backup you databases and then on localhost use a query like "restore database TESTfrom disk = 'c:\TEST.BAK'WITH MOVE 'TEST' TO 'C:\TEST\TEST.mdf'"|||

Thank you Konan,

I'll try right now

Backup and Restiore

Running SQL Server 2K. I use transactional Replication.
It is sometimes necessary to take precautionary backups
during business hours for various reasons. However, I have
noted that backups made while replication is running can
not be made without causing an error during Restore. Is
there a workaround for this, or does Replication need to be
diabled prior to backup?
Thanks,
TomWhat error do you see during restore? To keep replication during a restore
you have to use TSQL and specify the KEEP_Replication option. However,
without this option it should restore fine, it will just strip out the
replication. The option is not available in Enterprise Manager.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.