?
Solved

Restore SQL mdf file with BackupExec

Posted on 2006-04-10
10
Medium Priority
?
2,704 Views
Last Modified: 2008-01-09
I have to restore an mdf file. I can't rename the existing mdf file , it says file in use.
So I want to restore the databse through BackupExec to another folder.


I keep getting the following error:

"Database Query Failure. See the job log for details."

any help ?
thanks
0
Comment
Question by:jskfan
10 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 16421683
>> I have to restore an mdf file. I can't rename the existing mdf file , it says file in use.

To rename the existing mdf file from the Enterprise Mgr right click the DB --> All tasks --> Detach Database and follow the prompts.

From the Query Analyzer run

sp_detach_db @dbname = 'dbname'  ,  @skipchecks =  'false'


0
 

Author Comment

by:jskfan
ID: 16421779
Actually the database is in use and don't want to disconnect users.
I want to restore to another instance of sql server for now. Itried but I keep getting the following error:

"Database Query Failure. See the job log for details."
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 16421865
Well what's in the log?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:jskfan
ID: 16422334
An error occurred while executing the following query: "RESTORE DATABASE [databasename_DATA] FROM virtual_device = 'databasename_DATA_00__cf7d5090_932b_4bbc_9e37_2545b39e13eb_' WITH NORECOVERY".
 On server: "servername".
 SQL error number: "0C1D".
 SQL error message: "Exclusive access could not be obtained because the database is in use.


FYI : I am trying to restore the from the tape
0
 

Author Comment

by:jskfan
ID: 16422398
well, I managed to restore it to another instance of sql server, but I don't think it's a better idea. because user needs the database to be restored to the original sql server with a different name.

I guess I will have to stop services and rename the existing  database in the server and restore the database from the tape.

By the way , after that can I have them both showing up under SQL Databases node? how can I do that ?

thamks.

0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 300 total points
ID: 16423309
try this

ALTER DATABASE urDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE command
ALTER DATABASE urDatabase SET MULTI_USER


0
 
LVL 4

Assisted Solution

by:csachdeva
csachdeva earned 300 total points
ID: 16423515
Backup Exec has an SQL Agent add-on. With this add-on you can backup SQL Server directly while its still on-line. However, what they only tell you in the small print is that this backups up the "content" of the database and NOT its structure (ie: table, constraints, sprocs etc).

Take SQL Server offline through a script file (as you are suggesting); backup the MDF & LDF files directly; restart SQL Server. There are of few points you should consider though doing it this way. You MUST backup both the MDF & LDF files in order to maintain consistency and not break the database. Restoring a database backed up this way is a little more effort as the current DB needs detaching before you overwrite and re-attach the backed up files. Taking SQL
Server offline in a Production environment every day to do a backup is not really that helpful for the users (unless you really do have inactive periods).

All that being said, the commands to place in your script to stop SQL Server while you back it up are:

NET STOP MSSQLSERVER
rem Do The Backup Here.
NET START MSSQLSERVER

If you are using the SQL Server Agent to run other maintenance tasks then that will also need stopping as it is dependant on SQL Server. However, it should be stopped first and started last or else your script will require user intervention. For Example:
NET STOP SQLSERVERAGENT
NET STOP MSSQLSERVER
rem Do The Backup Here.
NET START MSSQLSERVER
NET START SQLSERVERAGENT

While this last approach is NOT ideal I have to admit to using it myself for speed when backing up our Development, Support and Test databases. It means our SQL Server is down for an average of 15 mins and then the
tape can backup my NTBackup's at its leisure (several hours). I never use maintenance plans as they break too frequently. However, on our production server I schedule my own jobs to backup SQL Server properly.

Regards,
Chetan Sachdeva
0
 
LVL 44

Assisted Solution

by:scrathcyboy
scrathcyboy earned 300 total points
ID: 16423536
restore the same database name, but to a DIIFERENT directory.  This will work with Backup Exec, and you will have your original instance of the DB in another directory, to compare to the new one.
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 600 total points
ID: 16425826
If you were able to restore the DB to another instance then all you have to do is detach is on the other instance, move the files and then  attach it with another name

sp_detach_db @dbname = 'dbname'  ,  @skipchecks =  'false'

move the mdf and ldf files

sp_attach_db @dbname = 'NewDBname' , @filename1 = 'MyFile.mdf', @filename2 = 'MyFile.ldf'

0
 
LVL 38

Expert Comment

by:Jim P.
ID: 16468551
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Independent Software Vendors: 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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

850 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