April 23, 2009 at 7:22 am
Hello together,
I want to use a variable in an ALTER DATABASE command.
Example:
DECLARE @db varchar(32)
set @db = 'Test'
Alter Database @db Set Recovery Simple
Also with exec it is not possible:
EXEC ('Alter Database @db Set Recovery Simple')
Has anybody an idea.
Thanks & Regards
April 23, 2009 at 8:01 am
Try
DECLARE @sql varchar(255),
@db varchar(32)
SET @db = 'Test'
SET @sql = 'ALTER DATABASE ' + @db + ' SET RECOVERY SIMPLE'
EXEC (@sql)
Hope that helps.
Mike
April 23, 2009 at 8:03 am
Mike beat me to the solution.
I do have to ask, why do you need to do this?
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
April 24, 2009 at 12:30 am
Thanks for your help.
Normally I should know this.
My idea is to change the recovery model and to shrink the transaction protocol with a script.
The change the current database.
See as the following:
DECLARE @strDatabaseName varchar(32)
DECLARE @strTransactionProtocol varchar(32)
DECLARE @strSQL varchar(256)
SET @strDatabaseName = DB_NAME()
SET @strSQL = 'Alter Database ' + @strDatabaseName + ' Set Recovery Simple'
EXEC (@strSQL)
SET @strTransactionProtocol = (SELECT name FROM sys.database_files WHERE type = 1) --LOG
SET @strSQL = 'DBCC SHRINKFILE(' + @strTransactionProtocol + ', 1) WITH NO_INFOMSGS'
EXEC (@strSQL)
April 24, 2009 at 6:31 am
You should read this article[/url] and this blog post.
Shrinking the transaction log is not a recommended practice.
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
April 24, 2009 at 7:22 am
Why not?
I only want to use some databases in a testing enviroment.
April 24, 2009 at 7:34 am
Jack, I won't speak for the OP, but I recently did something along the same lines, where the database name had to be generated dynamically to save me a ton of time.
The place I'm working for sells an application to specific organizations; each organization gets their own database. About 100 clients, each with their own training database, so 200 user databases. When I started here about two months ago, the first thing I did was to go through a couple of the databases and start looking at their properties, trying to get familiar with the overall setup. What I found was that some of the datbases were in 8.0 compatibility mode, some were in Simple Recovery, that kind of thing.
So, I wrote a script that used the database name to enforce the same settings across all the databases - recovery mode, etc. - and had it done in about 15 minutes.
April 24, 2009 at 7:36 am
If you check out the articles I reference you will see the reason(s) why. Mainly because the log grew to that size for a reason so it will likely grow there again, and growth is an expensive operation.
If you are doing it in a test/development environment it is probably okay, but should really never (or very rarely) be done in a production environment. You had not mentioned that this was for a test environment.
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
April 24, 2009 at 7:39 am
David (4/24/2009)
Jack, I won't speak for the OP, but I recently did something along the same lines, where the database name had to be generated dynamically to save me a ton of time.The place I'm working for sells an application to specific organizations; each organization gets their own database. About 100 clients, each with their own training database, so 200 user databases. When I started here about two months ago, the first thing I did was to go through a couple of the databases and start looking at their properties, trying to get familiar with the overall setup. What I found was that some of the datbases were in 8.0 compatibility mode, some were in Simple Recovery, that kind of thing.
So, I wrote a script that used the database name to enforce the same settings across all the databases - recovery mode, etc. - and had it done in about 15 minutes.
Thanks David, that is definitely a valid reason for doing this. I only asked why because sometimes there is a better way to do what needs to be done, if you know the why.
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
April 24, 2009 at 7:44 am
For non-production environments, below are the SQL statements that I versus SQL Server 2000 where some minor modifactions to run under 2005 are needed (change the names of the 2000 system tables to the corresponding 2005 views).
Shrink the transaction log file to 10% of the database size:
exec dbo.sp_MSforeachdb
'declare @SQLCmdnvarchar(4000)
,@DatabaseNamesysname
,@DataMbnumeric(8,2)
,@LogMbnumeric(8,2)
,@LogNamenvarchar(255)
,@logfilesinteger
,@DebugIndchar(1)
set@DebugInd= ''N''
set@DatabaseName = ''?''
SELECT@DataMb = SUM ( (sysfiles.size * 8) / 1024.0 )
FROM [?].dbo.sysfilesas sysfiles
where (sysfiles.status & 0x40 0x40) -- NOT a transacton log file
SELECT@LogMb = SUM ( (sysfiles.size * 8) / 1024.0 )
,@logfiles= count(*)
FROM [?].dbo.sysfilesas sysfiles
where (sysfiles.status & 0x40 = 0x40)
/*
select@DatabaseName as DatabaseName , @LogMb as LogMb , @DataMb as DataMb, @LogMb / @DataMb as RatioMb
*/
IF@logfiles = 1
and@LogMb> 1
AND@LogMb / @DataMb > .1
begin
set@SQLCmd = ''backup log ['' + @DatabaseName + ''] with truncate_only''
IF@DebugInd ''N''
select@DatabaseName as DatabaseName, @SQLCmd
exec (@SQLCmd)
select @LogName = name
FROM [?].dbo.sysfilesas sysfiles
where (sysfiles.status & 0x40 = 0x40)
set@LogName = rtrim(@LogName)
set@SQLCmd = ''use ['' + @DatabaseName + ''] DBCC SHRINKFILE(['' + @LogName + ''],1)''
IF@DebugInd ''N''
select@DatabaseName as DatabaseName, @SQLCmd
exec (@SQLCmd)
IF@DataMb > 10
BEGIN
set@SQLCmd
= ''ALTER DATABASE ['' + @DatabaseName + ''] MODIFY FILE ( NAME = '' + @LogName + '', SIZE = ''
+CAST( CEILING ( @DataMb / 10 ) as varchar(8) ) + ''MB ) ''
IF@DebugInd ''N''
select@DatabaseName as DatabaseName, @SQLCmd
exec (@SQLCmd)
END
END'
Here is the SQL to set the database options:
IF OBJECT_ID('tempdb..#AlterSQL') is not null drop table #AlterSQL
create table #AlterSQL
(AlterSQLnvarchar(4000)
)
insert into #AlterSQL
(AlterSQL)
select AlterSQL
FROM(
selectdb.name
,'ALTER DATABASE [' + db.name + '] SET MULTI_USER' as AlterSQL
from master.dbo.sysdatabases db
where DATABASEPROPERTYEX(db.name,'UserAccess') 'MULTI_USER'
and db.name not in ('master','model','tempdb','msdb')
) as X
IF 0 (select count(*) from #AlterSQL)
BEGIN
exec sp_execresultset @cmd = 'select AlterSQL from #AlterSQL'
truncate table #AlterSQL
END
insert into #AlterSQL
(AlterSQL)
select AlterSQL
FROM(
selectdb.name
,'ALTER DATABASE [' + db.name + '] SET READ_WRITE' as AlterSQL
from master.dbo.sysdatabases db
where DATABASEPROPERTYEX(db.name,'UserAccess') 'READ_WRITE'
and db.name not in ('master','model','tempdb','msdb')
) as X
IF 0 (select count(*) from #AlterSQL)
BEGIN
exec sp_execresultset @cmd = 'select AlterSQL from #AlterSQL'
truncate table #AlterSQL
END
insert into #AlterSQL
(AlterSQL)
select AlterSQL
FROM(
-- Turn off options
selectdb.name
,'ALTER DATABASE [' + db.name + '] SET AUTO_CLOSE OFF'as AlterSQL
from master.dbo.sysdatabases db
where DATABASEPROPERTYEX(db.name,'IsAutoClose') = 1
and DATABASEPROPERTYEX(db.name,'Status') = 'Online'
union all
selectdb.name
,'ALTER DATABASE [' + db.name + '] SET AUTO_SHRINK OFF'
from master.dbo.sysdatabases db
where DATABASEPROPERTYEX(db.name,'IsAutoShrink') = 1
and DATABASEPROPERTYEX(db.name,'Status') = 'Online'
union all
-- Turn ON options
selectdb.name
,'ALTER DATABASE [' + db.name + '] SET TORN_PAGE_DETECTION ON'
from master.dbo.sysdatabases db
where DATABASEPROPERTYEX(db.name,'IsTornPageDetectionEnabled') = 0
and DATABASEPROPERTYEX(db.name,'Status') = 'Online'
union all
selectdb.name
,'ALTER DATABASE [' + db.name + '] SET AUTO_CREATE_STATISTICS ON'
from master.dbo.sysdatabases db
where DATABASEPROPERTYEX(db.name,'IsAutoCreateStatistics') = 0
and DATABASEPROPERTYEX(db.name,'Status') = 'Online'
union all
selectdb.name
,'ALTER DATABASE [' + db.name + '] SET AUTO_UPDATE_STATISTICS ON'
from master.dbo.sysdatabases db
where DATABASEPROPERTYEX(db.name,'IsAutoUpdateStatistics') = 0
and DATABASEPROPERTYEX(db.name,'Status') = 'Online'
union all
selectdb.name
,'ALTER DATABASE [' + db.name + '] SET RECOVERY SIMPLE'
from master.dbo.sysdatabases db
where DATABASEPROPERTYEX(db.name,'RECOVERY') 'SIMPLE'
and DATABASEPROPERTYEX(db.name,'Status') = 'Online'
) as X
whereX.name not in ('master','model','tempdb','msdb')
IF 0 (select count(*) from #AlterSQL)
BEGIN
exec sp_execresultset @cmd = 'select AlterSQL from #AlterSQL'
END
Finally,here is the SQL Server 2000 system procedure sp_execresultset
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create procedure dbo.sp_execresultset
(
@cmd nvarchar(4000),-- Command that will return a resultset to concat and execute
@debugbit = 0-- Display debug information and resultset query (no exec)
)
as
begin
set nocount on
declare @MAXBUFFLENint,
@qPosition int,
@qValuenvarchar(4000),
@scriptLen int,
@scriptText nvarchar(4000),
@maxIndexbigint,
@rowIndexbigint
-- declare 250 nvarchar(4000) (nearly 1MB worth of space)
declare @q1 nvarchar(4000), @q2 nvarchar(4000), @q3 nvarchar(4000), @q4 nvarchar(4000), @q5 nvarchar(4000), @q6 nvarchar(4000), @q7 nvarchar(4000), @q8 nvarchar(4000), @q9 nvarchar(4000), @q10 nvarchar(4000),
@q11 nvarchar(4000), @q12 nvarchar(4000), @q13 nvarchar(4000), @q14 nvarchar(4000), @q15 nvarchar(4000), @q16 nvarchar(4000), @q17 nvarchar(4000), @q18 nvarchar(4000), @q19 nvarchar(4000), @q20 nvarchar(4000),
@q21 nvarchar(4000), @q22 nvarchar(4000), @q23 nvarchar(4000), @q24 nvarchar(4000), @q25 nvarchar(4000), @q26 nvarchar(4000), @q27 nvarchar(4000), @q28 nvarchar(4000), @q29 nvarchar(4000), @q30 nvarchar(4000),
@q31 nvarchar(4000), @q32 nvarchar(4000), @q33 nvarchar(4000), @q34 nvarchar(4000), @q35 nvarchar(4000), @q36 nvarchar(4000), @q37 nvarchar(4000), @q38 nvarchar(4000), @q39 nvarchar(4000), @q40 nvarchar(4000),
@q41 nvarchar(4000), @q42 nvarchar(4000), @q43 nvarchar(4000), @q44 nvarchar(4000), @q45 nvarchar(4000), @q46 nvarchar(4000), @q47 nvarchar(4000), @q48 nvarchar(4000), @q49 nvarchar(4000), @q50 nvarchar(4000),
@q51 nvarchar(4000), @q52 nvarchar(4000), @q53 nvarchar(4000), @q54 nvarchar(4000), @q55 nvarchar(4000), @q56 nvarchar(4000), @q57 nvarchar(4000), @q58 nvarchar(4000), @q59 nvarchar(4000), @q60 nvarchar(4000),
@q61 nvarchar(4000), @q62 nvarchar(4000), @q63 nvarchar(4000), @q64 nvarchar(4000), @q65 nvarchar(4000), @q66 nvarchar(4000), @q67 nvarchar(4000), @q68 nvarchar(4000), @q69 nvarchar(4000), @q70 nvarchar(4000),
@q71 nvarchar(4000), @q72 nvarchar(4000), @q73 nvarchar(4000), @q74 nvarchar(4000), @q75 nvarchar(4000), @q76 nvarchar(4000), @q77 nvarchar(4000), @q78 nvarchar(4000), @q79 nvarchar(4000), @q80 nvarchar(4000),
@q81 nvarchar(4000), @q82 nvarchar(4000), @q83 nvarchar(4000), @q84 nvarchar(4000), @q85 nvarchar(4000), @q86 nvarchar(4000), @q87 nvarchar(4000), @q88 nvarchar(4000), @q89 nvarchar(4000), @q90 nvarchar(4000),
@q91 nvarchar(4000), @q92 nvarchar(4000), @q93 nvarchar(4000), @q94 nvarchar(4000), @q95 nvarchar(4000), @q96 nvarchar(4000), @q97 nvarchar(4000), @q98 nvarchar(4000), @q99 nvarchar(4000), @q100 nvarchar(4000),
@q101 nvarchar(4000), @q102 nvarchar(4000), @q103 nvarchar(4000), @q104 nvarchar(4000), @q105 nvarchar(4000), @q106 nvarchar(4000), @q107 nvarchar(4000), @q108 nvarchar(4000), @q109 nvarchar(4000), @q110 nvarchar(4000),
@q111 nvarchar(4000), @q112 nvarchar(4000), @q113 nvarchar(4000), @q114 nvarchar(4000), @q115 nvarchar(4000), @q116 nvarchar(4000), @q117 nvarchar(4000), @q118 nvarchar(4000), @q119 nvarchar(4000), @q120 nvarchar(4000),
@q121 nvarchar(4000), @q122 nvarchar(4000), @q123 nvarchar(4000), @q124 nvarchar(4000), @q125 nvarchar(4000), @q126 nvarchar(4000), @q127 nvarchar(4000), @q128 nvarchar(4000), @q129 nvarchar(4000), @q130 nvarchar(4000),
@q131 nvarchar(4000), @q132 nvarchar(4000), @q133 nvarchar(4000), @q134 nvarchar(4000), @q135 nvarchar(4000), @q136 nvarchar(4000), @q137 nvarchar(4000), @q138 nvarchar(4000), @q139 nvarchar(4000), @q140 nvarchar(4000),
@q141 nvarchar(4000), @q142 nvarchar(4000), @q143 nvarchar(4000), @q144 nvarchar(4000), @q145 nvarchar(4000), @q146 nvarchar(4000), @q147 nvarchar(4000), @q148 nvarchar(4000), @q149 nvarchar(4000), @q150 nvarchar(4000),
@q151 nvarchar(4000), @q152 nvarchar(4000), @q153 nvarchar(4000), @q154 nvarchar(4000), @q155 nvarchar(4000), @q156 nvarchar(4000), @q157 nvarchar(4000), @q158 nvarchar(4000), @q159 nvarchar(4000), @q160 nvarchar(4000),
@q161 nvarchar(4000), @q162 nvarchar(4000), @q163 nvarchar(4000), @q164 nvarchar(4000), @q165 nvarchar(4000), @q166 nvarchar(4000), @q167 nvarchar(4000), @q168 nvarchar(4000), @q169 nvarchar(4000), @q170 nvarchar(4000),
@q171 nvarchar(4000), @q172 nvarchar(4000), @q173 nvarchar(4000), @q174 nvarchar(4000), @q175 nvarchar(4000), @q176 nvarchar(4000), @q177 nvarchar(4000), @q178 nvarchar(4000), @q179 nvarchar(4000), @q180 nvarchar(4000),
@q181 nvarchar(4000), @q182 nvarchar(4000), @q183 nvarchar(4000), @q184 nvarchar(4000), @q185 nvarchar(4000), @q186 nvarchar(4000), @q187 nvarchar(4000), @q188 nvarchar(4000), @q189 nvarchar(4000), @q190 nvarchar(4000),
@q191 nvarchar(4000), @q192 nvarchar(4000), @q193 nvarchar(4000), @q194 nvarchar(4000), @q195 nvarchar(4000), @q196 nvarchar(4000), @q197 nvarchar(4000), @q198 nvarchar(4000), @q199 nvarchar(4000), @q200 nvarchar(4000),
@q201 nvarchar(4000), @q202 nvarchar(4000), @q203 nvarchar(4000), @q204 nvarchar(4000), @q205 nvarchar(4000), @q206 nvarchar(4000), @q207 nvarchar(4000), @q208 nvarchar(4000), @q209 nvarchar(4000), @q210 nvarchar(4000),
@q211 nvarchar(4000), @q212 nvarchar(4000), @q213 nvarchar(4000), @q214 nvarchar(4000), @q215 nvarchar(4000), @q216 nvarchar(4000), @q217 nvarchar(4000), @q218 nvarchar(4000), @q219 nvarchar(4000), @q220 nvarchar(4000),
@q221 nvarchar(4000), @q222 nvarchar(4000), @q223 nvarchar(4000), @q224 nvarchar(4000), @q225 nvarchar(4000), @q226 nvarchar(4000), @q227 nvarchar(4000), @q228 nvarchar(4000), @q229 nvarchar(4000), @q230 nvarchar(4000),
@q231 nvarchar(4000), @q232 nvarchar(4000), @q233 nvarchar(4000), @q234 nvarchar(4000), @q235 nvarchar(4000), @q236 nvarchar(4000), @q237 nvarchar(4000), @q238 nvarchar(4000), @q239 nvarchar(4000), @q240 nvarchar(4000),
@q241 nvarchar(4000), @q242 nvarchar(4000), @q243 nvarchar(4000), @q244 nvarchar(4000), @q245 nvarchar(4000), @q246 nvarchar(4000), @q247 nvarchar(4000), @q248 nvarchar(4000), @q249 nvarchar(4000), @q250 nvarchar(4000)
-- initialize all vars
select @q1 = N'', @q2 = N'', @q3 = N'', @q4 = N'', @q5 = N'', @q6 = N'', @q7 = N'', @q8 = N'', @q9 = N'', @q10 = N'',
@q11 = N'', @q12 = N'', @q13 = N'', @q14 = N'', @q15 = N'', @q16 = N'', @q17 = N'', @q18 = N'', @q19 = N'', @q20 = N'',
@q21 = N'', @q22 = N'', @q23 = N'', @q24 = N'', @q25 = N'', @q26 = N'', @q27 = N'', @q28 = N'', @q29 = N'', @q30 = N'',
@q31 = N'', @q32 = N'', @q33 = N'', @q34 = N'', @q35 = N'', @q36 = N'', @q37 = N'', @q38 = N'', @q39 = N'', @q40 = N'',
@q41 = N'', @q42 = N'', @q43 = N'', @q44 = N'', @q45 = N'', @q46 = N'', @q47 = N'', @q48 = N'', @q49 = N'', @q50 = N'',
@q51 = N'', @q52 = N'', @q53 = N'', @q54 = N'', @q55 = N'', @q56 = N'', @q57 = N'', @q58 = N'', @q59 = N'', @q60 = N'',
@q61 = N'', @q62 = N'', @q63 = N'', @q64 = N'', @q65 = N'', @q66 = N'', @q67 = N'', @q68 = N'', @q69 = N'', @q70 = N'',
@q71 = N'', @q72 = N'', @q73 = N'', @q74 = N'', @q75 = N'', @q76 = N'', @q77 = N'', @q78 = N'', @q79 = N'', @q80 = N'',
@q81 = N'', @q82 = N'', @q83 = N'', @q84 = N'', @q85 = N'', @q86 = N'', @q87 = N'', @q88 = N'', @q89 = N'', @q90 = N'',
@q91 = N'', @q92 = N'', @q93 = N'', @q94 = N'', @q95 = N'', @q96 = N'', @q97 = N'', @q98 = N'', @q99 = N'', @q100 = N'',
@q101 = N'', @q102 = N'', @q103 = N'', @q104 = N'', @q105 = N'', @q106 = N'', @q107 = N'', @q108 = N'', @q109 = N'', @q110 = N'',
@q111 = N'', @q112 = N'', @q113 = N'', @q114 = N'', @q115 = N'', @q116 = N'', @q117 = N'', @q118 = N'', @q119 = N'', @q120 = N'',
@q121 = N'', @q122 = N'', @q123 = N'', @q124 = N'', @q125 = N'', @q126 = N'', @q127 = N'', @q128 = N'', @q129 = N'', @q130 = N'',
@q131 = N'', @q132 = N'', @q133 = N'', @q134 = N'', @q135 = N'', @q136 = N'', @q137 = N'', @q138 = N'', @q139 = N'', @q140 = N'',
@q141 = N'', @q142 = N'', @q143 = N'', @q144 = N'', @q145 = N'', @q146 = N'', @q147 = N'', @q148 = N'', @q149 = N'', @q150 = N'',
@q151 = N'', @q152 = N'', @q153 = N'', @q154 = N'', @q155 = N'', @q156 = N'', @q157 = N'', @q158 = N'', @q159 = N'', @q160 = N'',
@q161 = N'', @q162 = N'', @q163 = N'', @q164 = N'', @q165 = N'', @q166 = N'', @q167 = N'', @q168 = N'', @q169 = N'', @q170 = N'',
@q171 = N'', @q172 = N'', @q173 = N'', @q174 = N'', @q175 = N'', @q176 = N'', @q177 = N'', @q178 = N'', @q179 = N'', @q180 = N'',
@q181 = N'', @q182 = N'', @q183 = N'', @q184 = N'', @q185 = N'', @q186 = N'', @q187 = N'', @q188 = N'', @q189 = N'', @q190 = N'',
@q191 = N'', @q192 = N'', @q193 = N'', @q194 = N'', @q195 = N'', @q196 = N'', @q197 = N'', @q198 = N'', @q199 = N'', @q200 = N'',
@q201 = N'', @q202 = N'', @q203 = N'', @q204 = N'', @q205 = N'', @q206 = N'', @q207 = N'', @q208 = N'', @q209 = N'', @q210 = N'',
@q211 = N'', @q212 = N'', @q213 = N'', @q214 = N'', @q215 = N'', @q216 = N'', @q217 = N'', @q218 = N'', @q219 = N'', @q220 = N'',
@q221 = N'', @q222 = N'', @q223 = N'', @q224 = N'', @q225 = N'', @q226 = N'', @q227 = N'', @q228 = N'', @q229 = N'', @q230 = N'',
@q231 = N'', @q232 = N'', @q233 = N'', @q234 = N'', @q235 = N'', @q236 = N'', @q237 = N'', @q238 = N'', @q239 = N'', @q240 = N'',
@q241 = N'', @q242 = N'', @q243 = N'', @q244 = N'', @q245 = N'', @q246 = N'', @q247 = N'', @q248 = N'', @q249 = N'', @q250 = N''
select @MAXBUFFLEN = 4000,
@qPosition= 1,
@qValue= N'',
@scriptLen= 0,
@scriptText = N''
-- check if our scripting table exists, if so let's drop it
if object_id('tempdb..#MSrepl_exec_script', 'U') is not null
begin
drop table #MSrepl_exec_script
end
-- create and load a local tmp table with the resultset based off of the cmd provided
create table #MSrepl_exec_script (orderCol int identity(1,1) primary key clustered, scriptText nvarchar(4000))
if @@error 0
begin
goto Error
end
insert into #MSrepl_exec_script(scriptText) exec(@cmd)
if @@error 0
begin
goto Error
end
-- get the max value inserted in the identity col for
-- #MSrepl_exec_script table and set the rowIndex = 1
select @maxIndex = max(orderCol),
@rowIndex = 1
from #MSrepl_exec_script
-- retrieve the current scriptText row
select @scriptText = isnull(scriptText, N'')
from #MSrepl_exec_script
where orderCol = @rowIndex
while (@rowIndex <= @maxIndex)
begin
select @scriptLen = @scriptLen + datalength(@scriptText)/2
if (@scriptLen < @MAXBUFFLEN)
begin
-- for this case we continue to append since we know we have room
select @qValue = @qValue + @scriptText + N' '
-- add one to the script length for the space we just added
select @scriptLen = @scriptLen + 1
-- increment the row position counter
select @rowIndex = @rowIndex + 1
-- retrieve the current scriptText row
select @scriptText = isnull(scriptText, N'')
from #MSrepl_exec_script
where orderCol = @rowIndex
-- If there are no more script pieces then we will not continue
-- instead we will set the current @qPosition and the loop will exit
--
-- Also be sure that if we reach the MAXBUFFLEN at this point we do not
-- continue. Instead we want to set the value below first before we continue.
-- FYI: It will never be possible to be greater than MAXBUFFLEN in this section.
if(@rowIndex @MAXBUFFLEN)
begin
-- here we have exceeded the @MAXBUFFLEN char limit so we will
-- fill the @qValue buffer and then store off the remaining
-- scriptText to be appended on the next itteration of the loop
select @qValue = @qValue + left(@scriptText, @MAXBUFFLEN - datalength(@qValue)/2)
select @scriptText = right(@scriptText, @scriptLen - @MAXBUFFLEN)
end
else
begin
-- Append the entire scriptText since we know we have room. Do not
-- add the command seperator (space) since there is no room for that
select @qValue = @qValue + @scriptText
-- set the scripttext to empty so that on the next loop
-- we will add a space to the first qPostition buffer
select @scriptText = N''
end
-- set one of the 250 vars
-- we need to break this 'else if' list by 100 because there is a limit of nesting levels
-- this is a good idea in any case because it should speed things up a bit
if @qPosition <= 100
begin
if @qPosition = 1 select @q1 = @qValue else if @qPosition = 2 select @q2 = @qValue else if @qPosition = 3 select @q3 = @qValue else if @qPosition = 4 select @q4 = @qValue else if @qPosition = 5 select @q5 = @qValue else if @qPosition = 6 select @q6 = @qValue else if @qPosition = 7 select @q7 = @qValue else if @qPosition = 8 select @q8 = @qValue else if @qPosition = 9 select @q9 = @qValue else if @qPosition = 10 select @q10 = @qValue
else if @qPosition = 11 select @q11 = @qValue else if @qPosition = 12 select @q12 = @qValue else if @qPosition = 13 select @q13 = @qValue else if @qPosition = 14 select @q14 = @qValue else if @qPosition = 15 select @q15 = @qValue else if @qPosition = 16 select @q16 = @qValue else if @qPosition = 17 select @q17 = @qValue else if @qPosition = 18 select @q18 = @qValue else if @qPosition = 19 select @q19 = @qValue else if @qPosition = 20 select @q20 = @qValue
else if @qPosition = 21 select @q21 = @qValue else if @qPosition = 22 select @q22 = @qValue else if @qPosition = 23 select @q23 = @qValue else if @qPosition = 24 select @q24 = @qValue else if @qPosition = 25 select @q25 = @qValue else if @qPosition = 26 select @q26 = @qValue else if @qPosition = 27 select @q27 = @qValue else if @qPosition = 28 select @q28 = @qValue else if @qPosition = 29 select @q29 = @qValue else if @qPosition = 30 select @q30 = @qValue
else if @qPosition = 31 select @q31 = @qValue else if @qPosition = 32 select @q32 = @qValue else if @qPosition = 33 select @q33 = @qValue else if @qPosition = 34 select @q34 = @qValue else if @qPosition = 35 select @q35 = @qValue else if @qPosition = 36 select @q36 = @qValue else if @qPosition = 37 select @q37 = @qValue else if @qPosition = 38 select @q38 = @qValue else if @qPosition = 39 select @q39 = @qValue else if @qPosition = 40 select @q40 = @qValue
else if @qPosition = 41 select @q41 = @qValue else if @qPosition = 42 select @q42 = @qValue else if @qPosition = 43 select @q43 = @qValue else if @qPosition = 44 select @q44 = @qValue else if @qPosition = 45 select @q45 = @qValue else if @qPosition = 46 select @q46 = @qValue else if @qPosition = 47 select @q47 = @qValue else if @qPosition = 48 select @q48 = @qValue else if @qPosition = 49 select @q49 = @qValue else if @qPosition = 50 select @q50 = @qValue
else if @qPosition = 51 select @q51 = @qValue else if @qPosition = 52 select @q52 = @qValue else if @qPosition = 53 select @q53 = @qValue else if @qPosition = 54 select @q54 = @qValue else if @qPosition = 55 select @q55 = @qValue else if @qPosition = 56 select @q56 = @qValue else if @qPosition = 57 select @q57 = @qValue else if @qPosition = 58 select @q58 = @qValue else if @qPosition = 59 select @q59 = @qValue else if @qPosition = 60 select @q60 = @qValue
else if @qPosition = 61 select @q61 = @qValue else if @qPosition = 62 select @q62 = @qValue else if @qPosition = 63 select @q63 = @qValue else if @qPosition = 64 select @q64 = @qValue else if @qPosition = 65 select @q65 = @qValue else if @qPosition = 66 select @q66 = @qValue else if @qPosition = 67 select @q67 = @qValue else if @qPosition = 68 select @q68 = @qValue else if @qPosition = 69 select @q69 = @qValue else if @qPosition = 70 select @q70 = @qValue
else if @qPosition = 71 select @q71 = @qValue else if @qPosition = 72 select @q72 = @qValue else if @qPosition = 73 select @q73 = @qValue else if @qPosition = 74 select @q74 = @qValue else if @qPosition = 75 select @q75 = @qValue else if @qPosition = 76 select @q76 = @qValue else if @qPosition = 77 select @q77 = @qValue else if @qPosition = 78 select @q78 = @qValue else if @qPosition = 79 select @q79 = @qValue else if @qPosition = 80 select @q80 = @qValue
else if @qPosition = 81 select @q81 = @qValue else if @qPosition = 82 select @q82 = @qValue else if @qPosition = 83 select @q83 = @qValue else if @qPosition = 84 select @q84 = @qValue else if @qPosition = 85 select @q85 = @qValue else if @qPosition = 86 select @q86 = @qValue else if @qPosition = 87 select @q87 = @qValue else if @qPosition = 88 select @q88 = @qValue else if @qPosition = 89 select @q89 = @qValue else if @qPosition = 90 select @q90 = @qValue
else if @qPosition = 91 select @q91 = @qValue else if @qPosition = 92 select @q92 = @qValue else if @qPosition = 93 select @q93 = @qValue else if @qPosition = 94 select @q94 = @qValue else if @qPosition = 95 select @q95 = @qValue else if @qPosition = 96 select @q96 = @qValue else if @qPosition = 97 select @q97 = @qValue else if @qPosition = 98 select @q98 = @qValue else if @qPosition = 99 select @q99 = @qValue else if @qPosition = 100 select @q100 = @qValue
end
else if @qPosition 250
begin
raiserror('Internal Error : Command buffer limit of ~1MB has been reached! Resultset will not be executed.', 16, -1)
goto Error
end
end
-- Since we just filled a buffer move the index value by one
-- and reset the current query row value to empty and len to 0
select @qPosition= @qPosition + 1,
@qValue= N'',
@scriptLen= 0
end
if @debug = 0
begin
-- concat all buffers and execute
exec(@q1+@q2+@q3+@q4+@q5+@q6+@q7+@q8+@q9+@q10+
@q11+@q12+@q13+@q14+@q15+@q16+@q17+@q18+@q19+@q20+
@q21+@q22+@q23+@q24+@q25+@q26+@q27+@q28+@q29+@q30+
@q31+@q32+@q33+@q34+@q35+@q36+@q37+@q38+@q39+@q40+
@q41+@q42+@q43+@q44+@q45+@q46+@q47+@q48+@q49+@q50+
@q51+@q52+@q53+@q54+@q55+@q56+@q57+@q58+@q59+@q60+
@q61+@q62+@q63+@q64+@q65+@q66+@q67+@q68+@q69+@q70+
@q71+@q72+@q73+@q74+@q75+@q76+@q77+@q78+@q79+@q80+
@q81+@q82+@q83+@q84+@q85+@q86+@q87+@q88+@q89+@q90+
@q91+@q92+@q93+@q94+@q95+@q96+@q97+@q98+@q99+@q100+
@q101+@q102+@q103+@q104+@q105+@q106+@q107+@q108+@q109+@q110+
@q111+@q112+@q113+@q114+@q115+@q116+@q117+@q118+@q119+@q120+
@q121+@q122+@q123+@q124+@q125+@q126+@q127+@q128+@q129+@q130+
@q131+@q132+@q133+@q134+@q135+@q136+@q137+@q138+@q139+@q140+
@q141+@q142+@q143+@q144+@q145+@q146+@q147+@q148+@q149+@q150+
@q151+@q152+@q153+@q154+@q155+@q156+@q157+@q158+@q159+@q160+
@q161+@q162+@q163+@q164+@q165+@q166+@q167+@q168+@q169+@q170+
@q171+@q172+@q173+@q174+@q175+@q176+@q177+@q178+@q179+@q180+
@q181+@q182+@q183+@q184+@q185+@q186+@q187+@q188+@q189+@q190+
@q191+@q192+@q193+@q194+@q195+@q196+@q197+@q198+@q199+@q200+
@q201+@q202+@q203+@q204+@q205+@q206+@q207+@q208+@q209+@q210+
@q211+@q212+@q213+@q214+@q215+@q216+@q217+@q218+@q219+@q220+
@q221+@q222+@q223+@q224+@q225+@q226+@q227+@q228+@q229+@q230+
@q231+@q232+@q233+@q234+@q235+@q236+@q237+@q238+@q239+@q240+
@q241+@q242+@q243+@q244+@q245+@q246+@q247+@q248+@q249+@q250)
if @@error 0
begin
goto Error
end
end
else
begin
-- print out debug info
select '@q1' = datalength(@q1)/2, '@q2' = datalength(@q2)/2, '@q3' = datalength(@q3)/2, '@q4' = datalength(@q4)/2, '@q5' = datalength(@q5)/2, '@q6' = datalength(@q6)/2, '@q7' = datalength(@q7)/2, '@q8' = datalength(@q8)/2, '@q9' = datalength(@q9)/2, '@q10' = datalength(@q10)/2,
'@q11' = datalength(@q11)/2, '@q12' = datalength(@q12)/2, '@q13' = datalength(@q13)/2, '@q14' = datalength(@q14)/2, '@q15' = datalength(@q15)/2, '@q16' = datalength(@q16)/2, '@q17' = datalength(@q17)/2, '@q18' = datalength(@q18)/2, '@q19' = datalength(@q19)/2, '@q20' = datalength(@q20)/2,
'@q21' = datalength(@q21)/2, '@q22' = datalength(@q22)/2, '@q23' = datalength(@q23)/2, '@q24' = datalength(@q24)/2, '@q25' = datalength(@q25)/2, '@q26' = datalength(@q26)/2, '@q27' = datalength(@q27)/2, '@q28' = datalength(@q28)/2, '@q29' = datalength(@q29)/2, '@q30' = datalength(@q30)/2,
'@q31' = datalength(@q31)/2, '@q32' = datalength(@q32)/2, '@q33' = datalength(@q33)/2, '@q34' = datalength(@q34)/2, '@q35' = datalength(@q35)/2, '@q36' = datalength(@q36)/2, '@q37' = datalength(@q37)/2, '@q38' = datalength(@q38)/2, '@q39' = datalength(@q39)/2, '@q40' = datalength(@q40)/2,
'@q41' = datalength(@q41)/2, '@q42' = datalength(@q42)/2, '@q43' = datalength(@q43)/2, '@q44' = datalength(@q44)/2, '@q45' = datalength(@q45)/2, '@q46' = datalength(@q46)/2, '@q47' = datalength(@q47)/2, '@q48' = datalength(@q48)/2, '@q49' = datalength(@q49)/2, '@q50' = datalength(@q50)/2,
'@q51' = datalength(@q51)/2, '@q52' = datalength(@q52)/2, '@q53' = datalength(@q53)/2, '@q54' = datalength(@q54)/2, '@q55' = datalength(@q55)/2, '@q56' = datalength(@q56)/2, '@q57' = datalength(@q57)/2, '@q58' = datalength(@q58)/2, '@q59' = datalength(@q59)/2, '@q60' = datalength(@q60)/2,
'@q61' = datalength(@q61)/2, '@q62' = datalength(@q62)/2, '@q63' = datalength(@q63)/2, '@q64' = datalength(@q64)/2, '@q65' = datalength(@q65)/2, '@q66' = datalength(@q66)/2, '@q67' = datalength(@q67)/2, '@q68' = datalength(@q68)/2, '@q69' = datalength(@q69)/2, '@q70' = datalength(@q70)/2,
'@q71' = datalength(@q71)/2, '@q72' = datalength(@q72)/2, '@q73' = datalength(@q73)/2, '@q74' = datalength(@q74)/2, '@q75' = datalength(@q75)/2, '@q76' = datalength(@q76)/2, '@q77' = datalength(@q77)/2, '@q78' = datalength(@q78)/2, '@q79' = datalength(@q79)/2, '@q80' = datalength(@q80)/2,
'@q81' = datalength(@q81)/2, '@q82' = datalength(@q82)/2, '@q83' = datalength(@q83)/2, '@q84' = datalength(@q84)/2, '@q85' = datalength(@q85)/2, '@q86' = datalength(@q86)/2, '@q87' = datalength(@q87)/2, '@q88' = datalength(@q88)/2, '@q89' = datalength(@q89)/2, '@q90' = datalength(@q90)/2,
'@q91' = datalength(@q91)/2, '@q92' = datalength(@q92)/2, '@q93' = datalength(@q93)/2, '@q94' = datalength(@q94)/2, '@q95' = datalength(@q95)/2, '@q96' = datalength(@q96)/2, '@q97' = datalength(@q97)/2, '@q98' = datalength(@q98)/2, '@q99' = datalength(@q99)/2, '@q100' = datalength(@q100)/2,
'@q101' = datalength(@q101)/2, '@q102' = datalength(@q102)/2, '@q103' = datalength(@q103)/2, '@q104' = datalength(@q104)/2, '@q105' = datalength(@q105)/2, '@q106' = datalength(@q106)/2, '@q107' = datalength(@q107)/2, '@q108' = datalength(@q108)/2, '@q109' = datalength(@q109)/2, '@q110' = datalength(@q110)/2,
'@q111' = datalength(@q111)/2, '@q112' = datalength(@q112)/2, '@q113' = datalength(@q113)/2, '@q114' = datalength(@q114)/2, '@q115' = datalength(@q115)/2, '@q116' = datalength(@q116)/2, '@q117' = datalength(@q117)/2, '@q118' = datalength(@q118)/2, '@q119' = datalength(@q119)/2, '@q120' = datalength(@q120)/2,
'@q121' = datalength(@q121)/2, '@q122' = datalength(@q122)/2, '@q123' = datalength(@q123)/2, '@q124' = datalength(@q124)/2, '@q125' = datalength(@q125)/2, '@q126' = datalength(@q126)/2, '@q127' = datalength(@q127)/2, '@q128' = datalength(@q128)/2, '@q129' = datalength(@q129)/2, '@q130' = datalength(@q130)/2,
'@q131' = datalength(@q131)/2, '@q132' = datalength(@q132)/2, '@q133' = datalength(@q133)/2, '@q134' = datalength(@q134)/2, '@q135' = datalength(@q135)/2, '@q136' = datalength(@q136)/2, '@q137' = datalength(@q137)/2, '@q138' = datalength(@q138)/2, '@q139' = datalength(@q139)/2, '@q140' = datalength(@q140)/2,
'@q141' = datalength(@q141)/2, '@q142' = datalength(@q142)/2, '@q143' = datalength(@q143)/2, '@q144' = datalength(@q144)/2, '@q145' = datalength(@q145)/2, '@q146' = datalength(@q146)/2, '@q147' = datalength(@q147)/2, '@q148' = datalength(@q148)/2, '@q149' = datalength(@q149)/2, '@q150' = datalength(@q150)/2,
'@q151' = datalength(@q151)/2, '@q152' = datalength(@q152)/2, '@q153' = datalength(@q153)/2, '@q154' = datalength(@q154)/2, '@q155' = datalength(@q155)/2, '@q156' = datalength(@q156)/2, '@q157' = datalength(@q157)/2, '@q158' = datalength(@q158)/2, '@q159' = datalength(@q159)/2, '@q160' = datalength(@q160)/2,
'@q161' = datalength(@q161)/2, '@q162' = datalength(@q162)/2, '@q163' = datalength(@q163)/2, '@q164' = datalength(@q164)/2, '@q165' = datalength(@q165)/2, '@q166' = datalength(@q166)/2, '@q167' = datalength(@q167)/2, '@q168' = datalength(@q168)/2, '@q169' = datalength(@q169)/2, '@q170' = datalength(@q170)/2,
'@q171' = datalength(@q171)/2, '@q172' = datalength(@q172)/2, '@q173' = datalength(@q173)/2, '@q174' = datalength(@q174)/2, '@q175' = datalength(@q175)/2, '@q176' = datalength(@q176)/2, '@q177' = datalength(@q177)/2, '@q178' = datalength(@q178)/2, '@q179' = datalength(@q179)/2, '@q180' = datalength(@q180)/2,
'@q181' = datalength(@q181)/2, '@q182' = datalength(@q182)/2, '@q183' = datalength(@q183)/2, '@q184' = datalength(@q184)/2, '@q185' = datalength(@q185)/2, '@q186' = datalength(@q186)/2, '@q187' = datalength(@q187)/2, '@q188' = datalength(@q188)/2, '@q189' = datalength(@q189)/2, '@q190' = datalength(@q190)/2,
'@q191' = datalength(@q191)/2, '@q192' = datalength(@q192)/2, '@q193' = datalength(@q193)/2, '@q194' = datalength(@q194)/2, '@q195' = datalength(@q195)/2, '@q196' = datalength(@q196)/2, '@q197' = datalength(@q197)/2, '@q198' = datalength(@q198)/2, '@q199' = datalength(@q199)/2, '@q200' = datalength(@q200)/2,
'@q201' = datalength(@q201)/2, '@q202' = datalength(@q202)/2, '@q203' = datalength(@q203)/2, '@q204' = datalength(@q204)/2, '@q205' = datalength(@q205)/2, '@q206' = datalength(@q206)/2, '@q207' = datalength(@q207)/2, '@q208' = datalength(@q208)/2, '@q209' = datalength(@q209)/2, '@q210' = datalength(@q210)/2,
'@q211' = datalength(@q211)/2, '@q212' = datalength(@q212)/2, '@q213' = datalength(@q213)/2, '@q214' = datalength(@q214)/2, '@q215' = datalength(@q215)/2, '@q216' = datalength(@q216)/2, '@q217' = datalength(@q217)/2, '@q218' = datalength(@q218)/2, '@q219' = datalength(@q219)/2, '@q220' = datalength(@q220)/2,
'@q221' = datalength(@q221)/2, '@q222' = datalength(@q222)/2, '@q223' = datalength(@q223)/2, '@q224' = datalength(@q224)/2, '@q225' = datalength(@q225)/2, '@q226' = datalength(@q226)/2, '@q227' = datalength(@q227)/2, '@q228' = datalength(@q228)/2, '@q229' = datalength(@q229)/2, '@q230' = datalength(@q230)/2,
'@q231' = datalength(@q231)/2, '@q232' = datalength(@q232)/2, '@q233' = datalength(@q233)/2, '@q234' = datalength(@q234)/2, '@q235' = datalength(@q235)/2, '@q236' = datalength(@q236)/2, '@q237' = datalength(@q237)/2, '@q238' = datalength(@q238)/2, '@q239' = datalength(@q239)/2, '@q240' = datalength(@q240)/2,
'@q241' = datalength(@q241)/2, '@q242' = datalength(@q242)/2, '@q243' = datalength(@q243)/2, '@q244' = datalength(@q244)/2, '@q245' = datalength(@q245)/2, '@q246' = datalength(@q246)/2, '@q247' = datalength(@q247)/2, '@q248' = datalength(@q248)/2, '@q249' = datalength(@q249)/2, '@q250' = datalength(@q250)/2
select @q1+@q2+@q3+@q4+@q5+@q6+@q7+@q8+@q9+@q10+
@q11+@q12+@q13+@q14+@q15+@q16+@q17+@q18+@q19+@q20+
@q21+@q22+@q23+@q24+@q25+@q26+@q27+@q28+@q29+@q30+
@q31+@q32+@q33+@q34+@q35+@q36+@q37+@q38+@q39+@q40+
@q41+@q42+@q43+@q44+@q45+@q46+@q47+@q48+@q49+@q50+
@q51+@q52+@q53+@q54+@q55+@q56+@q57+@q58+@q59+@q60+
@q61+@q62+@q63+@q64+@q65+@q66+@q67+@q68+@q69+@q70+
@q71+@q72+@q73+@q74+@q75+@q76+@q77+@q78+@q79+@q80+
@q81+@q82+@q83+@q84+@q85+@q86+@q87+@q88+@q89+@q90+
@q91+@q92+@q93+@q94+@q95+@q96+@q97+@q98+@q99+@q100+
@q101+@q102+@q103+@q104+@q105+@q106+@q107+@q108+@q109+@q110+
@q111+@q112+@q113+@q114+@q115+@q116+@q117+@q118+@q119+@q120+
@q121+@q122+@q123+@q124+@q125+@q126+@q127+@q128+@q129+@q130+
@q131+@q132+@q133+@q134+@q135+@q136+@q137+@q138+@q139+@q140+
@q141+@q142+@q143+@q144+@q145+@q146+@q147+@q148+@q149+@q150+
@q151+@q152+@q153+@q154+@q155+@q156+@q157+@q158+@q159+@q160+
@q161+@q162+@q163+@q164+@q165+@q166+@q167+@q168+@q169+@q170+
@q171+@q172+@q173+@q174+@q175+@q176+@q177+@q178+@q179+@q180+
@q181+@q182+@q183+@q184+@q185+@q186+@q187+@q188+@q189+@q190+
@q191+@q192+@q193+@q194+@q195+@q196+@q197+@q198+@q199+@q200+
@q201+@q202+@q203+@q204+@q205+@q206+@q207+@q208+@q209+@q210+
@q211+@q212+@q213+@q214+@q215+@q216+@q217+@q218+@q219+@q220+
@q221+@q222+@q223+@q224+@q225+@q226+@q227+@q228+@q229+@q230+
@q231+@q232+@q233+@q234+@q235+@q236+@q237+@q238+@q239+@q240+
@q241+@q242+@q243+@q244+@q245+@q246+@q247+@q248+@q249+@q250
end
return 0
Error:
return 1
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SQL = Scarcely Qualifies as a Language
April 30, 2009 at 7:24 am
Thank you for your hints.
I also think now that it is not good to shrink the transcation log to 1MB.
Although the recovery mode is set to simple (so only the last transactions is stored in the transaction log),
the growth of the transaction log is an expensive operation.
But, why should the transaction log size set to 10% of the database size?
Has someone experiences which is a good value in simple recovery mode?
Regards,
April 30, 2009 at 5:42 pm
There are no definitive rules on how you should size the database log file. If I may bore you with my past, back in 1993 I was sent off to Sybase to learn about Sybase System 10. As we all know Sybase and SQL Server are one and the same (poetic licence now I feel) and back then Sybase were saying that, as a good starting point, the log file should be 20% of the size of the database or twice the size of the largest table on which you might perform a single update which hits every row.
But really it depends on how your database is used. One of the databases I look after has data files totalling 1.5TB but the log file is sized at 5GB which is roughly 3%.
Mike
October 18, 2013 at 12:44 pm
Sometimes you need to change all db's from full to simple in a second environment, where log room is scarce or you are just testing.
Select 'Alter Database ['+name+'] Set Recovery simple with no_wait'
from sys.databases
where recovery_model_desc like 'full' and database_id > 4
Will generate sql's to alter your user db's ( > 4) to simple, but won't run them. You can pick and execute them with cut and paste.
-007sql
March 31, 2016 at 2:06 am
Step1: Create an VIEW for generating the offline DBs command for selected DBs
create view DBOfflineView as
select 'alter database'+ ' '+name+' '+'set offline with rollback immediate' as command
from sys.databases
where name not in ('master','model','msdb','tempdb')
--Note: Here you can as per your requirement.For alter the Dbs to simple use,
select 'alter database'+ ' '+name+' '+'set recovery simple' as command
from sys.databases
where name not in ('master','model','msdb','tempdb')
Step2: Select from the View to see the output first
select Command from DBOfflineView
Step3: Create cursor for executing the Command.
DECLARE @OfflineCommand VARCHAR(500) --set offline command
DECLARE db_cursor CURSOR FOR
select Command from DBOfflineView
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @OfflineCommand
WHILE @@FETCH_STATUS = 0
BEGIN
exec(@OfflineCommand)
FETCH NEXT FROM db_cursor INTO @OfflineCommand
END
CLOSE db_cursor
DEALLOCATE db_cursor
/*
Note:
This is how you can put the selected databases offline.
Like this,you can make your own code as per requirement.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply