Backup All Databases

-----------------------------------using cursor----------------------------------
declare @name varchar(50)

declare @filename varchar(50)

declare @path varchar(50)

declare @filedate varchar(50)



set @path='E:\'

select @filedate =convert(varchar,getdate(),112)

declare db_cursor cursor for

select name from sys.databases where name not in ('master','model','tempdb','msdb')

open db_cursor



fetch next from db_cursor into @name

while(@@fetch_status=0)

begin

set @filename=@path+@name+'_'+@filedate+'.bak'

backup database @name to disk =@filename

print @filename

fetch next from db_cursor  into @name

end

close db_cursor

deallocate db_cursor

-------------------------------using temp tables----------------------------------------------------
declare @name varchar(50)
declare @path varchar(50)
declare @filename varchar(50)
declare @filedate varchar(50)
declare @i int
declare @cnt int
declare @sql nvarchar(4000)
set @path ='E:\'
select @filedate = convert (varchar (20),getdate(),112)

create table #tab(id int identity ,name varchar(50))
insert into #tab select name from sys.databases where name not in ('master','model','msdb','tempdb')
set @i=1
select @cnt=count(1) from #tab

while (@i<=@cnt)
begin
select @name =name from #tab where id =@i
set @filename =@path+@name+'_'+@filedate+'.bak'
set @sql ='backup database '+ @name +' to disk ='+''''+ @filename +''''
exec sp_executesql @sql
set @i=@i+1
end

drop table #tab

Delete Duplicate Records in a table

create table EMP1(id int,name varchar(100))
INSERT INTO EMP1(id,name) SELECT 1,'odessa'
UNION ALL SELECT 2,'moscow'
UNION ALL SELECT 3,'rostov'
UNION ALL SELECT 4,'omsk'
UNION ALL SELECT 5,'los-angeles'
UNION ALL SELECT 6,'New York'
UNION ALL SELECT 7,'Miami'
UNION ALL SELECT 8,'omsk'
UNION ALL SELECT 9,'los-angeles'
UNION ALL SELECT 10,'New York'
UNION ALL SELECT 11,'Miami'
-----------------------------------------------------------------------------
(1)with cte as(select ROW_NUMBER() over(partition by name order by name)rid,id from EMP1)
delete from cte where rid <> 1
or
WITH empCTE(id,name,Ranking) AS (SELECT id,name,Ranking = row_number() OVER(PARTITION BY name ORDER BY name) FROM EMP1)
DELETE FROM empCTE WHERE Ranking <> 1

(2)DELETE FROM EMP1 WHERE ID < (SELECT MAX(ID)FROM EMP1 AS e WHERE e.name = EMP1.name)

(3)delete e1 from EMP1 e1, EMP1 e2 where e1.name = e2.name and e1.id > e2.id

----------------------------------------------------------------------------------------------------------------------
(4)
set nocount on
create table #tbFruit (Fruit varchar(20))
insert into #tbFruit (Fruit) values ('apple')
insert into #tbFruit (Fruit) values ('apple')
insert into #tbFruit (Fruit) values ('apple')
insert into #tbFruit (Fruit) values ('apple')
insert into #tbFruit (Fruit) values ('orange')
insert into #tbFruit (Fruit) values ('orange')
insert into #tbFruit (Fruit) values ('orange')

create table #tbFruitsWithDupes (DupeID int identity, Fruit varchar(20), DupeCount int)

insert into #tbFruitsWithDupes (Fruit, DupeCount)
    select Fruit, count(*) DupeCount from #tbFruit
    group by Fruit having count(*) > 1

declare @x int, @max int, @Fruit varchar(20), @DupeCount int

select @x = 1, @max    = max(DupeID) from #tbFruitsWithDupes

while (@x <= @max)
    begin
        select @Fruit = Fruit, @DupeCount = DupeCount - 1
            from #tbFruitsWithDupes Where DupeID = @x

        set rowcount @DupeCount

        delete from #tbFruit where Fruit = @Fruit

        select @x = @x + 1
    end

select * from #tbFruit

drop table #tbFruit
drop table #tbFruitsWithDupes