Hi Group,
I performed a restore operation on a database. The restore said it was
successful, however the database is in an unusable state. It is greyed out
and just says (Loading) after it. It stays in this status forever. I
restarted SQL Services, and even rebooted the server, which made a few
people cry but they'll get over it.
I also detached the database, since I have a copy of it stored on another
drive, but when I went to re-attach it to the copy, I get a Error 5173
Cannot associate files with different databases. That is all it says.
What am I missing here?
Thanks
CurtSounds like you did not recover the database when you restored it.
Try running the following from Query Analyzer (assuming your database is
called MyDB for example and you still have it in the "loading" state on your
server):
restore database MyDB with recovery
This should bring it online again.
Tony.
"C Fish" <cfish@.qwpi.com> wrote in message
news:D7XQa.1291$cX.480@.newssvr23.news.prodigy.com...
> Hi Group,
> I performed a restore operation on a database. The restore said it was
> successful, however the database is in an unusable state. It is greyed out
> and just says (Loading) after it. It stays in this status forever. I
> restarted SQL Services, and even rebooted the server, which made a few
> people cry but they'll get over it.
> I also detached the database, since I have a copy of it stored on another
> drive, but when I went to re-attach it to the copy, I get a Error 5173
> Cannot associate files with different databases. That is all it says.
> What am I missing here?
> Thanks
> Curt
>|||Hi Tony,
Well that got me this far
Server: Msg 913, Level 16, State 8, Line 1
Could not find database ID 65535. Database may not be activated yet or may
be in transition.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Does this mean anything to you' The database is on the list in enterprise
manager, however it is gray whereas the other databases are yellow and it
has (Loading) after the name in parenthesis. It does not however show up in
ISQL list nor the list in SQL Query Analyzer. FYI this is SQL Server 2000
with SP3 and the Backup set is from Veritas Back Up Exec ver 8.5 with SQL
Server plug in. I have restored before without any issues. I followed the
same procedures as I have in the past, but this time I am feeling like I'm
in some trouble.
Curt
"Tony Lewis" <tony.lewis@.unspam_dowcorning.com> wrote in message
news:OMHhz1vSDHA.2084@.TK2MSFTNGP11.phx.gbl...
> Sounds like you did not recover the database when you restored it.
> Try running the following from Query Analyzer (assuming your database is
> called MyDB for example and you still have it in the "loading" state on
your
> server):
> restore database MyDB with recovery
> This should bring it online again.
> Tony.
> "C Fish" <cfish@.qwpi.com> wrote in message
> news:D7XQa.1291$cX.480@.newssvr23.news.prodigy.com...
> > Hi Group,
> >
> > I performed a restore operation on a database. The restore said it was
> > successful, however the database is in an unusable state. It is greyed
out
> > and just says (Loading) after it. It stays in this status forever. I
> > restarted SQL Services, and even rebooted the server, which made a few
> > people cry but they'll get over it.
> >
> > I also detached the database, since I have a copy of it stored on
another
> > drive, but when I went to re-attach it to the copy, I get a Error 5173
> > Cannot associate files with different databases. That is all it says.
> >
> > What am I missing here?
> >
> > Thanks
> >
> > Curt
> >
> >
>
>
Showing posts with label performed. Show all posts
Showing posts with label performed. Show all posts
Friday, March 23, 2012
Tuesday, March 20, 2012
restore question
I have SQL2000 server with 300 databases. I have a maintenance plan that
performs a full backup of all databases every night.
I have performed test restores of some of those databases but now I'm
wondering in case that something catastrophic happens and I have to restore
entire SQL server, how can I do that?
Would I have to restore every database separately or is there a way to
restore all of them in one step (some transact SQL script ...)?
Tom
Hi,
Best option is to restore the system databases first and then restore the
User databases one by one. You could write the RESTORE DATABASE
Script and execute it from Query Analyzer in a single execution.
See the below URLS for the step by step approach with different
methodologies:-
http://support.microsoft.com/default...b;en-us;304692
http://support.microsoft.com/default...b;en-us;224071
http://support.microsoft.com/default...b;en-us;314546
Thanks
Hari
SQL Server MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:%23XIEZ1VjFHA.2904@.tk2msftngp13.phx.gbl...
>I have SQL2000 server with 300 databases. I have a maintenance plan that
>performs a full backup of all databases every night.
> I have performed test restores of some of those databases but now I'm
> wondering in case that something catastrophic happens and I have to
> restore entire SQL server, how can I do that?
> Would I have to restore every database separately or is there a way to
> restore all of them in one step (some transact SQL script ...)?
> Tom
>
|||Hi Tom,
This gives you an opportunity to move some of the databases to different
servers, to mitigate the risk of running all the databases on the same server.
Also make sure that you are running the server on disk array's, to avoid any
disk failures.
For avoid dependency on OS, you can look at fail-over clusterting also.
- - - - - - - - -
Thanks
Yogish
"Tom" wrote:
> I have SQL2000 server with 300 databases. I have a maintenance plan that
> performs a full backup of all databases every night.
> I have performed test restores of some of those databases but now I'm
> wondering in case that something catastrophic happens and I have to restore
> entire SQL server, how can I do that?
> Would I have to restore every database separately or is there a way to
> restore all of them in one step (some transact SQL script ...)?
> Tom
>
>
performs a full backup of all databases every night.
I have performed test restores of some of those databases but now I'm
wondering in case that something catastrophic happens and I have to restore
entire SQL server, how can I do that?
Would I have to restore every database separately or is there a way to
restore all of them in one step (some transact SQL script ...)?
Tom
Hi,
Best option is to restore the system databases first and then restore the
User databases one by one. You could write the RESTORE DATABASE
Script and execute it from Query Analyzer in a single execution.
See the below URLS for the step by step approach with different
methodologies:-
http://support.microsoft.com/default...b;en-us;304692
http://support.microsoft.com/default...b;en-us;224071
http://support.microsoft.com/default...b;en-us;314546
Thanks
Hari
SQL Server MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:%23XIEZ1VjFHA.2904@.tk2msftngp13.phx.gbl...
>I have SQL2000 server with 300 databases. I have a maintenance plan that
>performs a full backup of all databases every night.
> I have performed test restores of some of those databases but now I'm
> wondering in case that something catastrophic happens and I have to
> restore entire SQL server, how can I do that?
> Would I have to restore every database separately or is there a way to
> restore all of them in one step (some transact SQL script ...)?
> Tom
>
|||Hi Tom,
This gives you an opportunity to move some of the databases to different
servers, to mitigate the risk of running all the databases on the same server.
Also make sure that you are running the server on disk array's, to avoid any
disk failures.
For avoid dependency on OS, you can look at fail-over clusterting also.
- - - - - - - - -
Thanks
Yogish
"Tom" wrote:
> I have SQL2000 server with 300 databases. I have a maintenance plan that
> performs a full backup of all databases every night.
> I have performed test restores of some of those databases but now I'm
> wondering in case that something catastrophic happens and I have to restore
> entire SQL server, how can I do that?
> Would I have to restore every database separately or is there a way to
> restore all of them in one step (some transact SQL script ...)?
> Tom
>
>
restore question
I have SQL2000 server with 300 databases. I have a maintenance plan that
performs a full backup of all databases every night.
I have performed test restores of some of those databases but now I'm
wondering in case that something catastrophic happens and I have to restore
entire SQL server, how can I do that?
Would I have to restore every database separately or is there a way to
restore all of them in one step (some transact SQL script ...)?
TomHi,
Best option is to restore the system databases first and then restore the
User databases one by one. You could write the RESTORE DATABASE
Script and execute it from Query Analyzer in a single execution.
See the below URLS for the step by step approach with different
methodologies:-
http://support.microsoft.com/default.aspx?scid=kb;en-us;304692
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
Thanks
Hari
SQL Server MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:%23XIEZ1VjFHA.2904@.tk2msftngp13.phx.gbl...
>I have SQL2000 server with 300 databases. I have a maintenance plan that
>performs a full backup of all databases every night.
> I have performed test restores of some of those databases but now I'm
> wondering in case that something catastrophic happens and I have to
> restore entire SQL server, how can I do that?
> Would I have to restore every database separately or is there a way to
> restore all of them in one step (some transact SQL script ...)?
> Tom
>|||Hi Tom,
This gives you an opportunity to move some of the databases to different
servers, to mitigate the risk of running all the databases on the same server.
Also make sure that you are running the server on disk array's, to avoid any
disk failures.
For avoid dependency on OS, you can look at fail-over clusterting also.
--
- - - - - - - - -
Thanks
Yogish
"Tom" wrote:
> I have SQL2000 server with 300 databases. I have a maintenance plan that
> performs a full backup of all databases every night.
> I have performed test restores of some of those databases but now I'm
> wondering in case that something catastrophic happens and I have to restore
> entire SQL server, how can I do that?
> Would I have to restore every database separately or is there a way to
> restore all of them in one step (some transact SQL script ...)?
> Tom
>
>
performs a full backup of all databases every night.
I have performed test restores of some of those databases but now I'm
wondering in case that something catastrophic happens and I have to restore
entire SQL server, how can I do that?
Would I have to restore every database separately or is there a way to
restore all of them in one step (some transact SQL script ...)?
TomHi,
Best option is to restore the system databases first and then restore the
User databases one by one. You could write the RESTORE DATABASE
Script and execute it from Query Analyzer in a single execution.
See the below URLS for the step by step approach with different
methodologies:-
http://support.microsoft.com/default.aspx?scid=kb;en-us;304692
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
Thanks
Hari
SQL Server MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:%23XIEZ1VjFHA.2904@.tk2msftngp13.phx.gbl...
>I have SQL2000 server with 300 databases. I have a maintenance plan that
>performs a full backup of all databases every night.
> I have performed test restores of some of those databases but now I'm
> wondering in case that something catastrophic happens and I have to
> restore entire SQL server, how can I do that?
> Would I have to restore every database separately or is there a way to
> restore all of them in one step (some transact SQL script ...)?
> Tom
>|||Hi Tom,
This gives you an opportunity to move some of the databases to different
servers, to mitigate the risk of running all the databases on the same server.
Also make sure that you are running the server on disk array's, to avoid any
disk failures.
For avoid dependency on OS, you can look at fail-over clusterting also.
--
- - - - - - - - -
Thanks
Yogish
"Tom" wrote:
> I have SQL2000 server with 300 databases. I have a maintenance plan that
> performs a full backup of all databases every night.
> I have performed test restores of some of those databases but now I'm
> wondering in case that something catastrophic happens and I have to restore
> entire SQL server, how can I do that?
> Would I have to restore every database separately or is there a way to
> restore all of them in one step (some transact SQL script ...)?
> Tom
>
>
restore question
I have SQL2000 server with 300 databases. I have a maintenance plan that
performs a full backup of all databases every night.
I have performed test restores of some of those databases but now I'm
wondering in case that something catastrophic happens and I have to restore
entire SQL server, how can I do that?
Would I have to restore every database separately or is there a way to
restore all of them in one step (some transact SQL script ...)?
TomHi,
Best option is to restore the system databases first and then restore the
User databases one by one. You could write the RESTORE DATABASE
Script and execute it from Query Analyzer in a single execution.
See the below URLS for the step by step approach with different
methodologies:-
http://support.microsoft.com/defaul...kb;en-us;304692
http://support.microsoft.com/defaul...kb;en-us;224071
http://support.microsoft.com/defaul...kb;en-us;314546
Thanks
Hari
SQL Server MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:%23XIEZ1VjFHA.2904@.tk2msftngp13.phx.gbl...
>I have SQL2000 server with 300 databases. I have a maintenance plan that
>performs a full backup of all databases every night.
> I have performed test restores of some of those databases but now I'm
> wondering in case that something catastrophic happens and I have to
> restore entire SQL server, how can I do that?
> Would I have to restore every database separately or is there a way to
> restore all of them in one step (some transact SQL script ...)?
> Tom
>|||Hi Tom,
This gives you an opportunity to move some of the databases to different
servers, to mitigate the risk of running all the databases on the same serve
r.
Also make sure that you are running the server on disk array's, to avoid any
disk failures.
For avoid dependency on OS, you can look at fail-over clusterting also.
--
- - - - - - - - -
Thanks
Yogish
"Tom" wrote:
> I have SQL2000 server with 300 databases. I have a maintenance plan that
> performs a full backup of all databases every night.
> I have performed test restores of some of those databases but now I'm
> wondering in case that something catastrophic happens and I have to restor
e
> entire SQL server, how can I do that?
> Would I have to restore every database separately or is there a way to
> restore all of them in one step (some transact SQL script ...)?
> Tom
>
>
performs a full backup of all databases every night.
I have performed test restores of some of those databases but now I'm
wondering in case that something catastrophic happens and I have to restore
entire SQL server, how can I do that?
Would I have to restore every database separately or is there a way to
restore all of them in one step (some transact SQL script ...)?
TomHi,
Best option is to restore the system databases first and then restore the
User databases one by one. You could write the RESTORE DATABASE
Script and execute it from Query Analyzer in a single execution.
See the below URLS for the step by step approach with different
methodologies:-
http://support.microsoft.com/defaul...kb;en-us;304692
http://support.microsoft.com/defaul...kb;en-us;224071
http://support.microsoft.com/defaul...kb;en-us;314546
Thanks
Hari
SQL Server MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:%23XIEZ1VjFHA.2904@.tk2msftngp13.phx.gbl...
>I have SQL2000 server with 300 databases. I have a maintenance plan that
>performs a full backup of all databases every night.
> I have performed test restores of some of those databases but now I'm
> wondering in case that something catastrophic happens and I have to
> restore entire SQL server, how can I do that?
> Would I have to restore every database separately or is there a way to
> restore all of them in one step (some transact SQL script ...)?
> Tom
>|||Hi Tom,
This gives you an opportunity to move some of the databases to different
servers, to mitigate the risk of running all the databases on the same serve
r.
Also make sure that you are running the server on disk array's, to avoid any
disk failures.
For avoid dependency on OS, you can look at fail-over clusterting also.
--
- - - - - - - - -
Thanks
Yogish
"Tom" wrote:
> I have SQL2000 server with 300 databases. I have a maintenance plan that
> performs a full backup of all databases every night.
> I have performed test restores of some of those databases but now I'm
> wondering in case that something catastrophic happens and I have to restor
e
> entire SQL server, how can I do that?
> Would I have to restore every database separately or is there a way to
> restore all of them in one step (some transact SQL script ...)?
> Tom
>
>
Monday, March 12, 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
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
Subscribe to:
Posts (Atom)