Restore SQL master database from a file

[Moti.Ba]

Hello,

I need to restore the Master DB in my SQL 2k server (with sp3). The
problem is that i don't have SQL backups of this file I only have
general backup of the entire c:\ drive i made using NTBackup. So i
tried all I know and succesfully run the rebuildm utility. Now when I
try to restore it from the general backup (not SQL backup) using
"restore database master from gen_backup" i got error: "The file on
device 'gen_backup' is not a valid Microsoft Tape Format backup set."
RESTORE DATABASE is terminating abnormally.
Any ideas how I can restore it?

[Simon Hayes]

<Mo*****@gmail.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

Hello,

I need to restore the Master DB in my SQL 2k server (with sp3). The
problem is that i don't have SQL backups of this file I only have
general backup of the entire c:\ drive i made using NTBackup. So i
tried all I know and succesfully run the rebuildm utility. Now when I
try to restore it from the general backup (not SQL backup) using
"restore database master from gen_backup" i got error: "The file on
device 'gen_backup' is not a valid Microsoft Tape Format backup set."
RESTORE DATABASE is terminating abnormally.
Any ideas how I can restore it?

Is this the same problem you had a few months ago?

http://groups.google.co.uk/groups?hl...ews.bluewin.ch

If you have the master.mdf and mastlog.ldf files (if you can restore them
from your NTBackup backup set), then you can try the process I described
earlier, although I haven't tried it myself, so I can't say for sure that it
will work.

Whatever happens, you need to set up proper MSSQL backups to make sure you
don't have this problem again - you can use the maintenance plan wizard to
get started quickly. You should also check out the Books Online section on
"Backing Up and Restoring Databases".

Simon

[Erland Sommarskog]

(Mo*****@gmail.com) writes:

I need to restore the Master DB in my SQL 2k server (with sp3). The
problem is that i don't have SQL backups of this file I only have
general backup of the entire c:\ drive i made using NTBackup. So i
tried all I know and succesfully run the rebuildm utility. Now when I
try to restore it from the general backup (not SQL backup) using
"restore database master from gen_backup" i got error: "The file on
device 'gen_backup' is not a valid Microsoft Tape Format backup set."
RESTORE DATABASE is terminating abnormally.
Any ideas how I can restore it?

You cannot use the RESTORE command in SQL Server to read something
from a NT Backup, as far as I know. You first need to restore the
master.mdf and master.ldf from backup with NT Backup.

However, it is far from certain that the files are usuable, because if
you backup database files with NT backup, there is no guarantee that they
are transactionally consistent. You can get all sorts of rubbish. That is,
if SQL Server was running when you ran NT backup. If SQL Server wasn't
running the backups should be OK.

There are somewhat better odds with master than other database, because
there is little update traffic in that database, and the database is small.

But nevertheless, before you actually try to use it as a master database,
just try to attach it as a regular user database first and run DBCC CHECKDB
on it.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp