-----------------------------------------------------------------
-- Backup database to file
-----------------------------------------------------------------
declare @backupFileName varchar(100), @backupDirectory varchar(100),
	@databaseDataFilename varchar(100), @databaseLogFilename varchar(100),
	@databaseDataFile varchar(100), @databaseLogFile varchar(100),
	@databaseName varchar(100), @execSql varchar(1000)

-- Set the name of the database to backup
set @databaseName = 'myDatabase'
-- Set the path fo the backup directory on the sql server pc
set @backupDirectory = 'aboslute_path_to_backup_directory' -- such as 'c:\temp\'

-- Create the backup file name based on the backup directory, the database name and today's date
set @backupFileName = @backupDirectory + @databaseName + '-' + replace(convert(varchar, getdate(), 110), '-', '.') + '.bak'

-- Get the data file and its path
select	@databaseDataFile = rtrim([Name]),
	@databaseDataFilename = rtrim([Filename])
from	master.dbo.sysaltfiles as files
	inner join
	master.dbo.sysfilegroups as groups
	on
	files.groupID = groups.groupID
where	DBID = (
		select	dbid
		from	master.dbo.sysdatabases
		where	[Name] = @databaseName
	)

-- Get the log file and its path
select	@databaseLogFile = rtrim([Name]),
	@databaseLogFilename = rtrim([Filename])
from	master.dbo.sysaltfiles as files
where	DBID = (
		select	dbid
		from	master.dbo.sysdatabases
		where	[Name] = @databaseName
	)
	and
	groupID = 0

print 'Backing up "' + @databaseName + '" database to "' + @backupFileName + '" with '
print '  data file "' + @databaseDataFile + '" located at "' + @databaseDataFilename + '"'
print '  log file "' + @databaseLogFile + '" located at "' + @databaseLogFilename + '"'

set @execSql = '
backup database [' + @databaseName + ']
to disk = ''' + @backupFileName + '''
with
  noformat,
  noinit,
  name = ''' + @databaseName + ' backup'',
  norewind,
  nounload,
  skip'

exec(@execSql)