Use
master
Go
DECLARE
@LOC nvarchar(512) = N'H:\Backup\SQLTEST02\';
DECLARE
@BKP_EXT nvarchar(5);
DECLARE
@retval int;
SET
@BKP_EXT = '.bak';
DECLARE
@newdb table
(
newdb nvarchar(512)
);
DECLARE
@LOC2 table
(
loc2 nvarchar(512)
);
Exec
@retval = sp_track_new_databases
If
(@retval > 0)
insert
into
@newdb
Select new_db_name
from Last_DB_Track;
select * from @newdb;
insert into @LOC2
select 'backup database ' + new_db_name + ' to disk = ''' + @LOC + new_db_name + '.bak''' from last_db_track;
select * from @LOC2;
backup database @newdb to disk = @loc2;
go

quote:

---

Msg 137, Level 16, State 1, Line 21
Must declare the scalar variable "@newdb"

---

quote:

---

Msg 137, Level 16, State 1, Line 21
Must declare the scalar variable "@LOC2".

---

declare @cmd nvarchar(100)
declare @LOC2 table (id int identity, loc2 nvarchar(512))
insert into @LOC2 (loc2)
select 'backup database ' + new_db_name + ' to disk = ''' + @LOC + new_db_name + '.bak''' from last_db_track;

declare @id int
select @id = min(id) from @LOC2
while @id is not null
begin
    select @cmd = loc2 from @LOC2 where id = @id
    exec (@cmd)
    select @id = min(id) from @LOC2 where id > @id
end

quote:

---

Originally posted by khtan

Firstly on the error message itself

quote:

---

Msg 137, Level 16, State 1, Line 21
Must declare the scalar variable "@newdb"

---

you did not declare the variable @newdb

quote:

---

Msg 137, Level 16, State 1, Line 21
Must declare the scalar variable "@LOC2".

---

You declare the variable @LOC2 as table variable and not as scalar variable. The Backup Database expects scalar variable

Actually you already form the backup command in the @LOC2 table. You only need to grab the command from @LOC2 table and execute it. You can use cursor or while loop to do that.

example below uses while loop.

declare @cmd nvarchar(100)
declare @LOC2 table (id int identity, loc2 nvarchar(512))
insert into @LOC2 (loc2)
select 'backup database ' + new_db_name + ' to disk = ''' + @LOC + new_db_name + '.bak''' from last_db_track;

declare @id int
select @id = min(id) from @LOC2
while @id is not null
begin
    select @cmd = loc2 from @LOC2 where id = @id
    exec (@cmd)
    select @id = min(id) from @LOC2 where id > @id
end

---

KH
[spoiler]Time is always against us[/spoiler]

---