I have a copy of SQL Server Express on a clients computer.
When i try & restart the services it fails with the following error:-
Error: 9003, Severity: 20, State1.
It looks like the master database has become corrupt & needs rebuilding.
I have found a utility rebuildm.exe that repairs the master db but unfortunately it is not supported in sql 2005.
Does anyone have any other suggestions on how to repair the master db.
Thanks.
Hi,
how long a rebuildm.exe takes?
TIA
Hey all,
I'm a newbie to SQL, the organization i work for recently had a server experience a multiple hard-drive failure, corrupted the OS, etc etc. We called in a data recovery specialist who used an Ontrack tool to pull the SQL files we could off of the dead server and onto a different server. Question being how do i attempt a restore from disk, and also the Ontrack recovery software created a directory named "LOSTFILE" which contains multiple directories which contain a variety of different filetypes. Has anyone experienced a similar situation? Any ideas how i can get this resolved?
Ultimately i'd like to setup a new server and transfer the recovered files to it.
Any ideas or help is greatly appreciated,
Thanks!
With some performance issues faced as I checked on Google came across Paul Randal's article on Logging & Recovery: URL...In this regard, and with some schema changes required, ended up with a decision to drop and re-create a database. What are the proper way to do the same and following queries:
A) What would be the recommended way to shift data from some tables of former database to that of the newer one (The latter ones created with the Script Table transaction)?
B) Would a simple Drop Database completely delete the former database completely and clean up the memory (database pages etc.) or I need to take some additional steps for clearing up the space?
C) Most of the data tables have been updated via loading the Bulk Insert commands time to time from CSV/flat files. Could that be somehow the cause of enlarging the the size of Database and Log?
I have come across a database system which isn't designed to work optimally. It is fairly large (~400GB) and performance of loading and querying is degrading (improper data types, fragmented indexes, non unique clustering key and other problems). So, I have quite a task in front of me, but I am up for the challenge. I figure this is not a unique situation, many of us would have come across this before. I have done this before too, but only for smaller databases, some of the operations here I expect to take a couple of hours or more to complete (depending on load/infrastructure speed etc, I know).
My plan is thus:
+ Take a full backup of the database
+ Set the recovery model of the DB to simple
+ Drop non clustered indexes
+ Drop clustered indexes
+ Remove PKs (wrong data types, too large!)
+ Narrow data types (add new column, update column in batches to old value, rename new column to old column)
+ Add PKs, which will create clustered indexes automatically based on PK ID
+ Create non clustered indexes
+ Run a SHRINKDB (normal operations I would never do this, but this is a special case, ensure log file is truncated to a logical size especially after all those table modifications...)
+ Set the recovery model of the DB to Full
+ Ensure everything works OK or better
I had to to relocate the database log file and I issued an Alter database command but by mistake I put a space in the file name as below. The space is at the beginning file name. Now I am unable get the database loaded to SQL Server. The database has 2 replications configured, so deleting and re-attaching the database means the replication needs to be re-configured. Is there an alternative way to issue a command to update the database FILENAME ? Not sure if this can be edited in master database (sys files).
ALTER DATABASE [User_DB]
MODIFY FILE (NAME = User_DB_log, FILENAME = 'I:SQLLogs User_DB_log.ldf')
GO
Cn not do anything with my sql server, everything i trt to do i get this message, user does not have permision, etc, ,
I am running windows Vista Business, SQL SERVER 2005
so what going on here
got rid of my error about user login rights, it was all working yesterday. but for some reason i now get this error CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file C:InetpubwwwrootsqlSiteApp_DatasiteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file C:InetpubwwwrootsqlSiteApp_DatasiteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file C:InetpubwwwrootsqlSiteApp_DatasiteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734995
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +41
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360
i have this in my web.config file <connectionStrings> <add name="ConnectionStringTest" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:InetpubwwwrootsqlSiteApp_DatasiteDB.mdf;Integrated Security=SSPI;Connect Timeout=30;User Instance=False" providerName="System.Data.SqlClient" /> </connectionStrings> <appSettings /> <system.web> <!-- Set compilation debug="true" to insert debugging symbols into the compiled page. Because this affects performance, set this value to true only during development. --> <compilation debug="true" /> <identity impersonate="true"/> and my asp connection string is <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringTest %>" SelectCommand="SELECT [entryID], [compID], [emailAddy], [answer] FROM [entry]"></asp:SqlDataSource> if i set user instance to true i get a user permission error. it says on sql server management that i have dbo rights on my database, but it wont let me put datareader or write on this login. any ideas? its driving me insane
I have installed new SQLServer2012 instance and my domain user have sysadmin privileges on this instance. I have a restore procedure and it will execute WITH EXECUTE AS 'domainmy username', for all the developers have exec permissions to this procedure. But newly installed server this procedure was failing with the following message. But the same procedure executing fine on other servers.
Msg 262, Level 14, State 1, Line 1
CREATE DATABASE permission denied in database 'master'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
whenenver i run cold fusion, it automatically gets my tables out of the master database instead of the new one i have created. i've added a user that has a default to the new database instead of the master, but i need to know how to use that new user correctly, and what i will need to do once i start adding more databases so that cold fusion will go to the appropriate database and not to the master.
View 3 Replies View RelatedHi,
I'm using Visual Studio 2005 Pro and SQLExpress that comes with it.
I have my program running fine in XP Pro OS using a window user "Glen" (Computer administrator) with Administrator rights. This means that I installed VS 2005 using this window user "Glen"
I created another windows user "TestUser" (Limited account) in the same physical PC.
I tried to run the program and on the part that I need to access SQL table, I got the error [CREATE DATABASE permission denied in database 'master']
At the same time while using "TestUser" and running sqlcmd (to check if I can connect to SQL), I also got error HResult 0x2, Level 16, State 1.
I read alot on MSDN discussions and related links but it seems that I can't get the solution that I need.
SO HERE ARE MY QUESTIONS :
1. Am I allowed to run my program using user "TestUser" since SQL is installed using "Glen" windows user?
2. Do I need to add access rights to "TestUser" to allow the user to have CREATE rights? (Note : for security reason, I can add other access rights except Administrator)
Thanks in advance for all you help.
Sometime during the night last night some user account permissions were "lost". Am I right to think that restoring the master database would be the way to go? We have a 2 node 2012 cluster and I stop the cluster resource and start the db in single user mode from the active node. Somehow the sharepoint farm is still trying to connect so I can't get logged in single user. What method could I use to stop users from connecting when I don't have access to the sharepoint farm.
View 8 Replies View RelatedHi 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,
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
I 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
I installed SQL Server, created a database for a sql server user andnoticed that the user has access to the master database even though thecheckbox for master database for the user login is not checked.They can list sysusers and find out all the names. They can list all thedatabases as well by using sp_helpdb.Is this normal behavior?If I check db_denydatareader and db_denydatawriter in the master databasefor that user, will that break anything?John Dalberg
View 1 Replies View RelatedHi
For encytion in database, the following statement is correct?
I found it in msdn book.
The database master key is not mandatory if you want to encrypt data.
I am using SQL express and Visual web developer on windows Vista.
When I try to create a new database the following message appears.
CREATE DATABASE permission denied in database master (error 262)
I log on to my computer as an administrator.
Help appreciated
Prontonet
How do I set the Master database to "single user mode"?
It is grayed out when I go to the property.
Thanks
Iam planning to move the production server from one server to another. Can any one tell me how to do the migration of the master,model and msdb database. Help in this regard is highly appreciated.Is it ok if I do the dts for these three databases Thanks
Naveen.
In SQL 7 Books Online, the suggested method for disaster recovery is to restore the master database along with msdb and user databases.
I am testing this and have an issue restoring the master database. I start SQL in single user mode connected as sa. Using Enterprise Manager to initiate the restore I get the following error:
Microsoft SQL-DMO (ODBC SQLState:01000)
ConnectionTransact (GetOverlappedResult())
Connection broken.
This stops SQL Server, and prevents subsequent connections and the only fix is to rebuild the master database.
Any ideas?
Although user databases can be recreated and restored without restoring the original master, configuration, security and user logon information is lost without it.
Thanks.
Hi all, I'm hopeful that someone can help.. I'm working with my DRP scripts, and am having some trouble.
First I run Rebuildm with no issue, sql works fine (btw we're on SQL7 SP2)
then I start sql in single user mode
sqlservr -m
next, I attempt to restore my master db from a dump, and receive the following error:
Getpage: bstat=0x9, sstat=0x1, cache
pageno is/should be:objid is/should be:
(1:988)/(1:988)6/30
... IAM indicates that page is NOT allocated to this object
Error: 605, Severity: 21, State: 1.
Error: 3151, Severity: 21, State: 0
The master database failed to restore. Use the rebuildm utility to rebuild the master database. Shutting down SQL Server..
PLEASE HELP!
I would like to restore the master database from a current backup tape but I got the error “Load database must be used in single user mode if trying to restore the Master database”. I tried to change the master database option but the option ‘single user’ cannot be changed for the ‘master’ database. Then I tried to start the SQL Server in Single-User Mode but I was not able to restore the master database from a tape.
Would anybody help me on how to restore the Master database like any other database?
Many thanks
Stella Liu
Hello All,
I am going through the procedure of restoring the master database as described in the Microsoft SQL 6.5 Survival Guide by SAMS. I deleted the master.dat file from c:mssqldatamaster.dat. I went into setup and choose rebuild master database. After rebuilding the master database, I went into setup again to add the "-m" server option to start server in single user mode so I can load the master database from backup. Then I went into Enterprise Manager to start the server. The server started, but when I try to double click on the server. It gave me the following
" A connection could not be established to [server name] - SQL server login failed."
I try reregistering the server, but gave me the same error. The SQL server is using standard security.
Does anybody know what I did wrong?
Pauli
Hallo
I do my backup using BACKUP database <myDB> TO DISK='D:BACKUPmyDB.bak'
I do this for ALL my databases every day.
Another product writes every day all the files on the server to tape EXCEPT the online databases because these files are open by SQL server 2000.
Now I restore the files of the tape to the server
This works well but I haven't got the online databases !!
And because the master database isn't available, SQL server wont start ...
BUT I have the backup of the master database 'D:BACKUPMaster_data.bak'
How can I use this master backup to do a restore even when SQL server can't start ?
Or must I do the bakup of the master database in another way ?
Hi all,
We are going to have several dozen servers around the country- each with a mirror copy of the initial database (blank) loaded upon them.
The data will be entered at each station, and then once a month, the systems will dial into the 'net and -
this is where i am getting stuck -
we would like the data from the remote areas to update the data in the master database -
What would be the best solution to update the data from the remote station into the master database???
thanks
tony
After rebuilding my SQL 6.5 server and restoring all of my drives I noticed that the mssql/data direcory didnt properly backup. I reinstalled SQL, applied SP5a, recreated my device and databases, and then replaced my dat files from my restores. The only database that I am affraid of is my Master. I can run Queries successfully against all my databases successfully. Is there anything else I need to do or should be worried about? I might be able to restore the master from a backup done earlier yesterday morning. But dont want to unless necessary.
Thanks in advance.
David
We are currently in the process of transferring a database from one server (sql 6.5) to another (SQL 6.5) but in order to successfully transfer the database we have to transfer the master database first. Any ideas on how to transfer the Master database successfully ???
View 1 Replies View RelatedALL,
When I attempt to restore a master database I am having some issues. I am using SQL Server 2000. I have set the start up option with -c -m to set the database in single user mode.
When I run the restore from enterprise manager I am get an popup window that is titled "SQL-DMo(odbc SQLSTATE:HY000)" with not message and then the database conection is lost. Thus the master datbase is appears to it was not resotred. As when I close enterprise manager and then restart the MSSQLSERVER service the database is in the same state.
Please advise on how i can get the Database restored.
Thanks
Chris
Hello,
I can't get bcp to run on any database but 'master'. I get a 'failed to login' error. i'm logged in as 'sa' with full permissions.
Please help
Thanks
Installed SQLSERVER 2005 EVAL copy in my server and trying to rebuild the master database and it is not working.
I tried the following command.
start /wait setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=RAJESH
Any thoughts?
Thanks