SQL backup to UNC path with custom file name (TSQL)

I didn’t write this from scratch. I pieced it together from a few MSDN articles and posts.

Just fill in the details and execute or add to a management task to get the job done.

Make sure that the SQL Server agent has access to the backup path that you specify.

[box]

USE database name;
GO

DECLARE @BackupFileName varchar(200)

SELECT @BackupFileName = (SELECT ‘backup pathfilename_’ + REPLACE(convert(nvarchar(20),GetDate(),120),’:’,’-‘) + ‘.bak’)

BACKUP DATABASE database name
TO DISK = @BackupFileName
WITH FORMAT,
MEDIANAME = ‘media name’,
NAME = ‘Full backup of database name’;

[/box]

If you have any questions, let me know.