hot backup: backup

Showing posts with label backup. Show all posts

Monday, March 26, 2012

How can I cancel a backup?

I'm running SBS 2003 Premium. I've got several databases set up to be backed
up nightly, but I have deleted one of them since I set up the backup jobs.
Unfortunately, even though the database is deleted, it still tries to back it
iup each night and I get an error in the "Monitoring and Reporting" page in
Server Management.
I set up the backups through SQL server. I can't seem to find where the
backups jobs are "stored." I looked at Scheduled Tasks, the registry, and
in Enterprise Manager under Maintenance Plans and under Management -> Backup.
The backups were not set with Maintenance Plans, they were set by right
clicking on the individual databases and selecting "Backup Database."
How do I cancel the backup?
Thank you in advance.
JonEM, Management, SQL Server Agent, Jobs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jonathan R. Karp" <JonathanRKarp@.discussions.microsoft.com> wrote in message
news:8FBB877F-E6FF-47F4-8900-FEFBD92E0653@.microsoft.com...
> I'm running SBS 2003 Premium. I've got several databases set up to be backed
> up nightly, but I have deleted one of them since I set up the backup jobs.
> Unfortunately, even though the database is deleted, it still tries to back it
> iup each night and I get an error in the "Monitoring and Reporting" page in
> Server Management.
> I set up the backups through SQL server. I can't seem to find where the
> backups jobs are "stored." I looked at Scheduled Tasks, the registry, and
> in Enterprise Manager under Maintenance Plans and under Management -> Backup.
> The backups were not set with Maintenance Plans, they were set by right
> clicking on the individual databases and selecting "Backup Database."
> How do I cancel the backup?
> Thank you in advance.
> Jon
>|||That worked! Thank you very much!
Jon
"Tibor Karaszi" wrote:
> EM, Management, SQL Server Agent, Jobs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jonathan R. Karp" <JonathanRKarp@.discussions.microsoft.com> wrote in message
> news:8FBB877F-E6FF-47F4-8900-FEFBD92E0653@.microsoft.com...
> > I'm running SBS 2003 Premium. I've got several databases set up to be backed
> > up nightly, but I have deleted one of them since I set up the backup jobs.
> > Unfortunately, even though the database is deleted, it still tries to back it
> > iup each night and I get an error in the "Monitoring and Reporting" page in
> > Server Management.
> >
> > I set up the backups through SQL server. I can't seem to find where the
> > backups jobs are "stored." I looked at Scheduled Tasks, the registry, and
> > in Enterprise Manager under Maintenance Plans and under Management -> Backup.
> >
> > The backups were not set with Maintenance Plans, they were set by right
> > clicking on the individual databases and selecting "Backup Database."
> >
> > How do I cancel the backup?
> >
> > Thank you in advance.
> >
> > Jon
> >
>
>

How can I cancel a backup?

I'm running SBS 2003 Premium. I've got several databases set up to be backed
up nightly, but I have deleted one of them since I set up the backup jobs.
Unfortunately, even though the database is deleted, it still tries to back it
iup each night and I get an error in the "Monitoring and Reporting" page in
Server Management.
I set up the backups through SQL server. I can't seem to find where the
backups jobs are "stored." I looked at Scheduled Tasks, the registry, and
in Enterprise Manager under Maintenance Plans and under Management -> Backup.
The backups were not set with Maintenance Plans, they were set by right
clicking on the individual databases and selecting "Backup Database."
How do I cancel the backup?
Thank you in advance.
Jon
EM, Management, SQL Server Agent, Jobs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jonathan R. Karp" <JonathanRKarp@.discussions.microsoft.com> wrote in message
news:8FBB877F-E6FF-47F4-8900-FEFBD92E0653@.microsoft.com...
> I'm running SBS 2003 Premium. I've got several databases set up to be backed
> up nightly, but I have deleted one of them since I set up the backup jobs.
> Unfortunately, even though the database is deleted, it still tries to back it
> iup each night and I get an error in the "Monitoring and Reporting" page in
> Server Management.
> I set up the backups through SQL server. I can't seem to find where the
> backups jobs are "stored." I looked at Scheduled Tasks, the registry, and
> in Enterprise Manager under Maintenance Plans and under Management -> Backup.
> The backups were not set with Maintenance Plans, they were set by right
> clicking on the individual databases and selecting "Backup Database."
> How do I cancel the backup?
> Thank you in advance.
> Jon
>
|||That worked! Thank you very much!
Jon
"Tibor Karaszi" wrote:

> EM, Management, SQL Server Agent, Jobs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jonathan R. Karp" <JonathanRKarp@.discussions.microsoft.com> wrote in message
> news:8FBB877F-E6FF-47F4-8900-FEFBD92E0653@.microsoft.com...
>
>

How can I cancel a backup?

I'm running SBS 2003 Premium. I've got several databases set up to be backe
d
up nightly, but I have deleted one of them since I set up the backup jobs.
Unfortunately, even though the database is deleted, it still tries to back i
t
iup each night and I get an error in the "Monitoring and Reporting" page in
Server Management.
I set up the backups through SQL server. I can't seem to find where the
backups jobs are "stored." I looked at Scheduled Tasks, the registry, and
in Enterprise Manager under Maintenance Plans and under Management -> Backup
.
The backups were not set with Maintenance Plans, they were set by right
clicking on the individual databases and selecting "Backup Database."
How do I cancel the backup?
Thank you in advance.
JonEM, Management, SQL Server Agent, Jobs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jonathan R. Karp" <JonathanRKarp@.discussions.microsoft.com> wrote in messag
e
news:8FBB877F-E6FF-47F4-8900-FEFBD92E0653@.microsoft.com...
> I'm running SBS 2003 Premium. I've got several databases set up to be bac
ked
> up nightly, but I have deleted one of them since I set up the backup jobs.
> Unfortunately, even though the database is deleted, it still tries to back
it
> iup each night and I get an error in the "Monitoring and Reporting" page i
n
> Server Management.
> I set up the backups through SQL server. I can't seem to find where the
> backups jobs are "stored." I looked at Scheduled Tasks, the registry, and
> in Enterprise Manager under Maintenance Plans and under Management -> Back
up.
> The backups were not set with Maintenance Plans, they were set by right
> clicking on the individual databases and selecting "Backup Database."
> How do I cancel the backup?
> Thank you in advance.
> Jon
>|||That worked! Thank you very much!
Jon
"Tibor Karaszi" wrote:

> EM, Management, SQL Server Agent, Jobs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jonathan R. Karp" <JonathanRKarp@.discussions.microsoft.com> wrote in mess
age
> news:8FBB877F-E6FF-47F4-8900-FEFBD92E0653@.microsoft.com...
>
>sql

Friday, March 23, 2012

How can I buy SQL 6.5?

We have an old SQL 6.5 .DAT backup that we want to load to SQL 2000. As far
as I can see we cannot upload the file directly, we must first restore to
SQL6.5 and then upgrade the DB to SQL2000.
Does anybody know how or where I can buy/licence SQL6.5?
Thanks,
SimonIf you have an MSDN Universal subscription, you can pull it down from Visual
Studio 6.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Simon J Fisher" <Simon J Fisher@.discussions.microsoft.com> wrote in message
news:0F38C229-5F52-40DE-ACEB-6BAD32088DFE@.microsoft.com...
We have an old SQL 6.5 .DAT backup that we want to load to SQL 2000. As far
as I can see we cannot upload the file directly, we must first restore to
SQL6.5 and then upgrade the DB to SQL2000.
Does anybody know how or where I can buy/licence SQL6.5?
Thanks,
Simon|||If you do not have a MSDN subscription, you might try ebay.
Rand
This posting is provided "as is" with no warranties and confers no rights.

How can I buy SQL 6.5?

We have an old SQL 6.5 .DAT backup that we want to load to SQL 2000. As far
as I can see we cannot upload the file directly, we must first restore to
SQL6.5 and then upgrade the DB to SQL2000.
Does anybody know how or where I can buy/licence SQL6.5?
Thanks,
Simon
If you have an MSDN Universal subscription, you can pull it down from Visual
Studio 6.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Simon J Fisher" <Simon J Fisher@.discussions.microsoft.com> wrote in message
news:0F38C229-5F52-40DE-ACEB-6BAD32088DFE@.microsoft.com...
We have an old SQL 6.5 .DAT backup that we want to load to SQL 2000. As far
as I can see we cannot upload the file directly, we must first restore to
SQL6.5 and then upgrade the DB to SQL2000.
Does anybody know how or where I can buy/licence SQL6.5?
Thanks,
Simon
|||If you do not have a MSDN subscription, you might try ebay.
Rand
This posting is provided "as is" with no warranties and confers no rights.

How can I buy SQL 6.5?

We have an old SQL 6.5 .DAT backup that we want to load to SQL 2000. As far
as I can see we cannot upload the file directly, we must first restore to
SQL6.5 and then upgrade the DB to SQL2000.
Does anybody know how or where I can buy/licence SQL6.5?
Thanks,
SimonIf you have an MSDN Universal subscription, you can pull it down from Visual
Studio 6.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Simon J Fisher" <Simon J Fisher@.discussions.microsoft.com> wrote in message
news:0F38C229-5F52-40DE-ACEB-6BAD32088DFE@.microsoft.com...
We have an old SQL 6.5 .DAT backup that we want to load to SQL 2000. As far
as I can see we cannot upload the file directly, we must first restore to
SQL6.5 and then upgrade the DB to SQL2000.
Does anybody know how or where I can buy/licence SQL6.5?
Thanks,
Simon|||If you do not have a MSDN subscription, you might try ebay.
Rand
This posting is provided "as is" with no warranties and confers no rights.sql

How can i backup sql 2000 using win 2003 backup util

Hi
I have a windows 2003 server running sql. I would like to backup SQL 2000
databases using the built in backup software to a network location.
Could anyone tell me how i do this.
Thanks
Nick
To do this properly, you would have to take the databases offline, or stop
the SQL Server service.
Are you not able to use the built-in-and-highly-effective SQL Server backup
and then take those .bak files to a network location?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Nick" <andync55@.hotmail.com> wrote in message
news:OvQct%23a1FHA.2428@.tk2msftngp13.phx.gbl...
> Hi
> I have a windows 2003 server running sql. I would like to backup SQL 2000
> databases using the built in backup software to a network location.
> Could anyone tell me how i do this.
>
> Thanks
> Nick
>
|||Hi
Thanks for the fast reply. Can i do that by right clicking on each databse
and selecting backup. Is that all i need to do. Is there any other sql
setting i should backup other than just the databases.
Thanks
Nick Comper
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:ehe21Jb1FHA.3660@.TK2MSFTNGP15.phx.gbl...
> To do this properly, you would have to take the databases offline, or stop
> the SQL Server service.
> Are you not able to use the built-in-and-highly-effective SQL Server
> backup and then take those .bak files to a network location?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Nick" <andync55@.hotmail.com> wrote in message
> news:OvQct%23a1FHA.2428@.tk2msftngp13.phx.gbl...
>
|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Nick" <andync55@.hotmail.com> wrote in message
news:eLJqeOb1FHA.3720@.TK2MSFTNGP14.phx.gbl...
> Hi
> Thanks for the fast reply. Can i do that by right clicking on each databse
> and selecting backup. Is that all i need to do. Is there any other sql
> setting i should backup other than just the databases.
> Thanks
> --
> Nick Comper
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:ehe21Jb1FHA.3660@.TK2MSFTNGP15.phx.gbl...
>
|||Hi
Thanks for the fast reply. Can i do that by right clicking on each databse
and selecting backup. Is that all i need to do. Is there any other sql
setting i should backup other than just the databases.
Thanks
Nick Comper
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:ehe21Jb1FHA.3660@.TK2MSFTNGP15.phx.gbl...
> To do this properly, you would have to take the databases offline, or stop
> the SQL Server service.
> Are you not able to use the built-in-and-highly-effective SQL Server
> backup and then take those .bak files to a network location?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Nick" <andync55@.hotmail.com> wrote in message
> news:OvQct%23a1FHA.2428@.tk2msftngp13.phx.gbl...
>

How can i backup sql 2000 using win 2003 backup util

Hi
I have a windows 2003 server running sql. I would like to backup SQL 2000
databases using the built in backup software to a network location.
Could anyone tell me how i do this.
Thanks
NickTo do this properly, you would have to take the databases offline, or stop
the SQL Server service.
Are you not able to use the built-in-and-highly-effective SQL Server backup
and then take those .bak files to a network location?
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Nick" <andync55@.hotmail.com> wrote in message
news:OvQct%23a1FHA.2428@.tk2msftngp13.phx.gbl...
> Hi
> I have a windows 2003 server running sql. I would like to backup SQL 2000
> databases using the built in backup software to a network location.
> Could anyone tell me how i do this.
>
> Thanks
> Nick
>|||Hi
Thanks for the fast reply. Can i do that by right clicking on each databse
and selecting backup. Is that all i need to do. Is there any other sql
setting i should backup other than just the databases.
Thanks
--
Nick Comper
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:ehe21Jb1FHA.3660@.TK2MSFTNGP15.phx.gbl...
> To do this properly, you would have to take the databases offline, or stop
> the SQL Server service.
> Are you not able to use the built-in-and-highly-effective SQL Server
> backup and then take those .bak files to a network location?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Nick" <andync55@.hotmail.com> wrote in message
> news:OvQct%23a1FHA.2428@.tk2msftngp13.phx.gbl...
>> Hi
>> I have a windows 2003 server running sql. I would like to backup SQL 2000
>> databases using the built in backup software to a network location.
>> Could anyone tell me how i do this.
>>
>> Thanks
>> Nick
>|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Nick" <andync55@.hotmail.com> wrote in message
news:eLJqeOb1FHA.3720@.TK2MSFTNGP14.phx.gbl...
> Hi
> Thanks for the fast reply. Can i do that by right clicking on each databse
> and selecting backup. Is that all i need to do. Is there any other sql
> setting i should backup other than just the databases.
> Thanks
> --
> Nick Comper
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:ehe21Jb1FHA.3660@.TK2MSFTNGP15.phx.gbl...
>> To do this properly, you would have to take the databases offline, or
>> stop the SQL Server service.
>> Are you not able to use the built-in-and-highly-effective SQL Server
>> backup and then take those .bak files to a network location?
>> --
>> Kevin Hill
>> President
>> 3NF Consulting
>> www.3nf-inc.com/NewsGroups.htm
>>
>> "Nick" <andync55@.hotmail.com> wrote in message
>> news:OvQct%23a1FHA.2428@.tk2msftngp13.phx.gbl...
>> Hi
>> I have a windows 2003 server running sql. I would like to backup SQL
>> 2000 databases using the built in backup software to a network location.
>> Could anyone tell me how i do this.
>>
>> Thanks
>> Nick
>>
>|||Hi
Thanks for the fast reply. Can i do that by right clicking on each databse
and selecting backup. Is that all i need to do. Is there any other sql
setting i should backup other than just the databases.
Thanks
--
Nick Comper
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:ehe21Jb1FHA.3660@.TK2MSFTNGP15.phx.gbl...
> To do this properly, you would have to take the databases offline, or stop
> the SQL Server service.
> Are you not able to use the built-in-and-highly-effective SQL Server
> backup and then take those .bak files to a network location?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Nick" <andync55@.hotmail.com> wrote in message
> news:OvQct%23a1FHA.2428@.tk2msftngp13.phx.gbl...
>> Hi
>> I have a windows 2003 server running sql. I would like to backup SQL 2000
>> databases using the built in backup software to a network location.
>> Could anyone tell me how i do this.
>>
>> Thanks
>> Nick
>

How can i backup sql 2000 using win 2003 backup util

Hi
I have a windows 2003 server running sql. I would like to backup SQL 2000
databases using the built in backup software to a network location.
Could anyone tell me how i do this.
Thanks
NickTo do this properly, you would have to take the databases offline, or stop
the SQL Server service.
Are you not able to use the built-in-and-highly-effective SQL Server backup
and then take those .bak files to a network location?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Nick" <andync55@.hotmail.com> wrote in message
news:OvQct%23a1FHA.2428@.tk2msftngp13.phx.gbl...
> Hi
> I have a windows 2003 server running sql. I would like to backup SQL 2000
> databases using the built in backup software to a network location.
> Could anyone tell me how i do this.
>
> Thanks
> Nick
>|||Hi
Thanks for the fast reply. Can i do that by right clicking on each databse
and selecting backup. Is that all i need to do. Is there any other sql
setting i should backup other than just the databases.
Thanks
Nick Comper
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:ehe21Jb1FHA.3660@.TK2MSFTNGP15.phx.gbl...
> To do this properly, you would have to take the databases offline, or stop
> the SQL Server service.
> Are you not able to use the built-in-and-highly-effective SQL Server
> backup and then take those .bak files to a network location?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Nick" <andync55@.hotmail.com> wrote in message
> news:OvQct%23a1FHA.2428@.tk2msftngp13.phx.gbl...
>|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Nick" <andync55@.hotmail.com> wrote in message
news:eLJqeOb1FHA.3720@.TK2MSFTNGP14.phx.gbl...
> Hi
> Thanks for the fast reply. Can i do that by right clicking on each databse
> and selecting backup. Is that all i need to do. Is there any other sql
> setting i should backup other than just the databases.
> Thanks
> --
> Nick Comper
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:ehe21Jb1FHA.3660@.TK2MSFTNGP15.phx.gbl...
>|||Hi
Thanks for the fast reply. Can i do that by right clicking on each databse
and selecting backup. Is that all i need to do. Is there any other sql
setting i should backup other than just the databases.
Thanks
Nick Comper
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:ehe21Jb1FHA.3660@.TK2MSFTNGP15.phx.gbl...
> To do this properly, you would have to take the databases offline, or stop
> the SQL Server service.
> Are you not able to use the built-in-and-highly-effective SQL Server
> backup and then take those .bak files to a network location?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Nick" <andync55@.hotmail.com> wrote in message
> news:OvQct%23a1FHA.2428@.tk2msftngp13.phx.gbl...
>

How can I backup and restore a database

I mean, how can I do that in code. I know how to do it in EM but this not what I need.
Thank you!Got Books Online?

It's all in there...

You can even check out

http://weblogs.sqlteam.com/tarad/category/95.aspx

How can I back up a log-shipped database?

Hi,
You can not backup a database or log that is standby mode
with regular backups.
As a work around, you have to restore the database and
then back it up.
Here is something you could use:
USE MASTER
RESTORE DATABASE DB_NAME
WITH RECOVERY
This changes the standby status to normal db use and then
you can back it up.
The only thing that I am not sure is what happens at the
next log shipped/restored because it depends how you have
it setup.
hth
DeeJay
>--Original Message--
>(SQL Server 2000, SP3a)
>Hello all!
>I've got a database that is the secondary server in a log-
shipped pair. Whenever I try
>and do a BACKUP on this database, I get an error message
that the database is in a
>READ-ONLY STANDBY mode.
>Is there any way to circumvent this, temporarily, and
make a database backup of a
>log-shipped database?
>Thanks!
>
>.
>Thanks DeeJay!
What we're thinking (if you'll humor us for a moment):
* Temporarily disable the Job that's responsible for processing the log-ship
ped
transaction logs.
* Change the status of the database to get it out of STANDBY mode (as per yo
ur
recommendation).
* Back up the database.
* Change the status of the database back to STANDBY (dunno how to do this ye
t).
* Re-enable the Job.
I'm not sure if this is advisable, though. For instance, what does the Log-
Shipping
Monitor service do? Is it sensitive to any of the proposed elements above?
Thanks for any additional help you can provide! :-)
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:3a6001c48f88$24958740$a301280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> You can not backup a database or log that is standby mode
> with regular backups.
> As a work around, you have to restore the database and
> then back it up.
> Here is something you could use:
> USE MASTER
> RESTORE DATABASE DB_NAME
> WITH RECOVERY
> This changes the standby status to normal db use and then
> you can back it up.
> The only thing that I am not sure is what happens at the
> next log shipped/restored because it depends how you have
> it setup.
> hth
> DeeJay
> shipped pair. Whenever I try
> that the database is in a
> make a database backup of a|||Hi John,

> * Change the status of the database back to STANDBY (dunno how to do this yet).[/v
bcol]
No can do. The recovery procedures etc in SQL Server aren't written to handl
e this scenario, quite simply. Any
way you can grab the log backups already on the fallback machine?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Peterson" <j0hnp@.comcast.net> wrote in message news:uXJ9wm4jEHA.632@.TK2MSFTNGP12.phx.g
bl...[vbcol=seagreen]
> Thanks DeeJay!
> What we're thinking (if you'll humor us for a moment):
> * Temporarily disable the Job that's responsible for processing the log-sh
ipped
> transaction logs.
> * Change the status of the database to get it out of STANDBY mode (as per
your
> recommendation).
> * Back up the database.
> * Change the status of the database back to STANDBY (dunno how to do this
yet).
> * Re-enable the Job.
> I'm not sure if this is advisable, though. For instance, what does the Lo
g-Shipping
> Monitor service do? Is it sensitive to any of the proposed elements above
?
> Thanks for any additional help you can provide! :-)
>
> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> news:3a6001c48f88$24958740$a301280a@.phx.gbl...
>|||Thanks Tibor. I don't think that we have the main database backup available
from the
fallback machine.
Any way we can use an undocumented "flag" in some system table to toggle the
DB back in
STANDBY mode? ;-)
Just to give you the heads up: we've got our production databases log-shipp
ed into our
corporate network. If we can leverage these log-shipped databases, we won't
have to pay
the network price to copy from production again.
Thanks for any additional help you might be able to provide! :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:uDrJTt4jEHA.2908@.TK2MSFTNGP10.phx.gbl...
> Hi John,
>
> No can do. The recovery procedures etc in SQL Server aren't written to han
dle this
> scenario, quite simply. Any
> way you can grab the log backups already on the fallback machine?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:uXJ9wm4jEHA.632@.TK2MSFTNGP12.phx.gbl...
>|||You might want to test this out in dev first.
I am not sure if this is supported by MS or if your log
shipping will work properly again.
You might have to re-configure log-shipping.
DeeJay
>--Original Message--
>Thanks Tibor. I don't think that we have the main
database backup available from the
>fallback machine.
>Any way we can use an undocumented "flag" in some system
table to toggle the DB back in
>STANDBY mode? ;-)
>Just to give you the heads up: we've got our production
databases log-shipped into our
>corporate network. If we can leverage these log-shipped
databases, we won't have to pay
>the network price to copy from production again.
>Thanks for any additional help you might be able to
provide! :-)
>
>"Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in message
>news:uDrJTt4jEHA.2908@.TK2MSFTNGP10.phx.gbl...
(dunno how to do this yet).[vbcol=seagreen]
aren't written to handle this[vbcol=seagreen]
fallback machine?[vbcol=seagreen]
processing the log-shipped[vbcol=seagreen]
STANDBY mode (as per your[vbcol=seagreen]
(dunno how to do this yet).[vbcol=seagreen]
instance, what does the Log-Shipping[vbcol=seagreen]
proposed elements above?[vbcol=seagreen]
mode[vbcol=seagreen]
and[vbcol=seagreen]
then[vbcol=seagreen]
the[vbcol=seagreen]
have[vbcol=seagreen]
a log-[vbcol=seagreen]
message[vbcol=seagreen]
>
>.
>|||> Any way we can use an undocumented "flag" in some system table to toggle the DB back in[vb
col=seagreen]
> STANDBY mode? ;-)[/vbcol]
Nope, none that I know of. Just think about it. When you bring a db out of s
tandby mode, you get the recovery
work persisted. Stuff has been rolled forward and rolled back. Period. How w
ould you be able to apply a later
log backup onto this, as the log records in that log backup are totally out-
of sync with the database you have
performed a permanent recovery on?
For this to work, MS would need to do some changes in how the recovery proce
ss work or give us some other
option for recovery (NO_RECOVERY, RECOVERY, STANDBY, QUASI_PERMANENT_RECOVER
Y).
Or MS would need to change SQL Server so it allow us to do a backup of a dat
abase in STANDBY mode. (And here
I'm too tired right now to consider what ramifications that would have on lo
g record sequencing and recovery
;-) ).
I know this question has been on the table before, so you might want to chec
k the archives to see if someone
came up with anything. I have a feeling that you are out of luck, though...
Perhaps you should opt for a home-grown log shipping solution, to give you b
etter control of handling of the
backup files?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Peterson" <j0hnp@.comcast.net> wrote in message news:%23UXfE54jEHA.2412@.TK2MSFTNGP15.ph
x.gbl...
> Thanks Tibor. I don't think that we have the main database backup availab
le from the
> fallback machine.
> Any way we can use an undocumented "flag" in some system table to toggle t
he DB back in
> STANDBY mode? ;-)
> Just to give you the heads up: we've got our production databases log-shi
pped into our
> corporate network. If we can leverage these log-shipped databases, we won
't have to pay
> the network price to copy from production again.
> Thanks for any additional help you might be able to provide! :-)
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:uDrJTt4jEHA.2908@.TK2MSFTNGP10.phx.gbl...
>|||Thanks Tibor! It's clear I don't understand the whole RECOVERY business.
I had *hoped* that, by temporarily suspending the log file processing, I cou
ld somehow get
the DB in a state where it was backup-able. But, if I'm understanding you c
orrectly, it
sounds as if, by virtue of performing a backup on the DB, I'd be "marking" t
he transaction
log in such a way as to be incompatible with the normal log files when they'
re later
resumed.
Could I, then, do a detach and copy the underlying .MDF/.LDF files? Or woul
d that break
the whole log-shipping "linkage"?
Thanks again!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:%23tykrd5jEHA.3896@.TK2MSFTNGP15.phx.gbl...
> Nope, none that I know of. Just think about it. When you bring a db out of
standby mode,
> you get the recovery
> work persisted. Stuff has been rolled forward and rolled back. Period. How
would you be
> able to apply a later
> log backup onto this, as the log records in that log backup are totally ou
t-of sync with
> the database you have
> performed a permanent recovery on?
> For this to work, MS would need to do some changes in how the recovery pro
cess work or
> give us some other
> option for recovery (NO_RECOVERY, RECOVERY, STANDBY, QUASI_PERMANENT_RECOV
ERY).
> Or MS would need to change SQL Server so it allow us to do a backup of a d
atabase in
> STANDBY mode. (And here
> I'm too tired right now to consider what ramifications that would have on
log record
> sequencing and recovery
> ;-) ).
> I know this question has been on the table before, so you might want to ch
eck the
> archives to see if someone
> came up with anything. I have a feeling that you are out of luck, though..
.
> Perhaps you should opt for a home-grown log shipping solution, to give you
better
> control of handling of the
> backup files?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:%23UXfE54jEHA.2412@.TK2MSFTNGP15.phx.gbl...
>|||I think that when you do recovery, log records are either removed or added (
possibly both) to the
transaction log. This means that a later log backup from the production data
base will not just be
able to add the log records to the log-shipped database, because the transac
tion log has been
changed. The LSN (log sequence numbers) doesn't match anymore.
I haven't tested whether you can detach and attach a database a database in
STANDBY mode. Give it a
try. If not, you might consider stopping SQL server and just grabbing the fi
les. Not supported and
not guaranteed that you can attach such files, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Peterson" <j0hnp@.comcast.net> wrote in message news:O991LI6jEHA.1348@.TK2MSFTNGP15.phx.
gbl...
> Thanks Tibor! It's clear I don't understand the whole RECOVERY business.
> I had *hoped* that, by temporarily suspending the log file processing, I c
ould somehow get
> the DB in a state where it was backup-able. But, if I'm understanding you
correctly, it
> sounds as if, by virtue of performing a backup on the DB, I'd be "marking"
the transaction
> log in such a way as to be incompatible with the normal log files when the
y're later
> resumed.
> Could I, then, do a detach and copy the underlying .MDF/.LDF files? Or wo
uld that break
> the whole log-shipping "linkage"?
> Thanks again!
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23tykrd5jEHA.3896@.TK2MSFTNGP15.phx.gbl...
>sql

How can I back up a log-shipped database?

(SQL Server 2000, SP3a)
Hello all!
I've got a database that is the secondary server in a log-shipped pair. Whenever I try
and do a BACKUP on this database, I get an error message that the database is in a
READ-ONLY STANDBY mode.
Is there any way to circumvent this, temporarily, and make a database backup of a
log-shipped database?
Thanks!Hi,
You can not backup a database or log that is standby mode
with regular backups.
As a work around, you have to restore the database and
then back it up.
Here is something you could use:
USE MASTER
RESTORE DATABASE DB_NAME
WITH RECOVERY
This changes the standby status to normal db use and then
you can back it up.
The only thing that I am not sure is what happens at the
next log shipped/restored because it depends how you have
it setup.
hth
DeeJay
>--Original Message--
>(SQL Server 2000, SP3a)
>Hello all!
>I've got a database that is the secondary server in a log-
shipped pair. Whenever I try
>and do a BACKUP on this database, I get an error message
that the database is in a
>READ-ONLY STANDBY mode.
>Is there any way to circumvent this, temporarily, and
make a database backup of a
>log-shipped database?
>Thanks!
>
>.
>|||Thanks DeeJay!
What we're thinking (if you'll humor us for a moment):
* Temporarily disable the Job that's responsible for processing the log-shipped
transaction logs.
* Change the status of the database to get it out of STANDBY mode (as per your
recommendation).
* Back up the database.
* Change the status of the database back to STANDBY (dunno how to do this yet).
* Re-enable the Job.
I'm not sure if this is advisable, though. For instance, what does the Log-Shipping
Monitor service do? Is it sensitive to any of the proposed elements above?
Thanks for any additional help you can provide! :-)
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:3a6001c48f88$24958740$a301280a@.phx.gbl...
> Hi,
> You can not backup a database or log that is standby mode
> with regular backups.
> As a work around, you have to restore the database and
> then back it up.
> Here is something you could use:
> USE MASTER
> RESTORE DATABASE DB_NAME
> WITH RECOVERY
> This changes the standby status to normal db use and then
> you can back it up.
> The only thing that I am not sure is what happens at the
> next log shipped/restored because it depends how you have
> it setup.
> hth
> DeeJay
>>--Original Message--
>>(SQL Server 2000, SP3a)
>>Hello all!
>>I've got a database that is the secondary server in a log-
> shipped pair. Whenever I try
>>and do a BACKUP on this database, I get an error message
> that the database is in a
>>READ-ONLY STANDBY mode.
>>Is there any way to circumvent this, temporarily, and
> make a database backup of a
>>log-shipped database?
>>Thanks!
>>
>>.|||Hi John,
> * Change the status of the database back to STANDBY (dunno how to do this yet).
No can do. The recovery procedures etc in SQL Server aren't written to handle this scenario, quite simply. Any
way you can grab the log backups already on the fallback machine?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Peterson" <j0hnp@.comcast.net> wrote in message news:uXJ9wm4jEHA.632@.TK2MSFTNGP12.phx.gbl...
> Thanks DeeJay!
> What we're thinking (if you'll humor us for a moment):
> * Temporarily disable the Job that's responsible for processing the log-shipped
> transaction logs.
> * Change the status of the database to get it out of STANDBY mode (as per your
> recommendation).
> * Back up the database.
> * Change the status of the database back to STANDBY (dunno how to do this yet).
> * Re-enable the Job.
> I'm not sure if this is advisable, though. For instance, what does the Log-Shipping
> Monitor service do? Is it sensitive to any of the proposed elements above?
> Thanks for any additional help you can provide! :-)
>
> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> news:3a6001c48f88$24958740$a301280a@.phx.gbl...
> > Hi,
> >
> > You can not backup a database or log that is standby mode
> > with regular backups.
> >
> > As a work around, you have to restore the database and
> > then back it up.
> >
> > Here is something you could use:
> >
> > USE MASTER
> > RESTORE DATABASE DB_NAME
> > WITH RECOVERY
> >
> > This changes the standby status to normal db use and then
> > you can back it up.
> >
> > The only thing that I am not sure is what happens at the
> > next log shipped/restored because it depends how you have
> > it setup.
> >
> > hth
> >
> > DeeJay
> >>--Original Message--
> >>(SQL Server 2000, SP3a)
> >>
> >>Hello all!
> >>
> >>I've got a database that is the secondary server in a log-
> > shipped pair. Whenever I try
> >>and do a BACKUP on this database, I get an error message
> > that the database is in a
> >>READ-ONLY STANDBY mode.
> >>
> >>Is there any way to circumvent this, temporarily, and
> > make a database backup of a
> >>log-shipped database?
> >>
> >>Thanks!
> >>
> >>
> >>.
> >>
>|||Thanks Tibor. I don't think that we have the main database backup available from the
fallback machine.
Any way we can use an undocumented "flag" in some system table to toggle the DB back in
STANDBY mode? ;-)
Just to give you the heads up: we've got our production databases log-shipped into our
corporate network. If we can leverage these log-shipped databases, we won't have to pay
the network price to copy from production again.
Thanks for any additional help you might be able to provide! :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:uDrJTt4jEHA.2908@.TK2MSFTNGP10.phx.gbl...
> Hi John,
>> * Change the status of the database back to STANDBY (dunno how to do this yet).
> No can do. The recovery procedures etc in SQL Server aren't written to handle this
> scenario, quite simply. Any
> way you can grab the log backups already on the fallback machine?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:uXJ9wm4jEHA.632@.TK2MSFTNGP12.phx.gbl...
>> Thanks DeeJay!
>> What we're thinking (if you'll humor us for a moment):
>> * Temporarily disable the Job that's responsible for processing the log-shipped
>> transaction logs.
>> * Change the status of the database to get it out of STANDBY mode (as per your
>> recommendation).
>> * Back up the database.
>> * Change the status of the database back to STANDBY (dunno how to do this yet).
>> * Re-enable the Job.
>> I'm not sure if this is advisable, though. For instance, what does the Log-Shipping
>> Monitor service do? Is it sensitive to any of the proposed elements above?
>> Thanks for any additional help you can provide! :-)
>>
>> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
>> news:3a6001c48f88$24958740$a301280a@.phx.gbl...
>> > Hi,
>> >
>> > You can not backup a database or log that is standby mode
>> > with regular backups.
>> >
>> > As a work around, you have to restore the database and
>> > then back it up.
>> >
>> > Here is something you could use:
>> >
>> > USE MASTER
>> > RESTORE DATABASE DB_NAME
>> > WITH RECOVERY
>> >
>> > This changes the standby status to normal db use and then
>> > you can back it up.
>> >
>> > The only thing that I am not sure is what happens at the
>> > next log shipped/restored because it depends how you have
>> > it setup.
>> >
>> > hth
>> >
>> > DeeJay
>> >>--Original Message--
>> >>(SQL Server 2000, SP3a)
>> >>
>> >>Hello all!
>> >>
>> >>I've got a database that is the secondary server in a log-
>> > shipped pair. Whenever I try
>> >>and do a BACKUP on this database, I get an error message
>> > that the database is in a
>> >>READ-ONLY STANDBY mode.
>> >>
>> >>Is there any way to circumvent this, temporarily, and
>> > make a database backup of a
>> >>log-shipped database?
>> >>
>> >>Thanks!
>> >>
>> >>
>> >>.
>> >>
>>
>|||You might want to test this out in dev first.
I am not sure if this is supported by MS or if your log
shipping will work properly again.
You might have to re-configure log-shipping.
DeeJay
>--Original Message--
>Thanks Tibor. I don't think that we have the main
database backup available from the
>fallback machine.
>Any way we can use an undocumented "flag" in some system
table to toggle the DB back in
>STANDBY mode? ;-)
>Just to give you the heads up: we've got our production
databases log-shipped into our
>corporate network. If we can leverage these log-shipped
databases, we won't have to pay
>the network price to copy from production again.
>Thanks for any additional help you might be able to
provide! :-)
>
>"Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in message
>news:uDrJTt4jEHA.2908@.TK2MSFTNGP10.phx.gbl...
>> Hi John,
>> * Change the status of the database back to STANDBY
(dunno how to do this yet).
>> No can do. The recovery procedures etc in SQL Server
aren't written to handle this
>> scenario, quite simply. Any
>> way you can grab the log backups already on the
fallback machine?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "John Peterson" <j0hnp@.comcast.net> wrote in message
>> news:uXJ9wm4jEHA.632@.TK2MSFTNGP12.phx.gbl...
>> Thanks DeeJay!
>> What we're thinking (if you'll humor us for a moment):
>> * Temporarily disable the Job that's responsible for
processing the log-shipped
>> transaction logs.
>> * Change the status of the database to get it out of
STANDBY mode (as per your
>> recommendation).
>> * Back up the database.
>> * Change the status of the database back to STANDBY
(dunno how to do this yet).
>> * Re-enable the Job.
>> I'm not sure if this is advisable, though. For
instance, what does the Log-Shipping
>> Monitor service do? Is it sensitive to any of the
proposed elements above?
>> Thanks for any additional help you can provide! :-)
>>
>> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
>> news:3a6001c48f88$24958740$a301280a@.phx.gbl...
>> > Hi,
>> >
>> > You can not backup a database or log that is standby
mode
>> > with regular backups.
>> >
>> > As a work around, you have to restore the database
and
>> > then back it up.
>> >
>> > Here is something you could use:
>> >
>> > USE MASTER
>> > RESTORE DATABASE DB_NAME
>> > WITH RECOVERY
>> >
>> > This changes the standby status to normal db use and
then
>> > you can back it up.
>> >
>> > The only thing that I am not sure is what happens at
the
>> > next log shipped/restored because it depends how you
have
>> > it setup.
>> >
>> > hth
>> >
>> > DeeJay
>> >>--Original Message--
>> >>(SQL Server 2000, SP3a)
>> >>
>> >>Hello all!
>> >>
>> >>I've got a database that is the secondary server in
a log-
>> > shipped pair. Whenever I try
>> >>and do a BACKUP on this database, I get an error
message
>> > that the database is in a
>> >>READ-ONLY STANDBY mode.
>> >>
>> >>Is there any way to circumvent this, temporarily, and
>> > make a database backup of a
>> >>log-shipped database?
>> >>
>> >>Thanks!
>> >>
>> >>
>> >>.
>> >>
>>
>>
>
>.
>|||> Any way we can use an undocumented "flag" in some system table to toggle the DB back in
> STANDBY mode? ;-)
Nope, none that I know of. Just think about it. When you bring a db out of standby mode, you get the recovery
work persisted. Stuff has been rolled forward and rolled back. Period. How would you be able to apply a later
log backup onto this, as the log records in that log backup are totally out-of sync with the database you have
performed a permanent recovery on?
For this to work, MS would need to do some changes in how the recovery process work or give us some other
option for recovery (NO_RECOVERY, RECOVERY, STANDBY, QUASI_PERMANENT_RECOVERY).
Or MS would need to change SQL Server so it allow us to do a backup of a database in STANDBY mode. (And here
I'm too tired right now to consider what ramifications that would have on log record sequencing and recovery
;-) ).
I know this question has been on the table before, so you might want to check the archives to see if someone
came up with anything. I have a feeling that you are out of luck, though...
Perhaps you should opt for a home-grown log shipping solution, to give you better control of handling of the
backup files?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Peterson" <j0hnp@.comcast.net> wrote in message news:%23UXfE54jEHA.2412@.TK2MSFTNGP15.phx.gbl...
> Thanks Tibor. I don't think that we have the main database backup available from the
> fallback machine.
> Any way we can use an undocumented "flag" in some system table to toggle the DB back in
> STANDBY mode? ;-)
> Just to give you the heads up: we've got our production databases log-shipped into our
> corporate network. If we can leverage these log-shipped databases, we won't have to pay
> the network price to copy from production again.
> Thanks for any additional help you might be able to provide! :-)
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uDrJTt4jEHA.2908@.TK2MSFTNGP10.phx.gbl...
> > Hi John,
> >
> >> * Change the status of the database back to STANDBY (dunno how to do this yet).
> >
> > No can do. The recovery procedures etc in SQL Server aren't written to handle this
> > scenario, quite simply. Any
> > way you can grab the log backups already on the fallback machine?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:uXJ9wm4jEHA.632@.TK2MSFTNGP12.phx.gbl...
> >> Thanks DeeJay!
> >>
> >> What we're thinking (if you'll humor us for a moment):
> >>
> >> * Temporarily disable the Job that's responsible for processing the log-shipped
> >> transaction logs.
> >> * Change the status of the database to get it out of STANDBY mode (as per your
> >> recommendation).
> >> * Back up the database.
> >> * Change the status of the database back to STANDBY (dunno how to do this yet).
> >> * Re-enable the Job.
> >>
> >> I'm not sure if this is advisable, though. For instance, what does the Log-Shipping
> >> Monitor service do? Is it sensitive to any of the proposed elements above?
> >>
> >> Thanks for any additional help you can provide! :-)
> >>
> >>
> >> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> >> news:3a6001c48f88$24958740$a301280a@.phx.gbl...
> >> > Hi,
> >> >
> >> > You can not backup a database or log that is standby mode
> >> > with regular backups.
> >> >
> >> > As a work around, you have to restore the database and
> >> > then back it up.
> >> >
> >> > Here is something you could use:
> >> >
> >> > USE MASTER
> >> > RESTORE DATABASE DB_NAME
> >> > WITH RECOVERY
> >> >
> >> > This changes the standby status to normal db use and then
> >> > you can back it up.
> >> >
> >> > The only thing that I am not sure is what happens at the
> >> > next log shipped/restored because it depends how you have
> >> > it setup.
> >> >
> >> > hth
> >> >
> >> > DeeJay
> >> >>--Original Message--
> >> >>(SQL Server 2000, SP3a)
> >> >>
> >> >>Hello all!
> >> >>
> >> >>I've got a database that is the secondary server in a log-
> >> > shipped pair. Whenever I try
> >> >>and do a BACKUP on this database, I get an error message
> >> > that the database is in a
> >> >>READ-ONLY STANDBY mode.
> >> >>
> >> >>Is there any way to circumvent this, temporarily, and
> >> > make a database backup of a
> >> >>log-shipped database?
> >> >>
> >> >>Thanks!
> >> >>
> >> >>
> >> >>.
> >> >>
> >>
> >>
> >
> >
>|||Thanks Tibor! It's clear I don't understand the whole RECOVERY business.
I had *hoped* that, by temporarily suspending the log file processing, I could somehow get
the DB in a state where it was backup-able. But, if I'm understanding you correctly, it
sounds as if, by virtue of performing a backup on the DB, I'd be "marking" the transaction
log in such a way as to be incompatible with the normal log files when they're later
resumed.
Could I, then, do a detach and copy the underlying .MDF/.LDF files? Or would that break
the whole log-shipping "linkage"?
Thanks again!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:%23tykrd5jEHA.3896@.TK2MSFTNGP15.phx.gbl...
>> Any way we can use an undocumented "flag" in some system table to toggle the DB back in
>> STANDBY mode? ;-)
> Nope, none that I know of. Just think about it. When you bring a db out of standby mode,
> you get the recovery
> work persisted. Stuff has been rolled forward and rolled back. Period. How would you be
> able to apply a later
> log backup onto this, as the log records in that log backup are totally out-of sync with
> the database you have
> performed a permanent recovery on?
> For this to work, MS would need to do some changes in how the recovery process work or
> give us some other
> option for recovery (NO_RECOVERY, RECOVERY, STANDBY, QUASI_PERMANENT_RECOVERY).
> Or MS would need to change SQL Server so it allow us to do a backup of a database in
> STANDBY mode. (And here
> I'm too tired right now to consider what ramifications that would have on log record
> sequencing and recovery
> ;-) ).
> I know this question has been on the table before, so you might want to check the
> archives to see if someone
> came up with anything. I have a feeling that you are out of luck, though...
> Perhaps you should opt for a home-grown log shipping solution, to give you better
> control of handling of the
> backup files?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:%23UXfE54jEHA.2412@.TK2MSFTNGP15.phx.gbl...
>> Thanks Tibor. I don't think that we have the main database backup available from the
>> fallback machine.
>> Any way we can use an undocumented "flag" in some system table to toggle the DB back in
>> STANDBY mode? ;-)
>> Just to give you the heads up: we've got our production databases log-shipped into our
>> corporate network. If we can leverage these log-shipped databases, we won't have to
>> pay
>> the network price to copy from production again.
>> Thanks for any additional help you might be able to provide! :-)
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:uDrJTt4jEHA.2908@.TK2MSFTNGP10.phx.gbl...
>> > Hi John,
>> >
>> >> * Change the status of the database back to STANDBY (dunno how to do this yet).
>> >
>> > No can do. The recovery procedures etc in SQL Server aren't written to handle this
>> > scenario, quite simply. Any
>> > way you can grab the log backups already on the fallback machine?
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> >
>> >
>> > "John Peterson" <j0hnp@.comcast.net> wrote in message
>> > news:uXJ9wm4jEHA.632@.TK2MSFTNGP12.phx.gbl...
>> >> Thanks DeeJay!
>> >>
>> >> What we're thinking (if you'll humor us for a moment):
>> >>
>> >> * Temporarily disable the Job that's responsible for processing the log-shipped
>> >> transaction logs.
>> >> * Change the status of the database to get it out of STANDBY mode (as per your
>> >> recommendation).
>> >> * Back up the database.
>> >> * Change the status of the database back to STANDBY (dunno how to do this yet).
>> >> * Re-enable the Job.
>> >>
>> >> I'm not sure if this is advisable, though. For instance, what does the Log-Shipping
>> >> Monitor service do? Is it sensitive to any of the proposed elements above?
>> >>
>> >> Thanks for any additional help you can provide! :-)
>> >>
>> >>
>> >> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
>> >> news:3a6001c48f88$24958740$a301280a@.phx.gbl...
>> >> > Hi,
>> >> >
>> >> > You can not backup a database or log that is standby mode
>> >> > with regular backups.
>> >> >
>> >> > As a work around, you have to restore the database and
>> >> > then back it up.
>> >> >
>> >> > Here is something you could use:
>> >> >
>> >> > USE MASTER
>> >> > RESTORE DATABASE DB_NAME
>> >> > WITH RECOVERY
>> >> >
>> >> > This changes the standby status to normal db use and then
>> >> > you can back it up.
>> >> >
>> >> > The only thing that I am not sure is what happens at the
>> >> > next log shipped/restored because it depends how you have
>> >> > it setup.
>> >> >
>> >> > hth
>> >> >
>> >> > DeeJay
>> >> >>--Original Message--
>> >> >>(SQL Server 2000, SP3a)
>> >> >>
>> >> >>Hello all!
>> >> >>
>> >> >>I've got a database that is the secondary server in a log-
>> >> > shipped pair. Whenever I try
>> >> >>and do a BACKUP on this database, I get an error message
>> >> > that the database is in a
>> >> >>READ-ONLY STANDBY mode.
>> >> >>
>> >> >>Is there any way to circumvent this, temporarily, and
>> >> > make a database backup of a
>> >> >>log-shipped database?
>> >> >>
>> >> >>Thanks!
>> >> >>
>> >> >>
>> >> >>.
>> >> >>
>> >>
>> >>
>> >
>> >
>>
>|||I think that when you do recovery, log records are either removed or added (possibly both) to the
transaction log. This means that a later log backup from the production database will not just be
able to add the log records to the log-shipped database, because the transaction log has been
changed. The LSN (log sequence numbers) doesn't match anymore.
I haven't tested whether you can detach and attach a database a database in STANDBY mode. Give it a
try. If not, you might consider stopping SQL server and just grabbing the files. Not supported and
not guaranteed that you can attach such files, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Peterson" <j0hnp@.comcast.net> wrote in message news:O991LI6jEHA.1348@.TK2MSFTNGP15.phx.gbl...
> Thanks Tibor! It's clear I don't understand the whole RECOVERY business.
> I had *hoped* that, by temporarily suspending the log file processing, I could somehow get
> the DB in a state where it was backup-able. But, if I'm understanding you correctly, it
> sounds as if, by virtue of performing a backup on the DB, I'd be "marking" the transaction
> log in such a way as to be incompatible with the normal log files when they're later
> resumed.
> Could I, then, do a detach and copy the underlying .MDF/.LDF files? Or would that break
> the whole log-shipping "linkage"?
> Thanks again!
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23tykrd5jEHA.3896@.TK2MSFTNGP15.phx.gbl...
> >> Any way we can use an undocumented "flag" in some system table to toggle the DB back in
> >> STANDBY mode? ;-)
> >
> > Nope, none that I know of. Just think about it. When you bring a db out of standby mode,
> > you get the recovery
> > work persisted. Stuff has been rolled forward and rolled back. Period. How would you be
> > able to apply a later
> > log backup onto this, as the log records in that log backup are totally out-of sync with
> > the database you have
> > performed a permanent recovery on?
> > For this to work, MS would need to do some changes in how the recovery process work or
> > give us some other
> > option for recovery (NO_RECOVERY, RECOVERY, STANDBY, QUASI_PERMANENT_RECOVERY).
> >
> > Or MS would need to change SQL Server so it allow us to do a backup of a database in
> > STANDBY mode. (And here
> > I'm too tired right now to consider what ramifications that would have on log record
> > sequencing and recovery
> > ;-) ).
> >
> > I know this question has been on the table before, so you might want to check the
> > archives to see if someone
> > came up with anything. I have a feeling that you are out of luck, though...
> >
> > Perhaps you should opt for a home-grown log shipping solution, to give you better
> > control of handling of the
> > backup files?
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:%23UXfE54jEHA.2412@.TK2MSFTNGP15.phx.gbl...
> >> Thanks Tibor. I don't think that we have the main database backup available from the
> >> fallback machine.
> >>
> >> Any way we can use an undocumented "flag" in some system table to toggle the DB back in
> >> STANDBY mode? ;-)
> >>
> >> Just to give you the heads up: we've got our production databases log-shipped into our
> >> corporate network. If we can leverage these log-shipped databases, we won't have to
> >> pay
> >> the network price to copy from production again.
> >>
> >> Thanks for any additional help you might be able to provide! :-)
> >>
> >>
> >> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> >> news:uDrJTt4jEHA.2908@.TK2MSFTNGP10.phx.gbl...
> >> > Hi John,
> >> >
> >> >> * Change the status of the database back to STANDBY (dunno how to do this yet).
> >> >
> >> > No can do. The recovery procedures etc in SQL Server aren't written to handle this
> >> > scenario, quite simply. Any
> >> > way you can grab the log backups already on the fallback machine?
> >> >
> >> > --
> >> > Tibor Karaszi, SQL Server MVP
> >> > http://www.karaszi.com/sqlserver/default.asp
> >> > http://www.solidqualitylearning.com/
> >> >
> >> >
> >> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> >> > news:uXJ9wm4jEHA.632@.TK2MSFTNGP12.phx.gbl...
> >> >> Thanks DeeJay!
> >> >>
> >> >> What we're thinking (if you'll humor us for a moment):
> >> >>
> >> >> * Temporarily disable the Job that's responsible for processing the log-shipped
> >> >> transaction logs.
> >> >> * Change the status of the database to get it out of STANDBY mode (as per your
> >> >> recommendation).
> >> >> * Back up the database.
> >> >> * Change the status of the database back to STANDBY (dunno how to do this yet).
> >> >> * Re-enable the Job.
> >> >>
> >> >> I'm not sure if this is advisable, though. For instance, what does the Log-Shipping
> >> >> Monitor service do? Is it sensitive to any of the proposed elements above?
> >> >>
> >> >> Thanks for any additional help you can provide! :-)
> >> >>
> >> >>
> >> >> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> >> >> news:3a6001c48f88$24958740$a301280a@.phx.gbl...
> >> >> > Hi,
> >> >> >
> >> >> > You can not backup a database or log that is standby mode
> >> >> > with regular backups.
> >> >> >
> >> >> > As a work around, you have to restore the database and
> >> >> > then back it up.
> >> >> >
> >> >> > Here is something you could use:
> >> >> >
> >> >> > USE MASTER
> >> >> > RESTORE DATABASE DB_NAME
> >> >> > WITH RECOVERY
> >> >> >
> >> >> > This changes the standby status to normal db use and then
> >> >> > you can back it up.
> >> >> >
> >> >> > The only thing that I am not sure is what happens at the
> >> >> > next log shipped/restored because it depends how you have
> >> >> > it setup.
> >> >> >
> >> >> > hth
> >> >> >
> >> >> > DeeJay
> >> >> >>--Original Message--
> >> >> >>(SQL Server 2000, SP3a)
> >> >> >>
> >> >> >>Hello all!
> >> >> >>
> >> >> >>I've got a database that is the secondary server in a log-
> >> >> > shipped pair. Whenever I try
> >> >> >>and do a BACKUP on this database, I get an error message
> >> >> > that the database is in a
> >> >> >>READ-ONLY STANDBY mode.
> >> >> >>
> >> >> >>Is there any way to circumvent this, temporarily, and
> >> >> > make a database backup of a
> >> >> >>log-shipped database?
> >> >> >>
> >> >> >>Thanks!
> >> >> >>
> >> >> >>
> >> >> >>.
> >> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||Hello Olu!
We had hoped to be able to grab some of these Production databases for Dev and QE testing.
It'd be more convenient to grab them from our DR environment (the log-shipped environment)
because it's already on our corporate network. But, I see that it's proving to be more of
a challenge than we had hoped. ;-)
Out of curiosity, how would I configure Replication over log-shipping? Does that mean I'd
set up a log-shipped DB as the Replication Publisher? I would have thought that couldn't
be done on a read-only DB...
At this point, I'm kind of considering using DTS and the Transfer Database Task to
accomplish what I want. Some of the DBs are big, and I hate the thought of essentially
BCPing everything out, but it *does* appear to work...
"Olu Adedeji" <OluAdedeji@.discussions.microsoft.com> wrote in message
news:C91B47A6-F4D2-4599-819F-D87924CE42D7@.microsoft.com...
> Hi,
> Just for my benefit, what would be the purpose of backing up a database that
> does not change? I assume that the secondary DB of the pair resides in DR and
> as a result the site will be protected (fire proof etc.) secondly the
> Database in the prod environment is being backed up and the backups are sent
> off site.
> - You might want to consider Replication over logshipping of you really must
> backup the secondary DB .
> "John Peterson" wrote:
>> (SQL Server 2000, SP3a)
>> Hello all!
>> I've got a database that is the secondary server in a log-shipped pair. Whenever I try
>> and do a BACKUP on this database, I get an error message that the database is in a
>> READ-ONLY STANDBY mode.
>> Is there any way to circumvent this, temporarily, and make a database backup of a
>> log-shipped database?
>> Thanks!
>>|||Well, *shoot*! As it turns out, the DTS "Transfer Databases Task" will bring a DB out of
RECOVERY mode. <sigh> So that's a no go. :-(
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eCi9jeCkEHA.2652@.TK2MSFTNGP15.phx.gbl...
> Hello Olu!
> We had hoped to be able to grab some of these Production databases for Dev and QE
> testing. It'd be more convenient to grab them from our DR environment (the log-shipped
> environment) because it's already on our corporate network. But, I see that it's
> proving to be more of a challenge than we had hoped. ;-)
> Out of curiosity, how would I configure Replication over log-shipping? Does that mean
> I'd set up a log-shipped DB as the Replication Publisher? I would have thought that
> couldn't be done on a read-only DB...
> At this point, I'm kind of considering using DTS and the Transfer Database Task to
> accomplish what I want. Some of the DBs are big, and I hate the thought of essentially
> BCPing everything out, but it *does* appear to work...
>
> "Olu Adedeji" <OluAdedeji@.discussions.microsoft.com> wrote in message
> news:C91B47A6-F4D2-4599-819F-D87924CE42D7@.microsoft.com...
>> Hi,
>> Just for my benefit, what would be the purpose of backing up a database that
>> does not change? I assume that the secondary DB of the pair resides in DR and
>> as a result the site will be protected (fire proof etc.) secondly the
>> Database in the prod environment is being backed up and the backups are sent
>> off site.
>> - You might want to consider Replication over logshipping of you really must
>> backup the secondary DB .
>> "John Peterson" wrote:
>> (SQL Server 2000, SP3a)
>> Hello all!
>> I've got a database that is the secondary server in a log-shipped pair. Whenever I
>> try
>> and do a BACKUP on this database, I get an error message that the database is in a
>> READ-ONLY STANDBY mode.
>> Is there any way to circumvent this, temporarily, and make a database backup of a
>> log-shipped database?
>> Thanks!
>>
>

Monday, March 19, 2012

How can db_owner restore db from backup and keep his permissions?

Hi there
I'm developing an application, and from time to time I take it to
production environment and keep it up to date with dev version. When I
tried to do this alone (istead of sysadmin) I got some error saying
that I didn't have enough permissions to do restore (despite I was a
db_owner).
Is it possible, that the reason for that is the fact that in dev
version which I was restoring there was no user which had db_owner
permissions? so, I was working on this database as some user with
db_owner permissions and restored it to the state in which there wasn't
any user mapped to my login in db anymore.
Is it possible? If so, then how can I restore db as a db_owner?
thanks a lot
HPFrom BOL:
"If the database being restored does not exist, the user must have CREATE
DATABASE permissions to be able to execute RESTORE. If the database exists,
RESTORE permissions default to members of the sysadmin and dbcreator fixed
server roles and the owner (dbo) of the database (for the FROM
DATABASE_SNAPSHOT option, the database always exists)."
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

How can db_owner restore db from backup and keep his permissions?

Hi there
I'm developing an application, and from time to time I take it to
production environment and keep it up to date with dev version. When I
tried to do this alone (istead of sysadmin) I got some error saying
that I didn't have enough permissions to do restore (despite I was a
db_owner).
Is it possible, that the reason for that is the fact that in dev
version which I was restoring there was no user which had db_owner
permissions? so, I was working on this database as some user with
db_owner permissions and restored it to the state in which there wasn't
any user mapped to my login in db anymore.
Is it possible? If so, then how can I restore db as a db_owner?
thanks a lot
HP
From BOL:
"If the database being restored does not exist, the user must have CREATE
DATABASE permissions to be able to execute RESTORE. If the database exists,
RESTORE permissions default to members of the sysadmin and dbcreator fixed
server roles and the owner (dbo) of the database (for the FROM
DATABASE_SNAPSHOT option, the database always exists)."
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Monday, March 26, 2012

How can I cancel a backup?

How can I cancel a backup?

How can I cancel a backup?

Friday, March 23, 2012

How can I buy SQL 6.5?

How can I buy SQL 6.5?

How can I buy SQL 6.5?

How can i backup sql 2000 using win 2003 backup util

How can i backup sql 2000 using win 2003 backup util

How can i backup sql 2000 using win 2003 backup util

How can I backup and restore a database

How can I back up a log-shipped database?

How can I back up a log-shipped database?

How can I back up a log-shipped database?

How can I back up a log-shipped database?

Monday, March 19, 2012

How can db_owner restore db from backup and keep his permissions?

How can db_owner restore db from backup and keep his permissions?

How can db_owner restore db from backup and keep his permissions?

hot backup

Blog Archive

About Me