How to retrieve OneDrive size and persist in SQL Server

Introduction

I needed to collect usage data to prepare for a OneDrive migration and do some capacity planning. To facilitate this, I collected all OneDrive sites and their sizes and persisted them in a SQL database, that I created for this purpose.

In a first step, I connect to SharePointOnline, using Connect-SPOService. Then I retrieve all OneDrive sites in the tenant, including their size with Get-SPOSite -IncludePersonalSite $true -Limit all -Filter "Url -like '-my.sharepoint.com/personal/'" and extract the necessary information with Select-Object Url,Owner,StorageUsageCurrent and then write it to a staging table using Write-SqlTableData. This cmdlet will take care of the table and schema creation through the -Force parameter, if it does not exist, therefore I’m only using it for the staging tables. I’m also dropping already existing staging tables beforehand.

In a last step, I persist the data from staging, using a MERGE query.

 

Sample Code

Here is the code, I used to run this process. 

First, we create the table in SQL Server, where we persist the data:

CREATE TABLE [dbo].[OneDrive](

       [Url] [nvarchar](max) NOT NULL,

       [Owner] [nvarchar](max) NOT NULL,

       [StorageUsageCurrent] [bigint] NULL

)

 

Then the PowerShell code

Import-Module sqlserver 

Import-Module Microsoft.Online.SharePoint.PowerShell

$SQLserver   = "SQLserver"

$SQLdatabase = "SharePoint"

 

#build credential object

$cred = Get-Credential

 

#drop staging tables, if they exist

$q = "IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[collab].[OneDrive]') AND type in (N'U')) DROP TABLE [collab].[OneDrive]"

Invoke-Sqlcmd -Query $q -ServerInstance $SQLserver -Database $SQLdatabase

 

#Connect to SharePointOnline

Connect-SPOService -Url https://XXXXX-admin.sharepoint.com/ -Credential $cred

 

#Get all OneDrive sites including their current size and write to staging table

Get-SPOSite -IncludePersonalSite $true -Limit all -Filter "Url -like '-my.sharepoint.com/personal/'" | Select-Object Url,Owner,StorageUsageCurrent | Write-SqlTableData -ServerInstance $SQLserver -DatabaseName $SQLdatabase -Force -SchemaName collab -TableName OneDrive

 

#persist data 

$q = @"

MERGE INTO dbo.OneDrive as tgt

USING (

SELECT Url

      ,Owner

      ,StorageUsageCurrent

FROM collab.OneDrive

) as src

ON tgt.Url = src.Url

WHEN matched THEN

UPDATE SET StorageUsageCurrent = src.StorageUsageCurrent

WHEN not matched by target THEN

INSERT(Url,Owner,StorageUsageCurrent)

VALUES(Url,Owner,StorageUsageCurrent);

"@

Invoke-Sqlcmd -Query $q -ServerInstance $SQLserver -Database $SQLdatabase