Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

6 Answers 6

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
add comment

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
add comment

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
add comment

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
add comment

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
add comment

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
add comment

Your Answer

 
discard

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