Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a Microsoft SQL Server 2005 DB server. In the DB server, I have around 250 user databases. I have to take a back up of all these databases. Since, manually taking backup consumes lot of time, I am looking for a Batch script or DB script which will automatically take the backup of all the 250 databases. Can anyone please help on this?

share|improve this question

migrated from stackoverflow.com Jul 26 '12 at 7:40

This question came from our site for professional and enthusiast programmers.

    
Do you have to do this once, or on a regular basis? – Jon Seigel Jul 26 '12 at 13:23
    
Are all the backups using the same recovery model? Automating backups are nice but if the backups do not meet your recovery needs for your user's data, the backup means nothing. – Shawn Melton Jul 30 '12 at 7:25

Develop a Maintenance Plan

Sql server has this amazing feature where it will create the script and job for you

Step 1 Right click on Maintenance Plan under Management Right Click on Management

Step 2 Name your Plan Name the Plan

Step 3 Select Database Backup Task Database Backup Task

Step 4 Configure The Task, select Databases , Folder location, Type of Backup (Full, differential,Transaction log) connection etc.

Configure The Backup Task

Step 5 Configure the Job Schedule Configure the Job Schedule


i recommend having a separate plan for system database and you user databases to avoid any kind of problem

helping Links

Setting up a Maintenance Plan to Backup Databases

Back Up Database Task (Maintenance Plan)

share|improve this answer
    
Note: Express editions of SQL Server don't offer Maintenance Plan functionality. – Alan B Sep 17 '15 at 10:32
    
I don't recommend Maintenance plans, but Ola Hallengren's scripts (see Sina Hassanpour's answer) are great! – Peter Elzinga Oct 20 '15 at 6:49

Note : First Create a folder on D: Drive. D:\User_DataBackup\ .

Step 1. Create a procedure that are given below.

Create PROCEDURE [dbo].[UserDataBaseBackUp]
AS

BEGIN
SET NOCOUNT ON;

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 = 'D:\User_DataBackup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),104)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')
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
END

Step 2:Execute the above procedure.

 EXEC [UserDataBaseBackUp]

You can also schedule this procedure. This procedure is tested hopefully it will help.

share|improve this answer

I know for a fact that maintaining a lot of databases with SSMS could be a little confusing, but it's straight forward.

I can recommend a more effective way, that is using Ola Hallengren's maintenance script. It is very cool and very effective. And you can do much more than just backing up all databases, you can do all types of maintenance procedures.

For example, you can backup all databases, compress them and encrypt them with a certificate of your choice, by using a command like this (and they are all optional, and encryption and compression will not work on SQL Server 2005 but I think it will show the flexibility and strength of Ola's script):

EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Compress = 'Y',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256',
@ServerCertificate = 'MyCertificate'
share|improve this answer
    
I also recommend Ola Hallengrens scripts as they are very good and flexible. However, the example given will not work on the databases user1493004 asks to backup. Backup compression was introduced in SQL2008 and encryption in SQL2014. user1493004 uses SQL2005 databases. – Peter Elzinga Oct 20 '15 at 6:47
    
that's true, just wanted an example to show the strength of this script. thanks for your comment. :) – Sina Hassanpour Oct 20 '15 at 6:49

Create SQL Job for this purpose; if properly configured sql job will take backups automatically so there is no need for complex scripts i guess.

share|improve this answer
    
Hi Yaqub, this isn't quite what we would call a full answer. Is there any way you can expound on this for us so that it provides a little more context for in the future? Maybe some detail on what you mean by "properly configured SQL job"? – jcolebrand Jul 26 '12 at 18:24

http://beyondrelational.com/modules/2/blogs/88/posts/10152/sql-server-automated-all-databases-backups-script.aspx for sure will cover all your needs. It gets diferential, full and transactional backups done on really easy and understandable scripts.

share|improve this answer
2  
Hi Yaroslav and welcome to Stack Exchange. We generally ask that people provide definition on their link so that (heaven forbid) when the link rots, the context of the link stays. Maybe you could paste in the parts you think are pertinent, keeping your link for attribution? – jcolebrand Jul 26 '12 at 18:23

Working on the answer by JP, I've added a parameter to pass the destination directory (and quite possibly add more options):

Create PROCEDURE [dbo].[UserDataBaseBackUp] (
  @OutputDir varchar(255)
) AS
BEGIN
    SET NOCOUNT ON;

    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 = @OutputDir
    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),104)
    PRINT 'Starting Backups'
    DECLARE db_cursor CURSOR FOR
        SELECT name FROM MASTER.dbo.sysdatabases
            WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')
        OPEN db_cursor
            FETCH NEXT FROM db_cursor INTO @name
            WHILE @@FETCH_STATUS = 0 BEGIN
                SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
                    PRINT 'Starting Backup For ' + @name
                    BACKUP DATABASE @name TO DISK = @fileName WITH FORMAT
                FETCH NEXT FROM db_cursor INTO @name
            END
        CLOSE db_cursor
    DEALLOCATE db_cursor
    PRINT 'Backups Finished'
END
GO

So then:

EXEC UserDataBaseBackUp @OutputDir = 'F:\Backups\SQL Databases\'
share|improve this answer

Another method http://beyondrelational.com/modules/2/blogs/70/posts/10895/backup-all-databases.aspx Create that procedure and schedule it as a job

share|improve this answer

You could also use powershell: http://blogs.msdn.com/b/buckwoody/archive/2009/06/25/use-powershell-to-backup-all-user-databases.aspx

share|improve this answer
    
Hi Emito and welcome to Stack Exchange. We generally ask that people provide definition on their link so that (heaven forbid) when the link rots, the context of the link stays. Maybe you could paste in the parts you think are pertinent, keeping your link for attribution? – jcolebrand Jul 26 '12 at 18:22
    
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'BWOODY1\SQL2K8' $bkdir = $s.Settings.BackupDirectory $dbs = $s.Databases – Emito Jul 27 '12 at 20:58
    
and then a For each DB – Emito Jul 27 '12 at 20:59
    
$dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup') $dbbk.Action = 'Database' $dbbk.BackupSetDescription = "Full backup of " + $dbname $dbbk.BackupSetName = $dbname + " Backup" $dbbk.Database = $dbname $dbbk.MediaDescription = "Disk" $dbbk.Devices.AddDevice($bkdir + "\" + $dbname + "db" + $dt + ".bak", 'File') $dbbk.SqlBackup($s) – Emito Jul 27 '12 at 20:59
    
you can edit your post by clicking on the Edit button below your post and pasting your text in new. Go ahead and give it a try. – jcolebrand Jul 27 '12 at 22:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.