I can backup this key using something like:
BACKUP MASTER KEY TO FILE = 'c:TestEncryptionMasterKey.key'
ENCRYPTION BY PASSWORD = '23'
but why would I need to do it as the DBMK is stored in the databases itself ( in sys.symmetric_keys) and we dump all databases nightly. Just "in case"?
TIA,
barkingdog
Hi Everyone, I'm hoping someone can help me.
We've had a SQL login deleted (special application one) and I understand the best way I can get it back, is to restore the Master database. Is this true ? All the manuals are working on the fact that Master has become corrupt & that it should be rebuilt before restoring. Ours is not corrupt, so am I ok to skip rebuilding it first ?
Cheers
I'm trying to recreate a sql database on another server and I installed sql server 6.5 on a server and then restored the master db using the Sql Setup program. It worked without any errors, but it did not create the loginthat will use the database that I have not restored yet. Aren't the Logins storied in the master database?
Thanks.
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 RelatedNew to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.
When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on the roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?
I have been working on an app for several weeks. My database size went from 9MB to about 12MB. Sometime in the last 2 days it jumped up to 242 MB. I don't know why. Now I cannot open it with management Studio Express. It says another process is using it. Usually this means I have a aspnet_wp.exe process running (from Visual Studio) and I just end the process, but Wndows Task Manager will not end the process. It does knock its size way down to 2.9 MB but won't kill it. Rebooting won't kill it either.Are these two symptoms related? How do I recover?Thank you.
View 1 Replies View Related
Hi all
Iam working in Prodcution ENV,Please help how make space
The log file for database 'Home_alone' is full. Back up the transaction log for the database to free up some log space.
Hello,i am in great trouble. I want to revert back to original state ofdatabase before i performed restore database on my sql server 2KDatabase. Accidently i didn't take backup of my Database and i didrestore, so is there any way to get the original state back of myDatabase?Any suggestion will be highly appriciated.Regards,S. Domadia.
View 2 Replies View RelatedI 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
Can someone provide a step by step tutorial for this? I'd like to safely update a database that is used for a website without much or any downtime.
View 1 Replies View Relatedgot 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.
I'm having a problem with a Maintenance Plan created for SQL Server 2005 using Microsoft SQL Server Management Studio. See version information at the bottom of this post.
I have a Maintenance Plan using the "Back Up Database Task" that is set to perform a "Full" back up of "All Databases" on the local server. However, it appears that the list of "All databases" are hard-coded to be those databases that were available at the time the task was created. It seems any newly created databases don't appear to be in the list. If I attempt to edit the task and select the "These databases:" option, the newly created databases aren't even in the list.
Is there some way to have the task refresh the list of the databases available for backup? Ideally, I'd like any newly created databases to get backed up automatically without having to modify the task.
Thanks for your help,
Jonathan.
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790
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 RelatedCan any body help with sample code to backup (sql server 2005 express)database using VB.net on click of a button?
Thanks
hi can any one guide me how to take database back up from sql server 2005 using visual studio frame work 2008
View 2 Replies View RelatedHi everyone,
I am working in a company where data is changed daily (IT department of a shopping center) where item prices change everyday, the quantity available in stock vary daily,...What is the scenarios available for the back up on SQL Server 2000 (I am a developer not a database administrator, but I have a task to do which is to make a back up for the critical data) .
could u help me with some links or idea.I was thinking of doing a full back up one time everyweek and daily I will do a differential backup?is that the best solution?Thanks.
Hello,I'm trying to create a simple back up in the SQL Maintenance Plan that willmake a single back up copy of all database every night at 10 pm. I'd likethe previous nights file to be overwritten, so there will be only a singleback up file for each database (tape back up runs every night, so each daysback up will be saved on tape).Every night the maintenance plan makes a back up of all the databases to anew file with a datetime stamp, meaning the previous nights file stillexists. Even when I check "Remove files older than 22 hours" the previousnights file still exists. Is there any way to create a back up file withoutthe date time stamp so it overwrites the previous nights file?Thanks!Rick
View 5 Replies View RelatedI need to run two reports each of A5 Size to run back to page and print on single A4 paper means in 1st half Sale bill will be printed and in second half Gate Pass Will Be Printed both report will be on same page and size and shape should be maintained. How to do it.
View 4 Replies View RelatedHi, i have one database question here. Let me describe my situation first:
i have two different hard disk drives, here i call it HDD1 and HDD2. i want to format my HDD1, but all my database tables are stored in HDD1. i want to know is there any way for me to backup my database table to HDD2, and restore it back later to HDD1.
thanks to you all.
I am trying to back up database to a different server. I am able to map the serve to my current ones, however, when I go to Enterprise Manager - back up DB, I can not see the mapped drive from back up file location, I wasn't able to see any other disk location but the local drives.
Can somebody tell me how can I back up database to a different server from Enterprise Manager or any syntax?
Thanks.
Hi all,
I'm getting the following error message in NT event view:
Error: 9002, Severity: 17, State: 2
The log file for database 'db_sys' is full. Back up the transaction log for the database to free up some log space.
But I don't know how to back up the transaction log for the database.
Do I need TRUNCTE LOG (in E.M) to free up some log space?
I only know back up all the database using ALL TASKS -> BACKUP DATABASE in E.M. Please help me. Thanks in advance.
TH
I had to transfer my database from one server to another. Both are running SQL 2000. I backed the database up and moved it to the new server however when I try to do a restore I get the following message:
The media set for database "xxx' has 2 family members but only 1 are provided. RESTORE DATABASE is terminating abnormally.
I did some searching and found a couple of posts that mentioned something about the back up including 2 files.
My problem is that the machine I backed this up from crashed when it was shut down and would not reboot so all of the data and the original database do not exist. Is there any hope of restoring this database. It is pretty critical.
Thanks,
Melissa :-)
Hi,
I have a report deployed and working. Now I need to change it but I lost the source (RDL).
Can I get the RDL back from the reportServer database? How?
Help is really appreciated
Tks in advance,
DD
P.S: I'm Using SQL Server 2000
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,
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 Related