Backup script skipping database

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)

Backup script skipping database

Author

Topic

5pirates
Starting Member

4 Posts

Posted - 2010-10-05 : 10:41:10

I have a basic backup script (below) that keeps skipping one of the databases. The select from sys.databases returns the record but does not backup. There are no errors in the log. Does anyone have any ideas?

DECLARE @dbname sysname
DECLARE @dbid int
DECLARE @backupsql varchar(500)

DECLARE db_cursor CURSOR FOR
SELECT name, database_id FROM sys.databases
where database_id > 4
ORDER BY database_id

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname, @dbid

WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@dbid > 4)
BEGIN
SELECT @backupsql = 'BACKUP DATABASE ' + @dbname + ' TO DISK = ' + CHAR(39) + '\\serverIP\sqlbackups$\servername\' + @dbname + '.BAK' + CHAR(39) + ' WITH INIT'
EXEC(@backupsql)
END
FETCH NEXT FROM db_cursor INTO @dbname, @dbid
END

CLOSE db_cursor
DEALLOCATE db_cursor
RETURN

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-05 : 10:53:54

What is the name of the skipped database?

No, you're never too old to Yak'n'Roll if you're too young to die.

5pirates
Starting Member

4 Posts

Posted - 2010-10-05 : 11:16:38

The database name is SLX_IMPORTS and the databaseid is 8

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-05 : 11:35:09

if you do a print @backupsql instead of EXEC(@backupsql), is there a statement shown for that database?
OR
Are you testing on a different server where that database is present but not on the server where the cursor is running?

No, you're never too old to Yak'n'Roll if you're too young to die.

5pirates
Starting Member

4 Posts

Posted - 2010-10-05 : 11:47:41

I tested on the same server and the 'print' shows the statement. If I run the procedure in QA all databases get backed up. It's just when the procedure is executed from a job.

BACKUP DATABASE SLX_IMPORTS TO DISK = '\\serverIP\sqlbackups$\servername\SLX_IMPORTS.BAK' WITH INIT

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-05 : 12:10:14

does this work?

DECLARE @dbname sysname
DECLARE @dbid int
DECLARE @backupsql varchar(500)

DECLARE db_cursor CURSOR FOR 
SELECT name, database_id FROM sys.databases
where database_id > 4 
ORDER BY database_id

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname, @dbid

WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@dbid > 4)
BEGIN
	SELECT @backupsql = 'BACKUP DATABASE [' + @dbname + '] TO DISK = ' + CHAR(39) + '\\serverIP\sqlbackups$\servername\' + @dbname + '.BAK' + CHAR(39) + ' WITH INIT;
	GO'
	print(@backupsql)
END
FETCH NEXT FROM db_cursor INTO @dbname, @dbid
END

CLOSE db_cursor
DEALLOCATE db_cursor
RETURN

5pirates
Starting Member

4 Posts

Posted - 2010-10-05 : 12:24:14

Ran successfully on the single database. I will try this tonight in the job. Thanks for the help.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-05 : 12:29:46

Welcome

let us know how it turns out

don't forget to change my PRINT to EXEC b4 you plug it into your job, else you won't have backups tonight!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-05 : 13:28:38

If russell's change works, I'll be shocked. The skipped database has no special characters in the name, so the square brackets shouldn't be required. GO and a semi colon aren't needed either as each EXEC will run in its own batch.

I'm ready to be proved wrong, but I'll be shocked.

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

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-05 : 13:30:16

If you want a robust custom backup script, then here's what I use and wrote: http://weblogs.sqlteam.com/tarad/archive/2009/12/29/Backup-SQL-Server-2005-and-2008-DatabasesAgain.aspx

It's actually used around the world by many different companies. I'm using it on literally hundreds of SQL Servers at my work.

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

Subscribe to my blog

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-05 : 13:46:43

quote:
Originally posted by tkizer

If russell's change works, I'll be shocked.

me too lol. actually, i was thinking that maybe it's a db just b4 the one in question that might be stopping the script. since i can't see 'em...it was a shot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources