restore master database: 2012

Friday, March 30, 2012

Restoreing From Log File

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

Restored SQL 6.5 but can only access locally - Need Help

Pardon my long windedness, but I want to provide a full explanation.
I have very limited (next to none) knowledge of SQL Server or NT / 2000
Server.
We had a membership database stored on an SQL Server 6.5 on an NT system.
We accessed it via a custom written Visual Basic program called Member. The
computer crashed, not the drive. Apparently, it was an old single processor
PC. I tried to move the drive into a newer computer and got a HAL.DLL
error. I was able to recover the DAT files using the drive as a second dive
on a newer PC.
I put the drive in a newer computer and upgraded it to Windows 2000. Then I
reinstalled SQL Server 6.5. I created the matching database and database
devices. I then copied the old DAT files over and it worked. I was able to
access the old database files. Hooray!
I reinstalled the Member program, and sure enough the connection strings
work and I can access my data again. Another Hooray!
Now my only problem is that I cannot connect to the database from any of the
network computers, only locally. I have read many pages on the internet and
experimented quite a bit, but I still get an ODBC timeout failure every time
I try to connect from one of the other computers on the network.
It appears that my SQL is running and sharing the information, but only
locally. Or there is some other reason that I cannot access it over the
network? Any ideas? I really need to get this up and running again soon.
Thanks for any help.
TedDid you enabled the TCP/IP protocol while installing the SQL Server 6.5? If
not go to SQL 6.5 Setup program
and check if the TCP/IP is selected; else include that and try connecting.
Its been a long time I have worked with
SQL 6.5.
Thanks
Hari
SQL Server MVP
"Ted Hadley" <thadley@.cypresscoveresort.com> wrote in message
news:3MWdnUKfzpi_r3PZnZ2dnUVZ_oCdnZ2d@.giganews.com...
> Pardon my long windedness, but I want to provide a full explanation.
> I have very limited (next to none) knowledge of SQL Server or NT / 2000
> Server.
> We had a membership database stored on an SQL Server 6.5 on an NT system.
> We accessed it via a custom written Visual Basic program called Member.
> The computer crashed, not the drive. Apparently, it was an old single
> processor PC. I tried to move the drive into a newer computer and got a
> HAL.DLL error. I was able to recover the DAT files using the drive as a
> second dive on a newer PC.
> I put the drive in a newer computer and upgraded it to Windows 2000. Then
> I reinstalled SQL Server 6.5. I created the matching database and
> database devices. I then copied the old DAT files over and it worked. I
> was able to access the old database files. Hooray!
> I reinstalled the Member program, and sure enough the connection strings
> work and I can access my data again. Another Hooray!
> Now my only problem is that I cannot connect to the database from any of
> the network computers, only locally. I have read many pages on the
> internet and experimented quite a bit, but I still get an ODBC timeout
> failure every time I try to connect from one of the other computers on the
> network.
> It appears that my SQL is running and sharing the information, but only
> locally. Or there is some other reason that I cannot access it over the
> network? Any ideas? I really need to get this up and running again soon.
> Thanks for any help.
> Ted
>|||Hi Ted
Just wondering if you changed the name of the server, in which case you may
need to change the connection properties for the application.
John
"Ted Hadley" wrote:
> Pardon my long windedness, but I want to provide a full explanation.
> I have very limited (next to none) knowledge of SQL Server or NT / 2000
> Server.
> We had a membership database stored on an SQL Server 6.5 on an NT system.
> We accessed it via a custom written Visual Basic program called Member. The
> computer crashed, not the drive. Apparently, it was an old single processor
> PC. I tried to move the drive into a newer computer and got a HAL.DLL
> error. I was able to recover the DAT files using the drive as a second dive
> on a newer PC.
> I put the drive in a newer computer and upgraded it to Windows 2000. Then I
> reinstalled SQL Server 6.5. I created the matching database and database
> devices. I then copied the old DAT files over and it worked. I was able to
> access the old database files. Hooray!
> I reinstalled the Member program, and sure enough the connection strings
> work and I can access my data again. Another Hooray!
> Now my only problem is that I cannot connect to the database from any of the
> network computers, only locally. I have read many pages on the internet and
> experimented quite a bit, but I still get an ODBC timeout failure every time
> I try to connect from one of the other computers on the network.
> It appears that my SQL is running and sharing the information, but only
> locally. Or there is some other reason that I cannot access it over the
> network? Any ideas? I really need to get this up and running again soon.
> Thanks for any help.
> Ted
>
>|||That was exactly the problem. Thank you Hari. I ran the Setup program,
checked TCP/IP, and restarted SQL. It worked immediately.
You're a lifesaver!
Ted
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23Pe%23SG%23xGHA.4576@.TK2MSFTNGP06.phx.gbl...
> Did you enabled the TCP/IP protocol while installing the SQL Server 6.5?
> If not go to SQL 6.5 Setup program
> and check if the TCP/IP is selected; else include that and try connecting.
> Its been a long time I have worked with
> SQL 6.5.
> Thanks
> Hari
> SQL Server MVP
>
> "Ted Hadley" <thadley@.cypresscoveresort.com> wrote in message
> news:3MWdnUKfzpi_r3PZnZ2dnUVZ_oCdnZ2d@.giganews.com...
>> Pardon my long windedness, but I want to provide a full explanation.
>> I have very limited (next to none) knowledge of SQL Server or NT / 2000
>> Server.
>> We had a membership database stored on an SQL Server 6.5 on an NT system.
>> We accessed it via a custom written Visual Basic program called Member.
>> The computer crashed, not the drive. Apparently, it was an old single
>> processor PC. I tried to move the drive into a newer computer and got a
>> HAL.DLL error. I was able to recover the DAT files using the drive as a
>> second dive on a newer PC.
>> I put the drive in a newer computer and upgraded it to Windows 2000.
>> Then I reinstalled SQL Server 6.5. I created the matching database and
>> database devices. I then copied the old DAT files over and it worked. I
>> was able to access the old database files. Hooray!
>> I reinstalled the Member program, and sure enough the connection strings
>> work and I can access my data again. Another Hooray!
>> Now my only problem is that I cannot connect to the database from any of
>> the network computers, only locally. I have read many pages on the
>> internet and experimented quite a bit, but I still get an ODBC timeout
>> failure every time I try to connect from one of the other computers on
>> the network.
>> It appears that my SQL is running and sharing the information, but only
>> locally. Or there is some other reason that I cannot access it over the
>> network? Any ideas? I really need to get this up and running again
>> soon.
>> Thanks for any help.
>> Ted
>|||Thats a good news..
Thanks
Hari
SQL Server MVP
"Ted Hadley" <thadley@.cypresscoveresort.com> wrote in message
news:6a-dnUP6fbGi53LZnZ2dnUVZ_sydnZ2d@.giganews.com...
> That was exactly the problem. Thank you Hari. I ran the Setup program,
> checked TCP/IP, and restarted SQL. It worked immediately.
> You're a lifesaver!
> Ted
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23Pe%23SG%23xGHA.4576@.TK2MSFTNGP06.phx.gbl...
>> Did you enabled the TCP/IP protocol while installing the SQL Server 6.5?
>> If not go to SQL 6.5 Setup program
>> and check if the TCP/IP is selected; else include that and try
>> connecting. Its been a long time I have worked with
>> SQL 6.5.
>> Thanks
>> Hari
>> SQL Server MVP
>>
>> "Ted Hadley" <thadley@.cypresscoveresort.com> wrote in message
>> news:3MWdnUKfzpi_r3PZnZ2dnUVZ_oCdnZ2d@.giganews.com...
>> Pardon my long windedness, but I want to provide a full explanation.
>> I have very limited (next to none) knowledge of SQL Server or NT / 2000
>> Server.
>> We had a membership database stored on an SQL Server 6.5 on an NT
>> system. We accessed it via a custom written Visual Basic program called
>> Member. The computer crashed, not the drive. Apparently, it was an old
>> single processor PC. I tried to move the drive into a newer computer
>> and got a HAL.DLL error. I was able to recover the DAT files using the
>> drive as a second dive on a newer PC.
>> I put the drive in a newer computer and upgraded it to Windows 2000.
>> Then I reinstalled SQL Server 6.5. I created the matching database and
>> database devices. I then copied the old DAT files over and it worked.
>> I was able to access the old database files. Hooray!
>> I reinstalled the Member program, and sure enough the connection strings
>> work and I can access my data again. Another Hooray!
>> Now my only problem is that I cannot connect to the database from any of
>> the network computers, only locally. I have read many pages on the
>> internet and experimented quite a bit, but I still get an ODBC timeout
>> failure every time I try to connect from one of the other computers on
>> the network.
>> It appears that my SQL is running and sharing the information, but only
>> locally. Or there is some other reason that I cannot access it over the
>> network? Any ideas? I really need to get this up and running again
>> soon.
>> Thanks for any help.
>> Ted
>>
>

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

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

http://wvmitchell.blogspot.com/search?q=backup+restore

Restored Database/Diagrams Support/SQL-Server 2005

Hello,

I have restored a database from SQL-server 2000 into SQL-server 2005 and
when I try to install diagram support, I receive this error:

TITLE: Microsoft SQL Server Management Studio
Database diagram support objects cannot be installed because this database
does not have a valid owner. To continue, first use the Files page of the
Database Properties dialog box or the ALTER AUTHORIZATION statement to set
the database owner to a valid login, then add the database diagram support
objects.

Section 5.8.1 of the SQL 2005 Readme file says:
The installation of these support objects can fail on a database that has
been attached or restored from another instance of SQL Server. This can occur
when the database owner name (stored in the database) is not a valid logon
for the instance of SQL Server the database is being attached or restored to.

Changing the owner with the line below did not solve the problem:
ALTER AUTHORIZATION ON DATABASE::databaseTest TO userTest

Can anyone help me with this issue?

Thanks
/Ershad

you're missing just a few steps...

ALTER AUTHORIZATION ON DATABASE::[databaseTest] TO userTest
go
use [databaseTest]
go
EXECUTE AS USER = N'dbo' REVERT
go

|||You answer was very helpful. Thank you very much! I only nedded to execute the following before I could run you statements

EXEC sp_dbcmptlevel 'databaseTest', '90';

|||Just in case anyone else experiences the same problem as I did I'll add one more comment. If your login is a domain account and you are working offline you may get this message until you connect to the domain.|||

Yeah!

It really works! but can I know why?

|||

It appears that the database must be converted to the compatibility level 90 (2005).. I tried all the other recommended commands, while retaining the SQL2000 compatibility level, and it would not work.

I will usually keep a dev data base at the compatibility level of the server it is hosted on, not automatically convert it to the level of my dev machine, but it looks like this is not going to be possible.. Someone please correct me if I am wrong as I would like to be able to continue doing this..

|||

????

?? ???????? ??? ??????.

??? ?? ????? ??????

|||

"Clint" reports an alternative in a thread on this topic at http://geekswithblogs.net/takenote/archive/2006/02/06/68381.aspx:

Actually, you can just go to the database properties, go to the Options page, and change the compatibility to SQL Server 2005.

This seems to have worked for me.

Either way, be advised that this change is not trouble-free for everyone: in the same thread, Harry Chou recommends a SQL Server 2005 Books Online topic that covers a variety of possible incompatibilities between SQL Server 2005 and previous versions: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm. These incompatibilities would become issues in a database after one of these approaches is used to make it compatible with SQL Server 2005.

I don't know if the Upgrade Wizard checks for these incompatibilities.

sql

Restored Database/Diagrams Support/SQL-Server 2005

Hello,

I have restored a database from SQL-server 2000 into SQL-server 2005 and
when I try to install diagram support, I receive this error:

Changing the owner with the line below did not solve the problem:
ALTER AUTHORIZATION ON DATABASE::databaseTest TO userTest

Can anyone help me with this issue?

Thanks
/Ershad

you're missing just a few steps...

ALTER AUTHORIZATION ON DATABASE::[databaseTest] TO userTest
go
use [databaseTest]
go
EXECUTE AS USER = N'dbo' REVERT
go

|||You answer was very helpful. Thank you very much! I only nedded to execute the following before I could run you statements

EXEC sp_dbcmptlevel 'databaseTest', '90';

Yeah!

It really works! but can I know why?

|||

????

?? ???????? ??? ??????.

??? ?? ????? ??????

|||

"Clint" reports an alternative in a thread on this topic at http://geekswithblogs.net/takenote/archive/2006/02/06/68381.aspx:

Actually, you can just go to the database properties, go to the Options page, and change the compatibility to SQL Server 2005.

This seems to have worked for me.

I don't know if the Upgrade Wizard checks for these incompatibilities.

Restored Database/Diagrams Support/SQL-Server 2005

Hello,

I have restored a database from SQL-server 2000 into SQL-server 2005 and
when I try to install diagram support, I receive this error:

Changing the owner with the line below did not solve the problem:
ALTER AUTHORIZATION ON DATABASE::databaseTest TO userTest

Can anyone help me with this issue?

Thanks
/Ershad

you're missing just a few steps...

ALTER AUTHORIZATION ON DATABASE::[databaseTest] TO userTest
go
use [databaseTest]
go
EXECUTE AS USER = N'dbo' REVERT
go

|||You answer was very helpful. Thank you very much! I only nedded to execute the following before I could run you statements

EXEC sp_dbcmptlevel 'databaseTest', '90';

Yeah!

It really works! but can I know why?

|||

????

?? ???????? ??? ??????.

??? ?? ????? ??????

|||

"Clint" reports an alternative in a thread on this topic at http://geekswithblogs.net/takenote/archive/2006/02/06/68381.aspx:

Actually, you can just go to the database properties, go to the Options page, and change the compatibility to SQL Server 2005.

This seems to have worked for me.

I don't know if the Upgrade Wizard checks for these incompatibilities.

Restored Database/Diagrams Support/SQL-Server 2005

Hello,

I have restored a database from SQL-server 2000 into SQL-server 2005 and
when I try to install diagram support, I receive this error:

Changing the owner with the line below did not solve the problem:
ALTER AUTHORIZATION ON DATABASE::databaseTest TO userTest

Can anyone help me with this issue?

Thanks
/Ershad

you're missing just a few steps...

ALTER AUTHORIZATION ON DATABASE::[databaseTest] TO userTest
go
use [databaseTest]
go
EXECUTE AS USER = N'dbo' REVERT
go

|||You answer was very helpful. Thank you very much! I only nedded to execute the following before I could run you statements

EXEC sp_dbcmptlevel 'databaseTest', '90';

Yeah!

It really works! but can I know why?

|||

????

?? ???????? ??? ??????.

??? ?? ????? ??????

|||

"Clint" reports an alternative in a thread on this topic at http://geekswithblogs.net/takenote/archive/2006/02/06/68381.aspx:

Actually, you can just go to the database properties, go to the Options page, and change the compatibility to SQL Server 2005.

This seems to have worked for me.

I don't know if the Upgrade Wizard checks for these incompatibilities.

Restored Database/Diagrams Support/SQL-Server 2005

Hello,

I have restored a database from SQL-server 2000 into SQL-server 2005 and
when I try to install diagram support, I receive this error:

Changing the owner with the line below did not solve the problem:
ALTER AUTHORIZATION ON DATABASE::databaseTest TO userTest

Can anyone help me with this issue?

Thanks
/Ershad

you're missing just a few steps...

ALTER AUTHORIZATION ON DATABASE::[databaseTest] TO userTest
go
use [databaseTest]
go
EXECUTE AS USER = N'dbo' REVERT
go

|||You answer was very helpful. Thank you very much! I only nedded to execute the following before I could run you statements

EXEC sp_dbcmptlevel 'databaseTest', '90';

Yeah!

It really works! but can I know why?

|||

????

?? ???????? ??? ??????.

??? ?? ????? ??????

|||

"Clint" reports an alternative in a thread on this topic at http://geekswithblogs.net/takenote/archive/2006/02/06/68381.aspx:

Actually, you can just go to the database properties, go to the Options page, and change the compatibility to SQL Server 2005.

This seems to have worked for me.

I don't know if the Upgrade Wizard checks for these incompatibilities.

Restored Database User Issue

I restored a SQl database from one server to another and everything was exac
t
from one server to another besides the users. I had 2 users that would not
update correctly. I have tried to drop and recreate and had errors. Any
tips on getting users from one database to another?
TIAHi,
See the system procedure sp_change_users_login in the sql server books
online
Thanks
Hari
SQL Server MVP
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:444BE209-AA86-4D3A-B912-97529E0753EA@.microsoft.com...
>I restored a SQl database from one server to another and everything was
>exact
> from one server to another besides the users. I had 2 users that would
> not
> update correctly. I have tried to drop and recreate and had errors. Any
> tips on getting users from one database to another?
> TIA|||Hi
Look at sp_change_users_login with the Auto_Fix option.
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/
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:444BE209-AA86-4D3A-B912-97529E0753EA@.microsoft.com...
>I restored a SQl database from one server to another and everything was
>exact
> from one server to another besides the users. I had 2 users that would
> not
> update correctly. I have tried to drop and recreate and had errors. Any
> tips on getting users from one database to another?
> TIAsql

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

Restored database from sql server 2005 to 2000

Is there any workaround on this? I need to migrate my data in sql server 2005 into sql server 2000. Thanks in advance.

I would use theSQL Hosting toolkit to move the database to an older version of sql server. Create a script to recreate the database. Then execute the script on the older version of sql server

|||

Hi,

You can only restore database from SQL Server 2000 to SQL Server 2005,

but you can not restore a database from SQL Server 2005 to SQL Server 2000.

Think about it for minute, there are new features and data types that exist only in SQL Server 2005, so it can not work in SQL Server 2000.

However, you may export your data to MS Excel from SQL Server 2005 and then import data from excel to SQL Server 2000.

This may be a workaround for you.

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

Restored All Database Except for Master

Hi there,
Yesterday we built a new SQL server 2000 SP3, we restored all company
database using our b-up software. We tested them and our apps seems to work
fine against the newly built SQL server.
Question that is burning my head is: we restored everything except the
MASTER, MODEL, MSDB and PUBS. It was a matter of not knowing if they MUST be
restored together with our own databases.
Mind you, all our apps can talk and write and no problem at all. Do we still
need to restore these ones to our new SQL server?
Any advises or suggestions is much appreciated. Any questions is appreciated
too.
TIA
DMC
You might want to browse thru these to see why you may or may not want to
move the system dbs and what is involved. By the way there is nothing
special about Pubs, it is a sample db and is usually removed from production
servers.
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"dmc" <dario@.semiconductor.com> wrote in message
news:ebjbO6z9EHA.2984@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> Yesterday we built a new SQL server 2000 SP3, we restored all company
> database using our b-up software. We tested them and our apps seems to
> work
> fine against the newly built SQL server.
> Question that is burning my head is: we restored everything except the
> MASTER, MODEL, MSDB and PUBS. It was a matter of not knowing if they MUST
> be
> restored together with our own databases.
> Mind you, all our apps can talk and write and no problem at all. Do we
> still
> need to restore these ones to our new SQL server?
> Any advises or suggestions is much appreciated. Any questions is
> appreciated
> too.
> TIA
> DMC
>

Restored All Database Except for Master

Hi there,
Yesterday we built a new SQL server 2000 SP3, we restored all company
database using our b-up software. We tested them and our apps seems to work
fine against the newly built SQL server.
Question that is burning my head is: we restored everything except the
MASTER, MODEL, MSDB and PUBS. It was a matter of not knowing if they MUST be
restored together with our own databases.
Mind you, all our apps can talk and write and no problem at all. Do we still
need to restore these ones to our new SQL server?
Any advises or suggestions is much appreciated. Any questions is appreciated
too.
TIA
DMCYou might want to browse thru these to see why you may or may not want to
move the system dbs and what is involved. By the way there is nothing
special about Pubs, it is a sample db and is usually removed from production
servers.
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"dmc" <dario@.semiconductor.com> wrote in message
news:ebjbO6z9EHA.2984@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> Yesterday we built a new SQL server 2000 SP3, we restored all company
> database using our b-up software. We tested them and our apps seems to
> work
> fine against the newly built SQL server.
> Question that is burning my head is: we restored everything except the
> MASTER, MODEL, MSDB and PUBS. It was a matter of not knowing if they MUST
> be
> restored together with our own databases.
> Mind you, all our apps can talk and write and no problem at all. Do we
> still
> need to restore these ones to our new SQL server?
> Any advises or suggestions is much appreciated. Any questions is
> appreciated
> too.
> TIA
> DMC
>sql

Restored All Database Except for Master

Hi there,
Yesterday we built a new SQL server 2000 SP3, we restored all company
database using our b-up software. We tested them and our apps seems to work
fine against the newly built SQL server.
Question that is burning my head is: we restored everything except the
MASTER, MODEL, MSDB and PUBS. It was a matter of not knowing if they MUST be
restored together with our own databases.
Mind you, all our apps can talk and write and no problem at all. Do we still
need to restore these ones to our new SQL server?
Any advises or suggestions is much appreciated. Any questions is appreciated
too.
TIA
DMCYou might want to browse thru these to see why you may or may not want to
move the system dbs and what is involved. By the way there is nothing
special about Pubs, it is a sample db and is usually removed from production
servers.
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"dmc" <dario@.semiconductor.com> wrote in message
news:ebjbO6z9EHA.2984@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> Yesterday we built a new SQL server 2000 SP3, we restored all company
> database using our b-up software. We tested them and our apps seems to
> work
> fine against the newly built SQL server.
> Question that is burning my head is: we restored everything except the
> MASTER, MODEL, MSDB and PUBS. It was a matter of not knowing if they MUST
> be
> restored together with our own databases.
> Mind you, all our apps can talk and write and no problem at all. Do we
> still
> need to restore these ones to our new SQL server?
> Any advises or suggestions is much appreciated. Any questions is
> appreciated
> too.
> TIA
> DMC
>

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

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

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.

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

Friday, March 30, 2012

Restoreing From Log File

Restored SQL 6.5 but can only access locally - Need Help

Restored SQL 6.5 but can only access locally - Need Help

Restored Full with NORECOVERY and db reads dbName (Restoring...)

Restored Full with NORECOVERY and db reads dbName (Restoring...)

Restored Database/Diagrams Support/SQL-Server 2005

Restored Database/Diagrams Support/SQL-Server 2005

Restored Database/Diagrams Support/SQL-Server 2005

Restored Database/Diagrams Support/SQL-Server 2005

Restored Database/Diagrams Support/SQL-Server 2005

Restored Database User Issue

Restored database still "Loading" after two days

Restored database still "Loading" after two days

restored database LOADING for ever

restored database LOADING for ever

restored database LOADING for ever

Restored database in loading mode.

Restored database from sql server 2005 to 2000

Restored Backup problem

Restored All Database Except for Master

Restored All Database Except for Master

Restored All Database Except for Master

Restore?

Restore?

restore: EXCEPTION_ACCESS_VIOLATION c0000005 (17052)

Restore: Cannot Open Backup Device

Restore: Cannot Open Backup Device

Restore: Cannot Open Backup Device

Restore/Create new db using existing mdf and ldf

restore master database

Blog Archive

About Me