One of the hard drive in my office’s NT server was crashed two weeks ago. I had installed MS-SQL in c: and the data was stored in d:. The hard crased was c:. Thus I replaced the crashed hard drive and re-install NT server and SQL server again.
The problem now is how to restore the data from the .mdf files located in d: (there’s no other backup). I’ve tried to “restore” but MS-SQL server reported that it was not a valid backup file format and not allowed me to restore the database. Could anybody help? Thanks!!
Important note: before trying to get your databases back, make sure you have a copy of all your .mdf and .ldf files somewhere safe.
An .mdf file is a file which contains the actual data. You cannot “restore” anything from them, because they are not backups, thus Microsoft SQL Server tells you they are not valid backup files.
What you can do is to recreate the databases and bind them to the actual .mdf files. This is done by right-clicking on the database in SQL Server Management Studio and going to:
Properties > Files > Database files > Path/File Name
Microsoft SQL Server uses two types of files: .mdf contains the data, and .ldf contains the logs. It is crucial to recover the log files side by side with the data files, especially in the circumstances where the database engine couldn’t be shut down correctly, such as when the server crashes. The fact that it’s the C: disk which crashed while the data files were on D: is irrelevant here: what matters is that the SQL Server engine wasn’t terminated correctly.
Once you get the databases back, there is an additional step that you might have noticed now is crucial: configure regular backups on a NAS/SAN, as well as off-site. You do that by going to:
Management > Maintenance Plans > New maintenance plan
and adding Back Up Database Task to the plan. Configure several maintenance plans with different options (for instance one complete backup once per month and one incremental one twice per day, as well as a weekly off-site backup) depending on your needs.
my office’s NT server
Windows NT support ended twelve years ago. It is about time to upgrade to a more reliable version of Windows or move to an open source operating system if money is a concern. By keeping an unsupported version which doesn’t receive security patches any longer, you are introducing a security threat, as well as potential compatibility issues with other products (and thus management nightmare).