Help with Backup TSQL command.

Question

Post reply

Help with Backup TSQL command.

itsjustme

SSC Veteran

Points: 244
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: 184388 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