restore master database: transaction

Showing posts with label transaction. Show all posts

Friday, March 30, 2012

Restoreing From Log File

if the datafile is corrupted but log file is there how do I restore the
database with latest transaction from log file in sql server
*** Sent via Developersdex http://www.codecomments.com ***Backup the log file (using NO_TRUNCATE), restore latest database backup, and
all subsequent log
backups (including this last one).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jaydip Das" <jaydip_j@.hotmail.com> wrote in message news:ezGvukroFHA.2444@.tk2msftngp13.phx.
gbl...
> if the datafile is corrupted but log file is there how do I restore the
> database with latest transaction from log file in sql server
> *** Sent via Developersdex http://www.codecomments.com ***

restored database LOADING for ever

I restored a 175g database using enterprise manager. It had about 6
transaction logs in the backup set and I checked the point in time restore.
I went back and it said DB restore is complete. But the database icon still
shows (loading). How do I clear the loading status and be able to access and
use my Database? Please help as I really need to work tonight. Thanks.
JamesTry executing a restore using the recovery option:
RESTORE DATABASE YourDatabase
WITH RECOVERY
-Sue
On Mon, 28 Feb 2005 20:13:02 -0800, "James Juno"
<JamesJuno@.discussions.microsoft.com> wrote:
>I restored a 175g database using enterprise manager. It had about 6
>transaction logs in the backup set and I checked the point in time restore.
>I went back and it said DB restore is complete. But the database icon still
>shows (loading). How do I clear the loading status and be able to access and
>use my Database? Please help as I really need to work tonight. Thanks.
>James|||Sue,
I did and it helped. Thank you very much
James
"Sue Hoegemeier" wrote:
> Try executing a restore using the recovery option:
> RESTORE DATABASE YourDatabase
> WITH RECOVERY
> -Sue
> On Mon, 28 Feb 2005 20:13:02 -0800, "James Juno"
> <JamesJuno@.discussions.microsoft.com> wrote:
> >I restored a 175g database using enterprise manager. It had about 6
> >transaction logs in the backup set and I checked the point in time restore.
> >I went back and it said DB restore is complete. But the database icon still
> >shows (loading). How do I clear the loading status and be able to access and
> >use my Database? Please help as I really need to work tonight. Thanks.
> >
> >James
>sql

Restore without big transaction log

All,
Our production database is growing quite rapidly and I'm having trouble
refreshing our QA and Dev environments due to limited disk space on those
servers. My process now is
- Restore the production DB with a new name
- Set to simple and shrink the log file to 0
- Back up the shrunk db to a new .bak file
- Restore to dev or QA from that .bak file
Our production DB is way to busy to shrink the log file on and it's kind of
a pain to go through all those steps. I was wondering if anyone knew a way to
restore from a backup with an empty .trn file? I don't see that this is
possible, but thought I'd ask.
Thanks in advance.
> I was wondering if anyone knew a way to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
Your assumption is correct. A restored database need as big file size for each file as the one you
were restoring from.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:3357D5E4-5001-42C7-8ED5-E854B7E90711@.microsoft.com...
> All,
> Our production database is growing quite rapidly and I'm having trouble
> refreshing our QA and Dev environments due to limited disk space on those
> servers. My process now is
> - Restore the production DB with a new name
> - Set to simple and shrink the log file to 0
> - Back up the shrunk db to a new .bak file
> - Restore to dev or QA from that .bak file
> Our production DB is way to busy to shrink the log file on and it's kind of
> a pain to go through all those steps. I was wondering if anyone knew a way to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
> Thanks in advance.
|||On Jul 10, 2:20 pm, sqlboy2000 <sqlboy2...@.discussions.microsoft.com>
wrote:
> All,
> Our production database is growing quite rapidly and I'm having trouble
> refreshing our QA and Dev environments due to limited disk space on those
> servers. My process now is
> - Restore the production DB with a new name
> - Set to simple and shrink the log file to 0
> - Back up the shrunk db to a new .bak file
> - Restore to dev or QA from that .bak file
> Our production DB is way to busy to shrink the log file on and it's kind of
> a pain to go through all those steps. I was wondering if anyone knew a way to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
> Thanks in advance.
One solution might be once you backup your production database (full
backup) to truncate your transaction log and shrink it, then perform
the restore after that. I would think that would clear up quite a few
space issues.
|||> One solution might be once you backup your production database (full
> backup) to truncate your transaction log and shrink it, then perform
> the restore after that.
This won't help. I assume you mean shrink the transaction log file for the (existing) destination
database. Won't help, since SQL Server need to create each file with same size as the originating
database has.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"acorcoran" <acorcoran@.gmail.com> wrote in message
news:1184209592.791721.108060@.k79g2000hse.googlegr oups.com...
> On Jul 10, 2:20 pm, sqlboy2000 <sqlboy2...@.discussions.microsoft.com>
> wrote:
> One solution might be once you backup your production database (full
> backup) to truncate your transaction log and shrink it, then perform
> the restore after that. I would think that would clear up quite a few
> space issues.
>
|||On Jul 12, 2:58 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> This won't help. I assume you mean shrink the transaction log file for the (existing) destination
> database. Won't help, since SQL Server need to create each file with same size as the originating
> database has.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "acorcoran" <acorco...@.gmail.com> wrote in message
> news:1184209592.791721.108060@.k79g2000hse.googlegr oups.com...
>
>
>
>
> - Show quoted text -
This is true, however, I guess I was thinking of backing up the
production database, truncting the log file, then re-backing up the
database (just for day 1). When you restore, you will be restoring a
very limited size log file. Of course, I guess this simply depends on
how large your log file grows between your full backups. As all
future backups would consist of a full backup then a truncation,
maintaining the size on your source database.
Just a though.

Restore without big transaction log

All,
Our production database is growing quite rapidly and I'm having trouble
refreshing our QA and Dev environments due to limited disk space on those
servers. My process now is
- Restore the production DB with a new name
- Set to simple and shrink the log file to 0
- Back up the shrunk db to a new .bak file
- Restore to dev or QA from that .bak file
Our production DB is way to busy to shrink the log file on and it's kind of
a pain to go through all those steps. I was wondering if anyone knew a way t
o
restore from a backup with an empty .trn file? I don't see that this is
possible, but thought I'd ask.
Thanks in advance.> I was wondering if anyone knew a way to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
Your assumption is correct. A restored database need as big file size for ea
ch file as the one you
were restoring from.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:3357D5E4-5001-42C7-8ED5-E854B7E90711@.microsoft.com...
> All,
> Our production database is growing quite rapidly and I'm having trouble
> refreshing our QA and Dev environments due to limited disk space on those
> servers. My process now is
> - Restore the production DB with a new name
> - Set to simple and shrink the log file to 0
> - Back up the shrunk db to a new .bak file
> - Restore to dev or QA from that .bak file
> Our production DB is way to busy to shrink the log file on and it's kind o
f
> a pain to go through all those steps. I was wondering if anyone knew a way
to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
> Thanks in advance.|||On Jul 10, 2:20 pm, sqlboy2000 <sqlboy2...@.discussions.microsoft.com>
wrote:
> All,
> Our production database is growing quite rapidly and I'm having trouble
> refreshing our QA and Dev environments due to limited disk space on those
> servers. My process now is
> - Restore the production DB with a new name
> - Set to simple and shrink the log file to 0
> - Back up the shrunk db to a new .bak file
> - Restore to dev or QA from that .bak file
> Our production DB is way to busy to shrink the log file on and it's kind o
f
> a pain to go through all those steps. I was wondering if anyone knew a way
to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
> Thanks in advance.
One solution might be once you backup your production database (full
backup) to truncate your transaction log and shrink it, then perform
the restore after that. I would think that would clear up quite a few
space issues.|||> One solution might be once you backup your production database (full
> backup) to truncate your transaction log and shrink it, then perform
> the restore after that.
This won't help. I assume you mean shrink the transaction log file for the (
existing) destination
database. Won't help, since SQL Server need to create each file with same si
ze as the originating
database has.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"acorcoran" <acorcoran@.gmail.com> wrote in message
news:1184209592.791721.108060@.k79g2000hse.googlegroups.com...
> On Jul 10, 2:20 pm, sqlboy2000 <sqlboy2...@.discussions.microsoft.com>
> wrote:
> One solution might be once you backup your production database (full
> backup) to truncate your transaction log and shrink it, then perform
> the restore after that. I would think that would clear up quite a few
> space issues.
>|||On Jul 12, 2:58 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> This won't help. I assume you mean shrink the transaction log file for the
(existing) destination
> database. Won't help, since SQL Server need to create each file with same
size as the originating
> database has.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> "acorcoran" <acorco...@.gmail.com> wrote in message
> news:1184209592.791721.108060@.k79g2000hse.googlegroups.com...
>
>
>
>
>
>
> - Show quoted text -
This is true, however, I guess I was thinking of backing up the
production database, truncting the log file, then re-backing up the
database (just for day 1). When you restore, you will be restoring a
very limited size log file. Of course, I guess this simply depends on
how large your log file grows between your full backups. As all
future backups would consist of a full backup then a truncation,
maintaining the size on your source database.
Just a though.

Restore without big transaction log

All,
Our production database is growing quite rapidly and I'm having trouble
refreshing our QA and Dev environments due to limited disk space on those
servers. My process now is
- Restore the production DB with a new name
- Set to simple and shrink the log file to 0
- Back up the shrunk db to a new .bak file
- Restore to dev or QA from that .bak file
Our production DB is way to busy to shrink the log file on and it's kind of
a pain to go through all those steps. I was wondering if anyone knew a way to
restore from a backup with an empty .trn file? I don't see that this is
possible, but thought I'd ask.
Thanks in advance.> I was wondering if anyone knew a way to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
Your assumption is correct. A restored database need as big file size for each file as the one you
were restoring from.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:3357D5E4-5001-42C7-8ED5-E854B7E90711@.microsoft.com...
> All,
> Our production database is growing quite rapidly and I'm having trouble
> refreshing our QA and Dev environments due to limited disk space on those
> servers. My process now is
> - Restore the production DB with a new name
> - Set to simple and shrink the log file to 0
> - Back up the shrunk db to a new .bak file
> - Restore to dev or QA from that .bak file
> Our production DB is way to busy to shrink the log file on and it's kind of
> a pain to go through all those steps. I was wondering if anyone knew a way to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
> Thanks in advance.|||On Jul 10, 2:20 pm, sqlboy2000 <sqlboy2...@.discussions.microsoft.com>
wrote:
> All,
> Our production database is growing quite rapidly and I'm having trouble
> refreshing our QA and Dev environments due to limited disk space on those
> servers. My process now is
> - Restore the production DB with a new name
> - Set to simple and shrink the log file to 0
> - Back up the shrunk db to a new .bak file
> - Restore to dev or QA from that .bak file
> Our production DB is way to busy to shrink the log file on and it's kind of
> a pain to go through all those steps. I was wondering if anyone knew a way to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
> Thanks in advance.
One solution might be once you backup your production database (full
backup) to truncate your transaction log and shrink it, then perform
the restore after that. I would think that would clear up quite a few
space issues.|||> One solution might be once you backup your production database (full
> backup) to truncate your transaction log and shrink it, then perform
> the restore after that.
This won't help. I assume you mean shrink the transaction log file for the (existing) destination
database. Won't help, since SQL Server need to create each file with same size as the originating
database has.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"acorcoran" <acorcoran@.gmail.com> wrote in message
news:1184209592.791721.108060@.k79g2000hse.googlegroups.com...
> On Jul 10, 2:20 pm, sqlboy2000 <sqlboy2...@.discussions.microsoft.com>
> wrote:
>> All,
>> Our production database is growing quite rapidly and I'm having trouble
>> refreshing our QA and Dev environments due to limited disk space on those
>> servers. My process now is
>> - Restore the production DB with a new name
>> - Set to simple and shrink the log file to 0
>> - Back up the shrunk db to a new .bak file
>> - Restore to dev or QA from that .bak file
>> Our production DB is way to busy to shrink the log file on and it's kind of
>> a pain to go through all those steps. I was wondering if anyone knew a way to
>> restore from a backup with an empty .trn file? I don't see that this is
>> possible, but thought I'd ask.
>> Thanks in advance.
> One solution might be once you backup your production database (full
> backup) to truncate your transaction log and shrink it, then perform
> the restore after that. I would think that would clear up quite a few
> space issues.
>|||On Jul 12, 2:58 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > One solution might be once you backup your production database (full
> > backup) to truncate your transaction log and shrink it, then perform
> > the restore after that.
> This won't help. I assume you mean shrink the transaction log file for the (existing) destination
> database. Won't help, since SQL Server need to create each file with same size as the originating
> database has.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "acorcoran" <acorco...@.gmail.com> wrote in message
> news:1184209592.791721.108060@.k79g2000hse.googlegroups.com...
>
> > On Jul 10, 2:20 pm, sqlboy2000 <sqlboy2...@.discussions.microsoft.com>
> > wrote:
> >> All,
> >> Our production database is growing quite rapidly and I'm having trouble
> >> refreshing our QA and Dev environments due to limited disk space on those
> >> servers. My process now is
> >> - Restore the production DB with a new name
> >> - Set to simple and shrink the log file to 0
> >> - Back up the shrunk db to a new .bak file
> >> - Restore to dev or QA from that .bak file
> >> Our production DB is way to busy to shrink the log file on and it's kind of
> >> a pain to go through all those steps. I was wondering if anyone knew a way to
> >> restore from a backup with an empty .trn file? I don't see that this is
> >> possible, but thought I'd ask.
> >> Thanks in advance.
> > One solution might be once you backup your production database (full
> > backup) to truncate your transaction log and shrink it, then perform
> > the restore after that. I would think that would clear up quite a few
> > space issues.- Hide quoted text -
> - Show quoted text -
This is true, however, I guess I was thinking of backing up the
production database, truncting the log file, then re-backing up the
database (just for day 1). When you restore, you will be restoring a
very limited size log file. Of course, I guess this simply depends on
how large your log file grows between your full backups. As all
future backups would consist of a full backup then a truncation,
maintaining the size on your source database.
Just a though.

Wednesday, March 28, 2012

restore with dev database and transaction log.

Problem:
A database has been deleted. I have a development version, which dates from
some time in the past. (8/23/03). I have transactions logs dataing from
2/15/04. This is not a heavily used system and some data will be better
than anything. However, when I try to apply the first transaction log, the
system tells me, not surprisingly, that this is not a valid log for this
database.
Assistance Requested:
Is there some way to "fool" SQL server into thinking that the transaction
log should apply? Are their date/time stamps recorded in a system table that
I could overwrite? Even if some data is corrupted, something is better than
nothing.
Moral of the story:
Do not believe you operations people when they tell you that tape backups
are working. VERIFY. Force them to do regular tests. (the backup files on
the tape are either missing, or are 16 KB a piece)
John Willard
(company name withheld to protect the innocent -- me!)
Hi John,
As my understanding of your problem, you have delete one of you database on
the production SQL server, then you only have the database on the develope
server. You also have a 'transactions log dating from 2/15/04', you want to
recover the database, right? If I misunderstood you, please feel free to
let me know.
For your 'transactional log dating from 2/15/04', I assume it is a
transaction log backup. However, based on my knowledge, it is not possible
to apply a transaction log backup:
1) Unless the database or differential database backup preceding the
transaction log backup is restored first.
2) Unless all preceding transaction logs created since the database or
differential database were backed up are applied first.
3) If the database has already recovered and all outstanding transactions
have either been rolled back or rolled forward.
You cannot restore the transaction log backup on an old database.
Thanks. For any question, please feel free to post message here and we are
glad to help. Thanks
Best regards
Baisong Wei
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
|||You have understood the problem correctly. I was hoping to be able to change
the timestamp or in some other way trick SQL server into accepting those
logs. However, your message confirms for me that the database is
unrecoverable. Oh, well -- NMF (not my fault)
Thanks
John Willard
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:m8zCMncLEHA.3900@.cpmsftngxa10.phx.gbl...
> Hi John,
> As my understanding of your problem, you have delete one of you database
on
> the production SQL server, then you only have the database on the develope
> server. You also have a 'transactions log dating from 2/15/04', you want
to
> recover the database, right? If I misunderstood you, please feel free to
> let me know.
> For your 'transactional log dating from 2/15/04', I assume it is a
> transaction log backup. However, based on my knowledge, it is not possible
> to apply a transaction log backup:
> 1) Unless the database or differential database backup preceding the
> transaction log backup is restored first.
> 2) Unless all preceding transaction logs created since the database or
> differential database were backed up are applied first.
> 3) If the database has already recovered and all outstanding transactions
> have either been rolled back or rolled forward.
> You cannot restore the transaction log backup on an old database.
> Thanks. For any question, please feel free to post message here and we are
> glad to help. Thanks
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
sql

restore with dev database and transaction log.

Problem:
A database has been deleted. I have a development version, which dates from
some time in the past. (8/23/03). I have transactions logs dataing from
2/15/04. This is not a heavily used system and some data will be better
than anything. However, when I try to apply the first transaction log, the
system tells me, not surprisingly, that this is not a valid log for this
database.
Assistance Requested:
Is there some way to "fool" SQL server into thinking that the transaction
log should apply? Are their date/time stamps recorded in a system table that
I could overwrite? Even if some data is corrupted, something is better than
nothing.
Moral of the story:
Do not believe you operations people when they tell you that tape backups
are working. VERIFY. Force them to do regular tests. (the backup files on
the tape are either missing, or are 16 KB a piece)
John Willard
(company name withheld to protect the innocent -- me!)Hi John,
As my understanding of your problem, you have delete one of you database on
the production SQL server, then you only have the database on the develope
server. You also have a 'transactions log dating from 2/15/04', you want to
recover the database, right? If I misunderstood you, please feel free to
let me know.
For your 'transactional log dating from 2/15/04', I assume it is a
transaction log backup. However, based on my knowledge, it is not possible
to apply a transaction log backup:
1) Unless the database or differential database backup preceding the
transaction log backup is restored first.
2) Unless all preceding transaction logs created since the database or
differential database were backed up are applied first.
3) If the database has already recovered and all outstanding transactions
have either been rolled back or rolled forward.
You cannot restore the transaction log backup on an old database.
Thanks. For any question, please feel free to post message here and we are
glad to help. Thanks
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||You have understood the problem correctly. I was hoping to be able to change
the timestamp or in some other way trick SQL server into accepting those
logs. However, your message confirms for me that the database is
unrecoverable. Oh, well -- NMF (not my fault)
Thanks
John Willard
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:m8zCMncLEHA.3900@.cpmsftngxa10.phx.gbl...
> Hi John,
> As my understanding of your problem, you have delete one of you database
on
> the production SQL server, then you only have the database on the develope
> server. You also have a 'transactions log dating from 2/15/04', you want
to
> recover the database, right? If I misunderstood you, please feel free to
> let me know.
> For your 'transactional log dating from 2/15/04', I assume it is a
> transaction log backup. However, based on my knowledge, it is not possible
> to apply a transaction log backup:
> 1) Unless the database or differential database backup preceding the
> transaction log backup is restored first.
> 2) Unless all preceding transaction logs created since the database or
> differential database were backed up are applied first.
> 3) If the database has already recovered and all outstanding transactions
> have either been rolled back or rolled forward.
> You cannot restore the transaction log backup on an old database.
> Thanks. For any question, please feel free to post message here and we are
> glad to help. Thanks
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>

restore with dev database and transaction log.

Problem:
A database has been deleted. I have a development version, which dates from
some time in the past. (8/23/03). I have transactions logs dataing from
2/15/04. This is not a heavily used system and some data will be better
than anything. However, when I try to apply the first transaction log, the
system tells me, not surprisingly, that this is not a valid log for this
database.
Assistance Requested:
Is there some way to "fool" SQL server into thinking that the transaction
log should apply? Are their date/time stamps recorded in a system table that
I could overwrite? Even if some data is corrupted, something is better than
nothing.
Moral of the story:
Do not believe you operations people when they tell you that tape backups
are working. VERIFY. Force them to do regular tests. (the backup files on
the tape are either missing, or are 16 KB a piece)
John Willard
(company name withheld to protect the innocent -- me!)Hi John,
As my understanding of your problem, you have delete one of you database on
the production SQL server, then you only have the database on the develope
server. You also have a 'transactions log dating from 2/15/04', you want to
recover the database, right? If I misunderstood you, please feel free to
let me know.
For your 'transactional log dating from 2/15/04', I assume it is a
transaction log backup. However, based on my knowledge, it is not possible
to apply a transaction log backup:
1) Unless the database or differential database backup preceding the
transaction log backup is restored first.
2) Unless all preceding transaction logs created since the database or
differential database were backed up are applied first.
3) If the database has already recovered and all outstanding transactions
have either been rolled back or rolled forward.
You cannot restore the transaction log backup on an old database.
Thanks. For any question, please feel free to post message here and we are
glad to help. Thanks
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||You have understood the problem correctly. I was hoping to be able to change
the timestamp or in some other way trick SQL server into accepting those
logs. However, your message confirms for me that the database is
unrecoverable. Oh, well -- NMF (not my fault)
Thanks
John Willard
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:m8zCMncLEHA.3900@.cpmsftngxa10.phx.gbl...
> Hi John,
> As my understanding of your problem, you have delete one of you database
on
> the production SQL server, then you only have the database on the develope
> server. You also have a 'transactions log dating from 2/15/04', you want
to
> recover the database, right? If I misunderstood you, please feel free to
> let me know.
> For your 'transactional log dating from 2/15/04', I assume it is a
> transaction log backup. However, based on my knowledge, it is not possible
> to apply a transaction log backup:
> 1) Unless the database or differential database backup preceding the
> transaction log backup is restored first.
> 2) Unless all preceding transaction logs created since the database or
> differential database were backed up are applied first.
> 3) If the database has already recovered and all outstanding transactions
> have either been rolled back or rolled forward.
> You cannot restore the transaction log backup on an old database.
> Thanks. For any question, please feel free to post message here and we are
> glad to help. Thanks
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>

Restore transaction log?????

Hi all,
I had been made full backup, differential backup, transaction log backup. I want to create new database from these file backup.
I did it follow:
1. Create new database from full backup file, it is ok. then
2. restore this database use transaction log backup file, an message raise :
"The proceding restore operation did not specify WITH NORECOVERY or WITH STANBY. Restart the restore sequence, specfying WITH NORECOVERY or WITH SATNBY for all but the final step... "
thank for reading.if you're restoring the full backup using gui, then check the box saying something about leaving the database in the loading state...wait...yes, leave database nonoperational but able to restore additional transaction logs...there, that's what it say :)|||Originally posted by ms_sql_dba
if you're restoring the full backup using gui, then check the box saying something about leaving the database in the loading state...wait...yes, leave database nonoperational but able to restore additional transaction logs...there, that's what it say :)

thank you,
i do follow your guide, but it does not work. please show me again
best regard.|||You cannot restore a backup if you will be applying a log or differential backup without specifying it not be in recoverable status.|||Originally posted by rhigdon
You cannot restore a backup if you will be applying a log or differential backup without specifying it not be in recoverable status.

Thanks, but i can not understan what you mean, please show me.
Best regard|||Do you have BOL installed? This is from BOL - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_565v.asp

Try this script to illustrate:
--Add devices
USE master
EXEC sp_addumpdevice 'disk', 'MyFull',
'c:\myfull.dat'
EXEC sp_addumpdevice 'disk', 'MyDiff',
'c:\MyDiff.dat'
EXEC sp_addumpdevice 'disk', 'MyLog',
'c:\MyLog.dat'
--create database
create database blah
--set recovery model
alter database blah set recovery full
--do our backups
backup database blah to MyFull

use blah
create table afterfull(ident int identity(1,1))

BACKUP DATABASE blah TO MyDiff WITH DIFFERENTIAL

use blah
drop table afterfull
create table afterdiff (ident int identity(1,1))

backup log blah to MyLog

--Now start our restores
use master
RESTORE DATABASE blah
FROM MyFull
--The above works, this was the full backup
RESTORE DATABASE blah
FROM MyDiff
--The above does not work as you did the full restore without specifying "with recovery"
RESTORE DATABASE blah
FROM MyFull
with norecovery
RESTORE DATABASE blah
FROM MyDiff
--the above works as we specified norecovery with the full restore
--now try the tran log
RESTORE log blah
FROM MyLog
--Look familiar? We need to use the NORECOVERY with the full and DIFF so we can apply the log backup
RESTORE DATABASE blah
FROM MyFull
with norecovery
RESTORE DATABASE blah
FROM MyDiff
with norecovery
RESTORE log blah
FROM MyLog
--Voila!|||1. Create New Database
2. right click all task and restore database
3. tab options at Recovery completion state select "Leave database read-only and able to restore additional transaction logs"

restore transaction log.

Dear friends,
I do transaction log backup 3 times a day 11:30 am, 05:00
pm, 07:00 pm. My question is
1. If I restore transaction log, Do I need restore all
transaction log or only the latest transaction log.
thx."kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:00ef01c378e2$2f125b20$a001280a@.phx.gbl...
> I do transaction log backup 3 times a day 11:30 am, 05:00
> pm, 07:00 pm. My question is
> 1. If I restore transaction log, Do I need restore all
> transaction log or only the latest transaction log.
> 1. If I restore transaction log, Do I need restore all
> transaction log or only the latest transaction log.
Since the transaction log records each change in sequence, you'll need to
restore all the transaction log backups (in sequence).|||"David Campbell" <dave_gc_nospam@.hotmail.com> wrote in message
news:vm2j3l3e5sa44b@.corp.supernews.com...
> "kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
> news:00ef01c378e2$2f125b20$a001280a@.phx.gbl...
> >
> > I do transaction log backup 3 times a day 11:30 am, 05:00
> > pm, 07:00 pm. My question is
> >
> > 1. If I restore transaction log, Do I need restore all
> > transaction log or only the latest transaction log.
> > 1. If I restore transaction log, Do I need restore all
> > transaction log or only the latest transaction log.
> Since the transaction log records each change in sequence, you'll need to
> restore all the transaction log backups (in sequence).
Mr Cambell is absolutely correct. However, if when you backup each log you
specify the option 'with no_truncate' then you would only need to restore
the last log backup. The drawback of this is that your log file will grow
and grow, so you might want to consider immediately before your full backup
backing up the log with the default of 'with truncate'|||> However, if when you backup each log you
> specify the option 'with no_truncate' then you would only need to restore
> the last log backup.
Note that BOL, for the NO_TRUNCATE, parameter say:
"Allows backing up the log in situations where the database is damaged."
Based on that, I would say that using the NO_TRUNCATE option for other purposes isn't supported (as
it isn't documented). Personally, I'd prefer if MS either renamed this option, or documented
explicitly whether it is supported to use for such backup scenario.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"bob simms" <bob_simms@.hotmail.com> wrote in message news:OuvE15QeDHA.1736@.TK2MSFTNGP12.phx.gbl...
> "David Campbell" <dave_gc_nospam@.hotmail.com> wrote in message
> news:vm2j3l3e5sa44b@.corp.supernews.com...
> > "kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
> > news:00ef01c378e2$2f125b20$a001280a@.phx.gbl...
> > >
> > > I do transaction log backup 3 times a day 11:30 am, 05:00
> > > pm, 07:00 pm. My question is
> > >
> > > 1. If I restore transaction log, Do I need restore all
> > > transaction log or only the latest transaction log.
> > > 1. If I restore transaction log, Do I need restore all
> > > transaction log or only the latest transaction log.
> >
> > Since the transaction log records each change in sequence, you'll need to
> > restore all the transaction log backups (in sequence).
> Mr Cambell is absolutely correct. However, if when you backup each log you
> specify the option 'with no_truncate' then you would only need to restore
> the last log backup. The drawback of this is that your log file will grow
> and grow, so you might want to consider immediately before your full backup
> backing up the log with the default of 'with truncate'
>

Restore Transaction log or the .bak

If there is inconsistency on the DB and I want to restore to a last known good state, should I restore the transaction log files which is backed up every hour or the .bak file which is backed up every night. Basically, what is the difference between the t
wo? When should I use the Transaction log files? Thanks.
This depends on when you what you want to do and when you database was last
consistant.
If the last consistant state was just after you last nightly full or
differential backups then you will not need any transaction log backups.
The transaction log allows you to restore up to a particular point in time
between your nightly full and/or differentials backups. If your requirement
is to restore your database to 8:36 AM today, then you will need to use the
last full backup, plus the closest differential (last nights, if there was
one) and then the 1 am, 2 am, 3, am, 4 am, 5 am, 6 am, 7 am, 8 am and 9 am
transaction logs to restore using the STOPAT. The STOPAT command allows you
to stop the restore process at 8:36 am.
What backups do you have and when where they taken? Also when was the last
time you knew for sure the database was consistant.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"mmc" <anonymous@.discussions.microsoft.com> wrote in message
news:AD0940C9-56AF-4851-88B0-CBC2AB05058E@.microsoft.com...
> If there is inconsistency on the DB and I want to restore to a last known
good state, should I restore the transaction log files which is backed up
every hour or the .bak file which is backed up every night. Basically, what
is the difference between the two? When should I use the Transaction log
files? Thanks.
|||You need to read the BOL (Books On-Line) section on Backing up and Restoring
Databases located under Administering SQL Server. It is a bit long and
complex, but then the topic is somewhat complex and deserves a long
explanation.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"mmc" <anonymous@.discussions.microsoft.com> wrote in message
news:AD0940C9-56AF-4851-88B0-CBC2AB05058E@.microsoft.com...
> If there is inconsistency on the DB and I want to restore to a last known
good state, should I restore the transaction log files which is backed up
every hour or the .bak file which is backed up every night. Basically, what
is the difference between the two? When should I use the Transaction log
files? Thanks.

Restore Transaction log or the .bak

If there is inconsistency on the DB and I want to restore to a last known go
od state, should I restore the transaction log files which is backed up ever
y hour or the .bak file which is backed up every night. Basically, what is t
he difference between the t
wo? When should I use the Transaction log files? Thanks.This depends on when you what you want to do and when you database was last
consistant.
If the last consistant state was just after you last nightly full or
differential backups then you will not need any transaction log backups.
The transaction log allows you to restore up to a particular point in time
between your nightly full and/or differentials backups. If your requirement
is to restore your database to 8:36 AM today, then you will need to use the
last full backup, plus the closest differential (last nights, if there was
one) and then the 1 am, 2 am, 3, am, 4 am, 5 am, 6 am, 7 am, 8 am and 9 am
transaction logs to restore using the STOPAT. The STOPAT command allows you
to stop the restore process at 8:36 am.
What backups do you have and when where they taken? Also when was the last
time you knew for sure the database was consistant.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"mmc" <anonymous@.discussions.microsoft.com> wrote in message
news:AD0940C9-56AF-4851-88B0-CBC2AB05058E@.microsoft.com...
> If there is inconsistency on the DB and I want to restore to a last known
good state, should I restore the transaction log files which is backed up
every hour or the .bak file which is backed up every night. Basically, what
is the difference between the two? When should I use the Transaction log
files? Thanks.|||You need to read the BOL (Books On-Line) section on Backing up and Restoring
Databases located under Administering SQL Server. It is a bit long and
complex, but then the topic is somewhat complex and deserves a long
explanation.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"mmc" <anonymous@.discussions.microsoft.com> wrote in message
news:AD0940C9-56AF-4851-88B0-CBC2AB05058E@.microsoft.com...
> If there is inconsistency on the DB and I want to restore to a last known
good state, should I restore the transaction log files which is backed up
every hour or the .bak file which is backed up every night. Basically, what
is the difference between the two? When should I use the Transaction log
files? Thanks.sql

Restore Transaction log or the .bak

If there is inconsistency on the DB and I want to restore to a last known good state, should I restore the transaction log files which is backed up every hour or the .bak file which is backed up every night. Basically, what is the difference between the two? When should I use the Transaction log files? Thanks.This depends on when you what you want to do and when you database was last
consistant.
If the last consistant state was just after you last nightly full or
differential backups then you will not need any transaction log backups.
The transaction log allows you to restore up to a particular point in time
between your nightly full and/or differentials backups. If your requirement
is to restore your database to 8:36 AM today, then you will need to use the
last full backup, plus the closest differential (last nights, if there was
one) and then the 1 am, 2 am, 3, am, 4 am, 5 am, 6 am, 7 am, 8 am and 9 am
transaction logs to restore using the STOPAT. The STOPAT command allows you
to stop the restore process at 8:36 am.
What backups do you have and when where they taken? Also when was the last
time you knew for sure the database was consistant.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"mmc" <anonymous@.discussions.microsoft.com> wrote in message
news:AD0940C9-56AF-4851-88B0-CBC2AB05058E@.microsoft.com...
> If there is inconsistency on the DB and I want to restore to a last known
good state, should I restore the transaction log files which is backed up
every hour or the .bak file which is backed up every night. Basically, what
is the difference between the two? When should I use the Transaction log
files? Thanks.|||You need to read the BOL (Books On-Line) section on Backing up and Restoring
Databases located under Administering SQL Server. It is a bit long and
complex, but then the topic is somewhat complex and deserves a long
explanation.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"mmc" <anonymous@.discussions.microsoft.com> wrote in message
news:AD0940C9-56AF-4851-88B0-CBC2AB05058E@.microsoft.com...
> If there is inconsistency on the DB and I want to restore to a last known
good state, should I restore the transaction log files which is backed up
every hour or the .bak file which is backed up every night. Basically, what
is the difference between the two? When should I use the Transaction log
files? Thanks.

Restore Transaction Log Backups

Hy Guys,

I have a problem with restore Log.

We make a FULL backup everyday at 22:00.
Transaction LOGs Backups are made at every 30 minutes (all the day) (WITH NO INIT) - One Log Backup per Day

I restored a a FULL Backup database from 10/17/2002 with this code:

RESTORE DATABASE dbTest
from disk = 'd:\MSSQL7\BACKUP\DB_20021017_dbsolomonprodapp.bak '
WITH norecovery
GO

Now I need to restore Transactions until 10/18/2002 16:30.
So, I have to restore
Lg_20021017_dbSolomonProdApp.bak
AND
Lg_20021018_dbSolomonProdApp.bak (Until 16:30)

My problem is.. How to make this Log Restore?Q1 My problem is.. How to make this Log Restore?
A1 Sequentially (in your case, by position similar to when restoring from tape, using time constraints as necessary). I suggest the following:
i I find it best to Dump to individual (dump) files. (multiple reasons)
ii Doing so also makes it clearer to see what you are doing, for example:

Restore Database
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_db_200210250 830.Bak'
With Stats = 1, Replace, NoRecovery,
Move 'dbSolomonProdApp_Data' To 'c:\Test_dbSolomonProdApp_Data.mdf',
Move 'dbSolomonProdApp_Log' To 'c:\Test_dbSolomonProdApp_Log.ldf'

--> Sequential dTL Restore Statements:

--> dTL file: 1
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 50843.Trn'
With Stats = 1, NoRecovery

--> dTL file: 2
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 50913.Trn'
With Stats = 1, NoRecovery

--> dTL file: 3
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 50943.Trn'
With Stats = 1, NoRecovery

--> dTL file: 4
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51013.Trn'
With Stats = 1, NoRecovery

--> dTL file: 5
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51043.Trn'
With Stats = 1, NoRecovery

--> dTL file: 6
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51113.Trn'
With Stats = 1, NoRecovery

--> dTL file: 7
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51143.Trn'
With Stats = 1, NoRecovery

--> dTL file: 8
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51213.Trn'
With Stats = 1, NoRecovery

--> dTL file: 9
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51243.Trn'
With Stats = 1, NoRecovery

--> dTL file: 10
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51313.Trn'
With Stats = 1, NoRecovery

--> dTL file: 11
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51343.Trn'
With Stats = 1, NoRecovery

--> dTL file: 12
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51413.Trn'
With Stats = 1, NoRecovery

--> dTL file: 13
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51443.Trn'
With Stats = 1, NoRecovery

--> dTL file: 14
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51513.Trn'
With Stats = 1, NoRecovery

--> dTL file: 15
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51543.Trn'
With Stats = 1, NoRecovery

--> dTL file: 16
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51613.Trn'
With Stats = 1, NoRecovery

--> dTL file: 17
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51643.Trn'
With Stats = 1, NoRecovery

--> dTL file: 18
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51716.Trn'
With Stats = 1, NoRecovery

--> dTL file: 19
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51743.Trn'
With Stats = 1, NoRecovery

--> dTL file: 20
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51813.Trn'
With Stats = 1, NoRecovery

Restore DataBase
[Test_SolomonProdApp]
With Recovery|||Hy DBA,
Your answer works perfect. But i was with my transaction logs in one file. Then i made like this:

-- This command returns many important information about this backup.
-- Like the date of the backup sets and their numbers.
RESTORE HEADERONLY FROM DISK = N'D:\Lg_20021016_dbSolomonProdApp.bak' WITH NOUNLOAD
GO

--Restoring the last Full Backup
RESTORE DATABASE dbSolomonTeste
from disk = 'd:\20021016_dbsolomonprodapp.bak'
WITH norecovery
GO

--Now I recovery all backup sets in the Log backup
RESTORE LOG [dbSolomonTeste]
FROM DISK = N'D:\Lg_20021016_dbSolomonProdApp.bak'
WITH FILE = 1,
NOUNLOAD ,
STATS = 10,
NORECOVERY
GO

RESTORE LOG [dbSolomonTeste]
FROM DISK = N'D:\Lg_20021016_dbSolomonProdApp.bak'
WITH FILE = 2,
NOUNLOAD ,
STATS = 10,
NORECOVERY
GO

RESTORE LOG [dbSolomonTeste]
FROM DISK = N'D:\Lg_20021016_dbSolomonProdApp.bak'
WITH FILE = 3,
NOUNLOAD ,
STATS = 10,
NORECOVERY
GO
.
.
.

RESTORE LOG [dbSolomonTeste]
FROM DISK = N'D:\Lg_20021016_dbSolomonProdApp.bak'
WITH FILE = 48, --In my Case, the last backup set i needed!
NOUNLOAD ,
STATS = 10,
NORECOVERY
GO

Thank you for helping me!!|||RE: Thank you for helping me!!

You are welcome.

As I alluded to, I generally avoid monolithic TL dump files.
Some pain I've experienced with them includes:
i) You want to move it elsewhere to use it, but cannot because it has become huge (either connectivity loss, corruption, etc., occurs during attempted file transfer processes, or the target only has sufficient free disk space on multiple smaller volumes).
ii) RESTORE HEADERONLY results take FOREVER, and fails before the complete header result set is returned (fortunatly, the needed TL dumps on it were usable).
iii) It (a single monolithic TL dump file) gets corrupted, and the whole thing is useless! (That can be extremely painful for an organization, and is the main reason I implement and / or encourage the use of individual dump files.) While I have seen this happen on a Windows OS fileserver, it seems to happen most frequently on third party NAS boxes, (especially older SNAP storage servers), with large monolithic TL dump files. I don't recall ever seeing it happen on a local data storage volume, however; so, if that is your situuation, you may have relativly little (corruption) risk in using monolithic TL dump files.

restore transaction log

dear all,

I want to restore transaction log to other server using transact-SQL.

can we restore serveral transaction log files at the same time ?

anybody have experience on this one.

thanks

Use the "WITH NO RECOVERY" option after each restore to allow subsequent restores. The "WITH STANDBY" option allows the database to be inspected (read only) after eah restore. Use the "WITH RECOVERY" option on the final restore to make the database read/write.

You need to make sure each log is restored in the order in which it was created. Further, you need to ensure the transaction log backup matches the full backup i.e. : you can't skip log backups, and if you truncated the log at any point, then the sequence is broken and you would need to start again with another full backup.

|||

hi rod,

I have tries your suggestion but I still have some problem.

I restore my full backup to other server using no recovery option and then I restore the transaction log. which on the last transcation log I use recovery option.

that part its work perfectly.

the problem is when I want to restore another transcation log there is an error that said :

Msg 3117, Level 16, State 4, Line 1

The log or differential backup cannot be restored because no files are ready to rollforward.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

we do daily restore transaction log to other server.

regards,

-dedys

|||

Ok, now I understand what you are trying to do.

Basically you can't do what you trying. Once you've restored a log "with recovery" you cannot restore another log at a later point. You would need to take another full backup.

What you should probably do is setup transaction log shipping but select the option to leave the database in a read only state.This allows you to read the database between log restores, however, if you are using the database when the log restore is scheduled, then either the resore will fail, or you can select the option to terminate current connections before the restore.

Does this make sense ? Let me know if you need further help on setting up log shipping.

Hope this helps.

|||

hi rod,

yes actually I want to set log shipping.

but I can't set it cause my production server is using sql 2000 and my staging server is using sql 2005.

so I plan to do it manually using job.

if you said that we can't add more transaction log after using with recovery.

so what is the best option for my case.

is there a third party software to do this ?

thanks

-dedys

restore transaction isolation level

Within a store procedure I would like to pump up the isolation level to
serializable just for one particular transaction and restore it back to the
previous level when it's done even if there is a fatal error, would someone
know how to do that? Thank you very much
Here is what I thought the implemenation/body of my store procedure should
be but I see problems
--STORE PROCEDURE --
--If I set transaction isolation level here before the transaction begins,
it will stay there through out the life of the connection, right? I don't
want that.
BEGIN TRANSACTION
-- If I set transaction isolation level here, it might not be
applicable for this transaction, right?
-- DOING SOME QUERYING AND UPDATING HERE
COMMIT TRANSACTION
-- If I restore the isolation level here, there are 2 issues? 1) How to read
it and restore it 2) If my update fails,this will never get executedZeng
You can set the isolation level back to READ COMMITTED:
SET TRANSACTION ISOLATION SERIALIZABLE
BEGIN TRANSACTION
... trap errors!
If errors,
ROLLBACK
else
COMMIT
SET TRANSACTION ISOLATION READ COMMITTED
RETURN
You can read the current isolation level with DBCC USEROPTIONS, but you only
see the isolation level if it's been changed once.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> Within a store procedure I would like to pump up the isolation level to
> serializable just for one particular transaction and restore it back to
the
> previous level when it's done even if there is a fatal error, would
someone
> know how to do that? Thank you very much
> Here is what I thought the implemenation/body of my store procedure should
> be but I see problems
>
> --STORE PROCEDURE --
> --If I set transaction isolation level here before the transaction begins,
> it will stay there through out the life of the connection, right? I don't
> want that.
> BEGIN TRANSACTION
> -- If I set transaction isolation level here, it might not be
> applicable for this transaction, right?
> -- DOING SOME QUERYING AND UPDATING HERE
> COMMIT TRANSACTION
> -- If I restore the isolation level here, there are 2 issues? 1) How to
read
> it and restore it 2) If my update fails,this will never get executed
>|||That won't work well for me because:
1) Fatal error doesn't go through the trap
2) Read Committed is not always what my store proc might start out with.
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Zeng
> You can set the isolation level back to READ COMMITTED:
> SET TRANSACTION ISOLATION SERIALIZABLE
> BEGIN TRANSACTION
> ... trap errors!
> If errors,
> ROLLBACK
> else
> COMMIT
> SET TRANSACTION ISOLATION READ COMMITTED
> RETURN
> You can read the current isolation level with DBCC USEROPTIONS, but you
only
> see the isolation level if it's been changed once.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Zeng" <zzy@.nonospam.com> wrote in message
> news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> >
> > Within a store procedure I would like to pump up the isolation level to
> > serializable just for one particular transaction and restore it back to
> the
> > previous level when it's done even if there is a fatal error, would
> someone
> > know how to do that? Thank you very much
> >
> > Here is what I thought the implemenation/body of my store procedure
should
> > be but I see problems
> >
> >
> > --STORE PROCEDURE --
> >
> > --If I set transaction isolation level here before the transaction
begins,
> > it will stay there through out the life of the connection, right? I
don't
> > want that.
> >
> > BEGIN TRANSACTION
> > -- If I set transaction isolation level here, it might not be
> > applicable for this transaction, right?
> >
> > -- DOING SOME QUERYING AND UPDATING HERE
> >
> > COMMIT TRANSACTION
> >
> > -- If I restore the isolation level here, there are 2 issues? 1) How to
> read
> > it and restore it 2) If my update fails,this will never get executed
> >
> >
>|||Easy: simply add the statement « With HoldLock » to your Select query that
you want to be Serializable.
S. L.
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> Within a store procedure I would like to pump up the isolation level to
> serializable just for one particular transaction and restore it back to
> the
> previous level when it's done even if there is a fatal error, would
> someone
> know how to do that? Thank you very much
> Here is what I thought the implemenation/body of my store procedure should
> be but I see problems
>
> --STORE PROCEDURE --
> --If I set transaction isolation level here before the transaction begins,
> it will stay there through out the life of the connection, right? I don't
> want that.
> BEGIN TRANSACTION
> -- If I set transaction isolation level here, it might not be
> applicable for this transaction, right?
> -- DOING SOME QUERYING AND UPDATING HERE
> COMMIT TRANSACTION
> -- If I restore the isolation level here, there are 2 issues? 1) How to
> read
> it and restore it 2) If my update fails,this will never get executed
>|||Zeng,
There's nothing you can do about the fatal errors that cancel an entire
batch. For fatal errors that simply abort the procedure, you can set up
wrapper code around the call to the proc that will restore the former
isolation level.
Here's one possible way:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @.SetValue varchar(100)
CREATE TABLE #useroptions (SetOption varchar(100), SetValue varchar(100))
INSERT #useroptions
EXEC ('DBCC USEROPTIONS')
SET @.SetValue = (SELECT SetValue FROM #useroptions WHERE SetOption ='isolation level')
DROP TABLE #useroptions
SELECT @.SetValue
-- Simulates a call to a procedure, that changes the isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Now back in the calling code:
IF @.SetValue IS NULL OR @.SetValue = 'read committed'
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
IF @.SetValue = 'read uncommitted'
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @.SetValue = 'serializable'
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
IF @.SetValue = 'repeatable read'
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Ron
--
Ron Talmage
SQL Server MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23E597oh7EHA.2552@.TK2MSFTNGP09.phx.gbl...
> That won't work well for me because:
> 1) Fatal error doesn't go through the trap
> 2) Read Committed is not always what my store proc might start out with.
>
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> > Zeng
> >
> > You can set the isolation level back to READ COMMITTED:
> >
> > SET TRANSACTION ISOLATION SERIALIZABLE
> > BEGIN TRANSACTION
> > ... trap errors!
> >
> > If errors,
> > ROLLBACK
> > else
> > COMMIT
> >
> > SET TRANSACTION ISOLATION READ COMMITTED
> > RETURN
> >
> > You can read the current isolation level with DBCC USEROPTIONS, but you
> only
> > see the isolation level if it's been changed once.
> >
> > Hope this helps,
> > Ron
> > --
> > Ron Talmage
> > SQL Server MVP
> >
> > "Zeng" <zzy@.nonospam.com> wrote in message
> > news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> > >
> > > Within a store procedure I would like to pump up the isolation level
to
> > > serializable just for one particular transaction and restore it back
to
> > the
> > > previous level when it's done even if there is a fatal error, would
> > someone
> > > know how to do that? Thank you very much
> > >
> > > Here is what I thought the implemenation/body of my store procedure
> should
> > > be but I see problems
> > >
> > >
> > > --STORE
PROCEDURE --
> > >
> > > --If I set transaction isolation level here before the transaction
> begins,
> > > it will stay there through out the life of the connection, right? I
> don't
> > > want that.
> > >
> > > BEGIN TRANSACTION
> > > -- If I set transaction isolation level here, it might not be
> > > applicable for this transaction, right?
> > >
> > > -- DOING SOME QUERYING AND UPDATING HERE
> > >
> > > COMMIT TRANSACTION
> > >
> > > -- If I restore the isolation level here, there are 2 issues? 1) How
to
> > read
> > > it and restore it 2) If my update fails,this will never get executed
> > >
> > >
> >
> >
>|||SET commands doesn't stick after the proc execution, so you only need to restore the isolation level
if you have further statements *inside* the procedure that you want to restore isolation level for.
Evidence:
USE tempdb
GO
CREATE PROC p
AS
DBCC USEROPTIONS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DBCC USEROPTIONS
GO
EXEC p
DBCC USEROPTIONS
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zeng" <zzy@.nonospam.com> wrote in message news:%23E597oh7EHA.2552@.TK2MSFTNGP09.phx.gbl...
> That won't work well for me because:
> 1) Fatal error doesn't go through the trap
> 2) Read Committed is not always what my store proc might start out with.
>
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
>> Zeng
>> You can set the isolation level back to READ COMMITTED:
>> SET TRANSACTION ISOLATION SERIALIZABLE
>> BEGIN TRANSACTION
>> ... trap errors!
>> If errors,
>> ROLLBACK
>> else
>> COMMIT
>> SET TRANSACTION ISOLATION READ COMMITTED
>> RETURN
>> You can read the current isolation level with DBCC USEROPTIONS, but you
> only
>> see the isolation level if it's been changed once.
>> Hope this helps,
>> Ron
>> --
>> Ron Talmage
>> SQL Server MVP
>> "Zeng" <zzy@.nonospam.com> wrote in message
>> news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
>> >
>> > Within a store procedure I would like to pump up the isolation level to
>> > serializable just for one particular transaction and restore it back to
>> the
>> > previous level when it's done even if there is a fatal error, would
>> someone
>> > know how to do that? Thank you very much
>> >
>> > Here is what I thought the implemenation/body of my store procedure
> should
>> > be but I see problems
>> >
>> >
>> > --STORE PROCEDURE --
>> >
>> > --If I set transaction isolation level here before the transaction
> begins,
>> > it will stay there through out the life of the connection, right? I
> don't
>> > want that.
>> >
>> > BEGIN TRANSACTION
>> > -- If I set transaction isolation level here, it might not be
>> > applicable for this transaction, right?
>> >
>> > -- DOING SOME QUERYING AND UPDATING HERE
>> >
>> > COMMIT TRANSACTION
>> >
>> > -- If I restore the isolation level here, there are 2 issues? 1) How to
>> read
>> > it and restore it 2) If my update fails,this will never get executed
>> >
>> >
>>
>sql

restore transaction isolation level

Monday, March 26, 2012

Restore Tranaction log

I am trying to restore a Transaction Log in SQL Server 2000 in Full recovery status, However I was received the following error Message:
----------------------------------------
Error message: Exclusive access could not be obtained because the database is in use.
----------------------------------------
Also I changed the UserAccess database from MULTI_USER to SINGLE_USER, but still do not work and shows the same error message.
Please let me know, if you have any solution for this problem.

Thanks,

Try the link below to test drive Lumigent. Hope this helps.
http://www.lumigent.com/

Restore to read-only mode

Hi all,

EM has the following restore option:

"Leave database read-only and able to restore additional transaction logs."

Using this, I can restore to a named transaction, then run a SELECT statement that outputs the current state of my data to a file while the db is still in load mode.

What is the T-SQL version of this EM property, if it exists. I'm using:

restore log MyDatabase from MyDiskDevice with file=1, norecovery,
stopatmark = 'mymark'

I can't find a read-only attribute in BOL though to use with the RESTORE statement. Can I do this in script?

Thanks,

BObALTER DATABASE <dbname> SET READ_ONLY WITH ROLLBACK IMMEDIATE|||Thanks Brett. I'll give it a try.

Bob

Friday, March 30, 2012

Restoreing From Log File

restored database LOADING for ever

Restore without big transaction log

Restore without big transaction log

Restore without big transaction log

Wednesday, March 28, 2012

restore with dev database and transaction log.

restore with dev database and transaction log.

restore with dev database and transaction log.

Restore transaction log?????

restore transaction log.

Restore Transaction log or the .bak

Restore Transaction log or the .bak

Restore Transaction log or the .bak

Restore Transaction Log Backups

restore transaction log

restore transaction isolation level

restore transaction isolation level

restore transaction isolation level

Monday, March 26, 2012

Restore Tranaction log

Restore to read-only mode

restore master database

Blog Archive

About Me