Restoring sql server 7.0 database into sql server 2005 express - problem!

Hi!

I'm a beginner w ith SQL Express and am having some problems. I am using 2005 SP1 and installed the SQL Server Management Studio Express as well. I have a backup from a SQL Server 7.0 database that I am trying to restore into my database using the management studio. When I try to restore from the file into my database, I get the error,

System.Data.SqlClient.SqlError: The backup of the system database on the device D:\GMBeta2Local.bak cannot be restored because it was created by a different version of the server (7.00.1063) than this server (9.00.3033). (Microsoft.SqlServer.Express.Smo)

Is this true? Is there any way for me to get this data into my SQL Server 2005 express on my laptop?

hi,

thi message is usually raised when a system database is tried to be restored across different SQL Server versions, as reported in http://support.microsoft.com/kb/264474/en-us..

you should only "upgrade" user's databases..

regards

Restoring SQL Server 2000 backup to Express

When I try to restore my SQL Server 2000 backup file to a newly installed copy of SQL Server 2005 Express using Studio Express, I get the following error:

The backup set holds a backup of a database other than the existing 'UpperBridge' database. (Microsoft.sqlservfer.Express.smo)

The backup is made from an SQL Server 2000 database called 'UpperBridge'

I am trying to restore to a database called 'UpperBridge' which I created under 'New databases' in Studio Express.

Any help very much appreciated.

hi,

withers wrote:

When I try to restore my SQL Server 2000 backup file to a newly installed copy of SQL Server 2005 Express using Studio Express, I get the following error:

The backup set holds a backup of a database other than the existing 'UpperBridge' database. (Microsoft.sqlservfer.Express.smo)

The backup is made from an SQL Server 2000 database called 'UpperBridge'

I am trying to restore to a database called 'UpperBridge' which I created under 'New databases' in Studio Express.

Any help very much appreciated.

try forcing the "overwrite" of the database already present in the SQLExpress instance,
RESTORE DATABASE ....
....
WITH REPLACE;

or just drop the database before restoring (obviously take a full local backup of it before dropping it, if required )..

regards

Restoring MSDE databases to SQL Server 2000

Hi,
We have a MSDE 2000 installed on a blackberry server that contains
Blackberry configuration data. For testing the Net backups we need to restor
e
it to a SQL Server 2000 server. The SQL Server 2000 is already installed and
has couple of test databases that I will drop before doing the restore for
the system and the user databases from the Net backups of the MSDE 2000
databases.
I am planning on restoring master first, then msdb, then model and then the
user databases. For restoring master I'll just restore the master from the
Net backup so that it replaces the current master and then I will just repea
t
the process with other system databases. Is this the right way to proceed or
am I missing something? Any advise will be greatly appreciated. Thanks.If you don't have the same path, then you will get into some problems. Also
make sure you have the
same build number of the SQL Server engines. And, finally, read KB 224071
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:5DDC3553-2145-4212-9EBE-69EAF201633A@.microsoft.com...
> Hi,
> We have a MSDE 2000 installed on a blackberry server that contains
> blackberry configuration data. For testing the Net backups we need to rest
ore
> it to a SQL Server 2000 server. The SQL Server 2000 is already installed a
nd
> has couple of test databases that I will drop before doing the restore for
> the system and the user databases from the Net backups of the MSDE 2000
> databases.
> I am planning on restoring master first, then msdb, then model and then th
e
> user databases. For restoring master I'll just restore the master from the
> Net backup so that it replaces the current master and then I will just rep
eat
> the process with other system databases. Is this the right way to proceed
or
> am I missing something? Any advise will be greatly appreciated. Thanks.|||Thanks a lot. I did check the paths and found that the paths were different.
So I created the same path on the target server. I found this on the SQL
Server properties 8.00.760 (SP3) on both the SQL Servers. Is this what you
are referring to as the build numbers?
The article that you have mentioned KB 224071 diccusses moving databases by
detaching and attaching, while I want to restore using the files from the
VERITAS Net Backup to test it. Any insight would be appreciated.
"Tibor Karaszi" wrote:

> If you don't have the same path, then you will get into some problems. Als
o make sure you have the
> same build number of the SQL Server engines. And, finally, read KB 224071
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:5DDC3553-2145-4212-9EBE-69EAF201633A@.microsoft.com...
>
>|||> I found this on the SQL
> Server properties 8.00.760 (SP3) on both the SQL Servers. Is this what you
> are referring to as the build numbers?
Yes.

> while I want to restore using the files from the
> VERITAS Net Backup to test it. Any insight would be appreciated.
Which, I believe, lead you in to unsupported territory. Anyhow, check out
http://vyaskn.tripod.com/moving_sql_server.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:20CAF583-3917-4396-89E3-ED54C7BD3087@.microsoft.com...[vbcol=seagreen]
> Thanks a lot. I did check the paths and found that the paths were differen
t.
> So I created the same path on the target server. I found this on the SQL
> Server properties 8.00.760 (SP3) on both the SQL Servers. Is this what you
> are referring to as the build numbers?
> The article that you have mentioned KB 224071 diccusses moving databases b
y
> detaching and attaching, while I want to restore using the files from the
> VERITAS Net Backup to test it. Any insight would be appreciated.
> "Tibor Karaszi" wrote:
>

Restoring MSDE databases to SQL Server 2000

Hi,
We have a MSDE 2000 installed on a Blackberry server that contains
Blackberry configuration data. For testing the Net backups we need to restore
it to a SQL Server 2000 server. The SQL Server 2000 is already installed and
has couple of test databases that I will drop before doing the restore for
the system and the user databases from the Net backups of the MSDE 2000
databases.
I am planning on restoring master first, then msdb, then model and then the
user databases. For restoring master I'll just restore the master from the
Net backup so that it replaces the current master and then I will just repeat
the process with other system databases. Is this the right way to proceed or
am I missing something? Any advise will be greatly appreciated. Thanks.
If you don't have the same path, then you will get into some problems. Also make sure you have the
same build number of the SQL Server engines. And, finally, read KB 224071
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:5DDC3553-2145-4212-9EBE-69EAF201633A@.microsoft.com...
> Hi,
> We have a MSDE 2000 installed on a Blackberry server that contains
> Blackberry configuration data. For testing the Net backups we need to restore
> it to a SQL Server 2000 server. The SQL Server 2000 is already installed and
> has couple of test databases that I will drop before doing the restore for
> the system and the user databases from the Net backups of the MSDE 2000
> databases.
> I am planning on restoring master first, then msdb, then model and then the
> user databases. For restoring master I'll just restore the master from the
> Net backup so that it replaces the current master and then I will just repeat
> the process with other system databases. Is this the right way to proceed or
> am I missing something? Any advise will be greatly appreciated. Thanks.
|||Thanks a lot. I did check the paths and found that the paths were different.
So I created the same path on the target server. I found this on the SQL
Server properties 8.00.760 (SP3) on both the SQL Servers. Is this what you
are referring to as the build numbers?
The article that you have mentioned KB 224071 diccusses moving databases by
detaching and attaching, while I want to restore using the files from the
VERITAS Net Backup to test it. Any insight would be appreciated.
"Tibor Karaszi" wrote:

> If you don't have the same path, then you will get into some problems. Also make sure you have the
> same build number of the SQL Server engines. And, finally, read KB 224071
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:5DDC3553-2145-4212-9EBE-69EAF201633A@.microsoft.com...
>
>
|||> I found this on the SQL
> Server properties 8.00.760 (SP3) on both the SQL Servers. Is this what you
> are referring to as the build numbers?
Yes.

> while I want to restore using the files from the
> VERITAS Net Backup to test it. Any insight would be appreciated.
Which, I believe, lead you in to unsupported territory. Anyhow, check out
http://vyaskn.tripod.com/moving_sql_server.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:20CAF583-3917-4396-89E3-ED54C7BD3087@.microsoft.com...[vbcol=seagreen]
> Thanks a lot. I did check the paths and found that the paths were different.
> So I created the same path on the target server. I found this on the SQL
> Server properties 8.00.760 (SP3) on both the SQL Servers. Is this what you
> are referring to as the build numbers?
> The article that you have mentioned KB 224071 diccusses moving databases by
> detaching and attaching, while I want to restore using the files from the
> VERITAS Net Backup to test it. Any insight would be appreciated.
> "Tibor Karaszi" wrote:

Restoring MSDE databases to SQL Server 2000

Hi,
We have a MSDE 2000 installed on a Blackberry server that contains
Blackberry configuration data. For testing the Net backups we need to restore
it to a SQL Server 2000 server. The SQL Server 2000 is already installed and
has couple of test databases that I will drop before doing the restore for
the system and the user databases from the Net backups of the MSDE 2000
databases.
I am planning on restoring master first, then msdb, then model and then the
user databases. For restoring master I'll just restore the master from the
Net backup so that it replaces the current master and then I will just repeat
the process with other system databases. Is this the right way to proceed or
am I missing something? Any advise will be greatly appreciated. Thanks.If you don't have the same path, then you will get into some problems. Also make sure you have the
same build number of the SQL Server engines. And, finally, read KB 224071
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:5DDC3553-2145-4212-9EBE-69EAF201633A@.microsoft.com...
> Hi,
> We have a MSDE 2000 installed on a Blackberry server that contains
> Blackberry configuration data. For testing the Net backups we need to restore
> it to a SQL Server 2000 server. The SQL Server 2000 is already installed and
> has couple of test databases that I will drop before doing the restore for
> the system and the user databases from the Net backups of the MSDE 2000
> databases.
> I am planning on restoring master first, then msdb, then model and then the
> user databases. For restoring master I'll just restore the master from the
> Net backup so that it replaces the current master and then I will just repeat
> the process with other system databases. Is this the right way to proceed or
> am I missing something? Any advise will be greatly appreciated. Thanks.|||Thanks a lot. I did check the paths and found that the paths were different.
So I created the same path on the target server. I found this on the SQL
Server properties 8.00.760 (SP3) on both the SQL Servers. Is this what you
are referring to as the build numbers?
The article that you have mentioned KB 224071 diccusses moving databases by
detaching and attaching, while I want to restore using the files from the
VERITAS Net Backup to test it. Any insight would be appreciated.
"Tibor Karaszi" wrote:
> If you don't have the same path, then you will get into some problems. Also make sure you have the
> same build number of the SQL Server engines. And, finally, read KB 224071
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:5DDC3553-2145-4212-9EBE-69EAF201633A@.microsoft.com...
> > Hi,
> >
> > We have a MSDE 2000 installed on a Blackberry server that contains
> > Blackberry configuration data. For testing the Net backups we need to restore
> > it to a SQL Server 2000 server. The SQL Server 2000 is already installed and
> > has couple of test databases that I will drop before doing the restore for
> > the system and the user databases from the Net backups of the MSDE 2000
> > databases.
> >
> > I am planning on restoring master first, then msdb, then model and then the
> > user databases. For restoring master I'll just restore the master from the
> > Net backup so that it replaces the current master and then I will just repeat
> > the process with other system databases. Is this the right way to proceed or
> > am I missing something? Any advise will be greatly appreciated. Thanks.
>
>|||> I found this on the SQL
> Server properties 8.00.760 (SP3) on both the SQL Servers. Is this what you
> are referring to as the build numbers?
Yes.
> while I want to restore using the files from the
> VERITAS Net Backup to test it. Any insight would be appreciated.
Which, I believe, lead you in to unsupported territory. Anyhow, check out
http://vyaskn.tripod.com/moving_sql_server.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:20CAF583-3917-4396-89E3-ED54C7BD3087@.microsoft.com...
> Thanks a lot. I did check the paths and found that the paths were different.
> So I created the same path on the target server. I found this on the SQL
> Server properties 8.00.760 (SP3) on both the SQL Servers. Is this what you
> are referring to as the build numbers?
> The article that you have mentioned KB 224071 diccusses moving databases by
> detaching and attaching, while I want to restore using the files from the
> VERITAS Net Backup to test it. Any insight would be appreciated.
> "Tibor Karaszi" wrote:
>> If you don't have the same path, then you will get into some problems. Also make sure you have
>> the
>> same build number of the SQL Server engines. And, finally, read KB 224071
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:5DDC3553-2145-4212-9EBE-69EAF201633A@.microsoft.com...
>> > Hi,
>> >
>> > We have a MSDE 2000 installed on a Blackberry server that contains
>> > Blackberry configuration data. For testing the Net backups we need to restore
>> > it to a SQL Server 2000 server. The SQL Server 2000 is already installed and
>> > has couple of test databases that I will drop before doing the restore for
>> > the system and the user databases from the Net backups of the MSDE 2000
>> > databases.
>> >
>> > I am planning on restoring master first, then msdb, then model and then the
>> > user databases. For restoring master I'll just restore the master from the
>> > Net backup so that it replaces the current master and then I will just repeat
>> > the process with other system databases. Is this the right way to proceed or
>> > am I missing something? Any advise will be greatly appreciated. Thanks.
>>

Restoring master db from old install of different SQL version

All,
I had to downgrade from Enterprise to Standard on a server. So I took
backups of all DB's, uninstalled enterprise and installed standard with
SP3a. Then I restored all user DB's.
I then wanted to use the master from enterprise and install into standard so
I would keep users and passwords etc. We have apps that use users like
SalesAppUser with a password hardly anyone knows. So I just wanted a nice
plain replace new master with old master.
Thanks to Robert Davies who helped me get to the command line prompt for
opening up the server in single user mode. I then restored master from the
previous backup and server no longer functions.
I just want to confirm that is what you would expect from one install to
another. Reading back it now looks like I was trying to put the square piece
through the round hole !!
Any suggestions for how I might achieve my goal?
Thanks
MPMHi
If logins/passwords are the only things you need to keep then you may want
to look at
http://support.microsoft.com/kb/246133/
Good overall references are:
http://support.microsoft.com/kb/224071/EN-US/
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
John
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:A30FB667-82C7-4156-A70C-E24497E3E96D@.microsoft.com...
> All,
> I had to downgrade from Enterprise to Standard on a server. So I took
> backups of all DB's, uninstalled enterprise and installed standard with
> SP3a. Then I restored all user DB's.
> I then wanted to use the master from enterprise and install into standard
> so
> I would keep users and passwords etc. We have apps that use users like
> SalesAppUser with a password hardly anyone knows. So I just wanted a nice
> plain replace new master with old master.
> Thanks to Robert Davies who helped me get to the command line prompt for
> opening up the server in single user mode. I then restored master from
> the
> previous backup and server no longer functions.
> I just want to confirm that is what you would expect from one install to
> another. Reading back it now looks like I was trying to put the square
> piece
> through the round hole !!
> Any suggestions for how I might achieve my goal?
> Thanks
> MPM

Restoring master db from old install of different SQL version

All,
I had to downgrade from Enterprise to Standard on a server. So I took
backups of all DB's, uninstalled enterprise and installed standard with
SP3a. Then I restored all user DB's.
I then wanted to use the master from enterprise and install into standard so
I would keep users and passwords etc. We have apps that use users like
SalesAppUser with a password hardly anyone knows. So I just wanted a nice
plain replace new master with old master.
Thanks to Robert Davies who helped me get to the command line prompt for
opening up the server in single user mode. I then restored master from the
previous backup and server no longer functions.
I just want to confirm that is what you would expect from one install to
another. Reading back it now looks like I was trying to put the square piece
through the round hole !!
Any suggestions for how I might achieve my goal?
Thanks
MPM
Hi
If logins/passwords are the only things you need to keep then you may want
to look at
http://support.microsoft.com/kb/246133/
Good overall references are:
http://support.microsoft.com/kb/224071/EN-US/
http://support.microsoft.com/default...;en-us;Q314546
John
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:A30FB667-82C7-4156-A70C-E24497E3E96D@.microsoft.com...
> All,
> I had to downgrade from Enterprise to Standard on a server. So I took
> backups of all DB's, uninstalled enterprise and installed standard with
> SP3a. Then I restored all user DB's.
> I then wanted to use the master from enterprise and install into standard
> so
> I would keep users and passwords etc. We have apps that use users like
> SalesAppUser with a password hardly anyone knows. So I just wanted a nice
> plain replace new master with old master.
> Thanks to Robert Davies who helped me get to the command line prompt for
> opening up the server in single user mode. I then restored master from
> the
> previous backup and server no longer functions.
> I just want to confirm that is what you would expect from one install to
> another. Reading back it now looks like I was trying to put the square
> piece
> through the round hole !!
> Any suggestions for how I might achieve my goal?
> Thanks
> MPM

Restoring master db from old install of different SQL version

All,
I had to downgrade from Enterprise to Standard on a server. So I took
backups of all DB's, uninstalled enterprise and installed standard with
SP3a. Then I restored all user DB's.
I then wanted to use the master from enterprise and install into standard so
I would keep users and passwords etc. We have apps that use users like
SalesAppUser with a password hardly anyone knows. So I just wanted a nice
plain replace new master with old master.
Thanks to Robert Davies who helped me get to the command line prompt for
opening up the server in single user mode. I then restored master from the
previous backup and server no longer functions.
I just want to confirm that is what you would expect from one install to
another. Reading back it now looks like I was trying to put the square piec
e
through the round hole !!
Any suggestions for how I might achieve my goal?
Thanks
MPMHi
If logins/passwords are the only things you need to keep then you may want
to look at
http://support.microsoft.com/kb/246133/
Good overall references are:
http://support.microsoft.com/kb/224071/EN-US/
http://support.microsoft.com/defaul...b;en-us;Q314546
John
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:A30FB667-82C7-4156-A70C-E24497E3E96D@.microsoft.com...
> All,
> I had to downgrade from Enterprise to Standard on a server. So I took
> backups of all DB's, uninstalled enterprise and installed standard with
> SP3a. Then I restored all user DB's.
> I then wanted to use the master from enterprise and install into standard
> so
> I would keep users and passwords etc. We have apps that use users like
> SalesAppUser with a password hardly anyone knows. So I just wanted a nice
> plain replace new master with old master.
> Thanks to Robert Davies who helped me get to the command line prompt for
> opening up the server in single user mode. I then restored master from
> the
> previous backup and server no longer functions.
> I just want to confirm that is what you would expect from one install to
> another. Reading back it now looks like I was trying to put the square
> piece
> through the round hole !!
> Any suggestions for how I might achieve my goal?
> Thanks
> MPM

Restoring just the Data?

Hi All,

We have a database on our server in the office, which I have installed to test an application in delevelopment. I've got it all working and tested OK.

I've continued working on it at home. I've added some fields to one of the tables. The database in office has a data in which I whish to transfer to my test database - with the new fields added.

I have tried the backup and restore procedure, which works great, but it overwrites the entire database, I just want the data from all tables in one procedure each way, one to backup and one to restore.

I hope that makes sense.

Thanks

Wayne

You will need to employ some form of scripting, perhaps these resources will help point you in the right direction.

DDL -Script Database to File
http://www.wardyit.com/blog/blog/archive/2006/07/21/133.aspx
http://www.sqlteam.com/publish/scriptio
http://www.aspfaq.com/etiquette.asp?id=5006
http://www.codeproject.com/dotnet/ScriptDatabase.asp
http://www.nigelrivett.net/DMO/DMOScriptAllDatabases.html
http://rac4sql.net/qalite_main.asp

restoring from .bak

i have sql server 2005 dev installed on a laptop. i have 2 .bak's from production sql server 2005 database. i went through the gui restore procedure and both backup files completed successfully. now both databases are in the (Restoring...) status and have been for quite some time. i have sp2 applied. when will these become available? each .bak was around 400 mb. it has been a few hours.Did you refresh your GUI afterte restore? Can you connect to your database?|||it's now the morning and been at least a good 12 hours. refresh in the GUI shows them still in restoring status. i closed and reopened the management studio and they are still both restoring. i am unable to choose either database from the drop down when i open a new query window. i told it during restore to do them in norecovery mode under the expectation that this would be the fastest.|||i did notice that the data files created were substantially bigger... one backup created a 7.2gb mdf, 92mb ldf, the other created a 543mb mdf, 1.3gb ldf, and 371mb ndf... actually it appears when I specified the new filepath for that one from the backup, that I forgot the suffix. will that matter? I suppose I could stop the db service and rename it to an .ndf real quick. doesn't seem to be the cause of my slow restore issue though..|||FYI i restored the db in norecovery mode, under the impression that db access was denied only for the duration of the restore. i tried it with the first option later and the db's loaded fine. there must be some step to flip the db into available once in the restoring status, however it was not obvious/intuitive to me based on the gui or error messages i was receiving. but i seemed to be missing that step.|||I've experienced the same symptom, but with both norecovery and recovery options. Each time this produces a process that cannot be terminated until the machine is turned off. No errors are logged in sql server or windows. I've also verified the database backup through the verifyrestoreonly option and by restoring to another machine/instance.

Any thoughts?
|||i did not appear to have an active ongoing process; my cpu appeared idle. i didn't have a problem installing sp2 hoping it would fix the problem while the databases were in the restoring state. my assumption is there is some toggle (that should be made more apparent in gui error messages) that one could flip in there or some hidden sql to flip the db to go. i'm just naively jumping into this and figuring it out, my assumption is some book (a book online maybe) covers this situation (?) or perhaps it is some flakey bug.|||

That's your key.

Restoring a database in norecovery mode tells SQL Server that you intend to apply transaction log backups to roll the database forward before making it available. Thus the restore process is not complete. That's why your database is staying in 'restoring' state.

To bring your database online, simply use the command RESTORE DATABASE <dbname> WITH RECOVERY

You don't need a backup file for this - it just invokes the recovery logic to bring the DB online.

restoring from .bak

i have sql server 2005 dev installed on a laptop. i have 2 .bak's from production sql server 2005 database. i went through the gui restore procedure and both backup files completed successfully. now both databases are in the (Restoring...) status and have been for quite some time. i have sp2 applied. when will these become available? each .bak was around 400 mb. it has been a few hours.Did you refresh your GUI afterte restore? Can you connect to your database?|||it's now the morning and been at least a good 12 hours. refresh in the GUI shows them still in restoring status. i closed and reopened the management studio and they are still both restoring. i am unable to choose either database from the drop down when i open a new query window. i told it during restore to do them in norecovery mode under the expectation that this would be the fastest.|||i did notice that the data files created were substantially bigger... one backup created a 7.2gb mdf, 92mb ldf, the other created a 543mb mdf, 1.3gb ldf, and 371mb ndf... actually it appears when I specified the new filepath for that one from the backup, that I forgot the suffix. will that matter? I suppose I could stop the db service and rename it to an .ndf real quick. doesn't seem to be the cause of my slow restore issue though..|||FYI i restored the db in norecovery mode, under the impression that db access was denied only for the duration of the restore. i tried it with the first option later and the db's loaded fine. there must be some step to flip the db into available once in the restoring status, however it was not obvious/intuitive to me based on the gui or error messages i was receiving. but i seemed to be missing that step.|||I've experienced the same symptom, but with both norecovery and recovery options. Each time this produces a process that cannot be terminated until the machine is turned off. No errors are logged in sql server or windows. I've also verified the database backup through the verifyrestoreonly option and by restoring to another machine/instance.

Any thoughts?
|||i did not appear to have an active ongoing process; my cpu appeared idle. i didn't have a problem installing sp2 hoping it would fix the problem while the databases were in the restoring state. my assumption is there is some toggle (that should be made more apparent in gui error messages) that one could flip in there or some hidden sql to flip the db to go. i'm just naively jumping into this and figuring it out, my assumption is some book (a book online maybe) covers this situation (?) or perhaps it is some flakey bug.|||

That's your key.

Restoring a database in norecovery mode tells SQL Server that you intend to apply transaction log backups to roll the database forward before making it available. Thus the restore process is not complete. That's why your database is staying in 'restoring' state.

To bring your database online, simply use the command RESTORE DATABASE <dbname> WITH RECOVERY

You don't need a backup file for this - it just invokes the recovery logic to bring the DB online.

Restoring database in SQL - 7

Hi,
my problem is :-
I have installed SQL - 7 at my home and work. instance name of both SQL is
differ.
I have created mydata.bak file at home and trying to restore at work, but I
am fail .
Any hints will be sincerly appreciated.
Hardik ShahJust taking a stab in the dark, since you have not provided what error
message you are getting. If you are trying to restore using Enterprise
Manager, you must click the Options tab, and check 'force restore over
existing database'. Then, change the location of the data files to match th
e
physical locations on the machine you are restoring to.
"h" wrote:

> Hi,
> my problem is :-
> I have installed SQL - 7 at my home and work. instance name of both SQL is
> differ.
> I have created mydata.bak file at home and trying to restore at work, but
I
> am fail .
> Any hints will be sincerly appreciated.
> Hardik Shah
>
>
>

Restoring database in SQL - 7

Hi,
my problem is :-
I have installed SQL - 7 at my home and work. instance name of both SQL is
differ.
I have created mydata.bak file at home and trying to restore at work, but I
am fail .
Any hints will be sincerly appreciated.
Hardik ShahJust taking a stab in the dark, since you have not provided what error
message you are getting. If you are trying to restore using Enterprise
Manager, you must click the Options tab, and check 'force restore over
existing database'. Then, change the location of the data files to match the
physical locations on the machine you are restoring to.
"h" wrote:
> Hi,
> my problem is :-
> I have installed SQL - 7 at my home and work. instance name of both SQL is
> differ.
> I have created mydata.bak file at home and trying to restore at work, but I
> am fail .
> Any hints will be sincerly appreciated.
> Hardik Shah
>
>
>