Question: Identify/Remove rows contained in prior block
Posted: June 27, 2018 Filed under: Uncategorized Leave a comment
create table #temp (schedule_id int, job_id int, client_id int, day_id int,
block_start int, block_length int, block_end int, block_key nvarchar(256))
insert #temp select 17, 1, 108, 1, 72000, 3600, 75599, '17/1/108/1/72000'
insert #temp select 17, 1, 108, 1, 75600, 3600, 79199, '17/1/108/1/75600'
insert #temp select 17, 1, 108, 2, 72000, 3600, 75599, '17/1/108/2/72000'
insert #temp select 17, 1, 108, 2, 73800, 1800, 75599, '17/1/108/2/73800'
insert #temp select 17, 1, 108, 2, 75600, 3600, 79199, '17/1/108/2/75600'
insert #temp select 17, 1, 108, 2, 77400, 1800, 79199, '17/1/108/2/77400'
insert #temp select 17, 1, 108, 3, 72000, 3600, 75599, '17/1/108/3/72000'
insert #temp select 17, 1, 108, 3, 75600, 3600, 79199, '17/1/108/3/75600'
insert #temp select 17, 1, 108, 4, 72000, 7200, 79199, '17/1/108/4/72000'
insert #temp select 17, 1, 108, 4, 73800, 1800, 75599, '17/1/108/4/73800'
insert #temp select 17, 1, 108, 4, 75600, 1800, 79199, '17/1/108/4/75600'
insert #temp select 17, 1, 108, 4, 77400, 1800, 79199, '17/1/108/4/77400'
insert #temp select 17, 1, 108, 5, 72000, 3600, 75599, '17/1/108/5/72000'
insert #temp select 17, 1, 108, 5, 75600, 3600, 79199, '17/1/108/5/75600'
insert #temp select 17, 1, 108, 6, 72000, 7200, 79199, '17/1/108/6/72000'
insert #temp select 17, 1, 108, 6, 75600, 3600, 79199, '17/1/108/6/75600'
insert #temp select 17, 1, 108, 7, 68400, 1800, 70199, '17/1/108/7/68400'
insert #temp select 17, 1, 108, 7, 70200, 1800, 71999, '17/1/108/7/70200'
insert #temp select 17, 1, 108, 7, 72000, 1800, 73799, '17/1/108/7/72000'
insert #temp select 17, 1, 108, 7, 73800, 1800, 75599, '17/1/108/7/73800'
insert #temp select 17, 1, 108, 7, 75600, 3600, 79199, '17/1/108/7/75600'
insert #temp select 17, 1, 108, 7, 77400, 1800, 79199, '17/1/108/7/77400'
--My solution
--**** create a Number table
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Num3 (n) AS (SELECT 1 FROM Num1 AS X, Num2 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num3)
,mycte as (select *
,count(*) Over(partition by day_id,block_start+n-1) cnt
,row_number()Over(partition by day_id,block_start+n-1 Order by block_start ) rn
from #temp
Cross apply (select n from nums) d(n)
where n1 and rn=1)
order by schedule_id, job_id, client_id, day_id, block_start, block_length desc
---Sarat's query (this is much better with Exists)
select * from #temp t1
where not exists
(select 1 from #temp t2
where t1.day_id = t2.day_id
and ( t1.block_start > t2.block_start and
t1.block_end <= t2.block_end ) and t1.block_key t2.block_key
)
if object_id('tempdb..#temp') is not null
drop table #temp
Get Database Restore Duration
Posted: June 22, 2018 Filed under: Uncategorized Leave a commentIt may not that straightforward to get a database restoration time from msdb database. Here are a few options to get database restoration time discussed from stackexchange site. You can try out each of them. I like the newer sample query from John Eisbrener.
https://dba.stackexchange.com/questions/101291/history-of-database-restore-completion-times
Find Available Time Slot
Posted: June 18, 2018 Filed under: Uncategorized Leave a comment
CREATE TABLE [dbo].[ReservationDetails](
[SessionID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [bigint] NOT NULL,
[ExpectedStart] [datetime] NOT NULL,
[ExpectedEnd] [datetime] NOT NULL)
INSERT INTO ReservationDetails (UserID,ExpectedStart,ExpectedEnd)
VALUES (1,'1900-01-01 09:15:00.000','1900-01-01 09:30:00.000')
INSERT INTO ReservationDetails (UserID,ExpectedStart,ExpectedEnd)
VALUES (2,'1900-01-01 10:00:00.000','1900-01-01 10:30:00.000')
--select * from ReservationDetails
--to list possible free/available time slots between 09:00 AM to 11:30 AM.
declare @starttime datetime='1900-01-01 9:00:00.000'
declare @endtime datetime='1900-01-01 11:30:00.000'
--===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
,mytime as (
select dateadd(minute,n-1, starttime) dt
from (select @starttime starttime) t
cross apply (select n from Nums) d(n)
WHERE dateadd(minute,n-1,starttime) ExpectedStart and dt<ExpectedEnd)
)
--Result
select row_number() Over(order by grp) rn,
Format(min(dt),'hh:mm tt') as [From]
,Format(max(dt),'hh:mm tt') as [To]
from mycte2
group by grp
drop table ReservationDetails
Restore smart_admin Backup File Stored at Azure Storage
Posted: June 14, 2018 Filed under: Uncategorized Leave a commentRestored database from Azure storage. The backup was created by smart_admin at server to Azure (a subscription URL).
The server holds all backup information. It can be retrieved from calling a function for specific database: msdb.smart_admin.fn_available_backups(‘dbname’).
To get most recent full backup date and the file path, use OPENQUERY through linked server to retrieve these information.
With the full backup file path, a typical restore command with from URL to access the backup file and restore it at remote location. Since encryption involved, you need to open a valid master key to make sure you can restore it.
Please check a simplified version of the restore script.
The script can be put into a stored procedure and use an agent job to execute it with a schedule.
OPEN Master Key Decryption BY PASSWORD = 'myAzureSuperPa$$'
--Change database name
declare @dbName Nvarchar(128)='myDbName'
declare @restoreSQLfromAzure Nvarchar(4000)=''
declare @bkFile varchar(1000),@bkdt varchar(10)
declare @sqlfilenamedate nvarchar(4000)=''
Select @sqlfilenamedate=N'select @bkFile= backup_path, @bkdt=format(backup_finish_date ,''yyyyMMdd'')
from OPENQUERY ([Linked_Server_theMainServerDBBackupOccurred], ''select top 1 backup_path, backup_finish_date from msdb.smart_admin.fn_available_backups(''''' + @dbName + ''''')
where backup_type=''''DB''''
order by backup_finish_date DESC'')'
--print @sqlfilenamedate
execute sp_executesql @sqlfilenamedate, N'@bkFile varchar(1000) OUTPUT,@bkdt varchar(10) OUTPUT', @bkFile=@bkFile OUTPUT,@bkdt=@bkdt OUTPUT ;
Set @restoreSQLfromAzure=N'RESTORE DATABASE ['+@dbName+@bkdt+'] FROM URL = N'''+@bkFile+ '''
WITH CREDENTIAL = ''myAzureStorageCredentialSecret''
,File=1, NOUNLOAD, REPLACE, STATS = 20
,MOVE '''+ @dbName +''' TO ''C:\MSSQL\'+@dbName+@bkdt+'.mdf''' +'
,MOVE '''+ @dbName+'_log'' TO ''C:\MSSQL\'+@dbName+@bkdt+'_Log.ldf'''
--print @restoreSQLfromAzure
execute(@restoreSQLfromAzure)
Insert Data Into a Table with Identity Column
Posted: June 1, 2018 Filed under: Uncategorized Leave a commentcreate table dbo.t1 (id int identity(100,3),col1 int, col2 varchar(10)) insert into dbo.t1 (col1,col2) values(11,'foo1'),(12,'foo2'),(13,'foo3') create table dbo.t2 (id int identity,col1 int, col2 varchar(10)) select * from dbo.t1 declare @qrytext varchar(1000), @filepath varchar(1000) set @filepath = ' "C:\temp\mytable_Data.txt" ' set @qrytext = '"SELECT * from mydb.dbo.t1 "' --@qrytext should be in one line -- generate format file and need to remove the first row in the format file to skip a table column DECLARE @cmd1 VARCHAR(4000) set @cmd1 ='bcp [myDB].[dbo].[t1] format nul -f C:\temp\mytableFormatFile.fmt -c -t\t -r\n -T -S '+ @@servername;; exec master..xp_cmdshell @cmd1, NO_OUTPUT --export data DECLARE @cmdOut VARCHAR(4000) set @cmdOut ='BCP ' + @qrytext + ' QUERYOUT ' + @filepath + ' -n -c -T -S '+ @@servername;; exec master..xp_cmdshell @cmdOut, NO_OUTPUT --import data DECLARE @cmdIn VARCHAR(4000) set @cmdIn = 'bcp myDB.dbo.t2 IN ' + @filepath + ' -f C:\temp\mytableFormatFile.fmt -E -T -S '+ @@servername exec master..xp_cmdshell @cmdIn, NO_OUTPUT --clean up DECLARE @cmdDelete VARCHAR(4000) SET @cmdDelete ='del '+ @filepath exec master..xp_cmdshell @cmdDelete, NO_OUTPUT select * from t2 drop table t1,t2
Jingyang Li 