Backup

Hi,
What i would like to do is to use script to do the maintenance plan
instead of using the maintenance plan wizard. I create the following backup
scipts, my question is how am i able to write the result to a log file just
like what maintenance plan does?
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT Catalog_Name
FROM Information_Schema.Schemata
-- WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
WHERE [Catalog_Name] IN ('BusinessViews_Backup')
--('MASTER','MODEL','MSDB')
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'F:\Program Files\Microsoft SQL Server\MSSQL\Backup' +
@.DBName + '_Full_' +
CONVERT(NVARCHAR(16),GETDATE(),112) -- + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
-- PRINT 'Backing up database ' + @.DBName
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
-- RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
-- PRINT '--- '
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
My second question is assume i copy the above code to do the transaction_log
backup, after that, i execute the following code to each database,
CheckPoint
DBCC shrinkdatabase ('databasename', 10)
is that correct to shrink the database and log files?
Thanks so much for your time
ed>my question is how am i able to write the result to a log file just
> like what maintenance plan does?
The error messages are sent to the client application. I presume that the cl
ient application in this
case is Agent. Let Agent write an output file for you (defined in the job st
ep). You can send
additional information to the output file using RAISERROR. And you can take
this a step further by
emailing this (using SMTP if you wish) with info about success etc in email
subject line. See
http://www.karaszi.com/SQLServer/info_no_mapi.asp for some information.

> My second question is assume i copy the above code to do the transaction_l
og
> backup, after that, i execute the following code to each database,
> CheckPoint
> DBCC shrinkdatabase ('databasename', 10)
Don't shrink. See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for
more information.
Note: I'm in a hurry so I didn't have time to check your TSQL code...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:445823E6-7E16-4DB6-AA25-3C36F258AA34@.microsoft.com...
> Hi,
> What i would like to do is to use script to do the maintenance plan
> instead of using the maintenance plan wizard. I create the following back
up
> scipts, my question is how am i able to write the result to a log file jus
t
> like what maintenance plan does?
> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
> DECLARE cur_DBs CURSOR STATIC LOCAL
> FOR SELECT Catalog_Name
> FROM Information_Schema.Schemata
> -- WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB
')
> WHERE [Catalog_Name] IN ('BusinessViews_Backup')
> --('MASTER','MODEL','MSDB')
> OPEN cur_DBs
> FETCH NEXT FROM cur_DBs INTO @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.Device = N'F:\Program Files\Microsoft SQL Server\MSSQL\Backup' +
> @.DBName + '_Full_' +
> CONVERT(NVARCHAR(16),GETDATE(),112) -- + N'.BAK'
> SET @.Name = @.DBName + N' Full Backup'
> -- PRINT 'Backing up database ' + @.DBName
> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
> -- RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
> -- PRINT '--- '
> FETCH NEXT FROM cur_DBs INTO @.DBName
> END
> CLOSE cur_DBs
> DEALLOCATE cur_DBs
> My second question is assume i copy the above code to do the transaction_l
og
> backup, after that, i execute the following code to each database,
> CheckPoint
> DBCC shrinkdatabase ('databasename', 10)
> is that correct to shrink the database and log files?
> Thanks so much for your time
> ed
>