January 16, 2012 at 1:01 pm
I run a backup script that backups to a dbbackup server and it works great except running on our sharepoint server. I run the job and it says it is successful but when I look in the folders I do not see backups for most the sharepoint db's. Anyone see anything with this code that would cause this, or come across something like this. Btw when I create a maintance plan and do backup plan it still does not backup all the databases? Example name of DB that is not backed up is WSS_Content_Intrashare1_55555: Running SQL 2008 R2. (some of the DB's are in 2005 compatability mode)
Code:
DECLARE @name VARCHAR(256) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(200) -- used for file name
DECLARE @Folder VARCHAR(256)
DECLARE @servername VARCHAR(50)
SET @Servername = @@servername
SET @path = '\\DBBackup\' + @servername
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
use msdb
DECLARE @LastLSRestore datetime
SELECT @LastLSRestore = last_restored_date
FROM log_shipping_monitor_secondary
WHERE secondary_server = @@servername
DECLARE @DatetoDelete datetime, @DaysAgo datetime
SET @DaysAgo = DATEADD(dd, -2, getdate())
SELECT @DatetoDelete = CASE
WHEN @DatetoDelete < @DaysAgo THEN @DatetoDelete
ELSE @DaysAgo
END
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
Set @folder = @path + '\' + @name
EXECUTE master.dbo.xp_create_subdir @folder
SET @fileName = @folder + '\' + @name + '_' + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName WITH COMPRESSION
EXECUTE master.dbo.xp_delete_file 0,@folder,N'*',@DatetoDelete
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
January 16, 2012 at 9:40 pm
I suggest following:
1) Check the SQL Server error log. Verify the entries related to backup. Are there any errors? Notice the backup path\filename.
2) Increase the length of @FileName to 1000
3) Add PRINT @FileName inside the WHILE loop
4) Execute the script in SSMS query window (not job) and see the out put of PRINT @FileName
January 17, 2012 at 6:53 am
Is the procedure creating the empty folders for the backups? If not, it could be permissions
Have you tried running the script without the xp_delete_file line?
Training cats makes SQL Server look easy
January 18, 2012 at 10:39 am
I know EXACTLY what your backup problem is. You need to have [dbname] around the db name due to the characters in the database name. If a dash is in the db name you cannot back it up as just the db name it will fail. If you want to test that theory try backing it up without the [ ] and then with the [ ] wrapped around the db name.
You have to code it like below:
BACKUP DATABASE [SharePoint_AdminContent_5f4c9204-bb71-48ac-8404-f65a24ed0d8f]
TO DISK = 'X:\dbname.bak'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply