Have you ever wondered if you can back up all your databases at once? In this post I will discuss how to do this with one script. A separate file will be created for each database.

Let’s start!

1) Please make a directory where you want the backup files to be created. I will use C:\DB_Backup\ as my directory in this post.

Note: Step one is an important thing to do. We will use the path of that directory later in our script.

2) Open SQL Server Management Studio and select the server name.

3) Click the New Query button and enter in the following 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

SET @path = ‘C:\DB_Backup\’

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

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

4) Make sure to set your directory path created in step 1 in the above script at the fifth line where you can see SET @path = ‘C:\DB_Backup\’  . You will need to replace C:\DB_Backup\ with the path of your directory.

5) Click the Execute! button and the script will execute

6) Once finished, you can see all your databases are backed up in the directory you have created (C:\DB_Backup\ in our example) with the database name as the file name

See! Sometimes things that seem complicated are much easier than you think. This is what we do here at Arvixe. Our target is to always make things easier for our clients.

Looking for quality web hosting? Look no further than Arvixe Web Hosting!