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.
James
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
|||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:
>
>

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 an
d
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 stil
l
>shows (loading). How do I clear the loading status and be able to access a
nd
>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:
>
>

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 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 using Microsoft SQL Server Management Studio?

Can someone help me with restoring a database using sql server
management studio? Every time I attempt to restore a database, I get
the following message:
Restore failed for Server 'localhost\SQLExpress'.
Additional Information:
System.Data.SqlClient.SqlError: Exclusive access could not be
obtained because the database is in use.
I have no problem restarting the db server locally, but in a
production environment where the db is shared, I cannot restart it.
How can I restore a db successfully using the management studio?
Perhaps MS should add a checkbox to the restore dialog that kills all
processes before the restore is issued...
Anyway, thanks for any help
JimHi
It seems that someone is using the database and therefore stopping you
restoring over it. Look at the current activity on the server and check to
see who is connected. You can either kill the process of get the to logoff
properly.
John
"Jim Geurts" wrote:
> Can someone help me with restoring a database using sql server
> management studio? Every time I attempt to restore a database, I get
> the following message:
> Restore failed for Server 'localhost\SQLExpress'.
> Additional Information:
> System.Data.SqlClient.SqlError: Exclusive access could not be
> obtained because the database is in use.
>
> I have no problem restarting the db server locally, but in a
> production environment where the db is shared, I cannot restart it.
> How can I restore a db successfully using the management studio?
> Perhaps MS should add a checkbox to the restore dialog that kills all
> processes before the restore is issued...
> Anyway, thanks for any help
> Jim
>|||On Wed, 31 Oct 2007 07:41:02 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Hi
>It seems that someone is using the database and therefore stopping you
>restoring over it. Look at the current activity on the server and check to
>see who is connected. You can either kill the process of get the to logoff
>properly.
>John
And don't forget to allow for it being yourself! 8-)
Roy Harvey
Beacon Falls, CT|||I would first set the database to single-user mode or restricted user during
restore if you are encountering such errors
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:A29E8900-FD44-4493-84DD-CDCBB0D16F6D@.microsoft.com...
> Hi
> It seems that someone is using the database and therefore stopping you
> restoring over it. Look at the current activity on the server and check to
> see who is connected. You can either kill the process of get the to logoff
> properly.
> John
> "Jim Geurts" wrote:
>> Can someone help me with restoring a database using sql server
>> management studio? Every time I attempt to restore a database, I get
>> the following message:
>> Restore failed for Server 'localhost\SQLExpress'.
>> Additional Information:
>> System.Data.SqlClient.SqlError: Exclusive access could not be
>> obtained because the database is in use.
>>
>> I have no problem restarting the db server locally, but in a
>> production environment where the db is shared, I cannot restart it.
>> How can I restore a db successfully using the management studio?
>> Perhaps MS should add a checkbox to the restore dialog that kills all
>> processes before the restore is issued...
>> Anyway, thanks for any help
>> Jim
>>|||On Oct 31, 10:04 am, "bass_player [SBS-MVP]" <bass_pla...@.mvps.org>
wrote:
> I would first set the database to single-user mode or restricted user during
> restore if you are encountering such errors
> "John Bell" <jbellnewspo...@.hotmail.com> wrote in message
> news:A29E8900-FD44-4493-84DD-CDCBB0D16F6D@.microsoft.com...
> > Hi
> > It seems that someone is using the database and therefore stopping you
> > restoring over it. Look at the current activity on the server and check to
> > see who is connected. You can either kill the process of get the to logoff
> > properly.
> > John
> > "Jim Geurts" wrote:
> >> Can someone help me with restoring a database using sql server
> >> management studio? Every time I attempt to restore a database, I get
> >> the following message:
> >> Restore failed for Server 'localhost\SQLExpress'.
> >> Additional Information:
> >> System.Data.SqlClient.SqlError: Exclusive access could not be
> >> obtained because the database is in use.
> >> I have no problem restarting the db server locally, but in a
> >> production environment where the db is shared, I cannot restart it.
> >> How can I restore a db successfully using the management studio?
> >> Perhaps MS should add a checkbox to the restore dialog that kills all
> >> processes before the restore is issued...
> >> Anyway, thanks for any help
> >> Jim
I tried setting the production db to single user mode in the past, but
then it wouldn't let me connect to restore the db. Honestly, there
has to be a simple way of doing this, no? This seems like a fairly
common task. I'm amazed that it's so difficult to have it work with a
click of a button, using the management studio. Is there a block of
sql that will accomplish everything that is needed (set to single user
mode, restore db, restore back to multi-user mode, etc)?|||On Oct 31, 9:55 am, Jim Geurts <jgeu...@.gmail.com> wrote:
> Can someone help me with restoring a database using sql server
> management studio? Every time I attempt to restore a database, I get
> the following message:
> Restore failed for Server 'localhost\SQLExpress'.
> Additional Information:
> System.Data.SqlClient.SqlError: Exclusive access could not be
> obtained because the database is in use.
> I have no problem restarting the db server locally, but in a
> production environment where the db is shared, I cannot restart it.
> How can I restore a db successfully using the management studio?
> Perhaps MS should add a checkbox to the restore dialog that kills all
> processes before the restore is issued...
> Anyway, thanks for any help
> Jim
Here is another one of the many ways:
Detach the Database. OR
Kill all conections in the Database and Run the Restore T-SQL(Use With
Replace) or thourgh Studio
How critical is this DB ( How many users are connected at one time ),
if not many users, sut them off(Kill) the then restoe the DB|||Hi
You should not be connected to the database to restore, if the database is
the default for your login it may be an issue connecting once you have a
database in single user mode and the "connection" is used. SQL2000 used to
have a disconnect button on the detach dialog which could be used to remove
connections but this is not available on SQL2005.
John
"Jim Geurts" wrote:
> On Oct 31, 10:04 am, "bass_player [SBS-MVP]" <bass_pla...@.mvps.org>
> wrote:
> > I would first set the database to single-user mode or restricted user during
> > restore if you are encountering such errors
> >
> > "John Bell" <jbellnewspo...@.hotmail.com> wrote in message
> >
> > news:A29E8900-FD44-4493-84DD-CDCBB0D16F6D@.microsoft.com...
> >
> > > Hi
> >
> > > It seems that someone is using the database and therefore stopping you
> > > restoring over it. Look at the current activity on the server and check to
> > > see who is connected. You can either kill the process of get the to logoff
> > > properly.
> >
> > > John
> >
> > > "Jim Geurts" wrote:
> >
> > >> Can someone help me with restoring a database using sql server
> > >> management studio? Every time I attempt to restore a database, I get
> > >> the following message:
> >
> > >> Restore failed for Server 'localhost\SQLExpress'.
> >
> > >> Additional Information:
> > >> System.Data.SqlClient.SqlError: Exclusive access could not be
> > >> obtained because the database is in use.
> >
> > >> I have no problem restarting the db server locally, but in a
> > >> production environment where the db is shared, I cannot restart it.
> > >> How can I restore a db successfully using the management studio?
> > >> Perhaps MS should add a checkbox to the restore dialog that kills all
> > >> processes before the restore is issued...
> >
> > >> Anyway, thanks for any help
> >
> > >> Jim
> I tried setting the production db to single user mode in the past, but
> then it wouldn't let me connect to restore the db. Honestly, there
> has to be a simple way of doing this, no? This seems like a fairly
> common task. I'm amazed that it's so difficult to have it work with a
> click of a button, using the management studio. Is there a block of
> sql that will accomplish everything that is needed (set to single user
> mode, restore db, restore back to multi-user mode, etc)?
>|||You can use the toolbar button to script the restore that you are attempting
to do in SSMS. Then add to the top of that script the following command:
ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
After the backup place it back in Multi-user mode.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jim Geurts" <jgeurts@.gmail.com> wrote in message
news:1193844965.038551.326520@.e9g2000prf.googlegroups.com...
> On Oct 31, 10:04 am, "bass_player [SBS-MVP]" <bass_pla...@.mvps.org>
> wrote:
>> I would first set the database to single-user mode or restricted user
>> during
>> restore if you are encountering such errors
>> "John Bell" <jbellnewspo...@.hotmail.com> wrote in message
>> news:A29E8900-FD44-4493-84DD-CDCBB0D16F6D@.microsoft.com...
>> > Hi
>> > It seems that someone is using the database and therefore stopping you
>> > restoring over it. Look at the current activity on the server and check
>> > to
>> > see who is connected. You can either kill the process of get the to
>> > logoff
>> > properly.
>> > John
>> > "Jim Geurts" wrote:
>> >> Can someone help me with restoring a database using sql server
>> >> management studio? Every time I attempt to restore a database, I get
>> >> the following message:
>> >> Restore failed for Server 'localhost\SQLExpress'.
>> >> Additional Information:
>> >> System.Data.SqlClient.SqlError: Exclusive access could not be
>> >> obtained because the database is in use.
>> >> I have no problem restarting the db server locally, but in a
>> >> production environment where the db is shared, I cannot restart it.
>> >> How can I restore a db successfully using the management studio?
>> >> Perhaps MS should add a checkbox to the restore dialog that kills all
>> >> processes before the restore is issued...
>> >> Anyway, thanks for any help
>> >> Jim
> I tried setting the production db to single user mode in the past, but
> then it wouldn't let me connect to restore the db. Honestly, there
> has to be a simple way of doing this, no? This seems like a fairly
> common task. I'm amazed that it's so difficult to have it work with a
> click of a button, using the management studio. Is there a block of
> sql that will accomplish everything that is needed (set to single user
> mode, restore db, restore back to multi-user mode, etc)?
>|||On Oct 31, 12:40 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> You can use the toolbar button to script the restore that you are attempting
> to do in SSMS. Then add to the top of that script the following command:
> ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> After the backup place it back in Multi-user mode.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
> "Jim Geurts" <jgeu...@.gmail.com> wrote in message
> news:1193844965.038551.326520@.e9g2000prf.googlegroups.com...
> > On Oct 31, 10:04 am, "bass_player [SBS-MVP]" <bass_pla...@.mvps.org>
> > wrote:
> >> I would first set the database to single-user mode or restricted user
> >> during
> >> restore if you are encountering such errors
> >> "John Bell" <jbellnewspo...@.hotmail.com> wrote in message
> >>news:A29E8900-FD44-4493-84DD-CDCBB0D16F6D@.microsoft.com...
> >> > Hi
> >> > It seems that someone is using the database and therefore stopping you
> >> > restoring over it. Look at the current activity on the server and check
> >> > to
> >> > see who is connected. You can either kill the process of get the to
> >> > logoff
> >> > properly.
> >> > John
> >> > "Jim Geurts" wrote:
> >> >> Can someone help me with restoring a database using sql server
> >> >> management studio? Every time I attempt to restore a database, I get
> >> >> the following message:
> >> >> Restore failed for Server 'localhost\SQLExpress'.
> >> >> Additional Information:
> >> >> System.Data.SqlClient.SqlError: Exclusive access could not be
> >> >> obtained because the database is in use.
> >> >> I have no problem restarting the db server locally, but in a
> >> >> production environment where the db is shared, I cannot restart it.
> >> >> How can I restore a db successfully using the management studio?
> >> >> Perhaps MS should add a checkbox to the restore dialog that kills all
> >> >> processes before the restore is issued...
> >> >> Anyway, thanks for any help
> >> >> Jim
> > I tried setting the production db to single user mode in the past, but
> > then it wouldn't let me connect to restore the db. Honestly, there
> > has to be a simple way of doing this, no? This seems like a fairly
> > common task. I'm amazed that it's so difficult to have it work with a
> > click of a button, using the management studio. Is there a block of
> > sql that will accomplish everything that is needed (set to single user
> > mode, restore db, restore back to multi-user mode, etc)?
Cool, thanks Andrew. I'll give that a shot.
@.Maninder: The database server is very critical. It's at a hosting
provider and is shared with many other users. Thus, my login defaults
to my database and as John mentioned, that's probably why it always
says someone is connected. As far as my specific db, there are
usually only two or three applications that access it.

restore two databases from one device

Is it possible to have two restores running at the same
time restoring two databases to two different servers from
the same backup device? For example, I have server A and
server B. I want to restore pubs to both A and B at the
same time from last night's backup device. Thanks!Hi,
A Single file can be opened for reading by only one process.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:275f201c46381$aa08f020$a501280a@.phx.gbl...
> Is it possible to have two restores running at the same
> time restoring two databases to two different servers from
> the same backup device? For example, I have server A and
> server B. I want to restore pubs to both A and B at the
> same time from last night's backup device. Thanks!|||Darn NT!!!
>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:275f201c46381$aa08f020$a501280a@.phx.gbl...
>> Is it possible to have two restores running at the same
>> time restoring two databases to two different servers
from
>> the same backup device? For example, I have server A
and
>> server B. I want to restore pubs to both A and B at the
>> same time from last night's backup device. Thanks!
>
>.
>|||No, files can be opened either shared or exclusive.
But sorry, I don't know which mode SQL does.
Why not just try it?
>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:275f201c46381$aa08f020$a501280a@.phx.gbl...
>> Is it possible to have two restores running at the same
>> time restoring two databases to two different servers
from
>> the same backup device? For example, I have server A
and
>> server B. I want to restore pubs to both A and B at the
>> same time from last night's backup device. Thanks!
>
>.
>

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 taking very long time

i have sql 2000 db of about 120 GB. its taking about 10 -12 hours to restore on the same disk as new database.

server configuration is good.

when i try to restore another db of about 10 GB size, its restoring in about 5 minutes.

A slow restore could be any number of things.

Is the backup on the local disk, or the network? What else is the drive with the backup doing? What else is the drive that the restore is going to doing? How much data is in the transaction log that needs to be rolled forward/backward?

|||

backup and restore is on the same local disk.

not much processes are going on, except log backup every 15 min will put data on the disk, the log backup with be finished within seconds.

reg: data in transaction log that needs to be rolled forward/ backword: i really cant say because i dont know how to calculate it.

sql

restore taking very long time

i have sql 2000 db of about 120 GB. its taking about 10 -12 hours to restore on the same disk as new database.

server configuration is good.

when i try to restore another db of about 10 GB size, its restoring in about 5 minutes.

A slow restore could be any number of things.

Is the backup on the local disk, or the network? What else is the drive with the backup doing? What else is the drive that the restore is going to doing? How much data is in the transaction log that needs to be rolled forward/backward?

|||

backup and restore is on the same local disk.

not much processes are going on, except log backup every 15 min will put data on the disk, the log backup with be finished within seconds.

reg: data in transaction log that needs to be rolled forward/ backword: i really cant say because i dont know how to calculate it.

Restore takes forever..

Okay, I understand that when I'm restoring a database that
it needs some time to "create" the empty database before
it starts populating the data, but this is ridiculous...
I have an approximately 60gb database (with separate log
and data drives) that I backup up (to the data drive) in
22 minutes.
I'm now trying to restore that database and it has so far
literally taken 4 hours and the restore progress bar (in
EM) hasn't even started moving yet. Checking current
activity I can see that the restore process is "Waiting
For ASYNC_IO_COMPLETION".
Now I know that my disk configuration is okay because SQL
was able to read the enter 60gb database and write it out
to a backup file in 22 minutes. Why in the world would
it take so long to restore?
My experience has been that this initial part of the
restore (where my guess is that it is creating the "blank"
database) takes forever and then once the progress bars
start it goes really quick.
What is making this take so long? How can I back
something up and then try to restore and it literally take
10 or 20 times as long?
Any help would be greatly appreciated!
thanks!
-dave
P.S. as a postscript... how come apps like SQL and
Exchange can write out 60gb backup files in a matter of
minutes but if I try to copy (not move) a 60gb file from
one directory on a drive to another directory on the same
drive it takes 2 hours? What do Exchange/SQL do that
makes disk access so fast and how can we make is that fast
for normal file copy operations?Dave,
From what I understand, it goes a little something like this...
When you restore, SQL Server has to initialize all pages in the database.
Then, it has to put the data back on _exactly_ those same pages.
If it did not do this, then the internal list linking strategy would be
hosed.
You might consider not having the backup device on the same disk system
during the restore, or you will be stepping all over yourself with
read/write ops, and waste a lot of time thrashing.
James Hokes
"Dave Rose" <anonymous@.discussions.microsoft.com> wrote in message
news:92a801c3eab8$3eb2c090$a301280a@.phx.gbl...

quote:

---

> Okay, I understand that when I'm restoring a database that
> it needs some time to "create" the empty database before
> it starts populating the data, but this is ridiculous...
> I have an approximately 60gb database (with separate log
> and data drives) that I backup up (to the data drive) in
> 22 minutes.
> I'm now trying to restore that database and it has so far
> literally taken 4 hours and the restore progress bar (in
> EM) hasn't even started moving yet. Checking current
> activity I can see that the restore process is "Waiting
> For ASYNC_IO_COMPLETION".
> Now I know that my disk configuration is okay because SQL
> was able to read the enter 60gb database and write it out
> to a backup file in 22 minutes. Why in the world would
> it take so long to restore?
> My experience has been that this initial part of the
> restore (where my guess is that it is creating the "blank"
> database) takes forever and then once the progress bars
> start it goes really quick.
> What is making this take so long? How can I back
> something up and then try to restore and it literally take
> 10 or 20 times as long?
> Any help would be greatly appreciated!
> thanks!
> -dave
> P.S. as a postscript... how come apps like SQL and
> Exchange can write out 60gb backup files in a matter of
> minutes but if I try to copy (not move) a 60gb file from
> one directory on a drive to another directory on the same
> drive it takes 2 hours? What do Exchange/SQL do that
> makes disk access so fast and how can we make is that fast
> for normal file copy operations?

---

Restore takes forever..

Okay, I understand that when I'm restoring a database that
it needs some time to "create" the empty database before
it starts populating the data, but this is ridiculous...
I have an approximately 60gb database (with separate log
and data drives) that I backup up (to the data drive) in
22 minutes.
I'm now trying to restore that database and it has so far
literally taken 4 hours and the restore progress bar (in
EM) hasn't even started moving yet. Checking current
activity I can see that the restore process is "Waiting
For ASYNC_IO_COMPLETION".
Now I know that my disk configuration is okay because SQL
was able to read the enter 60gb database and write it out
to a backup file in 22 minutes. Why in the world would
it take so long to restore?
My experience has been that this initial part of the
restore (where my guess is that it is creating the "blank"
database) takes forever and then once the progress bars
start it goes really quick.
What is making this take so long? How can I back
something up and then try to restore and it literally take
10 or 20 times as long?
Any help would be greatly appreciated!
thanks!
-dave
P.S. as a postscript... how come apps like SQL and
Exchange can write out 60gb backup files in a matter of
minutes but if I try to copy (not move) a 60gb file from
one directory on a drive to another directory on the same
drive it takes 2 hours? What do Exchange/SQL do that
makes disk access so fast and how can we make is that fast
for normal file copy operations?Dave,
From what I understand, it goes a little something like this...
When you restore, SQL Server has to initialize all pages in the database.
Then, it has to put the data back on _exactly_ those same pages.
If it did not do this, then the internal list linking strategy would be
hosed.
You might consider not having the backup device on the same disk system
during the restore, or you will be stepping all over yourself with
read/write ops, and waste a lot of time thrashing.
James Hokes
"Dave Rose" <anonymous@.discussions.microsoft.com> wrote in message
news:92a801c3eab8$3eb2c090$a301280a@.phx.gbl...
> Okay, I understand that when I'm restoring a database that
> it needs some time to "create" the empty database before
> it starts populating the data, but this is ridiculous...
> I have an approximately 60gb database (with separate log
> and data drives) that I backup up (to the data drive) in
> 22 minutes.
> I'm now trying to restore that database and it has so far
> literally taken 4 hours and the restore progress bar (in
> EM) hasn't even started moving yet. Checking current
> activity I can see that the restore process is "Waiting
> For ASYNC_IO_COMPLETION".
> Now I know that my disk configuration is okay because SQL
> was able to read the enter 60gb database and write it out
> to a backup file in 22 minutes. Why in the world would
> it take so long to restore?
> My experience has been that this initial part of the
> restore (where my guess is that it is creating the "blank"
> database) takes forever and then once the progress bars
> start it goes really quick.
> What is making this take so long? How can I back
> something up and then try to restore and it literally take
> 10 or 20 times as long?
> Any help would be greatly appreciated!
> thanks!
> -dave
> P.S. as a postscript... how come apps like SQL and
> Exchange can write out 60gb backup files in a matter of
> minutes but if I try to copy (not move) a 60gb file from
> one directory on a drive to another directory on the same
> drive it takes 2 hours? What do Exchange/SQL do that
> makes disk access so fast and how can we make is that fast
> for normal file copy operations?

Restore published database

I need to restore published database from backup. Do I need to restore
distribution database ftom the same point in time or I have to rebuild
replication? Any sugestins are welcome.
Thanks
dk
For a merge replication you should not have to worry about it as the
subscribers will backfill the missing information.
For a transactional publication, the log reader will complain, and your best
bet is to drop your subscriptions and then resubscribe and send the snapshot
over again.
If you don't want to do this, you could resubscribe using a no sync
subscription and then do a validation to determine how out of sync you are
and cobble together a consistent database.
You could also do a sp_replrestart to get your publication going again, but
I would advise you to only do this under the guidence of a Microsoft PSS
engineer, or engineeress.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"dk" <dk@.discussions.microsoft.com> wrote in message
news:66158E1C-85B5-431E-8899-25DB6BDD3D04@.microsoft.com...
> I need to restore published database from backup. Do I need to restore
> distribution database ftom the same point in time or I have to rebuild
> replication? Any sugestins are welcome.
> Thanks
> dk
|||Thanks very much Hilary. I do have a transactional replication running, and
no initial snapshot is required, since the subscriber already has replicated
schema. I am only concirned about log reader, if it would be able to figure
out where to continue. So in your opinion, restore of the distribution
database wouldn't help eather.
"Hilary Cotter" wrote:

> For a merge replication you should not have to worry about it as the
> subscribers will backfill the missing information.
> For a transactional publication, the log reader will complain, and your best
> bet is to drop your subscriptions and then resubscribe and send the snapshot
> over again.
> If you don't want to do this, you could resubscribe using a no sync
> subscription and then do a validation to determine how out of sync you are
> and cobble together a consistent database.
> You could also do a sp_replrestart to get your publication going again, but
> I would advise you to only do this under the guidence of a Microsoft PSS
> engineer, or engineeress.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "dk" <dk@.discussions.microsoft.com> wrote in message
> news:66158E1C-85B5-431E-8899-25DB6BDD3D04@.microsoft.com...
>
>

Restore Problems

From time to time I receive the following error while
restoring:
"The log in this backup set terminates at LSNxxx, which
is too early to apply to the database. A more recent log
backup that includes LSNxxx can be restored."
I am not backing up any logs. This is my backup command:
BACKUP DATABASE [Order] TO DISK = N'C:\Program
Files\Microsoft SQL Server\MSSQL\BACKUP\Orderback' WITH
INIT , NOUNLOAD , NAME = N'Order backup', NOSKIP ,
STATS = 10, NOFORMAT
This is my restore:
restore database [order]
filegroup = 'primary', filegroup = 'ORDER1', filegroup
= 'ORDER2'
from disk='c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Orderback'
with file=1,norecovery,partial,replace,
move 'order_data' to 'c:\program files\microsoft sql
server\mssql\data\order_data.mdf',
move 'order_data1' to 'c:\program files\microsoft sql
server\mssql\data\order_data1.ndf',
move 'order_data2' to 'c:\program files\microsoft sql
server\mssql\data\order_data2.ndf',
move 'order_log' to 'c:\program files\microsoft sql
server\mssql\data\order_log.ldf'
go
restore log [order]
from disk='c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Orderback'
with file=1,recovery
goHi Paul.
I don't think you need that final restore log command - the full database
restore will fully restore the database and log entries in that file so I'm
wondering what you're expecting to get from that restore log command?
Regards,
Greg Linwood
SQL Server MVP
"Paul Offerle" <pofferle@.fmcconnell.com> wrote in message
news:664a01c4016a$6821c020$a001280a@.phx.gbl...
> From time to time I receive the following error while
> restoring:
> "The log in this backup set terminates at LSNxxx, which
> is too early to apply to the database. A more recent log
> backup that includes LSNxxx can be restored."
> I am not backing up any logs. This is my backup command:
> BACKUP DATABASE [Order] TO DISK = N'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\Orderback' WITH
> INIT , NOUNLOAD , NAME = N'Order backup', NOSKIP ,
> STATS = 10, NOFORMAT
> This is my restore:
> restore database [order]
> filegroup = 'primary', filegroup = 'ORDER1', filegroup
> = 'ORDER2'
> from disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Orderback'
> with file=1,norecovery,partial,replace,
> move 'order_data' to 'c:\program files\microsoft sql
> server\mssql\data\order_data.mdf',
> move 'order_data1' to 'c:\program files\microsoft sql
> server\mssql\data\order_data1.ndf',
> move 'order_data2' to 'c:\program files\microsoft sql
> server\mssql\data\order_data2.ndf',
> move 'order_log' to 'c:\program files\microsoft sql
> server\mssql\data\order_log.ldf'
> go
> restore log [order]
> from disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Orderback'
> with file=1,recovery
> go|||When I omit the restore log, I am unable to access the
database. Enterprise Manager displays "(loading)" next to
the database name.|||Paul
Although Greg said the restore database will "will fully restore the
database and log entries in that file", he meant it COULD do the full
restore, but only if you specify the keyword RECOVERY. The default is not to
run recovery, with the assumption that you will first be applying more logs.
Recovery needs to be run with the last thing you're restoring.
Use the recovery option on your RESTORE DATABASE command and you won't need
to restore any logs.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:6cca01c401fd$90a802a0$a301280a@.phx.gbl...
> When I omit the restore log, I am unable to access the
> database. Enterprise Manager displays "(loading)" next to
> the database name.|||Try dropping the database before doing the restore.
I have found that doing filegroup restores using the REPLACE option creates
these type of errors.
Good Luck.
**********************************
"Paul Offerle" <pofferle@.fmcconnell.com> wrote in message
news:664a01c4016a$6821c020$a001280a@.phx.gbl...
> From time to time I receive the following error while
> restoring:
> "The log in this backup set terminates at LSNxxx, which
> is too early to apply to the database. A more recent log
> backup that includes LSNxxx can be restored."
> I am not backing up any logs. This is my backup command:
> BACKUP DATABASE [Order] TO DISK = N'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\Orderback' WITH
> INIT , NOUNLOAD , NAME = N'Order backup', NOSKIP ,
> STATS = 10, NOFORMAT
> This is my restore:
> restore database [order]
> filegroup = 'primary', filegroup = 'ORDER1', filegroup
> = 'ORDER2'
> from disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Orderback'
> with file=1,norecovery,partial,replace,
> move 'order_data' to 'c:\program files\microsoft sql
> server\mssql\data\order_data.mdf',
> move 'order_data1' to 'c:\program files\microsoft sql
> server\mssql\data\order_data1.ndf',
> move 'order_data2' to 'c:\program files\microsoft sql
> server\mssql\data\order_data2.ndf',
> move 'order_log' to 'c:\program files\microsoft sql
> server\mssql\data\order_log.ldf'
> go
> restore log [order]
> from disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Orderback'
> with file=1,recovery
> go|||ooops - I missed that the restore db didn't have recovery option..
Regards,
Greg Linwood
SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%238XCQEgAEHA.1420@.TK2MSFTNGP11.phx.gbl...
> Paul
> Although Greg said the restore database will "will fully restore the
> database and log entries in that file", he meant it COULD do the full
> restore, but only if you specify the keyword RECOVERY. The default is not
to
> run recovery, with the assumption that you will first be applying more
logs.
> Recovery needs to be run with the last thing you're restoring.
> Use the recovery option on your RESTORE DATABASE command and you won't
need
> to restore any logs.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:6cca01c401fd$90a802a0$a301280a@.phx.gbl...
>|||Thank you very much.

Restore Problems

From time to time I receive the following error while
restoring:
"The log in this backup set terminates at LSNxxx, which
is too early to apply to the database. A more recent log
backup that includes LSNxxx can be restored."
I am not backing up any logs. This is my backup command:
BACKUP DATABASE [Order] TO DISK = N'C:\Program
Files\Microsoft SQL Server\MSSQL\BACKUP\Orderback' WITH
INIT , NOUNLOAD , NAME = N'Order backup', NOSKIP ,
STATS = 10, NOFORMAT
This is my restore:
restore database [order]
filegroup = 'primary', filegroup = 'ORDER1', filegroup
= 'ORDER2'
from disk='c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Orderback'
with file=1,norecovery,partial,replace,
move 'order_data' to 'c:\program files\microsoft sql
server\mssql\data\order_data.mdf',
move 'order_data1' to 'c:\program files\microsoft sql
server\mssql\data\order_data1.ndf',
move 'order_data2' to 'c:\program files\microsoft sql
server\mssql\data\order_data2.ndf',
move 'order_log' to 'c:\program files\microsoft sql
server\mssql\data\order_log.ldf'
go
restore log [order]
from disk='c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Orderback'
with file=1,recovery
goHi Paul.
I don't think you need that final restore log command - the full database
restore will fully restore the database and log entries in that file so I'm
wondering what you're expecting to get from that restore log command?
Regards,
Greg Linwood
SQL Server MVP
"Paul Offerle" <pofferle@.fmcconnell.com> wrote in message
news:664a01c4016a$6821c020$a001280a@.phx.gbl...
> From time to time I receive the following error while
> restoring:
> "The log in this backup set terminates at LSNxxx, which
> is too early to apply to the database. A more recent log
> backup that includes LSNxxx can be restored."
> I am not backing up any logs. This is my backup command:
> BACKUP DATABASE [Order] TO DISK = N'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\Orderback' WITH
> INIT , NOUNLOAD , NAME = N'Order backup', NOSKIP ,
> STATS = 10, NOFORMAT
> This is my restore:
> restore database [order]
> filegroup = 'primary', filegroup = 'ORDER1', filegroup
> = 'ORDER2'
> from disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Orderback'
> with file=1,norecovery,partial,replace,
> move 'order_data' to 'c:\program files\microsoft sql
> server\mssql\data\order_data.mdf',
> move 'order_data1' to 'c:\program files\microsoft sql
> server\mssql\data\order_data1.ndf',
> move 'order_data2' to 'c:\program files\microsoft sql
> server\mssql\data\order_data2.ndf',
> move 'order_log' to 'c:\program files\microsoft sql
> server\mssql\data\order_log.ldf'
> go
> restore log [order]
> from disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Orderback'
> with file=1,recovery
> go|||When I omit the restore log, I am unable to access the
database. Enterprise Manager displays "(loading)" next to
the database name.|||Paul
Although Greg said the restore database will "will fully restore the
database and log entries in that file", he meant it COULD do the full
restore, but only if you specify the keyword RECOVERY. The default is not to
run recovery, with the assumption that you will first be applying more logs.
Recovery needs to be run with the last thing you're restoring.
Use the recovery option on your RESTORE DATABASE command and you won't need
to restore any logs.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:6cca01c401fd$90a802a0$a301280a@.phx.gbl...
> When I omit the restore log, I am unable to access the
> database. Enterprise Manager displays "(loading)" next to
> the database name.|||Try dropping the database before doing the restore.
I have found that doing filegroup restores using the REPLACE option creates
these type of errors.
Good Luck.
**********************************
"Paul Offerle" <pofferle@.fmcconnell.com> wrote in message
news:664a01c4016a$6821c020$a001280a@.phx.gbl...
> From time to time I receive the following error while
> restoring:
> "The log in this backup set terminates at LSNxxx, which
> is too early to apply to the database. A more recent log
> backup that includes LSNxxx can be restored."
> I am not backing up any logs. This is my backup command:
> BACKUP DATABASE [Order] TO DISK = N'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\Orderback' WITH
> INIT , NOUNLOAD , NAME = N'Order backup', NOSKIP ,
> STATS = 10, NOFORMAT
> This is my restore:
> restore database [order]
> filegroup = 'primary', filegroup = 'ORDER1', filegroup
> = 'ORDER2'
> from disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Orderback'
> with file=1,norecovery,partial,replace,
> move 'order_data' to 'c:\program files\microsoft sql
> server\mssql\data\order_data.mdf',
> move 'order_data1' to 'c:\program files\microsoft sql
> server\mssql\data\order_data1.ndf',
> move 'order_data2' to 'c:\program files\microsoft sql
> server\mssql\data\order_data2.ndf',
> move 'order_log' to 'c:\program files\microsoft sql
> server\mssql\data\order_log.ldf'
> go
> restore log [order]
> from disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Orderback'
> with file=1,recovery
> go|||ooops - I missed that the restore db didn't have recovery option..
Regards,
Greg Linwood
SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%238XCQEgAEHA.1420@.TK2MSFTNGP11.phx.gbl...
> Paul
> Although Greg said the restore database will "will fully restore the
> database and log entries in that file", he meant it COULD do the full
> restore, but only if you specify the keyword RECOVERY. The default is not
to
> run recovery, with the assumption that you will first be applying more
logs.
> Recovery needs to be run with the last thing you're restoring.
> Use the recovery option on your RESTORE DATABASE command and you won't
need
> to restore any logs.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:6cca01c401fd$90a802a0$a301280a@.phx.gbl...
> > When I omit the restore log, I am unable to access the
> > database. Enterprise Manager displays "(loading)" next to
> > the database name.
>

Restore Problem from an unkown .BAK file

Hi
I am trying to restore an old database may be done in SQL 6.5 into 7.0 or 2000. I dont know what is the database name given at that time or the backup device name used earlier. I just have this .BAK file through which I must restore the database. Is there anyway in which I can restore the database
If anybody can help I would greatful
Thanks
Srinivas RaoYou cannot restore 6.5 backups into SQL Server 7.0 or 2000. You should first
restore this into 6.5 and then use BCP or DTS to move the data from 6.5 to
7.0/2000.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Srinivas Rao" <asraogoud@.yahoo.com> wrote in message
news:E9BE3713-3E91-4419-8167-EC2F244E0CF8@.microsoft.com...
Hi,
I am trying to restore an old database may be done in SQL 6.5 into 7.0 or
2000. I dont know what is the database name given at that time or the backup
device name used earlier. I just have this .BAK file through which I must
restore the database. Is there anyway in which I can restore the database?
If anybody can help I would greatful.
Thanks.
Srinivas Rao|||Hi Srinivas,
You cannot restore the database backups /files of SQL
Server 6.5 to SQL Server 7.0/2000. I would suggest you to
install SQL Server 6.5 ( if you dont have it)on a test
server, restore the .BAK file, then upgrade 6.5 to SQL
Server 7.0/2000 , take a backup and restore on the real
server.
Also You cannot restore a SQL Server 2000 database backup
to a SQL Server 7.0 server.
The following KB's are for ur reference.
http://www.support.microsoft.com/?id=314546 Moving DB's
between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL
Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=246133 How To
Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins
& SIDs after a
Restore
http://www.support.microsoft.com/?id=168001 User Logon
and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve
Permission
Issues When a Database Is Moved Between SQL Servers
Regards
Thirumal Reddy M.
System Administrator
www.sstil.com
>--Original Message--
>Hi,
>I am trying to restore an old database may be done in SQL
6.5 into 7.0 or 2000. I dont know what is the database
name given at that time or the backup device name used
earlier. I just have this .BAK file through which I must
restore the database. Is there anyway in which I can
restore the database?
>If anybody can help I would greatful.
>
>Thanks.
>Srinivas Rao
>.
>|||Hi,
SQL 6.5 backup file can not be restored to SQL 7 or SQL 2000. You have to
use Upgrade wizard to upgrade the database from SQL 6.5 to SQL 7/2000. For
that you should have SQL 6.5 database up.
If it is SQL 7/2000 then use the below statement in query analyzer to get
file info,
Restore filelistonly from disk='driveletter\folder\dbname.bak'
After that you can use any database name to restore the backup file. Execute
the below statement to restore the database.
Restore database dbname from disk='driveletter\folder\dbname.bak' with
stas=10 ,
move 'logical_datafilename' to 'physical_dataname',
move 'logical_logfilename' to 'physical_logname'
Thanks
Hari
MCDBA
"Srinivas Rao" <asraogoud@.yahoo.com> wrote in message
news:E9BE3713-3E91-4419-8167-EC2F244E0CF8@.microsoft.com...
> Hi,
> I am trying to restore an old database may be done in SQL 6.5 into 7.0 or
2000. I dont know what is the database name given at that time or the backup
device name used earlier. I just have this .BAK file through which I must
restore the database. Is there anyway in which I can restore the database?
> If anybody can help I would greatful.
>
> Thanks.
> Srinivas Rao

Restore Problem from an unkown .BAK file

Hi,
I am trying to restore an old database may be done in SQL 6.5 into 7.0 or 20
00. I dont know what is the database name given at that time or the backup d
evice name used earlier. I just have this .BAK file through which I must res
tore the database. Is there
anyway in which I can restore the database?
If anybody can help I would greatful.
Thanks.
Srinivas RaoYou cannot restore 6.5 backups into SQL Server 7.0 or 2000. You should first
restore this into 6.5 and then use BCP or DTS to move the data from 6.5 to
7.0/2000.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Srinivas Rao" <asraogoud@.yahoo.com> wrote in message
news:E9BE3713-3E91-4419-8167-EC2F244E0CF8@.microsoft.com...
Hi,
I am trying to restore an old database may be done in SQL 6.5 into 7.0 or
2000. I dont know what is the database name given at that time or the backup
device name used earlier. I just have this .BAK file through which I must
restore the database. Is there anyway in which I can restore the database?
If anybody can help I would greatful.
Thanks.
Srinivas Rao|||Hi,
SQL 6.5 backup file can not be restored to SQL 7 or SQL 2000. You have to
use Upgrade wizard to upgrade the database from SQL 6.5 to SQL 7/2000. For
that you should have SQL 6.5 database up.
If it is SQL 7/2000 then use the below statement in query analyzer to get
file info,
Restore filelistonly from disk='driveletter\folder\dbname.bak'
After that you can use any database name to restore the backup file. Execute
the below statement to restore the database.
Restore database dbname from disk='driveletter\folder\dbname.bak' with
stas=10 ,
move 'logical_datafilename' to 'physical_dataname',
move 'logical_logfilename' to 'physical_logname'
Thanks
Hari
MCDBA
"Srinivas Rao" <asraogoud@.yahoo.com> wrote in message
news:E9BE3713-3E91-4419-8167-EC2F244E0CF8@.microsoft.com...
> Hi,
> I am trying to restore an old database may be done in SQL 6.5 into 7.0 or
2000. I dont know what is the database name given at that time or the backup
device name used earlier. I just have this .BAK file through which I must
restore the database. Is there anyway in which I can restore the database?
> If anybody can help I would greatful.
>
> Thanks.
> Srinivas Rao

Restore problem - It takes a long time (loading...)

Hi,
I have set a restore job for a Database using Enterprise Manager. The job
ran quite quickly but the Database now appears on the database list with
(loading) written after it and none of the objects are visible. It has been
sitting there for about n hours. The database file is only about 1,5Mb and
this is the first time is has been restored. Is this kind of time usual? Is
there something else I need to do?
I´m doing this in 2 servers, one for backup and the other one for restore
tks,
edFirst right click on the databases tab and select refresh ( SEM is bad about
not refreshing.)
If the database is still marked as loading, it means that recovery has not
yet run... If you have finished loading all of the logs etc... then to make
the database available run
restore database prod with recovery
from Query Analyzer... The database will now be available...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Edmilson" <troqui@.padtec.com.br> wrote in message
news:#xDTrT1VDHA.2012@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have set a restore job for a Database using Enterprise Manager. The job
> ran quite quickly but the Database now appears on the database list with
> (loading) written after it and none of the objects are visible. It has
been
> sitting there for about n hours. The database file is only about 1,5Mb and
> this is the first time is has been restored. Is this kind of time usual?
Is
> there something else I need to do?
> I´m doing this in 2 servers, one for backup and the other one for restore
> tks,
> ed
>

Restore Problem

Hi,

I am trying to restore a point in time restore of backup. My bkp plan includes full bkp-weekly, daily diff bkp and every 2 hr TLog bkp. I would like to restore the latest bkp as on ''2007-10-18 ' with minimul data loss. Hence i follow the following seqence of restores;

--Restore Full backup
RESTORE DATABASE [ABC] FROM ABC_BackUp WITH FILE = 1, NORECOVERY
GO
--Restore Diff backup
RESTORE DATABASE ABC FROM ABC_BackUp WITH FILE = 3
--Restore TLog backup
RESTORE LOG ABC FROM ABC_BackUp WITH FILE=4, NORECOVERY, STOPAT = '2007-10-18 15:11:00'
GO

The system works well with Full bkp and Diff bkp restore but throws following error while TLog restore;

ERROR:
Server: Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 86001000000103200001, which is too late to apply to the database. An earlier log backup that includes LSN 86001000000081300001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

The following is the backup information of my database:-

Position Bkp Type Bkp Size First LSN Last LSN 1 1 1923229696 86001000000001600002 86001000000005900001 2 2 7284736 85999000000278900001 86001000000081100001 3 5 534528 86001000000081100001 86001000000081300001 4 2 598016 86001000000103200001 86001000000176700001 5 2 729088 86001000000176700001 86001000000276800001 6 2 598016 86001000000276800001 86001000000353100001 7 2 663552 86001000000353100001 86001000000432900001

Pls. guide why i get this error. I don't do any shrinking between Full bkp and TLog bkps. Thanks in advance.

Please mention the timings for Full, Differential and Transaction log backups from the above sequence, it seems the error is by default as the LSN is mismatched.|||

Full backp : every sunday 7.00 am

Diff backup: daily 11.00 pm

trn backup: every 2 hrs mon - sat

|||

hello,

i just wanted to say that i have the same promblem when trying to restore poin-in-time on a different server... maybe this helps.

i can do point-in-time restores on the same server.

i believe the log sequences get mixed up on other servers.

is there a workaround?

thank you

Hugo

|||As explained it refers on the LSN matchup, also mention the restore sequence as it seems the backup database & log are not matched up when restored.|||

i was wrong... i was backing up the log to two different files... sorry. everything is working now.