Question: Identify/Remove rows contained in prior block

  
 
 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


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a1c9f8b8-23ff-4b3c-be22-607fa7655664/identifyremove-rows-contained-in-prior-block?forum=transactsql

Advertisements

Get Database Restore Duration

It 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

  
 
 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 


 

https://forums.asp.net/t/2142473.aspx?How+to+find+available+or+free+timeslots+between+2+times+sql+server


Restore smart_admin Backup File Stored at Azure Storage

Restored 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

  

 
 create 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



 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e63aa42f-bd33-4a1b-90de-a082504a8866/inserting-data-without-using-column-names?forum=transactsql