Some dtabase missing from maintenance plan

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Some dtabase missing from maintenance plan

Author

Topic

dbaman
Starting Member

46 Posts

Posted - 2009-12-16 : 10:37:18

I have Ent. Ed. SQL 2005 SP2. I am trying to create a maintenance plan to backup some of my Databases (User Created). However when I use the create maintenance plan wizard in the database list some of my database are missing. Why some of the database are missing from the list.

Thanks

R

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-16 : 10:58:00

That could be a maintenance plan bug (there are many and why I don't use them), but it also could mean that those databases are in a state where they can be backed up. Can those databases be backed up normally?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

dbaman
Starting Member

46 Posts

Posted - 2009-12-16 : 11:01:18

Yes, I can backup those database individually using SMS or Script without any issue.

R

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2009-12-16 : 12:01:03

That's weird, but I'll go with what Tara said, I never use the maintenance plans because they're buggy.
If it's an option, create your own backup scripts (I think there's one on Tara's blog) and schedule them through SQL Agent, the time you're going to spend trying to make your maintenance plan to work could be spent working on a script to backup your users database.

-Igor

---
http://www.ssisdude.blogspot.com/

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2009-12-16 : 12:19:52

Our backup procedure, this backups User or System or All... Lots of switches, check it out:

CREATE PROCEDURE [dbo].[usp_dba_full_backup]
(
@path nvarchar(200) = NULL
,@dbType nvarchar(256) = 'ALL'
,@bkpType nchar(4) = 'FULL'
,@retention tinyint = 3
,@liteSpeed char(1) = 'Y')
AS
SET NOCOUNT ON
DECLARE @now char(14) --Current date and time yyyymmddhhmms
DECLARE @dbName sysname --current db being processed in the cursor
DECLARE @cmd nvarchar(4000) --Dynamically created dos command
DECLARE @result tinyint --result if the dir dos command
DECLARE @rowCnt tinyint --result of @@ROWCOUNT
DECLARE @fileName nvarchar(200) --filepath for BAK file
DECLARE @edition tinyint --edition of SQL Server
DECLARE @rc tinyint --return code
DECLARE @extension nchar(4) --backup file extension
DECLARE @version nchar(1) --one digit version number

--determine the version to make sure the correct tables and commands are used
SET @version = CONVERT(char(1), SERVERPROPERTY('ProductVersion'))

IF @version NOT IN ('8','9')
BEGIN
SET @rc = 1
GOTO EXIT_ROUTINE
END
--determine whether log shipping tables exist - Enterprise and Developer Editions
SET @edition = CONVERT(tinyint, SERVERPROPERTY('EngineEdition'))

-- validate input parameters
IF @dbType IS NOT NULL AND @dbType NOT IN ('ALL', 'System', 'User') AND @dbType NOT LIKE '-%'
BEGIN
SET @rc = 2
GOTO EXIT_ROUTINE
END

IF @dbType LIKE '-%' AND @version = '8'
BEGIN
IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE [name] = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
BEGIN
SET @rc = 3
GOTO EXIT_ROUTINE
END
END
ELSE IF @dbType LIKE '-%' AND @version = '9'
BEGIN
IF NOT EXISTS (SELECT * FROM master.sys.databases WHERE [name] = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
BEGIN
SET @rc = 3
GOTO EXIT_ROUTINE
END
END

IF @bkpType IS NOT NULL AND @bkpType NOT IN ('FULL', 'TLOG', 'DIFF')
BEGIN
SET @rc = 4
GOTO EXIT_ROUTINE
END

IF @dbType = 'System' AND @bkpType <> 'FULL'
BEGIN
SET @rc = 5
GOTO EXIT_ROUTINE
END

IF @liteSpeed IS NOT NULL AND @liteSpeed NOT IN ('N', 'Y')
BEGIN
SET @rc = 6
GOTO EXIT_ROUTINE
END

-- use the default backup directory if @path is null
IF @path IS NULL
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', @path output, 'no_output'

-- we need the backslash after the path, so add it if it wasn't provided in the input parameter
IF RIGHT(@path, 1) <> '\'
SET @path = @path + '\'

CREATE TABLE #WhichDatabase(dbName sysname NOT NULL)

-- put the databases to be backed up into temp table
IF @dbType LIKE '-%'
BEGIN
IF @bkpType = 'TLOG' AND DATABASEPROPERTYEX(SUBSTRING(@dbType, 2, DATALENGTH(@dbType)), 'RECOVERY') = 'SIMPLE'
BEGIN
SET @rc = 7
GOTO EXIT_ROUTINE
END
--separate routines according to edition and version
IF @edition = 3
BEGIN
IF @version = '8'
BEGIN
IF EXISTS (SELECT * FROM msdb.dbo.log_shipping_databases WHERE database_name = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
BEGIN
SET @rc = 8
GOTO EXIT_ROUTINE
END
END
ELSE IF @version = '9'
BEGIN
IF EXISTS (SELECT * FROM msdb.dbo.log_shipping_primary_databases WHERE primary_database = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
BEGIN
SET @rc = 8
GOTO EXIT_ROUTINE
END
END
END

IF @version = '9'
BEGIN
IF EXISTS (SELECT * FROM master.sys.databases WHERE [name] = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)) AND source_database_id IS NOT NULL)
BEGIN
SET @rc = 11
GOTO EXIT_ROUTINE
END
END
--Populate temp table with databases to be backed up
INSERT INTO #WhichDatabase(dbName)
VALUES(SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
END
ELSE IF @dbType = 'ALL'
BEGIN
IF @edition = 3 AND @version = '8'
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE
[name] NOT IN ('tempdb', 'ReportServerTempDB','pubs','Northwind','LiteSpeedLocal') AND
[name] NOT IN (SELECT database_name FROM msdb.dbo.log_shipping_databases) AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
ORDER BY [name]
ELSE IF @edition = 3 AND @version = '9'
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.sys.databases
WHERE
[name] NOT IN ('tempdb', 'ReportServerTempDB','pubs','AdventureWorks','LiteSpeedLocal') AND
[name] NOT IN (SELECT primary_database FROM msdb.dbo.log_shipping_primary_databases) AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
ORDER BY [name]
ELSE IF @version = '8'
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE
[name] NOT IN ('tempdb', 'ReportServerTempDB','pubs','Northwind','LiteSpeedLocal') AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
ORDER BY [name]
ELSE -- version is 9
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.sys.databases
WHERE
[name] NOT IN ('tempdb', 'ReportServerTempDB','pubs','AdventureWorks','LiteSpeedLocal') AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
ORDER BY [name]
END
ELSE IF @dbType = 'System'
BEGIN
IF @version = 8
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE [name] IN ('master', 'model', 'msdb')
ORDER BY [name]
ELSE
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.sys.databases
WHERE [name] IN ('master', 'model', 'msdb')
ORDER BY [name]
END
ELSE IF @dbType = 'User'
BEGIN
IF @edition = 3 AND @version = '8'
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE
[name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB','pubs','Northwind','LiteSpeedLocal') AND
[name] NOT IN (SELECT database_name FROM msdb.dbo.log_shipping_databases) AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
ORDER BY [name]
ELSE IF @edition = 3 AND @version = '9'
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.sys.databases
WHERE
[name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB','pubs','AdventureWorks','LiteSpeedLocal') AND
[name] NOT IN (SELECT primary_database FROM msdb.dbo.log_shipping_primary_databases) AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
ORDER BY [name]
ELSE IF @version = '8'
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE
[name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
ORDER BY [name]
ELSE
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.sys.databases
WHERE
[name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
ORDER BY [name]
END
ELSE -- no databases to be backed up
BEGIN
SET @rc = 9
GOTO EXIT_ROUTINE
END

-- Remove snapshots
IF @version = '9'
DELETE t
FROM #WhichDatabase t
INNER JOIN master.sys.databases d
ON t.dbName = d.[name]
WHERE d.source_database_id IS NOT NULL

-- Get the database to be backed up
SELECT TOP 1 @dbName = dbName
FROM #WhichDatabase

SET @rowCnt = @@ROWCOUNT

-- Iterate throught the temp table until no more databases need to be backed up
WHILE @rowCnt <> 0
BEGIN

IF @bkpType = 'TLOG' AND @dbType IN ('ALL', 'User') AND DATABASEPROPERTYEX(@dbName, 'RECOVERY') = 'SIMPLE'
PRINT 'Skipping transaction log backup of ' + @dbName
ELSE IF @bkpType = 'DIFF' AND @dbName IN ('master', 'model', 'msdb')
PRINT 'Skipping differential backup of ' + @dbName
ELSE
BEGIN
-- Build the dir command that will check to see if the directory exists
SET @cmd = 'dir ' + @path + REPLACE(@dbName,'','_')

-- Run the dir command, put output of xp_cmdshell into @result
EXEC @result = master..xp_cmdshell @cmd, NO_OUTPUT

-- If the directory does not exist, we must create it
IF @result <> 0
BEGIN
-- Build the mkdir command
SET @cmd = 'mkdir ' + @path + REPLACE(@dbName,'','_')

-- Create the directory
EXEC master..xp_cmdshell @cmd, NO_OUTPUT

IF @@ERROR <> 0
BEGIN
SET @rc = 10
GOTO EXIT_ROUTINE
END
END
-- Get the current date using style 120, remove all dashes, spaces, and colons
SET @now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), '-', ''), ' ', ''), ':', '')

SET @extension =
CASE
WHEN @bkpType = 'FULL' THEN '.BAK'
WHEN @bkpType = 'TLOG' THEN '.TRN'
ELSE '.DIF'
END

-- Build the backup path and file name, backup the database
IF @liteSpeed = 'N'
BEGIN
SET @fileName = @path + REPLACE(@dbName,'','_') + '\' + REPLACE(@dbName,'','_') + '_' + @now + @extension
IF @bkpType = 'FULL'
BACKUP DATABASE @dbName
TO DISK = @fileName
WITH INIT
ELSE IF @bkpType = 'DIFF'
BACKUP DATABASE @dbName
TO DISK = @fileName
WITH INIT, DIFFERENTIAL
ELSE
BACKUP LOG @dbName
TO DISK = @fileName
WITH INIT
END
ELSE
--Add a LiteSpeedIdentifier to the file
BEGIN
SET @fileName = @path + REPLACE(@dbName,'','_') + '\' + REPLACE(@dbName,'','_') + '_LS_' + @now + @extension

DECLARE @regOutput varchar(20) -- stores the output from the registry
DECLARE @numProcs INT -- stores the number of processors that the server has registered

-- Get the number of processors that the server has
EXEC master..xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SYSTEM\CurrentControlSet\Control\Session Manager\Environment\',
@value_name = 'NUMBER_OF_PROCESSORS',
@value = @regOutput OUTPUT

-- We want n - 1 threads, where n is the number of processors
SET @numProcs = CONVERT(int, @regOutput) - 1

IF @bkpType = 'FULL'
EXEC master.dbo.xp_backup_database
@database = @dbName,
@fileName = @fileName,
@threads = @numProcs,
@init = 1,
@olrmap = 1
ELSE IF @bkpType = 'DIFF'
EXEC master.dbo.xp_backup_database
@database = @dbName,
@fileName = @fileName,
@threads = @numProcs,
@init = 1,
@olrmap = 1,
@with = 'DIFFERENTIAL'
ELSE
EXEC master.dbo.xp_backup_log
@database = @dbName,
@fileName = @fileName,
@threads = @numProcs,
@init = 1
END
-- The directory exists, so let's delete files older than TWO days
IF @retention <> -1
BEGIN
-- Stores the name of the file to be deleted
DECLARE @whichFile VARCHAR(1000)

CREATE TABLE #DeleteOldFiles(DirInfo VARCHAR(7000))

-- Build the command that will list out all of the files in a directory
SELECT @cmd = 'dir ' + @path + REPLACE(@dbName,'','_') + ' /OD'

-- Run the dir command and put the results into a temp table
INSERT INTO #DeleteOldFiles
EXEC master..xp_cmdshell @cmd

-- Delete all rows from the temp table except the ones that correspond to the files to be deleted
DELETE FROM #DeleteOldFiles
WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 0 OR DirInfo LIKE '%<DIR>%' OR SUBSTRING(DirInfo, 1, 10) >= GETDATE() - @retention

-- Get the file name portion of the row that corresponds to the file to be deleted
SELECT TOP 1 @whichFile = SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))
FROM #DeleteOldFiles

SET @rowCnt = @@ROWCOUNT

-- Interate through the temp table until there are no more files to delete
WHILE @rowCnt <> 0
BEGIN
-- Build the del command
SELECT @cmd = 'del ' + @path + + REPLACE(@dbName,'','_') + '\' + @whichFile + ' /Q /F'

-- Delete the file
EXEC master..xp_cmdshell @cmd, NO_OUTPUT

-- To move to the next file, the current file name needs to be deleted from the temp table
DELETE FROM #DeleteOldFiles
WHERE SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) = @whichFile

-- Get the file name portion of the row that corresponds to the file to be deleted
SELECT TOP 1 @whichFile = SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))
FROM #DeleteOldFiles

SET @rowCnt = @@ROWCOUNT
END
DROP TABLE #DeleteOldFiles
END

END
-- To move onto the next database, the current database name needs to be deleted from the temp table
DELETE FROM #WhichDatabase
WHERE dbName = @dbName

-- Get the database to be backed up
SELECT TOP 1 @dbName = dbName
FROM #WhichDatabase

SET @rowCnt = @@ROWCOUNT

-- Let the system rest for 3 seconds before starting on the next backup
WAITFOR DELAY '00:00:03'
END

SET @rc = 0

EXIT_ROUTINE:

IF @rc <> 0
BEGIN
DECLARE @rm varchar(500)
DECLARE @error table (returnCode int PRIMARY KEY CLUSTERED, returnMessage varchar(500))

INSERT INTO @error(returnCode, returnMessage)
SELECT 0, 'Success' UNION ALL
SELECT 1, 'Version is not 2000 or 2005' UNION ALL
SELECT 2, 'Invalid option passed to @dbType' UNION ALL
SELECT 3, 'Database passed to @dbType does not exist' UNION ALL
SELECT 4, 'Invalid option passed to @bkpType' UNION ALL
SELECT 5, 'Only full backups are allowed on system databases' UNION ALL
SELECT 6, 'Invalid option passed to @liteSpeed' UNION ALL
SELECT 7, 'Can not backup tlog when using SIMPLE recovery model' UNION ALL
SELECT 8, 'Will not backup the tlog on a log shipped database' UNION ALL
SELECT 9, 'No databases to be backed up' UNION ALL
SELECT 10, 'Unable to create directory' UNION ALL
SELECT 11, 'Can not backup database snapshots'

SELECT @rm = returnMessage
FROM @error
WHERE returnCode = @rc

RAISERROR(@rm, 16, 1)
END

RETURN @rc

-Igor

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-16 : 12:28:41

Igor, I love how you said that's your backup stored procedure when actually that's an old version of mine. You should at least give credit to the author in the comment header: me.

Here's the latest version which is much improved since the one you posted: http://weblogs.sqlteam.com/tarad/archive/2009/09/08/Backup-SQL-Server-2005-and-2008-Databases.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2009-12-16 : 12:39:19

Tara,
I never said "mine", I said "our stored procedure" which embraced you - to an extent.
My apologies for not giving you the credit you deserve.

-Igor

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources