Direct Backup of .mdf & .ldf

[Dharamveer]

Hi friends,

We had SQL server. Its one instance is splited on many drives path.

we are able to backup it throug SQL.

we are able to restore on same server.

BUT

My challange come when we tried to restore it on other server, I think it was not able to detect those diff location (path) as define in original server.

so we tried to backup drive wise & restore all drive one-by-one but it skip .mdf & .ldf files in backup job.

Kindly suggest a way out to restore it on different server.

I had heared, there are some registry modification to enable symantec backup 2012 directly from drive(flat file) without skip .mdf & .ldf

[pkh · Accepted solution]

You cannot backup the .MDF and .ldf files because they are automatically excluded by AFE. This is by design. To restore your databases to other servers or paths, use the SQL redirection facility. When you use SQL redirection, the .mdf and .ldf files will always end up in the same directory. You can move the .ldf files to other directories after the restore.

[Dharamveer]

Hi pkh,

are you sugesting to restore my 1TB data to new server. and after sucssesful restore change the drive to available paths.

but we don't have 1 TB of free space. all we had is 3 drive with 400 GB on each.

[pkh]

It depends on what you are trying to restore.  If you want to restore the existing databases to some point in past to your backup, then you do a in-place restore without any redirection.  You said that you want to restore the databases to another server.  You have to use redirection to do so.  If you do not have sufficient space on the new server to restore your databases, then you would have to find more space.  Space problem is not a BE problem.

[Dharamveer]

I knew space is not BE issue, all i was looking to restore is 1TB SQL data to new servr. which is currently splited on to 3 drives C,D & E in production server.

1) Restore to new server with 1TB is possible

2) Restore to Identical server is possible (C,D & E have suficent space)

but my Question is as follows

3) Is it possible to have succesful restore on new server with D,E & F drives with nearly 300 GB available.on each. if I performe restore to 1TB usb drive how can I direvt it to D,E F respectivily.........

I hope my question is clear.....

[Dharamveer]

I knew space is not BE issue but there should be some workaround for it???/

[VJware]

Possible option is to disable Active File Exclusion via the registry, so that the .mdf & .ldf files can be backed up as flat-files - http://www.symantec.com/business/support/index?page=content&id=TECH92375

[pkh]

There is no guarantee that backing up the .mdf and .ldf files as flat files and then restoring them as flat files, would result in a working database.