Sql-server – Powershell to dump database schema to text file

You need to try/catch the exception.

$OutputFile="C:\output.txt"
$HostName="MyServer"

try
{ 
    $con="server=$HostName;database=master;Integrated security=sspi"
    $da=New-Object System.Data.SqlClient.SqlDataAdapter ("

            declare @ServerRestartDate datetime
            set @ServerRestartDate = (select crdate from master..sysdatabases where dbid=2)
            if(@ServerRestartDate>GETDATE()-1)
                print @@servername +' was restarted in last 24 hours. Restart date/time = '+cast(@ServerRestartDate as varchar(20))

                                        ",$con)
    $dt=New-Object System.Data.DataTable
    $da.fill($dt)|Out-Null
    $svr
    $dt|Format-table -AutoSize |Out-File $OutputFile -Append
}
catch [Exception]
{
    // Do something with the exception 
    Write-Host $_.Exception.ToString()
}

As Aaron mentioned, there is no click - click - finish and export to xml option in sql server.

Rather you can use FOR XML AUTO, XMLDATA to do that using T-SQL.

use databaseName
go
SELECT SCHEMA_NAME(t.schema_id) AS schema_name,
        t.name AS table_name,
        c.name AS column_name,
        st.name as data_type,
        c.max_length 
        ,c.Is_ANSI_Padded
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
inner join sys.types st on c.system_type_id = st.system_type_id
where t.is_ms_shipped = 0 and t.name <> 'sysdiagrams'
order by t.name
FOR XML AUTO, XMLDATA

Refer to : Specifying the XMLDATA Schema Option in a Query

Generating an XML-Data schema is an overhead on the server, is likely to affect performance, and should be used only when data types are needed.

Alternatively, if you just want to script out the schema, then refer to this answer for more details.