BACKING UP and Restore AZURE SQL DATABASE

Jason Strate published two blogs about how to backup and restore Azure databases. The links to these two blog entries:

Backup:
http://www.jasonstrate.com/2013/04/backing-up-azure-sql-database-to-the-cloud/

Restore:
http://www.jasonstrate.com/2013/04/restoring-azure-sql-database-to-a-local-server/

Advertisements

Set Cascade DELETE/UPDATE in SQL Server

Try to set up cascading delete/update in your database through SSMS. Here is the steps:

You can set the Delete/Update Rule to Cascade for the foreign key. In SQL Server Management Studio, Click on your table name and find the Keys and expand i;, right click on the foreignkey name in your key collection and click Modify>> under INSERT and UPDATE specification to see the Delete Rule and Update Rule. The default value is No Action for DELETE Rule or UPDATE Rule. You need to change the value to Cascade.

You can use T-SQL script to set these:

 
ALTER TABLE [dbo].[ChildTable]  WITH CHECK ADD  CONSTRAINT [FK_ChildTable_ParentTable] FOREIGN KEY([aid])
REFERENCES [dbo].[ParentTable] ([AId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

Restore Database with New Names (T-SQL)




USE [master]
GO



BEGIN


DECLARE @Destinationdb    AS NVARCHAR(255)= 'myDB', --Database Name to be restored
        @fullbackup       AS NVARCHAR(2000),
        @diffbackup       AS NVARCHAR(2000)=NULL,
        @logbackup1       AS NVARCHAR(2000)=NULL,
        @logbackup2       AS NVARCHAR(2000)=NULL,
	@logbackup3       AS NVARCHAR(2000)=NULL,
        @desPathData      AS NVARCHAR(2000) ='C:\MSSQL\Data\',
        @desPathLog        AS NVARCHAR(2000) ='C:\MSSQL\Log\' ,
        @backupSourcePath AS NVARCHAR(2000) ='C:\Backup\',
	--Check filestream feature
	@includeFilestream bit=0

----1.Full backup file
SET @fullbackup = N'MyDB_FULL.bak'

----2.Most recent Diff backup since Full backup (Optional)
--SET @diffbackup = N'MyDB_DIFF_20120712_080511.bak'
----3. Moste recent Log backups since Diff backup (Optional and upto three log files) 
--SET @logbackup1 = N'MyDB_LOG_20120712_083107.trn' 
----SET @logbackup2 = N'MyDB_LOG_20120711_144502.trn' 
----SET @logbackup3 = N'MyDB_LOG_20120711_150002.trn' 


/***************************************************************/


  --Full path to backup files

SET @fullbackup = @backupSourcePath +  @fullbackup
SET @diffbackup = @backupSourcePath +  @diffbackup
SET @logbackup1 = @backupSourcePath +  @logbackup1
SET @logbackup2 = @backupSourcePath +  @logbackup2
SET @logbackup3 = @backupSourcePath +  @logbackup3




DECLARE @CreateNewDatabaseSQL AS NVARCHAR(4000),
        @cmdFull              AS NVARCHAR(2000),
        @cmdDiff              AS NVARCHAR(2000),
        @cmdLog1              AS NVARCHAR(2000),
        @cmdLog2              AS NVARCHAR(2000),
	@cmdLog3              AS NVARCHAR(2000),
        @cmdFinal             AS NVARCHAR(2000),
        @cmdGetFileNames      AS NVARCHAR(1000),
        @cmdUpdateLogicalName NVARCHAR(2000),
        @s                    VARCHAR(128) 



--Check if the Destination Database exists
IF NOT EXISTS (SELECT 1
               FROM   sys.sysdatabases
               WHERE  name = @destinationdb)
  BEGIN
      SET @CreateNewDatabaseSQL='CREATE DATABASE [' + @destinationdb
               + '] ON  PRIMARY ( NAME = N'''
               + @destinationdb + ''', FILENAME = N'''
               + @desPathData + @destinationdb
               + '.mdf'')'
             If  (@includeFilestream<>0)  
               begin
               SET @CreateNewDatabaseSQL=@CreateNewDatabaseSQL+', FILEGROUP  ['
               + @destinationdb + '] CONTAINS FILESTREAM  DEFAULT (NAME = N'''  + @destinationdb + '_filestream'', FILENAME = N''' + @desPathData + @destinationdb + '_filegroup'')'
               end
               
         SET @CreateNewDatabaseSQL=@CreateNewDatabaseSQL+' LOG ON ( NAME = N''' + @destinationdb
               + '_log'', FILENAME =  N''' + @desPathLog
               + @destinationdb + '_log.ldf'')'
--print @CreateNewDatabaseSQL
EXEC (@CreateNewDatabaseSQL)
  END





CREATE TABLE #backupdetails
  (
     LogicalName          NVARCHAR(255),
     PhysicalName         NVARCHAR(255),
     Type                 NVARCHAR(1),
     FileGroupName        NVARCHAR(255),
     Size                 BIGINT,
     MaxSize              BIGINT,
     FileId               INT NULL,
     CreateLSN            NUMERIC(25, 0) NULL,
     DropLSN              NUMERIC(25, 0) NULL,
     UniqueFileId         UNIQUEIDENTIFIER NULL,
     readonlyLSN          NUMERIC(25, 0) NULL,
     readwriteLSN         NUMERIC(25, 0) NULL,
     BackupSizeInBytes    BIGINT NULL,
     SourceBlkSize        INT NULL,
     FileGroupId          INT NULL,
     LogGroupGuid         UNIQUEIDENTIFIER NULL,
     DifferentialBaseLsn  NUMERIC(25, 0) NULL,
     DifferentialBaseGuid UNIQUEIDENTIFIER NULL,
     IsReadOnly           BIT NULL,
     IsPresent            BIT NULL,
     --SQL Server 2008 Only
     TDEThumbprint        VARBINARY(32) NULL
  ) 





SET @cmdGetFileNames ='RESTORE FILELISTONLY FROM DISK='''
                      + @fullbackup + ''''

INSERT #backupdetails
EXEC(@cmdGetFileNames) 



-- build the restore command
SELECT @cmdFull = NULL,
       @cmdUpdateLogicalName = NULL,
       @s = ''

WHILE @s < (SELECT Max(LogicalName)
            FROM   #backupdetails)
  BEGIN
      BEGIN
          SELECT @s = Min(LogicalName)
          FROM   #backupdetails
          WHERE  LogicalName > @s

   
		      SELECT @cmdFull = COALESCE(@cmdFull + ', MOVE ', '') + '''' + @s
                        + ''' to ''' + CASE WHEN #backupdetails.type = 'D' THEN ''+ @desPathData+'' WHEN #backupdetails.type = 'L' THEN ''+ @desPathLog+'' WHEN #backupdetails.type = 'S' THEN ''+ @desPathData+''ELSE ''+ @desPathData+''  END +                        
                        @destinationdb + CASE WHEN #backupdetails.type = 'D' THEN '.mdf' WHEN #backupdetails.type = 'L' THEN '.ldf' ELSE'' +@s+''  END + ''''
          FROM   #backupdetails
          WHERE  LogicalName = @s
      END

      BEGIN
          SELECT @cmdUpdateLogicalName = COALESCE(@cmdUpdateLogicalName, '')
                                         + ' 
                                         ALTER DATABASE [' + @destinationdb
                                         + ']
                 MODIFY FILE (NAME= [' + @s + '], NEWNAME=[' + @destinationdb + ''+ CASE WHEN #backupdetails.type = 'D' THEN '] ' WHEN #backupdetails.type = 'L' THEN '_Log] '  WHEN #backupdetails.type = 'S' THEN '_FileGroup]' ELSE '-' END + ')'
          FROM   #backupdetails
          WHERE  LogicalName = @s
                 AND LogicalName <> @destinationdb + CASE WHEN #backupdetails.type = 'D' THEN '' WHEN #backupdetails.type = 'L' THEN '_Log' WHEN #backupdetails.type = 'S' THEN '_FileGroup'  ELSE '' END
      END
  END


  SELECT @cmdFull = 'Restore database ' + @destinationdb
                  + ' from disk = ''' + @fullbackup
                  + ''' WITH REPLACE,NORECOVERY,  MOVE '
                  + @cmdFull

IF ( @diffbackup IS NOT NULL )
  BEGIN
      SELECT @cmdDiff = 'Restore database ' + @destinationdb
                        + ' from disk = ''' + @diffbackup
                        + ''' WITH NORECOVERY'
  END

IF ( @logbackup1 IS NOT NULL )
  BEGIN
      SELECT @cmdLog1 = 'Restore database ' + @destinationdb
                           + ' from disk = ''' + @logbackup1
                           + ''' WITH NORECOVERY'
  END

IF ( @logbackup2 IS NOT NULL )
  BEGIN
      SELECT @cmdLog2 = 'Restore database ' + @destinationdb
                           + ' from disk = ''' + @logbackup2
                           + ''' WITH NORECOVERY'
  END

  IF ( @logbackup3 IS NOT NULL )
  BEGIN
      SELECT @cmdLog3 = 'Restore database ' + @destinationdb
                           + ' from disk = ''' + @logbackup3
                           + ''' WITH NORECOVERY'
  END

SELECT @cmdFinal = 'Restore database ' + @destinationdb
                   + ' WITH RECOVERY' 


DROP TABLE #backupdetails 


DECLARE @changeMode AS NVARCHAR(255)

--Change to Single-user mode
SET @changeMode='USE [' + @destinationdb
                + ']    ALTER DATABASE [' + @destinationdb
                + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE '

EXEC(@changeMode)
--Restore Full backup with NORecovery to continue
EXEC (@cmdFull)

--Restore Diff backup 
IF ( @cmdDiff IS NOT NULL )
  EXEC (@cmdDiff)


--Restore Log backup 
IF ( @logbackup1 IS NOT NULL )
  EXEC (@cmdLog1)
--Restore Log backup2 
  IF ( @logbackup2 IS NOT NULL )
  EXEC (@cmdLog2)
  --Restore Log backup3
IF ( @logbackup3 IS NOT NULL )
  EXEC (@cmdLog3)


--Restore with Recovery to finish restore process
EXEC (@cmdFinal) 



--Update Logical names for the new database
EXEC (@cmdUpdateLogicalName)

--Change to RECOVERY SIMPLE
SET @changeMode='USE [' + @destinationdb
                + ']    ALTER DATABASE [' + @destinationdb
                + '] SET RECOVERY SIMPLE'

EXEC(@changeMode)

--Change to Single-user mode
SET @changeMode='USE [' + @destinationdb
                + ']    ALTER DATABASE [' + @destinationdb
                + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE  CHECKPOINT  DBCC SHRINKFILE (2, 500);'

EXEC(@changeMode)

--Change back to multiple user mode
SET @changeMode='ALTER DATABASE [' + @destinationdb
                + '] SET MULTI_USER'

EXEC(@changeMode)

--Change to RECOVERY FULL
SET @changeMode='USE [' + @destinationdb
                + ']    ALTER DATABASE [' + @destinationdb
                + '] SET RECOVERY FULL'

--EXEC(@changeMode) 


END



Check Restore Process with sys.dm_exec_requests (T-SQL)


SELECT percent_complete , (estimated_completion_time/1000)/60 Estimated_completion_time_Mins,
    [spid] = r.session_id,
    [database] = DB_NAME(r.database_id),
    r.start_time,
    r.[status],
    r.command,
    /* add other interesting columns here */
    [obj] = QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.[dbid]))
    + '.' + QUOTENAME(OBJECT_NAME(t.objectid, t.[dbid])),
    t.[text]
FROM
    sys.dm_exec_requests AS r
CROSS APPLY
    sys.dm_exec_sql_text(r.[sql_handle]) AS t
WHERE
    r.session_id <> @@SPID
    AND r.session_id > 50



http://sqlblog.com/blogs/aaron_bertrand/archive/2008/07/01/sys-dm-exec-requests.aspx


A Usage of Row_Number Function (T-SQL)

An example to use Row_Number function to solve an complex problem on MSDN:
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/84a30edf-67a9-4598-8aef-cc6594135113



create table #temp
(rownum int,
id int,
r_id varchar(10),
ord int,
w_no int,
r_no varchar(10),
dt_Frm datetime,
tr_time datetime
)
insert into #temp values ('2','101','101||3','3','37','925','2012-09-27','1900-01-01 14:30:00.000')
insert into #temp values ('4','101','101||8','8','55','978','2012-10-18','1900-01-01 08:51:00.000')
insert into #temp values ('5','101','101||9','9','55','976','2012-10-18','1900-01-01 14:44:00.000')
insert into #temp values ('6','101','101||12','12','42','1425','2013-01-01','1900-01-01 12:12:00.000')
insert into #temp values ('8','101','101||17','17','55','976','2013-01-07','1900-01-01 08:43:00.000')
insert into #temp values ('9','101','101||20','20','65','1876','2013-01-09','1900-01-01 09:34:00.000')
insert into #temp values ('2','102','102||3','3','37','1451','2012-08-18 00:00:00.000','1900-01-01 14:51:00.000')
insert into #temp values ('4','102','102||8','8','37','393','2012-08-30 00:00:00.000','1900-01-01 13:05:00.000')
insert into #temp values ('5','102','102||9','9','37','1451','2012-10-09 00:00:00.000','1900-01-01 22:36:00.000')
insert into #temp values ('6','102','102||12','12','42','276','2013-01-16 00:00:00.000','1900-01-01 00:18:00.000')
insert into #temp values ('8','102','102||17','17','42','276','2013-01-21 00:00:00.000','1900-01-01 01:03:00.000')
insert into #temp values ('8','102','102||17','17','42','1147','2013-01-26 00:00:00.000','1900-01-01 16:36:00.000')
insert into #temp values ('9','102','102||20','20','65','1876','2013-02-07 00:00:00.000','1900-01-01 11:00:00.000')

 ;with mycte as
 (
 select *,  row_number() over( order by  dt_Frm DESC) - row_number() over(partition by w_no order by dt_Frm DESC)  deltaRn from #temp
 
 )

 ,mycte1 as
 (
 Select *,  row_number() over(partition by id,w_no, deltaRn  order by id, dt_Frm DESC, r_no) rn from mycte)

 Select * from mycte1 WHERE rn=1
 order by id, rownum

/*********************************************************/

create table #result_temp
(rownum int,
id int,
r_id varchar(10),
ord int,
w_no int,
r_no varchar(10),
dt_Frm datetime,
tr_time datetime
)
insert into #result_temp values ('2','101','101||3','3','37','925','2012-09-27','1900-01-01 14:30:00.000')
insert into #result_temp values ('5','101','101||9','9','55','976','2012-10-18','1900-01-01 14:44:00.000')
insert into #result_temp values ('6','101','101||12','12','42','1425','2013-01-01','1900-01-01 12:12:00.000')
insert into #result_temp values ('8','101','101||17','17','55','976','2013-01-07','1900-01-01 08:43:00.000')
insert into #result_temp values ('9','101','101||20','20','65','1876','2013-01-09','1900-01-01 09:34:00.000')
insert into #result_temp values ('5','102','102||9','9','37','1451','2012-10-09 00:00:00.000','1900-01-01 22:36:00.000')
insert into #result_temp values ('8','102','102||17','17','42','1147','2013-01-26 00:00:00.000','1900-01-01 16:36:00.000')
insert into #result_temp values ('9','102','102||20','20','65','1876','2013-02-07 00:00:00.000','1900-01-01 11:00:00.000')


select * from #result_temp

drop table #temp
drop table #result_temp




An Approximate Pagination with Non-Break Group With T-SQL

I have been asked about this question with Oracle. I came across this discussion at this thread:
http://stackoverflow.com/questions/3438228/oracle-how-to-group-rows-for-pagination

I have modified the solution to work with SQL Server as below:

create table test (empno int, ename varchar(20), trandate date, amt int);
insert into test values (100, 'Alison'   ,  '21-MAR-1996' ,   45000);
insert into test values (100, 'Alison'   ,  '12-DEC-1978' ,   23000);
insert into test values (100, 'Alison'   ,  '24-OCT-1982' ,   11000);
insert into test values (101, 'Linda'    ,  '15-JAN-1984' ,   16000);
insert into test values (101, 'Linda'    , '30-JUL-1987' ,   17000);
insert into test values (101, 'Linda'    ,  '30-JUL-1987' ,   17000);
insert into test values (102, 'Celia'    ,  '31-DEC-1990' ,   78000);
insert into test values (102, 'Celia'    ,  '17-SEP-1996' ,   21000);
insert into test values (103, 'James'    ,  '21-MAR-1996' ,   45000);
insert into test values (103, 'James'    , '12-DEC-1978' ,   23000);
insert into test values (103, 'James'    , '12-DEC-1978' ,   23000);
insert into test values (100, 'Alison'   ,  '21-MAR-1996' ,   45000);
insert into test values (100, 'Alison'   ,  '12-DEC-1978' ,   23000);
insert into test values (100, 'Alison'   ,  '24-OCT-1982' ,   11000);
insert into test values (101, 'Linda'    ,  '15-JAN-1984' ,   16000);
insert into test values (101, 'Linda'    , '30-JUL-1987' ,   17000);
insert into test values (101, 'Linda'    ,  '30-JUL-1987' ,   17000);
insert into test values (102, 'Celia'    ,  '31-DEC-1990' ,   78000);
insert into test values (102, 'Celia'    ,  '17-SEP-1996' ,   21000);
insert into test values (103, 'James'    ,  '21-MAR-1996' ,   45000);
insert into test values (103, 'James'    , '12-DEC-1978' ,   23000);
insert into test values (103, 'James'    , '12-DEC-1978' ,   23000);
insert into test values (103, 'James'    , '12-DEC-1978' ,   23000);


select empno, ename,
       ceiling((rank() over (order by empno) +
        count(*) over (partition by empno))/8.) as chunk
,rank() over (order by empno) myRank
, count(*) over (partition by empno)myCnt
from test
order by empno;
/*
empno	ename	chunk	myRank	myCnt
100	Alison	1	1	6
100	Alison	1	1	6
100	Alison	1	1	6
100	Alison	1	1	6
100	Alison	1	1	6
100	Alison	1	1	6
101	Linda	2	7	6
101	Linda	2	7	6
101	Linda	2	7	6
101	Linda	2	7	6
101	Linda	2	7	6
101	Linda	2	7	6
102	Celia	3	13	4
102	Celia	3	13	4
102	Celia	3	13	4
102	Celia	3	13	4
103	James	3	17	7
103	James	3	17	7
103	James	3	17	7
103	James	3	17	7
103	James	3	17	7
103	James	3	17	7
103	James	3	17	7

*/

Combine Rows To Column—T-SQL Sample

CREATE TABLE ContractEvents (ProjectId      INT,
                             ContractNumber INT)

INSERT INTO ContractEvents
VALUES      (1,1), (1,2), (1,3), (2,1), (2,2)

DECLARE @Events TABLE ( ProjectId       INT,
               ContractNumbers VARCHAR(200)) 


INSERT INTO @Events  

Select Projectid,  ContractNumbers = STUFF(( Select ', ' + convert(varchar(100),c.ContractNumber) as [text()]
From ContractEvents c
Where c.ProjectId = b.ProjectId  
Order by ProjectId
For XML Path ('')), 1, 1, '')

From ContractEvents b
Group by ProjectId

--Select * from @Events
 

drop table ContractEvents