1. Delete all the databases having a specific prefix, in the example below it will drop all the databases starting with habitat.dev.local_ - this may be helpful when Sitecore 9 installation broke half way down and you'd like to return everything to an initial point, also removing (partly) installed databases:
USE MASTER
GO
DECLARE @dbnames NVARCHAR(MAX)
DECLARE @statement NVARCHAR(MAX)
SET @dbnames = ''
SET @statement = ''
SELECT @dbnames = @dbnames + ',[' + name + ']' FROM sys.databases WHERE NAME LIKE 'habitat.dev.local_%'
IF LEN(@dbnames) = 0
BEGIN
PRINT 'no databases to drop'
END
ELSE
BEGIN
SET @statement = 'drop database ' + SUBSTRING(@dbnames, 2, LEN(@dbnames))
PRINT @statement
EXEC sp_executesql @statement
END
2. Backup all SQL database having some prefix (in this case, habitat.dev.local_) to a folder (like C:\DatabaseBackups\):
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @scPrefix VARCHAR (20) -- Sitecore database prefix
-- specify database backup directory
SET @path = 'C:\DatabaseBackups\'
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
-- specify the Sitecore db prefix
SET @scPrefix = 'habitat.dev.local_'
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE CharIndex(@scPrefix, name) = 1
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
3. Shrink all databases larger than a certain number of megabytes (stored in @megabytesMoreToShrink variable):
DECLARE @megabytesMoreToShrink INT SET @megabytesMoreToShrink = 100
DECLARE @name NVARCHAR(MAX)
DECLARE @logical_name NVARCHAR(MAX)
DECLARE db_cursor CURSOR FOR
SELECT db.name AS DBName, mf.Name AS Logical_Name
FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id Where type_desc = 'LOG' AND (size*8)/1024 > @megabytesMoreToShrink
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name, @logical_name
WHILE @@FETCH_STATUS = 0
BEGIN
exec('USE ' + @name + ' ;ALTER DATABASE ' + @name + ' SET RECOVERY SIMPLE;DBCC SHRINKFILE ('''+@logical_name +''', 1);ALTER DATABASE '+@name +' SET RECOVERY FULL')
FETCH NEXT FROM db_cursor INTO @name, @logical_name
END
CLOSE db_cursor
DEALLOCATE db_cursor
4. Change sa password for SQL instance, you do not need to know the previous password at all, but need to have an administrative access, for sure. Run osql tool, it's path should be added to the environment variable, but if it's not there, please find it here: "%ProgramFiles%\Microsoft SQL Server\130\Tools\Binn\OSQL.exe":
osql -S SITECOREDEV -E
1> sp_password NULL, 'SA_PASSWORD','sa'
2> GO
5. Some basic item's operations, such as retrieving by ID, by parent and retrieving fields and versions:
-- Get item by Sitecore ID
SELECT TOP 1000 *
FROM [habitat_Master].[dbo].[Items]
WHERE ID = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}'
-- Get item by ID of parent in Sitecore
SELECT TOP 1000 *
FROM [habitat_Master].[dbo].[Items]
WHERE ID = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}'
-- Enumerate all the versioned fieds for an item in Sitecore by item ID
SELECT TOP 1000
i.Name, v.[Id], [ItemId], [LANGUAGE], [FieldId], [Value], v.[Created], v.[Updated]
FROM [habitat_Master].[dbo].[versionedFields] v
JOIN [habitat_Master].[dbo].Items i
ON v.FieldId = i.ID
WHERE ItemId = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}'
ORDER BY [Name]5. Some basic item's operations, such as retrieving by ID, by parent and retrieving fields and versions:
-- Get item by Sitecore ID
SELECT TOP 1000 *
FROM [habitat_Master].[dbo].[Items]
WHERE ID = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}'
-- Get item by ID of parent in Sitecore
SELECT TOP 1000 *
FROM [habitat_Master].[dbo].[Items]
WHERE ID = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}'
-- Enumerate all the versioned fieds for an item in Sitecore by item ID
SELECT TOP 1000
i.Name, v.[Id], [ItemId], [LANGUAGE], [FieldId], [Value], v.[Created], v.[Updated]
FROM [habitat_Master].[dbo].[versionedFields] v
JOIN [habitat_Master].[dbo].Items i
ON v.FieldId = i.ID
WHERE ItemId = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}'
ORDER BY [Name]6. One of the most popular snippets, to restore default admin / b password. Please keep in mind running that against core database:
-- use core database
UPDATE dbo.aspnet_Membership
SET
Password='qOvF8m8F2IcWMvfOBjJYHmfLABc=',
PasswordSalt='OM5gu45RQuJ76itRvkSPFw=='
WHERE
UserId = (SELECT UserId FROM dbo.aspnet_Users WHERE UserName = 'sitecore\Admin')7. Copy users and all their data including passwords from core database of one instance to another:
-- Copy all non-existing users and their data from OLD core database to NEW core database
INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_Applications SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_Applications
WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_Applications WHERE [Sitecore.NEW.Core].dbo.aspnet_Applications .ApplicationId = [Sitecore.OLD.Core].dbo.aspnet_Applications.ApplicationId)
INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_Membership SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_Membership
WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_Membership WHERE [Sitecore.NEW.Core].dbo.aspnet_Membership .UserId = [Sitecore.OLD.Core].dbo.aspnet_Membership.UserId)
INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_Paths SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_Paths
WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_Paths WHERE [Sitecore.NEW.Core].dbo.aspnet_Paths .PathId = [Sitecore.OLD.Core].dbo.aspnet_Paths.PathId)
INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_PersonalizationAllUsers SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_PersonalizationAllUsers
WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_PersonalizationAllUsers WHERE [Sitecore.NEW.Core].dbo.aspnet_PersonalizationAllUsers.PathId = [Sitecore.OLD.Core].dbo.aspnet_PersonalizationAllUsers .PathId)
INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_PersonalizationPerUser SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_PersonalizationPerUser
WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_PersonalizationPerUser WHERE [Sitecore.NEW.Core].dbo.aspnet_PersonalizationPerUser.PathId = [Sitecore.OLD.Core].dbo.aspnet_PersonalizationPerUser .PathId)
INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_Profile SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_Profile
WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_Profile WHERE [Sitecore.NEW.Core].dbo.aspnet_Profile.UserId = [Sitecore.OLD.Core].dbo.aspnet_Profile.UserId)
INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_Roles SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_Roles
WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_Roles WHERE [Sitecore.NEW.Core].dbo.aspnet_Roles.RoleName = [Sitecore.OLD.Core].dbo.aspnet_Roles.RoleName)
INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_SchemaVersions SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_SchemaVersions
WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_SchemaVersions WHERE [Sitecore.NEW.Core].dbo.aspnet_SchemaVersions.Feature = [Sitecore.OLD.Core].dbo.aspnet_SchemaVersions.Feature)
INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_Users SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_Users
WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_Users WHERE [Sitecore.NEW.Core].dbo.aspnet_Users.UserName = [Sitecore.OLD.Core].dbo.aspnet_Users.UserName)
INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_UsersInRoles SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_UsersInRoles
WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_UsersInRoles WHERE [Sitecore.NEW.Core].dbo.aspnet_UsersInRoles.UserId = [Sitecore.OLD.Core].dbo.aspnet_UsersInRoles.UserId)
INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_WebEvent_Events SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_WebEvent_Events
WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_WebEvent_Events WHERE [Sitecore.NEW.Core].dbo.aspnet_WebEvent_Events.EventId = [Sitecore.OLD.Core].dbo.aspnet_WebEvent_Events .EventId)8. Delete children item of an item with a given ID directly from SQL (beware item cache). Please make sure you execute that in a context of a Sitecore database (ie. call use Sitecore_master before running this):
DECLARE @parentId AS UNIQUEIDENTIFIER; SET @parentId = '{29267838-AAB2-415F-B07A-D006719CD088}'
DECLARE @RowsToProcess INT
DECLARE @CurrentRow INT
DECLARE @SelectCol1 UNIQUEIDENTIFIER
DECLARE @Items2Delete TABLE (RowID INT NOT NULL PRIMARY KEY IDENTITY(1,1),ID UNIQUEIDENTIFIER)
SET @parentId = '{29267838-AAB2-415F-B07A-D006719CD088}'
INSERT INTO @Items2Delete (ID)
SELECT [Descendant] AS ID
FROM [dbo].[Descendants]
WHERE Ancestor = @parentId
SET @RowsToProcess=@@ROWCOUNT
PRINT @RowsToProcess
INSERT INTO @Items2Delete (ID)
SELECT ID FROM [dbo].[Items] WHERE ParentID = @parentId
SET @RowsToProcess=@@ROWCOUNT
PRINT @RowsToProcess
SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
SET @CurrentRow=@CurrentRow+1
SELECT
@SelectCol1=ID
FROM @Items2Delete
WHERE RowID=@CurrentRow
DECLARE @id AS UNIQUEIDENTIFIER;
SET @id = @SelectCol1
PRINT @id
EXEC sp_executesql N'DELETE FROM [Items]
WHERE [ID] = @itemId
DELETE FROM [SharedFields]
WHERE [ItemId] = @itemId
DELETE FROM [UnversionedFields]
WHERE [ItemId] = @itemId
DELETE FROM [VersionedFields]
WHERE [ItemId] = @itemId',N'@itemId UNIQUEIDENTIFIER',@itemId=@id
EXEC sp_executesql N'DELETE FROM [Descendants] WHERE [Descendant] = @itemId',N'@itemId uniqueidentifier',@itemId=@id
EXEC sp_executesql N' DELETE FROM [Links] WHERE [SourceItemID] = @itemID AND [SourceDatabase] = @database',N'@itemID uniqueidentifier,@database nvarchar(6)',@itemID=@id,@database=N'master'
EXEC sp_executesql N'DELETE FROM [Tasks] WHERE [ItemID] = @itemID AND [Database] = @database',N'@itemID uniqueidentifier,@database nvarchar(6)',@itemID=@id,@database=N'master'
ENDHope that helps you!
