SQL Server Backup Script to backup SQL Server databases. It loops through all the databases dynamically using a select statement to the master database where all the names of the databases are located on the instance. It backs up each database, except those specified in the WHERE clause which you can add your unwanted databases to be backed up.

Here is the script that will allow you to backup each database within your instance of SQL Server.  You will need to change the @path to the appropriate backup directory.

File Naming Format DBname_YYYYDDMM.BAK

DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name

-- specify database backup directory
SET @path = 'C:\Backup\' 
 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR 
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
       BACKUP DATABASE @name TO DISK = @fileName 
 
       FETCH NEXT FROM db_cursor INTO @name  
END  
 
CLOSE db_cursor  
DEALLOCATE db_cursor

File Naming Format DBname_YYYYDDMM_HHMMSS.BAK

If you want to also include the time in the filename you can replace this line in the above script:
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)


with this line:
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')


In this script we are bypassing the system databases, but these could easily be included as well.  You could also change this into a stored procedure and pass in a database name or if left NULL it backups all databases.  Any way you choose to use it, this script gives you the starting point to simply backup all of your databases.

Also, if you wanted to bypass some of your user databases you can include them in the NOT IN section as well.
and the next steps :

  1. Add this script to your toolbox
  2. Modify this script and make it a stored procedure to include one or many parameters
  3. Enhance the script to use additional BACKUP options


ASPHostPortal.com Special SQL Server 2014 Hosting Complete Features

What we think makes ASPHostPortal.com so compelling is how deeply integrated all the pieces are. We integrate and centralize everything--from the systems to the control panel software to the process of buying a domain name. For us, that means we can innovate literally everywhere. We've put the guys who develop the software and the admins who watch over the server right next to the 24-hour Fanatical Support team, so we all learn from each other.

  • Full Remote Access - We allow you full remote connectivity to your SQL Server 2014 Hosting database and do not restrict access in any way.
  • Easily transfer your existing SQL Server database - With our SQL Server hosting package, there's no need to rebuild your database from scratch should you wish to transfer an existing SQL Server database to us. If you already have a database hosted elsewhere, you can easily transfer the contents of your database using SQL Server Management Studio which is fully supported by our packages. SSMS provides you with an Import/Export wizard which you can use to upload your data and stored procedures with a couple of clicks.

Use the Promo Code "DBSQL" (without quotes) and receive double SQL Server Space ! This offer valids only from 01st June 2014 to 30th June 2014 and it applies to all the new clients registered during these dates only.