Backup *.MDF file only 2

[password99]

Is making a backup of database MDF file adequate or do I also need to backup transaction log file (*.ldf)

If I need to restore the database to an earlier version I would simply replace the working MDF file with the one from backup (and leave the transaction log file alone).

Would that work?

Input is appreciated.

 

[JayKusch]

Yes ... Tlogs are used to get a DB closer to the time it failed.

Thanks

J. Kusch

 

[SQLBill]

The answer is that it depends.

Do you want to be able to restore to a "point-in-time" or do you only care about the data saved when the last backup was made?

To do a point-in-time restore you need to have a full backup and transaction log backups.

If you just want to restore what you last saved, then the easiest way is to backup the .mdf file (remember, you must detach the database before you can backup the .mdf file). Then attach it using sp_attach_single_db. (I suggest backing up both the .mdf and .ldf files).

-SQLBill

 

[password99]

Thanks for the reply.

I was only interested in backing up data until the last backup but would like to know more about restoring "point-in-time"

Let say a user needs to restore to a point-in-time since thir .mdf file got corrupted. What would be my steps to restore their data to a point in time..

Why do you need to detach the database before backing up the .mdg file? I thought you could backup while the DB was in operation.


I cannot use SQL Server built in backup functionality since I am using MSDE and will be building a small tool to backup/restore .mdf and possibly .ldf files.

 

[jeanna143]

I have successfully restored a db in cases where a backup file was not available utilizing attach database in enterprise manager.

 

[SQLBill]

You can backup the database while it's in operation, but that's not the same as backing up the .mdf file.

If you can't use BACKUP DATABASE, BACKUP LOG, RESTORE DATABASE and/or RESTORE LOG commands, then you can not do Point-in-time recovery. Basically, you would backup the database and the logs; then restore the fullbackup followed by the log and use the STOPAT command to set what point-in-time you wanted the restore to stop at.

To successfully backup the .mdf and/or .ldf files they need to be detached first.

-SQLBill

 

[SQLSister]

Another thing you should know is that if you do not backup the transaction log, it will grow until it takes up all your hard drive, unless you periodically truncate it. Any truncation of the transaction log should be followed by an immediate full database backup.

Since backup and restore commands can be done in T-SQL language and not just in Enterprise manager, I see no reason why you wouldn't be able to use them to do the database backups. However, I haven't used MSDE so I can't guarantee that. Read about back-ups if books online to get a better feel for these commands and what they do and how to run them.