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 ***
Showing posts with label restore. Show all posts
Showing posts with label restore. Show all posts
Friday, March 30, 2012
Restored Full with NORECOVERY and db reads dbName (Restoring...)
Can anyone help me with this issue? I restored a full backup with NORECOVERY because I need to restore the differentials as well but I have not been able to access the db. No matter what I seem to try i receive the following error:
System.Data.SqlClient.SqlError: This differential backup cannot be restored because the database has not been restored to the correct earlier state. (Microsoft.SqlServer.Smo)
Does anyone know what I need to do?
Thank you in advance.
Julio
Yes I do I don't know what but the Restore process is not working like 2000 so you still need to run restore with recovery code before your restore will complete. Here was my situation I got a database from someone created on E drive SQL Server refused to restore it without dump devices in the Master. I did that and end up with your problem, so I started again and added restore with recovery code making about four sets of code and it was restored. The link below are some of the code you need, make sure you have the .bak delete the restoring and start fresh with the code in the link below. Hope this helps.
http://wvmitchell.blogspot.com/search?q=backup+restore
|||do you have anyother full backup other than what you restored with norecovery option.........may be you might have restored another set of full backup rather than the one which you took b4 differential backup was taken..........this differential wud only have the changes which you made after the most recent full backup was taken.........so restore the last full backup taken before the differential backup was taken....may be that mite be the problem........Restored Full with NORECOVERY and db reads dbName (Restoring...)
Can anyone help me with this issue? I restored a full backup with NORECOVERY because I need to restore the differentials as well but I have not been able to access the db. No matter what I seem to try i receive the following error:
System.Data.SqlClient.SqlError: This differential backup cannot be restored because the database has not been restored to the correct earlier state. (Microsoft.SqlServer.Smo)
Does anyone know what I need to do?
Thank you in advance.
Julio
Yes I do I don't know what but the Restore process is not working like 2000 so you still need to run restore with recovery code before your restore will complete. Here was my situation I got a database from someone created on E drive SQL Server refused to restore it without dump devices in the Master. I did that and end up with your problem, so I started again and added restore with recovery code making about four sets of code and it was restored. The link below are some of the code you need, make sure you have the .bak delete the restoring and start fresh with the code in the link below. Hope this helps.
http://wvmitchell.blogspot.com/search?q=backup+restore
|||do you have anyother full backup other than what you restored with norecovery option.........may be you might have restored another set of full backup rather than the one which you took b4 differential backup was taken..........this differential wud only have the changes which you made after the most recent full backup was taken.........so restore the last full backup taken before the differential backup was taken....may be that mite be the problem........Restored database still "Loading" after two days
On Monday, 31 July, I attempted to restore a backup of a Microsoft CRM 1.2
database (MSCRM) with a different name.
The database icon is grey and in parens at the end of the name is (Loading).
I have a feeling that this is not normal. However, I cannot do anything
because it is "Loading". If I try to delete it, take it off line, whatever,
I get the message:
"Error 3724: Cannot drop the database 'databasename' because it is being
used for replication."
It does not show up in the replication folder so am not sure what is
actually going on.
I have resolved the issue that required the backup data, so do not need the
database even if it was functional. Does anyone know how I can delete it?
Probably not doing any harm but it bugs me.
Thanks,
JimYou might have done the restore using the NORECOVERY option. Try:
RESTORE DATABASE dbname WITH RECOVERY
That should take the database out of "loading" state. Then you should be able to Google on how to
drop the replication stuff from the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CrystalJim" <CrystalJim@.discussions.microsoft.com> wrote in message
news:82935894-2C5B-40B6-B3D9-7F4DCDA202E9@.microsoft.com...
> On Monday, 31 July, I attempted to restore a backup of a Microsoft CRM 1.2
> database (MSCRM) with a different name.
> The database icon is grey and in parens at the end of the name is (Loading).
> I have a feeling that this is not normal. However, I cannot do anything
> because it is "Loading". If I try to delete it, take it off line, whatever,
> I get the message:
> "Error 3724: Cannot drop the database 'databasename' because it is being
> used for replication."
> It does not show up in the replication folder so am not sure what is
> actually going on.
> I have resolved the issue that required the backup data, so do not need the
> database even if it was functional. Does anyone know how I can delete it?
> Probably not doing any harm but it bugs me.
> Thanks,
> Jim
>|||WOW! That did it.
Have to admit I was skepticle.
Thanks so much!
Jim
"Tibor Karaszi" wrote:
> You might have done the restore using the NORECOVERY option. Try:
> RESTORE DATABASE dbname WITH RECOVERY
> That should take the database out of "loading" state. Then you should be able to Google on how to
> drop the replication stuff from the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CrystalJim" <CrystalJim@.discussions.microsoft.com> wrote in message
> news:82935894-2C5B-40B6-B3D9-7F4DCDA202E9@.microsoft.com...
> > On Monday, 31 July, I attempted to restore a backup of a Microsoft CRM 1.2
> > database (MSCRM) with a different name.
> >
> > The database icon is grey and in parens at the end of the name is (Loading).
> >
> > I have a feeling that this is not normal. However, I cannot do anything
> > because it is "Loading". If I try to delete it, take it off line, whatever,
> > I get the message:
> >
> > "Error 3724: Cannot drop the database 'databasename' because it is being
> > used for replication."
> >
> > It does not show up in the replication folder so am not sure what is
> > actually going on.
> >
> > I have resolved the issue that required the backup data, so do not need the
> > database even if it was functional. Does anyone know how I can delete it?
> >
> > Probably not doing any harm but it bugs me.
> >
> > Thanks,
> >
> > Jim
> >
>
database (MSCRM) with a different name.
The database icon is grey and in parens at the end of the name is (Loading).
I have a feeling that this is not normal. However, I cannot do anything
because it is "Loading". If I try to delete it, take it off line, whatever,
I get the message:
"Error 3724: Cannot drop the database 'databasename' because it is being
used for replication."
It does not show up in the replication folder so am not sure what is
actually going on.
I have resolved the issue that required the backup data, so do not need the
database even if it was functional. Does anyone know how I can delete it?
Probably not doing any harm but it bugs me.
Thanks,
JimYou might have done the restore using the NORECOVERY option. Try:
RESTORE DATABASE dbname WITH RECOVERY
That should take the database out of "loading" state. Then you should be able to Google on how to
drop the replication stuff from the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CrystalJim" <CrystalJim@.discussions.microsoft.com> wrote in message
news:82935894-2C5B-40B6-B3D9-7F4DCDA202E9@.microsoft.com...
> On Monday, 31 July, I attempted to restore a backup of a Microsoft CRM 1.2
> database (MSCRM) with a different name.
> The database icon is grey and in parens at the end of the name is (Loading).
> I have a feeling that this is not normal. However, I cannot do anything
> because it is "Loading". If I try to delete it, take it off line, whatever,
> I get the message:
> "Error 3724: Cannot drop the database 'databasename' because it is being
> used for replication."
> It does not show up in the replication folder so am not sure what is
> actually going on.
> I have resolved the issue that required the backup data, so do not need the
> database even if it was functional. Does anyone know how I can delete it?
> Probably not doing any harm but it bugs me.
> Thanks,
> Jim
>|||WOW! That did it.
Have to admit I was skepticle.
Thanks so much!
Jim
"Tibor Karaszi" wrote:
> You might have done the restore using the NORECOVERY option. Try:
> RESTORE DATABASE dbname WITH RECOVERY
> That should take the database out of "loading" state. Then you should be able to Google on how to
> drop the replication stuff from the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CrystalJim" <CrystalJim@.discussions.microsoft.com> wrote in message
> news:82935894-2C5B-40B6-B3D9-7F4DCDA202E9@.microsoft.com...
> > On Monday, 31 July, I attempted to restore a backup of a Microsoft CRM 1.2
> > database (MSCRM) with a different name.
> >
> > The database icon is grey and in parens at the end of the name is (Loading).
> >
> > I have a feeling that this is not normal. However, I cannot do anything
> > because it is "Loading". If I try to delete it, take it off line, whatever,
> > I get the message:
> >
> > "Error 3724: Cannot drop the database 'databasename' because it is being
> > used for replication."
> >
> > It does not show up in the replication folder so am not sure what is
> > actually going on.
> >
> > I have resolved the issue that required the backup data, so do not need the
> > database even if it was functional. Does anyone know how I can delete it?
> >
> > Probably not doing any harm but it bugs me.
> >
> > Thanks,
> >
> > Jim
> >
>
Restored database still "Loading" after two days
On Monday, 31 July, I attempted to restore a backup of a Microsoft CRM 1.2
database (MSCRM) with a different name.
The database icon is grey and in parens at the end of the name is (Loading).
I have a feeling that this is not normal. However, I cannot do anything
because it is "Loading". If I try to delete it, take it off line, whatever,
I get the message:
"Error 3724: Cannot drop the database 'databasename' because it is being
used for replication."
It does not show up in the replication folder so am not sure what is
actually going on.
I have resolved the issue that required the backup data, so do not need the
database even if it was functional. Does anyone know how I can delete it?
Probably not doing any harm but it bugs me.
Thanks,
JimYou might have done the restore using the NORECOVERY option. Try:
RESTORE DATABASE dbname WITH RECOVERY
That should take the database out of "loading" state. Then you should be abl
e to Google on how to
drop the replication stuff from the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CrystalJim" <CrystalJim@.discussions.microsoft.com> wrote in message
news:82935894-2C5B-40B6-B3D9-7F4DCDA202E9@.microsoft.com...
> On Monday, 31 July, I attempted to restore a backup of a Microsoft CRM 1.2
> database (MSCRM) with a different name.
> The database icon is grey and in parens at the end of the name is (Loading
).
> I have a feeling that this is not normal. However, I cannot do anything
> because it is "Loading". If I try to delete it, take it off line, whateve
r,
> I get the message:
> "Error 3724: Cannot drop the database 'databasename' because it is being
> used for replication."
> It does not show up in the replication folder so am not sure what is
> actually going on.
> I have resolved the issue that required the backup data, so do not need th
e
> database even if it was functional. Does anyone know how I can delete it?
> Probably not doing any harm but it bugs me.
> Thanks,
> Jim
>|||WOW! That did it.
Have to admit I was skepticle.
Thanks so much!
Jim
"Tibor Karaszi" wrote:
> You might have done the restore using the NORECOVERY option. Try:
> RESTORE DATABASE dbname WITH RECOVERY
> That should take the database out of "loading" state. Then you should be a
ble to Google on how to
> drop the replication stuff from the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CrystalJim" <CrystalJim@.discussions.microsoft.com> wrote in message
> news:82935894-2C5B-40B6-B3D9-7F4DCDA202E9@.microsoft.com...
>
database (MSCRM) with a different name.
The database icon is grey and in parens at the end of the name is (Loading).
I have a feeling that this is not normal. However, I cannot do anything
because it is "Loading". If I try to delete it, take it off line, whatever,
I get the message:
"Error 3724: Cannot drop the database 'databasename' because it is being
used for replication."
It does not show up in the replication folder so am not sure what is
actually going on.
I have resolved the issue that required the backup data, so do not need the
database even if it was functional. Does anyone know how I can delete it?
Probably not doing any harm but it bugs me.
Thanks,
JimYou might have done the restore using the NORECOVERY option. Try:
RESTORE DATABASE dbname WITH RECOVERY
That should take the database out of "loading" state. Then you should be abl
e to Google on how to
drop the replication stuff from the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CrystalJim" <CrystalJim@.discussions.microsoft.com> wrote in message
news:82935894-2C5B-40B6-B3D9-7F4DCDA202E9@.microsoft.com...
> On Monday, 31 July, I attempted to restore a backup of a Microsoft CRM 1.2
> database (MSCRM) with a different name.
> The database icon is grey and in parens at the end of the name is (Loading
).
> I have a feeling that this is not normal. However, I cannot do anything
> because it is "Loading". If I try to delete it, take it off line, whateve
r,
> I get the message:
> "Error 3724: Cannot drop the database 'databasename' because it is being
> used for replication."
> It does not show up in the replication folder so am not sure what is
> actually going on.
> I have resolved the issue that required the backup data, so do not need th
e
> database even if it was functional. Does anyone know how I can delete it?
> Probably not doing any harm but it bugs me.
> Thanks,
> Jim
>|||WOW! That did it.
Have to admit I was skepticle.
Thanks so much!
Jim
"Tibor Karaszi" wrote:
> You might have done the restore using the NORECOVERY option. Try:
> RESTORE DATABASE dbname WITH RECOVERY
> That should take the database out of "loading" state. Then you should be a
ble to Google on how to
> drop the replication stuff from the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CrystalJim" <CrystalJim@.discussions.microsoft.com> wrote in message
> news:82935894-2C5B-40B6-B3D9-7F4DCDA202E9@.microsoft.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.
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:
>
>
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:
>
>
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
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
Restored database in loading mode.
I tried to restore a dump database to a newly created
database.After it prompts the msg that the database has
beem successfully restored, in the Enterprise Manager, the
database (the restored database) is on the loading mode.
Is there any solution for this.
The data is running on SQL 2000(service pack 3A) in Win
Server 2003.
Pls advise.
Thanks.Seems like you forgot to do the Recovery. Check the "Recovering a Database
Without Restoring" topic in Books OnLine
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\ad
minsql.chm::/ad_bkprst_4zc7.htm).
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Ash'" <sukli@.hotmail.com> wrote in message
news:0c1101c38afb$e92d4b70$a401280a@.phx.gbl...
> I tried to restore a dump database to a newly created
> database.After it prompts the msg that the database has
> beem successfully restored, in the Enterprise Manager, the
> database (the restored database) is on the loading mode.
> Is there any solution for this.
> The data is running on SQL 2000(service pack 3A) in Win
> Server 2003.
> Pls advise.
> Thanks.|||In addition to Dejans message when you restore a database it is unavailable
(loading) while you restore the differential and all of the transaction
logs. When you have completed the restore, you run recovery will rolls back
incomplete transactions and makes the database 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's
community of SQL Server professionals.
www.sqlpass.org
"Ash'" <sukli@.hotmail.com> wrote in message
news:0c1101c38afb$e92d4b70$a401280a@.phx.gbl...
> I tried to restore a dump database to a newly created
> database.After it prompts the msg that the database has
> beem successfully restored, in the Enterprise Manager, the
> database (the restored database) is on the loading mode.
> Is there any solution for this.
> The data is running on SQL 2000(service pack 3A) in Win
> Server 2003.
> Pls advise.
> Thanks.
database.After it prompts the msg that the database has
beem successfully restored, in the Enterprise Manager, the
database (the restored database) is on the loading mode.
Is there any solution for this.
The data is running on SQL 2000(service pack 3A) in Win
Server 2003.
Pls advise.
Thanks.Seems like you forgot to do the Recovery. Check the "Recovering a Database
Without Restoring" topic in Books OnLine
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\ad
minsql.chm::/ad_bkprst_4zc7.htm).
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Ash'" <sukli@.hotmail.com> wrote in message
news:0c1101c38afb$e92d4b70$a401280a@.phx.gbl...
> I tried to restore a dump database to a newly created
> database.After it prompts the msg that the database has
> beem successfully restored, in the Enterprise Manager, the
> database (the restored database) is on the loading mode.
> Is there any solution for this.
> The data is running on SQL 2000(service pack 3A) in Win
> Server 2003.
> Pls advise.
> Thanks.|||In addition to Dejans message when you restore a database it is unavailable
(loading) while you restore the differential and all of the transaction
logs. When you have completed the restore, you run recovery will rolls back
incomplete transactions and makes the database 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's
community of SQL Server professionals.
www.sqlpass.org
"Ash'" <sukli@.hotmail.com> wrote in message
news:0c1101c38afb$e92d4b70$a401280a@.phx.gbl...
> I tried to restore a dump database to a newly created
> database.After it prompts the msg that the database has
> beem successfully restored, in the Enterprise Manager, the
> database (the restored database) is on the loading mode.
> Is there any solution for this.
> The data is running on SQL 2000(service pack 3A) in Win
> Server 2003.
> Pls advise.
> Thanks.
Restore?
I am trying to restore a .bak to a test server in order to test the integrity
of my production DB backups but the test server rejects the .bak file because
it is a file used by the production server. A full and transactional backups
are used in the production DB. Any insights on how to perform a test restore
is highly appreciated.
What error message are you getting ?
Razvan
morphius wrote:
> I am trying to restore a .bak to a test server in order to test the integrity
> of my production DB backups but the test server rejects the .bak file because
> it is a file used by the production server. A full and transactional backups
> are used in the production DB. Any insights on how to perform a test restore
> is highly appreciated.
|||Please post the exact error message.
Is your test machine a separate instance on the same hardware, or a
completely different server?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:0AA8DE03-924A-41D8-A706-87E9F047B945@.microsoft.com...
>I am trying to restore a .bak to a test server in order to test the
>integrity
> of my production DB backups but the test server rejects the .bak file
> because
> it is a file used by the production server. A full and transactional
> backups
> are used in the production DB. Any insights on how to perform a test
> restore
> is highly appreciated.
>
|||Razvan
I think the OP is asking how to restore .bak file and are not getting any
errors :-)
morphius
RESTORE DATABASE dbname FROM DISK='C:\db.bak' WITH FILE = 1,NORECOVERY
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 1, NORECOVERY
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 2, NORECOVERY
.....
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = n, RECOVERY
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1165930086.612341.260330@.16g2000cwy.googlegro ups.com...
> What error message are you getting ?
> Razvan
> morphius wrote:
>
|||morphius wrote:
> I am trying to restore a .bak to a test server in order to test the integrity
> of my production DB backups but the test server rejects the .bak file because
> it is a file used by the production server. A full and transactional backups
> are used in the production DB. Any insights on how to perform a test restore
> is highly appreciated.
>
My guess is you're restoring over an existing database, and the message
you're seeing is telling you that there are existing files, in which
case you need to use the WITH MOVE option on the RESTORE command.
Posting the full error message will tell us for sure.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||It is a totally different server. Maybe I am doing something wrong. How would
you specifically restore a copy of the production db to the test server?
Thanks...
"Kevin3NF" wrote:
> Please post the exact error message.
> Is your test machine a separate instance on the same hardware, or a
> completely different server?
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:0AA8DE03-924A-41D8-A706-87E9F047B945@.microsoft.com...
>
>
|||Hello,
1. Copy the Full database backup file to test server
2. Copy the Transaction log backup files to test servr
3. Open Query Analyzer (SQL2000) or SSMS (SQL 2005) in test server
4. Use RESTORE DATABASE Command with NORECOVERY and MOVE option to restore
the Full database
5. Resttore all the transaction log backups using RESTORE LOG WITH
NORECOVERY until last file
6. Restore the Last Log backup using RESTORE LOG WITH Recovery option. THsi
will make the database online
See the commands usage in books online...
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:63F7B1D4-F3F2-43EB-81DD-065B7D6FF35B@.microsoft.com...[vbcol=seagreen]
> It is a totally different server. Maybe I am doing something wrong. How
> would
> you specifically restore a copy of the production db to the test server?
> Thanks...
> "Kevin3NF" wrote:
|||So, basically i need to copy the data.bak files to the local HD of the test
server and execute the backup statement. By log.bak did you mean .trn files?
"Uri Dimant" wrote:
> Razvan
> I think the OP is asking how to restore .bak file and are not getting any
> errors :-)
> morphius
> RESTORE DATABASE dbname FROM DISK='C:\db.bak' WITH FILE = 1,NORECOVERY
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 1, NORECOVERY
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 2, NORECOVERY
> .....
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = n, RECOVERY
>
>
> "Razvan Socol" <rsocol@.gmail.com> wrote in message
> news:1165930086.612341.260330@.16g2000cwy.googlegro ups.com...
>
>
|||Yes, take a look at WITH MOVE option in the BOL as well
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:7C00AC18-E4EB-48EB-8134-8544FDE6FCD1@.microsoft.com...[vbcol=seagreen]
> So, basically i need to copy the data.bak files to the local HD of the
> test
> server and execute the backup statement. By log.bak did you mean .trn
> files?
> "Uri Dimant" wrote:
|||morphius wrote:
> So, basically i need to copy the data.bak files to the local HD of the test
> server and execute the backup statement. By log.bak did you mean .trn files?
>
Technically, NO, you don't HAVE to copy the BAK files to the local HD.
You can restore from a remote share:
RESTORE DATABASE foo FROM DISK = '\\server\sharename'
However, the SQL Server service account must have permission to read
from this network share. This is NOT, repeat, NOT the account that YOU
login to SQL with, this is the account that the SERVICE runs under.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
of my production DB backups but the test server rejects the .bak file because
it is a file used by the production server. A full and transactional backups
are used in the production DB. Any insights on how to perform a test restore
is highly appreciated.
What error message are you getting ?
Razvan
morphius wrote:
> I am trying to restore a .bak to a test server in order to test the integrity
> of my production DB backups but the test server rejects the .bak file because
> it is a file used by the production server. A full and transactional backups
> are used in the production DB. Any insights on how to perform a test restore
> is highly appreciated.
|||Please post the exact error message.
Is your test machine a separate instance on the same hardware, or a
completely different server?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:0AA8DE03-924A-41D8-A706-87E9F047B945@.microsoft.com...
>I am trying to restore a .bak to a test server in order to test the
>integrity
> of my production DB backups but the test server rejects the .bak file
> because
> it is a file used by the production server. A full and transactional
> backups
> are used in the production DB. Any insights on how to perform a test
> restore
> is highly appreciated.
>
|||Razvan
I think the OP is asking how to restore .bak file and are not getting any
errors :-)
morphius
RESTORE DATABASE dbname FROM DISK='C:\db.bak' WITH FILE = 1,NORECOVERY
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 1, NORECOVERY
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 2, NORECOVERY
.....
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = n, RECOVERY
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1165930086.612341.260330@.16g2000cwy.googlegro ups.com...
> What error message are you getting ?
> Razvan
> morphius wrote:
>
|||morphius wrote:
> I am trying to restore a .bak to a test server in order to test the integrity
> of my production DB backups but the test server rejects the .bak file because
> it is a file used by the production server. A full and transactional backups
> are used in the production DB. Any insights on how to perform a test restore
> is highly appreciated.
>
My guess is you're restoring over an existing database, and the message
you're seeing is telling you that there are existing files, in which
case you need to use the WITH MOVE option on the RESTORE command.
Posting the full error message will tell us for sure.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||It is a totally different server. Maybe I am doing something wrong. How would
you specifically restore a copy of the production db to the test server?
Thanks...
"Kevin3NF" wrote:
> Please post the exact error message.
> Is your test machine a separate instance on the same hardware, or a
> completely different server?
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:0AA8DE03-924A-41D8-A706-87E9F047B945@.microsoft.com...
>
>
|||Hello,
1. Copy the Full database backup file to test server
2. Copy the Transaction log backup files to test servr
3. Open Query Analyzer (SQL2000) or SSMS (SQL 2005) in test server
4. Use RESTORE DATABASE Command with NORECOVERY and MOVE option to restore
the Full database
5. Resttore all the transaction log backups using RESTORE LOG WITH
NORECOVERY until last file
6. Restore the Last Log backup using RESTORE LOG WITH Recovery option. THsi
will make the database online
See the commands usage in books online...
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:63F7B1D4-F3F2-43EB-81DD-065B7D6FF35B@.microsoft.com...[vbcol=seagreen]
> It is a totally different server. Maybe I am doing something wrong. How
> would
> you specifically restore a copy of the production db to the test server?
> Thanks...
> "Kevin3NF" wrote:
|||So, basically i need to copy the data.bak files to the local HD of the test
server and execute the backup statement. By log.bak did you mean .trn files?
"Uri Dimant" wrote:
> Razvan
> I think the OP is asking how to restore .bak file and are not getting any
> errors :-)
> morphius
> RESTORE DATABASE dbname FROM DISK='C:\db.bak' WITH FILE = 1,NORECOVERY
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 1, NORECOVERY
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 2, NORECOVERY
> .....
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = n, RECOVERY
>
>
> "Razvan Socol" <rsocol@.gmail.com> wrote in message
> news:1165930086.612341.260330@.16g2000cwy.googlegro ups.com...
>
>
|||Yes, take a look at WITH MOVE option in the BOL as well
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:7C00AC18-E4EB-48EB-8134-8544FDE6FCD1@.microsoft.com...[vbcol=seagreen]
> So, basically i need to copy the data.bak files to the local HD of the
> test
> server and execute the backup statement. By log.bak did you mean .trn
> files?
> "Uri Dimant" wrote:
|||morphius wrote:
> So, basically i need to copy the data.bak files to the local HD of the test
> server and execute the backup statement. By log.bak did you mean .trn files?
>
Technically, NO, you don't HAVE to copy the BAK files to the local HD.
You can restore from a remote share:
RESTORE DATABASE foo FROM DISK = '\\server\sharename'
However, the SQL Server service account must have permission to read
from this network share. This is NOT, repeat, NOT the account that YOU
login to SQL with, this is the account that the SERVICE runs under.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Restore?
I am trying to restore a .bak to a test server in order to test the integrit
y
of my production DB backups but the test server rejects the .bak file becaus
e
it is a file used by the production server. A full and transactional backups
are used in the production DB. Any insights on how to perform a test restore
is highly appreciated.What error message are you getting ?
Razvan
morphius wrote:
> I am trying to restore a .bak to a test server in order to test the integr
ity
> of my production DB backups but the test server rejects the .bak file beca
use
> it is a file used by the production server. A full and transactional backu
ps
> are used in the production DB. Any insights on how to perform a test resto
re
> is highly appreciated.|||Please post the exact error message.
Is your test machine a separate instance on the same hardware, or a
completely different server?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:0AA8DE03-924A-41D8-A706-87E9F047B945@.microsoft.com...
>I am trying to restore a .bak to a test server in order to test the
>integrity
> of my production DB backups but the test server rejects the .bak file
> because
> it is a file used by the production server. A full and transactional
> backups
> are used in the production DB. Any insights on how to perform a test
> restore
> is highly appreciated.
>|||Razvan
I think the OP is asking how to restore .bak file and are not getting any
errors :-)
morphius
RESTORE DATABASE dbname FROM DISK='C:\db.bak' WITH FILE = 1,NORECOVERY
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 1, NORECOVERY
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 2, NORECOVERY
....
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = n, RECOVERY
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1165930086.612341.260330@.16g2000cwy.googlegroups.com...
> What error message are you getting ?
> Razvan
> morphius wrote:
>|||morphius wrote:
> I am trying to restore a .bak to a test server in order to test the integr
ity
> of my production DB backups but the test server rejects the .bak file beca
use
> it is a file used by the production server. A full and transactional backu
ps
> are used in the production DB. Any insights on how to perform a test resto
re
> is highly appreciated.
>
My guess is you're restoring over an existing database, and the message
you're seeing is telling you that there are existing files, in which
case you need to use the WITH MOVE option on the RESTORE command.
Posting the full error message will tell us for sure.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||It is a totally different server. Maybe I am doing something wrong. How woul
d
you specifically restore a copy of the production db to the test server?
Thanks...
"Kevin3NF" wrote:
> Please post the exact error message.
> Is your test machine a separate instance on the same hardware, or a
> completely different server?
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:0AA8DE03-924A-41D8-A706-87E9F047B945@.microsoft.com...
>
>|||Hello,
1. Copy the Full database backup file to test server
2. Copy the Transaction log backup files to test servr
3. Open Query Analyzer (SQL2000) or SSMS (SQL 2005) in test server
4. Use RESTORE DATABASE Command with NORECOVERY and MOVE option to restore
the Full database
5. Resttore all the transaction log backups using RESTORE LOG WITH
NORECOVERY until last file
6. Restore the Last Log backup using RESTORE LOG WITH Recovery option. THsi
will make the database online
See the commands usage in books online...
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:63F7B1D4-F3F2-43EB-81DD-065B7D6FF35B@.microsoft.com...[vbcol=seagreen]
> It is a totally different server. Maybe I am doing something wrong. How
> would
> you specifically restore a copy of the production db to the test server?
> Thanks...
> "Kevin3NF" wrote:
>|||So, basically i need to copy the data.bak files to the local HD of the test
server and execute the backup statement. By log.bak did you mean .trn files?
"Uri Dimant" wrote:
> Razvan
> I think the OP is asking how to restore .bak file and are not getting any
> errors :-)
> morphius
> RESTORE DATABASE dbname FROM DISK='C:\db.bak' WITH FILE = 1,NORECOVERY
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 1, NORECOVERY
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 2, NORECOVERY
> .....
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = n, RECOVERY
>
>
> "Razvan Socol" <rsocol@.gmail.com> wrote in message
> news:1165930086.612341.260330@.16g2000cwy.googlegroups.com...
>
>|||Yes, take a look at WITH MOVE option in the BOL as well
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:7C00AC18-E4EB-48EB-8134-8544FDE6FCD1@.microsoft.com...[vbcol=seagreen]
> So, basically i need to copy the data.bak files to the local HD of the
> test
> server and execute the backup statement. By log.bak did you mean .trn
> files?
> "Uri Dimant" wrote:
>|||morphius wrote:
> So, basically i need to copy the data.bak files to the local HD of the tes
t
> server and execute the backup statement. By log.bak did you mean .trn file
s?
>
Technically, NO, you don't HAVE to copy the BAK files to the local HD.
You can restore from a remote share:
RESTORE DATABASE foo FROM DISK = '\\server\sharename'
However, the SQL Server service account must have permission to read
from this network share. This is NOT, repeat, NOT the account that YOU
login to SQL with, this is the account that the SERVICE runs under.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
y
of my production DB backups but the test server rejects the .bak file becaus
e
it is a file used by the production server. A full and transactional backups
are used in the production DB. Any insights on how to perform a test restore
is highly appreciated.What error message are you getting ?
Razvan
morphius wrote:
> I am trying to restore a .bak to a test server in order to test the integr
ity
> of my production DB backups but the test server rejects the .bak file beca
use
> it is a file used by the production server. A full and transactional backu
ps
> are used in the production DB. Any insights on how to perform a test resto
re
> is highly appreciated.|||Please post the exact error message.
Is your test machine a separate instance on the same hardware, or a
completely different server?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:0AA8DE03-924A-41D8-A706-87E9F047B945@.microsoft.com...
>I am trying to restore a .bak to a test server in order to test the
>integrity
> of my production DB backups but the test server rejects the .bak file
> because
> it is a file used by the production server. A full and transactional
> backups
> are used in the production DB. Any insights on how to perform a test
> restore
> is highly appreciated.
>|||Razvan
I think the OP is asking how to restore .bak file and are not getting any
errors :-)
morphius
RESTORE DATABASE dbname FROM DISK='C:\db.bak' WITH FILE = 1,NORECOVERY
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 1, NORECOVERY
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 2, NORECOVERY
....
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = n, RECOVERY
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1165930086.612341.260330@.16g2000cwy.googlegroups.com...
> What error message are you getting ?
> Razvan
> morphius wrote:
>|||morphius wrote:
> I am trying to restore a .bak to a test server in order to test the integr
ity
> of my production DB backups but the test server rejects the .bak file beca
use
> it is a file used by the production server. A full and transactional backu
ps
> are used in the production DB. Any insights on how to perform a test resto
re
> is highly appreciated.
>
My guess is you're restoring over an existing database, and the message
you're seeing is telling you that there are existing files, in which
case you need to use the WITH MOVE option on the RESTORE command.
Posting the full error message will tell us for sure.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||It is a totally different server. Maybe I am doing something wrong. How woul
d
you specifically restore a copy of the production db to the test server?
Thanks...
"Kevin3NF" wrote:
> Please post the exact error message.
> Is your test machine a separate instance on the same hardware, or a
> completely different server?
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:0AA8DE03-924A-41D8-A706-87E9F047B945@.microsoft.com...
>
>|||Hello,
1. Copy the Full database backup file to test server
2. Copy the Transaction log backup files to test servr
3. Open Query Analyzer (SQL2000) or SSMS (SQL 2005) in test server
4. Use RESTORE DATABASE Command with NORECOVERY and MOVE option to restore
the Full database
5. Resttore all the transaction log backups using RESTORE LOG WITH
NORECOVERY until last file
6. Restore the Last Log backup using RESTORE LOG WITH Recovery option. THsi
will make the database online
See the commands usage in books online...
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:63F7B1D4-F3F2-43EB-81DD-065B7D6FF35B@.microsoft.com...[vbcol=seagreen]
> It is a totally different server. Maybe I am doing something wrong. How
> would
> you specifically restore a copy of the production db to the test server?
> Thanks...
> "Kevin3NF" wrote:
>|||So, basically i need to copy the data.bak files to the local HD of the test
server and execute the backup statement. By log.bak did you mean .trn files?
"Uri Dimant" wrote:
> Razvan
> I think the OP is asking how to restore .bak file and are not getting any
> errors :-)
> morphius
> RESTORE DATABASE dbname FROM DISK='C:\db.bak' WITH FILE = 1,NORECOVERY
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 1, NORECOVERY
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 2, NORECOVERY
> .....
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = n, RECOVERY
>
>
> "Razvan Socol" <rsocol@.gmail.com> wrote in message
> news:1165930086.612341.260330@.16g2000cwy.googlegroups.com...
>
>|||Yes, take a look at WITH MOVE option in the BOL as well
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:7C00AC18-E4EB-48EB-8134-8544FDE6FCD1@.microsoft.com...[vbcol=seagreen]
> So, basically i need to copy the data.bak files to the local HD of the
> test
> server and execute the backup statement. By log.bak did you mean .trn
> files?
> "Uri Dimant" wrote:
>|||morphius wrote:
> So, basically i need to copy the data.bak files to the local HD of the tes
t
> server and execute the backup statement. By log.bak did you mean .trn file
s?
>
Technically, NO, you don't HAVE to copy the BAK files to the local HD.
You can restore from a remote share:
RESTORE DATABASE foo FROM DISK = '\\server\sharename'
However, the SQL Server service account must have permission to read
from this network share. This is NOT, repeat, NOT the account that YOU
login to SQL with, this is the account that the SERVICE runs under.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
restore: EXCEPTION_ACCESS_VIOLATION c0000005 (17052)
Because of a corrupt mainboard we tried to restore our SQL Server 7
databases to a new computer but get the same error for all backups we
try to restore:
---
ODBC: Nachr.-Nr. 0, Schweregrad 19, Status 1
SqlDumpExceptionHandler: Prozess 21 erzeugte eine schwere
Ausnahmebedingung c0000005 EXCEPTION_ACCESS_VIOLATION.Sql Server
beendet diesen Prozess.
---
The Windows event log shows an event ID 17052. What can be wrong?I check out www.eventid.net for this issue and found something about it and
wanted to share with you. Maybe you can find something useful.
From www.EventId.net
Adrian Grigorof (Last update 1/4/2005):
This appears to be a generic event that can be recorded by the MS SQL Server
service for a wide range of problems. See the information for various errors
(ordered by the error number).
Error: 0, Description: SqlDumpExceptionHandler: Process 10 generated fatal
exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating
this process. - This type of error indicates a bug in one of the MS SQL
Server binaries. Installation of the latest service pack recommended. If
that does not fix it then call Microsoft Product support. Some newsgroup
postings suggest that this may occur due to lack of space on drive
containing the SQL logs
Event ID: 17052
Source MSSQLSERVER
Type Error
Description You do not have sufficient operating system permission to open
the SQLServerAgent status event.
Comments Adrian Grigorof (Last update 7/28/2004):
From a newsgroup post: "Installing SP3 or SP3a on MS SQL Server 2000
Enterprise Edition caused this problem in my case (MSSQLServer service is
running under different local user account than the SqlServerAgent service
and MSSQLServer is not given Administrator privileges). I see this buggy
behavior on various installations: W2K Advanced Server SP4 or W2K3. SP3 or
SP3a. Default instance or named instance. New freshly installed server or
old one. The only condition is that the MSSQLServer and SQLServerAgent
services run with different user accounts and the MSSQLServer
one does not have admin rights. Before SP3 installation everything runs OK.
What helps: Add MSSQLServer service account to Administrators group. OR Run
MSSQLServer and SQLServerAgent with the same account.
What doesn't help: Changing the services' user accounts using Enterprise
Manager. Add services' accounts to Power Users."
An answer from a MS engineer in a newsgroup post: "I suspect that the issue
is addressed in the cross-database ownership chain which uses in Microsoft
SQL Server Service Pack 3 (SP3). Based on my research, SP3 provides a new
security enhancement related option for configuring cross-database ownership
chaining; enable cross-database ownership chaining for all databases during
setup. With this new option, you can control whether or not you permit
cross-database ownership chaining. By default, this option is disabled.
Microsoft recommends that you use the default option, because it makes your
database server more secure.
However, database objects may have different owners. When an object such as
a view, a stored procedure, or a user-defined function references another
object, an ownership chain can be established. When the ownership chain is
unbroken, SQL Server checks permissions on the source object but not on the
target objects.
Therefore, it is recommended that you change the configuration of the
cross-database ownership chaining. You can reference the following
step-by-step article to perform such a change: M810474 (Cross-Database
Ownership Chaining Behavior Changes in SQL Server).
Additionally, I also suggest you running the MSSQLServer service with a
local account and
SQLServerAgent with Admin privilege to see if the jobs work fine. Otherwise,
it seems that you
should run these both services with the FULL privileges."
From a newsgroup post: "This is most likely a permissions issue. Make sure
the account SQLAgent starting under has permissions to:
- Replace A process level token
- Increase Quotas
- Logon as a service.
Books online states that the account needs to be a member of the local
admins group as well."
Anonymous (Last update 7/28/2004):
I got this error after I have created different accounts for sqlserver and
sqlagent service.
Ekrem Önsoy
"dentaku" <dentaku@.web.de> wrote in message
news:1189672135.231927.120740@.d55g2000hsg.googlegroups.com...
> Because of a corrupt mainboard we tried to restore our SQL Server 7
> databases to a new computer but get the same error for all backups we
> try to restore:
> ---
> ODBC: Nachr.-Nr. 0, Schweregrad 19, Status 1
> SqlDumpExceptionHandler: Prozess 21 erzeugte eine schwere
> Ausnahmebedingung c0000005 EXCEPTION_ACCESS_VIOLATION.Sql Server
> beendet diesen Prozess.
> ---
> The Windows event log shows an event ID 17052. What can be wrong?
>|||We have plenty of free disk and memory space (multiple GBs). We also
have the latest microsoft service packs and updates. And our DB user
is an admin (DBO rights didn't help either).
All restore verifyonly/filelistonly/headeronly requests returned no
error. But every time I try to restore the database (file) I get this
error and the sql error log files says
Bypassing recovery for database 'mine' because it is marked IN LOAD.
I can delete this database, and restore again, but with always the
same error.
databases to a new computer but get the same error for all backups we
try to restore:
---
ODBC: Nachr.-Nr. 0, Schweregrad 19, Status 1
SqlDumpExceptionHandler: Prozess 21 erzeugte eine schwere
Ausnahmebedingung c0000005 EXCEPTION_ACCESS_VIOLATION.Sql Server
beendet diesen Prozess.
---
The Windows event log shows an event ID 17052. What can be wrong?I check out www.eventid.net for this issue and found something about it and
wanted to share with you. Maybe you can find something useful.
From www.EventId.net
Adrian Grigorof (Last update 1/4/2005):
This appears to be a generic event that can be recorded by the MS SQL Server
service for a wide range of problems. See the information for various errors
(ordered by the error number).
Error: 0, Description: SqlDumpExceptionHandler: Process 10 generated fatal
exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating
this process. - This type of error indicates a bug in one of the MS SQL
Server binaries. Installation of the latest service pack recommended. If
that does not fix it then call Microsoft Product support. Some newsgroup
postings suggest that this may occur due to lack of space on drive
containing the SQL logs
Event ID: 17052
Source MSSQLSERVER
Type Error
Description You do not have sufficient operating system permission to open
the SQLServerAgent status event.
Comments Adrian Grigorof (Last update 7/28/2004):
From a newsgroup post: "Installing SP3 or SP3a on MS SQL Server 2000
Enterprise Edition caused this problem in my case (MSSQLServer service is
running under different local user account than the SqlServerAgent service
and MSSQLServer is not given Administrator privileges). I see this buggy
behavior on various installations: W2K Advanced Server SP4 or W2K3. SP3 or
SP3a. Default instance or named instance. New freshly installed server or
old one. The only condition is that the MSSQLServer and SQLServerAgent
services run with different user accounts and the MSSQLServer
one does not have admin rights. Before SP3 installation everything runs OK.
What helps: Add MSSQLServer service account to Administrators group. OR Run
MSSQLServer and SQLServerAgent with the same account.
What doesn't help: Changing the services' user accounts using Enterprise
Manager. Add services' accounts to Power Users."
An answer from a MS engineer in a newsgroup post: "I suspect that the issue
is addressed in the cross-database ownership chain which uses in Microsoft
SQL Server Service Pack 3 (SP3). Based on my research, SP3 provides a new
security enhancement related option for configuring cross-database ownership
chaining; enable cross-database ownership chaining for all databases during
setup. With this new option, you can control whether or not you permit
cross-database ownership chaining. By default, this option is disabled.
Microsoft recommends that you use the default option, because it makes your
database server more secure.
However, database objects may have different owners. When an object such as
a view, a stored procedure, or a user-defined function references another
object, an ownership chain can be established. When the ownership chain is
unbroken, SQL Server checks permissions on the source object but not on the
target objects.
Therefore, it is recommended that you change the configuration of the
cross-database ownership chaining. You can reference the following
step-by-step article to perform such a change: M810474 (Cross-Database
Ownership Chaining Behavior Changes in SQL Server).
Additionally, I also suggest you running the MSSQLServer service with a
local account and
SQLServerAgent with Admin privilege to see if the jobs work fine. Otherwise,
it seems that you
should run these both services with the FULL privileges."
From a newsgroup post: "This is most likely a permissions issue. Make sure
the account SQLAgent starting under has permissions to:
- Replace A process level token
- Increase Quotas
- Logon as a service.
Books online states that the account needs to be a member of the local
admins group as well."
Anonymous (Last update 7/28/2004):
I got this error after I have created different accounts for sqlserver and
sqlagent service.
Ekrem Önsoy
"dentaku" <dentaku@.web.de> wrote in message
news:1189672135.231927.120740@.d55g2000hsg.googlegroups.com...
> Because of a corrupt mainboard we tried to restore our SQL Server 7
> databases to a new computer but get the same error for all backups we
> try to restore:
> ---
> ODBC: Nachr.-Nr. 0, Schweregrad 19, Status 1
> SqlDumpExceptionHandler: Prozess 21 erzeugte eine schwere
> Ausnahmebedingung c0000005 EXCEPTION_ACCESS_VIOLATION.Sql Server
> beendet diesen Prozess.
> ---
> The Windows event log shows an event ID 17052. What can be wrong?
>|||We have plenty of free disk and memory space (multiple GBs). We also
have the latest microsoft service packs and updates. And our DB user
is an admin (DBO rights didn't help either).
All restore verifyonly/filelistonly/headeronly requests returned no
error. But every time I try to restore the database (file) I get this
error and the sql error log files says
Bypassing recovery for database 'mine' because it is marked IN LOAD.
I can delete this database, and restore again, but with always the
same error.
Restore: Cannot Open Backup Device
I am attempting to restore a database onto "Server_A" from a backup file
located on a disk from a different server, "Server_B". My T-SQL is the
following:
restore database [dbname] from disk = '\\server_b\e$\backups\[backupfile.bak]
'
1. I have a domain account set up, and the domain account is running SQLAgent
on Server_A. This service has been stopped and restarted, and even
MSSQLServer service has been stopped and restarted.
2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[backupfile.
bak]'.
3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfile.bak]
' I get returned "Access is Denied"
What am I missing? Since I can successfully UNC to the directory and file on
Server_B, it seems there is something not set up right on SQL Server. Please
help.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1
You need to give read / write permission on the shared folder
"\\server_b\e$\backups" to the account used by sql server and sql agent
services.
AMB
"Robert Richards via droptable.com" wrote:
> I am attempting to restore a database onto "Server_A" from a backup file
> located on a disk from a different server, "Server_B". My T-SQL is the
> following:
> restore database [dbname] from disk = '\\server_b\e$\backups\[backupfile.bak]
> '
> 1. I have a domain account set up, and the domain account is running SQLAgent
> on Server_A. This service has been stopped and restarted, and even
> MSSQLServer service has been stopped and restarted.
> 2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[backupfile.
> bak]'.
> 3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfile.bak]
> ' I get returned "Access is Denied"
> What am I missing? Since I can successfully UNC to the directory and file on
> Server_B, it seems there is something not set up right on SQL Server. Please
> help.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200507/1
>
|||The account has full control on "\\server_b\e$\backups". As a check, I UNC'd
over to the directory and created a text file, just to test.
Alejandro Mesa wrote:[vbcol=seagreen]
>You need to give read / write permission on the shared folder
>"\\server_b\e$\backups" to the account used by sql server and sql agent
>services.
>AMB
>[quoted text clipped - 13 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1
|||RESOLUTION:
Not only did I have to have the domain account running SQLAgent, but I also
had to have the domain account running MSSQLServer on Server_A.
Robert Richards wrote:
>I am attempting to restore a database onto "Server_A" from a backup file
>located on a disk from a different server, "Server_B". My T-SQL is the
>following:
>restore database [dbname] from disk = '\\server_b\e$\backups\[backupfile.bak]
>'
>1. I have a domain account set up, and the domain account is running SQLAgent
>on Server_A. This service has been stopped and restarted, and even
>MSSQLServer service has been stopped and restarted.
>2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[backupfile.
>bak]'.
>3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfile.bak]
>' I get returned "Access is Denied"
>What am I missing? Since I can successfully UNC to the directory and file on
>Server_B, it seems there is something not set up right on SQL Server. Please
>help.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1
|||Robert,
> The account has full control on "\\server_b\e$\backups". As a check, I UNC'd
> over to the directory and created a text file, just to test.
I am not talking about your account, I am talking about the domain account
used by sql server and sql agent services.
Why can't I backup/restore my SQL Server database to a share on another
server?
http://www.windowsitpro.com/Article/...025/14025.html
AMB
"Robert Richards via droptable.com" wrote:
> The account has full control on "\\server_b\e$\backups". As a check, I UNC'd
> over to the directory and created a text file, just to test.
> Alejandro Mesa wrote:
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200507/1
>
sql
located on a disk from a different server, "Server_B". My T-SQL is the
following:
restore database [dbname] from disk = '\\server_b\e$\backups\[backupfile.bak]
'
1. I have a domain account set up, and the domain account is running SQLAgent
on Server_A. This service has been stopped and restarted, and even
MSSQLServer service has been stopped and restarted.
2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[backupfile.
bak]'.
3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfile.bak]
' I get returned "Access is Denied"
What am I missing? Since I can successfully UNC to the directory and file on
Server_B, it seems there is something not set up right on SQL Server. Please
help.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1
You need to give read / write permission on the shared folder
"\\server_b\e$\backups" to the account used by sql server and sql agent
services.
AMB
"Robert Richards via droptable.com" wrote:
> I am attempting to restore a database onto "Server_A" from a backup file
> located on a disk from a different server, "Server_B". My T-SQL is the
> following:
> restore database [dbname] from disk = '\\server_b\e$\backups\[backupfile.bak]
> '
> 1. I have a domain account set up, and the domain account is running SQLAgent
> on Server_A. This service has been stopped and restarted, and even
> MSSQLServer service has been stopped and restarted.
> 2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[backupfile.
> bak]'.
> 3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfile.bak]
> ' I get returned "Access is Denied"
> What am I missing? Since I can successfully UNC to the directory and file on
> Server_B, it seems there is something not set up right on SQL Server. Please
> help.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200507/1
>
|||The account has full control on "\\server_b\e$\backups". As a check, I UNC'd
over to the directory and created a text file, just to test.
Alejandro Mesa wrote:[vbcol=seagreen]
>You need to give read / write permission on the shared folder
>"\\server_b\e$\backups" to the account used by sql server and sql agent
>services.
>AMB
>[quoted text clipped - 13 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1
|||RESOLUTION:
Not only did I have to have the domain account running SQLAgent, but I also
had to have the domain account running MSSQLServer on Server_A.
Robert Richards wrote:
>I am attempting to restore a database onto "Server_A" from a backup file
>located on a disk from a different server, "Server_B". My T-SQL is the
>following:
>restore database [dbname] from disk = '\\server_b\e$\backups\[backupfile.bak]
>'
>1. I have a domain account set up, and the domain account is running SQLAgent
>on Server_A. This service has been stopped and restarted, and even
>MSSQLServer service has been stopped and restarted.
>2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[backupfile.
>bak]'.
>3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfile.bak]
>' I get returned "Access is Denied"
>What am I missing? Since I can successfully UNC to the directory and file on
>Server_B, it seems there is something not set up right on SQL Server. Please
>help.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1
|||Robert,
> The account has full control on "\\server_b\e$\backups". As a check, I UNC'd
> over to the directory and created a text file, just to test.
I am not talking about your account, I am talking about the domain account
used by sql server and sql agent services.
Why can't I backup/restore my SQL Server database to a share on another
server?
http://www.windowsitpro.com/Article/...025/14025.html
AMB
"Robert Richards via droptable.com" wrote:
> The account has full control on "\\server_b\e$\backups". As a check, I UNC'd
> over to the directory and created a text file, just to test.
> Alejandro Mesa wrote:
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200507/1
>
sql
Restore: Cannot Open Backup Device
I am attempting to restore a database onto "Server_A" from a backup file
located on a disk from a different server, "Server_B". My T-SQL is the
following:
restore database [dbname] from disk = '\\server_b\e$\backups\[backup
file.bak]
'
1. I have a domain account set up, and the domain account is running SQLAgen
t
on Server_A. This service has been stopped and restarted, and even
MSSQLServer service has been stopped and restarted.
2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[backu
pfile.
bak]'.
3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfile
.bak]
' I get returned "Access is Denied"
What am I missing? Since I can successfully UNC to the directory and file on
Server_B, it seems there is something not set up right on SQL Server. Please
help.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1You need to give read / write permission on the shared folder
"\\server_b\e$\backups" to the account used by sql server and sql agent
services.
AMB
"Robert Richards via droptable.com" wrote:
> I am attempting to restore a database onto "Server_A" from a backup file
> located on a disk from a different server, "Server_B". My T-SQL is the
> following:
> restore database [dbname] from disk = '\\server_b\e$\backups\[back
upfile.bak]
> '
> 1. I have a domain account set up, and the domain account is running SQLAg
ent
> on Server_A. This service has been stopped and restarted, and even
> MSSQLServer service has been stopped and restarted.
> 2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[bac
kupfile.
> bak]'.
> 3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfi
le.bak]
> ' I get returned "Access is Denied"
> What am I missing? Since I can successfully UNC to the directory and file
on
> Server_B, it seems there is something not set up right on SQL Server. Plea
se
> help.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200507/1
>|||The account has full control on "\\server_b\e$\backups". As a check, I UNC'd
over to the directory and created a text file, just to test.
Alejandro Mesa wrote:[vbcol=seagreen]
>You need to give read / write permission on the shared folder
>"\\server_b\e$\backups" to the account used by sql server and sql agent
>services.
>AMB
>
>[quoted text clipped - 13 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1|||RESOLUTION:
Not only did I have to have the domain account running SQLAgent, but I also
had to have the domain account running MSSQLServer on Server_A.
Robert Richards wrote:
>I am attempting to restore a database onto "Server_A" from a backup file
>located on a disk from a different server, "Server_B". My T-SQL is the
>following:
>restore database [dbname] from disk = '\\server_b\e$\backups\[backu
pfile.bak]
>'
>1. I have a domain account set up, and the domain account is running SQLAge
nt
>on Server_A. This service has been stopped and restarted, and even
>MSSQLServer service has been stopped and restarted.
>2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[back
upfile.
>bak]'.
>3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfil
e.bak]
>' I get returned "Access is Denied"
>What am I missing? Since I can successfully UNC to the directory and file o
n
>Server_B, it seems there is something not set up right on SQL Server. Pleas
e
>help.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1|||Robert,
> The account has full control on "\\server_b\e$\backups". As a check, I UNC
'd
> over to the directory and created a text file, just to test.
I am not talking about your account, I am talking about the domain account
used by sql server and sql agent services.
Why can't I backup/restore my SQL Server database to a share on another
server?
http://www.windowsitpro.com/Article...4025/14025.html
AMB
"Robert Richards via droptable.com" wrote:
> The account has full control on "\\server_b\e$\backups". As a check, I UNC
'd
> over to the directory and created a text file, just to test.
> Alejandro Mesa wrote:
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200507/1
>
located on a disk from a different server, "Server_B". My T-SQL is the
following:
restore database [dbname] from disk = '\\server_b\e$\backups\[backup
file.bak]
'
1. I have a domain account set up, and the domain account is running SQLAgen
t
on Server_A. This service has been stopped and restarted, and even
MSSQLServer service has been stopped and restarted.
2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[backu
pfile.
bak]'.
3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfile
.bak]
' I get returned "Access is Denied"
What am I missing? Since I can successfully UNC to the directory and file on
Server_B, it seems there is something not set up right on SQL Server. Please
help.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1You need to give read / write permission on the shared folder
"\\server_b\e$\backups" to the account used by sql server and sql agent
services.
AMB
"Robert Richards via droptable.com" wrote:
> I am attempting to restore a database onto "Server_A" from a backup file
> located on a disk from a different server, "Server_B". My T-SQL is the
> following:
> restore database [dbname] from disk = '\\server_b\e$\backups\[back
upfile.bak]
> '
> 1. I have a domain account set up, and the domain account is running SQLAg
ent
> on Server_A. This service has been stopped and restarted, and even
> MSSQLServer service has been stopped and restarted.
> 2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[bac
kupfile.
> bak]'.
> 3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfi
le.bak]
> ' I get returned "Access is Denied"
> What am I missing? Since I can successfully UNC to the directory and file
on
> Server_B, it seems there is something not set up right on SQL Server. Plea
se
> help.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200507/1
>|||The account has full control on "\\server_b\e$\backups". As a check, I UNC'd
over to the directory and created a text file, just to test.
Alejandro Mesa wrote:[vbcol=seagreen]
>You need to give read / write permission on the shared folder
>"\\server_b\e$\backups" to the account used by sql server and sql agent
>services.
>AMB
>
>[quoted text clipped - 13 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1|||RESOLUTION:
Not only did I have to have the domain account running SQLAgent, but I also
had to have the domain account running MSSQLServer on Server_A.
Robert Richards wrote:
>I am attempting to restore a database onto "Server_A" from a backup file
>located on a disk from a different server, "Server_B". My T-SQL is the
>following:
>restore database [dbname] from disk = '\\server_b\e$\backups\[backu
pfile.bak]
>'
>1. I have a domain account set up, and the domain account is running SQLAge
nt
>on Server_A. This service has been stopped and restarted, and even
>MSSQLServer service has been stopped and restarted.
>2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[back
upfile.
>bak]'.
>3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfil
e.bak]
>' I get returned "Access is Denied"
>What am I missing? Since I can successfully UNC to the directory and file o
n
>Server_B, it seems there is something not set up right on SQL Server. Pleas
e
>help.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1|||Robert,
> The account has full control on "\\server_b\e$\backups". As a check, I UNC
'd
> over to the directory and created a text file, just to test.
I am not talking about your account, I am talking about the domain account
used by sql server and sql agent services.
Why can't I backup/restore my SQL Server database to a share on another
server?
http://www.windowsitpro.com/Article...4025/14025.html
AMB
"Robert Richards via droptable.com" wrote:
> The account has full control on "\\server_b\e$\backups". As a check, I UNC
'd
> over to the directory and created a text file, just to test.
> Alejandro Mesa wrote:
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200507/1
>
Restore: Cannot Open Backup Device
I am attempting to restore a database onto "Server_A" from a backup file
located on a disk from a different server, "Server_B". My T-SQL is the
following:
restore database [dbname] from disk = '\\server_b\e$\backups\[backupfile.bak]
'
1. I have a domain account set up, and the domain account is running SQLAgent
on Server_A. This service has been stopped and restarted, and even
MSSQLServer service has been stopped and restarted.
2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[backupfile.
bak]'.
3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfile.bak]
' I get returned "Access is Denied"
What am I missing? Since I can successfully UNC to the directory and file on
Server_B, it seems there is something not set up right on SQL Server. Please
help.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1You need to give read / write permission on the shared folder
"\\server_b\e$\backups" to the account used by sql server and sql agent
services.
AMB
"Robert Richards via SQLMonster.com" wrote:
> I am attempting to restore a database onto "Server_A" from a backup file
> located on a disk from a different server, "Server_B". My T-SQL is the
> following:
> restore database [dbname] from disk = '\\server_b\e$\backups\[backupfile.bak]
> '
> 1. I have a domain account set up, and the domain account is running SQLAgent
> on Server_A. This service has been stopped and restarted, and even
> MSSQLServer service has been stopped and restarted.
> 2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[backupfile.
> bak]'.
> 3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfile.bak]
> ' I get returned "Access is Denied"
> What am I missing? Since I can successfully UNC to the directory and file on
> Server_B, it seems there is something not set up right on SQL Server. Please
> help.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1
>|||The account has full control on "\\server_b\e$\backups". As a check, I UNC'd
over to the directory and created a text file, just to test.
Alejandro Mesa wrote:
>You need to give read / write permission on the shared folder
>"\\server_b\e$\backups" to the account used by sql server and sql agent
>services.
>AMB
>> I am attempting to restore a database onto "Server_A" from a backup file
>> located on a disk from a different server, "Server_B". My T-SQL is the
>[quoted text clipped - 13 lines]
>> Server_B, it seems there is something not set up right on SQL Server. Please
>> help.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1|||RESOLUTION:
Not only did I have to have the domain account running SQLAgent, but I also
had to have the domain account running MSSQLServer on Server_A.
Robert Richards wrote:
>I am attempting to restore a database onto "Server_A" from a backup file
>located on a disk from a different server, "Server_B". My T-SQL is the
>following:
>restore database [dbname] from disk = '\\server_b\e$\backups\[backupfile.bak]
>'
>1. I have a domain account set up, and the domain account is running SQLAgent
>on Server_A. This service has been stopped and restarted, and even
>MSSQLServer service has been stopped and restarted.
>2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[backupfile.
>bak]'.
>3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfile.bak]
>' I get returned "Access is Denied"
>What am I missing? Since I can successfully UNC to the directory and file on
>Server_B, it seems there is something not set up right on SQL Server. Please
>help.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1|||Robert,
> The account has full control on "\\server_b\e$\backups". As a check, I UNC'd
> over to the directory and created a text file, just to test.
I am not talking about your account, I am talking about the domain account
used by sql server and sql agent services.
Why can't I backup/restore my SQL Server database to a share on another
server?
http://www.windowsitpro.com/Article/ArticleID/14025/14025.html
AMB
"Robert Richards via SQLMonster.com" wrote:
> The account has full control on "\\server_b\e$\backups". As a check, I UNC'd
> over to the directory and created a text file, just to test.
> Alejandro Mesa wrote:
> >You need to give read / write permission on the shared folder
> >"\\server_b\e$\backups" to the account used by sql server and sql agent
> >services.
> >
> >AMB
> >
> >> I am attempting to restore a database onto "Server_A" from a backup file
> >> located on a disk from a different server, "Server_B". My T-SQL is the
> >[quoted text clipped - 13 lines]
> >> Server_B, it seems there is something not set up right on SQL Server. Please
> >> help.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1
>
located on a disk from a different server, "Server_B". My T-SQL is the
following:
restore database [dbname] from disk = '\\server_b\e$\backups\[backupfile.bak]
'
1. I have a domain account set up, and the domain account is running SQLAgent
on Server_A. This service has been stopped and restarted, and even
MSSQLServer service has been stopped and restarted.
2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[backupfile.
bak]'.
3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfile.bak]
' I get returned "Access is Denied"
What am I missing? Since I can successfully UNC to the directory and file on
Server_B, it seems there is something not set up right on SQL Server. Please
help.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1You need to give read / write permission on the shared folder
"\\server_b\e$\backups" to the account used by sql server and sql agent
services.
AMB
"Robert Richards via SQLMonster.com" wrote:
> I am attempting to restore a database onto "Server_A" from a backup file
> located on a disk from a different server, "Server_B". My T-SQL is the
> following:
> restore database [dbname] from disk = '\\server_b\e$\backups\[backupfile.bak]
> '
> 1. I have a domain account set up, and the domain account is running SQLAgent
> on Server_A. This service has been stopped and restarted, and even
> MSSQLServer service has been stopped and restarted.
> 2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[backupfile.
> bak]'.
> 3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfile.bak]
> ' I get returned "Access is Denied"
> What am I missing? Since I can successfully UNC to the directory and file on
> Server_B, it seems there is something not set up right on SQL Server. Please
> help.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1
>|||The account has full control on "\\server_b\e$\backups". As a check, I UNC'd
over to the directory and created a text file, just to test.
Alejandro Mesa wrote:
>You need to give read / write permission on the shared folder
>"\\server_b\e$\backups" to the account used by sql server and sql agent
>services.
>AMB
>> I am attempting to restore a database onto "Server_A" from a backup file
>> located on a disk from a different server, "Server_B". My T-SQL is the
>[quoted text clipped - 13 lines]
>> Server_B, it seems there is something not set up right on SQL Server. Please
>> help.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1|||RESOLUTION:
Not only did I have to have the domain account running SQLAgent, but I also
had to have the domain account running MSSQLServer on Server_A.
Robert Richards wrote:
>I am attempting to restore a database onto "Server_A" from a backup file
>located on a disk from a different server, "Server_B". My T-SQL is the
>following:
>restore database [dbname] from disk = '\\server_b\e$\backups\[backupfile.bak]
>'
>1. I have a domain account set up, and the domain account is running SQLAgent
>on Server_A. This service has been stopped and restarted, and even
>MSSQLServer service has been stopped and restarted.
>2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[backupfile.
>bak]'.
>3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfile.bak]
>' I get returned "Access is Denied"
>What am I missing? Since I can successfully UNC to the directory and file on
>Server_B, it seems there is something not set up right on SQL Server. Please
>help.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1|||Robert,
> The account has full control on "\\server_b\e$\backups". As a check, I UNC'd
> over to the directory and created a text file, just to test.
I am not talking about your account, I am talking about the domain account
used by sql server and sql agent services.
Why can't I backup/restore my SQL Server database to a share on another
server?
http://www.windowsitpro.com/Article/ArticleID/14025/14025.html
AMB
"Robert Richards via SQLMonster.com" wrote:
> The account has full control on "\\server_b\e$\backups". As a check, I UNC'd
> over to the directory and created a text file, just to test.
> Alejandro Mesa wrote:
> >You need to give read / write permission on the shared folder
> >"\\server_b\e$\backups" to the account used by sql server and sql agent
> >services.
> >
> >AMB
> >
> >> I am attempting to restore a database onto "Server_A" from a backup file
> >> located on a disk from a different server, "Server_B". My T-SQL is the
> >[quoted text clipped - 13 lines]
> >> Server_B, it seems there is something not set up right on SQL Server. Please
> >> help.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1
>
Restore/Create new db using existing mdf and ldf
Hi All,
I'm sure there's a simple answer for this but I haven't found anything so far that gives me the info I'm needing, so if anyone could help, I'd be grateful!
I've been given mdf and ldf files from an existing off-site database to be "restored" locally (sqlserver 7). Usually I restore from a backup file, so I haven't had any experience with this scenario.
I've found info regarding sp_attach...if I create a new database locally and run sp_attach to point to the mdf and ldf files, will this work?
Many thanks in advance for any suggestions!
Cheers,
Megan:rolleyes:
Have answered my own question and for anyone who might have the same issue, yes it can be done by sp_attach_db very easily.
This will create a new database, and you just need to configure your users (sp_change_users_login), etc, again when it's attached.
Easy as!
I'm sure there's a simple answer for this but I haven't found anything so far that gives me the info I'm needing, so if anyone could help, I'd be grateful!
I've been given mdf and ldf files from an existing off-site database to be "restored" locally (sqlserver 7). Usually I restore from a backup file, so I haven't had any experience with this scenario.
I've found info regarding sp_attach...if I create a new database locally and run sp_attach to point to the mdf and ldf files, will this work?
Many thanks in advance for any suggestions!
Cheers,
Megan:rolleyes:
Have answered my own question and for anyone who might have the same issue, yes it can be done by sp_attach_db very easily.
This will create a new database, and you just need to configure your users (sp_change_users_login), etc, again when it's attached.
Easy as!
Restore.RelocateFiles
Hi,
I'm trying to restore a *.bak file into a different database.
Old Database SVW_TEST (from where the *.bak file is):
SVW_TEST_DATA - d:\SVW_TEST_DATA.mdf
SVW_TEST_DATA - d:\SVW_TEST_LOG.ldf
New Database SVW (to restore to):
SVW_DATA - d:\SVW_DATA.mdf
SVW_DATA - d:\SVW_LOG.ldf
I used the following code:
Dim pRestore As New Restore
pRestore.Action = SQLDMORestore_Database
pRestore.Database = "SVW"
pRestore.ReplaceDatabase = True
pRestore.Files = "D:\SVW_TEST.bak"
pRestore.RelocateFiles = "SVW_Test_Data, D:\SVW_DATA.mdf, SVW_TEST_LOG, D:\SVW_LOG.ldf"
pRestore.SQLRestore pServer
But it would work: I get an error saying that "The logical file 'SQL' is not part of the SVW-Database". Why 'SQL' ??
I also tried this version:
pRestore.RelocateFiles = "SVW_Data, D:\SVW_DATA.mdf, SVW_LOG, D:\SVW_LOG.ldf"
There I get an error saying that "The logical file 'SVW_DATA' is not part of the SVW-Database".
Can someone please help.I found the problem myself. I just had to put [] arround the names:
pRestore.RelocateFiles = "[SVW_Test_Data], [D:\SVW_DATA.mdf], [SVW_TEST_LOG], [D:\SVW_LOG.ldf]"
eventhough in some examples i found, it was with out them......
I'm trying to restore a *.bak file into a different database.
Old Database SVW_TEST (from where the *.bak file is):
SVW_TEST_DATA - d:\SVW_TEST_DATA.mdf
SVW_TEST_DATA - d:\SVW_TEST_LOG.ldf
New Database SVW (to restore to):
SVW_DATA - d:\SVW_DATA.mdf
SVW_DATA - d:\SVW_LOG.ldf
I used the following code:
Dim pRestore As New Restore
pRestore.Action = SQLDMORestore_Database
pRestore.Database = "SVW"
pRestore.ReplaceDatabase = True
pRestore.Files = "D:\SVW_TEST.bak"
pRestore.RelocateFiles = "SVW_Test_Data, D:\SVW_DATA.mdf, SVW_TEST_LOG, D:\SVW_LOG.ldf"
pRestore.SQLRestore pServer
But it would work: I get an error saying that "The logical file 'SQL' is not part of the SVW-Database". Why 'SQL' ??
I also tried this version:
pRestore.RelocateFiles = "SVW_Data, D:\SVW_DATA.mdf, SVW_LOG, D:\SVW_LOG.ldf"
There I get an error saying that "The logical file 'SVW_DATA' is not part of the SVW-Database".
Can someone please help.I found the problem myself. I just had to put [] arround the names:
pRestore.RelocateFiles = "[SVW_Test_Data], [D:\SVW_DATA.mdf], [SVW_TEST_LOG], [D:\SVW_LOG.ldf]"
eventhough in some examples i found, it was with out them......
Restore won't open
I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPrise
Manager, when I right-click on my 2 main databases, choose All
Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
stop it. If I do this for any of the sample databases, the Restore dialogue
opens. I even verified there were no open connections to the 2 databases in
question, & it happens on both servers. I had to do a command line restore.
The backup, etc works fine, just the restore won't open. It did this on SQL
SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
terminated any non-critical services to no avail, i.e. Backup Exec services,
Trend Micro AV, etc.
Ted
Possibly a LOT of backuphistory to read. Check out the number of rows in the backup history tables
in msdb. Also, read about sp_delete_backuphistory (which takes a long time to run in order to delete
a lot of backup history).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ted Cole" <Ted Cole@.discussions.microsoft.com> wrote in message
news:99032F45-145B-4500-ABCB-9B922BB540E1@.microsoft.com...
>I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPrise
> Manager, when I right-click on my 2 main databases, choose All
> Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
> stop it. If I do this for any of the sample databases, the Restore dialogue
> opens. I even verified there were no open connections to the 2 databases in
> question, & it happens on both servers. I had to do a command line restore.
> The backup, etc works fine, just the restore won't open. It did this on SQL
> SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
> terminated any non-critical services to no avail, i.e. Backup Exec services,
> Trend Micro AV, etc.
> Ted
sql
Manager, when I right-click on my 2 main databases, choose All
Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
stop it. If I do this for any of the sample databases, the Restore dialogue
opens. I even verified there were no open connections to the 2 databases in
question, & it happens on both servers. I had to do a command line restore.
The backup, etc works fine, just the restore won't open. It did this on SQL
SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
terminated any non-critical services to no avail, i.e. Backup Exec services,
Trend Micro AV, etc.
Ted
Possibly a LOT of backuphistory to read. Check out the number of rows in the backup history tables
in msdb. Also, read about sp_delete_backuphistory (which takes a long time to run in order to delete
a lot of backup history).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ted Cole" <Ted Cole@.discussions.microsoft.com> wrote in message
news:99032F45-145B-4500-ABCB-9B922BB540E1@.microsoft.com...
>I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPrise
> Manager, when I right-click on my 2 main databases, choose All
> Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
> stop it. If I do this for any of the sample databases, the Restore dialogue
> opens. I even verified there were no open connections to the 2 databases in
> question, & it happens on both servers. I had to do a command line restore.
> The backup, etc works fine, just the restore won't open. It did this on SQL
> SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
> terminated any non-critical services to no avail, i.e. Backup Exec services,
> Trend Micro AV, etc.
> Ted
sql
Restore won't open
I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPris
e
Manager, when I right-click on my 2 main databases, choose All
Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
stop it. If I do this for any of the sample databases, the Restore dialogue
opens. I even verified there were no open connections to the 2 databases in
question, & it happens on both servers. I had to do a command line restore.
The backup, etc works fine, just the restore won't open. It did this on SQL
SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
terminated any non-critical services to no avail, i.e. Backup Exec services,
Trend Micro AV, etc.
TedPossibly a LOT of backuphistory to read. Check out the number of rows in the
backup history tables
in msdb. Also, read about sp_delete_backuphistory (which takes a long time t
o run in order to delete
a lot of backup history).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ted Cole" <Ted Cole@.discussions.microsoft.com> wrote in message
news:99032F45-145B-4500-ABCB-9B922BB540E1@.microsoft.com...
>I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPri
se
> Manager, when I right-click on my 2 main databases, choose All
> Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER
to
> stop it. If I do this for any of the sample databases, the Restore dialogu
e
> opens. I even verified there were no open connections to the 2 databases i
n
> question, & it happens on both servers. I had to do a command line restore
.
> The backup, etc works fine, just the restore won't open. It did this on SQ
L
> SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
> terminated any non-critical services to no avail, i.e. Backup Exec service
s,
> Trend Micro AV, etc.
> Ted
e
Manager, when I right-click on my 2 main databases, choose All
Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
stop it. If I do this for any of the sample databases, the Restore dialogue
opens. I even verified there were no open connections to the 2 databases in
question, & it happens on both servers. I had to do a command line restore.
The backup, etc works fine, just the restore won't open. It did this on SQL
SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
terminated any non-critical services to no avail, i.e. Backup Exec services,
Trend Micro AV, etc.
TedPossibly a LOT of backuphistory to read. Check out the number of rows in the
backup history tables
in msdb. Also, read about sp_delete_backuphistory (which takes a long time t
o run in order to delete
a lot of backup history).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ted Cole" <Ted Cole@.discussions.microsoft.com> wrote in message
news:99032F45-145B-4500-ABCB-9B922BB540E1@.microsoft.com...
>I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPri
se
> Manager, when I right-click on my 2 main databases, choose All
> Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER
to
> stop it. If I do this for any of the sample databases, the Restore dialogu
e
> opens. I even verified there were no open connections to the 2 databases i
n
> question, & it happens on both servers. I had to do a command line restore
.
> The backup, etc works fine, just the restore won't open. It did this on SQ
L
> SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
> terminated any non-critical services to no avail, i.e. Backup Exec service
s,
> Trend Micro AV, etc.
> Ted
Restore won't open
I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPrise
Manager, when I right-click on my 2 main databases, choose All
Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
stop it. If I do this for any of the sample databases, the Restore dialogue
opens. I even verified there were no open connections to the 2 databases in
question, & it happens on both servers. I had to do a command line restore.
The backup, etc works fine, just the restore won't open. It did this on SQL
SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
terminated any non-critical services to no avail, i.e. Backup Exec services,
Trend Micro AV, etc.
TedPossibly a LOT of backuphistory to read. Check out the number of rows in the backup history tables
in msdb. Also, read about sp_delete_backuphistory (which takes a long time to run in order to delete
a lot of backup history).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ted Cole" <Ted Cole@.discussions.microsoft.com> wrote in message
news:99032F45-145B-4500-ABCB-9B922BB540E1@.microsoft.com...
>I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPrise
> Manager, when I right-click on my 2 main databases, choose All
> Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
> stop it. If I do this for any of the sample databases, the Restore dialogue
> opens. I even verified there were no open connections to the 2 databases in
> question, & it happens on both servers. I had to do a command line restore.
> The backup, etc works fine, just the restore won't open. It did this on SQL
> SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
> terminated any non-critical services to no avail, i.e. Backup Exec services,
> Trend Micro AV, etc.
> Ted
Manager, when I right-click on my 2 main databases, choose All
Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
stop it. If I do this for any of the sample databases, the Restore dialogue
opens. I even verified there were no open connections to the 2 databases in
question, & it happens on both servers. I had to do a command line restore.
The backup, etc works fine, just the restore won't open. It did this on SQL
SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
terminated any non-critical services to no avail, i.e. Backup Exec services,
Trend Micro AV, etc.
TedPossibly a LOT of backuphistory to read. Check out the number of rows in the backup history tables
in msdb. Also, read about sp_delete_backuphistory (which takes a long time to run in order to delete
a lot of backup history).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ted Cole" <Ted Cole@.discussions.microsoft.com> wrote in message
news:99032F45-145B-4500-ABCB-9B922BB540E1@.microsoft.com...
>I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPrise
> Manager, when I right-click on my 2 main databases, choose All
> Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
> stop it. If I do this for any of the sample databases, the Restore dialogue
> opens. I even verified there were no open connections to the 2 databases in
> question, & it happens on both servers. I had to do a command line restore.
> The backup, etc works fine, just the restore won't open. It did this on SQL
> SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
> terminated any non-critical services to no avail, i.e. Backup Exec services,
> Trend Micro AV, etc.
> Ted
Subscribe to:
Posts (Atom)