USE [master]
GO
ALTER DATABASE [Patient_Files] ADD FILEGROUP [Patient_Files_2017]
GO
ALTER DATABASE [Patient_Files] ADD FILEGROUP [Patient_Files_2018]
GO
ALTER DATABASE [Patient_Files] ADD FILEGROUP [Patient_Files_2019]
GO
ALTER DATABASE [Patient_Files] ADD FILEGROUP [Patient_Files_2020]
GO


ALTER DATABASE [Patient_Files] ADD FILE ( NAME = N'Patient_Files_2017', FILENAME = N'p:\DB\PatFiles\Patient_Files_2017.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Patient_Files_2017]
GO
ALTER DATABASE [Patient_Files] ADD FILE ( NAME = N'Patient_Files_2018', FILENAME = N'p:\DB\PatFiles\Patient_Files_2018.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Patient_Files_2018]
GO
ALTER DATABASE [Patient_Files] ADD FILE ( NAME = N'Patient_Files_2019', FILENAME = N'p:\DB\PatFiles\Patient_Files_2019.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Patient_Files_2019]
GO
ALTER DATABASE [Patient_Files] ADD FILE ( NAME = N'Patient_Files_2020', FILENAME = N'p:\DB\PatFiles\Patient_Files_2020.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Patient_Files_2020]
GO
       

USE [Patient_Files]
GO

BEGIN TRANSACTION

CREATE PARTITION FUNCTION [pf_Patient_Files_Year](datetime) AS RANGE LEFT FOR VALUES (
N'2017-12-31T23:59:59.997', 
N'2018-12-31T23:59:59.997',  
N'2019-12-31T23:59:59.997',
N'2020-12-31T23:59:59.997')

 --12/31/2017 23:59:59.998
CREATE PARTITION SCHEME [sch_Patient_Files_Year] AS PARTITION [pf_Patient_Files_Year] TO (
[Patient_Files_2017], 
[Patient_Files_2018], 
[Patient_Files_2019],  
[Patient_Files_2020], 
[PRIMARY])


CREATE CLUSTERED INDEX [ClusteredIndex_on_sch_Patient_Files_Year] ON [dbo].tbPatientDocs
(	[ConversionTime]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [sch_Patient_Files_Year]([ConversionTime])


CREATE CLUSTERED INDEX [ClusteredIndex_on_sch_Patient_Files_Year] ON [dbo].[tbDBImage]
(
	[TransDATE]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [sch_Patient_Files_Year]([TransDATE])


USE [Patient_Files]

GO


SELECT * FROM sys.dm_db_partition_stats   
WHERE object_id = OBJECT_ID('Patient_Files.dbo.tbPatientDocs');  
GO  
 

    
SELECT 1,* FROM tbPatientDocs
WHERE $PARTITION.[pf_Patient_Files_Year]([ConversionTime]) = 1 


-- you can shrink fiole now


--BACKUP DATABASE Patient_Files READ_WRITE_FILEGROUPS
--TO DISK = N'p:\DBRECOVERED\Patient_Files_CompletePartition.bak'
--WITH INIT
--GO

  



ALTER DATABASE Patient_Files MODIFY FILEGROUP [Patient_Files_2017] READONLY
GO

ALTER DATABASE Patient_Files MODIFY FILEGROUP [Patient_Files_2018] READONLY
GO


BACKUP DATABASE Patient_Files FILEGROUP = 'Patient_Files_2017' TO DISK = N'p:\DBRECOVERED\Patient_Files_CompletePartitionRO2017.bak' WITH INIT  
GO 
BACKUP DATABASE Patient_Files FILEGROUP = 'Patient_Files_2018' TO DISK = N'p:\DBRECOVERED\Patient_Files_CompletePartitionRO2018.bak' WITH INIT  
GO 
BACKUP DATABASE Patient_Files READ_WRITE_FILEGROUPS TO DISK = N'p:\DBRECOVERED\Patient_Files_CompletePartitionRW.bak' WITH INIT  
GO 
 
 
USE [master] 
GO 


-- SEE WHAT IS IN THE FILES
RESTORE FILELISTONLY
FROM DISK = N'p:\DBRECOVERED\Patient_Files_CompletePartitionRW.bak'
GO

--restore

RESTORE DATABASE [Patient_Files_Recovered] READ_WRITE_FILEGROUPS
FROM DISK = N'p:\DBRECOVERED\Patient_Files_CompletePartitionRW.bak'
WITH RECOVERY, PARTIAL,
MOVE 'Patient_Files' TO 'p:\DBRECOVERED\Patient_Files.mdf',
MOVE 'Patient_Files_2017' TO 'p:\DBRECOVERED\Patient_Files_2017.ndf',
MOVE 'Patient_Files_2018' TO 'p:\DBRECOVERED\Patient_Files_2018.ndf',
MOVE 'Patient_Files_2019' TO 'p:\DBRECOVERED\Patient_Files_2019.ndf',
MOVE 'Patient_Files_2020' TO 'p:\DBRECOVERED\Patient_Files_2020.ndf',
MOVE 'Patient_Files_log' TO 'p:\PartBak\Patient_Files_log.ldf'
GO

RESTORE DATABASE [Patient_Files_Recovered]
FILEGROUP = 'Patient_Files_2017'
FROM DISK = N'p:\DBRECOVERED\Patient_Files_CompletePartitionRO2017.bak'
WITH RECOVERY
GO 
RESTORE DATABASE [Patient_Files_Recovered]
FILEGROUP = 'Patient_Files_2018'
FROM DISK = N'p:\DBRECOVERED\Patient_Files_CompletePartitionRO2018.bak'
WITH RECOVERY
GO