Solved

Symmentac Backup Exec 2010 SQL restore

Posted on 2011-02-27
33
3,437 Views
Last Modified: 2012-05-11
I am trying to restore a backup from the tape drive through Symantec Backup exec 2010, now the restore job is a SQL database, and when ever i try to restore the Database, it keeps giving me errors, i have pasted the default settings for the restore, let me know which settings i have to change to make it work.
Symmentac-SQL.JPG
0
Comment
Question by:usmansultan
  • 17
  • 15
33 Comments
 
LVL 10

Expert Comment

by:wmeerza
ID: 34994119
what are the errors you get?
0
 
LVL 1

Author Comment

by:usmansultan
ID: 34994373
<?xml version="1.0" encoding="UTF-16"?>
<joblog><job_log_version version="2.0"/><header><filler>======================================================================
</filler><server>Job server: H
</server><name>Job name: Restore 00323
</name><start_time>Job started: Friday, 25 February 2011 at 11:36:37 AM
</start_time><type>Job type: Restore
</type><log_name>Job Log: BEX_HALO_02449.xml
</log_name><filler>======================================================================
</filler></header><restore><filler>======================================================================
</filler><title>Job Operation - Restore
</title><filler>======================================================================
</filler><machine><machine_name>P</machine_name><media_mount_date>
Drive and media mount requested: 25/02/2011 11:36:37 AM
</media_mount_date><media_drive_and_media_info><media_mount_date>
Drive and media information from media mount: 25/02/2011 11:37:38 AM
</media_mount_date><robotic_library_name>Robotic Library Name: HP 2
</robotic_library_name><drive_name>Drive Name: HP 1
</drive_name><slot>Slot: 9
</slot><media_label>Media Label: EYY704L4
</media_label><media_guid>Media GUID: {1cb0921d-d05b-4186-a5c2-3cba3dae66c1}
</media_guid><media_overwrite_date>Overwrite Protected Until: 22/01/2011 10:58:43 AM
</media_overwrite_date><media_append_date>Appendable Until: 31/12/9999 10:00:00 AM
</media_append_date></media_drive_and_media_info><info>Network control connection is established between 172.16.5.6:4684 &lt;--&gt; 172.16.5.13:10000
</info><info>Network data connection is established between    172.16.5.6:4688 &lt;--&gt; 172.16.5.13:2887
</info><set><set_resource_name>P </set_resource_name><restore_target_device>Restoring data to P.
</restore_target_device><tape_name>
Storage Media #1: " Media created 25/12/2010 4:48:00 AM "
Backup performed on 24/12/2010 at 11:03 PM
Backup set #59: " Daily Differential Backup "
</tape_name><agent_started>Microsoft SQL Server Agent: Started
</agent_started><start_time>Restore started on 25/02/2011 at 11:38:27 AM.
</start_time><OperationErrors Type="db_query_failure"><UMI>V-79-65323-3101</UMI> -
An error occurred on a query to database eTrack.
</OperationErrors><OperationErrors Type="error"><UMI>V-79-65323-3101</UMI> - Exclusive access could not be obtained because the database is in use.
</OperationErrors><end_time>Restore completed on 25/02/2011 at 11:38:58 AM.
</end_time><summary><restored_databases>Restored 1 databases
</restored_databases><file_skipped_stat>1 item was skipped.
</file_skipped_stat><new_processed_bytes>Processed 0 bytes in  31 seconds.
</new_processed_bytes><vlm_hist_rateformat2>Throughput rate: 0.000 MB/min
</vlm_hist_rateformat2></summary><filler>----------------------------------------------------------------------
</filler></set></machine></restore><footer><filler>======================================================================
</filler><end_time>Job ended: Friday, 25 February 2011 at 11:40:16 AM
</end_time><engine_completion_status>Job completion status: Failed
</engine_completion_status><filler>======================================================================
</filler><completeStatus>6</completeStatus><errorDescription>Final error: 0xe0008492 - Database Query Failure. See the job log for details.
</errorDescription><errorCategory>Final error category: Resource Errors</errorCategory><umiOriginator>79</umiOriginator><justErrorCode>-536836974</justErrorCode></footer></joblog>
0
 
LVL 10

Expert Comment

by:wmeerza
ID: 34994425
Looks like you are trying to restore over an existing database. In which case you will need to check the 'take existing destination database offline' and 'overwrite the existing database'. This is assuming you are sure you want to overwrite the database?.
You could try redirecting the database to a different name to prove the restore works ok before overwriting it.
Also, you are using the BE differential backup, are you doing any backups from SQL server?
If so you may want to check this article:
http://www.symantec.com/business/support/index?page=content&id=TECH58674
0
 
LVL 1

Author Comment

by:usmansultan
ID: 34994562
I am not going to restore over the existing database, under file redirection i have set up a path to restore it to a temp folder, is that the right thing to do?
The SQL database that i am trying to restore is part of an application, so we do take regular backups through symmantec backup exec 2010. is this what you are asking?
0
 
LVL 10

Expert Comment

by:wmeerza
ID: 34994620
I am assuming you are using the SQL agent for Backup Exec.
'
Ok, on the restore job you need to set the destination under SQL redirection (not file redirection). This will allow you to restore to a new database name on that SQL server. That explains the errors you were getting as it was trying to connect to the existing database!

I also mentioned the backup method as if you are using Backup Exec for all your SQL backups that is fine, the article i refered to explains problems with using mixed SQL server and Backup Exec backups.

Let me know how you go.
0
 
LVL 1

Author Comment

by:usmansultan
ID: 34994713
Thanks, thats right i was trying to restore it to a temp folder not on the live database, i wouldnt do that :-)
can you please guide me the settings that i have to apply under Microsoft SQL redirection window.

Symmentac-SQL2.JPG
0
 
LVL 10

Expert Comment

by:wmeerza
ID: 34994740
Yep,
In the Destination area -
Tick Server: and it will open the box to the right where you put your SQL server name
then
Tick Database: and the box will open, in here you can put the name of db eg. eTrack_Restored

You don't need to specify anything in the 'Destination options' area.

That is all thats required and it will create the db with the new name on your existing server.
Should work fine.
0
 
LVL 1

Author Comment

by:usmansultan
ID: 34994786
So in the first option can i put any server which have SQL on it ?
0
 
LVL 3

Expert Comment

by:Guillermin-go
ID: 34996033
Hi:

      I think you should select one of your sql server and also set a destination-premade sql database.
0
 
LVL 10

Expert Comment

by:wmeerza
ID: 35002111
You can select any server as long as you have the Backup Exec sql agent installed on it. You do not need a pre made database as it will be restored with all properties from the original database.
0
 
LVL 1

Author Comment

by:usmansultan
ID: 35003083
thanks, thats done, can i copy/export it across to somewhere else?
0
 
LVL 10

Expert Comment

by:wmeerza
ID: 35003117
Glad to hear the restore worked ok. Now you have it you can do what you like with it.
0
 
LVL 1

Author Comment

by:usmansultan
ID: 35003384
can i export it to a folder?
0
 
LVL 10

Expert Comment

by:wmeerza
ID: 35003393
sorry, i'm not 100% sure what you mean. Is it now available through SQL as a database you can manage?
 or is it just the contents of that database you want to export?
0
 
LVL 1

Author Comment

by:usmansultan
ID: 35004529
Is it possible to export the database, in excel spreadsheet, i tried to do it, but it started giving errors....
0
 
LVL 10

Expert Comment

by:wmeerza
ID: 35012925
are you using sql 2008? and the mangaement studio? if yu are can you see the contents of the tables ok by right clicking on the table and selecting the top 1000 rows?

0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 1

Author Comment

by:usmansultan
ID: 35013061
no, i am using SQL studio management 2005...
0
 
LVL 10

Expert Comment

by:wmeerza
ID: 35013076
Hmm, i only have 2008 available at the moment. What errors were you getting?
0
 
LVL 1

Author Comment

by:usmansultan
ID: 35053910
Well i guess i will step by step what i am doing and then you can correct me,
i have right clicked on the database which is restored then i click on restore, i left the default settings where it asks from which to copy data from...i left the data source as SQL Native Client, server name is local, database  i left it on restored,  in the next step under destination i selected Access, and then i get this screen, so what should i do over here...
SQL-Errors2.JPG
0
 
LVL 10

Expert Comment

by:wmeerza
ID: 35063046
Do you want to export to access or just a specific table to excel?
In access you just need to browse to an existing new access database you have created. Once selected you then get the option to choose which tables to export. It's pretty much a case of following the wizard. As you are exporting, there is not much chance of breaking things.
I have done a lot with excel but not much with access so I can't step you through in too much detail.
Let me know if you need more help.
0
 
LVL 1

Author Comment

by:usmansultan
ID: 35063248
thats fine, can you please help me through exporting it to excel, step by step....
thanks...
0
 
LVL 10

Accepted Solution

by:
wmeerza earned 500 total points
ID: 35063469
ok,

Right click on your database and select Task-->Export Data (this starts the import/export wizard)
Choose a data source (should show SQL server native client and your database name) click next
Choose a destination (select Excel from destination drop down), in Excel file path choose browse and select an output folder and type in a filename, click next.
Specify Table or Query (Copy data should be checked, click next)
Select source tables and views (put a check mark in the table you want, click next)
Keep clicking next till the end and select finish. (it should run immediately)
My example was on SQL 2008 but 2005 should be similar or the same.

Try that and let me know how you go.


0
 
LVL 1

Author Comment

by:usmansultan
ID: 35063669
Thanks for that, i have run the wizard and i got these errors...
SQL-Errors3.JPG
0
 
LVL 10

Expert Comment

by:wmeerza
ID: 35063985
can you try it again but just using 1 table to see if that works.
0
 
LVL 10

Expert Comment

by:wmeerza
ID: 35064175
it could be that the data types in your tables are not supported in excel. this article talks a bit about it:
http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/b91cf564-500a-4272-8981-c45f45e2a727

It mentions creating the columns in the excel sheet first then using the mapping to destination fields option.
0
 
LVL 1

Author Comment

by:usmansultan
ID: 35064413
thanks for that article, how can do that in excel, it says :you should create excel columns first physically"...how would i know how many i need.......
0
 
LVL 1

Author Comment

by:usmansultan
ID: 35064536
even i export one table at a time i am getting this error on couple of tables...
SQL-Errors4.JPG
0
 
LVL 10

Expert Comment

by:wmeerza
ID: 35064756
what exactly are you trying to achieve. do you need all data from all tables?
When you are in the export wizard there is a stage where you get to review data type mapping, that should show you which tables are not compatible.
0
 
LVL 1

Author Comment

by:usmansultan
ID: 35065733
yes, thats right i am trying export data from all the tables.
0
 
LVL 10

Expert Comment

by:wmeerza
ID: 35065777
I guess the problem you have is that without knowledge of all columns in the various tables (data types) it's hard to expect them to dump straight out. I have also tried it in access and it works much the same way but you still have the problem of data type comptability. It may well be a laborious job of looking at each column in each table to see if it is compatable.
It may be easier to create the table structure in an access database but again there is a bit of work involved.
It is probably best you look at the step at the end of the wizard where you can review data type mapping for each table and see where the problems are, this also allows you to choose how you want to handle the conversion or exclude it.
0
 
LVL 10

Expert Comment

by:wmeerza
ID: 35101293
How are you going with this? do you need more assistance?
0
 
LVL 1

Author Comment

by:usmansultan
ID: 35125437
just a last question, because as from your comments, its bit hard to export it to Access database...
is it possible to export this database to another server? how? what SQL version should be on that server?
0
 
LVL 10

Assisted Solution

by:wmeerza
wmeerza earned 500 total points
ID: 35125630
assuming you are coming from sql2005 (or 2000) you can move it to another 2005 or 2008 without any problems. There are a few ways to achieve this and this article gives a good explanation:
http://support.microsoft.com/kb/314546

There are lots more tips if you google moving sql databases but the above link is pretty straight forward.
Remember also if you move databases to a newer version of sql server you will need to increase the compatability level:
http://msdn.microsoft.com/en-us/library/bb933794.aspx

This article will also help if you need to transfer logins to the different server:
http://support.microsoft.com/kb/918992
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now