[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


SQL 2000 database Restore error from TAPE

Posted on 2007-11-20
Medium Priority
Last Modified: 2013-12-01
I am trying to restore SQL 2000 database called 'CBAS' from tape using backupexec 9.1 but i am getting following error message. I can restore others SQL databases on the same tape on the same server but it is just with this perticular database 'CBAS'. I think according to the following error it seems that this database backup contains more than one log files and while restoring, backupexe try to combine into one log file 'cbas_log.LDF' and fails.

I donot know how and where to use WITH MOVE clause under veritas backupexec.

Please help me to solve this issue and advise a working solution. thanks.

An error occurred on a query to database CBAS.
File 'T:\CBASLOG\cbas_log.LDF' is claimed by 'CBAS_1_Log'(4) and 'CBAS_Log'(2). The WITH MOVE clause can be used to relocate one or more files.

thanks & regards
Question by:tech2010
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
LVL 18

Expert Comment

ID: 20322617
First try to find out what is in the backup set using
    RESTORE FILELISTONLY (http://technet.microsoft.com/en-us/library/ms173778.aspx)
    RESTORE HEADERONLY (http://technet.microsoft.com/en-us/library/ms178536.aspx)

Then - as you stated - try to restore using the move clause (see code snippet).

hope this helps ...

FROM    <device> 
WITH    MOVE N'Yveau' TO N'Z:\Yveau.mdf'
,       MOVE N'Yveau_log' TO N'Z:\Yveau_log.ldf'
,       ...

Open in new window


Author Comment

ID: 20323316
Where you want me to run RESTORE FILELISTONLY command, as i have two seperate servers, one is SQL 2000 where i am trying to restore database and second which is my tape backup server from where i am trying to restore from?

LVL 18

Expert Comment

ID: 20323564
SQL Server, and I asume you have a backup device defined that is connected to the backupexec 9.1 ? Or does Backupexec runs the backup ?

If the latter is the case, I have no experience with that ...
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks


Author Comment

ID: 20326419
Backupexec runs the backup for SQL via backupexec agent on SQL box. Do I need to define backup device on SQL box which then connect to backup tape server? Also how to define backup device ?


Author Comment

ID: 20326599
Please ignore my previous message.

I have one SQL 2000 server directly connected to backup device, i have created backup device under sql enterprise manager but when i clicked on 'view contents' it gives me error message "Error 3241 Media Family on device '\\.\Tape0' is incorrectly formed, SQL server canot process this media family. RESTORE HEADERONLY is terminating abnormaly"

please tell me how should i fix this issue, i have SQL SP4 already installed as well.

LVL 18

Expert Comment

ID: 20330527
... as I said, I don't connect my SQL Servers directly to the BackupExec. I make a local backup to disk and than tell BackupExec (or currently NetBackup) to come and get the files in the backup folder as soon as I'm done making the backups.

... you get the error when the correct tape is loaded ?

Author Comment

ID: 20331168
yes :(
LVL 18

Expert Comment

ID: 20331217
... sorry, really have no experience with your setup ... I hope some of the BackupExec guys will pick up from here ...

Accepted Solution

DennisPost earned 1500 total points
ID: 20721838
Hi Tech2010,

Just to let you know, I have a similar problem. Here's my question.
I also added a pointer in my question to this one.

Could you please post the tsql you used when your error occured.
I have not been able to restore any database from tape, I need to redirect and/or rename it.

Here's what I found about the MOVE cluase in BOL: (RESTORE DATABASE TSQL Reference)
MOVE 'logical_file_name' TO 'operating_system_file_name'
Specifies that the given logical_file_name should be moved to operating_system_file_name. By default, the logical_file_name is restored to its original location. If the RESTORE statement is used to copy a database to the same or different server, the MOVE option may be needed to relocate the database files and to avoid collisions with existing files. Each logical file in the database can be specified in different MOVE statements.
Note  Use RESTORE FILELISTONLY to obtain a list of the logical files from the backup set.
For more information, see Copying Databases.
Is a placeholder that indicates more than one logical file can be moved by specifying multiple MOVE statements.

D. Restore a database and move files
This example restores a full database and transaction log and moves the restored database into the C:\Program Files\Microsoft SQL Server\MSSQL\Data directory.

   FROM MyNwind_1
      MOVE 'MyNwind' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewNwind.mdf',
      MOVE 'MyNwindLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewNwind.ldf'
   FROM MyNwindLog1

Author Closing Comment

ID: 31410181

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
This tutorial will walk an individual through the process of installing of Data Protection Manager on a server running Windows Server 2012 R2, including the prerequisites. Microsoft .Net 3.5 is required. To install this feature, go to Server Manager…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question