Backup SQLserverAgent is stopped

Hi
I m trying to make a backup of a SQL database on I m getting the message
SQLserverAgent is stopped on the target '(local)' server
what is this and what do I have to do to fix it
thanx
SQL Server Agent is the built in scheduler service for SQL Server. This is
usually how backups are done. Maybe if you can explain exactly how you are
attempting to issue a backup we can give a more precise answer.
Andrew J. Kelly SQL MVP
"hershel" <hershel@.discussions.microsoft.com> wrote in message
news:62A861BE-B5A5-4568-BE9C-9E52081A5F9C@.microsoft.com...
> Hi
> I m trying to make a backup of a SQL database on I m getting the message
> SQLserverAgent is stopped on the target '(local)' server
> what is this and what do I have to do to fix it
> thanx
>
|||I just want make backup and I got this error message
"Andrew J. Kelly" wrote:

> SQL Server Agent is the built in scheduler service for SQL Server. This is
> usually how backups are done. Maybe if you can explain exactly how you are
> attempting to issue a backup we can give a more precise answer.
> --
> Andrew J. Kelly SQL MVP
>
> "hershel" <hershel@.discussions.microsoft.com> wrote in message
> news:62A861BE-B5A5-4568-BE9C-9E52081A5F9C@.microsoft.com...
>
>
|||But how did you make the backup? If you simply typed BACKUP DATABSE xxxx in
Query Analyzer it would not give you any such message. You must have done
something associated with SQL Agent.
Andrew J. Kelly SQL MVP
"hershel" <hershel@.discussions.microsoft.com> wrote in message
news:A820A21F-8053-4CBC-863A-89963C7BFF6C@.microsoft.com...[vbcol=seagreen]
>I just want make backup and I got this error message
> "Andrew J. Kelly" wrote:
|||I was first trying to do it in the enterprise manager and I got the error
with SQLserverAgent, but I m trying to do it in the Query Analyzer but I m
not sure how to specify that back drive should be my USB which for example
‘f:\sqlbackup’
"Andrew J. Kelly" wrote:

> But how did you make the backup? If you simply typed BACKUP DATABSE xxxx in
> Query Analyzer it would not give you any such message. You must have done
> something associated with SQL Agent.
> --
> Andrew J. Kelly SQL MVP
>
> "hershel" <hershel@.discussions.microsoft.com> wrote in message
> news:A820A21F-8053-4CBC-863A-89963C7BFF6C@.microsoft.com...
>
>
|||BooksOnLine is a wonderful resource for details such as this but here is a
start:
BACKUP DATABASE YourDB TO DISK = 'F:\sqlbackup'
But you have to be careful when using a device such as this. Backups are
always done under the account sql server is running under. So if you map a
drive under another account or the account sql server is using does not see
the device as F: it will fail.
Andrew J. Kelly SQL MVP
"hershel" <hershel@.discussions.microsoft.com> wrote in message
news:2763B9E0-9A91-4724-A8B8-77C6FAB0EAA6@.microsoft.com...[vbcol=seagreen]
>I was first trying to do it in the enterprise manager and I got the error
> with SQLserverAgent, but I m trying to do it in the Query Analyzer but I m
> not sure how to specify that back drive should be my USB which for example
> 'f:\sqlbackup'
>
> "Andrew J. Kelly" wrote:

Backup SQLserverAgent is stopped

Hi
I m trying to make a backup of a SQL database on I m getting the message
SQLserverAgent is stopped on the target '(local)' server
what is this and what do I have to do to fix it
thanxSQL Server Agent is the built in scheduler service for SQL Server. This is
usually how backups are done. Maybe if you can explain exactly how you are
attempting to issue a backup we can give a more precise answer.
Andrew J. Kelly SQL MVP
"hershel" <hershel@.discussions.microsoft.com> wrote in message
news:62A861BE-B5A5-4568-BE9C-9E52081A5F9C@.microsoft.com...
> Hi
> I m trying to make a backup of a SQL database on I m getting the message
> SQLserverAgent is stopped on the target '(local)' server
> what is this and what do I have to do to fix it
> thanx
>|||I just want make backup and I got this error message
"Andrew J. Kelly" wrote:

> SQL Server Agent is the built in scheduler service for SQL Server. This is
> usually how backups are done. Maybe if you can explain exactly how you ar
e
> attempting to issue a backup we can give a more precise answer.
> --
> Andrew J. Kelly SQL MVP
>
> "hershel" <hershel@.discussions.microsoft.com> wrote in message
> news:62A861BE-B5A5-4568-BE9C-9E52081A5F9C@.microsoft.com...
>
>|||But how did you make the backup? If you simply typed BACKUP DATABSE xxxx in
Query Analyzer it would not give you any such message. You must have done
something associated with SQL Agent.
Andrew J. Kelly SQL MVP
"hershel" <hershel@.discussions.microsoft.com> wrote in message
news:A820A21F-8053-4CBC-863A-89963C7BFF6C@.microsoft.com...[vbcol=seagreen]
>I just want make backup and I got this error message
> "Andrew J. Kelly" wrote:
>|||I was first trying to do it in the enterprise manager and I got the error
with SQLserverAgent, but I m trying to do it in the Query Analyzer but I m
not sure how to specify that back drive should be my USB which for example
‘f:\sqlbackup’
"Andrew J. Kelly" wrote:

> But how did you make the backup? If you simply typed BACKUP DATABSE xxxx
in
> Query Analyzer it would not give you any such message. You must have done
> something associated with SQL Agent.
> --
> Andrew J. Kelly SQL MVP
>
> "hershel" <hershel@.discussions.microsoft.com> wrote in message
> news:A820A21F-8053-4CBC-863A-89963C7BFF6C@.microsoft.com...
>
>|||BooksOnLine is a wonderful resource for details such as this but here is a
start:
BACKUP DATABASE YourDB TO DISK = 'F:\sqlbackup'
But you have to be careful when using a device such as this. Backups are
always done under the account sql server is running under. So if you map a
drive under another account or the account sql server is using does not see
the device as F: it will fail.
Andrew J. Kelly SQL MVP
"hershel" <hershel@.discussions.microsoft.com> wrote in message
news:2763B9E0-9A91-4724-A8B8-77C6FAB0EAA6@.microsoft.com...[vbcol=seagreen]
>I was first trying to do it in the enterprise manager and I got the error
> with SQLserverAgent, but I m trying to do it in the Query Analyzer but I m
> not sure how to specify that back drive should be my USB which for example
> 'f:\sqlbackup'
>
> "Andrew J. Kelly" wrote:
>

Backup of databases

Hi
I would like to know if is possible to do a traditional backup to tape
every day as full database and also remote backup. The remote backup as the
comunications are not very good should be the first time full but the other
days should be differential. Is possible to have both backups mode as I
explain ?. Or only will I have one method to do a backup?. I never work with
SQL and I don't know if this configuration could be done.
Thanks in advance
> I would like to know if is possible to do a traditional backup to tape
> every day as full database and also remote backup.
Yes. You do two backups, one to tape, the other to disk on a share on another machine (or locally
and then copying the file).

>The remote backup as the
> comunications are not very good should be the first time full but the other
> days should be differential. Is possible to have both backups mode as I
> explain ?
YEs, but there's no automation that does it for you. You have to create your own scripts/jobs to
handle this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Juanchu" <Juanchu@.discussions.microsoft.com> wrote in message
news:6B96D8B2-83C5-415B-830C-C85B043FD4E4@.microsoft.com...
> Hi
> I would like to know if is possible to do a traditional backup to tape
> every day as full database and also remote backup. The remote backup as the
> comunications are not very good should be the first time full but the other
> days should be differential. Is possible to have both backups mode as I
> explain ?. Or only will I have one method to do a backup?. I never work with
> SQL and I don't know if this configuration could be done.
> Thanks in advance

Backup of databases

Hi
I would like to know if is possible to do a traditional backup to tape
every day as full database and also remote backup. The remote backup as the
comunications are not very good should be the first time full but the other
days should be differential. Is possible to have both backups mode as I
explain ?. Or only will I have one method to do a backup?. I never work with
SQL and I don't know if this configuration could be done.
Thanks in advance> I would like to know if is possible to do a traditional backup to tape
> every day as full database and also remote backup.
Yes. You do two backups, one to tape, the other to disk on a share on anothe
r machine (or locally
and then copying the file).

>The remote backup as the
> comunications are not very good should be the first time full but the othe
r
> days should be differential. Is possible to have both backups mode as I
> explain ?
YEs, but there's no automation that does it for you. You have to create your
own scripts/jobs to
handle this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Juanchu" <Juanchu@.discussions.microsoft.com> wrote in message
news:6B96D8B2-83C5-415B-830C-C85B043FD4E4@.microsoft.com...
> Hi
> I would like to know if is possible to do a traditional backup to tape
> every day as full database and also remote backup. The remote backup as th
e
> comunications are not very good should be the first time full but the othe
r
> days should be differential. Is possible to have both backups mode as I
> explain ?. Or only will I have one method to do a backup?. I never work wi
th
> SQL and I don't know if this configuration could be done.
> Thanks in advance

backup of database

Hi
I am new to sqlserver.
My question?
How frequently is recommended to take the backup of
model, master and msdb database.
Right now I am backing up only the user databases and transaction logs.
Please share your views.
Thanks
Mangesh
Daily backups should suffice.
Keith
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AD73ECD8-C5AD-4DEB-944E-2A39502F6AA3@.microsoft.com...
> Hi
> I am new to sqlserver.
> My question?
> How frequently is recommended to take the backup of
> model, master and msdb database.
> Right now I am backing up only the user databases and transaction logs.
> Please share your views.
> Thanks
> Mangesh
|||The only requirement would be whenever there are modifications. As master
and model should not change frequently, very few backups are required;
usually only right after a system modification. However, this is sometimes
difficult to ascertain. So, we regularly run FULL database backups on a
nightly basis.
The msdb, however, and in contrast to the other two, is modified whenever
jobs are ran, which sould be daily. So, you might want to, at least, run a
FULL backup daily, and even a few DIFFERENTIALs throughout the day, if there
is a heavy load. Also, be aware, that the SQL Agent service will reset the
RECOVERY mode of msdb to SIMPLE whenever it is restarted. So, if you desire
to also perform meaningful transaction log backups, you would have to create
a startup job to reset the msdb back to FULL or BULK LOGGED RECOVERY.
Sincerely,
Anthony Thomas

"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AD73ECD8-C5AD-4DEB-944E-2A39502F6AA3@.microsoft.com...
Hi
I am new to sqlserver.
My question?
How frequently is recommended to take the backup of
model, master and msdb database.
Right now I am backing up only the user databases and transaction logs.
Please share your views.
Thanks
Mangesh

backup of database

Hi
I am new to sqlserver.
My question?
How frequently is recommended to take the backup of
model, master and msdb database.
Right now I am backing up only the user databases and transaction logs.
Please share your views.
Thanks
MangeshDaily backups should suffice.
Keith
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AD73ECD8-C5AD-4DEB-944E-2A39502F6AA3@.microsoft.com...
> Hi
> I am new to sqlserver.
> My question?
> How frequently is recommended to take the backup of
> model, master and msdb database.
> Right now I am backing up only the user databases and transaction logs.
> Please share your views.
> Thanks
> Mangesh|||The only requirement would be whenever there are modifications. As master
and model should not change frequently, very few backups are required;
usually only right after a system modification. However, this is sometimes
difficult to ascertain. So, we regularly run FULL database backups on a
nightly basis.
The msdb, however, and in contrast to the other two, is modified whenever
jobs are ran, which sould be daily. So, you might want to, at least, run a
FULL backup daily, and even a few DIFFERENTIALs throughout the day, if there
is a heavy load. Also, be aware, that the SQL Agent service will reset the
RECOVERY mode of msdb to SIMPLE whenever it is restarted. So, if you desire
to also perform meaningful transaction log backups, you would have to create
a startup job to reset the msdb back to FULL or BULK LOGGED RECOVERY.
Sincerely,
Anthony Thomas
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AD73ECD8-C5AD-4DEB-944E-2A39502F6AA3@.microsoft.com...
Hi
I am new to sqlserver.
My question?
How frequently is recommended to take the backup of
model, master and msdb database.
Right now I am backing up only the user databases and transaction logs.
Please share your views.
Thanks
Mangesh

Backup of a MS SQL 2000 DB

Hi

I'm not really a MS SQL Admin. I have only one
system in our house holding an MS SQL DB.

I need to made a backup of the DB.
Can someone tell me what is the cheapest way?
To buy Backup Exec is not an option.

And I can't shut down the service while the backup is
running...

Regards and thanks in advance

Dominik"Dominik Hirt" <removethis.dhi@.dhi.chwrote in message
news:tCWHh.183311$_X1.48044@.fe05.news.easynews.com ...

Quote:

Originally Posted by

Hi
>
I'm not really a MS SQL Admin. I have only one
system in our house holding an MS SQL DB.
>
I need to made a backup of the DB.
Can someone tell me what is the cheapest way?
To buy Backup Exec is not an option.
>

Use QUERY Analyzer and look up details BACKUP DATABASE

You'll want something like BACKUP DATABASE 'foo' to
disk='\\remoteserver\share1\backupfilename.bak'

If your database is in full recovery mode, make sure to do a log backup also
on a regular basis.

And you can schedule backups also which is a good idea.

And all this can be done while the server is on line.

Quote:

Originally Posted by

And I can't shut down the service while the backup is
running...
>
Regards and thanks in advance
>
Dominik

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||On Thu, 08 Mar 2007 16:36:50 GMT, "Greg D. Moore \(Strider\)"
<mooregr_deleteth1s@.greenms.comwrote:

Quote:

Originally Posted by

>And all this can be done while the server is on line.

To be more specific, all this can ONLY be donw while the server is on
line.

Roy Harvey
Beacon Falls, CT|||"Roy Harvey" <roy_harvey@.snet.netwrote in message
news:0lh0v2pam34rqi0ps0sra9p5snboj68knn@.4ax.com...

Quote:

Originally Posted by

On Thu, 08 Mar 2007 16:36:50 GMT, "Greg D. Moore \(Strider\)"
<mooregr_deleteth1s@.greenms.comwrote:
>

Quote:

Originally Posted by

>>And all this can be done while the server is on line.

>
To be more specific, all this can ONLY be donw while the server is on
line.
>
Roy Harvey
Beacon Falls, CT

True, but I was answering his specific question to that effect. I probably
should have been more clear in that regard.

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Greg D. Moore (Strider) schrieb:

Quote:

Originally Posted by

Use QUERY Analyzer and look up details BACKUP DATABASE
>
You'll want something like BACKUP DATABASE 'foo' to
disk='\\remoteserver\share1\backupfilename.bak'
>
If your database is in full recovery mode, make sure to do a log backup also
on a regular basis.
>
And you can schedule backups also which is a good idea.
>
And all this can be done while the server is on line.

Many thanks. That worked for me.

Dominik|||"Dominik Hirt" <removethis.dhi@.dhi.chwrote in message
news:DPtJh.220196$Wn.61860@.fe06.news.easynews.com. ..

Quote:

Originally Posted by

Greg D. Moore (Strider) schrieb:

Quote:

Originally Posted by

>Use QUERY Analyzer and look up details BACKUP DATABASE
>>
>You'll want something like BACKUP DATABASE 'foo' to
>disk='\\remoteserver\share1\backupfilename.bak'
>>
>If your database is in full recovery mode, make sure to do a log backup
>also on a regular basis.
>>
>And you can schedule backups also which is a good idea.
>>
>And all this can be done while the server is on line.

>
Many thanks. That worked for me.
>
Dominik

Welcome. Glad I could help.

--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com

Backup Maintenance Plan -> MSDE

Hi
I have a problem creating a backup maintenance plan on MDSE 2000 using the
wizard. I want the backup to be written to disk and not into the default
backup directory. First I set the schedule to Sundays at 02:00 and in the
next prompt I set it to write the backup to D:\SQLBUP. So far everything
looks ok but when I try to set that old files should be removed, I can only
set the number but not of what (hours, days, weeks etc). That dropdown menu
is completely blank. So I skip it and set the file extension to BAK. I finish
the rest of the wizard with the default values.
When the maintenance plan is finished I go back and look at its properties.
Strangely enough the path I set for the backup is now blank and so is the
schedule!
Even if I try to set it again in the properties and then click ok and open
it again it is once more lost!
Can anybody tell me why?
Is it because it is MSDE or...?
Regards
Wayne
I have the same Problem. Do you have an answer?
Thanks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||I don't know why the gui is doing that (do you have the latest service packs
installed?) but it should be easy enough to fix. Since the removal of old
backups is not stored in metadata tables you can edit this pretty easy. It
is simply an option to the xp_sqlmaint call.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
9EEB1492-1A6C-4F16-B752-D86A325127CA -VrfyBackup -BkUpMedia DISK -BkUpDB
"C:\Data\Backups" -DelBkUps 4WEEKS -BkExt "BAK"'
The above is what the job step looks like for a Full backup plan that I just
created with a delete of 4 weeks. check out BooksOnLine under "sqlmaint
utility" for details on what each of the options are. So just edit the job
to be the way you want it with regards to the delete part.
Andrew J. Kelly SQL MVP
"Gaston Oxman" <oxmangas@.hotmail.com> wrote in message
news:%23SektOLEFHA.1564@.TK2MSFTNGP09.phx.gbl...
> I have the same Problem. Do you have an answer?
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Backup maintenance plan - MSDE 2000

Hi
I have a problem creating a backup maintenance plan on MDSE 2000 using the
wizard. I want the backup to be written to disk and not into the default
backup directory. First I set the schedule to Sundays at 02:00 and in the
next prompt I set it to write the backup to D:\SQLBUP. So far everything
looks ok but when I try to set that old files should be removed, I can only
set the number but not of what (hours, days, weeks etc). That dropdown menu
is completely blank. So I skip it and set the file extension to BAK. I finish
the rest of the wizard with the default values.
When the maintenance plan is finished I go back and look at its properties.
Strangely enough the path I set for the backup is now blank and so is the
schedule!
Even if I try to set it again in the properties and then click ok and open
it again it is once more lost!
Can anybody tell me why?
Is it because it is MSDE or...?
Regards
Wayne
hi Wayne,
Wayne Gore wrote:
> Hi
> I have a problem creating a backup maintenance plan on MDSE 2000
> using the wizard. I want the backup to be written to disk and not
> into the default backup directory. First I set the schedule to
> Sundays at 02:00 and in the next prompt I set it to write the backup
> to D:\SQLBUP. So far everything looks ok but when I try to set that
> old files should be removed, I can only set the number but not of
> what (hours, days, weeks etc). That dropdown menu is completely
> blank. So I skip it and set the file extension to BAK. I finish the
> rest of the wizard with the default values.
> When the maintenance plan is finished I go back and look at its
> properties. Strangely enough the path I set for the backup is now
> blank and so is the schedule!
> Even if I try to set it again in the properties and then click ok and
> open it again it is once more lost!
> Can anybody tell me why?
> Is it because it is MSDE or...?
> Regards
> Wayne
no, it's becouse of the maintenance plan wizard, that mess all thins a
little..
my advice, keep away from maintenance plan ad write your own Ttransact-SQL
job to perform all required steps..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||I am having the exact problem; Blank menus..
Initially it was the NT\admin auth error so I
edited the job itself and added the -S -U -P and got passed that error.
Then I notice the -UseDefDir, and when you look at the Grid tab you see the
error
"Could not find the directory for database xxxxx . backups may not succeed."
I tried using the -BkUpDB and the path, then the Grid changed to the
sqlmaint /?
giving me all of the options and no other errors.
Is there any thing out there ??
I'm using msde 2000 sp3
"Andrea Montanari" wrote:

> hi Wayne,
> Wayne Gore wrote:
> no, it's becouse of the maintenance plan wizard, that mess all thins a
> little..
> my advice, keep away from maintenance plan ad write your own Ttransact-SQL
> job to perform all required steps..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>

backup log in sqlserver 2000

Hi
I want to activate my standby database.
My primary database is fine and I want to continue to use it for some other
purpose.
My question :
Do I need to use no_truncate option during the remaining transactions backup
?
If I use NO_TRUNCATE option can I continue to use my primary database for
other work.
What is the difference between the regular T-LOG bkup and backup with
NO_TRUNCATE option.
Tks
MangeshNo, you shouldn't use NO_TRUNCATE. This is only there to support backing up
log of a corrupted
database. See http://www.karaszi.com/SQLServer/in...no_truncate.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in me
ssage
news:49D98498-C81E-4B7C-B117-F8E5AB7B7E3B@.microsoft.com...
> Hi
> I want to activate my standby database.
> My primary database is fine and I want to continue to use it for some othe
r
> purpose.
> My question :
> Do I need to use no_truncate option during the remaining transactions back
up?
> If I use NO_TRUNCATE option can I continue to use my primary database for
> other work.
> What is the difference between the regular T-LOG bkup and backup with
> NO_TRUNCATE option.
> Tks
> Mangesh|||Hi,
Backup Log command is used to backup the transaction log when database is
accessible. NO_TRUNCATE option is used when a database is inaccessi le and
you need to take a log backup. See the detailed explanation by tibor:-
http://www.karaszi.com/SQLServer/in...no_truncate.asp
Thanks
Hari
SQL server MVP
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:49D98498-C81E-4B7C-B117-F8E5AB7B7E3B@.microsoft.com...
> Hi
> I want to activate my standby database.
> My primary database is fine and I want to continue to use it for some
> other
> purpose.
> My question :
> Do I need to use no_truncate option during the remaining transactions
> backup?
> If I use NO_TRUNCATE option can I continue to use my primary database for
> other work.
> What is the difference between the regular T-LOG bkup and backup with
> NO_TRUNCATE option.
> Tks
> Mangesh

backup log in sqlserver 2000

Hi
I want to activate my standby database.
My primary database is fine and I want to continue to use it for some other
purpose.
My question :
Do I need to use no_truncate option during the remaining transactions backup?
If I use NO_TRUNCATE option can I continue to use my primary database for
other work.
What is the difference between the regular T-LOG bkup and backup with
NO_TRUNCATE option.
Tks
Mangesh
No, you shouldn't use NO_TRUNCATE. This is only there to support backing up log of a corrupted
database. See http://www.karaszi.com/SQLServer/inf...o_truncate.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in message
news:49D98498-C81E-4B7C-B117-F8E5AB7B7E3B@.microsoft.com...
> Hi
> I want to activate my standby database.
> My primary database is fine and I want to continue to use it for some other
> purpose.
> My question :
> Do I need to use no_truncate option during the remaining transactions backup?
> If I use NO_TRUNCATE option can I continue to use my primary database for
> other work.
> What is the difference between the regular T-LOG bkup and backup with
> NO_TRUNCATE option.
> Tks
> Mangesh
|||Hi,
Backup Log command is used to backup the transaction log when database is
accessible. NO_TRUNCATE option is used when a database is inaccessi le and
you need to take a log backup. See the detailed explanation by tibor:-
http://www.karaszi.com/SQLServer/inf...o_truncate.asp
Thanks
Hari
SQL server MVP
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:49D98498-C81E-4B7C-B117-F8E5AB7B7E3B@.microsoft.com...
> Hi
> I want to activate my standby database.
> My primary database is fine and I want to continue to use it for some
> other
> purpose.
> My question :
> Do I need to use no_truncate option during the remaining transactions
> backup?
> If I use NO_TRUNCATE option can I continue to use my primary database for
> other work.
> What is the difference between the regular T-LOG bkup and backup with
> NO_TRUNCATE option.
> Tks
> Mangesh

BackUp Log ...

hi
I'm trying to understand how "backup log/restore log" works
taking some examples from MSDN
http://msdn2.microsoft.com/en-us/library/ms187495.aspx
but finally, don't understand
this is my test
--
insert 'one' in table "test" >> then backup DB
insert 'two' in table "test" >> then backup LOG
insert 'three' in table "test" >> then backup LOG
thinking that :
- full DB backup contains the row ( 'one' )
- the first LOG backup contains the row ( 'two' )
- and second LOG backup contains the row ( 'three' )
but after the last RESTORE LOG ... with RECOVERY
just the 'one' row is restored
thanks
hope you can guide me
this is the CODE i'm trying:
--
USE master
INSERT into test.dbo.clients values ( 'one' )
BACKUP DATABASE "test" to "bkp" with format
INSERT into test.dbo.clients values ( 'two' )
BACKUP LOG "test" to "bkp"
INSERT into test.dbo.clients values ( 'three' )
BACKUP LOG "test" to "bkp"
-- backup log tail // start restauration
BACKUP DATABASE "test" to "bkp" with recovery
RESTORE DATABASE "test" from "bkp" with norecovery
RESTORE LOG "test" from "bkp" with norecovery
RESTORE LOG "test" from "bkp" with norecovery
RESTORE LOG "test" from "bkp" with NOrecovery
atte,
HernnIs the code you posted the exact code you executed? Seems strange as you did
n't specify which file
from the backup device to restore. Check out the FILE option of the RESTORE
command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:%23A71BtIlGHA.1664@.TK2MSFTNGP03.phx.gbl...
> hi
> I'm trying to understand how "backup log/restore log" works
> taking some examples from MSDN
> http://msdn2.microsoft.com/en-us/library/ms187495.aspx
> but finally, don't understand
> this is my test
> --
> insert 'one' in table "test" >> then backup DB
> insert 'two' in table "test" >> then backup LOG
> insert 'three' in table "test" >> then backup LOG
> thinking that :
> - full DB backup contains the row ( 'one' )
> - the first LOG backup contains the row ( 'two' )
> - and second LOG backup contains the row ( 'three' )
> but after the last RESTORE LOG ... with RECOVERY
> just the 'one' row is restored
> thanks
> hope you can guide me
> --
> this is the CODE i'm trying:
> --
> USE master
> INSERT into test.dbo.clients values ( 'one' )
> BACKUP DATABASE "test" to "bkp" with format
> INSERT into test.dbo.clients values ( 'two' )
> BACKUP LOG "test" to "bkp"
> INSERT into test.dbo.clients values ( 'three' )
> BACKUP LOG "test" to "bkp"
> -- backup log tail // start restauration
> BACKUP DATABASE "test" to "bkp" with recovery
> RESTORE DATABASE "test" from "bkp" with norecovery
> RESTORE LOG "test" from "bkp" with norecovery
> RESTORE LOG "test" from "bkp" with norecovery
> RESTORE LOG "test" from "bkp" with NOrecovery
>
> --
> atte,
> Hernn
>|||yes, thanks
i forgot FILE keyword
atte,
Hernn
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> escribi
en el mensaje news:uy6OnkJlGHA.3588@.TK2MSFTNGP02.phx.gbl...
| Is the code you posted the exact code you executed? Seems strange as you
didn't specify which file
| from the backup device to restore. Check out the FILE option of the
RESTORE command.
|
| --
| Tibor Karaszi, SQL Server MVP
| http://www.karaszi.com/sqlserver/default.asp
| http://www.solidqualitylearning.com/
|
|
| "bajopalabra" <bajopalabra@.hotmail.com> wrote in message
| news:%23A71BtIlGHA.1664@.TK2MSFTNGP03.phx.gbl...
| > hi
| > I'm trying to understand how "backup log/restore log" works
| > taking some examples from MSDN
| > http://msdn2.microsoft.com/en-us/library/ms187495.aspx
| > but finally, don't understand
| >
| > this is my test
| > --
| > insert 'one' in table "test" >> then backup DB
| > insert 'two' in table "test" >> then backup LOG
| > insert 'three' in table "test" >> then backup LOG
| >
| > thinking that :
| > - full DB backup contains the row ( 'one' )
| > - the first LOG backup contains the row ( 'two' )
| > - and second LOG backup contains the row ( 'three' )
| >
| > but after the last RESTORE LOG ... with RECOVERY
| > just the 'one' row is restored
| >
| > thanks
| > hope you can guide me
| >
| > --
| > this is the CODE i'm trying:
| > --
| >
| > USE master
| >
| > INSERT into test.dbo.clients values ( 'one' )
| > BACKUP DATABASE "test" to "bkp" with format
| >
| > INSERT into test.dbo.clients values ( 'two' )
| > BACKUP LOG "test" to "bkp"
| >
| > INSERT into test.dbo.clients values ( 'three' )
| > BACKUP LOG "test" to "bkp"
| >
| > -- backup log tail // start restauration
| >
| > BACKUP DATABASE "test" to "bkp" with recovery
| >
| > RESTORE DATABASE "test" from "bkp" with norecovery
| > RESTORE LOG "test" from "bkp" with norecovery
| > RESTORE LOG "test" from "bkp" with norecovery
| > RESTORE LOG "test" from "bkp" with NOrecovery
| >
| >
| > --
| > atte,
| > Hernn
| >
| >
||||Is the code you posted the exact code you executed? Seems strange as you did
n't specify which file
from the backup device to restore. Check out the FILE option of the RESTORE
command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:%23A71BtIlGHA.1664@.TK2MSFTNGP03.phx.gbl...
> hi
> I'm trying to understand how "backup log/restore log" works
> taking some examples from MSDN
> http://msdn2.microsoft.com/en-us/library/ms187495.aspx
> but finally, don't understand
> this is my test
> --
> insert 'one' in table "test" >> then backup DB
> insert 'two' in table "test" >> then backup LOG
> insert 'three' in table "test" >> then backup LOG
> thinking that :
> - full DB backup contains the row ( 'one' )
> - the first LOG backup contains the row ( 'two' )
> - and second LOG backup contains the row ( 'three' )
> but after the last RESTORE LOG ... with RECOVERY
> just the 'one' row is restored
> thanks
> hope you can guide me
> --
> this is the CODE i'm trying:
> --
> USE master
> INSERT into test.dbo.clients values ( 'one' )
> BACKUP DATABASE "test" to "bkp" with format
> INSERT into test.dbo.clients values ( 'two' )
> BACKUP LOG "test" to "bkp"
> INSERT into test.dbo.clients values ( 'three' )
> BACKUP LOG "test" to "bkp"
> -- backup log tail // start restauration
> BACKUP DATABASE "test" to "bkp" with recovery
> RESTORE DATABASE "test" from "bkp" with norecovery
> RESTORE LOG "test" from "bkp" with norecovery
> RESTORE LOG "test" from "bkp" with norecovery
> RESTORE LOG "test" from "bkp" with NOrecovery
>
> --
> atte,
> Hernn
>|||yes, thanks
i forgot FILE keyword
atte,
Hernn
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> escribi
en el mensaje news:uy6OnkJlGHA.3588@.TK2MSFTNGP02.phx.gbl...
| Is the code you posted the exact code you executed? Seems strange as you
didn't specify which file
| from the backup device to restore. Check out the FILE option of the
RESTORE command.
|
| --
| Tibor Karaszi, SQL Server MVP
| http://www.karaszi.com/sqlserver/default.asp
| http://www.solidqualitylearning.com/
|
|
| "bajopalabra" <bajopalabra@.hotmail.com> wrote in message
| news:%23A71BtIlGHA.1664@.TK2MSFTNGP03.phx.gbl...
| > hi
| > I'm trying to understand how "backup log/restore log" works
| > taking some examples from MSDN
| > http://msdn2.microsoft.com/en-us/library/ms187495.aspx
| > but finally, don't understand
| >
| > this is my test
| > --
| > insert 'one' in table "test" >> then backup DB
| > insert 'two' in table "test" >> then backup LOG
| > insert 'three' in table "test" >> then backup LOG
| >
| > thinking that :
| > - full DB backup contains the row ( 'one' )
| > - the first LOG backup contains the row ( 'two' )
| > - and second LOG backup contains the row ( 'three' )
| >
| > but after the last RESTORE LOG ... with RECOVERY
| > just the 'one' row is restored
| >
| > thanks
| > hope you can guide me
| >
| > --
| > this is the CODE i'm trying:
| > --
| >
| > USE master
| >
| > INSERT into test.dbo.clients values ( 'one' )
| > BACKUP DATABASE "test" to "bkp" with format
| >
| > INSERT into test.dbo.clients values ( 'two' )
| > BACKUP LOG "test" to "bkp"
| >
| > INSERT into test.dbo.clients values ( 'three' )
| > BACKUP LOG "test" to "bkp"
| >
| > -- backup log tail // start restauration
| >
| > BACKUP DATABASE "test" to "bkp" with recovery
| >
| > RESTORE DATABASE "test" from "bkp" with norecovery
| > RESTORE LOG "test" from "bkp" with norecovery
| > RESTORE LOG "test" from "bkp" with norecovery
| > RESTORE LOG "test" from "bkp" with NOrecovery
| >
| >
| > --
| > atte,
| > Hernn
| >
| >
||||bajopalabra wrote:
> yes, thanks
> i forgot FILE keyword
>
...you also have to restore the last logfile with RECOVERY and not
NORECOVERY. Otherwise your database will expect more logfiles to be applied.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||bajopalabra wrote:
> yes, thanks
> i forgot FILE keyword
>
...you also have to restore the last logfile with RECOVERY and not
NORECOVERY. Otherwise your database will expect more logfiles to be applied.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||yes, i knew that
(just a transcription mistake)
atte,
Hernn
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> escribi en el mensaje
news:%23HU3RRRlGHA.1832@.TK2MSFTNGP04.phx.gbl...
| bajopalabra wrote:
| > yes, thanks
| > i forgot FILE keyword
| >
| >
| ...you also have to restore the last logfile with RECOVERY and not
| NORECOVERY. Otherwise your database will expect more logfiles to be
applied.
|
|
| --
| Regards
| Steen Schlter Persson
| Databaseadministrator / Systemadministrator|||yes, i knew that
(just a transcription mistake)
atte,
Hernn
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> escribi en el mensaje
news:%23HU3RRRlGHA.1832@.TK2MSFTNGP04.phx.gbl...
| bajopalabra wrote:
| > yes, thanks
| > i forgot FILE keyword
| >
| >
| ...you also have to restore the last logfile with RECOVERY and not
| NORECOVERY. Otherwise your database will expect more logfiles to be
applied.
|
|
| --
| Regards
| Steen Schlter Persson
| Databaseadministrator / Systemadministrator

backup job quits without any errors

Hi

I have a job which is executed via SQL agent -it attempts to backup each database..

The only problem is that it terminates about 1/2 through (not always at the same spot) - there are about 80 databases on the server

No errors are logged.. Where do I look next ?

The commands executed are as follows:

--

DECLARE @.DB_Name varchar(32)
DECLARE @.Backup_Path varchar(255)
DECLARE @.Backup_Name varchar(255)

DECLARE DB_Cursor CURSOR FOR SELECT NAME FROM sysdatabases

OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor INTO @.DB_Name

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.DB_Name <> 'tempdb' AND @.DB_Name <> 'model'
BEGIN
print '--<< ' + @.db_name + ' >>--'
SET @.Backup_Path = N'C:\sql2005backups\nightly\' + @.DB_Name + 'Daily' + '.bak'
SET @.Backup_Name = @.DB_Name + N' backup'
BACKUP DATABASE @.DB_Name TO DISK = @.Backup_Path WITH INIT
END
FETCH NEXT FROM DB_Cursor INTO @.DB_Name
END

CLOSE DB_cursor
DEALLOCATE DB_cursor

print 'Finished backing up'

Have you tried running the TSQL code from within a management studio or a query analyzer window to see where it fails? Does it run to completion in from the Query Analyzer/Management Studio window?

The other thing that you can do is if you go into the properties of the SQL Server Agent job you can have it put the results of the commands to a text file.

In the SQL Server Management Studio or 2000 Enterprise Manager, go to the properties of the job. Edit the step and click the advanced tab. In the middle of the page there will be a text box called Output File. Just browse to where you would like the output of the commands to reside and indicate a file name.

You will be able to see what errors the SQL Server Agent job is encountering.

Drew

|||Are databases being added or removed (detatched/attached) during the backup processing maybe?

If the script quits without errors, does it write your completion message? @.@.fetch_status is a tri-state value. Either a -1 or a -2 value (any value but 0) would end your loop.

You may want to include more robust checking on that value.

In SQL Server 2005 you could throw a select from sys.dm_exec_cursors at the end of your script to see what the ending status was on the cursor down there with the completion message.

|||

thanks - I'll check it out your suggestions

no - no databases are being being detached etc and yes, it does get to the completion message..

Bruce.

backup job quits without any errors

Hi

I have a job which is executed via SQL agent -it attempts to backup each database..

The only problem is that it terminates about 1/2 through (not always at the same spot) - there are about 80 databases on the server

No errors are logged.. Where do I look next ?

The commands executed are as follows:

--

DECLARE @.DB_Name varchar(32)
DECLARE @.Backup_Path varchar(255)
DECLARE @.Backup_Name varchar(255)

DECLARE DB_Cursor CURSOR FOR SELECT NAME FROM sysdatabases

OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor INTO @.DB_Name

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.DB_Name <> 'tempdb' AND @.DB_Name <> 'model'
BEGIN
print '--<< ' + @.db_name + ' >>--'
SET @.Backup_Path = N'C:\sql2005backups\nightly\' + @.DB_Name + 'Daily' + '.bak'
SET @.Backup_Name = @.DB_Name + N' backup'
BACKUP DATABASE @.DB_Name TO DISK = @.Backup_Path WITH INIT
END
FETCH NEXT FROM DB_Cursor INTO @.DB_Name
END

CLOSE DB_cursor
DEALLOCATE DB_cursor

print 'Finished backing up'

Have you tried running the TSQL code from within a management studio or a query analyzer window to see where it fails? Does it run to completion in from the Query Analyzer/Management Studio window?

The other thing that you can do is if you go into the properties of the SQL Server Agent job you can have it put the results of the commands to a text file.

In the SQL Server Management Studio or 2000 Enterprise Manager, go to the properties of the job. Edit the step and click the advanced tab. In the middle of the page there will be a text box called Output File. Just browse to where you would like the output of the commands to reside and indicate a file name.

You will be able to see what errors the SQL Server Agent job is encountering.

Drew

|||Are databases being added or removed (detatched/attached) during the backup processing maybe?

If the script quits without errors, does it write your completion message? @.@.fetch_status is a tri-state value. Either a -1 or a -2 value (any value but 0) would end your loop.

You may want to include more robust checking on that value.

In SQL Server 2005 you could throw a select from sys.dm_exec_cursors at the end of your script to see what the ending status was on the cursor down there with the completion message.|||

thanks - I'll check it out your suggestions

no - no databases are being being detached etc and yes, it does get to the completion message..

Bruce.