May 27, 2010 at 1:44 pm
Someone gave me a file to restore their DB. They sent a MDF file. I really thought these files were hard to even remove from the data folder. I don't know how they sent it.
Can i restore a Db with this file?
May 27, 2010 at 1:48 pm
You can ATTACH a .mdf file, but not restore from it. Attaching it will create the database in the state in which it was detached. (Google: sql server attach)
--J
May 28, 2010 at 7:23 am
Thanks that is what i want to know.
May 28, 2010 at 8:01 am
Jacob Pressures (5/27/2010)
these files were hard to even remove from the data folder.
take the database offline and then take out the mdf
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 28, 2010 at 8:20 am
Definately not the way it should be done. They should have taken a backup of the database and sent you that. Depending on how the mdf was copied, you may not be able to attach it without the corresponding ldf file. Just be warned.
May 28, 2010 at 8:32 am
I have a question regarding this.
Recently, our team reinvestigated our maintenance plan. With the addition of a SAN, and the movement of our database files to the SAN, we took another look at how our backup are being performed, and how they should be performed (and where they should be stored).
Everyone (but me) was really big on using SAN snapshots to back up the database. Personally, I think they're using new technology just for technology's sake, without really analyzing how useful it truly is. The SAN snapshot works just like a database snapshot. Since the database files are single, large files however, a single database change invalidates the entire MDF file in the snapshot. I think its inefficient.
I also think it's inefficient to restore. Rather than simply restoring from a BAK file in a well-known location, I have to mount the snapshot (I don't even know how to do that yet). Then I have to detach/reattach the MDF file. I don't even know if I can restore transactions after that reattach. All-in-all, it seems really complicated, with no real benefit.
I have not yet tested the restore of the database (mostly because they haven't actually shown me how to mount the snapshot). In the absense of this testing, we are still performing traditional backups.
I'd like to make a case against these rediculous snapshot backups, in favor of traditional backups. I don't think it's a good solution, but I need some concrete information to go to my boss with. If I can't restore a transaction log, that's obviously a dealbreaker. If it is more difficult or takes significantly more time, that might help sell it too. Right now, all I've got is that it's more complicated with no real benefit. That might help, but I'm hoping I can bring more to the table.
Can someone help? Or does anyone thing SAN snapshots are actually a good way to perform database backups?
--J
May 28, 2010 at 9:52 am
SAN snapshots are good for speed, if you need to quickly debug things. We used to use them to get Customer support people a quick look at a current db, or for QA/Dev environments.
In terms of recoverability, here's the issue. A single change invalidates the SQL full backup as well since you now need a log backup. However with a SQL backup, you can apply a log backup to recover work.
With a SAN backup, you can't. You're attaching a MDF, and there isn't a way to put that in recovery mode and apply log backups. You still need to start with a full.
SAN snapshots have their place, but if you are trying to recover in between full backups, don't use them.
May 28, 2010 at 3:00 pm
Steve Jones - Editor (5/28/2010)
SAN snapshots are good for speed, if you need to quickly debug things. We used to use them to get Customer support people a quick look at a current db, or for QA/Dev environments.In terms of recoverability, here's the issue. A single change invalidates the SQL full backup as well since you now need a log backup. However with a SQL backup, you can apply a log backup to recover work.
With a SAN backup, you can't. You're attaching a MDF, and there isn't a way to put that in recovery mode and apply log backups. You still need to start with a full.
SAN snapshots have their place, but if you are trying to recover in between full backups, don't use them.
This really depends upon the SAN and how they have implemented their solution. Using Netapp Snap Manager, you end up restoring from the snapshot the same way you would restore from a traditional backup. This gives you the ability to apply the transaction logs as you would anything else.
I pushed back on using this solution for different reasons. First, their tool would fail if you tried to run another backup at the same time. This would cause problems with transaction log backups and would require that we stop running transaction log backups until all databases had been backed up and validated. Second, the snapshots are saved in the same array as the data files - and if you lose the array you lose your backups. Third, the transaction logs had to be managed by their tool - but there was no advantage to using their tool over native (e.g. no space savings, and still stored on the same array).
Since we had network backup storage available, and a private backup network - we used Hyperbac to compress and backup to the network share instead.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 28, 2010 at 3:31 pm
Didn't know that they could integrate with SQL to set up for recovery.
The reasons you give are pretty good ones for not using their tool, especially if there is a failure between the time that the backups are started and validated, and the next potential log backup. I would assume that's a small window, but it would stink if you lost the backups on disk because the SAN died for some reason.
I hate people selling these add-ins for SAN technology, especially if there are potential issues. Have you ensured that you can recover from the snap?
May 28, 2010 at 3:57 pm
Steve Jones - Editor (5/28/2010)
Didn't know that they could integrate with SQL to set up for recovery.The reasons you give are pretty good ones for not using their tool, especially if there is a failure between the time that the backups are started and validated, and the next potential log backup. I would assume that's a small window, but it would stink if you lost the backups on disk because the SAN died for some reason.
I hate people selling these add-ins for SAN technology, especially if there are potential issues. Have you ensured that you can recover from the snap?
I no longer work for that company, and we did not implement or use the snap technology for our backups.
You would think the snap process wouldn't take long - and you would be right, except this product would freeze one database, do some validation (few seconds), move to the next, etc... It could take several minutes - which is not a long time when you are talking about backups.
However, the tool also performed a restore from the snapshot - which could take several hours and block all other backups until it was finished.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 28, 2010 at 4:06 pm
You have the reasons that I'd give, and include the risk of keeping stuff together. Ultimately, however, you'd test both, then present a case to management if you and the sysadmins can't agree and let someone sort it out.
I'd recommend against it, but no good reason.
I would say that the price paid for the software is irrelevant. That money is spent, so you make the best decision from this point forward, and if it's not to use the software, you might save on maintenance in the future.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply