BE 2010 - Restore SQL DB to .mdf/.ldf directly - it´s possible?

[Renato_Pereira]

Hi,

does anyone if it´s possible restore a db (not master or other internal SQL db's) directly to a .bak or .mdf/.ldf files? Today the developer asked me a backup for some database, so I create a new one (empty obvious) using another name (like 'dbnameTemp') and I´m restoring to this one; after entire process I´ll use the SQL Enterprise Management tool to create .BAK file.

tks,

Renato P

[Backup_Exec1]

Hi

With backup exec ,when you backup SQL datbases with SQL agent ,then it will allow you to restore the mdf and ldf files but with bavkupexec you cannot restore directly as .back file

To restore mdf and ldf file you would have to restore the database to some other database like test but then without that it cannot restore the mdf and ldf file directly you would have to restore to a database name and later on extract the mdf and ldf from physical location were the database is stored.

Thanks

[pkh]

The procedure that you have described does not make sense.  If you need a backup, all you need to do is to backup the database.  Why do you need to restore to another database and then do a backup.

[Vasilis_Peppas]

I think what you are describing is a an agent based backup and a file level restore . This cannot be done. If you do an agent based backup you have to do an agent based restore (which can be done to another sql instance with sql redirection). 

[Renato_Pereira]

Hi pkh,

the developer company is asking for a 'old backup' for that database, how to send to them??

That´s why I´m restoring to another db with different name and then creating a .BAK to send to them.

There is another way to accomplish that?

[bdk1]

You can restore the DB out of place, then detach the DB and send them the MDF and LDF files

[Renato_Pereira]

Ok, tks bdk1.

[pkh · Accepted solution]

Presumably you have access to the instance which contains that database, otherwise you would not be able to back it up.  You can just restore the old backup to that instance using a different name.  You don't have to define the database first.  You just need to specify the new database name in your restore job.

[bdk1]

Which version of SQL are you using?

You can also use SQL management studio to export the database to script and the file will likely be alot smaller than the database.