November 28, 2016

SQL Server 2012 Backup Script

SQL Server 2012 Backup Script
SQL Server via
Sometimes things that seem complicated are much easier then you think and this is the power of using T-SQL to take care of repetitive tasks.  One of these tasks may be the need to backup all databases on your server.   This is not a big deal if you have a handful of databases, but I have seen several servers where there are 100+ databases on the same instance of SQL Server.  You could use SQL Server Management Studio to backup the databases or even use Maintenance Plans, but using T-SQL is a much simpler and faster approach.

With the use of T-SQL you can generate your backup commands and with the use of cursors you can cursor through all of your databases to back them up one by one.  This is a very straight forward process and you only need a handful of commands to do this. 

We are using SQL Server Management Studio to manage backup Schedule :

1. Create New Jobs
Open SQL Server management studio an login to SQL Server. In SQL Server Agent please expand it. You will find Jobs Folder, by default its already have 3 schedule backup but isn't active.

2. New Job

In new job properti, fill in the name of the backup schedule and using user account have permission to backup all databases and must be have permission to insert itu backup folder '\\shareFolder\BackupDatabaseBackupPath\'. In this case, i'm using domain admin.

3. Create a Job Step

In this section, we will insert a query to backup database.
script :
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 = '\\shareFolder\BackupDatabaseBackupPath\'  

-- specify filename format

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   

       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

       FETCH NEXT FROM db_cursor INTO @name   

CLOSE db_cursor   
DEALLOCATE db_cursor

4. Backup Time Schedule

we can set what time this schedule backup will be running. By default we can use daily, weekly, monthly backup.

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.

Okay, this is a simple way to backup sql server databases.

Previous Post
Next Post

post written by: