SQLTeam.com | Weblogs | Forums

Backup Recovery of SQL Server Database

tsql
sql2012

#1

Hello to all,

After a long time I am once again with you and hope here I will be fix my issue. I had took backup of SQL server database files which has .mdf file format. Now I am looking for the solution through which I can repairs backup files after corruption.


#2

The backup is created in .bak file, my be you have copied physical file(.mdf) and saved it. however you can repair the mdf file by attaching it and check what error message appears in error log. if the header information corrupted then you may have a look on this article: http://www.sqlrecoverytool.com/fix-ms-sql-server-error-5172.html


#3

Thanks jason,

Your direction help me lot and I got the exact trouble.


#5

I recommend that you do not copy MDF file as your backup in future. In most cases the database must be "stopped" in order to allow copying of MDF file, and at other times if you are able to make a copy it will be inconsistent / corrupted when you copy it back against.

If you have copied back your database from MDF file-copy I recommend that you check its consistency:

``sql
USE YourDatabaseName
GO
DBCC CHECKDB WITH NO_INFOMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY


In future I recommend that you use the BACKUP command to make a backup file - I recommend that you turn on COMPRESSION for backups - that file will be smaller than your MDF anyway, and much smaller with Compression, so will save I/O time and disk / tape space.

You might want to have a look at Minion Backup, that will perform backups of all your databases in a structured fashion.