Taking backup of all the databases in SQL Server

Question

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 pls help on this?

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 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

Step 2 Name your Plan

Step 3 Select Database Backup Task

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

Step 5 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)