Solved

stored proc to backup a list of databases

Posted on 2014-01-30
4
220 Views
Last Modified: 2014-02-03
I would like to create a stored procedure that will take a list of databases which I will supply upon execution, that will run a full backup.  I'm having trouble with getting it to loop through my list.
0
Comment
Question by:navajo26354
4 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39822798
check this out

declare @T TABLE
(
dbNames VARCHAR(100)
,backupPath VARCHAR(1000)
)
INSERT INTO @T VALUES('DB1')
INSERT INTO @T VALUES('DB2')

INSERT INTO @T VALUES('DB3')
INSERT INTO @T VALUES('DB4')

DECLARE @DbName VARCHAR(100)
DECLARE @BackupPath VARCHAR(1000)
DECLARE @SQL VARCHAR(1000)
DECLARE T CURSOR
FOR 
SELECT dbNames FROM @T

OPEN T
FETCH T INTO @dbName,@BackupPath

WHILE @@FETCH_STATUS = 0 
BEGIN
SET @BackupPath = @BackupPath + @dbName + '.bak'
SET @SQL = 'del '+@BackupPath
exec xp_cmdshell @SQL
SET @SQL = 'backup database ' + @dbName + ' to disk = ' + '''' + @BackupPath + ''''
exec(@SQL)

FETCH T INTO @dbName,@BackupPath
END
CLOSE T
DEALLOCATE T

Open in new window

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39822810
try the solution mentioned here

http://ola.hallengren.com/
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39822929
Hi,

Second Ola's script that Aneesh mentions above. As in why write your own when Ola's does nearly everything I can think of and then some!

Regards
  David

PS From someone who in the past has written their own scripts and restore jobs for test environments ...
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39825635
I use a table to control rebuilds instead of passing in a list or using Ola's code.

That allows a much more customized approach WITHOUT constantly having to change code, such as calling Ola's procedures.  It also allows significant changes to the backup process to be made quickly and easily just by UPDATEing the table, again instead of having to write or alter code.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Slow Connectivity over ODBC 8 38
SSIS with VPN COnnection 2 78
SQL Improvement  ( Speed) 14 28
SQL Server: Unable to remove duplicate sets in Header/Detail 6 25
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question