/****************************************************
AboutSQLServer.com blog
Written by Dmitri Korotkevitch

How To Move Data and Log Files To Different Drives 
Keeping the Database Online in Non-Enterprise Editions of SQL Server

2016-03-08
*****************************************************/
-- Creating the database and populate it with the data
-- Change file location if needed
use master
go

create database DataMovementDemo
on primary
( name = N'DataMovementDemo', filename = N'C:\OldDrive\DataMovementDemo.mdf', size = 100MB, filegrowth = 50MB),
 filegroup [Secondary]
( name = N'DataMovementDemo_Secondary1', filename = N'C:\OldDrive\DataMovementDemo_Secondary1.ndf', size = 100MB, filegrowth = 50MB),
( name = N'DataMovementDemo_Secondary2', filename = N'C:\OldDrive\DataMovementDemo_Secondary2.ndf', size = 100MB, filegrowth = 50MB)
log on
( name = N'DataMovementDemo_log', filename = N'C:\OldDrive\DataMovementDemo_log.ldf', size = 500MB, filegrowth = 500MB)
go

alter database DataMovementDemo set recovery full
go

-- Populating the database with the data
use DataMovementDemo
go

create table dbo.DataOnPrimary
(
    ID int not null,
    Placeholder char(8000),
    constraint PK_DataOnPrimary
    primary key clustered(ID)
    on [Primary]
);

create table dbo.DataOnSecondary
(
    ID int not null,
    Placeholder char(8000),
    constraint PK_DataOnSecondary
    primary key clustered(ID)
    on [Secondary]
);

;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows
,N5(C) as (select 0 from N4 as T1 cross join N4 as T2 ) -- 65,536 rows
,Nums(Num) as (select row_number() over (order by (select null)) from N5)
    insert into dbo.DataOnPrimary(ID)
        select Num from Nums;

insert into dbo.DataOnSecondary(ID)
    select ID from dbo.DataOnPrimary;
go

-- Checking amount of free space in the file
select 
    f.name as [FileName]
    ,fg.name as [FileGroup]
    ,f.physical_name as [Path]
    ,f.size / 128.0 as [CurrentSizeMB]
    ,convert(int,fileproperty(f.name,'SpaceUsed')) / 
        128.0 as [UsedSpaceMB]
    ,f.size / 128.0 - convert(int,fileproperty(f.name,'SpaceUsed')) / 
        128.0 as [FreeSpaceMb]
from 
    sys.database_files f left join sys.filegroups fg on 
        f.data_space_id = fg.data_space_id;
go

--Creating New Files in the Secondary FG on the new drive
alter database DataMovementDemo add file 
( name = N'DataMovementDemo_NewSecondary1', filename = N'C:\NewDrive\DataMovementDemo_NewSecondary1.ndf', size = 250MB, filegrowth = 50MB )
to filegroup [Secondary];

alter database DataMovementDemo add file 
( name = N'DataMovementDemo_NewSecondary2', filename = N'C:\NewDrive\DataMovementDemo_NewSecondary2.ndf', size = 250MB, filegrowth = 50MB)
to filegroup [Secondary];
go

-- Checking amount of free space in the file
select 
    f.name as [FileName]
    ,fg.name as [FileGroup]
    ,f.physical_name as [Path]
    ,f.size / 128.0 as [CurrentSizeMB]
    ,convert(int,fileproperty(f.name,'SpaceUsed')) / 
        128.0 as [UsedSpaceMB]
    ,f.size / 128.0 - convert(int,fileproperty(f.name,'SpaceUsed')) / 
        128.0 as [FreeSpaceMb]
from 
    sys.database_files f left join sys.filegroups fg on 
        f.data_space_id = fg.data_space_id;
go

-- Preventing DataMovementDemo_Secondary2 file to grow
declare
	@MaxFileSizeMB int
	,@SQL nvarchar(max)
	
-- Obtaining current file size 	
select @MaxFileSizeMB = size / 128 + 1
from sys.database_files
where name = 'DataMovementDemo_Secondary2';

set @SQL = N'alter database DataMovementDemo 
modify file(name=N''DataMovementDemo_Secondary2'',maxsize=' + 
	convert(nvarchar(32),@MaxFileSizeMB) + N'MB);';

exec sp_executesql @SQL;
go

-- Removing the first data file
dbcc shrinkfile(DataMovementDemo_Secondary1, emptyfile);
alter database DataMovementDemo remove file DataMovementDemo_Secondary1
go

-- Checking amount of free space in the file
select 
    f.name as [FileName]
    ,fg.name as [FileGroup]
    ,f.physical_name as [Path]
    ,f.size / 128.0 as [CurrentSizeMB]
    ,convert(int,fileproperty(f.name,'SpaceUsed')) / 
        128.0 as [UsedSpaceMB]
    ,f.size / 128.0 - convert(int,fileproperty(f.name,'SpaceUsed')) / 
        128.0 as [FreeSpaceMb]
from 
    sys.database_files f left join sys.filegroups fg on 
        f.data_space_id = fg.data_space_id;
go

-- Removing the second data file
dbcc shrinkfile(DataMovementDemo_Secondary2, emptyfile);
alter database DataMovementDemo remove file DataMovementDemo_Secondary2
go

-- Checking amount of free space in the file
select 
    f.name as [FileName]
    ,fg.name as [FileGroup]
    ,f.physical_name as [Path]
    ,f.size / 128.0 as [CurrentSizeMB]
    ,convert(int,fileproperty(f.name,'SpaceUsed')) / 
        128.0 as [UsedSpaceMB]
    ,f.size / 128.0 - convert(int,fileproperty(f.name,'SpaceUsed')) / 
        128.0 as [FreeSpaceMb]
from 
    sys.database_files f left join sys.filegroups fg on 
        f.data_space_id = fg.data_space_id;
go

-- Moving data from Primary FG
alter database DataMovementDemo add file 
( name = N'DataMovementDemo_Primary2', filename = N'C:\NewDrive\DataMovementDemo_Primary2.ndf', size = 550MB, filegrowth = 50MB )
to filegroup [Primary];
go

-- It will error in the end
dbcc shrinkfile(DataMovementDemo, emptyfile);
go

-- Checking amount of free space in the file
select 
    f.name as [FileName]
    ,fg.name as [FileGroup]
    ,f.physical_name as [Path]
    ,f.size / 128.0 as [CurrentSizeMB]
    ,convert(int,fileproperty(f.name,'SpaceUsed')) / 
        128.0 as [UsedSpaceMB]
    ,f.size / 128.0 - convert(int,fileproperty(f.name,'SpaceUsed')) / 
        128.0 as [FreeSpaceMb]
from 
    sys.database_files f left join sys.filegroups fg on 
        f.data_space_id = fg.data_space_id;
go

dbcc shrinkfile(DataMovementDemo, 10);
go

select 
    f.name as [FileName]
    ,fg.name as [FileGroup]
    ,f.physical_name as [Path]
    ,f.size / 128.0 as [CurrentSizeMB]
    ,convert(int,fileproperty(f.name,'SpaceUsed')) / 
        128.0 as [UsedSpaceMB]
    ,f.size / 128.0 - convert(int,fileproperty(f.name,'SpaceUsed')) / 
        128.0 as [FreeSpaceMb]
from 
    sys.database_files f left join sys.filegroups fg on 
        f.data_space_id = fg.data_space_id;
go

-- Shrinking T-Log
BACKUP DATABASE [DataMovementDemo] TO  DISK = N'c:\OldDrive\DataMovementDemo.bak';
BACKUP LOG [DataMovementDemo] TO  DISK = N'c:\OldDrive\DataMovementDemo.bak';
go

dbcc loginfo
go

dbcc shrinkfile(DataMovementDemo_Log, 65);
go

select 
    f.name as [FileName]
    ,fg.name as [FileGroup]
    ,f.physical_name as [Path]
    ,f.size / 128.0 as [CurrentSizeMB]
    ,convert(int,fileproperty(f.name,'SpaceUsed')) / 
        128.0 as [UsedSpaceMB]
    ,f.size / 128.0 - convert(int,fileproperty(f.name,'SpaceUsed')) / 
        128.0 as [FreeSpaceMb]
		,'',''
from 
    sys.database_files f left join sys.filegroups fg on 
        f.data_space_id = fg.data_space_id;
go

dbcc loginfo
go

-- Switching DB to simple recovery model. Consider pros and cons of this approach
use master
go

alter database DataMovementDemo set recovery simple
go

use DataMovementDemo
go

dbcc loginfo
go

dbcc shrinkfile(DataMovementDemo_Log, 65);
go

-- copying the files
use master
go

alter database DataMovementDemo modify file
( name = N'DataMovementDemo', filename = N'C:\NewDrive\DataMovementDemo.mdf');

alter database DataMovementDemo modify file
( name = N'DataMovementDemo_Log', filename = N'C:\NewDrive\DataMovementDemo_Log.ldf');
go

alter database DataMovementDemo set offline with rollback immediate
go

-- COPY FILES 

alter database DataMovementDemo set online
go

-- rebuilding the log
alter database DataMovementDemo modify file
( name = N'DataMovementDemo_log', size = 4000MB );

alter database DataMovementDemo modify file
( name = N'DataMovementDemo_log', size = 8000MB );
go

alter database DataMovementDemo set recovery full
go

-- Rebuild backup chain!!!

-- Index Fragmentation
;with FGObjects(SchemaName, TableName, IndexName, RowNum, Cnt)
as
(
	select 
		s.Name, t.Name, i.Name
		,ROW_NUMBER() over(order by t.object_id, i.index_id) as RowNum
		,COUNT(*) over() as Cnt
	from
		sys.indexes i join sys.filegroups f on
			i.data_space_id = f.data_space_id
		join sys.all_objects t on
			i.object_id = t.object_id
		join sys.schemas s on
			t.schema_id = s.schema_id
	where
		i.index_id >= 1 and
		t.type = 'U' and -- User Created Tables
		i.data_space_id = f.data_space_id and
		f.name = 'PRIMARY' -- Filegroup
)
select 
	'alter index ' as [text()]
	,[IndexName] as [text()]
	,' on ' + SchemaName + '.' as [text()]
	,[TableName] as [text()]
	,' reorganize;' + char(13) + char(10) as [text()]
	,'raiserror(''' as [text()]
	,RowNum as [text()]
	,'/' as [text()]
	,Cnt as [text()]
	,' is done'',0,1) with nowait;' + char(13) + char(10) as [text()]
	,'go' + char(13) + char(10) as [text()]
from FGObjects
for xml path('');


