Restore SP

How to you restore SP from one db to another? I tried a
backup restore and get the message "you can't over write
the .mdf file?What do you mean by "SP"? Stored procedure? If so, just generate scrip from
the existing database and apply that script on the destination database.
--
Tibor Karaszi
"Shelley" <anonymous@.discussions.microsoft.com> wrote in message
news:0f2101c3a3a8$bea16fb0$a101280a@.phx.gbl...
> How to you restore SP from one db to another? I tried a
> backup restore and get the message "you can't over write
> the .mdf file?

restore single table

Hi All ,
Is it possible to restore just a single table from a particulare db's backup ?
kindly advise
tks & rdgs
--
Message posted via http://www.sqlmonster.comHi ,
I believe i can put that particular table into its own file group and
therefore, if i restore using file group i am just restoring that particular
table
am i correct on the above ?
and is thre anyother solution to this ?
tks & rdgs
maxzsim wrote:
>Hi All ,
>Is it possible to restore just a single table from a particulare db's backup ?
>kindly advise
>tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Such a restore has to be done into a new database, and using the PARTIAL option of the RESTORE
command. There are other restrictions as well, see the RESTORE command and the PARTICL option in
Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message news:5fb1558785963@.uwe...
> Hi ,
> I believe i can put that particular table into its own file group and
> therefore, if i restore using file group i am just restoring that particular
> table
> am i correct on the above ?
> and is thre anyother solution to this ?
> tks & rdgs
> maxzsim wrote:
>>Hi All ,
>>Is it possible to restore just a single table from a particulare db's backup ?
>>kindly advise
>>tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Alternatively you could restore the entire database to a database of another
name then use TABLEDIFF
(http://www.replicationanswers.com/TableDiff2005.asp) or Redgate's
Datacompare to create the synchronization script.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||tks ppl . basically i want to backup/restore certain important table fast as
the whole db is quite huge
Paul Ibison wrote:
>Alternatively you could restore the entire database to a database of another
>name then use TABLEDIFF
>(http://www.replicationanswers.com/TableDiff2005.asp) or Redgate's
>Datacompare to create the synchronization script.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1

Restore ReportServer

I keep getting this message when i try and restore the ReportServer "system.data.sqlclient.sqlerror: Exclusive access could not be obtained because the database in in use"

All the other databases restored successfully.

Any ideas?
just check if you are accidentally using the db in the management studio.....proceed as follows,
use master
go
then perform the restoration as usual....cool
fyi
http://technet.microsoft.com/en-us/library/ms156421.aspx

restore replicated databases

I can't restore trans replicated databases because I get message that they
are in use - do I need to stop agents to get exclusive access - then restore
- then restart agents?
Thanks
yes, stop Sql Server agent, run sp_who2 to identify and kill spids that are
continuing to use the db and then retry your restore.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DrBob" <DrBob@.discussions.microsoft.com> wrote in message
news:F2DC7A4C-D2DB-493B-BBDB-6BBBF95306CE@.microsoft.com...
> I can't restore trans replicated databases because I get message that they
> are in use - do I need to stop agents to get exclusive access - then
restore
> - then restart agents?
> Thanks
|||Thanks
"Hilary Cotter" wrote:

> yes, stop Sql Server agent, run sp_who2 to identify and kill spids that are
> continuing to use the db and then retry your restore.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "DrBob" <DrBob@.discussions.microsoft.com> wrote in message
> news:F2DC7A4C-D2DB-493B-BBDB-6BBBF95306CE@.microsoft.com...
> restore
>
>

Restore problem - anyone seen this error before?

I am having a problem restoring a database (SQL2000).
from a .bak file device. When I attempt a restore I get
the following message
"The media set for database xxx has 3 family members but
only 1 are provided. All memebers must be provided.
Restore database is terminating abnormally"
The .bak file has been zipped and unzipped with winzip9.
Could this have corrupted the file?
Regards
Neil Gibson
ngibson(revovethis)@.mclgroup.co.ukI think you may have made the database backup to multiple files. You need
to find the other 2 backup files to be able to restore the database.
Look in the msdb..backupmediafamily table on the server you made the backup
to find the names of the other 2 files if you don't know them.
<plug> If you need small backups, give MiniSQLBackup a spin. </plug>
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"Neil Gibson" <anonymous@.discussions.microsoft.com> wrote in message
news:17b8a01c4494d$a81ed980$a001280a@.phx
.gbl...
> I am having a problem restoring a database (SQL2000).
> from a .bak file device. When I attempt a restore I get
> the following message
> "The media set for database xxx has 3 family members but
> only 1 are provided. All memebers must be provided.
> Restore database is terminating abnormally"
> The .bak file has been zipped and unzipped with winzip9.
> Could this have corrupted the file?
> Regards
> Neil Gibson
> ngibson(revovethis)@.mclgroup.co.uk

Restore problem - anyone seen this error before?

I am having a problem restoring a database (SQL2000).
from a .bak file device. When I attempt a restore I get
the following message
"The media set for database xxx has 3 family members but
only 1 are provided. All memebers must be provided.
Restore database is terminating abnormally"
The .bak file has been zipped and unzipped with winzip9.
Could this have corrupted the file?
Regards
Neil Gibson
ngibson(revovethis)@.mclgroup.co.uk
I think you may have made the database backup to multiple files. You need
to find the other 2 backup files to be able to restore the database.
Look in the msdb..backupmediafamily table on the server you made the backup
to find the names of the other 2 files if you don't know them.
<plug> If you need small backups, give MiniSQLBackup a spin. </plug>
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"Neil Gibson" <anonymous@.discussions.microsoft.com> wrote in message
news:17b8a01c4494d$a81ed980$a001280a@.phx.gbl...
> I am having a problem restoring a database (SQL2000).
> from a .bak file device. When I attempt a restore I get
> the following message
> "The media set for database xxx has 3 family members but
> only 1 are provided. All memebers must be provided.
> Restore database is terminating abnormally"
> The .bak file has been zipped and unzipped with winzip9.
> Could this have corrupted the file?
> Regards
> Neil Gibson
> ngibson(revovethis)@.mclgroup.co.uk

Restore problem - anyone seen this error before?

I am having a problem restoring a database (SQL2000).
from a .bak file device. When I attempt a restore I get
the following message
"The media set for database xxx has 3 family members but
only 1 are provided. All memebers must be provided.
Restore database is terminating abnormally"
The .bak file has been zipped and unzipped with winzip9.
Could this have corrupted the file?
Regards
Neil Gibson
ngibson(revovethis)@.mclgroup.co.ukI think you may have made the database backup to multiple files. You need
to find the other 2 backup files to be able to restore the database.
Look in the msdb..backupmediafamily table on the server you made the backup
to find the names of the other 2 files if you don't know them.
<plug> If you need small backups, give MiniSQLBackup a spin. </plug>
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"Neil Gibson" <anonymous@.discussions.microsoft.com> wrote in message
news:17b8a01c4494d$a81ed980$a001280a@.phx.gbl...
> I am having a problem restoring a database (SQL2000).
> from a .bak file device. When I attempt a restore I get
> the following message
> "The media set for database xxx has 3 family members but
> only 1 are provided. All memebers must be provided.
> Restore database is terminating abnormally"
> The .bak file has been zipped and unzipped with winzip9.
> Could this have corrupted the file?
> Regards
> Neil Gibson
> ngibson(revovethis)@.mclgroup.co.uk

Restore Problem

Hi Guys:
I am restoring a databse backup from sql server 2000 cluster to another
server. The restore has been failing with message "RESTORE DATABASE is
terminating abnormally.". When I use restore verify, i get the following
message. Backup is being done on sql server 200 cluster, I am restoring on
another server which is not a cluster. Any help is greatly appreciated
because this is critical and urgent.
Server: Msg 3242, Level 16, State 2, Line 1
The file on device 'C:\temp\LancomeSIS_US_db_200603240243.BAK' is not a
valid Microsoft Tape Format backup set.
Server: Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.
Thanks
Are you on Windows 2003?
> Hi Guys:
> I am restoring a databse backup from sql server 2000 cluster to another
> server. The restore has been failing with message "RESTORE DATABASE is
> terminating abnormally.". When I use restore verify, i get the following
> message. Backup is being done on sql server 200 cluster, I am restoring on
> another server which is not a cluster. Any help is greatly appreciated
> because this is critical and urgent.
>
> Server: Msg 3242, Level 16, State 2, Line 1
> The file on device 'C:\temp\LancomeSIS_US_db_200603240243.BAK' is not a
> valid Microsoft Tape Format backup set.
> Server: Msg 3013, Level 16, State 1, Line 1
> VERIFY DATABASE is terminating abnormally.
>
> Thanks
|||could you paste the output of restore headeronly
|||looks like your backup is corrupted, not sure of yet!
|||Thanks for replying. Below is the output of the Restore HeaderOnly. Looks
looks like db is corrupt but why. is it something to do with backups
happening on cluster (Raid drive).
BackupName
BackupDescription
BackupType ExpirationDate Compressed
Position DeviceType UserName
ServerName
DatabaseName
DatabaseVersion DatabaseCreationDate
BackupSize FirstLsn LastLsn
CheckpointLsn DifferentialBaseLsn
BackupStartDate BackupFinishDate
SortOrder CodePage UnicodeLocaleId
UnicodeComparisonStyle CompatibilityLevel SoftwareVendorId
SoftwareVersionMajor SoftwareVersionMinor SoftwareVersionBuild MachineName
Flags BindingId
RecoveryForkId Collation
------
-----------
-- --- --
-- --
------
------
------
-- ---
-- --
-- --
--- -- --
-- -- -- --
-- -- --
------
-- ---
------
*** INCOMPLETE ***
NULL
NULL NULL NULL
1 NULL NULL
NULL
NULL
NULL NULL
NULL NULL NULL
NULL NULL NULL
NULL
NULL NULL NULL NULL
NULL NULL NULL NULL
NULL NULL
NULL NULL NULL
NULL
"Shadow" wrote:

> looks like your backup is corrupted, not sure of yet!
>

Restore Problem

Hi everyone. I am trying to restore a database and continue to get this
error message. Can anyone help?
The backup works fine. The back is from a primary server and the restore is
on the secondary. I wish to restore in standby.
Here is the code and the error msg. any help is appricated.
RESTORE DATABASE HSP_FMH
FROM DISK='\\Dbprod01\BACKUP\HSP_FMH\HSP_FMH.Backup.Dev ice.BAK'
WITH
MOVE 'HSP_FMH_Data' TO 'G:\Program Files\Microsoft SQL
Server\MSSQL\Data\HSP_FMH_Data.MDF',
MOVE 'HSP_FMH_Log' TO 'G:\Program Files\Microsoft SQL
Server\MSSQL\Data\HSP_FMH_log.LDF',
STANDBY='\\Recover01\LogShipping\DBPROD02\UndoTran sactionLog\HSP_FMH_UNDO.DAT'
[Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL Server.
Connection may have been terminated by the server.
Server: Msg 3204, Level 16, State 1, Line 1
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Connection Broken
Hi
Check the SQL Event Log for any events at the time. SQL Server might be
terminating the SPID due to a corrupt backup or other internal error.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tom" <tbrackney@.kc.rr.com> wrote in message
news:amKWd.1085$Zm4.566@.twister.rdc-kc.rr.com...
> Hi everyone. I am trying to restore a database and continue to get this
> error message. Can anyone help?
> The backup works fine. The back is from a primary server and the restore
is
> on the secondary. I wish to restore in standby.
> Here is the code and the error msg. any help is appricated.
> RESTORE DATABASE HSP_FMH
> FROM DISK='\\Dbprod01\BACKUP\HSP_FMH\HSP_FMH.Backup.Dev ice.BAK'
> WITH
> MOVE 'HSP_FMH_Data' TO 'G:\Program Files\Microsoft SQL
> Server\MSSQL\Data\HSP_FMH_Data.MDF',
> MOVE 'HSP_FMH_Log' TO 'G:\Program Files\Microsoft SQL
> Server\MSSQL\Data\HSP_FMH_log.LDF',
>
STANDBY='\\Recover01\LogShipping\DBPROD02\UndoTran sactionLog\HSP_FMH_UNDO.DA
T'
> [Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL
Server.
> Connection may have been terminated by the server.
> Server: Msg 3204, Level 16, State 1, Line 1
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Connection Broken
>
|||Well, I did that and it is not givning me any information. I have tried this
several times witht he same result. Any other ideas? Anyonw else have an
idea?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ehlM$RpIFHA.1096@.tk2msftngp13.phx.gbl...
> Hi
> Check the SQL Event Log for any events at the time. SQL Server might be
> terminating the SPID due to a corrupt backup or other internal error.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Tom" <tbrackney@.kc.rr.com> wrote in message
> news:amKWd.1085$Zm4.566@.twister.rdc-kc.rr.com...
> is
> STANDBY='\\Recover01\LogShipping\DBPROD02\UndoTran sactionLog\HSP_FMH_UNDO.DA
> T'
> Server.
>
|||Yes. We've come across this several times. It has to do with slow disks
and network latency mixed with the use of UNC path network shares.
Normally, we only have trouble with the create files. So, we create our
backups locally and then use UNC to "PULL" the backup to the restore
location, just as you are currently doing.
However, I suspect that it is the creation of your UNDO.DAT file for the
Standby option. I would also use a local drive for that file, say the local
MSSQL\JOBS folder.
Go ahead and use the UNC to locate the backup file, but your MOVE commands,
and UNDO files should be local drive letters.
Good luck. Let us know how it works.
Sincerely,
Anthony Thomas

"Tom" <tbrackney@.kc.rr.com> wrote in message
news:a_KWd.18954$3V3.14955@.twister.rdc-kc.rr.com...
Well, I did that and it is not givning me any information. I have tried this
several times witht he same result. Any other ideas? Anyonw else have an
idea?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ehlM$RpIFHA.1096@.tk2msftngp13.phx.gbl...
> Hi
> Check the SQL Event Log for any events at the time. SQL Server might be
> terminating the SPID due to a corrupt backup or other internal error.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Tom" <tbrackney@.kc.rr.com> wrote in message
> news:amKWd.1085$Zm4.566@.twister.rdc-kc.rr.com...
> is
>
STANDBY='\\Recover01\LogShipping\DBPROD02\UndoTran sactionLog\HSP_FMH_UNDO.DA
> T'
> Server.
>

Restore Problem

Hi everyone. I am trying to restore a database and continue to get this
error message. Can anyone help?
The backup works fine. The back is from a primary server and the restore is
on the secondary. I wish to restore in standby.
Here is the code and the error msg. any help is appricated.
RESTORE DATABASE HSP_FMH
FROM DISK='\\Dbprod01\BACKUP\HSP_FMH\HSP_FMH.Backup.Device.BAK'
WITH
MOVE 'HSP_FMH_Data' TO 'G:\Program Files\Microsoft SQL
Server\MSSQL\Data\HSP_FMH_Data.MDF',
MOVE 'HSP_FMH_Log' TO 'G:\Program Files\Microsoft SQL
Server\MSSQL\Data\HSP_FMH_log.LDF',
STANDBY='\\Recover01\LogShipping\DBPROD02\UndoTransactionLog\HSP_FMH_UNDO.DAT'
[Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL Server.
Connection may have been terminated by the server.
Server: Msg 3204, Level 16, State 1, Line 1
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Connection BrokenHi
Check the SQL Event Log for any events at the time. SQL Server might be
terminating the SPID due to a corrupt backup or other internal error.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tom" <tbrackney@.kc.rr.com> wrote in message
news:amKWd.1085$Zm4.566@.twister.rdc-kc.rr.com...
> Hi everyone. I am trying to restore a database and continue to get this
> error message. Can anyone help?
> The backup works fine. The back is from a primary server and the restore
is
> on the secondary. I wish to restore in standby.
> Here is the code and the error msg. any help is appricated.
> RESTORE DATABASE HSP_FMH
> FROM DISK='\\Dbprod01\BACKUP\HSP_FMH\HSP_FMH.Backup.Device.BAK'
> WITH
> MOVE 'HSP_FMH_Data' TO 'G:\Program Files\Microsoft SQL
> Server\MSSQL\Data\HSP_FMH_Data.MDF',
> MOVE 'HSP_FMH_Log' TO 'G:\Program Files\Microsoft SQL
> Server\MSSQL\Data\HSP_FMH_log.LDF',
>
STANDBY='\\Recover01\LogShipping\DBPROD02\UndoTransactionLog\HSP_FMH_UNDO.DA
T'
> [Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL
Server.
> Connection may have been terminated by the server.
> Server: Msg 3204, Level 16, State 1, Line 1
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Connection Broken
>|||Well, I did that and it is not givning me any information. I have tried this
several times witht he same result. Any other ideas? Anyonw else have an
idea?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ehlM$RpIFHA.1096@.tk2msftngp13.phx.gbl...
> Hi
> Check the SQL Event Log for any events at the time. SQL Server might be
> terminating the SPID due to a corrupt backup or other internal error.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Tom" <tbrackney@.kc.rr.com> wrote in message
> news:amKWd.1085$Zm4.566@.twister.rdc-kc.rr.com...
>> Hi everyone. I am trying to restore a database and continue to get this
>> error message. Can anyone help?
>> The backup works fine. The back is from a primary server and the restore
> is
>> on the secondary. I wish to restore in standby.
>> Here is the code and the error msg. any help is appricated.
>> RESTORE DATABASE HSP_FMH
>> FROM DISK='\\Dbprod01\BACKUP\HSP_FMH\HSP_FMH.Backup.Device.BAK'
>> WITH
>> MOVE 'HSP_FMH_Data' TO 'G:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\HSP_FMH_Data.MDF',
>> MOVE 'HSP_FMH_Log' TO 'G:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\HSP_FMH_log.LDF',
> STANDBY='\\Recover01\LogShipping\DBPROD02\UndoTransactionLog\HSP_FMH_UNDO.DA
> T'
>> [Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL
> Server.
>> Connection may have been terminated by the server.
>> Server: Msg 3204, Level 16, State 1, Line 1
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> Connection Broken
>>
>|||Yes. We've come across this several times. It has to do with slow disks
and network latency mixed with the use of UNC path network shares.
Normally, we only have trouble with the create files. So, we create our
backups locally and then use UNC to "PULL" the backup to the restore
location, just as you are currently doing.
However, I suspect that it is the creation of your UNDO.DAT file for the
Standby option. I would also use a local drive for that file, say the local
MSSQL\JOBS folder.
Go ahead and use the UNC to locate the backup file, but your MOVE commands,
and UNDO files should be local drive letters.
Good luck. Let us know how it works.
Sincerely,
Anthony Thomas
"Tom" <tbrackney@.kc.rr.com> wrote in message
news:a_KWd.18954$3V3.14955@.twister.rdc-kc.rr.com...
Well, I did that and it is not givning me any information. I have tried this
several times witht he same result. Any other ideas? Anyonw else have an
idea?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ehlM$RpIFHA.1096@.tk2msftngp13.phx.gbl...
> Hi
> Check the SQL Event Log for any events at the time. SQL Server might be
> terminating the SPID due to a corrupt backup or other internal error.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Tom" <tbrackney@.kc.rr.com> wrote in message
> news:amKWd.1085$Zm4.566@.twister.rdc-kc.rr.com...
>> Hi everyone. I am trying to restore a database and continue to get this
>> error message. Can anyone help?
>> The backup works fine. The back is from a primary server and the restore
> is
>> on the secondary. I wish to restore in standby.
>> Here is the code and the error msg. any help is appricated.
>> RESTORE DATABASE HSP_FMH
>> FROM DISK='\\Dbprod01\BACKUP\HSP_FMH\HSP_FMH.Backup.Device.BAK'
>> WITH
>> MOVE 'HSP_FMH_Data' TO 'G:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\HSP_FMH_Data.MDF',
>> MOVE 'HSP_FMH_Log' TO 'G:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\HSP_FMH_log.LDF',
>
STANDBY='\\Recover01\LogShipping\DBPROD02\UndoTransactionLog\HSP_FMH_UNDO.DA
> T'
>> [Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL
> Server.
>> Connection may have been terminated by the server.
>> Server: Msg 3204, Level 16, State 1, Line 1
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> Connection Broken
>>
>

Restore Problem

Hi everyone. I am trying to restore a database and continue to get this
error message. Can anyone help?
The backup works fine. The back is from a primary server and the restore is
on the secondary. I wish to restore in standby.
Here is the code and the error msg. any help is appricated.
RESTORE DATABASE HSP_FMH
FROM DISK='\\Dbprod01\BACKUP\HSP_FMH\HSP_FMH.Backup.Device.BAK'
WITH
MOVE 'HSP_FMH_Data' TO 'G:\Program Files\Microsoft SQL
Server\MSSQL\Data\HSP_FMH_Data.MDF',
MOVE 'HSP_FMH_Log' TO 'G:\Program Files\Microsoft SQL
Server\MSSQL\Data\HSP_FMH_log.LDF',
STANDBY='\\Recover01\LogShipping\DBPROD0
2\UndoTransactionLog\HSP_FMH_UNDO.DA
T'
[Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL
Server.
Connection may have been terminated by the server.
Server: Msg 3204, Level 16, State 1, Line 1
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Connection BrokenHi
Check the SQL Event Log for any events at the time. SQL Server might be
terminating the SPID due to a corrupt backup or other internal error.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tom" <tbrackney@.kc.rr.com> wrote in message
news:amKWd.1085$Zm4.566@.twister.rdc-kc.rr.com...
> Hi everyone. I am trying to restore a database and continue to get this
> error message. Can anyone help?
> The backup works fine. The back is from a primary server and the restore
is
> on the secondary. I wish to restore in standby.
> Here is the code and the error msg. any help is appricated.
> RESTORE DATABASE HSP_FMH
> FROM DISK='\\Dbprod01\BACKUP\HSP_FMH\HSP_FMH.Backup.Device.BAK'
> WITH
> MOVE 'HSP_FMH_Data' TO 'G:\Program Files\Microsoft SQL
> Server\MSSQL\Data\HSP_FMH_Data.MDF',
> MOVE 'HSP_FMH_Log' TO 'G:\Program Files\Microsoft SQL
> Server\MSSQL\Data\HSP_FMH_log.LDF',
>
STANDBY='\\Recover01\LogShipping\DBPROD0
2\UndoTransactionLog\HSP_FMH_UNDO.DA
T'
> [Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL[/vbco
l]
Server.[vbcol=seagreen]
> Connection may have been terminated by the server.
> Server: Msg 3204, Level 16, State 1, Line 1
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Connection Broken
>|||Well, I did that and it is not givning me any information. I have tried this
several times witht he same result. Any other ideas? Anyonw else have an
idea?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ehlM$RpIFHA.1096@.tk2msftngp13.phx.gbl...
> Hi
> Check the SQL Event Log for any events at the time. SQL Server might be
> terminating the SPID due to a corrupt backup or other internal error.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Tom" <tbrackney@.kc.rr.com> wrote in message
> news:amKWd.1085$Zm4.566@.twister.rdc-kc.rr.com...
> is
> STANDBY='\\Recover01\LogShipping\DBPROD0
2\UndoTransactionLog\HSP_FMH_UNDO.
DA
> T'
> Server.
>|||Yes. We've come across this several times. It has to do with slow disks
and network latency mixed with the use of UNC path network shares.
Normally, we only have trouble with the create files. So, we create our
backups locally and then use UNC to "PULL" the backup to the restore
location, just as you are currently doing.
However, I suspect that it is the creation of your UNDO.DAT file for the
Standby option. I would also use a local drive for that file, say the local
MSSQL\JOBS folder.
Go ahead and use the UNC to locate the backup file, but your MOVE commands,
and UNDO files should be local drive letters.
Good luck. Let us know how it works.
Sincerely,
Anthony Thomas
"Tom" <tbrackney@.kc.rr.com> wrote in message
news:a_KWd.18954$3V3.14955@.twister.rdc-kc.rr.com...
Well, I did that and it is not givning me any information. I have tried this
several times witht he same result. Any other ideas? Anyonw else have an
idea?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ehlM$RpIFHA.1096@.tk2msftngp13.phx.gbl...
> Hi
> Check the SQL Event Log for any events at the time. SQL Server might be
> terminating the SPID due to a corrupt backup or other internal error.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Tom" <tbrackney@.kc.rr.com> wrote in message
> news:amKWd.1085$Zm4.566@.twister.rdc-kc.rr.com...
> is
>
STANDBY='\\Recover01\LogShipping\DBPROD0
2\UndoTransactionLog\HSP_FMH_UNDO.DA
> T'
> Server.
>

Restore Problem

Hi Guys:
I am restoring a databse backup from sql server 2000 cluster to another
server. The restore has been failing with message "RESTORE DATABASE is
terminating abnormally.". When I use restore verify, i get the following
message. Backup is being done on sql server 200 cluster, I am restoring on
another server which is not a cluster. Any help is greatly appreciated
because this is critical and urgent.
Server: Msg 3242, Level 16, State 2, Line 1
The file on device 'C:\temp\LancomeSIS_US_db_200603240243.BAK' is not a
valid Microsoft Tape Format backup set.
Server: Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.
ThanksAre you on Windows 2003?
> Hi Guys:
> I am restoring a databse backup from sql server 2000 cluster to another
> server. The restore has been failing with message "RESTORE DATABASE is
> terminating abnormally.". When I use restore verify, i get the following
> message. Backup is being done on sql server 200 cluster, I am restoring o
n
> another server which is not a cluster. Any help is greatly appreciated
> because this is critical and urgent.
>
> Server: Msg 3242, Level 16, State 2, Line 1
> The file on device 'C:\temp\LancomeSIS_US_db_200603240243.BAK' is not a
> valid Microsoft Tape Format backup set.
> Server: Msg 3013, Level 16, State 1, Line 1
> VERIFY DATABASE is terminating abnormally.
>
> Thanks|||could you paste the output of restore headeronly|||looks like your backup is corrupted, not sure of yet!|||Thanks for replying. Below is the output of the Restore HeaderOnly. Looks
looks like db is corrupt but why. is it something to do with backups
happening on cluster (Raid drive).
BackupName
BackupDescription
BackupType ExpirationDate Compressed
Position DeviceType UserName
ServerName
DatabaseName
DatabaseVersion DatabaseCreationDate
BackupSize FirstLsn LastLsn
CheckpointLsn DifferentialBaseLsn
BackupStartDate BackupFinishDate
SortOrder CodePage UnicodeLocaleId
UnicodeComparisonStyle CompatibilityLevel SoftwareVendorId
SoftwareVersionMajor SoftwareVersionMinor SoftwareVersionBuild MachineName
Flags BindingId
RecoveryForkId Collation
----
----
----
----
----
--
-- --- --
-- --
----
----
----
----
----
----
-- ---
-- --
-- --
--
---
--- -- --
-- -- -- --
-- -- --
----
----
-- ---
---
----
----
*** INCOMPLETE ***
NULL
NULL NULL NULL
1 NULL NULL
NULL
NULL
NULL NULL
NULL NULL NULL
NULL NULL NULL
NULL
NULL NULL NULL NULL
NULL NULL NULL NULL
NULL NULL
NULL NULL NULL
NULL
"Shadow" wrote:

> looks like your backup is corrupted, not sure of yet!
>

Restore Problem

Hi Guys:
I am restoring a databse backup from sql server 2000 cluster to another
server. The restore has been failing with message "RESTORE DATABASE is
terminating abnormally.". When I use restore verify, i get the following
message. Backup is being done on sql server 200 cluster, I am restoring on
another server which is not a cluster. Any help is greatly appreciated
because this is critical and urgent.
Server: Msg 3242, Level 16, State 2, Line 1
The file on device 'C:\temp\LancomeSIS_US_db_200603240243.BAK' is not a
valid Microsoft Tape Format backup set.
Server: Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.
ThanksAre you on Windows 2003?
> Hi Guys:
> I am restoring a databse backup from sql server 2000 cluster to another
> server. The restore has been failing with message "RESTORE DATABASE is
> terminating abnormally.". When I use restore verify, i get the following
> message. Backup is being done on sql server 200 cluster, I am restoring on
> another server which is not a cluster. Any help is greatly appreciated
> because this is critical and urgent.
>
> Server: Msg 3242, Level 16, State 2, Line 1
> The file on device 'C:\temp\LancomeSIS_US_db_200603240243.BAK' is not a
> valid Microsoft Tape Format backup set.
> Server: Msg 3013, Level 16, State 1, Line 1
> VERIFY DATABASE is terminating abnormally.
>
> Thanks|||could you paste the output of restore headeronly|||looks like your backup is corrupted, not sure of yet!|||Thanks for replying. Below is the output of the Restore HeaderOnly. Looks
looks like db is corrupt but why. is it something to do with backups
happening on cluster (Raid drive).
BackupName
BackupDescription
BackupType ExpirationDate Compressed
Position DeviceType UserName
ServerName
DatabaseName
DatabaseVersion DatabaseCreationDate
BackupSize FirstLsn LastLsn
CheckpointLsn DifferentialBaseLsn
BackupStartDate BackupFinishDate
SortOrder CodePage UnicodeLocaleId
UnicodeComparisonStyle CompatibilityLevel SoftwareVendorId
SoftwareVersionMajor SoftwareVersionMinor SoftwareVersionBuild MachineName
Flags BindingId
RecoveryForkId Collation
------
-----------
-- --- --
-- --
------
------
------
-- ---
-- --
-- --
--
---
--- -- --
-- -- -- --
-- -- --
------
-- ---
---
------
*** INCOMPLETE ***
NULL
NULL NULL NULL
1 NULL NULL
NULL
NULL
NULL NULL
NULL NULL NULL
NULL NULL NULL
NULL
NULL NULL NULL NULL
NULL NULL NULL NULL
NULL NULL
NULL NULL NULL
NULL
"Shadow" wrote:
> looks like your backup is corrupted, not sure of yet!
>

restore master database using sqlservr.exe at command line

When I restore master database by executing sqlservr.exe
at command line I encountered an error message:
Cannot find the file 'C:\Program' (or one of its
components). Make sure the path and filename are correct
and that all required libraries are available.
I have checked sqlservr.exe file it is there.
Thank you for help in advance.
Jeff LeeJeff
It sounds like you might have just specified the location of the executable
incorrectly. Can you actual move to the right directory first, and then run
sqlservr.exe right from there? Or show us the entire command line you are
using.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:039901c3d3c7$9c67d930$a101280a@.phx.gbl...
> When I restore master database by executing sqlservr.exe
> at command line I encountered an error message:
> Cannot find the file 'C:\Program' (or one of its
> components). Make sure the path and filename are correct
> and that all required libraries are available.
> I have checked sqlservr.exe file it is there.
> Thank you for help in advance.
> Jeff Lee|||Kalen,
Thank you for your direction. Following is the command I
used in command line
C:\Program Files\Microsoft SQL
Server\MSSQL\Binn\sqlservr.exe -c -m
It appears that sqlservr.exe reacts (quickly shows command
window and disappears serveral seconds later) when just
executing it without parameters -c -m.
>--Original Message--
>Jeff
>It sounds like you might have just specified the location
of the executable
>incorrectly. Can you actual move to the right directory
first, and then run
>sqlservr.exe right from there? Or show us the entire
command line you are
>using.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:039901c3d3c7$9c67d930$a101280a@.phx.gbl...
>> When I restore master database by executing sqlservr.exe
>> at command line I encountered an error message:
>> Cannot find the file 'C:\Program' (or one of its
>> components). Make sure the path and filename are correct
>> and that all required libraries are available.
>> I have checked sqlservr.exe file it is there.
>> Thank you for help in advance.
>> Jeff Lee
>
>.
>|||If you use that as the command, the parser will choke at the first space and
give you the error about Program not being recognized. You could put the
whole command in quotes, or as I suggested, first navigate to the binn
directory, and run sqlservr.exe from there.
If I put the command below in quotes, with or without the parameters, it
starts the SQL Server in that command window. From another command window,
or from QA, I can connect normally. Note that if you close the command
window where you started SQL Server it will stop the SQL Server.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:03ff01c3d3cc$da6e0ba0$a101280a@.phx.gbl...
> Kalen,
> Thank you for your direction. Following is the command I
> used in command line
> C:\Program Files\Microsoft SQL
> Server\MSSQL\Binn\sqlservr.exe -c -m
> It appears that sqlservr.exe reacts (quickly shows command
> window and disappears serveral seconds later) when just
> executing it without parameters -c -m.
>
> >--Original Message--
> >Jeff
> >
> >It sounds like you might have just specified the location
> of the executable
> >incorrectly. Can you actual move to the right directory
> first, and then run
> >sqlservr.exe right from there? Or show us the entire
> command line you are
> >using.
> >
> >--
> >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:039901c3d3c7$9c67d930$a101280a@.phx.gbl...
> >> When I restore master database by executing sqlservr.exe
> >> at command line I encountered an error message:
> >>
> >> Cannot find the file 'C:\Program' (or one of its
> >> components). Make sure the path and filename are correct
> >> and that all required libraries are available.
> >>
> >> I have checked sqlservr.exe file it is there.
> >>
> >> Thank you for help in advance.
> >>
> >> Jeff Lee
> >
> >
> >.
> >

restore master database not working

When I restored the master database from another server I immediately get
the message that the restore succeeded and the (single user) instance of SQL
server ends. When I attempt to start server again in single or multi-user
mode, it looks like it is going to start, but then I just get a bunch of
errors. Most of them are refering to the database files that I didn't copy
over (and they are in a different drive on the original server anyway). Then
the server just stops again. I tried using the enterprise manager and
changing the restore to location to match the new server install, and I also
used the following T-SQL:
RESTORE DATABASE master
FROM disk = 'e:\mssql\backup\master_bk.bak'
MOVE 'master' TO 'e:\mssql\data\master.mdf','mastlog' TO
'e:\mssql\data\mastlog.ldf'
go
We have done it before on a server with the same data path. I used the move
statement. Why doesn't this work?Hi,
This problem is not because of Master database restore. After restore, the
filename column for otherdatabases
(MSDB, Model, Tempdb...and user databases) in master..sysdatabases table may
be refrerring to some other
physical folder. Due to this all the databases failed tro recover and which
will cause the SQL Service to
come down. So look into the sysdatabases table and based on that copy the
files into appropriate folders,
before the restoring Master database.
Thanks
Hari
MCDBA
"rschatz" <@.> wrote in message news:uPtz449$DHA.3452@.TK2MSFTNGP11.phx.gbl...
> When I restored the master database from another server I immediately get
> the message that the restore succeeded and the (single user) instance of
SQL
> server ends. When I attempt to start server again in single or multi-user
> mode, it looks like it is going to start, but then I just get a bunch of
> errors. Most of them are refering to the database files that I didn't copy
> over (and they are in a different drive on the original server anyway).
Then
> the server just stops again. I tried using the enterprise manager and
> changing the restore to location to match the new server install, and I
also
> used the following T-SQL:
> RESTORE DATABASE master
> FROM disk = 'e:\mssql\backup\master_bk.bak'
> MOVE 'master' TO 'e:\mssql\data\master.mdf','mastlog' TO
> 'e:\mssql\data\mastlog.ldf'
> go
> We have done it before on a server with the same data path. I used the
move
> statement. Why doesn't this work?
>|||U have restored master, however your other system databases are not being
initialized because the info in master points to another physical location,
you might want to consider rebuildm utility to help you
get all system databases in correct location. . . .once this is done, you
can go on to the other server and perform sp_detach_db (check BOL) for the
user databases, then copy the *.ldf and *.mdf files into the correct
location on the new server and then run sp_attach_db(check BOL) for all the
user databases.
moving on from this, if you wish to restore your SQL agent Jobs then you can
just resotre the msdb database from the other server with the
REPLACE/MOVE(check BOL) options
--
Olu Adedeji
"rschatz" <@.> wrote in message news:uPtz449$DHA.3452@.TK2MSFTNGP11.phx.gbl...
> When I restored the master database from another server I immediately get
> the message that the restore succeeded and the (single user) instance of
SQL
> server ends. When I attempt to start server again in single or multi-user
> mode, it looks like it is going to start, but then I just get a bunch of
> errors. Most of them are refering to the database files that I didn't copy
> over (and they are in a different drive on the original server anyway).
Then
> the server just stops again. I tried using the enterprise manager and
> changing the restore to location to match the new server install, and I
also
> used the following T-SQL:
> RESTORE DATABASE master
> FROM disk = 'e:\mssql\backup\master_bk.bak'
> MOVE 'master' TO 'e:\mssql\data\master.mdf','mastlog' TO
> 'e:\mssql\data\mastlog.ldf'
> go
> We have done it before on a server with the same data path. I used the
move
> statement. Why doesn't this work?
>

restore master database not working

When I restored the master database from another server I immediately get
the message that the restore succeeded and the (single user) instance of SQL
server ends. When I attempt to start server again in single or multi-user
mode, it looks like it is going to start, but then I just get a bunch of
errors. Most of them are refering to the database files that I didn't copy
over (and they are in a different drive on the original server anyway). Then
the server just stops again. I tried using the enterprise manager and
changing the restore to location to match the new server install, and I also
used the following T-SQL:
RESTORE DATABASE master
FROM disk = 'e:\mssql\backup\master_bk.bak'
MOVE 'master' TO 'e:\mssql\data\master.mdf','mastlog' TO
'e:\mssql\data\mastlog.ldf'
go
We have done it before on a server with the same data path. I used the move
statement. Why doesn't this work?Hi,
This problem is not because of Master database restore. After restore, the
filename column for otherdatabases
(MSDB, Model, Tempdb...and user databases) in master..sysdatabases table may
be refrerring to some other
physical folder. Due to this all the databases failed tro recover and which
will cause the SQL Service to
come down. So look into the sysdatabases table and based on that copy the
files into appropriate folders,
before the restoring Master database.
Thanks
Hari
MCDBA
"rschatz" <@.> wrote in message news:uPtz449$DHA.3452@.TK2MSFTNGP11.phx.gbl...
> When I restored the master database from another server I immediately get
> the message that the restore succeeded and the (single user) instance of
SQL
> server ends. When I attempt to start server again in single or multi-user
> mode, it looks like it is going to start, but then I just get a bunch of
> errors. Most of them are refering to the database files that I didn't copy
> over (and they are in a different drive on the original server anyway).
Then
> the server just stops again. I tried using the enterprise manager and
> changing the restore to location to match the new server install, and I
also
> used the following T-SQL:
> RESTORE DATABASE master
> FROM disk = 'e:\mssql\backup\master_bk.bak'
> MOVE 'master' TO 'e:\mssql\data\master.mdf','mastlog' TO
> 'e:\mssql\data\mastlog.ldf'
> go
> We have done it before on a server with the same data path. I used the
move
> statement. Why doesn't this work?
>|||U have restored master, however your other system databases are not being
initialized because the info in master points to another physical location,
you might want to consider rebuildm utility to help you
get all system databases in correct location. . . .once this is done, you
can go on to the other server and perform sp_detach_db (check BOL) for the
user databases, then copy the *.ldf and *.mdf files into the correct
location on the new server and then run sp_attach_db(check BOL) for all the
user databases.
moving on from this, if you wish to restore your SQL agent Jobs then you can
just resotre the msdb database from the other server with the
REPLACE/MOVE(check BOL) options
Olu Adedeji
"rschatz" <@.> wrote in message news:uPtz449$DHA.3452@.TK2MSFTNGP11.phx.gbl...
> When I restored the master database from another server I immediately get
> the message that the restore succeeded and the (single user) instance of
SQL
> server ends. When I attempt to start server again in single or multi-user
> mode, it looks like it is going to start, but then I just get a bunch of
> errors. Most of them are refering to the database files that I didn't copy
> over (and they are in a different drive on the original server anyway).
Then
> the server just stops again. I tried using the enterprise manager and
> changing the restore to location to match the new server install, and I
also
> used the following T-SQL:
> RESTORE DATABASE master
> FROM disk = 'e:\mssql\backup\master_bk.bak'
> MOVE 'master' TO 'e:\mssql\data\master.mdf','mastlog' TO
> 'e:\mssql\data\mastlog.ldf'
> go
> We have done it before on a server with the same data path. I used the
move
> statement. Why doesn't this work?
>