DBCC CHECKDB ('[dbname]') WITH NO_INFOMSGS, ALL_ERRORMSGS

select id, deleted,
CAST(CAST(XML AS XML).query('r/crmId/node()') AS NVARCHAR(max)) AS crmId,
CAST(CAST(XML AS XML).query('r/name/node()') AS NVARCHAR(max)) AS name
from entity
where type = 'Department'

select *
from entity
where type = 'Department'

UPDATE entity 
SET xml = replace(cast(xml as nvarchar(max)), '<crmId>1011353</crmId>', '<crmId null="1" />'),
revision=(select revision from DBVersion) 
WHERE type='Department' and
id='B9A1EA92-815E-43F8-B748-65EA676AC8D2'; -- подставить id нужной записи

import resto.config.GroupServiceMode;
em.runTransacted() {
	em.getAll("Group").each() {
		if (it.serviceMode == GroupServiceMode.TABLE_SERVICE) {
		it.updating();
		it.setServiceMode(GroupServiceMode.FAST_FOOD);
		it.update();
		out.println('fixed:   ' + it);
	} else {
		out.println('skipped: ' + it);
		}
	}
}

select * from dbo.DBVersion

update entity

set xml = stuff(cast(xml as nvarchar(MAX)),

charindex('<passwordHash>', cast(xml as nvarchar(MAX))) + 14,40,'2155245b2c002a1986d3f384af93be813537a476')

where type='User' and xml like '%<loginName>admin</loginName>%'

EXEC sp_resetstatus [dbname];
ALTER DATABASE [dbname] SET EMERGENCY
DBCC checkdb([dbname])
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ([dbname], REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE [dbname] SET MULTI_USER

ALTER DATABASE [dbname] SET ONLINE

 

ALTER DATABASE villapinia REBUILD LOG ON (NAME=villapinia_log_new, FILENAME='D:\DB\6080.villapinia\villapinia_log_new.ldf')

Select Accessor.ExternalId, Client.Name, Account.Id, AccountCounter.Id, AbstractCounter.Id, CardAccessor.Number ,AbstractCounter.CounterValue
від Accessor, Account, AccountCounter, AbstractCounter, CardAccessor, Client
Where Account.AccountableId = Accessor.id AND
Account.Id = AccountCounter.AccountId AND
AccountCounter.Id = AbstractCounter.Id AND
CardAccessor.Id = Accessor.Id AND
Client.Id = Accessor.OwnerClientId

Select Accessor.OwnerClientId, Accessor.ExternalId, Account.Id, AccountCounter.Id, AbstractCounter.Id, CardAccessor.Number ,AbstractCounter.CounterValue
від Accessor, Account, AccountCounter, AbstractCounter, CardAccessor
Where Account.AccountableId = Accessor.id AND
Account.Id = AccountCounter.AccountId AND
AccountCounter.Id = AbstractCounter.Id AND
CardAccessor.Id = Accessor.Id

SELECT [Id]
,[Revision]
,[IsDeleted]
,[WhenDeleted]
,[LegalPosition]
,[Name]
,[EMail]
,[Code]
,[OrganizationId]
FROM [iikoCard].[dbo].[Client] where id in (select OwnerClientId from Accessor);
GO

declare @RowCount int, @tablename varchar(100)

declare @Tables table (

                       PK int IDENTITY(1,1),

                       tablename varchar(100),

                       processed bit

                       )

INSERT into @Tables (tablename)

SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' and TABLE_NAME not like 'dt%' order by TABLE_NAME asc

 

declare @Space table (

name varchar(100), rows nvarchar(100), reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100)

)

select top 1 @tablename = tablename from @Tables where processed is null

SET @RowCount = 1

WHILE (@RowCount <> 0)

BEGIN

insert into @Space exec sp_spaceused @tablename

update @Tables set processed = 1 where tablename = @tablename

select top 1 @tablename = tablename from @Tables where processed is null

SET @RowCount = @@RowCount

END

 

update @Space set data = replace(data, ' KB', '')

update @Space set data = convert(int, data)/1000

update @Space set data = data + ' MB'

update @Space set reserved = replace(reserved, ' KB','')

update @Space set reserved = convert(int, reserved)/1000

update @Space set reserved = reserved + ' MB'

 

select * from @Space order by convert(int, replace(data, ' MB', '')) desc

-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
 
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
 
-- Open the cursor.
OPEN partitions;
 
-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;
 
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;
 
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
 
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

truncate table UserEventAttribute
truncate table UserEvent

RESTORE VERIFYONLY FROM DISK = 'E:\archive\DB01_02\backup\'
GO

SELECT CASE WHEN CAST(ServerNode."xml" AS XML).value('(/r/chain)[1]', 'varchar(max)') = 'true'          THEN 'Chain'
                 WHEN CAST(ServerInstance."xml" AS XML).value('(/r/registered)[1]', 'varchar(max)') = 'true' THEN 'Replicating RMS (registered)'
                 WHEN CAST(ServerInstance."xml" AS XML).value('(/r/chainNode)[1]', 'varchar(max)') > ''      THEN 'Replicating RMS (unregistered)'
                 WHEN CAST(ServerNode."xml" AS XML).value('(/r/rmsDepartment)[1]', 'varchar(max)') > ''      THEN 'Standalone RMS'
                 ELSE 'Replicating RMS (new) or unknown'
            END serverType
       FROM entity ServerInstance
  LEFT JOIN entity ServerNode ON (ServerNode.type = 'ServerNode'
                             AND CAST(ServerInstance."xml" AS XML).value('(/r/currentNode)[1]', 'varchar(max)') = CAST(ServerNode.id AS varchar(max)))
      WHERE ServerInstance.type = 'ServerInstance'
;

-Duser.timezone=Asia/Krasnoyarsk
-Dtimezone-checks-enabled=false

license-server-url=licensing://licensing.iiko.co.uk/api/1/License