Hi All,
Can we have an sql server installation where we dont have a master database. Can the complete data dictionary be stored in another database , or put it other way can master database be renamed.
I have a need to assume that there will always be a master database for any SQL server instance. Want to confirm whether this assumption is true or not.
Thanks in advance.
Chandrakant Karale.
While performing import actions I had a system freeze, when the system returned the sessions had been closed and the database had vanished, with the help of support we recovered the database only to find that the original project ID had a suffix attached ( Original 40/0110, New 40/0110-1 ), when I try to return it to it's original numbering convention it says it has to be a unique number which suggests to me it is not deleted but hiding in the background, can the original be recovered or is it possible to renumber the recovered database, I have searched the whole of the databases and the original is nowhere to be seen.
View 1 Replies View Relatedhi
Basically, I create a database with sql, then I delete it manually(not via sql statment. This is a problem which I realise. In fact, you can't delete the database because the VS 2005 still is using it) I run the same code again,
then it says the database still exists, even it is physically destroied.
------Here is the errors:
System.Data.SqlClient.SqlException: Database 'riskDatabase' already exists.
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolea
n breakConnection)
------The evidence that the database doesn't exist physically:
Unhandled Exception: System.Data.SqlClient.SqlException: Cannot open database "riskDatabase" requested by the login. The login failed.
------The code:
/*
* C# code to programmically create
* database and table. It also inserts
* data into the table.
*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
namespace riskWizard
{
public class RiskWizard
{
// Sql
private string connectionString;
private SqlConnection connection;
private SqlCommand command;
// Database
private string databaseName;
private string currDatabasePath;
private string database_mdf;
private string database_ldf;
public RiskWizard(string databaseName, string currDatabasePath, string database_mdf, string database_ldf)
{
this.databaseName = databaseName;
this.currDatabasePath = currDatabasePath;
this.database_mdf = database_mdf;
this.database_ldf = database_ldf;
}
private void executeSql(string sql)
{
// Create a connection
connection = new SqlConnection(connectionString);
// Open the connection.
if (connection.State == ConnectionState.Open)
connection.Close();
connection.ConnectionString = connectionString;
connection.Open();
command = new SqlCommand(sql, connection);
try
{
command.ExecuteNonQuery();
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
}
public void createDatabase()
{
string database_data = databaseName + "_data";
string database_log = databaseName + "_log";
connectionString
= "Data Source=.\SQLExpress;Initial Catalog=;Integrated Security=SSPI;";
string sql = "CREATE DATABASE " + databaseName + " ON PRIMARY"
+ "(name=" + database_data + ",filename=" + database_mdf + ",size=3,"
+ "maxsize=5,filegrowth=10%)log on"
+ "(name=" + database_log + ",filename=" + database_ldf + ",size=3,"
+ "maxsize=20,filegrowth=1)";
executeSql(sql);
}
public void dropDatabase()
{
connectionString
= "Data Source=.\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";
string sql = "DROP DATABASE " + databaseName;
executeSql(sql);
}
// Create table.
public void createTable(string tableName)
{
connectionString
= "Data Source=.\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";
string sql = "CREATE TABLE " + tableName +
"(userId INTEGER IDENTITY(1, 1) CONSTRAINT PK_userID PRIMARY KEY," +
"name CHAR(50) NOT NULL, address CHAR(255) NOT NULL, employmentTitle TEXT NOT NULL)";
executeSql(sql);
}
// Insert data
public void insertData(string tableName)
{
string sql;
connectionString
= "Data Source=.\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";
sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1001, 'Puneet Nehra', 'A 449 Sect 19, DELHI', 'project manager') ";
executeSql(sql);
sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1002, 'Anoop Singh', 'Lodi Road, DELHI', 'software admin') ";
executeSql(sql);
sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1003, 'Rakesh M', 'Nag Chowk, Jabalpur M.P.', 'tester') ";
executeSql(sql);
sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1004, 'Madan Kesh', '4th Street, Lane 3, DELHI', 'quality insurance mamager') ";
executeSql(sql);
}
public static void Main(String[] argv)
{
string databaseName = "riskDatabase";
string currDatabasePath = "E:\liveProgrammes\cSharpWorkplace\riskWizard\A pp_Data";
// Need to be more flexible.
string database_mdf = "'E:\liveProgrammes\cSharpWorkplace\riskWizard\ App_Data\riskDatabase.mdf'";
string database_ldf = "'E:\liveProgrammes\cSharpWorkplace\riskWizard\ App_Data\riskDatabase.ldf'";
RiskWizard riskWizard = new RiskWizard(databaseName, currDatabasePath, database_mdf, database_ldf);
riskWizard.createDatabase();
riskWizard.createTable("userTable");
riskWizard.insertData("userTable");
//riskWizard.dropDatabase();
}
}
}
Hi,
I'm using SQL Server 2005, and I would like to understand how to create and to drop a database without errors:
Infact, if I try to create a database that already exists, SQL Server throws the error "Impossible to create the database because it already exists", and if I try to drop a database that doesn't exist, SQL Server throws the error "Impossible to drop the database because it doesn't esist".
Before creating or dropping a database, I should control if it exists or not...
Is there a method to do that?
I found that such control for a table is the following one (in this case, I drop the table only if it exists):
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table1]
I tried to adapt the statement to the database case, modifying it as follows:
if exists (select * from dbo.sysobjects where id = object_id(N'[Database1]') and OBJECTPROPERTY(id, N'IsDatabase') = 1)
drop database [Database1]
but it didn't function (it was a blind attempt).
Can you suggest me a statement to do that?
Thank you very much
Hello,
I would like to know how to rename a database. I use SQL manager to access it.
Thank you in advance for your help.
Dear friends, is it possible to rename a Database?
thank you very much.
Vinod
hi
How can i rename database name through command
Here i tried with the folloeing statement
ALTER DATABASE indraja1
MODIFY NAME = indraja11
but i got error as
Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
Thanks in Advance
Malathi Rao
Dear experts,
is it possible to rename a database?
I've tried with harsh suggesion from previous posts....
--alter database Script modify name= script_test
Use Master
GO
Alter database Script
Set Single_User
GO
THANK YOU VERY MUCH
Vinod
Even you learn 1%, Learn it with 100% confidence.
Morning everyone. Can anyone help me to rename the Database please?
View 2 Replies View Relatedhi
I want to rename the phisical file of the dabase for instance rename <MyDatabase.mdf> to <Renameddatabase.mdf>
every time I do so whether directly renamin the file or by
using Alter Database command I face an error
note that I don't want to logically rename the database
please tell me step by step what to do
thanks in advance
hi
every time I try to rename my database I face an error
I want to rename the database file phisically not just changing logigal name with
Alter Database <database name> modify name command
please step by step tell me what to do
whether with TSql or Managment Studio
thanks
hi
every time I try to rename my database I face an error
I want to rename the database file phisically not just changing logigal name with
Alter Database <database name> modify name command
please step by step tell me what to do
whether with using TSql or Managment Studio
thanks
Hello:
I am supporting a mssql 6.5, sp 4 application.
We have set up an user acceptance server with a database that is the same as production. However, I have been told that this database and server may be used for applying fixes rather than user acceptance.
To avoid confusion over which database is on which server, it might be a good idea to rename the database. I am not sure if this can be done un der mssql 6.5?
1)Can I rename a database?
2)If so, how can I do so?
Which system tables would I use to update?
Any information you can furnish will be appreciated. THanks.
David Spaisman
Hi,
which command is used to rename the database?
regards,
revanth
revanth babu
How does one make a copy of a database (with the data) on the sameserver. Would like to do this to use the copy in a test environment. Ilooked at the Copy Database Wizard but the instructions in Books Onlinestate that the database must be copied to another server and cannot berenamed using this process.Thank you.
View 2 Replies View RelatedI didn't know exactly which forum to post this in, so if I need to post it into another one, please let me know. Also, I have absolutely no SQL or DB experience. I am learning as I go.
The Situation:
I created a virtual network for my company which included MS CRM, which uses SQL Server 2005. Everything was working fine until today. My company asked me to make a change to the domain name, as we are changing company names. I followed the processes through microsoft's documentation that I found online. The rename went fine. Since then, we can no longer access CRM. When I run the environment checks, it gets hung up on the SQL Server components. It says that it can't confirm the MSSQLSERVER service is running (it is) and it can't connect to the database. As best as I can tell, the database is looking for the old domain name, which doesn't exist. How do I edit or configure the existing database to reflect the new domain name. It's important to do this as we have data in that database that we need to access. Any help in this matter is very much appreciated!!!
Hi,
first of all escuse my English. I'm new to SQL Server, my experience is with MySQL, Interbase and Firebird, but now I have a new job so I need to use SQL Server, version 2005 Express.
I have two SQL 2005 Express installations in two different machines (local and remote). I can access both servers via SQL Management Studio
I need to do the following:
1) Create a copy of a database with a new name in the same machine (local).
2) Copy the new renamed database to another host (remote)
Please help me, remember I use Express edition.
Thanks in advance.
Giorgio
Trying to find tsql for TRIGGER which will send me email, if someone rename database in test/dev environment. I found it for CREATE and DROP database trigger but could not find for RENAME database.
View 8 Replies View RelatedI want to Replace The Big Log database with A new one ( A database with same structure).But current DB has many connection .
This is my plan :
1- Create a new database with same structure.
2- Rename current database to olddb with this code :
USE master
GO
EXEC sp_dboption CurDataBase, 'Single User', True
EXEC sp_renamedb 'CurDataBase', 'OldDataBase'
GO
3- Rename Newdb to current DB.
USE master
GO
EXEC sp_renamedb 'NewDataBase', 'CurDataBase'
is it true ? and Tsql code is ok ? (dont forget many of connection to curdatabase (that Is a log db) and loss some seconds data is not problems)
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.