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
> >
>

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

restored database LOADING for ever

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

>I restored a 175g database using enterprise manager. It had about 6
>transaction logs in the backup set and I checked the point in time restore.
>I went back and it said DB restore is complete. But the database icon still
>shows (loading). How do I clear the loading status and be able to access and
>use my Database? Please help as I really need to work tonight. Thanks.
>James
|||Sue,
I did and it helped. Thank you very much
James
"Sue Hoegemeier" wrote:

> Try executing a restore using the recovery option:
> RESTORE DATABASE YourDatabase
> WITH RECOVERY
> -Sue
> On Mon, 28 Feb 2005 20:13:02 -0800, "James Juno"
> <JamesJuno@.discussions.microsoft.com> wrote:
>
>

restored database LOADING for ever

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

>I restored a 175g database using enterprise manager. It had about 6
>transaction logs in the backup set and I checked the point in time restore.
>I went back and it said DB restore is complete. But the database icon stil
l
>shows (loading). How do I clear the loading status and be able to access a
nd
>use my Database? Please help as I really need to work tonight. Thanks.
>James|||Sue,
I did and it helped. Thank you very much
James
"Sue Hoegemeier" wrote:

> Try executing a restore using the recovery option:
> RESTORE DATABASE YourDatabase
> WITH RECOVERY
> -Sue
> On Mon, 28 Feb 2005 20:13:02 -0800, "James Juno"
> <JamesJuno@.discussions.microsoft.com> wrote:
>
>

restored database LOADING for ever

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

Restored Backup problem

I had to reinstall my entire Server week. I had everything of importance
backed up.
When I put my Sql Server database .MDF files back, I found that although the
Users within the databases were there, the Logins were not. Where are the
Logins stored? I have my entire previous hard disk contents available. I jus
t
need to know what to restore.Hi
In master database.
t_4g4w.asp" target="_blank">http://msdn.microsoft.com/library/d... />
t_4g4w.asp
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Hoof Hearted" <HoofHearted@.discussions.microsoft.com> wrote in message
news:87C35929-C18C-4C8A-A80C-B36283866EE2@.microsoft.com...
>I had to reinstall my entire Server week. I had everything of importance
> backed up.
> When I put my Sql Server database .MDF files back, I found that although
> the
> Users within the databases were there, the Logins were not. Where are the
> Logins stored? I have my entire previous hard disk contents available. I
> just
> need to know what to restore.|||Hello Mike,
Yes I should have thought of that. It works fine now. Thank you.
Ian

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

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
>

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
>

Restore with scheduler!

Is there anyway you can schedule to restore a database table(s) from a backup file *.bak?

I think you can restore a entire db from a backup but not selected tables........you can restore the
entire db from a perticular days backup file and then script out the objects you need and run
it in some development or uat servers to recreate it there......

|||

Which version and edition of sql server u have? Yes u can schedule tasks in sql server. SQL Server Agent is the scheduller for SQL Server. You can create Jobs which run periodically as u scheduled. But if u have SQL Server 2005 Experss , then sql server agent is not available in that edition. Then u may use Windows Scheduller and SQLCMD command to schedule tasks.

As already mentioned you can not restore only few tables from a DB backup. But there are method alternate methods like restore the whole db with some temp name ,pump the table and drop the database etc etc.

Read about *JOBS* in BOL

and if u want further assistance pse let us know

Madhu

sql

restore with replace

I was testing a restore using a backup of db1 and
restoring to db2. Both db1 and db2 already exist and have
the same file structure. The restore was also moving the
db1 files to the db2 file locations. I did not specify
with replace. I expected the restore to fail, but it
didn't. What am I missing?Through a job in EM. Below is sql.
restore database SynComp_219
from disk = '\\hfddbms1p\d-
drive\mssql\backup\synygy_1t_1206a_0711.bak'
with
move 'metadata_001' to 'D:\MSSQL$INST1
\Data\metadata_IC_APP\metadata_001.mdf',
move 'datasets_001' to 'D:\MSSQL$INST1
\Data\metadata_IC_APP\datasets_001.ndf',
move 'indexes_001' to 'D:\MSSQL$INST1
\Data\metadata_IC_APP\indexes_001.ndf',
move 'logs_001' to 'E:\MSSQL$INST1\log\logs_001.ldf',
recovery,
stats = 5
>--Original Message--
>GM
>How did you perform restore command ? by T-SQL or EM.
>"GM" <gamaglia@.lnc.com> wrote in message
>news:9cf201c35c29$0ee50350$a001280a@.phx.gbl...
>> I was testing a restore using a backup of db1 and
>> restoring to db2. Both db1 and db2 already exist and
have
>> the same file structure. The restore was also moving the
>> db1 files to the db2 file locations. I did not specify
>> with replace. I expected the restore to fail, but it
>> didn't. What am I missing?
>
>.
>

restore weirdness

Hello,
I just did a restore on a backup. The date for the last good restore I
have, according to
select database_name,backup_finish_date from msdb..backupset
order by backup_finish_date desc
(thanks Hari) is 6/7/2004.
The odd thing is that I'm still missing some tables from that backup. I
know for sure because I did a cut and paste of a query I was using and
posted it to this news group on 6/7/2004. Any ideas why my table, and
more importantly the data, is gone?
Hi,
Only way to check that is to analyze your transaction log file. For that you
can use the log explorer.
www.lumigent.com
Thanks
Hari
MCDBA
"Won Lee" <noemail@.nospam.com> wrote in message
news:#zjjH9qWEHA.500@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I just did a restore on a backup. The date for the last good restore I
> have, according to
> select database_name,backup_finish_date from msdb..backupset
> order by backup_finish_date desc
> (thanks Hari) is 6/7/2004.
> The odd thing is that I'm still missing some tables from that backup. I
> know for sure because I did a cut and paste of a query I was using and
> posted it to this news group on 6/7/2004. Any ideas why my table, and
> more importantly the data, is gone?
>
|||OK will do.

restore weirdness

Hello,
I just did a restore on a backup. The date for the last good restore I
have, according to
select database_name,backup_finish_date from msdb..backupset
order by backup_finish_date desc
(thanks Hari) is 6/7/2004.
The odd thing is that I'm still missing some tables from that backup. I
know for sure because I did a cut and paste of a query I was using and
posted it to this news group on 6/7/2004. Any ideas why my table, and
more importantly the data, is gone?Hi,
Only way to check that is to analyze your transaction log file. For that you
can use the log explorer.
www.lumigent.com
Thanks
Hari
MCDBA
"Won Lee" <noemail@.nospam.com> wrote in message
news:#zjjH9qWEHA.500@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I just did a restore on a backup. The date for the last good restore I
> have, according to
> select database_name,backup_finish_date from msdb..backupset
> order by backup_finish_date desc
> (thanks Hari) is 6/7/2004.
> The odd thing is that I'm still missing some tables from that backup. I
> know for sure because I did a cut and paste of a query I was using and
> posted it to this news group on 6/7/2004. Any ideas why my table, and
> more importantly the data, is gone?
>|||OK will do.

restore weirdness

Hello,
I just did a restore on a backup. The date for the last good restore I
have, according to
select database_name,backup_finish_date from msdb..backupset
order by backup_finish_date desc
(thanks Hari) is 6/7/2004.
The odd thing is that I'm still missing some tables from that backup. I
know for sure because I did a cut and paste of a query I was using and
posted it to this news group on 6/7/2004. Any ideas why my table, and
more importantly the data, is gone?Hi,
Only way to check that is to analyze your transaction log file. For that you
can use the log explorer.
www.lumigent.com
--
Thanks
Hari
MCDBA
"Won Lee" <noemail@.nospam.com> wrote in message
news:#zjjH9qWEHA.500@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I just did a restore on a backup. The date for the last good restore I
> have, according to
> select database_name,backup_finish_date from msdb..backupset
> order by backup_finish_date desc
> (thanks Hari) is 6/7/2004.
> The odd thing is that I'm still missing some tables from that backup. I
> know for sure because I did a cut and paste of a query I was using and
> posted it to this news group on 6/7/2004. Any ideas why my table, and
> more importantly the data, is gone?
>|||OK will do.sql

RESTORE VERIFYONLY - Disk space

What is the disk space required for RESTORE VERIFYONLY ?
Could it be the size of the database ?
I got a backup of 11.8 GB and only 11.3 GB free.
If I run RESTORE VERIFYONLY, it does not return anything (no 'The
backup set is valid.').
After that I always get MS Windows 'Device full' errors whenever I try to
save any
file even if I still have 11 GB free.
I may not event start the MS Windows event viewer
I have to reboot my server.
Could it be that RESTORE VERIFYONLY restores the DB in some temporary
file and that the space is not actually released after RESTORE
VERIFYONLY failed ?
If this is the case is there a way to specify a directory for RESTORE
VERIFYONLY ?
Thanks in advance.
PS I am using MS SQL Server 2000 (8.00.760 SP3) on Windows 2000 Server
5.00.2195 SP4.
Hi, Eric
RESTORE VERIFYONLY only reads the backup, without writing anything in
any database (not even a temporary file), so free space is not an issue
when you use RESTORE VERIFYONLY (i.e. you can have only 1GB of free
space, and it would still work for a 11GB backup).
Razvan
|||VERIFYONLY simply reads the backup. In SQL Server 2000, it essentially read
the header. In SQL Server 2005, it reads the header, checksums, and page
chain. It doesn't write the backup back out anywhere.
What else is happening on your server when this runs?
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Paesmans Eric" <epa@.missioncriticalit.com> wrote in message
news:43c3c8d5$0$440$6c56d894@.reader0.news.be.easyn et.net...
> What is the disk space required for RESTORE VERIFYONLY ?
>
> Could it be the size of the database ?
>
> I got a backup of 11.8 GB and only 11.3 GB free.
>
> If I run RESTORE VERIFYONLY, it does not return anything (no 'The
> backup set is valid.').
>
> After that I always get MS Windows 'Device full' errors whenever I try to
> save any
> file even if I still have 11 GB free.
> I may not event start the MS Windows event viewer
> I have to reboot my server.
>
> Could it be that RESTORE VERIFYONLY restores the DB in some temporary
> file and that the space is not actually released after RESTORE
> VERIFYONLY failed ?
>
> If this is the case is there a way to specify a directory for RESTORE
> VERIFYONLY ?
>
> Thanks in advance.
>
> PS I am using MS SQL Server 2000 (8.00.760 SP3) on Windows 2000 Server
> 5.00.2195 SP4.
>
|||The command is executed via a Bash script that is started by the SQL Agent
as a DTS Package.
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:%233w7T9fFGHA.3172@.TK2MSFTNGP10.phx.gbl...
> VERIFYONLY simply reads the backup. In SQL Server 2000, it essentially
read[vbcol=seagreen]
> the header. In SQL Server 2005, it reads the header, checksums, and page
> chain. It doesn't write the backup back out anywhere.
> What else is happening on your server when this runs?
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Paesmans Eric" <epa@.missioncriticalit.com> wrote in message
> news:43c3c8d5$0$440$6c56d894@.reader0.news.be.easyn et.net...
to
>

RESTORE VERIFYONLY - Disk space

What is the disk space required for RESTORE VERIFYONLY ?
Could it be the size of the database ?
I got a backup of 11.8 GB and only 11.3 GB free.
If I run RESTORE VERIFYONLY, it does not return anything (no 'The
backup set is valid.').
After that I always get MS Windows 'Device full' errors whenever I try to
save any
file even if I still have 11 GB free.
I may not event start the MS Windows event viewer
I have to reboot my server.
Could it be that RESTORE VERIFYONLY restores the DB in some temporary
file and that the space is not actually released after RESTORE
VERIFYONLY failed ?
If this is the case is there a way to specify a directory for RESTORE
VERIFYONLY ?
Thanks in advance.
PS I am using MS SQL Server 2000 (8.00.760 SP3) on Windows 2000 Server
5.00.2195 SP4.Hi, Eric
RESTORE VERIFYONLY only reads the backup, without writing anything in
any database (not even a temporary file), so free space is not an issue
when you use RESTORE VERIFYONLY (i.e. you can have only 1GB of free
space, and it would still work for a 11GB backup).
Razvan|||VERIFYONLY simply reads the backup. In SQL Server 2000, it essentially read
the header. In SQL Server 2005, it reads the header, checksums, and page
chain. It doesn't write the backup back out anywhere.
What else is happening on your server when this runs?
--
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Paesmans Eric" <epa@.missioncriticalit.com> wrote in message
news:43c3c8d5$0$440$6c56d894@.reader0.news.be.easynet.net...
> What is the disk space required for RESTORE VERIFYONLY ?
>
> Could it be the size of the database ?
>
> I got a backup of 11.8 GB and only 11.3 GB free.
>
> If I run RESTORE VERIFYONLY, it does not return anything (no 'The
> backup set is valid.').
>
> After that I always get MS Windows 'Device full' errors whenever I try to
> save any
> file even if I still have 11 GB free.
> I may not event start the MS Windows event viewer
> I have to reboot my server.
>
> Could it be that RESTORE VERIFYONLY restores the DB in some temporary
> file and that the space is not actually released after RESTORE
> VERIFYONLY failed ?
>
> If this is the case is there a way to specify a directory for RESTORE
> VERIFYONLY ?
>
> Thanks in advance.
>
> PS I am using MS SQL Server 2000 (8.00.760 SP3) on Windows 2000 Server
> 5.00.2195 SP4.
>|||The command is executed via a Bash script that is started by the SQL Agent
as a DTS Package.
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:%233w7T9fFGHA.3172@.TK2MSFTNGP10.phx.gbl...
> VERIFYONLY simply reads the backup. In SQL Server 2000, it essentially
read
> the header. In SQL Server 2005, it reads the header, checksums, and page
> chain. It doesn't write the backup back out anywhere.
> What else is happening on your server when this runs?
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Paesmans Eric" <epa@.missioncriticalit.com> wrote in message
> news:43c3c8d5$0$440$6c56d894@.reader0.news.be.easynet.net...
> > What is the disk space required for RESTORE VERIFYONLY ?
> >
> >
> > Could it be the size of the database ?
> >
> >
> > I got a backup of 11.8 GB and only 11.3 GB free.
> >
> >
> > If I run RESTORE VERIFYONLY, it does not return anything (no 'The
> > backup set is valid.').
> >
> >
> > After that I always get MS Windows 'Device full' errors whenever I try
to
> > save any
> > file even if I still have 11 GB free.
> > I may not event start the MS Windows event viewer
> > I have to reboot my server.
> >
> >
> > Could it be that RESTORE VERIFYONLY restores the DB in some temporary
> > file and that the space is not actually released after RESTORE
> > VERIFYONLY failed ?
> >
> >
> > If this is the case is there a way to specify a directory for RESTORE
> > VERIFYONLY ?
> >
> >
> > Thanks in advance.
> >
> >
> > PS I am using MS SQL Server 2000 (8.00.760 SP3) on Windows 2000 Server
> > 5.00.2195 SP4.
> >
> >
>

RESTORE VERIFYONLY - Disk space

What is the disk space required for RESTORE VERIFYONLY ?
Could it be the size of the database ?
I got a backup of 11.8 GB and only 11.3 GB free.
If I run RESTORE VERIFYONLY, it does not return anything (no 'The
backup set is valid.').
After that I always get MS Windows 'Device full' errors whenever I try to
save any
file even if I still have 11 GB free.
I may not event start the MS Windows event viewer
I have to reboot my server.
Could it be that RESTORE VERIFYONLY restores the DB in some temporary
file and that the space is not actually released after RESTORE
VERIFYONLY failed ?
If this is the case is there a way to specify a directory for RESTORE
VERIFYONLY ?
Thanks in advance.
PS I am using MS SQL Server 2000 (8.00.760 SP3) on Windows 2000 Server
5.00.2195 SP4.Hi, Eric
RESTORE VERIFYONLY only reads the backup, without writing anything in
any database (not even a temporary file), so free space is not an issue
when you use RESTORE VERIFYONLY (i.e. you can have only 1GB of free
space, and it would still work for a 11GB backup).
Razvan|||VERIFYONLY simply reads the backup. In SQL Server 2000, it essentially read
the header. In SQL Server 2005, it reads the header, checksums, and page
chain. It doesn't write the backup back out anywhere.
What else is happening on your server when this runs?
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Paesmans Eric" <epa@.missioncriticalit.com> wrote in message
news:43c3c8d5$0$440$6c56d894@.reader0.news.be.easynet.net...
> What is the disk space required for RESTORE VERIFYONLY ?
>
> Could it be the size of the database ?
>
> I got a backup of 11.8 GB and only 11.3 GB free.
>
> If I run RESTORE VERIFYONLY, it does not return anything (no 'The
> backup set is valid.').
>
> After that I always get MS Windows 'Device full' errors whenever I try to
> save any
> file even if I still have 11 GB free.
> I may not event start the MS Windows event viewer
> I have to reboot my server.
>
> Could it be that RESTORE VERIFYONLY restores the DB in some temporary
> file and that the space is not actually released after RESTORE
> VERIFYONLY failed ?
>
> If this is the case is there a way to specify a directory for RESTORE
> VERIFYONLY ?
>
> Thanks in advance.
>
> PS I am using MS SQL Server 2000 (8.00.760 SP3) on Windows 2000 Server
> 5.00.2195 SP4.
>|||The command is executed via a Bash script that is started by the SQL Agent
as a DTS Package.
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:%233w7T9fFGHA.3172@.TK2MSFTNGP10.phx.gbl...
> VERIFYONLY simply reads the backup. In SQL Server 2000, it essentially
read
> the header. In SQL Server 2005, it reads the header, checksums, and page
> chain. It doesn't write the backup back out anywhere.
> What else is happening on your server when this runs?
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Paesmans Eric" <epa@.missioncriticalit.com> wrote in message
> news:43c3c8d5$0$440$6c56d894@.reader0.news.be.easynet.net...
to[vbcol=seagreen]
>

Restore using backup device

We currently have a nightly full backup of a user database (call it UserDB) going to a backup device, call it Device_A. Every 2 hours we perform a transaction log backup to the same device, Device_A. To simplify, lets say the backup device file is called
device_a.bak.
Our backup commands are:
BACKUP DATABASE userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'userdb backup', NOSKIP , STATS = 10, NOFORMAT
BACKUP LOG userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'userdb transaction log', NOSKIP , STATS = 10, NOFORMAT
What would be the restore syntax to get it back to a point in time if there were a failure, and at the time of the failure we had the full database backup, followed by two transaction log backups?
Would it be something like this:
1. Restore database userdb from disk = 'e:\backup\device_a.bak' with replace, norecovery
2. Restore log userdb from disk = 'e:\backup\device_a.bak'
It seems, if there are multiple transaction logs to apply, that this would not work to restore to a point in time. How would it be done?
Message posted via http://www.sqlmonster.com
since you do both full bk and log bk with NOINIT, SQL with keep adding new
backup sets into the backup device. Do a RESTORE FILELISTONLY to find out
the full bk set of the day you want to start with. Once you got it let's
say it = x you will run this:
restore database userdb from disk = 'e:\backup\device_a.bak' with replace,
file = x, standby= @.undo_userdb.bak
restore log userdb from disk = 'e:\backup\device_a.bak' with file = x + 1,
standby= @.undo_userdb.bak
I think you can pickup from here. Read "How to restore to a point in time"
in BOL for more details.
hth,
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:77008f35de2d430f818697e0a4d2a00d@.SQLMonster.c om...
> We currently have a nightly full backup of a user database (call it
> UserDB) going to a backup device, call it Device_A. Every 2 hours we
> perform a transaction log backup to the same device, Device_A. To
> simplify, lets say the backup device file is called device_a.bak.
> Our backup commands are:
> BACKUP DATABASE userdb TO device_a WITH NOINIT , NOUNLOAD , NAME =
> N'userdb backup', NOSKIP , STATS = 10, NOFORMAT
> BACKUP LOG userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'userdb
> transaction log', NOSKIP , STATS = 10, NOFORMAT
> What would be the restore syntax to get it back to a point in time if
> there were a failure, and at the time of the failure we had the full
> database backup, followed by two transaction log backups?
> Would it be something like this:
> 1. Restore database userdb from disk = 'e:\backup\device_a.bak' with
> replace, norecovery
> 2. Restore log userdb from disk = 'e:\backup\device_a.bak'
> It seems, if there are multiple transaction logs to apply, that this would
> not work to restore to a point in time. How would it be done?
> --
> Message posted via http://www.sqlmonster.com
|||Thanks, that is what I was looking for.
I need further clarification that BOL did not provide, or else I am too boneheaded to understand.
Since I am adding new backup sets to the same backup device the RESTORE FILELISTONLY seems to lack the full information I need to identify the the files (the filenumber needed in the RESTORE DATABASE and RESTORE LOG statements) needed to restore to a poin
t in time. It seems like I need to also utilize the RESTORE HEADERONLY also, but that still does not give me the filenumber. How can I retrieve the specific filenumbers needed to encompass my most recent full backup and the subsequent log backups? I must
be missing something and cannot see or understand it.
Message posted via http://www.sqlmonster.com
|||> It seems like I need to also utilize the RESTORE HEADERONLY also, but that
> still does not give me the filenumber.
The Position column of the RESTORE HEADERONLY results is the file number of
the backup. This is the value you need to specify as the FILE parameter in
your restore commands.
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:95c034fd30aa48d1a13d01d1b50fade2@.SQLMonster.c om...
> Thanks, that is what I was looking for.
> I need further clarification that BOL did not provide, or else I am too
> boneheaded to understand.
> Since I am adding new backup sets to the same backup device the RESTORE
> FILELISTONLY seems to lack the full information I need to identify the the
> files (the filenumber needed in the RESTORE DATABASE and RESTORE LOG
> statements) needed to restore to a point in time. It seems like I need to
> also utilize the RESTORE HEADERONLY also, but that still does not give me
> the filenumber. How can I retrieve the specific filenumbers needed to
> encompass my most recent full backup and the subsequent log backups? I
> must be missing something and cannot see or understand it.
> --
> Message posted via http://www.sqlmonster.com
|||I didn't double check when I wrote RESTORE FILELISTONLY. I just remmember
it is either one to get the file number. It should be in RESTORE HEADERONLY
as Dan pointed out.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:95c034fd30aa48d1a13d01d1b50fade2@.SQLMonster.c om...
> Thanks, that is what I was looking for.
> I need further clarification that BOL did not provide, or else I am too
> boneheaded to understand.
> Since I am adding new backup sets to the same backup device the RESTORE
> FILELISTONLY seems to lack the full information I need to identify the the
> files (the filenumber needed in the RESTORE DATABASE and RESTORE LOG
> statements) needed to restore to a point in time. It seems like I need to
> also utilize the RESTORE HEADERONLY also, but that still does not give me
> the filenumber. How can I retrieve the specific filenumbers needed to
> encompass my most recent full backup and the subsequent log backups? I
> must be missing something and cannot see or understand it.
> --
> Message posted via http://www.sqlmonster.com