Error restoring sql backup

Hi All,
I was trying to restore a sql server backup in a Windows 2000 Advanced
Server cluster running SQL Server Enterprise Edition Service Pack 3, in two
nodes.
I have restored successfully some databases, buy I can not restore two
databases. I have obtained the error bellow:
Error 3241 Severity 16 State 1
The media family on device 'f:\backupsql\file.bak' is incorrectly formed.
SQL Server cannot process this media family.
I have tried the Microsoft Article Q297104, but the error continues.
Can anybody help me?
Thanks in advance,
Guillermo Roldan
Spain
Hi
I am not sure what is happening here, but if the KB article is not working,
and you have used the trace flag correctly, then you may want to try some of
the following:
viewing the header only,
restoring the backup onto a different machine,
check the cluster resources allow access to this drive and the files
destination of the MDF and LDF are available.
John
"Guillermo Roldan" <GuillermoRoldan@.discussions.microsoft.com> wrote in
message news:D840177A-3E92-4FB3-B3A9-60656A195E3A@.microsoft.com...
> Hi All,
> I was trying to restore a sql server backup in a Windows 2000 Advanced
> Server cluster running SQL Server Enterprise Edition Service Pack 3, in
> two
> nodes.
> I have restored successfully some databases, buy I can not restore two
> databases. I have obtained the error bellow:
> Error 3241 Severity 16 State 1
> The media family on device 'f:\backupsql\file.bak' is incorrectly formed.
> SQL Server cannot process this media family.
> I have tried the Microsoft Article Q297104, but the error continues.
> Can anybody help me?
> Thanks in advance,
> Guillermo Roldan
> Spain
>
|||Hi John,
I have tried to execute RESTORE HEADERONLY, resulting in an ***INCOMPLETE***
message. I do not understand, because I have checked maintenance plan every
day, and backups was OK. Now, there are only two backups, and there are
incomplete.
It is possible to recover an incomplete backup? There is a full database
backup, with no transactional log backups.
I would like to try the MSSQLRecovery tool on Monday…
Thanks for your answer, John
Guillermo Roldan
Spain
"John Bell" escribió:

> Hi
> I am not sure what is happening here, but if the KB article is not working,
> and you have used the trace flag correctly, then you may want to try some of
> the following:
> viewing the header only,
> restoring the backup onto a different machine,
> check the cluster resources allow access to this drive and the files
> destination of the MDF and LDF are available.
> John
> "Guillermo Roldan" <GuillermoRoldan@.discussions.microsoft.com> wrote in
> message news:D840177A-3E92-4FB3-B3A9-60656A195E3A@.microsoft.com...
>
>
|||Hi
I can only think that it may be disc corruption, although I would have
expected you to have other things to indicate such a failure. If you
have a backup elsewhere you may want to try that, if not a call to PSS
may required.
John
|||Hi John,
I have discover why it is an Incomplete backup. The tape-robot was offline
since 22/11/2005 because a hardware problem, so somebody copy the sql server
backup files to a Novell Netware share. This is the problem: This Novell
Netware share has a limit of 4.194.304 KB. But the real backup was about
8,5GB !!
Finally, i can restore a new backup from tape. This new backup it's older
(18/11/2005), but it run sucesfully !
Thanks John, for your answers
Guillermo Roldan
"John Bell" wrote:

> Hi
> I can only think that it may be disc corruption, although I would have
> expected you to have other things to indicate such a failure. If you
> have a backup elsewhere you may want to try that, if not a call to PSS
> may required.
> John
>
sql

Error restoring sql backup

Hi All,
I was trying to restore a sql server backup in a Windows 2000 Advanced
Server cluster running SQL Server Enterprise Edition Service Pack 3, in two
nodes.
I have restored successfully some databases, buy I can not restore two
databases. I have obtained the error bellow:
Error 3241 Severity 16 State 1
The media family on device 'f:\backupsql\file.bak' is incorrectly formed.
SQL Server cannot process this media family.
I have tried the Microsoft Article Q297104, but the error continues.
Can anybody help me?
Thanks in advance,
Guillermo Roldan
SpainHi
I am not sure what is happening here, but if the KB article is not working,
and you have used the trace flag correctly, then you may want to try some of
the following:
viewing the header only,
restoring the backup onto a different machine,
check the cluster resources allow access to this drive and the files
destination of the MDF and LDF are available.
John
"Guillermo Roldan" <GuillermoRoldan@.discussions.microsoft.com> wrote in
message news:D840177A-3E92-4FB3-B3A9-60656A195E3A@.microsoft.com...
> Hi All,
> I was trying to restore a sql server backup in a Windows 2000 Advanced
> Server cluster running SQL Server Enterprise Edition Service Pack 3, in
> two
> nodes.
> I have restored successfully some databases, buy I can not restore two
> databases. I have obtained the error bellow:
> Error 3241 Severity 16 State 1
> The media family on device 'f:\backupsql\file.bak' is incorrectly formed.
> SQL Server cannot process this media family.
> I have tried the Microsoft Article Q297104, but the error continues.
> Can anybody help me?
> Thanks in advance,
> Guillermo Roldan
> Spain
>|||Hi John,
I have tried to execute RESTORE HEADERONLY, resulting in an ***INCOMPLETE***
message. I do not understand, because I have checked maintenance plan every
day, and backups was OK. Now, there are only two backups, and there are
incomplete.
It is possible to recover an incomplete backup? There is a full database
backup, with no transactional log backups.
I would like to try the MSSQLRecovery tool on Mondayâ?¦
Thanks for your answer, John
Guillermo Roldan
Spain
"John Bell" escribió:
> Hi
> I am not sure what is happening here, but if the KB article is not working,
> and you have used the trace flag correctly, then you may want to try some of
> the following:
> viewing the header only,
> restoring the backup onto a different machine,
> check the cluster resources allow access to this drive and the files
> destination of the MDF and LDF are available.
> John
> "Guillermo Roldan" <GuillermoRoldan@.discussions.microsoft.com> wrote in
> message news:D840177A-3E92-4FB3-B3A9-60656A195E3A@.microsoft.com...
> > Hi All,
> >
> > I was trying to restore a sql server backup in a Windows 2000 Advanced
> > Server cluster running SQL Server Enterprise Edition Service Pack 3, in
> > two
> > nodes.
> >
> > I have restored successfully some databases, buy I can not restore two
> > databases. I have obtained the error bellow:
> >
> > Error 3241 Severity 16 State 1
> > The media family on device 'f:\backupsql\file.bak' is incorrectly formed.
> > SQL Server cannot process this media family.
> >
> > I have tried the Microsoft Article Q297104, but the error continues.
> >
> > Can anybody help me?
> >
> > Thanks in advance,
> > Guillermo Roldan
> > Spain
> >
>
>|||Hi
I can only think that it may be disc corruption, although I would have
expected you to have other things to indicate such a failure. If you
have a backup elsewhere you may want to try that, if not a call to PSS
may required.
John|||Hi John,
I have discover why it is an Incomplete backup. The tape-robot was offline
since 22/11/2005 because a hardware problem, so somebody copy the sql server
backup files to a Novell Netware share. This is the problem: This Novell
Netware share has a limit of 4.194.304 KB. But the real backup was about
8,5GB !!
Finally, i can restore a new backup from tape. This new backup it's older
(18/11/2005), but it run sucesfully !
Thanks John, for your answers
Guillermo Roldan
"John Bell" wrote:
> Hi
> I can only think that it may be disc corruption, although I would have
> expected you to have other things to indicate such a failure. If you
> have a backup elsewhere you may want to try that, if not a call to PSS
> may required.
> John
>

Error restoring sql backup

Hi All,
I was trying to restore a sql server backup in a Windows 2000 Advanced
Server cluster running SQL Server Enterprise Edition Service Pack 3, in two
nodes.
I have restored successfully some databases, buy I can not restore two
databases. I have obtained the error bellow:
Error 3241 Severity 16 State 1
The media family on device 'f:\backupsql\file.bak' is incorrectly formed.
SQL Server cannot process this media family.
I have tried the Microsoft Article Q297104, but the error continues.
Can anybody help me?
Thanks in advance,
Guillermo Roldan
SpainHi
I am not sure what is happening here, but if the KB article is not working,
and you have used the trace flag correctly, then you may want to try some of
the following:
viewing the header only,
restoring the backup onto a different machine,
check the cluster resources allow access to this drive and the files
destination of the MDF and LDF are available.
John
"Guillermo Roldan" <GuillermoRoldan@.discussions.microsoft.com> wrote in
message news:D840177A-3E92-4FB3-B3A9-60656A195E3A@.microsoft.com...
> Hi All,
> I was trying to restore a sql server backup in a Windows 2000 Advanced
> Server cluster running SQL Server Enterprise Edition Service Pack 3, in
> two
> nodes.
> I have restored successfully some databases, buy I can not restore two
> databases. I have obtained the error bellow:
> Error 3241 Severity 16 State 1
> The media family on device 'f:\backupsql\file.bak' is incorrectly formed.
> SQL Server cannot process this media family.
> I have tried the Microsoft Article Q297104, but the error continues.
> Can anybody help me?
> Thanks in advance,
> Guillermo Roldan
> Spain
>|||Hi John,
I have tried to execute RESTORE HEADERONLY, resulting in an ***INCOMPLETE***
message. I do not understand, because I have checked maintenance plan every
day, and backups was OK. Now, there are only two backups, and there are
incomplete.
It is possible to recover an incomplete backup? There is a full database
backup, with no transactional log backups.
I would like to try the MSSQLRecovery tool on Monday…
Thanks for your answer, John
Guillermo Roldan
Spain
"John Bell" escribió:

> Hi
> I am not sure what is happening here, but if the KB article is not working
,
> and you have used the trace flag correctly, then you may want to try some
of
> the following:
> viewing the header only,
> restoring the backup onto a different machine,
> check the cluster resources allow access to this drive and the files
> destination of the MDF and LDF are available.
> John
> "Guillermo Roldan" <GuillermoRoldan@.discussions.microsoft.com> wrote in
> message news:D840177A-3E92-4FB3-B3A9-60656A195E3A@.microsoft.com...
>
>|||Hi
I can only think that it may be disc corruption, although I would have
expected you to have other things to indicate such a failure. If you
have a backup elsewhere you may want to try that, if not a call to PSS
may required.
John|||Hi John,
I have discover why it is an Incomplete backup. The tape-robot was offline
since 22/11/2005 because a hardware problem, so somebody copy the sql server
backup files to a Novell Netware share. This is the problem: This Novell
Netware share has a limit of 4.194.304 KB. But the real backup was about
8,5GB !!
Finally, i can restore a new backup from tape. This new backup it's older
(18/11/2005), but it run sucesfully !
Thanks John, for your answers
Guillermo Roldan
"John Bell" wrote:

> Hi
> I can only think that it may be disc corruption, although I would have
> expected you to have other things to indicate such a failure. If you
> have a backup elsewhere you may want to try that, if not a call to PSS
> may required.
> John
>

Error restoring MSDB database

I'm getting the following error while trying to restore the msdb database:
The backup of the system database on device
d:\mssql\backup\msdb_db_200510312200.BAK
cannot be restored because it was created by a different version of the
server (134218488) than this server (134218546).
Both servers are SQL2000 SE.
Ron
Ron wrote:
> I'm getting the following error while trying to restore the msdb
> database:
> The backup of the system database on device
> d:\mssql\backup\msdb_db_200510312200.BAK
> cannot be restored because it was created by a different version of
> the server (134218488) than this server (134218546).
> Both servers are SQL2000 SE.
> Ron
The backup is from a server with an older service pack than the one you
are restoring to. Make sure they are both the same service pack.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Can you tell from the version from the number - (134218488)?
"David Gugick" wrote:

> Ron wrote:
> The backup is from a server with an older service pack than the one you
> are restoring to. Make sure they are both the same service pack.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||Ron wrote:
> Can you tell from the version from the number - (134218488)?
>
I think that is SP3/SP3a, but read the following links for info on how
to get the version and a possible way to restore the msdb.
http://www.developmentnow.com/g/118_...the-server.htm
http://www.sqlservercentral.com/foru...ssageid=229354
David Gugick
Quest Software
www.imceda.com
www.quest.com

Error restoring MSDB database

I'm getting the following error while trying to restore the msdb database:
The backup of the system database on device
d:\mssql\backup\msdb_db_200510312200.BAK
cannot be restored because it was created by a different version of the
server (134218488) than this server (134218546).
Both servers are SQL2000 SE.
RonRon wrote:
> I'm getting the following error while trying to restore the msdb
> database:
> The backup of the system database on device
> d:\mssql\backup\msdb_db_200510312200.BAK
> cannot be restored because it was created by a different version of
> the server (134218488) than this server (134218546).
> Both servers are SQL2000 SE.
> Ron
The backup is from a server with an older service pack than the one you
are restoring to. Make sure they are both the same service pack.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Can you tell from the version from the number - (134218488)?
"David Gugick" wrote:

> Ron wrote:
> The backup is from a server with an older service pack than the one you
> are restoring to. Make sure they are both the same service pack.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Ron wrote:
> Can you tell from the version from the number - (134218488)?
>
I think that is SP3/SP3a, but read the following links for info on how
to get the version and a possible way to restore the msdb.
http://www.developmentnow.com/g/118...-the-server.htm
http://www.sqlservercentral.com/for...=229354

David Gugick
Quest Software
www.imceda.com
www.quest.com

Error restoring MSDB database

I'm getting the following error while trying to restore the msdb database:
The backup of the system database on device
d:\mssql\backup\msdb_db_200510312200.BAK
cannot be restored because it was created by a different version of the
server (134218488) than this server (134218546).
Both servers are SQL2000 SE.
RonRon wrote:
> I'm getting the following error while trying to restore the msdb
> database:
> The backup of the system database on device
> d:\mssql\backup\msdb_db_200510312200.BAK
> cannot be restored because it was created by a different version of
> the server (134218488) than this server (134218546).
> Both servers are SQL2000 SE.
> Ron
The backup is from a server with an older service pack than the one you
are restoring to. Make sure they are both the same service pack.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Can you tell from the version from the number - (134218488)?
"David Gugick" wrote:
> Ron wrote:
> > I'm getting the following error while trying to restore the msdb
> > database:
> >
> > The backup of the system database on device
> > d:\mssql\backup\msdb_db_200510312200.BAK
> > cannot be restored because it was created by a different version of
> > the server (134218488) than this server (134218546).
> >
> > Both servers are SQL2000 SE.
> >
> > Ron
> The backup is from a server with an older service pack than the one you
> are restoring to. Make sure they are both the same service pack.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Ron wrote:
> Can you tell from the version from the number - (134218488)?
>
I think that is SP3/SP3a, but read the following links for info on how
to get the version and a possible way to restore the msdb.
http://www.developmentnow.com/g/118_2003_11_0_0_464983/Restore-error--different-version-of-the-server.htm
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=229354
David Gugick
Quest Software
www.imceda.com
www.quest.comsql

Error restoring from SQL 2000 to 2005

Hi all,

I'm having problem to restore DB from SQL server 2000 to 2005.
Here is the error:

Restore failed for 'PROD'. (Micrsoft.SqlServer.Smo)
System.data.sqlclient.sqlerror:Invalid colun name'SC_BL_ADAPT'.(MicrsoftSQLSever.Smo).

Thanks for any help.

Regards,

Abrahim

__

check whether the backup is taken properly or not

RESTORE VERIFYONLY : To varify the database backup integrity

RESTORE FILELISTONLY : see the contents of backup file

Read more about these in BOL

Madhu

|||

Madhu,

The database has been backed-up with “Verify backup upon completion” on SQL server 2000.
I have no problem to restore the database in SQL 2000. It just SQL 2005 is not happy with column name “SC_BL_ADAPT”.

Any idea about “SC_BL_ADAPT”?

I’ll look it to RESTORE VERIFYONLY : To varify the database backup integrity

RESTORE FILELISTONLY : see the contents of backup file

Thanks in advance,

Abrahim

|||

sql server 2000 Restore Verifyonly is a useless command. it only check the header. But in SQL Server 2005 Restore varifyonly check the physical file integrity. check in 2005. And also try to restore this backup file in sql server 2000 as a temp database (only for the sake of error finding) and post back the result

Madhu

|||

Check the sql error log...if there is any other error related to restore.

|||

I think this is a tools problem and may not be the backup, try restoring using Query editior or have you tried?

Have you tested this from another machine?

|||Here is what I have done so far:
RESTORE verifyonly from disk =
N'C:\capdb_to_2005_1.bak'

Messages:
The backup set is valid.

/////////////////////////
then:
RESTORE FILELISTONLY from disk =
N'C:\capdb_to_2005_1.bak'

messages:
(4 row(s) affected)

Results:

capdb_Data C:\capdb_Data.MDF D PRIMARY 524288000 35184372080640
capdb_1_Data C:\capdb_1_Data D PRIMARY 1048576 35184372080640
capdb_Log C:\capdb_Log.LDF L NULL 1048576 35184372080640
capdb_1_Log C:\capdb_1_Log L NULL 13959168 35184372080640
/////////////////////////////////////////////

Yes, I try to restore it from several systems, but still have problem.|||You got the File List successfully, now have you actually tried a RESTORE DATABASE command?

Error Restoring Database - SQLState: 42000 - insufficient free space

I am trying to move a database from one machine to another by taking a
backup from one and restoring it on another. Both machines are W2k sp3 with
SQL2k sp3. As far as I can tell they are both straightforward, standard
installations (OS and DB), except;
The target machine has been partitioned into three (system, apps, and data),
and the disk is mirrored.
I can take the backup and restore it to a number of machines, but on the
machine that I need to restore the database to I get;
Microsoft SQL-DMO(ODBC SQL State: 42000)
There is insufficient free space on disk volume e:\ to create the database.
The database requires 16519462912 additional free bytes, while only
6329229312 bytes are available.
RESTORE DATABASE is terminating abnormally.
The database backup file is only 146M, the data file is 143M and the log
file is 15M.
The data partition (e:) has 8G free space, although the system/boot
partition (c:) has about 2G
Does anyone have any ideas how this could be happening?
--
regards,
Paul DurdinHi.
The data partition (e:) need + - 16 GB. free,you only have
+ - 6 GB.
>--Original Message--
>I am trying to move a database from one machine to
another by taking a
>backup from one and restoring it on another. Both
machines are W2k sp3 with
>SQL2k sp3. As far as I can tell they are both
straightforward, standard
>installations (OS and DB), except;
>The target machine has been partitioned into three
(system, apps, and data),
>and the disk is mirrored.
>I can take the backup and restore it to a number of
machines, but on the
>machine that I need to restore the database to I get;
>Microsoft SQL-DMO(ODBC SQL State: 42000)
>There is insufficient free space on disk volume e:\ to
create the database.
>The database requires 16519462912 additional free bytes,
while only
>6329229312 bytes are available.
>RESTORE DATABASE is terminating abnormally.
>The database backup file is only 146M, the data file is
143M and the log
>file is 15M.
>The data partition (e:) has 8G free space, although the
system/boot
>partition (c:) has about 2G
>Does anyone have any ideas how this could be happening?
>--
>regards,
>Paul Durdin
>
>.
>|||That's indeed what the error message says - but surely you telling me that a
140M database needs 16G to restore it!
--
regards,
"Jorge Lavado" <jorge.Lavado@.cgey.com_NO_SPAM> wrote in message
news:0aab01c3aa9b$1255c6d0$a101280a@.phx.gbl...
> Hi.
> The data partition (e:) need + - 16 GB. free,you only have
> + - 6 GB.
> >--Original Message--
> >I am trying to move a database from one machine to
> another by taking a
> >backup from one and restoring it on another. Both
> machines are W2k sp3 with
> >SQL2k sp3. As far as I can tell they are both
> straightforward, standard
> >installations (OS and DB), except;
> >The target machine has been partitioned into three
> (system, apps, and data),
> >and the disk is mirrored.
> >
> >I can take the backup and restore it to a number of
> machines, but on the
> >machine that I need to restore the database to I get;
> >
> >Microsoft SQL-DMO(ODBC SQL State: 42000)
> >There is insufficient free space on disk volume e:\ to
> create the database.
> >The database requires 16519462912 additional free bytes,
> while only
> >6329229312 bytes are available.
> >RESTORE DATABASE is terminating abnormally.
> >
> >The database backup file is only 146M, the data file is
> 143M and the log
> >file is 15M.
> >The data partition (e:) has 8G free space, although the
> system/boot
> >partition (c:) has about 2G
> >
> >Does anyone have any ideas how this could be happening?
> >--
> >regards,
> >
> >Paul Durdin
> >
> >
> >.
> >|||When you restore a database, you need the same amount of space for the mdf,
ndf and ldf as the size those files had when you did the backup. If the
files were smaller when you did the backup and the restore claims you need
more space, you have hit a bug in SQL Server (I haven't heard of such a bug,
though). A couple of things:
Make sure that you restore the correct backup. One backup file can have more
than one backup. Check using RESTORE HEADERONLY.
Check the size needed according to the backup. Use RESTORE FILELISTONLY.
Make sure you specify the correct FILE = if the backup file has more then
one backup.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul Durdin" <pdurdin@.nospam_hotmail.com> wrote in message
news:%23Vrx9mqqDHA.2644@.TK2MSFTNGP09.phx.gbl...
> That's indeed what the error message says - but surely you telling me that
a
> 140M database needs 16G to restore it!
> --
> regards,
>
> "Jorge Lavado" <jorge.Lavado@.cgey.com_NO_SPAM> wrote in message
> news:0aab01c3aa9b$1255c6d0$a101280a@.phx.gbl...
> > Hi.
> > The data partition (e:) need + - 16 GB. free,you only have
> > + - 6 GB.
> >
> > >--Original Message--
> > >I am trying to move a database from one machine to
> > another by taking a
> > >backup from one and restoring it on another. Both
> > machines are W2k sp3 with
> > >SQL2k sp3. As far as I can tell they are both
> > straightforward, standard
> > >installations (OS and DB), except;
> > >The target machine has been partitioned into three
> > (system, apps, and data),
> > >and the disk is mirrored.
> > >
> > >I can take the backup and restore it to a number of
> > machines, but on the
> > >machine that I need to restore the database to I get;
> > >
> > >Microsoft SQL-DMO(ODBC SQL State: 42000)
> > >There is insufficient free space on disk volume e:\ to
> > create the database.
> > >The database requires 16519462912 additional free bytes,
> > while only
> > >6329229312 bytes are available.
> > >RESTORE DATABASE is terminating abnormally.
> > >
> > >The database backup file is only 146M, the data file is
> > 143M and the log
> > >file is 15M.
> > >The data partition (e:) has 8G free space, although the
> > system/boot
> > >partition (c:) has about 2G
> > >
> > >Does anyone have any ideas how this could be happening?
> > >--
> > >regards,
> > >
> > >Paul Durdin
> > >
> > >
> > >.
> > >
>|||Thanks for the suggestions.
I've checked the backup file using RESTORE FILELISTONLY FROM
FILE='backupfilename'
This reports the log file as 16377380864 (where have I seem this figure
before?) So I guess that explains why SQLSvr says it cant restore the
database.
I checked the log file size in EM to be 14,643M, but the physical log file
is actually 14,993,664k.
Being a newbie to SQL DBA stuff I am puzzled. How come the physical size is
different from the size recorded in EM?
I did a bit of reading, and I am starting to get a bit of an understanding,
so I did a...
DBCC SHRINKDATABASE (databasename)
followed by
BACKUP LOG databasename WITH TRUNCATE_ONLY
Then did another backup and examined the backup as above. I get much the
same figures as before.
How can I shrink the log file (having already dome the above).
--
regards,
Paul Durdin
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:e1HtntqqDHA.2588@.tk2msftngp13.phx.gbl...
> When you restore a database, you need the same amount of space for the
mdf,
> ndf and ldf as the size those files had when you did the backup. If the
> files were smaller when you did the backup and the restore claims you need
> more space, you have hit a bug in SQL Server (I haven't heard of such a
bug,
> though). A couple of things:
> Make sure that you restore the correct backup. One backup file can have
more
> than one backup. Check using RESTORE HEADERONLY.
> Check the size needed according to the backup. Use RESTORE FILELISTONLY.
> Make sure you specify the correct FILE = if the backup file has more then
> one backup.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Paul Durdin" <pdurdin@.nospam_hotmail.com> wrote in message
> news:%23Vrx9mqqDHA.2644@.TK2MSFTNGP09.phx.gbl...
> > That's indeed what the error message says - but surely you telling me
that
> a
> > 140M database needs 16G to restore it!
> >
> > --
> > regards,
> >
> >
> > "Jorge Lavado" <jorge.Lavado@.cgey.com_NO_SPAM> wrote in message
> > news:0aab01c3aa9b$1255c6d0$a101280a@.phx.gbl...
> > > Hi.
> > > The data partition (e:) need + - 16 GB. free,you only have
> > > + - 6 GB.
> > >
> > > >--Original Message--
> > > >I am trying to move a database from one machine to
> > > another by taking a
> > > >backup from one and restoring it on another. Both
> > > machines are W2k sp3 with
> > > >SQL2k sp3. As far as I can tell they are both
> > > straightforward, standard
> > > >installations (OS and DB), except;
> > > >The target machine has been partitioned into three
> > > (system, apps, and data),
> > > >and the disk is mirrored.
> > > >
> > > >I can take the backup and restore it to a number of
> > > machines, but on the
> > > >machine that I need to restore the database to I get;
> > > >
> > > >Microsoft SQL-DMO(ODBC SQL State: 42000)
> > > >There is insufficient free space on disk volume e:\ to
> > > create the database.
> > > >The database requires 16519462912 additional free bytes,
> > > while only
> > > >6329229312 bytes are available.
> > > >RESTORE DATABASE is terminating abnormally.
> > > >
> > > >The database backup file is only 146M, the data file is
> > > 143M and the log
> > > >file is 15M.
> > > >The data partition (e:) has 8G free space, although the
> > > system/boot
> > > >partition (c:) has about 2G
> > > >
> > > >Does anyone have any ideas how this could be happening?
> > > >--
> > > >regards,
> > > >
> > > >Paul Durdin
> > > >
> > > >
> > > >.
> > > >
> >
> >
>|||Seems like EM reports the size incorrectly... Where in EM do you see that
space reported? In the taskpad or right-click the database, properties?
To shrink log, Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul Durdin" <pdurdin@.nospam_hotmail.com> wrote in message
news:OBrU7YsqDHA.3844@.tk2msftngp13.phx.gbl...
> Thanks for the suggestions.
> I've checked the backup file using RESTORE FILELISTONLY FROM
> FILE='backupfilename'
> This reports the log file as 16377380864 (where have I seem this figure
> before?) So I guess that explains why SQLSvr says it cant restore the
> database.
> I checked the log file size in EM to be 14,643M, but the physical log file
> is actually 14,993,664k.
> Being a newbie to SQL DBA stuff I am puzzled. How come the physical size
is
> different from the size recorded in EM?
> I did a bit of reading, and I am starting to get a bit of an
understanding,
> so I did a...
> DBCC SHRINKDATABASE (databasename)
> followed by
> BACKUP LOG databasename WITH TRUNCATE_ONLY
> Then did another backup and examined the backup as above. I get much the
> same figures as before.
> How can I shrink the log file (having already dome the above).
> --
> regards,
> Paul Durdin
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:e1HtntqqDHA.2588@.tk2msftngp13.phx.gbl...
> > When you restore a database, you need the same amount of space for the
> mdf,
> > ndf and ldf as the size those files had when you did the backup. If the
> > files were smaller when you did the backup and the restore claims you
need
> > more space, you have hit a bug in SQL Server (I haven't heard of such a
> bug,
> > though). A couple of things:
> >
> > Make sure that you restore the correct backup. One backup file can have
> more
> > than one backup. Check using RESTORE HEADERONLY.
> > Check the size needed according to the backup. Use RESTORE FILELISTONLY.
> > Make sure you specify the correct FILE = if the backup file has more
then
> > one backup.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Paul Durdin" <pdurdin@.nospam_hotmail.com> wrote in message
> > news:%23Vrx9mqqDHA.2644@.TK2MSFTNGP09.phx.gbl...
> > > That's indeed what the error message says - but surely you telling me
> that
> > a
> > > 140M database needs 16G to restore it!
> > >
> > > --
> > > regards,
> > >
> > >
> > > "Jorge Lavado" <jorge.Lavado@.cgey.com_NO_SPAM> wrote in message
> > > news:0aab01c3aa9b$1255c6d0$a101280a@.phx.gbl...
> > > > Hi.
> > > > The data partition (e:) need + - 16 GB. free,you only have
> > > > + - 6 GB.
> > > >
> > > > >--Original Message--
> > > > >I am trying to move a database from one machine to
> > > > another by taking a
> > > > >backup from one and restoring it on another. Both
> > > > machines are W2k sp3 with
> > > > >SQL2k sp3. As far as I can tell they are both
> > > > straightforward, standard
> > > > >installations (OS and DB), except;
> > > > >The target machine has been partitioned into three
> > > > (system, apps, and data),
> > > > >and the disk is mirrored.
> > > > >
> > > > >I can take the backup and restore it to a number of
> > > > machines, but on the
> > > > >machine that I need to restore the database to I get;
> > > > >
> > > > >Microsoft SQL-DMO(ODBC SQL State: 42000)
> > > > >There is insufficient free space on disk volume e:\ to
> > > > create the database.
> > > > >The database requires 16519462912 additional free bytes,
> > > > while only
> > > > >6329229312 bytes are available.
> > > > >RESTORE DATABASE is terminating abnormally.
> > > > >
> > > > >The database backup file is only 146M, the data file is
> > > > 143M and the log
> > > > >file is 15M.
> > > > >The data partition (e:) has 8G free space, although the
> > > > system/boot
> > > > >partition (c:) has about 2G
> > > > >
> > > > >Does anyone have any ideas how this could be happening?
> > > > >--
> > > > >regards,
> > > > >
> > > > >Paul Durdin
> > > > >
> > > > >
> > > > >.
> > > > >
> > >
> > >
> >
> >
>|||Sorted!!
Article 272318 says it all. Now have the log file shrunk and the backup
reflects a smaller size. I'll have to try restoring the backup on the target
machine on Monday, but this looks like it will have fixed the problem.
Thanks for your help,
--
regards,
Paul Durdin
"Paul Durdin" <pdurdin@.nospam_hotmail.com> wrote in message
news:OBrU7YsqDHA.3844@.tk2msftngp13.phx.gbl...
> Thanks for the suggestions.
> I've checked the backup file using RESTORE FILELISTONLY FROM
> FILE='backupfilename'
> This reports the log file as 16377380864 (where have I seem this figure
> before?) So I guess that explains why SQLSvr says it cant restore the
> database.
> I checked the log file size in EM to be 14,643M, but the physical log file
> is actually 14,993,664k.
> Being a newbie to SQL DBA stuff I am puzzled. How come the physical size
is
> different from the size recorded in EM?
> I did a bit of reading, and I am starting to get a bit of an
understanding,
> so I did a...
> DBCC SHRINKDATABASE (databasename)
> followed by
> BACKUP LOG databasename WITH TRUNCATE_ONLY
> Then did another backup and examined the backup as above. I get much the
> same figures as before.
> How can I shrink the log file (having already dome the above).
> --
> regards,
> Paul Durdin
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:e1HtntqqDHA.2588@.tk2msftngp13.phx.gbl...
> > When you restore a database, you need the same amount of space for the
> mdf,
> > ndf and ldf as the size those files had when you did the backup. If the
> > files were smaller when you did the backup and the restore claims you
need
> > more space, you have hit a bug in SQL Server (I haven't heard of such a
> bug,
> > though). A couple of things:
> >
> > Make sure that you restore the correct backup. One backup file can have
> more
> > than one backup. Check using RESTORE HEADERONLY.
> > Check the size needed according to the backup. Use RESTORE FILELISTONLY.
> > Make sure you specify the correct FILE = if the backup file has more
then
> > one backup.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Paul Durdin" <pdurdin@.nospam_hotmail.com> wrote in message
> > news:%23Vrx9mqqDHA.2644@.TK2MSFTNGP09.phx.gbl...
> > > That's indeed what the error message says - but surely you telling me
> that
> > a
> > > 140M database needs 16G to restore it!
> > >
> > > --
> > > regards,
> > >
> > >
> > > "Jorge Lavado" <jorge.Lavado@.cgey.com_NO_SPAM> wrote in message
> > > news:0aab01c3aa9b$1255c6d0$a101280a@.phx.gbl...
> > > > Hi.
> > > > The data partition (e:) need + - 16 GB. free,you only have
> > > > + - 6 GB.
> > > >
> > > > >--Original Message--
> > > > >I am trying to move a database from one machine to
> > > > another by taking a
> > > > >backup from one and restoring it on another. Both
> > > > machines are W2k sp3 with
> > > > >SQL2k sp3. As far as I can tell they are both
> > > > straightforward, standard
> > > > >installations (OS and DB), except;
> > > > >The target machine has been partitioned into three
> > > > (system, apps, and data),
> > > > >and the disk is mirrored.
> > > > >
> > > > >I can take the backup and restore it to a number of
> > > > machines, but on the
> > > > >machine that I need to restore the database to I get;
> > > > >
> > > > >Microsoft SQL-DMO(ODBC SQL State: 42000)
> > > > >There is insufficient free space on disk volume e:\ to
> > > > create the database.
> > > > >The database requires 16519462912 additional free bytes,
> > > > while only
> > > > >6329229312 bytes are available.
> > > > >RESTORE DATABASE is terminating abnormally.
> > > > >
> > > > >The database backup file is only 146M, the data file is
> > > > 143M and the log
> > > > >file is 15M.
> > > > >The data partition (e:) has 8G free space, although the
> > > > system/boot
> > > > >partition (c:) has about 2G
> > > > >
> > > > >Does anyone have any ideas how this could be happening?
> > > > >--
> > > > >regards,
> > > > >
> > > > >Paul Durdin
> > > > >
> > > > >
> > > > >.
> > > > >
> > >
> > >
> >
> >
>|||I was looking in the database properties under Transaction Log->Space
allocated.
Doh! I think it was me - I took 15,xxx to be 15M but now the figure is 11(M)
so EM was reporting 15G (correctly).
It was me! It was me! I shall have to go on a re-education course / take up
a new career, or maybe pack up for the day and get ready to watch the rugby
over the weekend (its very weird watching rugby in the morning - it should
be an evening thing with plenty of beer).
--
regards,
Paul Durdin
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eoivagsqDHA.2776@.tk2msftngp13.phx.gbl...
> Seems like EM reports the size incorrectly... Where in EM do you see that
> space reported? In the taskpad or right-click the database, properties?
> To shrink log, Check out below KB articles:
> INF: How to Shrink the SQL Server 7.0 Transaction Log
> http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
> INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
> http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
> Log File Grows too big
> http://www.support.microsoft.com/?id=317375
> Log file filling up
> http://www.support.microsoft.com/?id=110139
> Considerations for Autogrow and AutoShrink
> http://www.support.microsoft.com/?id=315512
> http://www.mssqlserver.com/faq/logs-shrinklog.asp
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Paul Durdin" <pdurdin@.nospam_hotmail.com> wrote in message
> news:OBrU7YsqDHA.3844@.tk2msftngp13.phx.gbl...
> > Thanks for the suggestions.
> >
> > I've checked the backup file using RESTORE FILELISTONLY FROM
> > FILE='backupfilename'
> >
> > This reports the log file as 16377380864 (where have I seem this figure
> > before?) So I guess that explains why SQLSvr says it cant restore the
> > database.
> >
> > I checked the log file size in EM to be 14,643M, but the physical log
file
> > is actually 14,993,664k.
> >
> > Being a newbie to SQL DBA stuff I am puzzled. How come the physical size
> is
> > different from the size recorded in EM?
> >
> > I did a bit of reading, and I am starting to get a bit of an
> understanding,
> > so I did a...
> >
> > DBCC SHRINKDATABASE (databasename)
> > followed by
> > BACKUP LOG databasename WITH TRUNCATE_ONLY
> >
> > Then did another backup and examined the backup as above. I get much the
> > same figures as before.
> >
> > How can I shrink the log file (having already dome the above).
> >
> > --
> > regards,
> >
> > Paul Durdin
> >
> > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:e1HtntqqDHA.2588@.tk2msftngp13.phx.gbl...
> > > When you restore a database, you need the same amount of space for the
> > mdf,
> > > ndf and ldf as the size those files had when you did the backup. If
the
> > > files were smaller when you did the backup and the restore claims you
> need
> > > more space, you have hit a bug in SQL Server (I haven't heard of such
a
> > bug,
> > > though). A couple of things:
> > >
> > > Make sure that you restore the correct backup. One backup file can
have
> > more
> > > than one backup. Check using RESTORE HEADERONLY.
> > > Check the size needed according to the backup. Use RESTORE
FILELISTONLY.
> > > Make sure you specify the correct FILE = if the backup file has more
> then
> > > one backup.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> > >
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Paul Durdin" <pdurdin@.nospam_hotmail.com> wrote in message
> > > news:%23Vrx9mqqDHA.2644@.TK2MSFTNGP09.phx.gbl...
> > > > That's indeed what the error message says - but surely you telling
me
> > that
> > > a
> > > > 140M database needs 16G to restore it!
> > > >
> > > > --
> > > > regards,
> > > >
> > > >
> > > > "Jorge Lavado" <jorge.Lavado@.cgey.com_NO_SPAM> wrote in message
> > > > news:0aab01c3aa9b$1255c6d0$a101280a@.phx.gbl...
> > > > > Hi.
> > > > > The data partition (e:) need + - 16 GB. free,you only have
> > > > > + - 6 GB.
> > > > >
> > > > > >--Original Message--
> > > > > >I am trying to move a database from one machine to
> > > > > another by taking a
> > > > > >backup from one and restoring it on another. Both
> > > > > machines are W2k sp3 with
> > > > > >SQL2k sp3. As far as I can tell they are both
> > > > > straightforward, standard
> > > > > >installations (OS and DB), except;
> > > > > >The target machine has been partitioned into three
> > > > > (system, apps, and data),
> > > > > >and the disk is mirrored.
> > > > > >
> > > > > >I can take the backup and restore it to a number of
> > > > > machines, but on the
> > > > > >machine that I need to restore the database to I get;
> > > > > >
> > > > > >Microsoft SQL-DMO(ODBC SQL State: 42000)
> > > > > >There is insufficient free space on disk volume e:\ to
> > > > > create the database.
> > > > > >The database requires 16519462912 additional free bytes,
> > > > > while only
> > > > > >6329229312 bytes are available.
> > > > > >RESTORE DATABASE is terminating abnormally.
> > > > > >
> > > > > >The database backup file is only 146M, the data file is
> > > > > 143M and the log
> > > > > >file is 15M.
> > > > > >The data partition (e:) has 8G free space, although the
> > > > > system/boot
> > > > > >partition (c:) has about 2G
> > > > > >
> > > > > >Does anyone have any ideas how this could be happening?
> > > > > >--
> > > > > >regards,
> > > > > >
> > > > > >Paul Durdin
> > > > > >
> > > > > >
> > > > > >.
> > > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||> It was me! It was me! I shall have to go on a re-education course / take
up
> a new career, or maybe pack up for the day and get ready to watch the
rugby
> over the weekend (its very weird watching rugby in the morning - it should
> be an evening thing with plenty of beer).
LOL! :-) We all do this from time to time. One tip: IF contradictions, trust
the engine, as the tools might have bugs in them. This wasn't such a case,
but could have been .
Well, here, we have to go to a sportsbar to watch the rugby. In the
morning...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul Durdin" <pdurdin@.nospam_hotmail.com> wrote in message
news:%2336PHpsqDHA.1960@.TK2MSFTNGP12.phx.gbl...
> I was looking in the database properties under Transaction Log->Space
> allocated.
> Doh! I think it was me - I took 15,xxx to be 15M but now the figure is
11(M)
> so EM was reporting 15G (correctly).
> It was me! It was me! I shall have to go on a re-education course / take
up
> a new career, or maybe pack up for the day and get ready to watch the
rugby
> over the weekend (its very weird watching rugby in the morning - it should
> be an evening thing with plenty of beer).
> --
> regards,
> Paul Durdin
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:eoivagsqDHA.2776@.tk2msftngp13.phx.gbl...
> > Seems like EM reports the size incorrectly... Where in EM do you see
that
> > space reported? In the taskpad or right-click the database, properties?
> >
> > To shrink log, Check out below KB articles:
> >
> > INF: How to Shrink the SQL Server 7.0 Transaction Log
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
> >
> > INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
SHRINKFILE
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
> >
> > Log File Grows too big
> > http://www.support.microsoft.com/?id=317375
> >
> > Log file filling up
> > http://www.support.microsoft.com/?id=110139
> >
> > Considerations for Autogrow and AutoShrink
> > http://www.support.microsoft.com/?id=315512
> >
> > http://www.mssqlserver.com/faq/logs-shrinklog.asp
> >
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Paul Durdin" <pdurdin@.nospam_hotmail.com> wrote in message
> > news:OBrU7YsqDHA.3844@.tk2msftngp13.phx.gbl...
> > > Thanks for the suggestions.
> > >
> > > I've checked the backup file using RESTORE FILELISTONLY FROM
> > > FILE='backupfilename'
> > >
> > > This reports the log file as 16377380864 (where have I seem this
figure
> > > before?) So I guess that explains why SQLSvr says it cant restore the
> > > database.
> > >
> > > I checked the log file size in EM to be 14,643M, but the physical log
> file
> > > is actually 14,993,664k.
> > >
> > > Being a newbie to SQL DBA stuff I am puzzled. How come the physical
size
> > is
> > > different from the size recorded in EM?
> > >
> > > I did a bit of reading, and I am starting to get a bit of an
> > understanding,
> > > so I did a...
> > >
> > > DBCC SHRINKDATABASE (databasename)
> > > followed by
> > > BACKUP LOG databasename WITH TRUNCATE_ONLY
> > >
> > > Then did another backup and examined the backup as above. I get much
the
> > > same figures as before.
> > >
> > > How can I shrink the log file (having already dome the above).
> > >
> > > --
> > > regards,
> > >
> > > Paul Durdin
> > >
> > > "Tibor Karaszi"
> > <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > > wrote in message news:e1HtntqqDHA.2588@.tk2msftngp13.phx.gbl...
> > > > When you restore a database, you need the same amount of space for
the
> > > mdf,
> > > > ndf and ldf as the size those files had when you did the backup. If
> the
> > > > files were smaller when you did the backup and the restore claims
you
> > need
> > > > more space, you have hit a bug in SQL Server (I haven't heard of
such
> a
> > > bug,
> > > > though). A couple of things:
> > > >
> > > > Make sure that you restore the correct backup. One backup file can
> have
> > > more
> > > > than one backup. Check using RESTORE HEADERONLY.
> > > > Check the size needed according to the backup. Use RESTORE
> FILELISTONLY.
> > > > Make sure you specify the correct FILE = if the backup file has
more
> > then
> > > > one backup.
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > Archive at:
> > > >
> > >
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > > >
> > > >
> > > > "Paul Durdin" <pdurdin@.nospam_hotmail.com> wrote in message
> > > > news:%23Vrx9mqqDHA.2644@.TK2MSFTNGP09.phx.gbl...
> > > > > That's indeed what the error message says - but surely you telling
> me
> > > that
> > > > a
> > > > > 140M database needs 16G to restore it!
> > > > >
> > > > > --
> > > > > regards,
> > > > >
> > > > >
> > > > > "Jorge Lavado" <jorge.Lavado@.cgey.com_NO_SPAM> wrote in message
> > > > > news:0aab01c3aa9b$1255c6d0$a101280a@.phx.gbl...
> > > > > > Hi.
> > > > > > The data partition (e:) need + - 16 GB. free,you only have
> > > > > > + - 6 GB.
> > > > > >
> > > > > > >--Original Message--
> > > > > > >I am trying to move a database from one machine to
> > > > > > another by taking a
> > > > > > >backup from one and restoring it on another. Both
> > > > > > machines are W2k sp3 with
> > > > > > >SQL2k sp3. As far as I can tell they are both
> > > > > > straightforward, standard
> > > > > > >installations (OS and DB), except;
> > > > > > >The target machine has been partitioned into three
> > > > > > (system, apps, and data),
> > > > > > >and the disk is mirrored.
> > > > > > >
> > > > > > >I can take the backup and restore it to a number of
> > > > > > machines, but on the
> > > > > > >machine that I need to restore the database to I get;
> > > > > > >
> > > > > > >Microsoft SQL-DMO(ODBC SQL State: 42000)
> > > > > > >There is insufficient free space on disk volume e:\ to
> > > > > > create the database.
> > > > > > >The database requires 16519462912 additional free bytes,
> > > > > > while only
> > > > > > >6329229312 bytes are available.
> > > > > > >RESTORE DATABASE is terminating abnormally.
> > > > > > >
> > > > > > >The database backup file is only 146M, the data file is
> > > > > > 143M and the log
> > > > > > >file is 15M.
> > > > > > >The data partition (e:) has 8G free space, although the
> > > > > > system/boot
> > > > > > >partition (c:) has about 2G
> > > > > > >
> > > > > > >Does anyone have any ideas how this could be happening?
> > > > > > >--
> > > > > > >regards,
> > > > > > >
> > > > > > >Paul Durdin
> > > > > > >
> > > > > > >
> > > > > > >.
> > > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Error restoring database

When attempting to restore a database, I get the following error:

'Cannot open backup device, (filename).bak, Operating system error 5, access is denied.'

I get similar errors when attempting to attach the database file, it shows up in the Mngt. Tool as 'read only'.

This error does not occur when restoring the same .bak file to other pc's. On the problem pc, we have completely uninstalled and reinstalled Sql Express and Mngt. Tool Express. It occurs whether logged in as full administrator or other user.

check the path where the backup file resides whether you could access it....may be that mite cause the above error|||Which OS are you running on ? using Vista it might be caused that you don′t have elevated rights (although logged on with an administrator account)

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

OS is xp-pro.

The directory of the file is accessible as well.

|||

Are you connecting using SQL Server authentication ? Then you will be impersonated with the SQL Server account which is running the service on the machine. If this one does not have permissions on the file / directory, then you probably will get the Permission denied error message.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Have tried using SQL authentication and windows authentication. How do you determine if the sql server account has particular permissions on the file/directory?

|||

just right click the file/directory and go to security tab and check the permission for the sql service account

Error restoring database

I'm trying to restore a database and getting the following error:
Bypassing recovery for database 'SPS02_Config_db' because it is marked IN
LOAD.
It's been sitting in this state for about 30 minutes now, and I don't know
what to do. This is the main config database for our sharepoint install. I
really need some help.
TIA,
Jerame
Hi,
There are 2 chances for this error:-
1. Restored the database with NORECOVERY
2. Restore staopped in middle.
If the problem is because of second issue then you need to restore the
database from a backup.
If the problem is because of first issue then issue the below command to
make the database online.
RESTORE DATABASE <DBNAME> WITH RECOVERY
Thanks
Hari
SQL Server MVP
"Jerame" <Jerame@.discussions.microsoft.com> wrote in message
news:79D5AB18-1D56-448B-A791-80D7CFF90445@.microsoft.com...
> I'm trying to restore a database and getting the following error:
> Bypassing recovery for database 'SPS02_Config_db' because it is marked IN
> LOAD.
> It's been sitting in this state for about 30 minutes now, and I don't know
> what to do. This is the main config database for our sharepoint install. I
> really need some help.
> TIA,
> Jerame
|||Thanks for the response!
I reran the restore, and it worked that time.
"Hari Prasad" wrote:

> Hi,
> There are 2 chances for this error:-
> 1. Restored the database with NORECOVERY
> 2. Restore staopped in middle.
> If the problem is because of second issue then you need to restore the
> database from a backup.
> If the problem is because of first issue then issue the below command to
> make the database online.
> RESTORE DATABASE <DBNAME> WITH RECOVERY
> Thanks
> Hari
> SQL Server MVP
> "Jerame" <Jerame@.discussions.microsoft.com> wrote in message
> news:79D5AB18-1D56-448B-A791-80D7CFF90445@.microsoft.com...
>
>

Error restoring database

I'm trying to restore a database and getting the following error:
Bypassing recovery for database 'SPS02_Config_db' because it is marked IN
LOAD.
It's been sitting in this state for about 30 minutes now, and I don't know
what to do. This is the main config database for our sharepoint install. I
really need some help.
TIA,
JerameHi,
There are 2 chances for this error:-
1. Restored the database with NORECOVERY
2. Restore staopped in middle.
If the problem is because of second issue then you need to restore the
database from a backup.
If the problem is because of first issue then issue the below command to
make the database online.
RESTORE DATABASE <DBNAME> WITH RECOVERY
Thanks
Hari
SQL Server MVP
"Jerame" <Jerame@.discussions.microsoft.com> wrote in message
news:79D5AB18-1D56-448B-A791-80D7CFF90445@.microsoft.com...
> I'm trying to restore a database and getting the following error:
> Bypassing recovery for database 'SPS02_Config_db' because it is marked IN
> LOAD.
> It's been sitting in this state for about 30 minutes now, and I don't know
> what to do. This is the main config database for our sharepoint install. I
> really need some help.
> TIA,
> Jerame|||Thanks for the response!
I reran the restore, and it worked that time.
"Hari Prasad" wrote:

> Hi,
> There are 2 chances for this error:-
> 1. Restored the database with NORECOVERY
> 2. Restore staopped in middle.
> If the problem is because of second issue then you need to restore the
> database from a backup.
> If the problem is because of first issue then issue the below command to
> make the database online.
> RESTORE DATABASE <DBNAME> WITH RECOVERY
> Thanks
> Hari
> SQL Server MVP
> "Jerame" <Jerame@.discussions.microsoft.com> wrote in message
> news:79D5AB18-1D56-448B-A791-80D7CFF90445@.microsoft.com...
>
>sql

Error restoring database

I get this error when restoring from a backup:
--
...
97 percent restored.
98 percent restored.
99 percent restored.
100 percent restored.
Processed 2811832 pages for database 'develop', file 'internalData' on file
1.
Processed 84368 pages for database 'develop', file 'internal3' on file 1.
Processed 3 pages for database 'develop', file 'intlog' on file 1.
Server: Msg 3624, Level 20, State 1, Line 10
Location: recbase.cpp:1378
Expression: m_offBeginVar < m_SizeRec
SPID: 52
Process ID: 1208
Connection Broken
--
VERSION INFO:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
I really need some code from this db. Any help would be appreciated.
DougSounds like you ran into some kind of data corruption. If
possible, I'd run DBCC CHECKDB, and also try to verify the
integrity of the disk subsystem. But to be sure, a call to
the Microsoft support may be in order.
Linchi
>--Original Message--
>I get this error when restoring from a backup:
>--
>....
>97 percent restored.
>98 percent restored.
>99 percent restored.
>100 percent restored.
>Processed 2811832 pages for database 'develop',
file 'internalData' on file
>1.
>Processed 84368 pages for database 'develop',
file 'internal3' on file 1.
>Processed 3 pages for database 'develop', file 'intlog'
on file 1.
>Server: Msg 3624, Level 20, State 1, Line 10
>Location: recbase.cpp:1378
>Expression: m_offBeginVar < m_SizeRec
>SPID: 52
>Process ID: 1208
>Connection Broken
>--
>VERSION INFO:
>Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)
>I really need some code from this db. Any help would be
appreciated.
>Doug
>
>.
>|||Hi,
Can you provide me the size of the Develop database.
If the size is small , use DTS to transfer all objects to a new database and
then perform a Backup.
Note:
Before performing DTS, use DBCC CheckDB command to identify the corrupted
object.
Thanks
Hari
MCDBA
"Doug Stiers" <stiers@.email.com> wrote in message
news:#lH4BjJ1DHA.2680@.TK2MSFTNGP11.phx.gbl...
> I get this error when restoring from a backup:
> --
> ...
> 97 percent restored.
> 98 percent restored.
> 99 percent restored.
> 100 percent restored.
> Processed 2811832 pages for database 'develop', file 'internalData' on
file
> 1.
> Processed 84368 pages for database 'develop', file 'internal3' on file 1.
> Processed 3 pages for database 'develop', file 'intlog' on file 1.
> Server: Msg 3624, Level 20, State 1, Line 10
> Location: recbase.cpp:1378
> Expression: m_offBeginVar < m_SizeRec
> SPID: 52
> Process ID: 1208
> Connection Broken
> --
> VERSION INFO:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> I really need some code from this db. Any help would be appreciated.
> Doug
>
>|||Hari,
That's what I was going to do but the database is suspect. I cannot get into
the database at all.
Doug
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:usVgJxP1DHA.1744@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Can you provide me the size of the Develop database.
> If the size is small , use DTS to transfer all objects to a new database
and
> then perform a Backup.
> Note:
> Before performing DTS, use DBCC CheckDB command to identify the corrupted
> object.
>
> Thanks
> Hari
> MCDBA
> "Doug Stiers" <stiers@.email.com> wrote in message
> news:#lH4BjJ1DHA.2680@.TK2MSFTNGP11.phx.gbl...
> > I get this error when restoring from a backup:
> > --
> > ...
> > 97 percent restored.
> > 98 percent restored.
> > 99 percent restored.
> > 100 percent restored.
> > Processed 2811832 pages for database 'develop', file 'internalData' on
> file
> > 1.
> > Processed 84368 pages for database 'develop', file 'internal3' on file
1.
> > Processed 3 pages for database 'develop', file 'intlog' on file 1.
> > Server: Msg 3624, Level 20, State 1, Line 10
> >
> > Location: recbase.cpp:1378
> > Expression: m_offBeginVar < m_SizeRec
> > SPID: 52
> > Process ID: 1208
> >
> > Connection Broken
> > --
> >
> > VERSION INFO:
> > Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> > Dec 17 2002 14:22:05
> > Copyright (c) 1988-2003 Microsoft Corporation
> > Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> >
> > I really need some code from this db. Any help would be appreciated.
> > Doug
> >
> >
> >
>|||Hi Doug,
Start the database in emergency mode and perform DTS to a new database.
How to start database in emergency mode,
update sysdatabases
set status=32768
where name='dbname'
Thanks
Hari
MCDBA
"Doug Stiers" <stiers@.email.com> wrote in message
news:uPfRtQR1DHA.1272@.TK2MSFTNGP12.phx.gbl...
> Hari,
> That's what I was going to do but the database is suspect. I cannot get
into
> the database at all.
> Doug
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:usVgJxP1DHA.1744@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Can you provide me the size of the Develop database.
> >
> > If the size is small , use DTS to transfer all objects to a new database
> and
> > then perform a Backup.
> >
> > Note:
> >
> > Before performing DTS, use DBCC CheckDB command to identify the
corrupted
> > object.
> >
> >
> > Thanks
> > Hari
> > MCDBA
> >
> > "Doug Stiers" <stiers@.email.com> wrote in message
> > news:#lH4BjJ1DHA.2680@.TK2MSFTNGP11.phx.gbl...
> > > I get this error when restoring from a backup:
> > > --
> > > ...
> > > 97 percent restored.
> > > 98 percent restored.
> > > 99 percent restored.
> > > 100 percent restored.
> > > Processed 2811832 pages for database 'develop', file 'internalData' on
> > file
> > > 1.
> > > Processed 84368 pages for database 'develop', file 'internal3' on file
> 1.
> > > Processed 3 pages for database 'develop', file 'intlog' on file 1.
> > > Server: Msg 3624, Level 20, State 1, Line 10
> > >
> > > Location: recbase.cpp:1378
> > > Expression: m_offBeginVar < m_SizeRec
> > > SPID: 52
> > > Process ID: 1208
> > >
> > > Connection Broken
> > > --
> > >
> > > VERSION INFO:
> > > Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> > > Dec 17 2002 14:22:05
> > > Copyright (c) 1988-2003 Microsoft Corporation
> > > Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> > >
> > > I really need some code from this db. Any help would be appreciated.
> > > Doug
> > >
> > >
> > >
> >
> >
>

Error restoring database

I'm trying to restore a database and getting the following error:
Bypassing recovery for database 'SPS02_Config_db' because it is marked IN
LOAD.
It's been sitting in this state for about 30 minutes now, and I don't know
what to do. This is the main config database for our sharepoint install. I
really need some help.
TIA,
JerameHi,
There are 2 chances for this error:-
1. Restored the database with NORECOVERY
2. Restore staopped in middle.
If the problem is because of second issue then you need to restore the
database from a backup.
If the problem is because of first issue then issue the below command to
make the database online.
RESTORE DATABASE <DBNAME> WITH RECOVERY
Thanks
Hari
SQL Server MVP
"Jerame" <Jerame@.discussions.microsoft.com> wrote in message
news:79D5AB18-1D56-448B-A791-80D7CFF90445@.microsoft.com...
> I'm trying to restore a database and getting the following error:
> Bypassing recovery for database 'SPS02_Config_db' because it is marked IN
> LOAD.
> It's been sitting in this state for about 30 minutes now, and I don't know
> what to do. This is the main config database for our sharepoint install. I
> really need some help.
> TIA,
> Jerame|||Thanks for the response!
I reran the restore, and it worked that time.
"Hari Prasad" wrote:
> Hi,
> There are 2 chances for this error:-
> 1. Restored the database with NORECOVERY
> 2. Restore staopped in middle.
> If the problem is because of second issue then you need to restore the
> database from a backup.
> If the problem is because of first issue then issue the below command to
> make the database online.
> RESTORE DATABASE <DBNAME> WITH RECOVERY
> Thanks
> Hari
> SQL Server MVP
> "Jerame" <Jerame@.discussions.microsoft.com> wrote in message
> news:79D5AB18-1D56-448B-A791-80D7CFF90445@.microsoft.com...
> > I'm trying to restore a database and getting the following error:
> >
> > Bypassing recovery for database 'SPS02_Config_db' because it is marked IN
> > LOAD.
> >
> > It's been sitting in this state for about 30 minutes now, and I don't know
> > what to do. This is the main config database for our sharepoint install. I
> > really need some help.
> >
> > TIA,
> > Jerame
>
>

Error restoring a database

Please that anybody help me.

I have a phisical device called 'respaldo.bak' inside this file there are three backups of three different databases, The first one is database called 'innovasoft' the second one is 'modelo_de_datos' and the last one is 'Inversiones'.

If I perform a restore of the first one there is no problem, but, when i try to restore the second o third database i get the following error:

TITLE: Microsoft SQL Server Management Studio

Restore failed for Server 'COMPUTO'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

El archivo lógico 'INNOVASOFT_Datos' no es parte de la base de datos 'PROBANDO'. Use RESTORE FILELISTONLY para enumerar los nombres de los archivos lógicos.
Fin anómalo de RESTORE DATABASE. (Microsoft SQL Server, Error: 3234)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3234&LinkId=20476

BUTTONS:

OK

I have to say that i want to restore the database with a different name that's why in the error says 'PROBANDO' , i'm really concern about this problem because it means that i have no backups for my data. I used to use this procedure on sql server 2000 every single day without problems, but on sql server 2005 it doesn't work properly.

Please help me.

Thanks in advance.

Make sure you restore with a file destination plus move.

Here is a quick demo showing how it all works.

Code Snippet

--create 3 test dbs
use master
go
create database db1
go
create database db2
go
create database db3
go

--add dummy data to db2
exec db2..sp_executesql N'select db_name() [db] into db'
go

--backup dbs to disk
backup database db1
to disk='c:\dbs.bak'
with init
go
backup database db2
to disk='c:\dbs.bak'
go
backup database db3
to disk='c:\dbs.bak'
go

--restore db2 to new db
restore database db2_2
from disk='c:\dbs.bak'
with file=2,replace,
move 'db2' to 'c:\db2_2.mdf',
move 'db2_log' to 'c:\db2_2.ldf'
go

--check to make sure we actually restore db2
exec db2_2..sp_executesql N'select * from db'
go

--clean up
drop database db2_2, db3, db2, db1
go

|||

Hi, sorry that i answer until today. Well this was what i done

backup database modelo_de_datos

to disk='d:\datos.bak'

with init

go

backup database inversiones

to disk='d:\datos.bak'

go

backup database innovasoft

to disk='d:\datos.bak'

go

--restore inversiones to new db

restore database db2_2

from disk='d:\datos.bak'

with file=2,replace,

move 'inversiones' to 'd:\database\db2_2.mdf',

move 'inversiones_log' to 'd:\database\db2_2.ldf'

go

There was no problem with the backup commands but with the restore part the result was:

Msg 3234, Level 16, State 2, Line 1

El archivo lógico 'inversiones' no es parte de la base de datos 'db2_2'. Use RESTORE FILELISTONLY para enumerar los nombres de los archivos lógicos.

Msg 3013, Level 16, State 1, Line 1

Fin anómalo de RESTORE DATABASE.

As you can see, the problem is exactly the same i gave when doing the task with the Management Studio

Thanks in advance

|||

I think the data & log filenames may not have the standard naming conventions. Test this out.

Code Snippet

create database db4
on
(
name = db4_data,
filename = 'c:\db4_data.mdf',
size = 10,
maxsize = 40,
filegrowth = 5
)
log on
(
name = db4log_log,
filename = 'c:\db4_log.ldf',
size = 10,
maxsize = 40,
filegrowth = 5
)

backup database db4
to disk='c:\dbs1.bak'
with init
go

RESTORE FILELISTONLY FROM DISK = 'c:\dbs1.bak'
WITH FILE=1;

restore database db4_2
from disk='c:\dbs1.bak'
with file=1,replace,
move 'db2' to 'c:\db4_2.mdf',
move 'db2_log' to 'c:\db4_2.ldf'
go

It throws similar error what you are getting now. Above post from oj, works correctly, all you need to do get the filenames from the backup with the below code and substitute in the backup command.

Code Snippet

RESTORE FILELISTONLY FROM DISK = 'c:\dbs.bak'
WITH FILE=2;

|||

You need to get the correct logical names for the invensiones database.

Do:

Code Snippet

restore filelistonly from disk='d:\datos.bak' with file=2

Once you have the correct logical names you then can move the data files to new location.

|||

Thank you guys for your useful help.

One more question. This works excellent using transact sql is it possible to do the same using Management Studio. Because the problem began when i tried to run the restore under Management Studio.,

Than you.,

|||

Sure. You would want to make sure the logical names are valid. In fact, you should see the same t-sql statement if you launch profiler and watch it.

Error restoring a database

Please that anybody help me.

I have a phisical device called 'respaldo.bak' inside this file there are three backups of three different databases, The first one is database called 'innovasoft' the second one is 'modelo_de_datos' and the last one is 'Inversiones'.

If I perform a restore of the first one there is no problem, but, when i try to restore the second o third database i get the following error:

TITLE: Microsoft SQL Server Management Studio

Restore failed for Server 'COMPUTO'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

El archivo lógico 'INNOVASOFT_Datos' no es parte de la base de datos 'PROBANDO'. Use RESTORE FILELISTONLY para enumerar los nombres de los archivos lógicos.
Fin anómalo de RESTORE DATABASE. (Microsoft SQL Server, Error: 3234)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3234&LinkId=20476

BUTTONS:

OK

I have to say that i want to restore the database with a different name that's why in the error says 'PROBANDO' , i'm really concern about this problem because it means that i have no backups for my data. I used to use this procedure on sql server 2000 every single day without problems, but on sql server 2005 it doesn't work properly.

Please help me.

Thanks in advance.

Make sure you restore with a file destination plus move.

Here is a quick demo showing how it all works.

Code Snippet

--create 3 test dbs
use master
go
create database db1
go
create database db2
go
create database db3
go

--add dummy data to db2
exec db2..sp_executesql N'select db_name() [db] into db'
go

--backup dbs to disk
backup database db1
to disk='c:\dbs.bak'
with init
go
backup database db2
to disk='c:\dbs.bak'
go
backup database db3
to disk='c:\dbs.bak'
go

--restore db2 to new db
restore database db2_2
from disk='c:\dbs.bak'
with file=2,replace,
move 'db2' to 'c:\db2_2.mdf',
move 'db2_log' to 'c:\db2_2.ldf'
go

--check to make sure we actually restore db2
exec db2_2..sp_executesql N'select * from db'
go

--clean up
drop database db2_2, db3, db2, db1
go

|||

Hi, sorry that i answer until today. Well this was what i done

backup database modelo_de_datos

to disk='d:\datos.bak'

with init

go

backup database inversiones

to disk='d:\datos.bak'

go

backup database innovasoft

to disk='d:\datos.bak'

go

--restore inversiones to new db

restore database db2_2

from disk='d:\datos.bak'

with file=2,replace,

move 'inversiones' to 'd:\database\db2_2.mdf',

move 'inversiones_log' to 'd:\database\db2_2.ldf'

go

There was no problem with the backup commands but with the restore part the result was:

Msg 3234, Level 16, State 2, Line 1

El archivo lógico 'inversiones' no es parte de la base de datos 'db2_2'. Use RESTORE FILELISTONLY para enumerar los nombres de los archivos lógicos.

Msg 3013, Level 16, State 1, Line 1

Fin anómalo de RESTORE DATABASE.

As you can see, the problem is exactly the same i gave when doing the task with the Management Studio

Thanks in advance

|||

I think the data & log filenames may not have the standard naming conventions. Test this out.

Code Snippet

create database db4
on
(
name = db4_data,
filename = 'c:\db4_data.mdf',
size = 10,
maxsize = 40,
filegrowth = 5
)
log on
(
name = db4log_log,
filename = 'c:\db4_log.ldf',
size = 10,
maxsize = 40,
filegrowth = 5
)

backup database db4
to disk='c:\dbs1.bak'
with init
go

RESTORE FILELISTONLY FROM DISK = 'c:\dbs1.bak'
WITH FILE=1;

restore database db4_2
from disk='c:\dbs1.bak'
with file=1,replace,
move 'db2' to 'c:\db4_2.mdf',
move 'db2_log' to 'c:\db4_2.ldf'
go

It throws similar error what you are getting now. Above post from oj, works correctly, all you need to do get the filenames from the backup with the below code and substitute in the backup command.

Code Snippet

RESTORE FILELISTONLY FROM DISK = 'c:\dbs.bak'
WITH FILE=2;

|||

You need to get the correct logical names for the invensiones database.

Do:

Code Snippet

restore filelistonly from disk='d:\datos.bak' with file=2

Once you have the correct logical names you then can move the data files to new location.

|||

Thank you guys for your useful help.

One more question. This works excellent using transact sql is it possible to do the same using Management Studio. Because the problem began when i tried to run the restore under Management Studio.,

Than you.,

|||

Sure. You would want to make sure the logical names are valid. In fact, you should see the same t-sql statement if you launch profiler and watch it.

Error restoring a database

Please that anybody help me.

I have a phisical device called 'respaldo.bak' inside this file there are three backups of three different databases, The first one is database called 'innovasoft' the second one is 'modelo_de_datos' and the last one is 'Inversiones'.

If I perform a restore of the first one there is no problem, but, when i try to restore the second o third database i get the following error:

TITLE: Microsoft SQL Server Management Studio

Restore failed for Server 'COMPUTO'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

El archivo lógico 'INNOVASOFT_Datos' no es parte de la base de datos 'PROBANDO'. Use RESTORE FILELISTONLY para enumerar los nombres de los archivos lógicos.
Fin anómalo de RESTORE DATABASE. (Microsoft SQL Server, Error: 3234)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3234&LinkId=20476

BUTTONS:

OK

I have to say that i want to restore the database with a different name that's why in the error says 'PROBANDO' , i'm really concern about this problem because it means that i have no backups for my data. I used to use this procedure on sql server 2000 every single day without problems, but on sql server 2005 it doesn't work properly.

Please help me.

Thanks in advance.

Make sure you restore with a file destination plus move.

Here is a quick demo showing how it all works.

Code Snippet

--create 3 test dbs
use master
go
create database db1
go
create database db2
go
create database db3
go

--add dummy data to db2
exec db2..sp_executesql N'select db_name() [db] into db'
go

--backup dbs to disk
backup database db1
to disk='c:\dbs.bak'
with init
go
backup database db2
to disk='c:\dbs.bak'
go
backup database db3
to disk='c:\dbs.bak'
go

--restore db2 to new db
restore database db2_2
from disk='c:\dbs.bak'
with file=2,replace,
move 'db2' to 'c:\db2_2.mdf',
move 'db2_log' to 'c:\db2_2.ldf'
go

--check to make sure we actually restore db2
exec db2_2..sp_executesql N'select * from db'
go

--clean up
drop database db2_2, db3, db2, db1
go

|||

Hi, sorry that i answer until today. Well this was what i done

backup database modelo_de_datos

to disk='d:\datos.bak'

with init

go

backup database inversiones

to disk='d:\datos.bak'

go

backup database innovasoft

to disk='d:\datos.bak'

go

--restore inversiones to new db

restore database db2_2

from disk='d:\datos.bak'

with file=2,replace,

move 'inversiones' to 'd:\database\db2_2.mdf',

move 'inversiones_log' to 'd:\database\db2_2.ldf'

go

There was no problem with the backup commands but with the restore part the result was:

Msg 3234, Level 16, State 2, Line 1

El archivo lógico 'inversiones' no es parte de la base de datos 'db2_2'. Use RESTORE FILELISTONLY para enumerar los nombres de los archivos lógicos.

Msg 3013, Level 16, State 1, Line 1

Fin anómalo de RESTORE DATABASE.

As you can see, the problem is exactly the same i gave when doing the task with the Management Studio

Thanks in advance

|||

I think the data & log filenames may not have the standard naming conventions. Test this out.

Code Snippet

create database db4
on
(
name = db4_data,
filename = 'c:\db4_data.mdf',
size = 10,
maxsize = 40,
filegrowth = 5
)
log on
(
name = db4log_log,
filename = 'c:\db4_log.ldf',
size = 10,
maxsize = 40,
filegrowth = 5
)

backup database db4
to disk='c:\dbs1.bak'
with init
go

RESTORE FILELISTONLY FROM DISK = 'c:\dbs1.bak'
WITH FILE=1;

restore database db4_2
from disk='c:\dbs1.bak'
with file=1,replace,
move 'db2' to 'c:\db4_2.mdf',
move 'db2_log' to 'c:\db4_2.ldf'
go

It throws similar error what you are getting now. Above post from oj, works correctly, all you need to do get the filenames from the backup with the below code and substitute in the backup command.

Code Snippet

RESTORE FILELISTONLY FROM DISK = 'c:\dbs.bak'
WITH FILE=2;

|||

You need to get the correct logical names for the invensiones database.

Do:

Code Snippet

restore filelistonly from disk='d:\datos.bak' with file=2

Once you have the correct logical names you then can move the data files to new location.

|||

Thank you guys for your useful help.

One more question. This works excellent using transact sql is it possible to do the same using Management Studio. Because the problem began when i tried to run the restore under Management Studio.,

Than you.,

|||

Sure. You would want to make sure the logical names are valid. In fact, you should see the same t-sql statement if you launch profiler and watch it.

Error Restore of Database

HI,

While restoring a database from a backup file having extension .back we are getting the following error
"back is not part of the multiple family media set backup with format can be used to form a new media set"

Please guide me how to handle this error.

Thanks,
KarthikI have got informative link...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=915089&SiteID=1

Error restore backup (sql2000 on w2000 server)

Hi,
I want to restore a backup that we did in another
computer, the name is: CPlus.bak, when I click : Restore...
(I used restore 'from device:').. it issues this
message: "Device activation error. The physical file
name "F:\MSSQL\data\CPlus_Data.MDF" may be
incorrect.File 'CPlus_Data' cannot restored
to 'f:\MSSQL\data\CPlus_Data.MDF'...
...
File 'CPlus_Log' cannot be restored
to 'f:\MSSQL\data\CPlus_Log.LDF'....
give us a light!!!
tia,
AnaHi,
The folder F:\MSSQL\data\?exist, what is BOL.
Thanks,
Ana
>--Original Message--
>Does the new computer have the path F:\MSSQL\data\? If
not
>use "restore...with move" which can be found in BOL.
>>--Original Message--
>>Hi,
>>I want to restore a backup that we did in another
>>computer, the name is: CPlus.bak, when I click :
>Restore...
>>(I used restore 'from device:').. it issues this
>>message: "Device activation error. The physical file
>>name "F:\MSSQL\data\CPlus_Data.MDF" may be
>>incorrect.File 'CPlus_Data' cannot restored
>>to 'f:\MSSQL\data\CPlus_Data.MDF'...
>>....
>>File 'CPlus_Log' cannot be restored
>>to 'f:\MSSQL\data\CPlus_Log.LDF'....
>>give us a light!!!
>>tia,
>>Ana
>>.
>.
>|||BOL = Books On Line . It should be found at Start>Programs>Microsoft SQL
Server>Books Online. Type Restore database into the index to find the syntax
for RESTORE DATABASE
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Ana" <anonymous@.discussions.microsoft.com> wrote in message
news:02d801c3b2ab$1753b760$a101280a@.phx.gbl...
Hi,
The folder F:\MSSQL\data\?exist, what is BOL.
Thanks,
Ana
>--Original Message--
>Does the new computer have the path F:\MSSQL\data\? If
not
>use "restore...with move" which can be found in BOL.
>>--Original Message--
>>Hi,
>>I want to restore a backup that we did in another
>>computer, the name is: CPlus.bak, when I click :
>Restore...
>>(I used restore 'from device:').. it issues this
>>message: "Device activation error. The physical file
>>name "F:\MSSQL\data\CPlus_Data.MDF" may be
>>incorrect.File 'CPlus_Data' cannot restored
>>to 'f:\MSSQL\data\CPlus_Data.MDF'...
>>....
>>File 'CPlus_Log' cannot be restored
>>to 'f:\MSSQL\data\CPlus_Log.LDF'....
>>give us a light!!!
>>tia,
>>Ana
>>.
>.
>|||SQL Server Books Online.
>--Original Message--
>Hi,
>The folder F:\MSSQL\data\?exist, what is BOL.
>Thanks,
>Ana
>>--Original Message--
>>Does the new computer have the path F:\MSSQL\data\? If
>not
>>use "restore...with move" which can be found in BOL.
>>--Original Message--
>>Hi,
>>I want to restore a backup that we did in another
>>computer, the name is: CPlus.bak, when I click :
>>Restore...
>>(I used restore 'from device:').. it issues this
>>message: "Device activation error. The physical file
>>name "F:\MSSQL\data\CPlus_Data.MDF" may be
>>incorrect.File 'CPlus_Data' cannot restored
>>to 'f:\MSSQL\data\CPlus_Data.MDF'...
>>....
>>File 'CPlus_Log' cannot be restored
>>to 'f:\MSSQL\data\CPlus_Log.LDF'....
>>give us a light!!!
>>tia,
>>Ana
>>.
>>.
>.
>|||Does the new computer have the path F:\MSSQL\data\? If not
use "restore...with move" which can be found in BOL.
>--Original Message--
>Hi,
>I want to restore a backup that we did in another
>computer, the name is: CPlus.bak, when I click :
Restore...
>(I used restore 'from device:').. it issues this
>message: "Device activation error. The physical file
>name "F:\MSSQL\data\CPlus_Data.MDF" may be
>incorrect.File 'CPlus_Data' cannot restored
>to 'f:\MSSQL\data\CPlus_Data.MDF'...
>....
>File 'CPlus_Log' cannot be restored
>to 'f:\MSSQL\data\CPlus_Log.LDF'....
>give us a light!!!
>tia,
>Ana
>.
>