DB Backup on a compressed drive

Is this a good idea to use compressed drive (Windows NTFS compression) for D
B
backup files?
What is the common practice and what are the risks?
Thanks,
VladVlad,
See http://support.microsoft.com/defaul...b;en-us;231347.
"Performing SQL Server database backups to disk files on compressed volumes
is also not recommended."
HTH
Jerry
"Vlad" <Vlad@.discussions.microsoft.com> wrote in message
news:579552B6-8EF5-4741-8F7E-B04BF8EABEF6@.microsoft.com...
> Is this a good idea to use compressed drive (Windows NTFS compression) for
> DB
> backup files?
> What is the common practice and what are the risks?
> --
> Thanks,
> Vlad|||That article applies to the database files themselves, not the backups.
Personally, I prefer not to use compressed drives for backup. I prefer
using a commercial SQL backup compression tool. This decreases the time to
get the data out of SQL, thus minimizing the impact on the host system.
Since I also prefer backing up directly to a network file share, this has
the additional benefit of reducing the network traffic.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uU8t$dWuFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Vlad,
> See http://support.microsoft.com/defaul...b;en-us;231347.
> "Performing SQL Server database backups to disk files on compressed
> volumes is also not recommended."
> HTH
> Jerry
> "Vlad" <Vlad@.discussions.microsoft.com> wrote in message
> news:579552B6-8EF5-4741-8F7E-B04BF8EABEF6@.microsoft.com...
>|||Geoff,
From the article:
"Performing SQL Server database backups to disk files on compressed volumes
is also not recommended. The failure modes are still under investigation,
but some evidence indicates that this backup is less reliable than backing
up to regular non-compressed volumes. If backups to compressed volumes are
performed, a complete DBCC should be run before each backup and after each
restore. The time required to run the DBCC should be factored into the
decision to use this backup technique. "
Jerry
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:OCyqJnWuFHA.1132@.TK2MSFTNGP10.phx.gbl...
> That article applies to the database files themselves, not the backups.
> Personally, I prefer not to use compressed drives for backup. I prefer
> using a commercial SQL backup compression tool. This decreases the time
> to get the data out of SQL, thus minimizing the impact on the host system.
> Since I also prefer backing up directly to a network file share, this has
> the additional benefit of reducing the network traffic.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uU8t$dWuFHA.3236@.TK2MSFTNGP14.phx.gbl...
>sql

DB Backup on a compressed drive

Is this a good idea to use compressed drive (Windows NTFS compression) for DB
backup files?
What is the common practice and what are the risks?
Thanks,
Vlad
Vlad,
See http://support.microsoft.com/default...;en-us;231347.
"Performing SQL Server database backups to disk files on compressed volumes
is also not recommended."
HTH
Jerry
"Vlad" <Vlad@.discussions.microsoft.com> wrote in message
news:579552B6-8EF5-4741-8F7E-B04BF8EABEF6@.microsoft.com...
> Is this a good idea to use compressed drive (Windows NTFS compression) for
> DB
> backup files?
> What is the common practice and what are the risks?
> --
> Thanks,
> Vlad
|||That article applies to the database files themselves, not the backups.
Personally, I prefer not to use compressed drives for backup. I prefer
using a commercial SQL backup compression tool. This decreases the time to
get the data out of SQL, thus minimizing the impact on the host system.
Since I also prefer backing up directly to a network file share, this has
the additional benefit of reducing the network traffic.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uU8t$dWuFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Vlad,
> See http://support.microsoft.com/default...;en-us;231347.
> "Performing SQL Server database backups to disk files on compressed
> volumes is also not recommended."
> HTH
> Jerry
> "Vlad" <Vlad@.discussions.microsoft.com> wrote in message
> news:579552B6-8EF5-4741-8F7E-B04BF8EABEF6@.microsoft.com...
>
|||Geoff,
From the article:
"Performing SQL Server database backups to disk files on compressed volumes
is also not recommended. The failure modes are still under investigation,
but some evidence indicates that this backup is less reliable than backing
up to regular non-compressed volumes. If backups to compressed volumes are
performed, a complete DBCC should be run before each backup and after each
restore. The time required to run the DBCC should be factored into the
decision to use this backup technique. "
Jerry
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:OCyqJnWuFHA.1132@.TK2MSFTNGP10.phx.gbl...
> That article applies to the database files themselves, not the backups.
> Personally, I prefer not to use compressed drives for backup. I prefer
> using a commercial SQL backup compression tool. This decreases the time
> to get the data out of SQL, thus minimizing the impact on the host system.
> Since I also prefer backing up directly to a network file share, this has
> the additional benefit of reducing the network traffic.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uU8t$dWuFHA.3236@.TK2MSFTNGP14.phx.gbl...
>

DB Backup on a compressed drive

Is this a good idea to use compressed drive (Windows NTFS compression) for DB
backup files?
What is the common practice and what are the risks?
--
Thanks,
VladVlad,
See http://support.microsoft.com/default.aspx?scid=kb;en-us;231347.
"Performing SQL Server database backups to disk files on compressed volumes
is also not recommended."
HTH
Jerry
"Vlad" <Vlad@.discussions.microsoft.com> wrote in message
news:579552B6-8EF5-4741-8F7E-B04BF8EABEF6@.microsoft.com...
> Is this a good idea to use compressed drive (Windows NTFS compression) for
> DB
> backup files?
> What is the common practice and what are the risks?
> --
> Thanks,
> Vlad|||That article applies to the database files themselves, not the backups.
Personally, I prefer not to use compressed drives for backup. I prefer
using a commercial SQL backup compression tool. This decreases the time to
get the data out of SQL, thus minimizing the impact on the host system.
Since I also prefer backing up directly to a network file share, this has
the additional benefit of reducing the network traffic.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uU8t$dWuFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Vlad,
> See http://support.microsoft.com/default.aspx?scid=kb;en-us;231347.
> "Performing SQL Server database backups to disk files on compressed
> volumes is also not recommended."
> HTH
> Jerry
> "Vlad" <Vlad@.discussions.microsoft.com> wrote in message
> news:579552B6-8EF5-4741-8F7E-B04BF8EABEF6@.microsoft.com...
>> Is this a good idea to use compressed drive (Windows NTFS compression)
>> for DB
>> backup files?
>> What is the common practice and what are the risks?
>> --
>> Thanks,
>> Vlad
>|||Geoff,
From the article:
"Performing SQL Server database backups to disk files on compressed volumes
is also not recommended. The failure modes are still under investigation,
but some evidence indicates that this backup is less reliable than backing
up to regular non-compressed volumes. If backups to compressed volumes are
performed, a complete DBCC should be run before each backup and after each
restore. The time required to run the DBCC should be factored into the
decision to use this backup technique. "
Jerry
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:OCyqJnWuFHA.1132@.TK2MSFTNGP10.phx.gbl...
> That article applies to the database files themselves, not the backups.
> Personally, I prefer not to use compressed drives for backup. I prefer
> using a commercial SQL backup compression tool. This decreases the time
> to get the data out of SQL, thus minimizing the impact on the host system.
> Since I also prefer backing up directly to a network file share, this has
> the additional benefit of reducing the network traffic.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uU8t$dWuFHA.3236@.TK2MSFTNGP14.phx.gbl...
>> Vlad,
>> See http://support.microsoft.com/default.aspx?scid=kb;en-us;231347.
>> "Performing SQL Server database backups to disk files on compressed
>> volumes is also not recommended."
>> HTH
>> Jerry
>> "Vlad" <Vlad@.discussions.microsoft.com> wrote in message
>> news:579552B6-8EF5-4741-8F7E-B04BF8EABEF6@.microsoft.com...
>> Is this a good idea to use compressed drive (Windows NTFS compression)
>> for DB
>> backup files?
>> What is the common practice and what are the risks?
>> --
>> Thanks,
>> Vlad
>>
>

DB Backup Methods

I am trying to better understand the process of backing up a SQL (MSDE)
database. There are several 3rd party tools on the market that include a
backup function, but are dependent on first establishing a connection to the
server. My specific questions are:
1) is it possible to merely copy (using a DOS or windows copy command) the
DB file for backup purposes? If so, is there any trick to restoring the
files?
2) what is the advantage (or requirement) of performing the backup through a
connection to the server? What is the process for creating such a backup --
does it depend on T-SQL statements?
My goal is to create a backup/restore routine within my application so that
the process is very easy for my end-users to manage and also something that
could be set up on an automated schedule.
Appreciate any help! --RS
hi RS,
Rob S wrote:
> I am trying to better understand the process of backing up a SQL
> (MSDE) database. There are several 3rd party tools on the market
> that include a backup function, but are dependent on first
> establishing a connection to the server. My specific questions are:
> 1) is it possible to merely copy (using a DOS or windows copy
> command) the DB file for backup purposes? If so, is there any trick
> to restoring the files?
actually it is... MSDE defaults all it's created databases with the
autoclose option, so that when not in use, they will be freely available for
copy tasks, but you have to consider connection pooling befor proceeding as
that can prevend direct file access... you could then stop the service, copy
the required dbs and restart it, but I do not consider this a best practice
approach.. stopping the service will provide a clean "close" of each db, and
you could even replace the data and log files of a database for a sort of
dirty restore operation, but, again, this is a dirty trick..
BTW... personally I always change that db setting to off, in order to speed
up I/O operation... and this will be a problem in this kind of management...

> 2) what is the advantage (or requirement) of performing the backup
> through a connection to the server? What is the process for creating
> such a backup -- does it depend on T-SQL statements?
yes, it relies on Transact-SQL statements, both for backup and restore
task.. and this is the preferred solution as you do not depend on particular
db settings (autoclose) and a proper backup of the db is taken in "native"
and supported mode... the server can remain up and running for other users
(only during backup, as restore requires esclusive access) supporting "hot"
backups... depending on the recovery model of the dbs, different backup
solutions are available, as full backup and differential, granting high(er)
speed recovery for faults events
for the relative statement syntax and synopsis please have a look at
http://msdn.microsoft.com/library/de...ba-bz_35ww.asp
depending on your needs you can use the SQL Server Agent to schedule backups
in order to plan the correct backup strategy based on your actual need and
critical information stored in the database, as long as depending frequency
of data changes and the like in order to plan for disaster recovery...
http://msdn.microsoft.com/library/de...kprst_63eh.asp
http://msdn.microsoft.com/library/de...kprst_7drn.asp
http://msdn.microsoft.com/library/de...kprst_7kvb.asp

> My goal is to create a backup/restore routine within my application
> so that the process is very easy for my end-users to manage and also
> something that could be set up on an automated schedule.
automated schedule should be better provided via standard SQL Server Agent
managed jobs, where you usually provide a job step of T-SQL subsystem with
the desired backup statement (and this is the way to plan for disaster
recovery strategy as you can not count on your users for manual execution of
"suggested" manual clean up tasks), where monotonic and one shot backup(s)
can be easily directly implemented in your application executing both
dynamic T-SQL statements (say from an ADO/Ado.Net connection) or via
predefined stored procedures provided by your db metaschema...
the same is true for restore operation, tha can be implemented within your
application without problems...
but restore operations are usually not that often perfomed as backup... or I
hope so :D
just a caveat, remember that sysadmin server role membership, or db_owner or
db_backupoperator database role membership is required in order to perform
backup operations, where sysadmin or dbcreator server roles membership is
required for restores, so you have to consider this kind of requirements as
well in your authorization plans...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Here's a .bat file that I run (part of a LONG run) that backs up all
teh DBs that I have on my local machine (MSDE, so no visual tool to
use)
osql is a command line utility that I think comes with SQL Server, all
installs.
You can check out the parameters of osql.exe -- -E is for logging in
via a trusted connection, I think, -S is for the server name, -Q is the
statement that you want to run.
As for the statement, you can see that it is pretty straightforward --
I haven't really investigated all the options that you can do with
incremental backups or anything, but this should get you started -- to
do this from your progra, I'm assuming VB6, then use the Shell command.
; back it up to a temp file
osql -E -Spbk01imd34\PM -Q"BACKUP DATABASE [EMDB] TO DISK =
'C:\Documents and Settings\Matt\My Documents\DataBackup\EMDB.bak' WITH
INIT "
; copy that temp file to the network that gets backed up.
xcopy "C:\Documents and Settings\Matt\My Documents\DataBackup"
"H:\DataBackup" /S /E /F /H /R /Y
Matt
|||Andrea & Matt,
Great input! I now have some clear direction to begin some testing. My
sincere thanks. --Rob
"Rob S" wrote:

> I am trying to better understand the process of backing up a SQL (MSDE)
> database. There are several 3rd party tools on the market that include a
> backup function, but are dependent on first establishing a connection to the
> server. My specific questions are:
> 1) is it possible to merely copy (using a DOS or windows copy command) the
> DB file for backup purposes? If so, is there any trick to restoring the
> files?
> 2) what is the advantage (or requirement) of performing the backup through a
> connection to the server? What is the process for creating such a backup --
> does it depend on T-SQL statements?
> My goal is to create a backup/restore routine within my application so that
> the process is very easy for my end-users to manage and also something that
> could be set up on an automated schedule.
> Appreciate any help! --RS

DB Backup Maint Plan - old file deletes

Hello. When using a DB Maint Plan to backup databases,
say 10 databases all in one plan... the job backs up each
database and THEN at the end of the entire job, it deletes
the previous backups (based on your backup retention).
question>> is there any way to backup the database, and
RIGHT THEN, in a Maint Plan job, delete the PREVIOUS
backup files? The problem is that you need enough disk
space to hold the current and old backups for ALL
databases being backed up, unless there's some setting
we're missing? Any thoughts on that? (using DB Maint
Plans, I know we could code things ourselves and NOT use
Maint Plans)... THanks, BruceThis is a multi-part message in MIME format.
--=_NextPart_000_03A4_01C3B337.52A2A5B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
If you're cramped for space, consider replacing the single maintenance plan
with one plan for each DB. This way, the old backup for a database is
removed before the next plan kicks in.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:0fca01c3b360$91c86e90$a401280a@.phx.gbl...
Hello. When using a DB Maint Plan to backup databases,
say 10 databases all in one plan... the job backs up each
database and THEN at the end of the entire job, it deletes
the previous backups (based on your backup retention).
question>> is there any way to backup the database, and
RIGHT THEN, in a Maint Plan job, delete the PREVIOUS
backup files? The problem is that you need enough disk
space to hold the current and old backups for ALL
databases being backed up, unless there's some setting
we're missing? Any thoughts on that? (using DB Maint
Plans, I know we could code things ourselves and NOT use
Maint Plans)... THanks, Bruce
--=_NextPart_000_03A4_01C3B337.52A2A5B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

If you're cramped for space, consider =replacing the single maintenance plan with one plan for each DB. This way, =the old backup for a database is removed before the next plan kicks =in.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Bruce de Freitas" wrote in =message news:0fca01c3b360$91=c86e90$a401280a@.phx.gbl...Hello. When using a DB Maint Plan to backup databases, say 10 databases all =in one plan... the job backs up each database and THEN at the end of the =entire job, it deletes the previous backups (based on your backup =retention). question>> is there any way to backup the database, =and RIGHT THEN, in a Maint Plan job, delete the PREVIOUS backup =files? The problem is that you need enough disk space to hold the current =and old backups for ALL databases being backed up, unless there's some =setting we're missing? Any thoughts on that? (using DB Maint =Plans, I know we could code things ourselves and NOT use Maint Plans)... =THanks, Bruce

---

--=_NextPart_000_03A4_01C3B337.52A2A5B0--|||Yep, we talked about that as a possibility. It's nice to
have DB backups in one Maint Plan though sometimes, and
was wondering if there was a way to do that. THanks
Tom... Bruce
>--Original Message--
>If you're cramped for space, consider replacing the
single maintenance plan
>with one plan for each DB. This way, the old backup for
a database is
>removed before the next plan kicks in.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:0fca01c3b360$91c86e90$a401280a@.phx.gbl...
>Hello. When using a DB Maint Plan to backup databases,
>say 10 databases all in one plan... the job backs up each
>database and THEN at the end of the entire job, it deletes
>the previous backups (based on your backup retention).
>question>> is there any way to backup the database, and
>RIGHT THEN, in a Maint Plan job, delete the PREVIOUS
>backup files? The problem is that you need enough disk
>space to hold the current and old backups for ALL
>databases being backed up, unless there's some setting
>we're missing? Any thoughts on that? (using DB Maint
>Plans, I know we could code things ourselves and NOT use
>Maint Plans)... THanks, Bruce
>|||This is a multi-part message in MIME format.
--=_NextPart_000_048B_01C3B33F.F3F4BCC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Maintenance plans give you ease of use, but that does not always translate
to "most efficient".
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:109a01c3b368$e3450d70$a401280a@.phx.gbl...
Yep, we talked about that as a possibility. It's nice to
have DB backups in one Maint Plan though sometimes, and
was wondering if there was a way to do that. THanks
Tom... Bruce
>--Original Message--
>If you're cramped for space, consider replacing the
single maintenance plan
>with one plan for each DB. This way, the old backup for
a database is
>removed before the next plan kicks in.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:0fca01c3b360$91c86e90$a401280a@.phx.gbl...
>Hello. When using a DB Maint Plan to backup databases,
>say 10 databases all in one plan... the job backs up each
>database and THEN at the end of the entire job, it deletes
>the previous backups (based on your backup retention).
>question>> is there any way to backup the database, and
>RIGHT THEN, in a Maint Plan job, delete the PREVIOUS
>backup files? The problem is that you need enough disk
>space to hold the current and old backups for ALL
>databases being backed up, unless there's some setting
>we're missing? Any thoughts on that? (using DB Maint
>Plans, I know we could code things ourselves and NOT use
>Maint Plans)... THanks, Bruce
>
--=_NextPart_000_048B_01C3B33F.F3F4BCC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Maintenance plans give you ease of =use, but that does not always translate to "most efficient".
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Bruce de Freitas" wrote in =message news:109a01c3b368$e3=450d70$a401280a@.phx.gbl...Yep, we talked about that as a possibility. It's nice to have DB =backups in one Maint Plan though sometimes, and was wondering if there was a =way to do that. THanks Tom... Bruce>--Original Message-->If you're cramped for space, consider replacing the single maintenance plan>with one plan for each DB. This =way, the old backup for a database is>removed before the next plan =kicks in.>>-->Tom>>--=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Bruc=e de Freitas" wrote in message>news:0fca01c3b360$91c86e90$a401280a@.phx.gbl...>Hell=o. When using a DB Maint Plan to backup databases,>say 10 databases =all in one plan... the job backs up each>database and THEN at the end of =the entire job, it deletes>the previous backups (based on your backup =retention).>>question>> is there any way to =backup the database, and>RIGHT THEN, in a Maint Plan job, delete the PREVIOUS>backup files? The problem is that you need enough disk>space to hold the current and old backups for =ALL>databases being backed up, unless there's some setting>we're missing? =Any thoughts on that? (using DB Maint>Plans, I know we could =code things ourselves and NOT use>Maint Plans)... THanks, Bruce>

---

--=_NextPart_000_048B_01C3B33F.F3F4BCC0--

DB Backup jobs

Hello,
Recently we had a hardware failure and I had to move SQLserver
databases to another hardware. Installed SQLServer 2000 and SP4 (OS Windows
Server 2003 R2 SE SP1). Restored all the databases. Restored msdb to get all
the Mainteance plans and jobs as well. None of the jobs run now. Even if I
kick them off, manually they sit at " Executing Job step1", for ever and it
never completes. I went ahead and deleted all the jobs, deleted all the
maintenance plans. Created a brand new maintenance plan. Still having the
same issue. Can somebody help me point to what could be causing this issue.
RK
That is not much to go on. What account is SQL Agent running under? Is
there anything in the error logs?
Andrew J. Kelly SQL MVP
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:ADF8D241-013E-425E-86C2-6B6D9E0F36BF@.microsoft.com...
> Hello,
> Recently we had a hardware failure and I had to move SQLserver
> databases to another hardware. Installed SQLServer 2000 and SP4 (OS
> Windows
> Server 2003 R2 SE SP1). Restored all the databases. Restored msdb to get
> all
> the Mainteance plans and jobs as well. None of the jobs run now. Even if I
> kick them off, manually they sit at " Executing Job step1", for ever and
> it
> never completes. I went ahead and deleted all the jobs, deleted all the
> maintenance plans. Created a brand new maintenance plan. Still having the
> same issue. Can somebody help me point to what could be causing this
> issue.
> --
> RK
|||SQLAgent was running as Local System and I changed it to a user(local user)
who is part of the administrator group. So is MSSQLServer service.
When I login as that user (local user) I can create files and folders in the
backup directory.
I tried right clicking the database -> all Tasks -> bcakup : this works fine.
RK
"RK73" wrote:

> Hello,
> Recently we had a hardware failure and I had to move SQLserver
> databases to another hardware. Installed SQLServer 2000 and SP4 (OS Windows
> Server 2003 R2 SE SP1). Restored all the databases. Restored msdb to get all
> the Mainteance plans and jobs as well. None of the jobs run now. Even if I
> kick them off, manually they sit at " Executing Job step1", for ever and it
> never completes. I went ahead and deleted all the jobs, deleted all the
> maintenance plans. Created a brand new maintenance plan. Still having the
> same issue. Can somebody help me point to what could be causing this issue.
> --
> RK
|||1) make a VERY simple sql agent job (like all it does is SELECT 1) and make
sure that simple thing works. If not, there could be a serious problem with
the install since you put the agent service account as local admin.
2) if your maintenance plans interact with ANY resource outside the server
sql is installed on, your service accounts need to be domain oriented and
have sufficient permissions to do the interaction.
3) do you get ANY log entries for the job starting/failing? add a few steps
in the job that somehow tell you that certain steps have started/finished to
see if you can isolate the problem.
TheSQLGuru
President
Indicium Resources, Inc.
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:5B0F58E6-138C-42B1-A987-E28EA2529F02@.microsoft.com...[vbcol=seagreen]
> SQLAgent was running as Local System and I changed it to a user(local
> user)
> who is part of the administrator group. So is MSSQLServer service.
> When I login as that user (local user) I can create files and folders in
> the
> backup directory.
> I tried right clicking the database -> all Tasks -> bcakup : this works
> fine.
> --
> RK
>
> "RK73" wrote:
sql

DB Backup jobs

Hello,
Recently we had a hardware failure and I had to move SQLserver
databases to another hardware. Installed SQLServer 2000 and SP4 (OS Windows
Server 2003 R2 SE SP1). Restored all the databases. Restored msdb to get all
the Mainteance plans and jobs as well. None of the jobs run now. Even if I
kick them off, manually they sit at " Executing Job step1", for ever and it
never completes. I went ahead and deleted all the jobs, deleted all the
maintenance plans. Created a brand new maintenance plan. Still having the
same issue. Can somebody help me point to what could be causing this issue.
--
RKThat is not much to go on. What account is SQL Agent running under? Is
there anything in the error logs?
--
Andrew J. Kelly SQL MVP
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:ADF8D241-013E-425E-86C2-6B6D9E0F36BF@.microsoft.com...
> Hello,
> Recently we had a hardware failure and I had to move SQLserver
> databases to another hardware. Installed SQLServer 2000 and SP4 (OS
> Windows
> Server 2003 R2 SE SP1). Restored all the databases. Restored msdb to get
> all
> the Mainteance plans and jobs as well. None of the jobs run now. Even if I
> kick them off, manually they sit at " Executing Job step1", for ever and
> it
> never completes. I went ahead and deleted all the jobs, deleted all the
> maintenance plans. Created a brand new maintenance plan. Still having the
> same issue. Can somebody help me point to what could be causing this
> issue.
> --
> RK|||SQLAgent was running as Local System and I changed it to a user(local user)
who is part of the administrator group. So is MSSQLServer service.
When I login as that user (local user) I can create files and folders in the
backup directory.
I tried right clicking the database -> all Tasks -> bcakup : this works fine.
--
RK
"RK73" wrote:
> Hello,
> Recently we had a hardware failure and I had to move SQLserver
> databases to another hardware. Installed SQLServer 2000 and SP4 (OS Windows
> Server 2003 R2 SE SP1). Restored all the databases. Restored msdb to get all
> the Mainteance plans and jobs as well. None of the jobs run now. Even if I
> kick them off, manually they sit at " Executing Job step1", for ever and it
> never completes. I went ahead and deleted all the jobs, deleted all the
> maintenance plans. Created a brand new maintenance plan. Still having the
> same issue. Can somebody help me point to what could be causing this issue.
> --
> RK|||1) make a VERY simple sql agent job (like all it does is SELECT 1) and make
sure that simple thing works. If not, there could be a serious problem with
the install since you put the agent service account as local admin.
2) if your maintenance plans interact with ANY resource outside the server
sql is installed on, your service accounts need to be domain oriented and
have sufficient permissions to do the interaction.
3) do you get ANY log entries for the job starting/failing? add a few steps
in the job that somehow tell you that certain steps have started/finished to
see if you can isolate the problem.
--
TheSQLGuru
President
Indicium Resources, Inc.
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:5B0F58E6-138C-42B1-A987-E28EA2529F02@.microsoft.com...
> SQLAgent was running as Local System and I changed it to a user(local
> user)
> who is part of the administrator group. So is MSSQLServer service.
> When I login as that user (local user) I can create files and folders in
> the
> backup directory.
> I tried right clicking the database -> all Tasks -> bcakup : this works
> fine.
> --
> RK
>
> "RK73" wrote:
>> Hello,
>> Recently we had a hardware failure and I had to move SQLserver
>> databases to another hardware. Installed SQLServer 2000 and SP4 (OS
>> Windows
>> Server 2003 R2 SE SP1). Restored all the databases. Restored msdb to get
>> all
>> the Mainteance plans and jobs as well. None of the jobs run now. Even if
>> I
>> kick them off, manually they sit at " Executing Job step1", for ever and
>> it
>> never completes. I went ahead and deleted all the jobs, deleted all the
>> maintenance plans. Created a brand new maintenance plan. Still having the
>> same issue. Can somebody help me point to what could be causing this
>> issue.
>> --
>> RK