Could someone help me identify my syntax error, or identify whether this is possible at all? Is it possible to do something like this in SQL with a real table? I know this is possible with a temp table, but cannot get it to work properly with a normal table. 

CREATE TABLE Backups
                (
                 DBName SYSNAME,
                 LastFullBackup DATETIME NULL,
                 LastDiffBackup DATETIME NULL,
                 [State] CHAR(1) NULL
                );

        WITH    LastFullBackup
                  AS (SELECT    bs.database_name,
                                MAX(bs.backup_finish_date) AS BackupDate
                      FROM      msdb..backupset bs
                      WHERE     bs.type='D'
                      GROUP BY  bs.database_name),
                LastDiffBackup
                  AS (SELECT    bs.database_name,
                                MAX(bs.backup_finish_date) AS BackupDate
                      FROM      msdb..backupset bs
                      WHERE     bs.type='I'
                      GROUP BY  bs.database_name)


I see a syntax error on the last parenthesis, but that doesn't give me much insight into where the real problem is.
There is nothing wrong with your syntax but:


  A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE
  statement that references some or all the CTE columns. A CTE can also
  be specified in a CREATE VIEW statement as part of the defining SELECT
  statement of the view.


Reference: https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql

As a best practice I will start with a semicolon (;).

As an example following should work.

;WITH lastfullbackup 
     AS (SELECT bs.database_name, 
                Max(bs.backup_finish_date) AS BackupDate 
         FROM   msdb..backupset bs 
         WHERE  bs.type = 'D' 
         GROUP  BY bs.database_name), 
     lastdiffbackup 
     AS (SELECT bs.database_name, 
                Max(bs.backup_finish_date) AS BackupDate 
         FROM   msdb..backupset bs 
         WHERE  bs.type = 'I' 
         GROUP  BY bs.database_name) 
SELECT * 
FROM   lastfullbackup 
UNION ALL 
SELECT * 
FROM   lastdiffbackup