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

Advertisements

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


Robocopy to Delete File Older than Two Weeks and with Timestamp in Log File Name

In a batch file:
::Include datetime format yyyy-mm-dd_hhmmss in log filename
set dt=%DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2%
set dt=%dt: =_%
set tm=%time:~0,2%%time:~3,2%%time:~6,2%
set tm=%tm: =0%
set Now=%dt%_%tm%
IF NOT EXIST \\src\empty_daily mkdir \\des\empty_dummy
Robocopy \\src\Filefolder \\des\empty_dummy *.* /s /MOVE /MINAGE:14 /W:5 /R:5 /log+:E:\log\%Now%_log.txt
rmdir \\des\empty_dummy/s /q

Another way in a batch file:
::Include datetime format yyyymmddhhmmss in log filename

For /F “Tokens=1-7 Delims=/:.- ” %%d In (“%Date%-%Time%”) Do Set Now=%%g%%e%%f%%h%%i%%j
IF NOT EXIST \\src\empty_daily mkdir \\des\empty_dummy
Robocopy \\src\Filefolder \\des\empty_dummy *.* /s /MOVE /MINAGE:14 /W:5 /R:5 /log+:E:\log\%Now%_log.txt
rmdir \\des\empty_dummy/s /q

https://gallery.technet.microsoft.com/scriptcenter/0595e5d5-184c-44ab-847e-056c2db3c253


Pass SSIS Package Parameter Values From Agent Job to Run a bat File with These Variables.

1. bat file
Design a bat file to use robocopy with three parameters for MINAGE and two file names with wildcards.
The parametera are used as percentage sign and a number starts at 1, like %1 %2 and %3.

2.SSIS package
Design an SSIS package to pass variables to the bat file

Create three SSIS variables: SSIS tab >> create one int32 (varNum) and two String variables (varDBName1 and varDBName2) and assign some default values if you choose to.
Next step to connect these variables to your bat file.

Use Execute Process Task:
From Execute Process Task Editor Process tab:
Execute—-E:\X\temp\mybatfilewiththreevariables.bat
Argument leave it blank and use the Expression tab to add argument for the string of these variables in a concatenation syntax.
WorkingDirectory—E:\temp\
StandardInputVariable — pick any variable as a first one

From Execute Process Task Editor Expression tab:
Expand Expression tab >>Property Expressions Editor from Property column, pick Arguments, then expand Expression tab (click on … tab>> Expression Builder window pop up.
link all variables as a string in this manner:
(DT_WSTR, 10) @[User::varNum]+” “+(DT_WSTR, 10) @[User::varDBName2]+” “+(DT_WSTR, 10) @[User::varDBName2]

Cast variable to datatype DT_WSTR and concatenate them with +” “+ to become a string with space separate them.
You can click on Evaluate Expression at the bottom and will see the Evaluated value from your default values.
Close all open windows and save your package.

3. Agent job

Create and agent job to run the SSIS package with three parameters to pass to a bat file to use robocopy inside the bat file.
@command=N’/FILE “\”E:\packages\passmultipleSSISParameterValuesInAgentJob.dtsx\”” /CHECKPOINTING OFF /SET “\”\Package.Variables[User::varNum]\””;6 /SET “\”\Package.Variables[User::vardbname1]\””;”\”\”\”myDb1\”\”\”” /SET “\”\Package.Variables[User::vardbname2]\””;”\”\”\”mydb2\”\”\”” /REPORTING E’,

Double click on AgentJob name >> from Job Properties window>>Click on Steps from left >> Click on Edit >> Job Step Properties >> click Set values tab >>
Add Property Path and value as below for each variable:
For integer parameter
Property Path: \Package.Variables[User::varNum]
Value: 1
For string type parameter, the Value needs double quotation around the string.
Property Path: \Package.Variables[User::varNum]
Value: “varDBName”

4.Check the bat file and an example

Retrieve these parameters from your bat file in the syntax %1 %2 %3 and use them in the place to replace static value.
An example to use these three parameters to use robocopy to copy and clean files:

IF NOT EXIST \\desfolder\empty mkdir \\desfolder\empty
Robocopy \\src \\desfolder\empty /s /MOVE /MINAGE:%1 /LOG+:E:\log\%DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2%_%TIME:~0,2%-%TIME:~3,2%_mylog.txt
rmdir \\desfolder\empty /s /q
Robocopy \\src \\desfolder *%2*.* *%3*.* /s /z /np /MT:32 /W:5 /R:5 /log+:E:\log\%DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2%_%TIME:~0,2%-%TIME:~3,2%_mylog.txt


Dynamic Unpivot and Pivot with Case

  

 
  
create table test (emp_id int 
,[17-Jul] decimal(6,2) 
,[17-Aug] decimal(6,2)
,[17-Sep] decimal(6,2) 
,[17-Oct] decimal(6,2) 
,[17-Nov] decimal(6,2) 
,[17-Dec] decimal(6,2)
,[18-Jan] decimal(6,2) 
,[18-Feb] decimal(6,2) 
,[18-Mar] decimal(6,2) 
,[18-Apr] decimal(6,2) 
,[18-May] decimal(6,2) 
,[18-Jun] decimal(6,2))

 Insert into test 
 values(597, 0,0,0,84.01,0,0,0,0,0,95.13,0,0)


 select * from test
 Declare @sqlUnpivot as NVarchar(4000)
  Declare @sqlPivot as NVarchar(4000)
Declare @ColsUnpivot as NVarchar(4000)=null
Declare @ColsPivot as NVarchar(4000)=null
 
 
 
Select @ColsUnpivot =  COALESCE(@ColsUnpivot + ', ', '') + '('+QUOTENAME(COLUMN_NAME,'''')+','  + QUOTENAME(COLUMN_NAME) +','+  Cast(ORDINAL_POSITION as varchar(3)) +')'
FROM   [INFORMATION_SCHEMA].[COLUMNS]
  WHERE TABLE_NAME='test' and COLUMN_NAME'emp_id'

  Select @ColsPivot =  COALESCE(@ColsPivot + ', ', '')  + QUOTENAME(COLUMN_NAME)  
FROM   [INFORMATION_SCHEMA].[COLUMNS]
  WHERE TABLE_NAME='test' and COLUMN_NAME'emp_id'
 
 
Select @sqlUnpivot='Select
emp_id,Col, Nullif(YY_MMM,0) YY_MMM, ORDINAL_POSITION
into temp
FROM test t
CROSS APPLY (Values ' + @ColsUnpivot + '  )  d(Col, YY_MMM,ORDINAL_POSITION) '
exec (@sqlUnpivot)
--print @sqlUnpivot
  
Select @ColsPivot = STUFF( (SELECT  ',' + 'Max(CASE WHEN Col=' + quotename(Col,'''') + ' THEN [YY_MMM] else 0 end ) as ' + quotename(Col,'[')  + char(10)+char(13)
                             FROM temp
							 order by ORDINAL_POSITION
                             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
  --print @ColumnHeaders
 
Set @sqlPivot  =' Select emp_id,'+   @ColsPivot + ' from 
( SELECT emp_id, Col, ORDINAL_POSITION, 
CAST(SUBSTRING(MAX( CAST(ORDINAL_POSITION AS BINARY(4)) + CAST(Nullif(YY_MMM,0) AS BINARY(8)) )
OVER( ORDER BY ORDINAL_POSITION ASC ROWS UNBOUNDED PRECEDING ),5,8) AS Decimal(6,2) ) [YY_MMM]
FROM temp) t Group by emp_id    ';
     
--print @sqlPivot
EXEC(@sqlPivot)

--clean up temp table
 drop table temp


 drop table test