User profile for swjohnson

Yes it is in the Administrators group. / comments

Yes it is in the Administrators group.

0 votes

Ok, but this is a stand alone server and there are no linked servers nor aliases with this one--it;s about a simple of a SQL Server as they come. However, i will try the update you spoke about and let you know. Thanks SJ / comments

Ok, but this is a stand alone server and there are no linked servers nor aliases with this one--it;s about a simple of a SQL Server as they come. However, i will try the update you spoke about and ...

0 votes

Brad, Yes, it would be easier if it was in the interface but I too have over 300 databases on my primary SQL Server (several others have over 100 databases each) and I didn't want a custom procedure for each one. Also, I do a full backup every night for my primary server and it only takes a few hours. The script I use is generic and I use it on all my servers for their backups. You should be able to modify it fairly easily for your use. With that said would it make more sense to do a full backup once a week and then differential backups every night if you don't want to impact performance very much? My script that is on each server in the Master DB and called from a scheduled job: CREATE PROCEDURE usp_SQLBackup @BackupPath varchar(255), @CopyToPath varchar(255), @SvrName varchar(50), @RetainDays tinyint=7, @CompressLvl tinyint=3, @SendCompletionMail bit=0, @NotifyAddr varchar(100)='sqlserverdb@xxxxxxx.com' AS /********************************************************* This stored procedure gets all of the databases and generates the necessary text for executing Red Gate's SQL Backup utillity v3.x. @BackupPath is the path to put the backup files @CopyToPath is the path to copy backup files after done @SvrName name of the server this should report @RetainDays is the number of days to retain (Max 255) @CompresssLvl 1=Default, 2=High, 3=Max @SendCompletionMail 0=Don't Send, 1=Send notice this uses EasyMail SMTP components @NotifyAddr is the email address to notify HISTORY: ***********************************************************/ --declare Variables DECLARE @dbName SYSNAME DECLARE @MyString VARCHAR(4000) DECLARE @Result VARCHAR(4000) DECLARE @BeginTime DATETIME DECLARE @EndTime DATETIME DECLARE @failFlag bit DECLARE @failCount int SET NOCOUNT ON --Create temporary tables for use below CREATE TABLE #ResultDatabase (DBName sysname, BUResult varchar(4000),BeginTime datetime,EndTime datetime) CREATE TABLE #ResultString (message varchar (4000)) --Initialize variables SET @failFlag = 0 SET @failCount=0 SELECT @BeginTime=Getdate() --Open Cursor of databases to backup DECLARE c1 CURSOR FOR SELECT [Name] FROM master.dbo.sysdatabases WHERE [Name] NOT IN ('pubs', 'tempdb', 'Northwind') ORDER BY [Name] OPEN c1 FETCH NEXT FROM c1 INTO @dbName WHILE @@fetch_status <> -1 BEGIN --Generate string for BackupSQL SELECT @MyString= '-SQL "BACKUP DATABASE [' + @dbName + '] TO DISK = ''' + @BackupPath + '<AUTO>'' WITH NAME = ''Database (' + @dbName + '), ' + cast(getdate() as varchar(30)) + ''', DESCRIPTION = ''Full Backup on ' + cast(getdate() as varchar(30)) + ' Database: ' + @dbName + ' Instance: (local) Server: ' + @SvrName + ''', PASSWORD = ''mypasswordNOT'', VERIFY, ERASEFILES = ' + cast(@RetainDays as varchar(3)) + ', MAILTO_ONERROR = ''' + @NotifyAddr + ''', COMPRESSION = ' + cast(@CompressLvl as varchar(1)) + '" -E' SELECT @MyString --Execute backup of specified database INSERT INTO #ResultString EXEC master..sqlbackup @Mystring --Check results of backup SELECT @EndTime=GetDate() SELECT @failCount = count(*) FROM #ResultString WHERE patindex('%error%', message) > 0 --If successful insert sucess entry, otherwise set failFlag and note in table IF @failCount > 0 BEGIN INSERT INTO #ResultDatabase (DBName, BUResult,BeginTime,EndTime) SELECT @DBName,message,@BeginTime,@EndTime FROM #resultstring SET @failFlag = 1 END ELSE BEGIN INSERT INTO #ResultDatabase (DBName, BUResult,BeginTime,EndTime) SELECT @DBName,'Backup Successful',@BeginTime,@EndTime FROM #resultstring END --Cleanup and get next record DELETE FROM #ResultString FETCH NEXT FROM c1 INTO @dbname END CLOSE c1 DEALLOCATE c1 --Do notifications If @SendCompletionMail=1 BEGIN EXEC Spammer.dbo.usp_InsertSpam2 'SJ','sqlserverdb@xxxxxxxx.com',@SvrName,'sqlserveragent@xxxxxxxx.com',@SvrName,'Full Backup done processing please check',@ProjectID =0,@DateAdded ='1/1/1900' END --Clean up temporary tables DROP TABLE #ResultDatabase DROP TABLE #ResultString GO Good luck! / comments

Brad, Yes, it would be easier if it was in the interface but I too have over 300 databases on my primary SQL Server (several others have over 100 databases each) and I didn't want a custom procedur...

0 votes

Peter, Correct, we are not able to do backups/restores from the two SP2 instances. The SP4 instance works just fine. So as I am understanding you are saying this is an issues for the way SQL 2005 SP2 handled calls to the newer DLL. Correct? If so, then my only plan is to upgrade the other instances to SP4. Thanks! SJ / comments

Peter, Correct, we are not able to do backups/restores from the two SP2 instances. The SP4 instance works just fine. So as I am understanding you are saying this is an issues for the way SQL 2005 ...

0 votes

Peter, Yes both the sqlvdi.dll are the same version 2000.85.2312.0 Thanks SJ / comments

Peter, Yes both the sqlvdi.dll are the same version 2000.85.2312.0 Thanks SJ

0 votes

Chris, Thanks, this is the result that I get back from the 3 instances. instance VAS (KB) // Max Free (KB) vsql30 (sp2) 8574413888 // 6703778112 JRA (sp4) 8581239204 // 6710851968 svydata6 (sp2) 8581229524 // 6710851968 I also see this in the System Event Log. SQLVDI: Loc=SVDS::Open. Desc=BADMEM. ErrorCode=(-1). Process=1548. Thread=2112. Server. Instance=SVYDEMO. VD=Global\SQLBACKUP_6886F8B0-F810-40AF-B050-48DBED89BA60_SQLVDIMemoryName_0. SQLVDI: Loc=SignalAbort. Desc=Client initiates abort. ErrorCode=(0). Process=956. Thread=1132. Client. Instance=SVYDEMO. VD=Global\SQLBACKUP_5F1F3589-B066-401D-9AA3-620C22C9CC32_SQLVDIMemoryName_0. SQLVDI: Loc=CVDS::Close. Desc=Abnormal termination state. ErrorCode=(0). Process=956. Thread=1132. Client. Instance=SVYDEMO. VD=Global\SQLBACKUP_5F1F3589-B066-401D-9AA3-620C22C9CC32_SQLVDIMemoryName_0. So I am having my tech team run some memory diagnostics on the server . Thanks! SJ / comments

Chris, Thanks, this is the result that I get back from the 3 instances. instance VAS (KB) // Max Free (KB) vsql30 (sp2) 8574413888 // 6703778112 JRA (sp4) 8581239204 // 67...

0 votes

Chris, The server has 8GB and each instance has max memory set to 2048GB. All are Standard Edition SQL Server 2005 x64 (2 at SP2 and 1 at SP4) and that should leave about 2GB for SQL and the other memory pools. NOTE: one of the instances is not used except to replay log files. / comments

Chris, The server has 8GB and each instance has max memory set to 2048GB. All are Standard Edition SQL Server 2005 x64 (2 at SP2 and 1 at SP4) and that should leave about 2GB for SQL and the other...

0 votes

What is the impact/benefit of boosting that setting? SJ / comments

What is the impact/benefit of boosting that setting? SJ

0 votes

Yes, it was probably the number of entries in all the msdb.backupmediaset, msdb.backupmediafamily, msdb.backupset, msdb.backupfile tables. I started to run the sp_delete_backuphistory Microsoft supplied stored procedure but I learned that it uses a cursor and that it was going to take about forever to delete all but 5 days of history in my msdb database (my db had about 3.8 million records), I found a neat little stored procedure that does it in a set based format that you use as a stored procedure and run through a job and keep the maintenance up-to-date. http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1704.aspx I know that your extended stored procedure can delete files after x number of days (erasefiles=x). Does it also clean up those entries in the tables above? / comments

Yes, it was probably the number of entries in all the msdb.backupmediaset, msdb.backupmediafamily, msdb.backupset, msdb.backupfile tables. I started to run the sp_delete_backuphistory Microsoft sup...

0 votes

Could be...the msdb.backupfile, msdb.backupmediafamily, msdb.backupmediaset databases are rather huge (about 3.6 million records each). / comments

Could be...the msdb.backupfile, msdb.backupmediafamily, msdb.backupmediaset databases are rather huge (about 3.6 million records each).

0 votes

How can we help you today?

swjohnson

Comments