SQL Backup Script

It's been a while since I did something besides C#/ASP so for a change of pace, I'm going to include a SQL script, which you can backup individual databases. You can add this script to a batch file, and then put it in scheduled tasks if you need it to be run periodically.

Here's the batch:
-sqlcmd -s <servername> -i c:\file.sql

Here's the SQL script:

-----------------------------------------------------------------
-- Backup database to file (c:\file.sql)
-----------------------------------------------------------------
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 = '<db_name>'
-- Set the path fo the backup directory on the sql server pc
set @backupDirectory = 'c:\backup\' -- 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)