ALTER DATABASE SET PARTNER OFF
Oct 25, 2006
I've read that when this run's, it removes all db mirroring information on that db. What exactly does it remove?
Here's my senario:
We are using SQL 2005€™s db mirroring process. We are using the certificate method of authentication between the principle and the mirror db€™s.
My question is that when the ALTER DATABASE dbname SET PARTNER OFF is run, does it remove these certificate settings as well? In other words when I want to enable the db mirroring, will I need to recreate these certificates or just recreate the endpoints to use these certificates?
View 5 Replies
ADVERTISEMENT
Jun 23, 2014
Running this query in DR server to start SQL mirroring but encountered an error below.
Query:
use master
go
alter database test set partner= N'TCP://HOSTNAME.DOMAIN.GROUP.INTRANET:5023'
go
Error:
Msg 1452, Level 16, State 6, Line 2
The partner server instance name must be distinct from the server instance that manages the database. The ALTER DATABASE SET PARTNER command failed.
View 2 Replies
View Related
Oct 25, 2007
Hello,
Am I reading it wrong or can I have a developer edition as a partner?
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx Please take a look at the Database Mirroring and SQL Server 2005 Editions section.
Anyone knows?
Thank you.
View 6 Replies
View Related
Sep 5, 2006
I want a database user to be able to alter login, database user and database role from my application. so, i assigned that user to sccurityadmin server role, db_accessadmin and db_securityadmin database roles....By now, the user can add or remove login and database user. However, the user cannot add or remove any database role membership. What am I missing here?? What should I do so that the user can create, and alter database roles in the database??
View 1 Replies
View Related
Jul 20, 2015
I have a script that automates some db drop/restore operations and bringing the database to single user mode is part of it: ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE...I want this to executes under a login, that has restricted privileges, so I've created a login and granted it a dbcreator role + ALTER ANY DATABASE privileges.
Problem: When I run the script against a database with an active/sleeping connection:It fails when using the restricted login: "Msg 5061...ALTER DATA BASE failed because a lock could not be placed on database ..."It completes successfully when using a sysadmin login According to stackoverflow.com the solution is to kill the active/sleeping connections to the database, before ALTER-ing it, which works fine, but the question is....
Questions: Why the "ALTER DATABASE..." statement works under the sysadmin login, but not under a dbcreator one?Does this mean the sysadmin login kills the connections to the target database in the background?Is it possible to grant additional privileges to the restricted login, so the "ALTER DATABASE..." statement won't need preventive killing of the connections?
View 5 Replies
View Related
Jul 23, 2005
Hi all,Here is the table and DML statmentsCREATE TABLE [jatpartnerMst] ([rowid] [int] ,[partnerid] [int] NULL ,[mcstat] [int] DEFAULT (1), -- 1 Pending ,2 Approved[sf] [varchar] (20))INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(1,1,2,'active')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(2,1,2,'active')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(3,1,2,'active')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(4,1,2,'active')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(5,1,1,'active')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(6,1,2,'inactive')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(7,1,2,'inactive')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(8,1,2,'inactive')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(9,2,2,'active')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(10,2,1,'active')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(11,1,2,'active')What I wish to find is the latest record on the top and it's otherrecordse.g If partnerID 1 is changed it goes to the bottom of the table , atany given time I am interested only in max(rowid) for a partner withstat 1 or 2I am using this queryselect * from jatpartnerMst where rowid in (select max(rowid) fromjatpartnermst where mcstat in (1,2) group by partnerid,mcstat )This query does not give me the latest.On using this queryselect * from jatpartnerMst where rowid in (select max(rowid) fromjatpartnermst where mcstat in (1,2) group by partnerid,mcstat )order by rowid descThe partner's two records get seperated . I wish to show them followingone another.So the output should be1112active511active922active1021active11 & 5 rowids are following each other because they are rows of samepartner and 11 is the most recent row [ because new rows are insertedat the end]Is it possible to do the above using single queryI am using cursor to do the same.With Warm regardsJatinder
View 11 Replies
View Related
Apr 25, 2007
Hi, i am working with SMO object,
At the time of accessing remote(LAN) database server I encountered the
following message: "MirroringPartnerInstance" : Undefined error.
I m not able to figure out whats the exact problem is..
i had gone through the following link too but doesn't make any sense to me :
msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database.mirroringpartnerinstance.aspx
All i understand is i need to set some property of mirroring partner.
Does any on have any idea how to resolve this problem
Thanking you all in advance
Vivek
View 2 Replies
View Related
Apr 21, 2006
Hi!
I have the following error during setting partner on mirror server
Msg 1431, Level 16, State 4, Line 1
Neither the partner nor the witness server instance for database "masterserver" is available. Reissue the command when at least one of the instances becomes available.
The partner is available through telnet. I've also checked ports vai netstat and have no found errors.
There are two noteworthy erros in the error log at mirror server
Error: 9642, Severity: 16, State: 3.
and
An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: '')
Security settings it seems are set accurately.
View 1 Replies
View Related
Aug 25, 2000
I am using SQL 6.5. Can I create a database and Alter the same
database in one SQL Script?
Thanks in advance.
View 1 Replies
View Related
Oct 11, 2012
We have a database,which has been mirrored.Also,We have an application which uses OpenRowSet to connect to this database.
Is it possible to set "Failover partner" in OpenRowSet connection string,so when we failover from Prinicple server to the mirrored database,The application still will continue to work?
Example:
select
*
from openrowset(
'SQLOLEDB',
'Data Source=Server1;Failover Partner=Server2;trusted_connection=yes;','select top 10 from Database1.dbo.Table1'
) temp
View 1 Replies
View Related
May 24, 2006
I need to connect to mirrored SQL servers (Developer Edition) using OLE DB, I tried both OLE DB and ODBC, but it doesn't work
I used connection ODBC string:
Driver={SQL Native Client};Server=10.0.1.161;Failover Partner=10.0.1.162;Uid=test;Pwd=test;Database=TestDB
if server 161 is principal and server 162 mirror, it connects ok, but
when I exchange server roles, connect fails (the error message is:
Cannot open database "TestDB" requested by the login. The login failed.
in LOGIN)
the connect string using OLE DB is:
Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=test;Password=test;Failover Partner=10.0.1.162;Initial
Catalog=TestDB;Data Source=10.0.1.161;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with
column collation when possible=False
error message is the same
when I try to connect using VS 2005 using connection string
Database=TestDB;User Id=test;Password=test;Server=10.0.1.161;Failover
Partner=10.0.1.162, it works OK
i have installed SQL server 2005 (on local - client machine) with SQL Native Client and also
SQL Server service pack 1
Is there any way how to connect from OLE DB?
Thanks
View 3 Replies
View Related
Mar 27, 2008
We have a pair of SQL 2005 SP2 with Rollups clusters. We have a series of DB's that we are migrating from an existing SQL 2000 cluster. I have scripted the process, however on one of the test DB's, it goes to "In Recovery" as soon as I issue the Set Partner statement. There are other DB's on the same cluster mirrored with no problems. As we have a bunch of DB's to migrate, I want to figure out what would cause it to start a recovery. After the initial restores are done, it is in "Restoring" for a status so everything works up to that point.
Thanks
Jon Macy
View 3 Replies
View Related
Dec 27, 2006
i tried to set up mirroring having only principal and mirror. mirroring endpoints were easily created for both servers. when i try to add the partner name it responds with
Msg 1418, Level 16, State 1, Line 1The server network address "TCP://machine1.domain.com:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.
On the log of machine1 i see,
Database Mirroring login attempt by user 'NT AUTHORITYANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITYANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'.
i have similar problems as in
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1020144&SiteID=1
i am a sysadmin on both SQL Servers and have connect permission on both end points.
Both the endpoints are in started state and listening to all IP's
Both services are as 'Local System'
As per what was mentioned in the above post to add a 'domain/machine$' user did not help.
Tried using setspn.exe to add a new SPN MSSQLsvc/<HOST>:<MIRRORINGPORT> did not work. an ERROR comes 'not enough privileges'
setup shows the following SPN only on both
HOST/Machine name
HOST/Machine name.Domin.com
MSSQLSvc/Machine name.Domin.com:1433
SMTPSVC/Machine name
SMTPSVC/machine name.Domain.com
and on second machine
HOST/Machine name
HOST/Machine name.Domin.com
MSSQLSvc/Machine name.Domin.com:1433
any Clues?
View 1 Replies
View Related
Jul 23, 2005
Hi people,I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K.Example:table : item_nota_fiscal_forn_setor_publicofield : qtd_mercadoria integer NOT NULLALTER TABLE item_nota_fiscal_forn_setor_publicoALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULLBut, It doesn't work. A sintax error rises.I need to change that field in a Visual Basic aplication, dinamically.How can I do it? How can I create a decimal(12,4) field via script in MSACCESS?Thanks,Euler Almeida--Message posted via http://www.sqlmonster.com
View 1 Replies
View Related
Jan 23, 2001
I am trying to move one of my database log to different location but I am geeting an error when I try to do this.
I am moving Registration DB log file from 'c:mssql7
egistrstion_log.ldf'
to 'e:sql
egistration_log.ldf'
Here the syntax I am using to do this:
alter database registration modify file (name='registration_log',filename= 'e:sql
egistration_log.ldf')
But I get the following error:
Server: Msg 5037, Level 16, State 1, Line 2
MODIFY FILE failed. Do not specify physical name.
Thank You,
John
View 1 Replies
View Related
Jul 31, 2006
Hi all this is my first of most likely many posts.
I am writting a stored proc that will be used on many diffrent named databases and i am setting the database name with a variable. I would like to use this variable in the following situation any clues on this would be greatly appreciated.
DECLARE @DBName varchar(50)
SET @DBName='Database'
alter database @DBName set recovery full
regards
Phil
View 4 Replies
View Related
Apr 25, 2008
Hey,
I am trying to give a user permission to change the name of a database.
They are a member of the server role 'secuirtyadmin'
They are a member of the database role 'db_owner'
They have been granted ALTER permission on the database.
But every time I try and run the command
ALTER DATABASE <dbname> MODIFY NAME = <newdbname>
I get an error saying the database doesn't exist or you don't have permissions.
What am I missing?
View 7 Replies
View Related
May 10, 2007
Hi
Sincere Apologies for the cross posting. Did not realize that there is a specific DB Mirroring group and so posted initially in the High Availability group. Here is the original post
Hi
I am trying to test DB Mirroring connectivity and running into a road block. using SQLOLDB in my connection string the failover partner keyword seems to be not recognised when the failover occurs and the connectivity fails. The same however works with the SQL Native client driver.
Can any expert please let me know what I am doing wrong and what is the right connection string for the OLEDB one?. I also tried using different flavors of FailoverPartner (like Failover Partner, FailoverPartner etc) to make it work with OLEDB but still could not connect with SQLOLEDB provider.
SQLNCLI works with no issues at all.
Connection string code samples included.
--Code that does not work
Code Snippetconnstring = "Provider=SQLOLEDB;network=dbmssocn;Data Source=Server1SQLInst1;FailoverPartner=Server2SQLInst2;Initial catalog=mydb;INTEGRATED SECURITY=SSPI;"
Code Snippetconnstring = "Provider=SQLNCLI;network=dbmssocn;Data Source=Server1SQLInst1;FailoverPartner=Server2SQLInst2;Initial catalog=mydb;INTEGRATED SECURITY=SSPI;"
Any help is appreciated.
Thanks
AK
View 3 Replies
View Related
Jan 27, 2006
I am getting this error while trying to setup mirroring.
"Neither the partner nor the witness server instance for database "shop" is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431)"
I am just using principal and mirror server, there is no witness server. I have tried fully qualified name and ip for the servers also. I can connect from the principal server to the mirror server using the management studio and it also creates endpoints on prinicipal and witness. But then gives me an error that it cannot find the instance of the mirror server and if I try again it gives the error above. I can also telnet to port 5022.
I have used -T1400 as the startup parameter.
I did the backup and recovery based on this link http://msdn2.microsoft.com/en-us/library/ms189053.aspx and the mirror server is in recovery mode now.
View 20 Replies
View Related
Mar 13, 2003
Hello,
I have Test database with Log file property Automatically grow the file, option Unrestricted file growth, I wanted to alter it with Restrict file growth upto 200 MB. I'm getting an error that you cannot modify as file doesnot exist
But the file does exist. I cannot figure out what is giving the problem.
below is the sql statements
select name from sysfiles
go
Test_Data
Test_Log
USE master
go
ALTER DATABASE Test MODIFY FILE
( NAME='Test_Log',
MAXSIZE=200MB )
go
ERROR
Server: Msg 5041, Level 16, State 1, Line 1
MODIFY FILE failed. File 'Test_Log' does not exist.
Any help is appreciated.
Thanks
Sejal
View 3 Replies
View Related
Jan 22, 2004
Hi
I got the following error
Error: 823, Severity: 24, State: 4
I/O error 33(The process cannot access the file because another process has locked a portion of the file.) detected during write at offset
0x0000000a796000 in file xxxxxxxxx.ndf'.
and the respective database could not be brought online - this was just due to a problem with a .ndf file containing only indexes...is there any way to connect to/alter a database while it is in this transitional state? (it would be no loss if i could just remove the file & its filegroup)
(i tried starting with -f -c, but no go)
thanks in advance
des
View 3 Replies
View Related
Apr 7, 2006
I have a *.sql script that creates database tables, and I need to modify the database to enable the service broker. In addition, the actual name of the database is not known in advance - it is set per instance of the application.
I know I need to do:
ALTER DATABASE dbname SET ENABLE_BROKER
But I must avoid including the name of the database in the script. I did wonder if this would work:
DECLARE @DB varchar(50)
SELECT @DB = DB_NAME()
ALTER DATABASE @DB SET ENABLE_BROKER
But I just get a syntax error. Presumably this also rules out setting the database name as a parameter to the script (SqlParameter stuff)
The only option I can think of is dynamically creating the statement, either in T-SQL or in the calling .NET environment.
Any thoughts?
Ruth
View 6 Replies
View Related
Nov 7, 2006
We were having problems setting up the mirroring, so I did it via command lines. I found out the "alter...set partnership" command works on the mirror server going to the principal, but gets a 1418 error when going from the principal to the mirror.
So if A is the principal and B is the mirror, A to B fails but B to A works.
If I reverse it so that B is the principal and A is the mirror, B to A fails and A to B works.
Any suggestions?
View 3 Replies
View Related
Apr 26, 2003
How can I do an alter table in some table that replicated database ?
I got the error message when I try !
View 3 Replies
View Related
Jan 15, 2007
Conrad writes "I'm currently working on Table Partitioning. I have done everything succesfull for partitioning, what I'm struggling with is to use
" SELECT MAX(NAME) from sys.filegroups WHERE NAME NOT LIKE 'PRIMARY' " to get the last used FileGroup. Now this works just fine, but when I run the following script:
--Decalre variables
DECLARE @LastFilegroupName VARCHAR(50)
DECLARE @FilegroupName VARCHAR(50)
--Retuns the next FileGroup to be used
SET @LastFilegroupName = (select MAX(NAME) from sys.filegroups WHERE NAME NOT LIKE 'PRIMARY')
SET @LastFilegroupName = Replace(@LastFilegroupName,'FileGrp','')
SET @FilegroupName = 'FileGrp' + CAST((@LastFilegroupName + 1) as varchar(10))
--Alter database statement
ALTER DATABASE VadivelTesting
ADD FILEGROUP @NewFG_Name
This script gives the following error "Incorrect syntax near '@NewFG_Name'."
When I give it a static name it works fine, but not with the variable.
Please can someone help me, I'm in struggeling with this one."
View 1 Replies
View Related
Dec 6, 2007
We're upgrading to SQL Server 2005, and we've restored databases from SQL Server 2000 to the development server (SQL Server 2005). Part of what we need to do is alter the database collation to SQL_Latin1_General_CP1_CI_AS; our SQL Server 2000 server is case-sensitive.
My initial attempts to alter the database;
alter database RGDWDEV collate SQL_Latin1_General_CP1_CI_AS
failed because of the presence of computed columns and user functions. So, I wrote scripts to save the definitions of the computed columns and functions, and wrote scripts to drop them. Now that they are gone, I expected to be able to successfully execute the above 'alter database' statement, but I am now getting this error;
Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'sys.sysschobjs' and index name 'nc1'. The duplicate key value is (0, 1, DISB_ADJ).
Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'RGDWDEV' cannot be set to SQL_Latin1_General_CP1_CI_AS.
I do not know how to fix this.
Can anyone help me?
thank you, beth
View 9 Replies
View Related
Nov 5, 2007
I have created a stored procedure in msdb that will accept a database name and then rebuild non-clustered indexes in that database.
I placed the sp in msdb since msdb is where most job-related information is stored.
The sp identifies non-clustered indexes whose avg fragmentation exceeds a pre-determined threshold, and then runs the following statement for each index in the resulting subset:
SET @str_sql = 'ALTER INDEX ' + @str_index + ' ON ' + @str_object + ' REBUILD '
EXEC sp_executesql @str_sql
where @str_index is the name of the index and @str_object is the name of the object (view, table).
Clearly, I need to revise the statement so that it refers to the appropriate database.
Is there a way to execute 'alter index ... rebuild' so that it points to a database other than the current?
Thanks.
View 3 Replies
View Related
Dec 1, 2005
If I execute the command ALTER DATASE SET MULTI_USER WITH ROLLBACK IMMEDIATE and there are any connections to the database, the command fails with a "Lock request time out period exceeded." message. If I use SET RESTRICTED_USER, the command succeeds with the following message: "Nonqualified transactions are being rolled back. Estimated rollback completion: 100%." This seems to be a bug.
View 5 Replies
View Related
Apr 20, 2007
Hi.
I'm very new to DB stuff. I've created a SQL Server CE database and I'm trying to implement a query notification using a SqlDependency object, as per the instructions at:
http://www.codeproject.com/cs/database/chatter.asp
I've connected to my DB using SQL Server Management Studio Express, and I'm getting the following error when trying to execute an ALTER DATABASE command:
I have no clue what I could be doing wrong. Is this operation supported using CE? Is there really something wrong with my syntax?
Major Error 0x80040E14, Minor Error 25501
> alter database GESim.Diagnostics set ENABLE_BROKER
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = database ]
Help appreciated...
Just Mike
View 3 Replies
View Related
May 14, 2008
Hi,
I'm trying to change the file extension of one of the database files of a big database that contains 10 years of EPOS data.
When this file was created, at the beginning of 2008, with the following script:
USE [Live]
ALTER DATABASE [Live] ADD FILE (NAME = 'Live_2008',
FILENAME = €˜E:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataLive_2008') TO FILEGROUP [2008]
the .ndf file extension was not added to the filename. So the file now has a file type of "File", instead of "SQL Server Secondary Data File"
I tried, in a test environment, to bring the database offline, change the filename to "Live_2008.ndf" and bring the database back online, but it gives me an error message saying that "cannot open the Live_2008 file", i'm guessing because it's looking for "Live_2008" and it finds "Live_2008.ndf" instead.
This file is quite big (5Gb) and I cannot afford to risk to lose any data.
The situation is complicated even further by the fact that it seems that a partition for 2008 does not exist.
How can I find out what data this file contains?
Is it going to be an issue if I leave the file like that?
Thanks
View 3 Replies
View Related
Sep 17, 2015
I have database1 and database2. Most table names are the same between both dbs.
I would like to copy a column from db1 into db2 but since the data requirement on db2 is different i would like to change it before inserting.
Example:
Data from db1: <SomePage id="1" <template>This is a test </template>
When it saves to db2 it needs to beÂ
<Page ident="1" <content>This is a test </content>
Is this possible at all?Â
View 2 Replies
View Related
Jul 22, 2014
it is possible to create Linked server with Failover partner option. I can query when primary server and getting the error when I set the DB Fail over. I have tried with following script and also gone through different sources, but failed. Please see the script and error below.
EXEC master.dbo.sp_addlinkedserver
@server = N'MIRRORLink',
@srvproduct=N'',
@provider=N'SQLOLEDB',
@provstr=N'Server=primary;FailoverPartner=mirror;network=dbmssocn;',
[code].....
View 2 Replies
View Related
