October 2, 2008 at 8:31 am
I have a server with a corrupt master daatabase on a cluster. How do I go about recreating the master database when I do not have a backup?
October 2, 2008 at 12:56 pm
If you don't have a backup, you'll have to rebuild it. Search BOL for how to.
October 2, 2008 at 7:03 pm
Sorry pal, but rebuilding it is the only fix now. Then you'll need to redo all your security and re-attach all your databases. I hope you have things documented.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 3, 2008 at 7:58 am
I don't mean to sound like a wise-guy or anything. But why don't people back up their databases ?? How many posts have there been over the years about recovering data without a backup ?
Run this to see what other Databases have not been backed up recently (or ever)
[font="Courier New"]SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(char(19), MAX(backup_finish_date), 100), 'NEVER') as LastBackupDate,
case
when type='D' then '** FULL **'
when type='I' then 'DIFFERENTIAL'
when type='L' then 'LOG'
end as Backup_Type
FROM master.dbo.sysdatabases B
LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name
GROUP BY B.name, a.type
ORDER BY B.name, LastBackupDate desc, a.type[/font]
October 16, 2008 at 3:07 pm
I always backup the databases, however this server was only brought to my attention after it failed.
I maanged to recover all the databases and by using a master database from an equivelant system with a different name, updating the server name and then reattaching the other files.
The only thing that is causing the issue is replication. To enyone know the correct order to remove the replication components and reconfigure?
I got it working once on a test system but could not recreate the solution. Still working on it.
October 17, 2008 at 8:03 pm
look on the bright side, restoring master is a pain in the stones anyway.
It's not terribly difficult to reattach database files (assuming they're not toast) and recreate users. just don't expect the passwords to match for SQL Auth.
If you're a clever cat you can reattach or restore the databases and run a cursor through all of them and recreate the logins from the users... then do the same with sp_change_users_logins to synch up the sids.
A good cheap trick is using sp_MSForeachDB and creative query writing to output the syntax for the things I mentioned above.
Good luck, don't forget to get a good backup next time
~Craig
Craig Outcalt
October 18, 2008 at 9:59 am
homebrew01 (10/3/2008)
I don't mean to sound like a wise-guy or anything. But why don't people back up their databases ?? How many posts have there been over the years about recovering data without a backup ?Run this to see what other Databases have not been backed up recently (or ever)
[font="Courier New"]SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(char(19), MAX(backup_finish_date), 100), 'NEVER') as LastBackupDate,
case
when type='D' then '** FULL **'
when type='I' then 'DIFFERENTIAL'
when type='L' then 'LOG'
end as Backup_Type
FROM master.dbo.sysdatabases B
LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name
GROUP BY B.name, a.type
ORDER BY B.name, LastBackupDate desc, a.type[/font]
Nice script here to know the information of backup databases!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply