sp_MSForEachDB problem during backups?
-
Trying to back up multiple databases to a remote server, but I get sporadic results with no errors.
Job from agent looks like it runs successfully, but it does not run completely - some db get backed up and others don't -- no errors found.
Tried it in SQL Server 2005 - Management Studio - SQL Query to see if I can catch more...
Is there anything obvious that I'm missing? How do I get this to successfully backup all of the db in the list the first time, or give some sort of error if it doesn't?
-- run 1:
EXEC sp_MSForEachDB
'if ''?'' in (''MyDatabase_11'', ''MyDatabase_13'', ''MyDatabase_9'' , ''MyDatabase_14'', ''MyDatabase_15'', ''MyDatabase_16'', ''MyDatabase_17'', ''MyDatabase_18'', ''MyDatabase_19'', ''MyDatabase_20'', ''MyDatabase_58'')
begin
declare @db_name nvarchar (100)
declare @cur_date datetime
declare @path_name nvarchar (800)
declare @full_command nvarchar (1000)
declare @converted_date nvarchar ( 11 )
set @cur_date = getdate ()
set @converted_date = cast ( @cur_date as nvarchar (11) )
set @converted_date = replace ( @converted_date, '' '', ''_'')
set @db_name = ''?''
set @path_name = ''\\MyServer\MyDrive\microsoft-sql-server-data-files\MSSQL.1\MSSQL\Backup\'' + @db_name
+ ''_FULL_DB_'' + @converted_date + ''.bak''
set @full_command = ''backup database '' + @db_name + '' to disk = ''''''
+ @path_name + '''''' ''
execute sp_executesql @full_command
print @full_command
end
'
----------------------------
run 1 results
-- sql server indicated that the sql ran successfully, but clearly not all of the db were backed up --
-- but no errors were thrown
(the db names are correct, and there is ample space on the destination drive)
Processed 6102312 pages for database 'MyDatabase_14', file 'MyDatabase_14' on file 1.
Processed 2 pages for database 'MyDatabase_14', file 'MyDatabase_14_log' on file 1.
BACKUP DATABASE successfully processed 6102314 pages in 2686.477 seconds (18.608 MB/sec).
backup database MyDatabase_14 to disk = '\\MyServer\MyDrive\microsoft-sql-server-data-files\MSSQL.1\MSSQL\Backup\MyDatabase_14_FULL_DB_Apr_24_2007.bak'
Processed 5224 pages for database 'MyDatabase_58', file 'MyDatabase_58_Data' on file 1.
Processed 1 pages for database 'MyDatabase_58', file 'MyDatabase_58_Log' on file 1.
BACKUP DATABASE successfully processed 5225 pages in 2.198 seconds (19.473 MB/sec).
backup database MyDatabase_58 to disk = '\\MyServer\MyDrive\microsoft-sql-server-data-files\MSSQL.1\MSSQL\Backup\MyDatabase_58_FULL_DB_Apr_24_2007.bak'
Processed 5177400 pages for database 'MyDatabase_9', file 'MyDatabase_9' on file 1.
Processed 0 pages for database 'MyDatabase_9', file 'MyDatabase_9_log' on file 1.
Processed 397 pages for database 'MyDatabase_9', file 'MyDatabase_9_log2' on file 1.
BACKUP DATABASE successfully processed 5177797 pages in 2027.301 seconds (20.922 MB/sec).
backup database MyDatabase_9 to disk = '\\MyServer\MyDrive\microsoft-sql-server-data-files\MSSQL.1\MSSQL\Backup\MyDatabase_9_FULL_DB_Apr_24_2007.bak'
-----------------------------
-- run 2:
edited the sql to not backup the db that were successfully backed up, and started again:
EXEC sp_MSForEachDB
'if ''?'' in (''MyDatabase_11'', ''MyDatabase_13'', ''MyDatabase_15'', ''MyDatabase_16'', ''MyDatabase_17'', ''MyDatabase_18'', ''MyDatabase_19'', ''MyDatabase_20'')
begin
declare @db_name nvarchar (100)
declare @cur_date datetime
declare @path_name nvarchar (800)
declare @full_command nvarchar (1000)
declare @converted_date nvarchar ( 11 )
set @cur_date = getdate ()
set @converted_date = cast ( @cur_date as nvarchar (11) )
set @converted_date = replace ( @converted_date, '' '', ''_'')
set @db_name = ''?''
set @path_name = ''\\MyServer\MyDrive\microsoft-sql-server-data-files\MSSQL.1\MSSQL\Backup\'' + @db_name
+ ''_FULL_DB_'' + @converted_date + ''.bak''
set @full_command = ''backup database '' + @db_name + '' to disk = ''''''
+ @path_name + '''''' ''
execute sp_executesql @full_command
print @full_command
end
'
Processed 266664 pages for database 'MyDatabase_16', file 'MyDatabase_16' on file 1.
Processed 1 pages for database 'MyDatabase_16', file 'MyDatabase_16_log' on file 1.
BACKUP DATABASE successfully processed 266665 pages in 69.765 seconds (31.312 MB/sec).
backup database MyDatabase_16 to disk = '\\MyServer\MyDrive\microsoft-sql-server-data-files\MSSQL.1\MSSQL\Backup\MyDatabase_16_FULL_DB_Apr_24_2007.bak'
Processed 6873312 pages for database 'MyDatabase_11', file 'MyDatabase_11' on file 1.
Processed 9 pages for database 'MyDatabase_11', file 'MyDatabase_11_log' on file 1.
BACKUP DATABASE successfully processed 6873321 pages in 3109.459 seconds (18.108 MB/sec).
backup database MyDatabase_11 to disk = '\\MyServer\MyDrive\microsoft-sql-server-data-files\MSSQL.1\MSSQL\Backup\MyDatabase_11_FULL_DB_Apr_24_2007.bak'
again some, but not all of the db were backed up, but I saw no errors.
-------
Repeated this process 2 more times until all of the db were backed up.
-
The sp_MSforeachdb procedure does some bizarre things with global cursors, my guess would be there is some interaction between it and the sp_executesql or the backup commands. Rather than holding a cursor open for hours, you could make your own list of database names in a table variable and script your own WHILE loop.
DECLARE
@dbs TABLE (
ID INT IDENTITY NOT NULL,
dbname sysname NOT NULL)
DECLARE @dbcount INT, @full_command NVARCHAR(1000)
INSERT INTO @dbs (dbname)
SELECT name FROM sys.databases
WHERE name <> 'tempdb' AND DATABASEPROPERTYEX(name, 'Status')='ONLINE' AND DATABASEPROPERTYEX(name, 'Updateability')='READ_WRITE'
ORDER BY name DESC
SET @dbcount = @@ROWCOUNT
WHILE @dbcount > 0
BEGIN
SELECT @dbcount = @dbcount - 1, @full_command = REPLACE(REPLACE(
'BACKUP DATABASE [<db>] TO DISK=''\\MyServer\MyDrive\microsoft-sql-server-data-files\MSSQL.1\MSSQL\Backup\<db>_FULL_DB_<ts>.bak''',
'<db>', name ),
'<ts>', REPLACE(CONVERT(CHAR(11),GETDATE(),109),' ','_'))
FROM @dbs WHERE ID = @dbcount
EXECUTE sp_executesql @full_command
PRINT @full_command
END
-
That worked beautifully -- thank you.
Once again, posts here have saved me.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply