Agent Jobs

Create Script With No 8192 Text Limit

Using the ‘for xml path(”), type’ will allow you to create text output without running into the 8192 Text limit. Simply click on the XML results link, and you’ll get a second tab with your text results. To parse; simply copy and paste it into another SSMS window.

use master;
set nocount on
declare @backup_db	varchar(max)
set		@backup_db	= ''
select	@backup_db	= @backup_db + 
'backup database [' + name + '] to disk = ''\\MyBackupShare\MyBackupFolder\' + name + '.bak'' with format;' + char(10)
from sys.databases
where name not in ('tempdb')
order by name asc
select	(@backup_db) for xml path(''), type
-- exec	(@backup_db)

Here’s a quick screenshot.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s