Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

stored proc to backup a list of databases

Posted on 2014-01-30
4
221 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PolyServe for SQL server 13 32
CDC and AOG on MS SQL 2012 13 23
Shrink multiple databases at once 4 18
SQL Availablity Groups Shared Path 2 13
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

809 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