November 19, 2015 at 7:32 am
Hi,
I want to create a dynamic backup job for the AS databases on my servers. It should be dynamic because it should work on different servers and different backup folders.
Main problem seems to be the database name in the cursor (@db_name). I get the names from a table but I cannot use the variable without a start value (e. g. @@SERVERNAME). If it keeps blank I get the error message "Must declare the scalar variable "@db_name"." Do you have any ideas?
Another problem could be the way the cursor is collected to run it.
Is there a better way to do so?
So here is my code:
DECLARE@day char(2) = UPPER(SUBSTRING (datename(DW,getdate()-1),0,3))
DECLARE @folder nvarchar(2000) = N'C:\Temp\'
DECLARE @as nvarchar(128) = N'AnalysisServices'
DECLARE @server SYSNAME = @@SERVERNAME
DECLARE @linkedServer SYSNAME = (SELECT name FROM sys.servers WHERE provider = 'MSOLAP' AND data_source = 'localhost')
DECLARE @db nvarchar (50) = 'Test'
DECLARE @schema nvarchar(10) = '.dbo.'
DECLARE @temptable nvarchar(max) = N'TestAS'
/****** create temp table ******/
DECLARE @drop nvarchar(max) = N'DROP TABLE '+ @db + @schema + @temptable
DECLARE @openquery nvarchar(max) = N' FROM OPENQUERY(['+ @linkedServer +'],N''SELECT OBJECT_ID FROM $SYSTEM.DISCOVER_OBJECT_ACTIVITY WHERE OBJECT_PARENT_PATH = ''''' + @server + '.Databases'''''')'
DECLARE @selectinto nvarchar(max) = N'SELECT * INTO '+ @db + @schema + @temptable + @openquery
DECLARE @table nvarchar(max) = N'IF EXISTS (SELECT 1 FROM '+ @db +'.[sys].[objects] WHERE name = '''+ @temptable +''')
BEGIN
'+ @drop +'
'+ @selectinto +'
END
ELSE
BEGIN
'+ @selectinto +'
END'
EXEC sp_executesql @table
/****** check target folder ******/
DECLARE @sqlEXIST nvarchar(2000) = N'EXEC master..xp_fileexist '''+ @folder + @as +'\' + @day + '\'''
DECLARE @sqlCREATE nvarchar(2000) = N'EXEC master.dbo.xp_create_subdir '''+ @folder + @as +'\'+ @day + '\'''
DECLARE @sql nvarchar(max) = N'IF OBJECT_ID(''tempdb..#temp_db_test'') IS NOT NULL DROP TABLE #temp_db_test;
IF OBJECT_ID(''tempdb..#temp_db_test'') IS NULL CREATE TABLE #temp_db_test (FileExists int, IsDirectory int, ParentDirExists int);
INSERT INTO #temp_db_test '+ @sqlEXIST +';
IF NOT EXISTS(SELECT IsDirectory FROM #temp_db_test WHERE IsDirectory=1) '+ @sqlCREATE +';'
EXEC sp_executesql @sql
/****** backup databases ******/
DECLARE @asdbs nvarchar(1000) = N'SELECT [OBJECT_ID] FROM ' + @db + @schema + @temptable
DECLARE @db_name nvarchar(100)
DECLARE @c1 nvarchar(max) = N'
OPEN cur1
FETCH NEXT FROM cur1 INTO '+ @db_name +'
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_executesql @c2
FETCH NEXT FROM cur1 INTO '+ @db_name +'
END
CLOSE cur1
DEALLOCATE cur1'
DECLARE @c2 nvarchar(max) = N'EXEC(<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>'+ @db_name +'</DatabaseID>
</Object>
<AllowOverwrite>1</AllowOverwrite>
<File>'+ @folder + @as + '\' + @day + '\' + @db_name +'.abf</File>
</Backup>) AT '+ @linkedServer
DECLARE @c3 nvarchar(max) = N'
IF CURSOR_STATUS(''global'',''cur1'') >= 1
BEGIN
DECLARE cur1 CURSOR FOR
' + @asdbs
+ @c1
EXEC sp_executesql @c3
November 19, 2015 at 7:59 am
You never declare the variable @db_name, which should be declared in the same scope that you declare the cursor.
You really have a weird way of doing things, I missed things because you seem to be coding backwards.
November 19, 2015 at 8:05 am
Excuse me, but this reply doesn't help me :unsure:
November 19, 2015 at 8:33 am
ratloser99 36160 (11/19/2015)
Excuse me, but this reply doesn't help me :unsure:
Seriously? And should I know why? Or should I guess?
It's a basic programming fact that variables have scope. In T-SQL, the scope is local and won't be shared within different calls. That's why you should send them as parameters if you care about the value and return them as output if you want to know what happened with them.
That said, you have many errors in your dynamic SQL, and it's not formatted which makes it even more difficult to read. You segment your code in a way that the declarations show after the actual code.
The best way to debug dynamic SQL is to print it to be able to know what's going on in the code.
I don't have experience with SSAS backups, so this might not work correctly, if you fix that part, you should be good to go.
/****** backup databases ******/
DECLARE @c2 nvarchar(max),
@c3 nvarchar(max)
SET @c2 = N'EXEC(''<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>''+ @db_name +''</DatabaseID>
</Object>
<AllowOverwrite>1</AllowOverwrite>
<File>'+ @folder + @as + '\' + @day + '\''+ @db_name +''.abf</File>
</Backup>'') AT '+ @linkedServer
SET @c3 = N'
DECLARE @db_name sysname
IF CURSOR_STATUS(''global'',''cur1'') >= 1
BEGIN
DECLARE cur1 CURSOR FOR
SELECT [OBJECT_ID]
FROM ' + @db + @schema + @temptable + N'
OPEN cur1
FETCH NEXT FROM cur1 INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
' + @c2 + '
FETCH NEXT FROM cur1 INTO @db_name
END
CLOSE cur1
DEALLOCATE cur1
END'
--PRINT @c3
EXEC sp_executesql @c3
November 19, 2015 at 9:09 am
Easy guys. 'Tis the season to be jolly. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
November 19, 2015 at 9:13 am
Jeff Moden (11/19/2015)
Easy guys. 'Tis the season to be jolly. 🙂
That's me being jolly.
November 20, 2015 at 6:33 am
It works in this way:
DECLARE
@day char(2) = UPPER(SUBSTRING (datename(DW,getdate()-1),0,3))
,@folder nvarchar(2000) = N'C:\Temp\'
,@as nvarchar(128) = N'AnalysisServices'
DECLARE
@server SYSNAME = @@SERVERNAME
,@linkedServer SYSNAME = (SELECT name FROM sys.servers WHERE provider = 'MSOLAP' AND data_source = 'localhost')
/****** create temp table, only if you need to backup specific AS DBs ******/
DECLARE
@db nvarchar (50) = 'tempdb'
,@schema nvarchar(10) = '.dbo.'
,@temptable nvarchar(max) = N'TestAS'
DECLARE
@drop nvarchar(max) = N'DROP TABLE '+ @db + @schema + @temptable
,@selectinto nvarchar(max) = N'SELECT [OBJECT_ID] INTO '+ @db + @schema + @temptable + ' FROM OPENQUERY(['+ @linkedServer +'],N''SELECT OBJECT_ID FROM $SYSTEM.DISCOVER_OBJECT_ACTIVITY WHERE OBJECT_PARENT_PATH = ''''' + @server + '.Databases'''''')'
DECLARE
@table nvarchar(max) = N'IF EXISTS (SELECT 1 FROM '+ @db +'.[sys].[objects] WHERE name = '''+ @temptable +''')
BEGIN
'+ @drop +'
'+ @selectinto +'
END
ELSE
BEGIN
'+ @selectinto +'
END'
EXEC sp_executesql @table
/****** check target folder ******/
DECLARE
@sqlEXIST nvarchar(2000) = N'EXEC master..xp_fileexist '''+ @folder + @as +'\' + @day + '\'''
,@sqlCREATE nvarchar(2000) = N'EXEC master.dbo.xp_create_subdir '''+ @folder + @as +'\'+ @day + '\'''
DECLARE
@sql nvarchar(max) = N'IF OBJECT_ID(''tempdb..#temp_db_test'') IS NOT NULL DROP TABLE #temp_db_test;
IF OBJECT_ID(''tempdb..#temp_db_test'') IS NULL CREATE TABLE #temp_db_test (FileExists int, IsDirectory int, ParentDirExists int);
INSERT INTO #temp_db_test '+ @sqlEXIST +';
IF NOT EXISTS(SELECT IsDirectory FROM #temp_db_test WHERE IsDirectory=1) '+ @sqlCREATE +';'
EXEC sp_executesql @sql
DECLARE
@db_name VARCHAR(50)
,@xmla nvarchar(max)
--,@cursor nvarchar(max) = N'DECLARE cur CURSOR FOR SELECT [OBJECT_ID] FROM OPENQUERY(['+ @linkedServer +'],N''SELECT OBJECT_ID FROM $SYSTEM.DISCOVER_OBJECT_ACTIVITY WHERE OBJECT_PARENT_PATH = ''''' + @server + '.Databases'''''')' -- for all AS DBs
,@cursor nvarchar(max) = N'DECLARE cur CURSOR FOR SELECT [OBJECT_ID] FROM '+ @db + @schema + @temptable +' WHERE [OBJECT_ID] NOT LIKE ''%TEST%''' --only if you need to backup specific AS DBs
EXEC sp_executesql @cursor
OPEN cur
FETCH NEXT FROM cur INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @xmla = N'''<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>'+ @db_name +'</DatabaseID>
</Object>
<File>'+ @folder + @as +'\'+ @day +'\'+ @db_name +'.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>'''
DECLARE
@exec nvarchar(4000) = N'EXEC ('+ @xmla +') AT '+@linkedServer
EXEC sp_executesql @exec
FETCH NEXT FROM cur INTO @db_name
END
CLOSE cur
DEALLOCATE cur
EXEC sp_executesql @drop --optional
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply