Master Db Corrupts
Dec 6, 2005What if the master db gets corrupted can we still re-cover our user databases
Thanks
What if the master db gets corrupted can we still re-cover our user databases
Thanks
This one is a needle in the haystack. I have a fairly robust Dell server running WIn 2003, 16 gig of memory, SQL SP3a using log shipping failing over to a local server and then back to my location across a WAN, using a vb6 app with about 60 users, a 24X7 shop Mon-Sat. 2 times in the past month the production server has created a corrupt log ship file which gets applied to my failover server and then corrupts the failover DB to both failover servers. Of course it is my 20 gig db and the only time I can recreate the failover is Saturday night. The production DB is not corrupt and is fine. Has anyone ever run into this before. Currently working with MS and Dell, but hoping someone had experience with this !
View 2 Replies View RelatedHi all,
I'm new to the forum, and fairly new to SQL, so please forgive me if I say something that sounds stupid at any time. I have been looking around this forum (and others) while trying to diagnose my current issues, and there seems to be a really good amount of knowledge about here so I thought I'd try my luck with a post.
We've been running SQL 2000 for around 2 years now, and had very few problems until we suffered a torn page error around 6 months ago (just before the time I started looking after the server).
Since then we've been getting intermittent database corruption (I implemented a nightly DBCC job as part of DB maintenance) and it seems to occur after our weekly index maintenance has run (also a scheduled DBMaint job). I have just confirmed this by restoring nightly backups to a test database - the current round of corruption has occured sometime between the Saturday and Sunday night backups (indexing runs at 1 am Sunday morning, and is the only activity between these two backups)
We have 9 small (all less than 200MB) databases which are used faily lightly by our salesforce. The corruption is occurring on different databases each time.
We are running SQL 2000 (SP4) on a Windows 2003 clustered server (2 x DL380s and an MSA500 G1) running in failover mode (active-passive). The raid 5 array does utilise disk caching, and I have wondered if this is involved somehow.
Our hardware support comany have checked the server thoroughly for HW issues, but have come up blank.
Here is the DBCC output from one of our corrupt databases from this morning:
dbcc checkdb (casestatus) WITH ALL_ERRORMSGS, NO_INFOMSGS
-----------------------
Server: Msg 2533, Level 16, State 1, Line 1
Table error: Page (1:20040) allocated to object ID 117575457, index ID 0 was not seen. Page may be invalid or have incorrect object ID information in its header.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 117575457, index ID 1. Page (1:20040) was not seen in the scan although its parent (1:19808) and previous (1:20039) refer to it. Check any previous errors.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 117575457, index ID 1. Page (1:20041) is missing a reference from previous page (1:20040). Possible chain linkage problem.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 117575457. The text, ntext, or image node at page (1:12449), slot 1, text ID 1190656606208 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 117575457. The text, ntext, or image node at page (1:12449), slot 3, text ID 1190656671744 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 117575457. The text, ntext, or image node at page (1:12449), slot 5, text ID 1190656737280 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 117575457. The text, ntext, or image node at page (1:12449), slot 7, text ID 1190656802816 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 117575457. The text, ntext, or image node at page (1:12449), slot 9, text ID 1190656868352 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 117575457. The text, ntext, or image node at page (1:12449), slot 11, text ID 1190656933888 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 117575457. The text, ntext, or image node at page (1:12449), slot 13, text ID 1190656999424 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 117575457. The text, ntext, or image node at page (1:12449), slot 15, text ID 1190657064960 is not referenced.
CHECKDB found 0 allocation errors and 11 consistency errors in table 'Raw:LifeStatusFP' (object ID 117575457).
CHECKDB found 0 allocation errors and 11 consistency errors in database 'CaseStatus'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (CaseStatus ).
-----------------------
The funny thing is that this particular database isn't even in use any more - I've had it in single user mode for about 2 months (and the users don't have any access to the back end stuff).
If anyone can shed any light on what's happening, I'd really appreciate this.
Thanks
Dave
I have an ftp task that grabs files from a remote dir ( *.csv ). However, it seams that the ftp task is corrupting some of my files. Has anyone else seen this? Is there something I can do? (These are grabbed as binary)
For example, here is the original on the remote server:
25316,<ACTUAL>,296,917,48,10,1,2006-03-29,UPLOADER
25319,<ACTUAL>,63060,106,64,10,1,2006-03-29,UPLOADER
25300,<ACTUAL>,63060,206,64,10,1,2006-03-29,UPLOADER
29743,<ACTUAL>,56060,106,96,10,1,2006-03-29,UPLOADER
29744,<ACTUAL>,56060,206,96,10,1,2006-03-29,UPLOADER
25315,<ACTUAL>,261,607,48,10,1,2006-03-29,UPLOADER
29749,<ACTUAL>,45030,103,96,10,1,2006-03-29,UPLOADER
29750,<ACTUAL>,45030,203,96,10,1,2006-03-29,UPLOADER
29751,<ACTUAL>,63030,303,64,10,1,2006-03-29,UPLOADER
But here is the result on the local machine. You can see that the last 4 lines are duplicated (plus the last character of the preceding line):
25316,<ACTUAL>,296,917,48,10,1,2006-03-29,UPLOADER
25319,<ACTUAL>,63060,106,64,10,1,2006-03-29,UPLOADER
25300,<ACTUAL>,63060,206,64,10,1,2006-03-29,UPLOADER
29743,<ACTUAL>,56060,106,96,10,1,2006-03-29,UPLOADER
29744,<ACTUAL>,56060,206,96,10,1,2006-03-29,UPLOADER
25315,<ACTUAL>,261,607,48,10,1,2006-03-29,UPLOADER
29749,<ACTUAL>,45030,103,96,10,1,2006-03-29,UPLOADER
29750,<ACTUAL>,45030,203,96,10,1,2006-03-29,UPLOADER
29751,<ACTUAL>,63030,303,64,10,1,2006-03-29,UPLOADER
R
25315,<ACTUAL>,261,607,48,10,1,2006-03-29,UPLOADER
29749,<ACTUAL>,45030,103,96,10,1,2006-03-29,UPLOADER
29750,<ACTUAL>,45030,203,96,10,1,2006-03-29,UPLOADER
29751,<ACTUAL>,63030,303,64,10,1,2006-03-29,UPLOADER
I have installed an application that uses MSDE on a server running Windows 2000 with SQL 7 already installed; my application, MSDE and SQL7 all continue to function properly after install.
The problems start when I uninstall MSDE ; the uninstall of MSDE seems to corrupt the SQL 7 installation. After using Control Panel/Add-Remove Programs to uninstall MSDE I get an error message box with heading SQL DMO and error message Error 340 - General Error. I can no longer see the SQL Server registration and cannot reregister the server either!
My quesiton is this? Is there a process of uninstalling MSDE that will leave the SQL 7 installation in the same state as it was prior to installing MSDE? Obviously, the uninstall of MSDE seems to corrupt the SQL 7 installation - how can I ensure that this does not hapen? Does the uninstall of MSDE remove or reregister COM objects that are required by SQL 7 and if so how can we get around this?
We are working with a client and are using Fuzzy Group transform for de-duping, and hierarchy creation for a national account list.
View 4 Replies View RelatedWe are not certain if this has happened due to the SSIS FTP Task, but incidently the Excel file that is being copied from the FTP site using an SSIS FTP Task got corrupted a couple hours after the package was scheduled as a SQL Server Agent Job on SQL Server 2005.
I had a SQL Server 2000 DTS package doing the same thing, but it was never an issue then. I was using the FTP Task there along with an Excel Data source in that and has been working for a couple years atleast with never any corruption related issues.
In the SSIS SQL Server 2005 package I am using an FTP Task with an Excel Connection Manager and Excel source and the Excel file got corrupted within a couple hours of the package being scheduled as a SQL Server Agent job.
Has anyone experienced this issue? Any inputs will be appreciated.
Just as an fyi, the excel file has a lot of vlookups.
Thanks,
MShah
I got a File with sales orders and their details.
Step 1. First I am filtering the Sales Order information and inserting it in my Sales Orders table.
Step 2.Then I am filtering the details from the sales Order and inserting them in the respective table.
My Problem is that the Sales Order File does not contain the Sales Order key (ID), this is generated by the SQL Server. How can get it in order to use it in the second step? I need it because it is a foreign key in the details table.
Any Idea?
Hello,
I'm new to SQL and need help with a query. Not sure if this is the right place.
I have 2 tables, one MASTER and one DETAIL.
The MASTER table has a masterID, name and the DETAIL table has a detailID, masterID, and value columns.
I want to return a populated MASTER table with entries based on the DETAIL.value.
SELECT MASTER.*
FROM MASTER
WHERE DETAIL.value > 3
This is a simplified version of my problem. I can't figure out how to set the relationship between MASTER.masterID and DETAIL.masterID. If I do an INNER JOIN, the number of results are based on the number of DETAIL entries. I only want one entry per MASTER entry.
Hope this makes sense.
How can I do this?
GrkEngineer
We already integrated different client data to MDS with MS Excel plugin, now we want to push back updated or new added record to source database. is it possible do using MDS? Do we have any background sync process to which automatically sync data to and from subscriber and MDS?
View 4 Replies View RelatedWhat would the reasons when some of user tables are in master db/user table section as well as those tables are in user db/user table section listed.
Thanks,
Glen
Hi All,
We will be transfering a big application onto another server. Is it possible to copy the Syslogins with passwords onto the new server from the old one it resides. I do not want to restore Master onto the new server because the Server name will be different.
if i want to create a database, using:
CREATE DATABASE name
does it need the USE MASTER before the line? a database MUST be created using Master??
i ask this because in an example i need to create a database, so i use the next code:
CREATE DATABASE Banco
ON
(NAME='P_Banco',FILENAME='C:Documents and SettingsVictorEscritorioBancoP_Banco.mdf')
LOG ON
(NAME='P_Banco_Log',FILENAME='C:Documents and SettingsVictorEscritorioBancoP_Banco_Log.ldf')
USE Banco <-- i wrote this, because i tought i could create tables and stuff directly in my previously declared database, but it sends this error:
"Could not locate entry in sysdatabases for database 'Banco'. No entry found with that name. Make sure that the name is entered correctly."
anyway, i could do it, but first creating the database, and after that, in a separate code creating the tables with USE Banco, but my question is..why is that? or i miss something in my sql code??
Hi Y'all,
What are the advantages of placing stored procedures in the master database? Can i always use all the database-names on the instance?
Thanks!
Hi all,
I am having trouble modifying the select statement(s) below to return me only unique rows instead of duplicated rows. I give up. Can someone please jump in and give it a shot? Please help out. Thanks.
blumonde
************************************************************************************************sb.Append("SELECT TOP (@PageSize) TopicID, TopicName, Message ");
sb.Append("FROM (Select TOP (@CurrentPage*@PageSize) ROW_NUMBER() OVER (ORDER BY ftp.DateEntered DESC) AS Row, ftp.TopicID, ftp.TopicName, ft.Message ");sb.Append("FROM forumTOPIC ftp, forumTHREAD ft, forumCategory cat, forumTechnology tn ");
sb.Append("WHERE tn.TechnologyID = cat.toTechnologyID AND cat.CategoryID = ftp.toCategoryID AND tn.TechnologyName LIKE @KeyForum AND ftp.TopicID = ft.toTopicID AND FREETEXT(ft.Message, @KeyWord) ");sb.Append("GROUP BY ftp.TopicID, ftp.TopicName, ftp.DateEntered, ft.Message ");
sb.Append("ORDER BY ftp.DateEntered DESC) as t1 ");sb.Append("WHERE Row BETWEEN (@CurrentPage-1)*@PageSize+1 AND (@CurrentPage*@PageSize) ");
sb.Append("ORDER BY t1.Row ASC");
**********************************************************************************************************
I had installed a third-party software on my server and that corrupted my Master DB. Over the weekend when we bought SQL server down, we could not bring it back up. I had to re-install SQL server and then restore all my databases from the backup. Is there a better way of doing this???
Curiously
Someone who calls himself a DBA
Hi Friends ...
I need your advice and help on the following problem . Say for example I have a SQL 7.0 Server A with 30 databases and i am in the process of transfering these databases to another system which is SQL Server B with 7.0 again .
Although the Databases can be copied by various means , can i restore Master database from the Server A to Server B so that my Server B will have all the database and login information as that of Server A.
As Master database is system database , it won't allow to be dropped and hence you cannot restore .
Is there any other way to restore Master database from One SQL server 7.0 to another with the same SQL version .
Any kind of assistance will be of great help to me . Many thanks in advance .
Kind Regards
Sneha
There is a table in the master DB where the sourcode of the SP's is saved, dies anyone knows the table's name?
Regards,
Hi folks,
In SQL 7 when you query
select name, crdate from sysdatabases where name like 'xxx3%'
You get a reply with the rows of matching databases in sysdatabases. But in SQL 2000 the same command throws an error saying that the object sysdatabases is wrong. Here is the modified command
select name, crdate from master.dbo.sysdatabases where name like 'xxx3%'
Now my problem is I can include the latest piece of code and go further. But my older version of product will have this problem and will fail. Is there anyway that I can dothis on SQL 2000 to be compatible with my old product.
regards,
mahesh,
Hi,
I have a question regarding master database.I know if a master database is
corrupt we need to rebuild the master.One of the guys suggested us to
maintain a copy of master data file and log file in diffrent directory and
when master database is corrupt we can copy the files to the actual location
and restart the sql server and this should fix the issue and by this method we can save the time of rebuilding the master database.I don't know if this works.Can anyone please tell me if this works and also disadvantages if any with this method?
thanks
Mohan
Various books read suggest that the Master
database be backed up when it is changed.'
What activities does a DBA perform (or perhaps)
a user) will cause the Master database to
change?
I am aware that information about the addition
of new databases is kept in the Master.
TIA Gary
Hi,
how long a rebuildm.exe takes?
TIA
When I try to restore master, I keep getting an error message, while it is in the process of restoring, that says the connection has been broken. I assume it means the connection for single user mode. Any ideas on how to fix this??????
View 2 Replies View RelatedI tried to import a stored procedure from one Master database to another Master database, but it won't allow me. I then tried to copy and paste that system stored procedure on isql, but it still won't allow me.
Why I can do a DTS on other databases, except on Master database?
Is there any way around it?
Thanks for any help.
hi, What are the symptoms that the master database is currepted?
If I do not know the databases sort order, code page, and Unicode collation and I had a master database crash, it is important during the rebuild process to maintain same configuration,
Is there a way to run a script which tells me the configuration set up of the master database. so I know ahead of time what configurations Ican use during the rebuilding process.
Ahmed
hi, what are the reasons for rebuilding master database and what are the steps to complete this task
Thanks
Ahmed
Does anyone have anything that can automate the restoration of the master database in MS SQL 7? I need to do it on a daily basis on a back up server. I also need to do the 2 production databases and the msdb and model as well. Does anyone have any suggestions to keep me from reinventing the wheel?
Thanks,
Tim Davis
Hi All,
I am currently creating a SQLServer 7 server. This server will be used to host customer databases that I will restore on to the server. However, I want to prevent these customers accessing any other databases on the server, apart from their own. By removing the public database role from each customer database, and granting them very limited rights (basically exec rights on their own Stored Procs)on their own db, I plan to limit them to their own db. However, my problem is this:
As you cannot remove the public role from the master db, a user could easily exec the following in a stored proc to read from the master:
Select * from master..sysusers
How do I prevent the users from accessing the master in this fashion.
Will removing every permission from the public role in master be enough?
Will removing every permission from the public role in master have any other side effects?
Will removing the public role from other user dbs be enough to secure them?
Any suggestions/pointers would be appreciated.
Gary.
Master Database in SQL Server 7 has a transaction log. Using Enterprise Manager the option to back up the transaction log is greyed out. Is this because there is no need to back it up.
I don't know if there is any value, or whether it is possible to do so.
I have a number of books, none of which cover this specific question.
Can anyone help.
pargat.bhatti@uk.neceur.com
I'm trying to nail down some procedures for disaster recovery of our SQL 6.5 databases. If I rebuild master (or do a fresh install), then create the devices based on the output of sp_helpdevice, and then create the databases based on the output of sp_help_revdatabase, do I really need to RESTORE the master database? Are there any other objects in the master database (except for maybe some custom stored procedures) that I really need from my production server?
I've just recreated the device and database structures manually, so do I really need to restore master?
Another issue: the current wisdom that I've seen is that although databases need to be grown back in the same increments, the devices do not. Therefore, you can just DISK INIT them to their current size. Does anyone think this is a bad idea?
Ok I had a server crash the master was bad or corrupted. I tried to rebuild the master but that failed. I had to reinstall sql and then attach the databases.
I need to know why the master all of a sudden just stopped working. Any ideas where to look or what would cause this?
Thanks.
:eek:
This is long post but I really want some feedback on this urgently so please hang in there!
I am migrating a SQL Server over to a new server.
I have done the following and although everything appears to be working like a charm I would like to know if there are any gotchas that I have not considered.
These are the steps I used to 'move' the master db.
/*
I restored all the users databases onto the new sesrver from backups of the old (file copies and attaches would also do).
I restored a db named masterbak from a backup of the master on the old server. And again into a second db called masterbak2. I then detach masterbak2. I need to use those files later on.
So at this point I now have all the user databases that the old server had but I dont have logins, extended stored proc defs, and any user objects that were created in the old master.
Now I want to put all those missing things into the new master db. These are the steps that I used to achieve this.
*/
-- where doing open heart on sys tables so need this turned on
EXEC sp_configure 'allow updates' , 1
reconfigure WITH OVERRIDE
-- remove all database defs from the copy of the old master
delete masterbak..sysdatabases
-- and load it with all the database defs from the new master
INSERT INTO [masterbak].[dbo].[sysdatabases]([name], [dbid], [sid], [mode], [status], [status2], [crdate], [reserved], [category], [cmptlevel], [filename])
SELECT [name], [dbid], [sid], [mode], [status], [status2], [crdate], [reserved], [category], [cmptlevel], [filename]
FROM [master].[dbo].[sysdatabases]
-- SYSSERVERS
-- remove all the servers defined in the copy of the old master
delete masterbak..sysservers
-- replacing them with those that are in the new master
INSERT INTO [masterbak].[dbo].[sysservers]([srvid], [srvstatus], [srvname], [srvproduct], [providername], [datasource], [location], [providerstring], [schemadate], [topologyx], [topologyy], [catalog], [srvcollation], [connecttimeout], [querytimeout])
SELECT [srvid], [srvstatus], [srvname], [srvproduct], [providername], [datasource], [location], [providerstring], [schemadate], [topologyx], [topologyy], [catalog], [srvcollation], [connecttimeout], [querytimeout]FROM [master].[dbo].[sysservers]
-- sysfiles1
-- remove the old
delete masterbak..sysfiles1
-- replace with new
insert masterbak..sysfiles1
select * From sysfiles1
-- sysdevices
-- remove the old
delete masterbak..sysdevices
-- replace with new
insert masterbak..sysdevices
select * From sysdevices
-- sysconfigures
-- remove the old
delete masterbak..sysconfigures
-- replace with new
insert masterbak..sysconfigures
select * From sysconfigures
-- sysaltfiles
-- remove the old
delete masterbak..sysaltfiles
-- replace with new
insert masterbak..sysaltfiles
select * From sysaltfiles
-- were done
-- turn off sys updates
EXEC sp_configure 'allow updates' , 0
reconfigure WITH OVERRIDE
/*
this leaves
syslogins intact
sysmessages intact
sysoledbusers intact
sysremotelogins intact (no records in mine anyway)
all other system tables are left intact as we want to import all the objects into the new server
*/
/*
now stop the SQL server instance;
rename the physical files master.mdf and mastlog.ldf to masterORIG.mdf and mastlogOrig.ldf
rename masterbak.mdf and mastlogbak.ldf to master.mdf and mastlog.ldf.
rename masterbak2.mdf and mastlogbak2.ldf to masterbak.mdf and mastlogbak.ldf.
I wanted to preserve the new master files so didnt rename to masterbak but I suppose you could.
Start up SQL server and everything should be fine.
Configuration settings are as expected, databases started up ok and file locations are ok, logins both SQL and NT are ok, all master user objects are present.
User and login SIDs match.
A linked server access that was setup failed but after redoing security on the linked server it was ok? Sysoledbusers holds the security for this but not sure about this one.
Important:
You need to check sql log for any errors.
It should be clean.
If you have auto start procs that reference reg keys that are not on the new server you with be told which key is missing and you can export key from old server and import into new server.
Dlls of course will need to come accross but again error messages will indicate the dll that is missing and its path.
So we now have a server that starts up clean but its of a different name of the original of course and since we have a myarid of external servers etc that depend on the server name we are going to keep the old name as well.
So now we change the server name, requires reboot, start up QA and logging into to each 'new' instance name, run the following
-- 'SQLSERV02 -> SQLSERV01'
sp_helpserver
sp_dropserver 'SQLSERV02'
sp_addserver 'SQLSERV01', local
and that should be that.
you might get this :
-- Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44
-- There are still remote logins for the server 'SQLSERV02'.
sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
-- have a look at it
select srvid from master.dbo.sysservers where srvname = 'SQLSERV02'
select * from master.dbo.sysxlogins
where srvid = 0 and
not(ishqoutmap = 1 and
xstatus&192 = 192 and
sid is null and
name is null and
password is null)
-- get rid of it
delete master.dbo.sysxlogins
where srvid = 0 and
not(ishqoutmap = 1 and
xstatus&192 = 192 and
sid is null and
name is null and
password is null)
sp_configure 'allow updates',0
RECONFIGURE WITH OVERRIDE
My last step is to get all those jobs and log shipping over to the new server. And I have not yet done this so am not sure if any further open heart is required.
But the theory is that I just need to restore an msdb taken from the old server.
Naturally you will need to put in place the same file path structure on the new server for those scripts / jobs that create files on disk - such as backup files.
I will find out about msdb soon enough as this is going down tonight.
Can anyone see any obvious gotchas in this method?
cheers and thanks for your feedback
*/
I've got a new (my first) SQL2000/SP2 installation in which I've set up a vanilla database maintenance plan, including backup of master & msdb with my user databases. My user databases are backing up, but master & msdb fail every time with "[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode." Presumably this is caused by Integrity checks being set to perform before every backup.
First, this should work as I'd think we want to do the integrity checks; however, in the interest of getting a backup I've unchecked that box on the Integrity form. Unfortunately, my "unchecking" doesn't stay; I've 'applied' and 'okayed', but the next time I open the maintenance plan form that box is checked again!
Any suggestions appreciated.
TIA, Al