November 14, 2008 at 12:30 pm
hi,
Iam using the below procedure to Backup system Databases. It is taking the backups of master,model msdb in all instances except one instance it is not taking msdb backup? could you plz suggest me what I need to check in that instance?
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[Backup]
As
Begin
DECLARE @name VARCHAR(100) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'E:\Backup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('master','model','msdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
END
November 14, 2008 at 12:52 pm
Mani (11/14/2008)
hi,Iam using the below procedure to Backup system Databases. It is taking the backups of master,model msdb in all instances except one instance it is not taking msdb backup? could you plz suggest me what I need to check in that instance?
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[Backup]
As
Begin
DECLARE @name VARCHAR(100) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'E:\Backup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('master','model','msdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
END
Don't know what to tell you about your problem, but I see something you should change.
Change this: master.dbo.sysdatabases -- this is for backward compatibility with previous versions of SQL Server
to this: master.sys.databases
November 15, 2008 at 10:40 am
What does the the log say from the instance that is failing to backup? Does the E: have enough space to backup msdb? If you have a lot of history it is possible for msdb to get large.
November 16, 2008 at 12:06 am
Hi,
Iam running the above stored procedure as job from sqlagent. The job history shows the job was success and has no errors, eventhough the msdb database not backedup!
why its showing as success eventhough msdb not backedup? the drive has enough space.
November 16, 2008 at 2:25 am
Mani (11/16/2008)
Hi,Iam running the above stored procedure as job from sqlagent. The job history shows the job was success and has no errors, eventhough the msdb database not backedup!
why its showing as success eventhough msdb not backedup? the drive has enough space.
Could it be that the database was backed up but the file was deleted? You can try and find out in when the database was backed up by querying the backupset table in MSDB. If you see that the database was not backed up, you can try to run the script manually and see which error that you are getting.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 16, 2008 at 3:16 pm
Hi,
I have checked the baclupset table and it showing as only the master and model were backed up,no history of msdb.The other instances msdb is backed up but in this particular instance it is not.
when run the job manually it taking the msdb backup too.But when I scheduled as a job it is not taking the msdb backup
plz advice me...
November 16, 2008 at 5:16 pm
Mani
You need to check the Sql Server error log not just the job log. Run the job and then run:
exec sp_readerrorlog;
I'm expecting some kind of error at the time corresponding to the job run.
Post the error message. If there isn't an error then we'll need to trace the job as it runs.
November 17, 2008 at 7:46 am
Each instance of SQL server on a server is a complete separate entity (ie. SQLServer1\Instance1, SQLServer1\Instance2). One instance can not be accessed from another instance unless it were setup as a linked server. You need to run this backup on each instance of SQL server.
November 17, 2008 at 7:52 am
Joe
I don't believe he is trying what you are describing. The way I'm reading the post Mani has common code for a common job deployed to multiple instances. He is getting a different result on one particular instance.
Mani, please update if this is not the configuration we are discussing.
David
November 17, 2008 at 10:23 am
Iam using the same procdure to backup system databases in all instances.In one instance the msdb is not backing up when the job runs at midnight. If run the job manually msdb is backing up. I did not find any clue from job history and the error log other this message below:
Database backed up. Database: master, creation date(time): 2008/11/12(23:09:05), pages dumped: 371, first LSN: 237:480:37, last LSN: 237:504:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'Z:\Backup\master_20081117.BAK'}). This is an informational message only. No user action is required.
Database backed up. Database: model, creation date(time): 2003/04/08(09:13:36), pages dumped: 163, first LSN: 24:416:37, last LSN: 24:440:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'Z:\Backup\model_20081117.BAK'}). This is an informational message only. No user action is required.
Its a production instance so little worried..could you plz suggest me some other procedure to backup system databases
November 17, 2008 at 11:16 am
The Quick fix just add a step that does a backup command:
backup database msdb to disk='your_file_path_here' with init;
To continue troubleshooting the code I would added print statements to see what it is doing and turn on the job output file or run a trace while the job is running.
Which do you have more experience doing?
David
November 17, 2008 at 11:20 am
Sounds like the SELECT is not returning the database from sysdatabase since there are no errors and the backup attempt was never made. Run the query "SELECT name FROM master.dbo.sysdatabases " and see if MSDB shows up in the resultset.
November 17, 2008 at 11:34 am
If msdb isn't in sysdatabases, Sql Agent wouldn't run and the job wouldn't fire and allow the other 2 system databases to be backed up.
The problem is mostly like in the code and needs to be troubleshot from the perspective. If a system database is missing we are talking about a corrupt instance. That would manifest itself in a much more serious manner.
David
November 17, 2008 at 11:43 am
I didn't say it wasn't in sysdatabases, I said the query may not be returning it. It could be an issue with the code or something different about the entry in sysdatabases that the code is not returning it.
November 17, 2008 at 11:47 am
And my suggestion still stands as well, change master.dbo.sysdatabases to sys.databases.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply