-- Rollback any transactions and set db to single user mode
-- ALTER DATABASE [MyDB-DEV-NEW] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
Sunday, August 21, 2022
The restore operation halted prematurely.
-- The restore operation halted prematurely.
-- Repeat the original RESTORE statement specifying WITH RESTART.
-- RESTORE DATABASE [MyDB-DEV] FROM DISK = 'C:\MyDB.bak' WITH RESTART
Restore a full database backup
-- Restore a full database backup
-- RESTORE DATABASE [MyDB-DEV] FROM DISK = 'C:\MyDB.bak'
Recover, NOT Restore, the db
-- Recover, NOT Restore, the db
-- https://msdn.microsoft.com/en-us/library/ms188696.aspx
-- RESTORE DATABASE [MyDB-DEV-NEW] WITH RECOVERY
Most recent Myweb_user
-- Most recent Myweb_user
SELECT TOP 33 wu.*
FROM dbo.Myweb_user wu
ORDER BY wu.MODDATE DESC
What is the status of the backups and restores?
-- What is the status of the backups and restores?
SELECT
r.session_id
, r.command
, CONVERT(NUMERIC(6,2), r.percent_complete) [Percent Complete]
, CONVERT(VARCHAR(20), DATEADD(ms,r.estimated_completion_time,GetDate()),20) [ETA Completion Time]
, CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) [Elapsed Min]
, CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) [ETA Min]
, CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) [ETA Hours]
, CONVERT(VARCHAR(1000)
, (SELECT SUBSTRING( text,r.statement_start_offset/2,
CASE
WHEN r.statement_end_offset = -1
THEN 1000
ELSE (r.statement_end_offset-r.statement_start_offset)/2
END)
FROM sys.dm_exec_sql_text(sql_handle)
)
)
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')
Kill the active database connections
-- Kill the active database connections
-- Note: Uncomment the 'exec'
DECLARE @execSql VARCHAR (1000) = ''
, @databaseName VARCHAR (100) = ''
, @dbid INT = 0
SET @execSql = ''
SET @databaseName = 'MyDB-DNN-TMAR'
SET @dbid = db_id(@databaseName)
IF (ISNULL (@dbid, '') = '') PRINT 'Bad DB Name'
SELECT @execSql = @execSql + 'kill ' + CONVERT (CHAR(10), sp.spid) + '; '
FROM master.dbo.sysprocesses sp
WHERE dbid = @dbid
AND sp.DBID <> 0
AND sp.spid <> @@spid
SELECT @execSql
-- EXEC (@execSql)
GO
-- kill 59
Renaming Databases
-- Renaming Databases
sp_who2
-- Optional but should be done for MyDB-DEV-NEW
EXEC Utility.dbo.BACKUP_APP_INTERFACE_PARAMETER_TBL_sp
GO
-- kill 88
SP_WHO2
-- Optional but should be done for MyDB-DEV-NEW
ALTER DATABASE [MyDB-TMAR] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE [MyDB-TMAR-NEW] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
USE master;
GO
ALTER DATABASE [MyDB-TMAR]
Modify Name = [MyDB-TMAR_OLD];
GO
ALTER DATABASE [MyDB-TMAR-NEW]
Modify Name = [MyDB-TMAR];
GO
EXEC Utility.dbo.RESTORE_APP_INTERFACE_PARAMETER_TBL_sp 'MyDB-TMAR'
GO
-- ALTER DATABASE [MyDB-TMAR] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
-- ALTER DATABASE [MyDB-TMAR_OLD] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
--use utility
--go
--select * from sysobjects where type = 'p'
-- EXEC Utility.dbo.TMARDBRestore_RemoveTRSJobs 'MyJob'
EXEC Utility.dbo.TMARDBRestore_RemoveTRSJobs 'MyJob'
GO
USE [MyDB-TMAR]
GO
DROP FULLTEXT INDEX ON MyDB_product_search
GO
--DROP FULLTEXT CATALOG MyDB_product_search
--GO
ALTER DATABASE [MyDB-TMAR] SET MULTI_USER;
GO
ALTER DATABASE [MyDB-TMAR_OLD] SET MULTI_USER;
GO
USE [MyDB-TMAR]
GO
IF EXISTS (
SELECT *
FROM sys.database_principals
WHERE name = 'MyDB-'
)
DROP USER [MyDB-]
CREATE USER [MyDB-] FOR LOGIN [MyDB-]
-- ALTER ROLE [db_owner] ADD MEMBER [MyDB-]
EXEC sp_addrolemember N'db_owner', N'MyDB-'
-- NOTE: remember to add in user "Myweb" to prod1 and prod2
USE [MyDB-TMAR]
GO
IF EXISTS (
SELECT *
FROM sys.database_principals
WHERE name = 'Myweb'
)
DROP USER [Myweb]
CREATE USER [Myweb] FOR LOGIN [Myweb]
-- ALTER ROLE [db_owner] ADD MEMBER [MyDB-]
EXEC sp_addrolemember N'db_owner', N'Myweb'
GO
-- NOTE: remember to add in user "MyAdmin" to prod1 and prod2
USE [MyDB-TMAR]
GO
IF EXISTS (
SELECT *
FROM sys.database_principals
WHERE name = 'MyAdmin'
)
DROP USER [MyAdmin]
CREATE USER [MyAdmin] FOR LOGIN [MyAdmin]
-- ALTER ROLE [db_owner] ADD MEMBER [MyDB-]
EXEC sp_addrolemember N'db_owner', N'MyAdmin'
What text is the sp executing?
-- What text is the sp executing?
DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 339
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO
What users are active db x?
-- What users are active db x?
USE master
GO
IF OBJECT_ID('tempdb..#dbSPIDS') IS NOT NULL DROP TABLE #dbSPIDS
CREATE TABLE #dbSPIDS (
SPID INT
, [Status] VARCHAR (256)
, [Login] VARCHAR (256)
, HostName VARCHAR (256)
, BlkBy VARCHAR (256)
, DBName VARCHAR (256)
, Command VARCHAR (MAX)
, CPUTime INT
, DiskIO INT
, LastBatch VARCHAR (256)
, ProgramName VARCHAR (256)
, SPID2 INT
, REQUESTID INT
)
INSERT #dbSPIDS EXEC sp_who2
-- SELECT d.*
SELECT 'KILL ', d.SPID, d.*
FROM #dbSPIDS d
where login not like '%sa%'
-- WHERE d.dbname LIKE '%MyDB%dnn%TMAR'
--WHERE d.dbname = 'MyDB-d-TMAR'
-- and (d.dbname LIKE 'MyDBTMAR' OR d.dbname LIKE 'MyDBTMAR_new' )
and (d.dbname LIKE '%760%' )
-- kill 54
Try/catch syntax
-- Try/catch syntax
BEGIN TRY
SELECT 2/0
END TRY
BEGIN CATCH
SELECT Utility.dbo.fnGetErrorTryCatch ()
END CATCH
Find routine with text (wraps 4000 N)
-- Find routine with text (wraps 4000 N)
-- http://www.sqltact.com/2012/04/syssqlmodules-vs-informationschemarouti.html
DECLARE @searchString NVARCHAR (4000) = ''
SET @searchString = 'product'
SELECT s.name
+ '.'
+ o.name
, o.type_desc
, m.[definition]
FROM sys.sql_modules m
JOIN sys.objects o
ON m.object_id = o.object_id
JOIN sys.schemas s
ON s.schema_id = o.schema_id
WHERE m.[definition] like '%' + @searchString + '%'
ORDER BY o.name
Thursday, August 18, 2022
-- Common commands, settings
SET NOEXEC ON
-- SET NOEXEC OFF
-- SET NOCOUNT ON
-- SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- (the 'recommended' way to look at sysobjects)
-- SELECT TOP 33 * FROM INFORMATION_SCHEMA.Tables
-- SELECT TOP 33 * FROM INFORMATION_SCHEMA.Columns
-- SELECT TOP 33 * FROM INFORMATION_SCHEMA.Views
-- SELECT TOP 33 * FROM INFORMATION_SCHEMA.Routines
-- drop temp table
-- IF OBJECT_ID('tempdb..#dbSPIDS') IS NOT NULL DROP TABLE #dbSPIDS
Subscribe to:
Posts (Atom)