SQL Server Script to Backup a Database to File

I frequently have to backup a database from the production machine and restore it in my testing environment. You can do this with SQL Server Enterprise Manager (or other similar tools) but it can get to be a pain if you have to it over and over again. So here is a script to do the first part - backup a database from a sql server to a file. For the second part, please see my related post called SQL Server Script to Restore a Database from File. To use this script you need to:

1. Set the database name in the @databaseName variable to your database
2. Set the absolute path for the backup directory in the @backupDirectory variable to the directory where the backup will be created
3. Run the script (with something like Query Analyzer)

The script will create a backup of the specified database and put in the directory specified in the @backupDirectory variable. The actual backup name will be based on the database name, today's date and the '.bak' extension (example: myDatabase-10.17.2007.bak).

Download the script from http://tech-cats.net/blog/downloads/sql/SQLServerBackupDatabaseToFile.txt

-----------------------------------------------------------------
-- 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)