USE [DBA]
GO

/****** Object:  StoredProcedure [dbo].[SP_REVERT_ALL_TO_SNAPSHOTS]    Script Date: 10/28/2015 11:21:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE Proc [dbo].[SP_REVERT_ALL_TO_SNAPSHOTS]
as 
begin
declare @sql table(stmt varchar(4000))
declare @stmt nvarchar(4000)

insert @sql (stmt)
select 'alter database '+a.name+' set single_user with rollback immediate' 
from sys.databases a
join sys.databases b on a.database_id=b.source_database_id
 where b.source_database_id is not null
 and a.name not in ('DBA')


insert @sql (stmt)
select 'restore database '+a.name+' from DATABASE_SNAPSHOT='''+b.name+'''' 
from sys.databases a
join sys.databases b on a.database_id=b.source_database_id
 where b.source_database_id is not null
  and a.name not in ('DBA')

declare sqlcursor cursor for
select stmt from @sql

open sqlcursor
fetch next from sqlcursor into @stmt

while @@FETCH_STATUS=0
begin

--exec sp_executesql @stmt
select @stmt
exec sp_executesql @stmt
fetch next from sqlcursor into @stmt
end
--select 'drop database  '+name from sys.databases where source_database_id is not null
end




GO