Restore SQL mdf file with BackupExec

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
jskfanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim P.Commented:
>> 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
jskfanAuthor Commented:
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
Jim P.Commented:
Well what's in the log?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jskfanAuthor Commented:
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
jskfanAuthor Commented:
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
Aneesh RetnakaranDatabase AdministratorCommented:
try this

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


0
csachdevaCommented:
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
scrathcyboyCommented:
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
Jim P.Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.