This query will backup the current database
declare @sql varchar(1000) select @sql = 'BACKUP DATABASE '+(select db_name())+' TO DISK = ''E:\'+(select db_name())+ convert(varchar,GETDATE(),112)+'.bak''' Exec(@sql)
If the database name is Test then executing this query will backup Test database and name of that backup file is Test20050817.bak. Current date with the format yyyymmdd will be suffixed to the DBName
If you schedule this query to run daily, then backup will be generated daily as a seperate fileUpdate:
The following post can be used to take backup of all databaseshttp://beyondrelational.com/blogs/madhivanan/archive/2010/05/10/backup-all-databases.aspx
Tags: t-sql, sql_server, backup,