Help with Backup TSQL command.

Question

Help with Backup TSQL command.

itsjustme

SSC Veteran

Points: 274
More actions
January 14, 2010 at 1:44 pm

#215428

Hi,
I am trying to use backup command to backup to disk.
I have a temporary table that stores the database names in "name" column
Temp table
Name
db1
db2
db3
db5
I want to check if the values match then
declare @db_name nvarchar(max)
if exists( select name from @TempTable)
backup database @db_name to disk =N'C:\Temp\'+@db_name+'.bak'
any help would be much appreciated.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

check if the values match what?

Currently you are never populating @dbname,also specifying it as nvarchar(max) seems overkill, have you got database names that long?

This is an example where a cursor to loop through the databases to backup would work well, as the time taken to backup the databases is by far the longest part of the query.

you can build up a list of databases from sys.databases.

---------------------------------------------------------------------

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

I think the issue is the concatenation for the backup path. When I do the concatenation I get an error, when I move the concatenation out and just use a variable there it works.

This works:

DECLARE @DBNAME NVARCHAR(100)

DECLARE @path NVARCHAR(200)

SET @DBNAME = N'master'

SET @path = N'C:\Backups\' + @dbname + N'.bak'

BACKUP DATABASE @DBNAME TO DISK = @path

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question