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:
>
>
Showing posts with label manager. Show all posts
Showing posts with label manager. Show all posts
Friday, March 30, 2012
restored database LOADING for ever
I restored a 175g database using enterprise manager. It had about 6
transaction logs in the backup set and I checked the point in time restore.
I went back and it said DB restore is complete. But the database icon still
shows (loading). How do I clear the loading status and be able to access an
d
use my Database? Please help as I really need to work tonight. Thanks.
JamesTry executing a restore using the recovery option:
RESTORE DATABASE YourDatabase
WITH RECOVERY
-Sue
On Mon, 28 Feb 2005 20:13:02 -0800, "James Juno"
<JamesJuno@.discussions.microsoft.com> wrote:
>I restored a 175g database using enterprise manager. It had about 6
>transaction logs in the backup set and I checked the point in time restore.
>I went back and it said DB restore is complete. But the database icon stil
l
>shows (loading). How do I clear the loading status and be able to access a
nd
>use my Database? Please help as I really need to work tonight. Thanks.
>James|||Sue,
I did and it helped. Thank you very much
James
"Sue Hoegemeier" wrote:
> Try executing a restore using the recovery option:
> RESTORE DATABASE YourDatabase
> WITH RECOVERY
> -Sue
> On Mon, 28 Feb 2005 20:13:02 -0800, "James Juno"
> <JamesJuno@.discussions.microsoft.com> wrote:
>
>
transaction logs in the backup set and I checked the point in time restore.
I went back and it said DB restore is complete. But the database icon still
shows (loading). How do I clear the loading status and be able to access an
d
use my Database? Please help as I really need to work tonight. Thanks.
JamesTry executing a restore using the recovery option:
RESTORE DATABASE YourDatabase
WITH RECOVERY
-Sue
On Mon, 28 Feb 2005 20:13:02 -0800, "James Juno"
<JamesJuno@.discussions.microsoft.com> wrote:
>I restored a 175g database using enterprise manager. It had about 6
>transaction logs in the backup set and I checked the point in time restore.
>I went back and it said DB restore is complete. But the database icon stil
l
>shows (loading). How do I clear the loading status and be able to access a
nd
>use my Database? Please help as I really need to work tonight. Thanks.
>James|||Sue,
I did and it helped. Thank you very much
James
"Sue Hoegemeier" wrote:
> Try executing a restore using the recovery option:
> RESTORE DATABASE YourDatabase
> WITH RECOVERY
> -Sue
> On Mon, 28 Feb 2005 20:13:02 -0800, "James Juno"
> <JamesJuno@.discussions.microsoft.com> wrote:
>
>
restored database LOADING for ever
I restored a 175g database using enterprise manager. It had about 6
transaction logs in the backup set and I checked the point in time restore.
I went back and it said DB restore is complete. But the database icon still
shows (loading). How do I clear the loading status and be able to access and
use my Database? Please help as I really need to work tonight. Thanks.
JamesTry executing a restore using the recovery option:
RESTORE DATABASE YourDatabase
WITH RECOVERY
-Sue
On Mon, 28 Feb 2005 20:13:02 -0800, "James Juno"
<JamesJuno@.discussions.microsoft.com> wrote:
>I restored a 175g database using enterprise manager. It had about 6
>transaction logs in the backup set and I checked the point in time restore.
>I went back and it said DB restore is complete. But the database icon still
>shows (loading). How do I clear the loading status and be able to access and
>use my Database? Please help as I really need to work tonight. Thanks.
>James|||Sue,
I did and it helped. Thank you very much
James
"Sue Hoegemeier" wrote:
> Try executing a restore using the recovery option:
> RESTORE DATABASE YourDatabase
> WITH RECOVERY
> -Sue
> On Mon, 28 Feb 2005 20:13:02 -0800, "James Juno"
> <JamesJuno@.discussions.microsoft.com> wrote:
> >I restored a 175g database using enterprise manager. It had about 6
> >transaction logs in the backup set and I checked the point in time restore.
> >I went back and it said DB restore is complete. But the database icon still
> >shows (loading). How do I clear the loading status and be able to access and
> >use my Database? Please help as I really need to work tonight. Thanks.
> >
> >James
>sql
transaction logs in the backup set and I checked the point in time restore.
I went back and it said DB restore is complete. But the database icon still
shows (loading). How do I clear the loading status and be able to access and
use my Database? Please help as I really need to work tonight. Thanks.
JamesTry executing a restore using the recovery option:
RESTORE DATABASE YourDatabase
WITH RECOVERY
-Sue
On Mon, 28 Feb 2005 20:13:02 -0800, "James Juno"
<JamesJuno@.discussions.microsoft.com> wrote:
>I restored a 175g database using enterprise manager. It had about 6
>transaction logs in the backup set and I checked the point in time restore.
>I went back and it said DB restore is complete. But the database icon still
>shows (loading). How do I clear the loading status and be able to access and
>use my Database? Please help as I really need to work tonight. Thanks.
>James|||Sue,
I did and it helped. Thank you very much
James
"Sue Hoegemeier" wrote:
> Try executing a restore using the recovery option:
> RESTORE DATABASE YourDatabase
> WITH RECOVERY
> -Sue
> On Mon, 28 Feb 2005 20:13:02 -0800, "James Juno"
> <JamesJuno@.discussions.microsoft.com> wrote:
> >I restored a 175g database using enterprise manager. It had about 6
> >transaction logs in the backup set and I checked the point in time restore.
> >I went back and it said DB restore is complete. But the database icon still
> >shows (loading). How do I clear the loading status and be able to access and
> >use my Database? Please help as I really need to work tonight. Thanks.
> >
> >James
>sql
Restore won't open
I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPrise
Manager, when I right-click on my 2 main databases, choose All
Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
stop it. If I do this for any of the sample databases, the Restore dialogue
opens. I even verified there were no open connections to the 2 databases in
question, & it happens on both servers. I had to do a command line restore.
The backup, etc works fine, just the restore won't open. It did this on SQL
SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
terminated any non-critical services to no avail, i.e. Backup Exec services,
Trend Micro AV, etc.
TedPossibly a LOT of backuphistory to read. Check out the number of rows in the backup history tables
in msdb. Also, read about sp_delete_backuphistory (which takes a long time to run in order to delete
a lot of backup history).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ted Cole" <Ted Cole@.discussions.microsoft.com> wrote in message
news:99032F45-145B-4500-ABCB-9B922BB540E1@.microsoft.com...
>I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPrise
> Manager, when I right-click on my 2 main databases, choose All
> Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
> stop it. If I do this for any of the sample databases, the Restore dialogue
> opens. I even verified there were no open connections to the 2 databases in
> question, & it happens on both servers. I had to do a command line restore.
> The backup, etc works fine, just the restore won't open. It did this on SQL
> SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
> terminated any non-critical services to no avail, i.e. Backup Exec services,
> Trend Micro AV, etc.
> Ted
Manager, when I right-click on my 2 main databases, choose All
Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
stop it. If I do this for any of the sample databases, the Restore dialogue
opens. I even verified there were no open connections to the 2 databases in
question, & it happens on both servers. I had to do a command line restore.
The backup, etc works fine, just the restore won't open. It did this on SQL
SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
terminated any non-critical services to no avail, i.e. Backup Exec services,
Trend Micro AV, etc.
TedPossibly a LOT of backuphistory to read. Check out the number of rows in the backup history tables
in msdb. Also, read about sp_delete_backuphistory (which takes a long time to run in order to delete
a lot of backup history).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ted Cole" <Ted Cole@.discussions.microsoft.com> wrote in message
news:99032F45-145B-4500-ABCB-9B922BB540E1@.microsoft.com...
>I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPrise
> Manager, when I right-click on my 2 main databases, choose All
> Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
> stop it. If I do this for any of the sample databases, the Restore dialogue
> opens. I even verified there were no open connections to the 2 databases in
> question, & it happens on both servers. I had to do a command line restore.
> The backup, etc works fine, just the restore won't open. It did this on SQL
> SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
> terminated any non-critical services to no avail, i.e. Backup Exec services,
> Trend Micro AV, etc.
> Ted
Restore Without msdb.backup Record
How is it possible to restore a DB from .BAK and .TRN
files that aren't listed in the DB restore drop-down lists
in Enterprise Manager?
I've read about the msdb.backupfile and
msdb.backupmediafamily tables and how they are used to
populate the drop-down lists for a DB restore. Is there a
way to tweak those tables to give me access to the files I
need to use in order to do the backup? Is there another
way to restore the database using those files?
Any help or direction is GREATLY appreciated! We are dead
in the water right now...
Thanks in advance!
MicahYou can simply use the RESTORE command in query analyzer. You can view the
contents of the files with the RESTORE FILELISTONLY and RESTORE HEADERONLY
commands. All of these can be found in BooksOnLine.
--
Andrew J. Kelly
SQL Server MVP
"Micah" <anonymous@.discussions.microsoft.com> wrote in message
news:0f9401c3dfb1$41db91a0$a301280a@.phx.gbl...
> How is it possible to restore a DB from .BAK and .TRN
> files that aren't listed in the DB restore drop-down lists
> in Enterprise Manager?
> I've read about the msdb.backupfile and
> msdb.backupmediafamily tables and how they are used to
> populate the drop-down lists for a DB restore. Is there a
> way to tweak those tables to give me access to the files I
> need to use in order to do the backup? Is there another
> way to restore the database using those files?
> Any help or direction is GREATLY appreciated! We are dead
> in the water right now...
> Thanks in advance!
> Micah|||Thank you very much!
It took a little while to get the command just right, but
I was able to do it.
Thanks again,
Micah
>--Original Message--
>You can simply use the RESTORE command in query
analyzer. You can view the
>contents of the files with the RESTORE FILELISTONLY and
RESTORE HEADERONLY
>commands. All of these can be found in BooksOnLine.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Micah" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0f9401c3dfb1$41db91a0$a301280a@.phx.gbl...
>> How is it possible to restore a DB from .BAK and .TRN
>> files that aren't listed in the DB restore drop-down
lists
>> in Enterprise Manager?
>> I've read about the msdb.backupfile and
>> msdb.backupmediafamily tables and how they are used to
>> populate the drop-down lists for a DB restore. Is
there a
>> way to tweak those tables to give me access to the
files I
>> need to use in order to do the backup? Is there another
>> way to restore the database using those files?
>> Any help or direction is GREATLY appreciated! We are
dead
>> in the water right now...
>> Thanks in advance!
>> Micah
>
>.
>|||from EM you can also use the restore from device option and pick the
files from disk.
Micah wrote:
> How is it possible to restore a DB from .BAK and .TRN
> files that aren't listed in the DB restore drop-down lists
> in Enterprise Manager?
> I've read about the msdb.backupfile and
> msdb.backupmediafamily tables and how they are used to
> populate the drop-down lists for a DB restore. Is there a
> way to tweak those tables to give me access to the files I
> need to use in order to do the backup? Is there another
> way to restore the database using those files?
> Any help or direction is GREATLY appreciated! We are dead
> in the water right now...
> Thanks in advance!
> Micah
files that aren't listed in the DB restore drop-down lists
in Enterprise Manager?
I've read about the msdb.backupfile and
msdb.backupmediafamily tables and how they are used to
populate the drop-down lists for a DB restore. Is there a
way to tweak those tables to give me access to the files I
need to use in order to do the backup? Is there another
way to restore the database using those files?
Any help or direction is GREATLY appreciated! We are dead
in the water right now...
Thanks in advance!
MicahYou can simply use the RESTORE command in query analyzer. You can view the
contents of the files with the RESTORE FILELISTONLY and RESTORE HEADERONLY
commands. All of these can be found in BooksOnLine.
--
Andrew J. Kelly
SQL Server MVP
"Micah" <anonymous@.discussions.microsoft.com> wrote in message
news:0f9401c3dfb1$41db91a0$a301280a@.phx.gbl...
> How is it possible to restore a DB from .BAK and .TRN
> files that aren't listed in the DB restore drop-down lists
> in Enterprise Manager?
> I've read about the msdb.backupfile and
> msdb.backupmediafamily tables and how they are used to
> populate the drop-down lists for a DB restore. Is there a
> way to tweak those tables to give me access to the files I
> need to use in order to do the backup? Is there another
> way to restore the database using those files?
> Any help or direction is GREATLY appreciated! We are dead
> in the water right now...
> Thanks in advance!
> Micah|||Thank you very much!
It took a little while to get the command just right, but
I was able to do it.
Thanks again,
Micah
>--Original Message--
>You can simply use the RESTORE command in query
analyzer. You can view the
>contents of the files with the RESTORE FILELISTONLY and
RESTORE HEADERONLY
>commands. All of these can be found in BooksOnLine.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Micah" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0f9401c3dfb1$41db91a0$a301280a@.phx.gbl...
>> How is it possible to restore a DB from .BAK and .TRN
>> files that aren't listed in the DB restore drop-down
lists
>> in Enterprise Manager?
>> I've read about the msdb.backupfile and
>> msdb.backupmediafamily tables and how they are used to
>> populate the drop-down lists for a DB restore. Is
there a
>> way to tweak those tables to give me access to the
files I
>> need to use in order to do the backup? Is there another
>> way to restore the database using those files?
>> Any help or direction is GREATLY appreciated! We are
dead
>> in the water right now...
>> Thanks in advance!
>> Micah
>
>.
>|||from EM you can also use the restore from device option and pick the
files from disk.
Micah wrote:
> How is it possible to restore a DB from .BAK and .TRN
> files that aren't listed in the DB restore drop-down lists
> in Enterprise Manager?
> I've read about the msdb.backupfile and
> msdb.backupmediafamily tables and how they are used to
> populate the drop-down lists for a DB restore. Is there a
> way to tweak those tables to give me access to the files I
> need to use in order to do the backup? Is there another
> way to restore the database using those files?
> Any help or direction is GREATLY appreciated! We are dead
> in the water right now...
> Thanks in advance!
> Micah
Wednesday, March 28, 2012
Restore using... attcahe database.....
When we wanted to restore SQL server (entire server was rebuild) my
colleague restored the database using attach database in the enterprise
manager even though the database was not detached exclusively. We did not
see any data discrepancies. It now makes me feel all my differential and
transactional backups are redundant.
Is it correct to attach the database when it is not exclusively detached?
I am using SQL 2K.
Thanks,
NelsonSometimes it work, sometimes it doesn't. You can search the newsgroup archives for a *bunch* of
times where it evidently didn't work. I.e nothing to count on. Also, BOL clearly states this (need
to be detached to guarantee).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nelson Smith" <NelSmith@.hotmail.com> wrote in message news:udXwZ7dQFHA.3404@.TK2MSFTNGP12.phx.gbl...
> When we wanted to restore SQL server (entire server was rebuild) my
> colleague restored the database using attach database in the enterprise
> manager even though the database was not detached exclusively. We did not
> see any data discrepancies. It now makes me feel all my differential and
> transactional backups are redundant.
> Is it correct to attach the database when it is not exclusively detached?
> I am using SQL 2K.
> Thanks,
> Nelson
>
>
colleague restored the database using attach database in the enterprise
manager even though the database was not detached exclusively. We did not
see any data discrepancies. It now makes me feel all my differential and
transactional backups are redundant.
Is it correct to attach the database when it is not exclusively detached?
I am using SQL 2K.
Thanks,
NelsonSometimes it work, sometimes it doesn't. You can search the newsgroup archives for a *bunch* of
times where it evidently didn't work. I.e nothing to count on. Also, BOL clearly states this (need
to be detached to guarantee).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nelson Smith" <NelSmith@.hotmail.com> wrote in message news:udXwZ7dQFHA.3404@.TK2MSFTNGP12.phx.gbl...
> When we wanted to restore SQL server (entire server was rebuild) my
> colleague restored the database using attach database in the enterprise
> manager even though the database was not detached exclusively. We did not
> see any data discrepancies. It now makes me feel all my differential and
> transactional backups are redundant.
> Is it correct to attach the database when it is not exclusively detached?
> I am using SQL 2K.
> Thanks,
> Nelson
>
>
Monday, March 26, 2012
Restore to different drive
I use Enterprise Manager to backup and restore various SQL Server databases
moving them around on my different dev boxes. I have a case now where a
database is on drive d on one system and it's on drive c on another system.
It won't let me restore to drive C - says I need to use the With Move
option.
I don't see a With Move option in enterprise manager and I don't want to
move a file anyway, I just want to restore it.
Thanks,
T
Technically, the "With Move" option is how SQL implements changing the
restore location of a database. Even if you use Enterprise Mangler to set
up the restores, it is a good idea to read about the BACKUP and RESTORE
commands in BOL (Books On-Line), just so you know what is happening. In
this case, it would have made the error message much clearer.
To use the WITH MOVE option in EM, choose the backup file you want to
restore and any other options that need setting on the General tab. Then
edit the RESTORE AS fields on the Options tab of the restore database popup
to point to the desired file locations.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:evZhdzLrFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I use Enterprise Manager to backup and restore various SQL Server databases
>moving them around on my different dev boxes. I have a case now where a
>database is on drive d on one system and it's on drive c on another system.
>It won't let me restore to drive C - says I need to use the With Move
>option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>
|||Tina,
When the restore dialog box appears, go to the "options tab" an change
"Restore As" for each file. If they are pointing to "D" and the server does
not have "D" drive or the specified folder does not exosts in the "D" drive,
then you have to change this arguments.
AMB
"Tina" wrote:
> I use Enterprise Manager to backup and restore various SQL Server databases
> moving them around on my different dev boxes. I have a case now where a
> database is on drive d on one system and it's on drive c on another system.
> It won't let me restore to drive C - says I need to use the With Move
> option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>
>
sql
moving them around on my different dev boxes. I have a case now where a
database is on drive d on one system and it's on drive c on another system.
It won't let me restore to drive C - says I need to use the With Move
option.
I don't see a With Move option in enterprise manager and I don't want to
move a file anyway, I just want to restore it.
Thanks,
T
Technically, the "With Move" option is how SQL implements changing the
restore location of a database. Even if you use Enterprise Mangler to set
up the restores, it is a good idea to read about the BACKUP and RESTORE
commands in BOL (Books On-Line), just so you know what is happening. In
this case, it would have made the error message much clearer.
To use the WITH MOVE option in EM, choose the backup file you want to
restore and any other options that need setting on the General tab. Then
edit the RESTORE AS fields on the Options tab of the restore database popup
to point to the desired file locations.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:evZhdzLrFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I use Enterprise Manager to backup and restore various SQL Server databases
>moving them around on my different dev boxes. I have a case now where a
>database is on drive d on one system and it's on drive c on another system.
>It won't let me restore to drive C - says I need to use the With Move
>option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>
|||Tina,
When the restore dialog box appears, go to the "options tab" an change
"Restore As" for each file. If they are pointing to "D" and the server does
not have "D" drive or the specified folder does not exosts in the "D" drive,
then you have to change this arguments.
AMB
"Tina" wrote:
> I use Enterprise Manager to backup and restore various SQL Server databases
> moving them around on my different dev boxes. I have a case now where a
> database is on drive d on one system and it's on drive c on another system.
> It won't let me restore to drive C - says I need to use the With Move
> option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>
>
sql
Restore to different drive
I use Enterprise Manager to backup and restore various SQL Server databases
moving them around on my different dev boxes. I have a case now where a
database is on drive d on one system and it's on drive c on another system.
It won't let me restore to drive C - says I need to use the With Move
option.
I don't see a With Move option in enterprise manager and I don't want to
move a file anyway, I just want to restore it.
Thanks,
TTechnically, the "With Move" option is how SQL implements changing the
restore location of a database. Even if you use Enterprise Mangler to set
up the restores, it is a good idea to read about the BACKUP and RESTORE
commands in BOL (Books On-Line), just so you know what is happening. In
this case, it would have made the error message much clearer.
To use the WITH MOVE option in EM, choose the backup file you want to
restore and any other options that need setting on the General tab. Then
edit the RESTORE AS fields on the Options tab of the restore database popup
to point to the desired file locations.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:evZhdzLrFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I use Enterprise Manager to backup and restore various SQL Server databases
>moving them around on my different dev boxes. I have a case now where a
>database is on drive d on one system and it's on drive c on another system.
>It won't let me restore to drive C - says I need to use the With Move
>option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>|||Tina,
When the restore dialog box appears, go to the "options tab" an change
"Restore As" for each file. If they are pointing to "D" and the server does
not have "D" drive or the specified folder does not exosts in the "D" drive,
then you have to change this arguments.
AMB
"Tina" wrote:
> I use Enterprise Manager to backup and restore various SQL Server database
s
> moving them around on my different dev boxes. I have a case now where a
> database is on drive d on one system and it's on drive c on another system
.
> It won't let me restore to drive C - says I need to use the With Move
> option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>
>
moving them around on my different dev boxes. I have a case now where a
database is on drive d on one system and it's on drive c on another system.
It won't let me restore to drive C - says I need to use the With Move
option.
I don't see a With Move option in enterprise manager and I don't want to
move a file anyway, I just want to restore it.
Thanks,
TTechnically, the "With Move" option is how SQL implements changing the
restore location of a database. Even if you use Enterprise Mangler to set
up the restores, it is a good idea to read about the BACKUP and RESTORE
commands in BOL (Books On-Line), just so you know what is happening. In
this case, it would have made the error message much clearer.
To use the WITH MOVE option in EM, choose the backup file you want to
restore and any other options that need setting on the General tab. Then
edit the RESTORE AS fields on the Options tab of the restore database popup
to point to the desired file locations.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:evZhdzLrFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I use Enterprise Manager to backup and restore various SQL Server databases
>moving them around on my different dev boxes. I have a case now where a
>database is on drive d on one system and it's on drive c on another system.
>It won't let me restore to drive C - says I need to use the With Move
>option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>|||Tina,
When the restore dialog box appears, go to the "options tab" an change
"Restore As" for each file. If they are pointing to "D" and the server does
not have "D" drive or the specified folder does not exosts in the "D" drive,
then you have to change this arguments.
AMB
"Tina" wrote:
> I use Enterprise Manager to backup and restore various SQL Server database
s
> moving them around on my different dev boxes. I have a case now where a
> database is on drive d on one system and it's on drive c on another system
.
> It won't let me restore to drive C - says I need to use the With Move
> option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>
>
Restore to different drive
I use Enterprise Manager to backup and restore various SQL Server databases
moving them around on my different dev boxes. I have a case now where a
database is on drive d on one system and it's on drive c on another system.
It won't let me restore to drive C - says I need to use the With Move
option.
I don't see a With Move option in enterprise manager and I don't want to
move a file anyway, I just want to restore it.
Thanks,
TTechnically, the "With Move" option is how SQL implements changing the
restore location of a database. Even if you use Enterprise Mangler to set
up the restores, it is a good idea to read about the BACKUP and RESTORE
commands in BOL (Books On-Line), just so you know what is happening. In
this case, it would have made the error message much clearer.
To use the WITH MOVE option in EM, choose the backup file you want to
restore and any other options that need setting on the General tab. Then
edit the RESTORE AS fields on the Options tab of the restore database popup
to point to the desired file locations.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:evZhdzLrFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I use Enterprise Manager to backup and restore various SQL Server databases
>moving them around on my different dev boxes. I have a case now where a
>database is on drive d on one system and it's on drive c on another system.
>It won't let me restore to drive C - says I need to use the With Move
>option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>|||Tina,
When the restore dialog box appears, go to the "options tab" an change
"Restore As" for each file. If they are pointing to "D" and the server does
not have "D" drive or the specified folder does not exosts in the "D" drive,
then you have to change this arguments.
AMB
"Tina" wrote:
> I use Enterprise Manager to backup and restore various SQL Server databases
> moving them around on my different dev boxes. I have a case now where a
> database is on drive d on one system and it's on drive c on another system.
> It won't let me restore to drive C - says I need to use the With Move
> option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>
>
moving them around on my different dev boxes. I have a case now where a
database is on drive d on one system and it's on drive c on another system.
It won't let me restore to drive C - says I need to use the With Move
option.
I don't see a With Move option in enterprise manager and I don't want to
move a file anyway, I just want to restore it.
Thanks,
TTechnically, the "With Move" option is how SQL implements changing the
restore location of a database. Even if you use Enterprise Mangler to set
up the restores, it is a good idea to read about the BACKUP and RESTORE
commands in BOL (Books On-Line), just so you know what is happening. In
this case, it would have made the error message much clearer.
To use the WITH MOVE option in EM, choose the backup file you want to
restore and any other options that need setting on the General tab. Then
edit the RESTORE AS fields on the Options tab of the restore database popup
to point to the desired file locations.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:evZhdzLrFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I use Enterprise Manager to backup and restore various SQL Server databases
>moving them around on my different dev boxes. I have a case now where a
>database is on drive d on one system and it's on drive c on another system.
>It won't let me restore to drive C - says I need to use the With Move
>option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>|||Tina,
When the restore dialog box appears, go to the "options tab" an change
"Restore As" for each file. If they are pointing to "D" and the server does
not have "D" drive or the specified folder does not exosts in the "D" drive,
then you have to change this arguments.
AMB
"Tina" wrote:
> I use Enterprise Manager to backup and restore various SQL Server databases
> moving them around on my different dev boxes. I have a case now where a
> database is on drive d on one system and it's on drive c on another system.
> It won't let me restore to drive C - says I need to use the With Move
> option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>
>
Monday, March 12, 2012
Restore problem - It takes a long time (loading...)
Hi,
I have set a restore job for a Database using Enterprise Manager. The job
ran quite quickly but the Database now appears on the database list with
(loading) written after it and none of the objects are visible. It has been
sitting there for about n hours. The database file is only about 1,5Mb and
this is the first time is has been restored. Is this kind of time usual? Is
there something else I need to do?
I´m doing this in 2 servers, one for backup and the other one for restore
tks,
edFirst right click on the databases tab and select refresh ( SEM is bad about
not refreshing.)
If the database is still marked as loading, it means that recovery has not
yet run... If you have finished loading all of the logs etc... then to make
the database available run
restore database prod with recovery
from Query Analyzer... The database will now be available...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Edmilson" <troqui@.padtec.com.br> wrote in message
news:#xDTrT1VDHA.2012@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have set a restore job for a Database using Enterprise Manager. The job
> ran quite quickly but the Database now appears on the database list with
> (loading) written after it and none of the objects are visible. It has
been
> sitting there for about n hours. The database file is only about 1,5Mb and
> this is the first time is has been restored. Is this kind of time usual?
Is
> there something else I need to do?
> I´m doing this in 2 servers, one for backup and the other one for restore
> tks,
> ed
>
I have set a restore job for a Database using Enterprise Manager. The job
ran quite quickly but the Database now appears on the database list with
(loading) written after it and none of the objects are visible. It has been
sitting there for about n hours. The database file is only about 1,5Mb and
this is the first time is has been restored. Is this kind of time usual? Is
there something else I need to do?
I´m doing this in 2 servers, one for backup and the other one for restore
tks,
edFirst right click on the databases tab and select refresh ( SEM is bad about
not refreshing.)
If the database is still marked as loading, it means that recovery has not
yet run... If you have finished loading all of the logs etc... then to make
the database available run
restore database prod with recovery
from Query Analyzer... The database will now be available...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Edmilson" <troqui@.padtec.com.br> wrote in message
news:#xDTrT1VDHA.2012@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have set a restore job for a Database using Enterprise Manager. The job
> ran quite quickly but the Database now appears on the database list with
> (loading) written after it and none of the objects are visible. It has
been
> sitting there for about n hours. The database file is only about 1,5Mb and
> this is the first time is has been restored. Is this kind of time usual?
Is
> there something else I need to do?
> I´m doing this in 2 servers, one for backup and the other one for restore
> tks,
> ed
>
Restore Problem
I need to copy a database FSPROD to FSSYS on a different SQL server. In
enterprise manager, I hightlighted the FSPROD and performed a complete
backup and named it FSPROD.bak. After the backup completed, I copied that
file to the server it needs to be restored on.
I didn't have a FSSYS database, but I thought SQL would do it on its own,
so when I did this:
RESTORE DATABASE FSSYS
FROM Disk='F:\FSPROD.bak'
WITH
MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
REPLACE
I got this message: Could not find database ID 65535. Database may not be
activated yet or may be in transition.
I then created a FSSYS database, and went to the Query Analyzer:
RESTORE DATABASE FSSYS
FROM Disk='F:\FSPROD.bak'
WITH
MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
REPLACE
I got this message: The backup set holds a backup of a database other than
the existing 'FSSYS' database.
I then tried created the FSPROD on the second server to use the same name,
as I figured I could ALTER the name later, but I still get: The backup set
holds a backup of a database other than the existing 'FSPROD' database.
If the DB name is the same, why isn't it letting me restore? Is it because
I used the wizard to perform the backup?
Thanks,
RockyWhat happens if you try it the first way (without the database existing)
without the REPLACE statement?
Christian Smith
"Rocky" <me@.me.co> wrote in message
news:Xns948B7C1D62EB3memeco@.207.46.248.16...
> I need to copy a database FSPROD to FSSYS on a different SQL server. In
> enterprise manager, I hightlighted the FSPROD and performed a complete
> backup and named it FSPROD.bak. After the backup completed, I copied that
> file to the server it needs to be restored on.
> I didn't have a FSSYS database, but I thought SQL would do it on its own,
> so when I did this:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: Could not find database ID 65535. Database may not be
> activated yet or may be in transition.
> I then created a FSSYS database, and went to the Query Analyzer:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: The backup set holds a backup of a database other than
> the existing 'FSSYS' database.
> I then tried created the FSPROD on the second server to use the same name,
> as I figured I could ALTER the name later, but I still get: The backup set
> holds a backup of a database other than the existing 'FSPROD' database.
> If the DB name is the same, why isn't it letting me restore? Is it
because
> I used the wizard to perform the backup?
> Thanks,
> Rocky|||I think you made it comnplicated. Let's say you already copied the backup
file to the root of drive F: on the new server. Frist you need to know the
logical file names of FSPROD.
RESTORE FILELISTONLY FROM DISK = 'F:\FSPROD.bak'
it returns logical files name of data file and log file of FSPROD. Use
them for restore command:
RESTORE DATABASE FSSYS FROM DISK = 'F:\FSPROD.bak'
WITH MOVE 'logical file name of data file of FSPROD' TO
'F:\Data\TDSFSSYS_data.mdf',
MOVE 'logical file name of log file of FSPROD' TO
'F:\Log\TDSFSSYS_log.ldf'
You don't need to create the DB first.
hth,
"Rocky" <me@.me.co> wrote in message
news:Xns948B7C1D62EB3memeco@.207.46.248.16...
> I need to copy a database FSPROD to FSSYS on a different SQL server. In
> enterprise manager, I hightlighted the FSPROD and performed a complete
> backup and named it FSPROD.bak. After the backup completed, I copied that
> file to the server it needs to be restored on.
> I didn't have a FSSYS database, but I thought SQL would do it on its own,
> so when I did this:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: Could not find database ID 65535. Database may not be
> activated yet or may be in transition.
> I then created a FSSYS database, and went to the Query Analyzer:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: The backup set holds a backup of a database other than
> the existing 'FSSYS' database.
> I then tried created the FSPROD on the second server to use the same name,
> as I figured I could ALTER the name later, but I still get: The backup set
> holds a backup of a database other than the existing 'FSPROD' database.
> If the DB name is the same, why isn't it letting me restore? Is it
because
> I used the wizard to perform the backup?
> Thanks,
> Rocky|||> What happens if you try it the first way (without the database
> existing) without the REPLACE statement?
Could not find database ID 65535. Database may not be activated yet or may
be in transition.
I'm re-building a backup from a Transact-SQL command, but it is about 50gb
so it will take awhile to copy to the other box. Hopefully it is just an
issue from the backup wizard-thing.
Later,
Rocky|||> I think you made it comnplicated. Let's say you already copied the
> backup file to the root of drive F: on the new server. Frist you need
> to know the logical file names of FSPROD.
> RESTORE FILELISTONLY FROM DISK = 'F:\FSPROD.bak'
> it returns logical files name of data file and log file of FSPROD.
> Use them for restore command:
RESTORE FILELISTONLY FROM DISK = 'F:\FSPROD.bak'
TDSFSSYSdata I:\Data\TDSFSSYS_data.mdf D
TDSFSSYSlog K:\Log\TDSFSSYS_log.ldf L
I was using those names in my move commands in the restore statement.
Later,
Rocky|||> I'm re-building a backup from a Transact-SQL command, but it is about
> 50gb so it will take awhile to copy to the other box. Hopefully it is
> just an issue from the backup wizard-thing.
Worked fine as usual from Query Analyzer.
Thanks for the help,
Rocky
enterprise manager, I hightlighted the FSPROD and performed a complete
backup and named it FSPROD.bak. After the backup completed, I copied that
file to the server it needs to be restored on.
I didn't have a FSSYS database, but I thought SQL would do it on its own,
so when I did this:
RESTORE DATABASE FSSYS
FROM Disk='F:\FSPROD.bak'
WITH
MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
REPLACE
I got this message: Could not find database ID 65535. Database may not be
activated yet or may be in transition.
I then created a FSSYS database, and went to the Query Analyzer:
RESTORE DATABASE FSSYS
FROM Disk='F:\FSPROD.bak'
WITH
MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
REPLACE
I got this message: The backup set holds a backup of a database other than
the existing 'FSSYS' database.
I then tried created the FSPROD on the second server to use the same name,
as I figured I could ALTER the name later, but I still get: The backup set
holds a backup of a database other than the existing 'FSPROD' database.
If the DB name is the same, why isn't it letting me restore? Is it because
I used the wizard to perform the backup?
Thanks,
RockyWhat happens if you try it the first way (without the database existing)
without the REPLACE statement?
Christian Smith
"Rocky" <me@.me.co> wrote in message
news:Xns948B7C1D62EB3memeco@.207.46.248.16...
> I need to copy a database FSPROD to FSSYS on a different SQL server. In
> enterprise manager, I hightlighted the FSPROD and performed a complete
> backup and named it FSPROD.bak. After the backup completed, I copied that
> file to the server it needs to be restored on.
> I didn't have a FSSYS database, but I thought SQL would do it on its own,
> so when I did this:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: Could not find database ID 65535. Database may not be
> activated yet or may be in transition.
> I then created a FSSYS database, and went to the Query Analyzer:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: The backup set holds a backup of a database other than
> the existing 'FSSYS' database.
> I then tried created the FSPROD on the second server to use the same name,
> as I figured I could ALTER the name later, but I still get: The backup set
> holds a backup of a database other than the existing 'FSPROD' database.
> If the DB name is the same, why isn't it letting me restore? Is it
because
> I used the wizard to perform the backup?
> Thanks,
> Rocky|||I think you made it comnplicated. Let's say you already copied the backup
file to the root of drive F: on the new server. Frist you need to know the
logical file names of FSPROD.
RESTORE FILELISTONLY FROM DISK = 'F:\FSPROD.bak'
it returns logical files name of data file and log file of FSPROD. Use
them for restore command:
RESTORE DATABASE FSSYS FROM DISK = 'F:\FSPROD.bak'
WITH MOVE 'logical file name of data file of FSPROD' TO
'F:\Data\TDSFSSYS_data.mdf',
MOVE 'logical file name of log file of FSPROD' TO
'F:\Log\TDSFSSYS_log.ldf'
You don't need to create the DB first.
hth,
"Rocky" <me@.me.co> wrote in message
news:Xns948B7C1D62EB3memeco@.207.46.248.16...
> I need to copy a database FSPROD to FSSYS on a different SQL server. In
> enterprise manager, I hightlighted the FSPROD and performed a complete
> backup and named it FSPROD.bak. After the backup completed, I copied that
> file to the server it needs to be restored on.
> I didn't have a FSSYS database, but I thought SQL would do it on its own,
> so when I did this:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: Could not find database ID 65535. Database may not be
> activated yet or may be in transition.
> I then created a FSSYS database, and went to the Query Analyzer:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: The backup set holds a backup of a database other than
> the existing 'FSSYS' database.
> I then tried created the FSPROD on the second server to use the same name,
> as I figured I could ALTER the name later, but I still get: The backup set
> holds a backup of a database other than the existing 'FSPROD' database.
> If the DB name is the same, why isn't it letting me restore? Is it
because
> I used the wizard to perform the backup?
> Thanks,
> Rocky|||> What happens if you try it the first way (without the database
> existing) without the REPLACE statement?
Could not find database ID 65535. Database may not be activated yet or may
be in transition.
I'm re-building a backup from a Transact-SQL command, but it is about 50gb
so it will take awhile to copy to the other box. Hopefully it is just an
issue from the backup wizard-thing.
Later,
Rocky|||> I think you made it comnplicated. Let's say you already copied the
> backup file to the root of drive F: on the new server. Frist you need
> to know the logical file names of FSPROD.
> RESTORE FILELISTONLY FROM DISK = 'F:\FSPROD.bak'
> it returns logical files name of data file and log file of FSPROD.
> Use them for restore command:
RESTORE FILELISTONLY FROM DISK = 'F:\FSPROD.bak'
TDSFSSYSdata I:\Data\TDSFSSYS_data.mdf D
TDSFSSYSlog K:\Log\TDSFSSYS_log.ldf L
I was using those names in my move commands in the restore statement.
Later,
Rocky|||> I'm re-building a backup from a Transact-SQL command, but it is about
> 50gb so it will take awhile to copy to the other box. Hopefully it is
> just an issue from the backup wizard-thing.
Worked fine as usual from Query Analyzer.
Thanks for the help,
Rocky
Friday, March 9, 2012
Restore Problem
I need to copy a database FSPROD to FSSYS on a different SQL server. In
enterprise manager, I hightlighted the FSPROD and performed a complete
backup and named it FSPROD.bak. After the backup completed, I copied that
file to the server it needs to be restored on.
I didn't have a FSSYS database, but I thought SQL would do it on its own,
so when I did this:
RESTORE DATABASE FSSYS
FROM Disk='F:\FSPROD.bak'
WITH
MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
REPLACE
I got this message: Could not find database ID 65535. Database may not be
activated yet or may be in transition.
I then created a FSSYS database, and went to the Query Analyzer:
RESTORE DATABASE FSSYS
FROM Disk='F:\FSPROD.bak'
WITH
MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
REPLACE
I got this message: The backup set holds a backup of a database other than
the existing 'FSSYS' database.
I then tried created the FSPROD on the second server to use the same name,
as I figured I could ALTER the name later, but I still get: The backup set
holds a backup of a database other than the existing 'FSPROD' database.
If the DB name is the same, why isn't it letting me restore? Is it because
I used the wizard to perform the backup?
Thanks,
RockyWhat happens if you try it the first way (without the database existing)
without the REPLACE statement?
Christian Smith
"Rocky" <me@.me.co> wrote in message
news:Xns948B7C1D62EB3memeco@.207.46.248.16...
> I need to copy a database FSPROD to FSSYS on a different SQL server. In
> enterprise manager, I hightlighted the FSPROD and performed a complete
> backup and named it FSPROD.bak. After the backup completed, I copied that
> file to the server it needs to be restored on.
> I didn't have a FSSYS database, but I thought SQL would do it on its own,
> so when I did this:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: Could not find database ID 65535. Database may not be
> activated yet or may be in transition.
> I then created a FSSYS database, and went to the Query Analyzer:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: The backup set holds a backup of a database other than
> the existing 'FSSYS' database.
> I then tried created the FSPROD on the second server to use the same name,
> as I figured I could ALTER the name later, but I still get: The backup set
> holds a backup of a database other than the existing 'FSPROD' database.
> If the DB name is the same, why isn't it letting me restore? Is it
because
> I used the wizard to perform the backup?
> Thanks,
> Rocky|||I think you made it comnplicated. Let's say you already copied the backup
file to the root of drive F: on the new server. Frist you need to know the
logical file names of FSPROD.
RESTORE FILELISTONLY FROM DISK = 'F:\FSPROD.bak'
it returns logical files name of data file and log file of FSPROD. Use
them for restore command:
RESTORE DATABASE FSSYS FROM DISK = 'F:\FSPROD.bak'
WITH MOVE 'logical file name of data file of FSPROD' TO
'F:\Data\TDSFSSYS_data.mdf',
MOVE 'logical file name of log file of FSPROD' TO
'F:\Log\TDSFSSYS_log.ldf'
You don't need to create the DB first.
hth,
"Rocky" <me@.me.co> wrote in message
news:Xns948B7C1D62EB3memeco@.207.46.248.16...
> I need to copy a database FSPROD to FSSYS on a different SQL server. In
> enterprise manager, I hightlighted the FSPROD and performed a complete
> backup and named it FSPROD.bak. After the backup completed, I copied that
> file to the server it needs to be restored on.
> I didn't have a FSSYS database, but I thought SQL would do it on its own,
> so when I did this:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: Could not find database ID 65535. Database may not be
> activated yet or may be in transition.
> I then created a FSSYS database, and went to the Query Analyzer:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: The backup set holds a backup of a database other than
> the existing 'FSSYS' database.
> I then tried created the FSPROD on the second server to use the same name,
> as I figured I could ALTER the name later, but I still get: The backup set
> holds a backup of a database other than the existing 'FSPROD' database.
> If the DB name is the same, why isn't it letting me restore? Is it
because
> I used the wizard to perform the backup?
> Thanks,
> Rocky|||> What happens if you try it the first way (without the database
> existing) without the REPLACE statement?
Could not find database ID 65535. Database may not be activated yet or may
be in transition.
I'm re-building a backup from a Transact-SQL command, but it is about 50gb
so it will take awhile to copy to the other box. Hopefully it is just an
issue from the backup wizard-thing.
Later,
Rocky|||> I think you made it comnplicated. Let's say you already copied the
> backup file to the root of drive F: on the new server. Frist you need
> to know the logical file names of FSPROD.
> RESTORE FILELISTONLY FROM DISK = 'F:\FSPROD.bak'
> it returns logical files name of data file and log file of FSPROD.
> Use them for restore command:
RESTORE FILELISTONLY FROM DISK = 'F:\FSPROD.bak'
TDSFSSYSdata I:\Data\TDSFSSYS_data.mdf D
TDSFSSYSlog K:\Log\TDSFSSYS_log.ldf L
I was using those names in my move commands in the restore statement.
Later,
Rocky|||> I'm re-building a backup from a Transact-SQL command, but it is about
> 50gb so it will take awhile to copy to the other box. Hopefully it is
> just an issue from the backup wizard-thing.
Worked fine as usual from Query Analyzer.
Thanks for the help,
Rocky
enterprise manager, I hightlighted the FSPROD and performed a complete
backup and named it FSPROD.bak. After the backup completed, I copied that
file to the server it needs to be restored on.
I didn't have a FSSYS database, but I thought SQL would do it on its own,
so when I did this:
RESTORE DATABASE FSSYS
FROM Disk='F:\FSPROD.bak'
WITH
MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
REPLACE
I got this message: Could not find database ID 65535. Database may not be
activated yet or may be in transition.
I then created a FSSYS database, and went to the Query Analyzer:
RESTORE DATABASE FSSYS
FROM Disk='F:\FSPROD.bak'
WITH
MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
REPLACE
I got this message: The backup set holds a backup of a database other than
the existing 'FSSYS' database.
I then tried created the FSPROD on the second server to use the same name,
as I figured I could ALTER the name later, but I still get: The backup set
holds a backup of a database other than the existing 'FSPROD' database.
If the DB name is the same, why isn't it letting me restore? Is it because
I used the wizard to perform the backup?
Thanks,
RockyWhat happens if you try it the first way (without the database existing)
without the REPLACE statement?
Christian Smith
"Rocky" <me@.me.co> wrote in message
news:Xns948B7C1D62EB3memeco@.207.46.248.16...
> I need to copy a database FSPROD to FSSYS on a different SQL server. In
> enterprise manager, I hightlighted the FSPROD and performed a complete
> backup and named it FSPROD.bak. After the backup completed, I copied that
> file to the server it needs to be restored on.
> I didn't have a FSSYS database, but I thought SQL would do it on its own,
> so when I did this:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: Could not find database ID 65535. Database may not be
> activated yet or may be in transition.
> I then created a FSSYS database, and went to the Query Analyzer:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: The backup set holds a backup of a database other than
> the existing 'FSSYS' database.
> I then tried created the FSPROD on the second server to use the same name,
> as I figured I could ALTER the name later, but I still get: The backup set
> holds a backup of a database other than the existing 'FSPROD' database.
> If the DB name is the same, why isn't it letting me restore? Is it
because
> I used the wizard to perform the backup?
> Thanks,
> Rocky|||I think you made it comnplicated. Let's say you already copied the backup
file to the root of drive F: on the new server. Frist you need to know the
logical file names of FSPROD.
RESTORE FILELISTONLY FROM DISK = 'F:\FSPROD.bak'
it returns logical files name of data file and log file of FSPROD. Use
them for restore command:
RESTORE DATABASE FSSYS FROM DISK = 'F:\FSPROD.bak'
WITH MOVE 'logical file name of data file of FSPROD' TO
'F:\Data\TDSFSSYS_data.mdf',
MOVE 'logical file name of log file of FSPROD' TO
'F:\Log\TDSFSSYS_log.ldf'
You don't need to create the DB first.
hth,
"Rocky" <me@.me.co> wrote in message
news:Xns948B7C1D62EB3memeco@.207.46.248.16...
> I need to copy a database FSPROD to FSSYS on a different SQL server. In
> enterprise manager, I hightlighted the FSPROD and performed a complete
> backup and named it FSPROD.bak. After the backup completed, I copied that
> file to the server it needs to be restored on.
> I didn't have a FSSYS database, but I thought SQL would do it on its own,
> so when I did this:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: Could not find database ID 65535. Database may not be
> activated yet or may be in transition.
> I then created a FSSYS database, and went to the Query Analyzer:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: The backup set holds a backup of a database other than
> the existing 'FSSYS' database.
> I then tried created the FSPROD on the second server to use the same name,
> as I figured I could ALTER the name later, but I still get: The backup set
> holds a backup of a database other than the existing 'FSPROD' database.
> If the DB name is the same, why isn't it letting me restore? Is it
because
> I used the wizard to perform the backup?
> Thanks,
> Rocky|||> What happens if you try it the first way (without the database
> existing) without the REPLACE statement?
Could not find database ID 65535. Database may not be activated yet or may
be in transition.
I'm re-building a backup from a Transact-SQL command, but it is about 50gb
so it will take awhile to copy to the other box. Hopefully it is just an
issue from the backup wizard-thing.
Later,
Rocky|||> I think you made it comnplicated. Let's say you already copied the
> backup file to the root of drive F: on the new server. Frist you need
> to know the logical file names of FSPROD.
> RESTORE FILELISTONLY FROM DISK = 'F:\FSPROD.bak'
> it returns logical files name of data file and log file of FSPROD.
> Use them for restore command:
RESTORE FILELISTONLY FROM DISK = 'F:\FSPROD.bak'
TDSFSSYSdata I:\Data\TDSFSSYS_data.mdf D
TDSFSSYSlog K:\Log\TDSFSSYS_log.ldf L
I was using those names in my move commands in the restore statement.
Later,
Rocky|||> I'm re-building a backup from a Transact-SQL command, but it is about
> 50gb so it will take awhile to copy to the other box. Hopefully it is
> just an issue from the backup wizard-thing.
Worked fine as usual from Query Analyzer.
Thanks for the help,
Rocky
Monday, February 20, 2012
restore master and not working the enterprise manager**
Hi
I started sqlserver 2000 as single user mode
and then tried to restore master db,and
it prompted "restoring master was completed successfully.and shut down the
sqlserver to complete."
but after this message the window of restoring remained
on main window and everything seemed to be halted
and I had to end task the enterprise manager and restarted
the computer but when I decided to run enterprise manager
again and to open the branches following error appearred:
"cannot open user default database login failed .pleas verify
sql server is running and check your sql server registration
properties by right clicking on it."
how can I repair this problem?
do I have to reinstall it again?
any help would be greatly thanked.Looks like you restored from an old master backup, and hence the default
database assigned to the login with which you registered the server in
Enterprise Manager is different from what's stored in the backup.
I suggest you go to command prompt, and login to SQL Server using the same
login with which you registered the server in Enterprise Manager (also
specify the default database as master). Here's how you would do it:
OSQL -S ServerNameHere -U LoginNameHere -P PasswordHere -d master
If you are using Windows authentication, then run the following command
instead:
OSQL -S ServerNameHere -E -d master
Once logged into SQL Server, use sp_defaultdb procedure to change the
default database of your login to an exisiting database. Once this is done
successfully, quit OSQL, go back to Enterprise Manager, and now you should
be able to connect fine. You can find more info and examples on sp_defaultdb
in SQL Server Books Online.
Note that if you create new databases before the backup (from which you
restored now) was taken, then you will have to restore or attach those
databases again.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsayj8ju4hqligo@.msnews.microsoft.com...
Hi
I started sqlserver 2000 as single user mode
and then tried to restore master db,and
it prompted "restoring master was completed successfully.and shut down the
sqlserver to complete."
but after this message the window of restoring remained
on main window and everything seemed to be halted
and I had to end task the enterprise manager and restarted
the computer but when I decided to run enterprise manager
again and to open the branches following error appearred:
"cannot open user default database login failed .pleas verify
sql server is running and check your sql server registration
properties by right clicking on it."
how can I repair this problem?
do I have to reinstall it again?
any help would be greatly thanked.
I started sqlserver 2000 as single user mode
and then tried to restore master db,and
it prompted "restoring master was completed successfully.and shut down the
sqlserver to complete."
but after this message the window of restoring remained
on main window and everything seemed to be halted
and I had to end task the enterprise manager and restarted
the computer but when I decided to run enterprise manager
again and to open the branches following error appearred:
"cannot open user default database login failed .pleas verify
sql server is running and check your sql server registration
properties by right clicking on it."
how can I repair this problem?
do I have to reinstall it again?
any help would be greatly thanked.Looks like you restored from an old master backup, and hence the default
database assigned to the login with which you registered the server in
Enterprise Manager is different from what's stored in the backup.
I suggest you go to command prompt, and login to SQL Server using the same
login with which you registered the server in Enterprise Manager (also
specify the default database as master). Here's how you would do it:
OSQL -S ServerNameHere -U LoginNameHere -P PasswordHere -d master
If you are using Windows authentication, then run the following command
instead:
OSQL -S ServerNameHere -E -d master
Once logged into SQL Server, use sp_defaultdb procedure to change the
default database of your login to an exisiting database. Once this is done
successfully, quit OSQL, go back to Enterprise Manager, and now you should
be able to connect fine. You can find more info and examples on sp_defaultdb
in SQL Server Books Online.
Note that if you create new databases before the backup (from which you
restored now) was taken, then you will have to restore or attach those
databases again.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsayj8ju4hqligo@.msnews.microsoft.com...
Hi
I started sqlserver 2000 as single user mode
and then tried to restore master db,and
it prompted "restoring master was completed successfully.and shut down the
sqlserver to complete."
but after this message the window of restoring remained
on main window and everything seemed to be halted
and I had to end task the enterprise manager and restarted
the computer but when I decided to run enterprise manager
again and to open the branches following error appearred:
"cannot open user default database login failed .pleas verify
sql server is running and check your sql server registration
properties by right clicking on it."
how can I repair this problem?
do I have to reinstall it again?
any help would be greatly thanked.
Subscribe to:
Posts (Atom)