Backup Possessive or Performance hog ???

Question

Backup Possessive or Performance hog ???

Viewing 5 posts - 31 through 34 (of 34 total)

You must be logged in to reply to this topic. Login to reply

HelpMeInOhio Hall of Fame Points: 3945 More actions · Answer 1

In my job the 'Target Local Server' is checked. Is that a problem?

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 2

I wrote the following procedure when the SQL Server agent on the server broke. I set this as a scheduled task to run from my PC with the query analyzer.

It will backup every database not in the where clause of the @DATABASENAME variable to whatever path is in the @dbdirectory variable.

Take a stab at it. If you need help send me a Private Message with your phone #. I should have some time today to talk you through some of this.

-----------------------
DROP PROCEDURE CP_Backup_DB
GO
CREATE PROCEDURE CP_Backup_DB AS 
DECLARE @CurrDtTm varchar(20)
DECLARE @dbIdNum int
DECLARE @MaxId int
DECLARE @DATABASENAME varchar(50)
DECLARE @dbDirectory varchar(100)
DECLARE @CommandLine varchar(200)
SET @MaxId = (SELECT MAX(dbid) FROM MASTER.dbo.sysdatabases)
PRINT @MaxId
SET @dbIdNum = 1 
PRINT @dbIdNum
SET @CurrDtTm = (select cast(CONVERT(char(8), GETDATE(), 112) + 
    left( convert(char(8),CURRENT_TIMESTAMP, 114)  ,2 ) +
    substring( convert(char(8),CURRENT_TIMESTAMP, 114) ,4 ,2 ) +
    right( convert(char(8),CURRENT_TIMESTAMP, 114)  ,2 ) as varchar(14) ))
print @CurrDtTm 
WHILE @dbIdNum <= @MaxId 
    begin    
    PRINT @dbIdNum 
    SET @DATABASENAME = ltrim(rtrim(cast((
                    SELECT name + ' '
                    FROM MASTER.dbo.sysdatabases 
                    WHERE dbid = @dbIdNum
                    and name not in ('tempdb', 'pubs' )
                    ) as varchar(30))))
    PRINT @DATABASENAME
    SET @dbdirectory = 'D:\MSSQL7\BACKUP\' + @DATABASENAME + '\' + @DATABASENAME + '_DB_' +
        @CurrDtTm + '.BAK'
    PRINT @dbdirectory
    SET @CommandLine = (SELECT   'BACKUP DATABASE [' + @DATABASENAME +  '] TO DISK = '  + CHAR(39) +
                @dbdirectory + CHAR(39) + ' WITH RETAINDAYS = 4 ')
    PRINT @CommandLine

   IF (@DATABASENAME '') 
    BEGIN
        EXEC (@CommandLine)
            PRINT @CommandLine
            SET @dbIdNum = @dbIdNum + 1
    END
    ELSE 
        BEGIN
            SET @dbIdNum = @dbIdNum + 1
        END
    end
GO
EXEC CP_Backup_DB
---------------------------

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.

HelpMeInOhio Hall of Fame Points: 3945 More actions · Answer 3

Thanks to all that helped:

What threw me was the advice:

UNC: \\servername\c$\Directory\filename where "c" = server's drive letter

Solution:

1. Took c$ out of path.

2. We have a large DB so I moved to :

BACKUP DATABASE DB1

TO DISK='\\whse\Drive_I\Stuff\BACKUP\DB1_1_of_1.BAK'

and

BACKUP DATABASE DB2

TO DISK='\\whse\Drive_I\Stuff\BACKUP\DB2_1_of_5.BAK',

DISK='\\whse\Drive_I\Stuff\BACKUP\DB2_2_of_5.BAK',

DISK='\\whse\Dirve_I\Stuff\BACKUP\DB2_3_of_5.BAK',

DISK='\\whse\Drive_I\Stuff\BACKUP\DB2_4_of_5.BAK',

DISK='\\whse\Drive_I\Stuff\BACKUP\DB2_5_of_5.BAK'

I can backup now but I have two last questions. I ran the backup in SQL Qery analyzer:

Question 1: As TSQL in a job do I need a GO after each BACKUP?

Question 2: Before doing the second backup the system made me go into single user mode. Any idea why?

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 4

This is the UNC:

\\whse\Drive_I\Stuff\BACKUP\DB2_1_of_5.BAK

The "Drive_I" is the share. The drive letter refers to the fact that the WinXX creates a default share of %drive letter%$ such as "C$" by default. What the "$" dollar sign does is it tells the OS that it is a hidden share. You can test it on your own workstation if you have sharing enabled.

I took some of your response and came up with this procedure:

-----------------
CREATE PROCEDURE CP_Backup_DB AS 
DECLARE @CurrDtTm varchar(20)
DECLARE @dbDirectory varchar(100)
DECLARE @CommandLine varchar(1000)

SET @CurrDtTm = (select cast(CONVERT(char(8), GETDATE(), 112) + 
    left( convert(char(8),CURRENT_TIMESTAMP, 114)  ,2 ) +
    substring( convert(char(8),CURRENT_TIMESTAMP, 114) ,4 ,2 ) +
    right( convert(char(8),CURRENT_TIMESTAMP, 114)  ,2 ) as varchar(14) ))
print @CurrDtTm 

SET @dbdirectory = '\\whse\Drive_I\Stuff\BACKUP\DB1_1_of_1_' + @CurrDtTm + '.BAK,'

SET @CommandLine = (SELECT   'BACKUP DATABASE DB1 TO DISK = '  + CHAR(39) +
                @dbdirectory + CHAR(39) + ' WITH RETAINDAYS = 2 ')
PRINT @CommandLine
EXEC (@CommandLine)

SET @CommandLine = (SELECT   'BACKUP DATABASE DB2 ' +
        'TO DISK = '  + CHAR(39) + '\\whse\Drive_I\Stuff\BACKUP\DB2_1_of_5_' + @CurrDtTm + '.BAK,' + CHAR(39) + 
        'TO DISK = '  + CHAR(39) + '\\whse\Drive_I\Stuff\BACKUP\DB2_2_of_5_' + @CurrDtTm + '.BAK,' + CHAR(39) + 
        'TO DISK = '  + CHAR(39) + '\\whse\Drive_I\Stuff\BACKUP\DB2_3_of_5_' + @CurrDtTm + '.BAK,' + CHAR(39) + 
        'TO DISK = '  + CHAR(39) + '\\whse\Drive_I\Stuff\BACKUP\DB2_4_of_5_' + @CurrDtTm + '.BAK,' + CHAR(39) + 
        'TO DISK = '  + CHAR(39) + '\\whse\Drive_I\Stuff\BACKUP\DB2_5_of_5_' + @CurrDtTm + '.BAK,' + CHAR(39) +
        ' WITH RETAINDAYS = 2 ')
PRINT @CommandLine
EXEC (@CommandLine)
GO
------------------------------

Three reasons I suggest the stored procedure:

1) It will put a date time stamp on the backups and make it easier to check if ran.

2) The old files will not be overwritten

3) To fire it you can run it from the command line (or a scheduled task) by doing something like this:

---------------------

C:\Program Files\Microsoft SQL Server\80\Tools\Binn\isql.exe -S %servername% -U %userid% -P %password% -Q "exec CP_Backup_DB"

---------------------

Your path may be different than mine. Just find the isql.exe on your drive. For all the command line options fire a DOS prompt, get to the right directory and type "isql /?".

Not sure on the single user-mode unless you had a checkDB in the script.

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.

HelpMeInOhio Hall of Fame Points: 3945 More actions · Answer 5

Thanks. I'll work on getting the stored procedure in place. We do have a DB (Data Warehouse) so large that we can't keep more than one online.