Print question

Question

Post reply

Print question

BigSam

SSCrazy

Points: 2146
More actions
January 19, 2012 at 12:37 pm

#249374

I need to create a custom backup process that basically replaces the date in the filename with just the Day & Hour. (It's a long story & not germain to my problem.) I've created a new database to keep a plan name, database name & a file name for the location of the backup. I have this script that is currently 'printing' the query to execute the backup - the problem is the Print statement is blank. I want to use this to make sure the query is correct before actually issuing the query. Right now, I suspect the query won't do anything either. Any advice is apprciated.
Declare @DD varchar(2),
@HH varchar(2),
@FileName NVarchar(300),
@Query NVarchar(300),
@PlanName Nvarchar(50),
@DatabaseName Nvarchar(50),
@RowCount int,
@I int
set @DD = DAY(GetDate())
If LEN(@DD) < 2 set @DD = '0' + @DD
select @HH = convert (time,getdate())
Set @I = 1
Set Nocount on
Select Identity (int, 1,1) as ID_Num, FileName, DatabaseName
into ##TempTable
from BackupMaster.dbo.tblDatabases
Where PlanName = 'Main'
Order by DatabaseName
Set @RowCount = (Select COUNT(*) from ##TempTable)
While @I <= @RowCount
Begin
Select @Filename, @Databasename
from ##TempTable
where ID_Num = @I
Set @FileName = ltrim(rtrim(@FileName)) + '_' + @DD + @HH
Set @Query = 'Backup Database [' + ltrim(rtrim(@DatabaseName)) + '] to Disk = ' + @FileName + '.bak with noformat, noinit, Name = ' + @FileName + ', Skip, Rewind,NoUnload,Stats=10'
print @Query
--Exec(@Query)
set @I = @I + 1
End
set nocount off
Drop Table ##TempTable

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jared SSC Guru Points: 61793 More actions · Answer 1

Right off the bat I can see that you are using 2 ## instead of 1 for your temp table...

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 2

Ok, look at this:

Select @Filename, @Databasename

from #TempTable

where ID_Num = @I

@filename and @databasename have not been set to anything. So they are NULL.

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 3

SQLKnowItAll (1/19/2012)
Ok, look at this:
Select @Filename, @Databasename
from #TempTable
where ID_Num = @I
@filename and @databasename have not been set to anything. So they are NULL.

Are you getting this? HINT: SELECT @filename = filename, @databasename = databasename 🙂

Jared
CE - Microsoft

BigSam SSCrazy Points: 2146 More actions · Answer 4

BigSam

SSCrazy

Points: 2146

January 20, 2012 at 6:41 am

#1436444

Thanks.

I'll see how that goes.

kritika SSC Veteran Points: 299 More actions · Answer 5

The one which u are using is a global temp table Global temporary tables are visible to any user after they are created; and they are deleted when all users referencing the table disconnect from SQL Server.

I dont think this is a requirement here.(or may be I donno if u wnt to use it :-P)

Secondly the variables must be initialised as mentioned above:-)

BigSam SSCrazy Points: 2146 More actions · Answer 6

Thanks for your replies. They are helpful.

Correcting the Select statement to populate the variables was the trick.

The information in the temp tables isn't sensitive, and I drop them at the end of the query. I've also added a test to see if they exist prior to creating the table.