Post reply

Backup Possessive or Performance hog ???

  • June 29, 2004 at 12:18 pm

    #86021

    Will it hurt if I run a backup of a database being used (Not in single user mode)? 

    Backup Format?

    sqlmaint -D Database  -WriteHistory  -VrfyBackup -BkUpMedia DISK -BkUpDB  "Drive:\Directory"  -CrBkSubDir -BkExt "BAK" -DelBkUps 3days

  • June 29, 2004 at 1:06 pm

    #512523

    A database can be backed up while in use.  no problem.

    Steve

  • June 29, 2004 at 7:49 pm

    #512559

    Hi,

    I have setup a backup (My 1st ever), and it has issues running. Can you tell me why?

    Note:  The drive is not on the local server ... I have mapped to it.

    Backup Code in DTS as CmdExec:

    sqlmaint -D DatabaseName  -WriteHistory  -VrfyBackup -BkUpMedia DISK -BkUpDB  "I:\Directory"  -CrBkSubDir -BkExt "BAK" -DelBkUps 3days

    Error:

    Executed as user: Domain\Owner. Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760 

    Logged on to SQL Server 'DatabaseName-WHSE-DB'   as 'Domain\Owner' (trusted)   

    Starting maintenance of database 'DatabaseName' on 6/29/2004 9:29:34 PM   

    Could not create a sub directory for database DatabaseName.

    The parent directory will be used instead. 

    [1] Database DatabaseName: Database Backup...       

    Destination: [I:\Directory\DatabaseName_db_200406292129.BAK]   

    [Microsoft SQL-DMO (ODBC SQLState: 42000)]

    Error 3201: [Microsoft][ODBC SQL Server Driver][SQL Server]

    Cannot open backup device 'I:\Directory\DatabaseName_db_200406292129.BAK'.

    Device error or device off-line. See the SQL Server error log for more details. 

    [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.   

    End of maintenance for database 'DatabaseName' on 6/29/2004 9:29:34 PM   

    SQLMAINT.EXE Process Exit Code: 1 (Failed).  Process Exit Code 1.  The step failed.

  • June 30, 2004 at 8:09 am

    #512671

    Its most likely a permissions issue.  The account that SQL is running under doesn't have rights to your mapped drive.  If at all possible, backup your databases to a local drive and then move them to your network share.

    Steve

  • June 30, 2004 at 8:13 am

    #512673

    SQL is very intolerant of any network delays as well. That's not your error here, but I'd recommend a local drive as well.

  • June 30, 2004 at 10:01 am

    #512702

    I agree with all that backing up to a network drive is not a good idea.

    But if you still need to back up, try giving a UNC path and hope that works.

  • June 30, 2004 at 11:20 am

    #512717

    When backing up to a network drive, you need to remember/check the following:

    1. Using the command tool, PING the system that you will be backing up to.....this will ensure you can actually 'reach' the system.

      1a. PING by name first, if that doesn't work then PING by IP. DNS name resolution may not be set up. If it isn't then using the name in the path won't work, you'll need to use the IP address.

    2. Backup jobs run under the login account that the SQL Server Agent uses.  Make sure that account is a DOMAIN account.

    3. Make sure that account has permissions to access the system and drive that you are going to backup to.

    4. Use the FULL UNC path in the backup command.

    -SQLBill

  • June 30, 2004 at 11:23 am

    #512719

    One other thing usually forgotten:

    The full UNC path includes two slashes (//) before the server name.

    -SQLBill

  • June 30, 2004 at 12:13 pm

    #512737

    The full command I am using is

    sqlmaint -D Database  -WriteHistory  -VrfyBackup -BkUpMedia DISK -BkUpDB  "I:\DIRECTORY"  -CrBkSubDir -BkExt "BAK" -DelBkUps 3days

    I can see the drive in Windows Explorer ... where does the // go?  Is the command case sensative?  What is the difference between BACKUP command and this SQLMAINT?

    I'm Melllttttingggggg ,  Thanks for helpppppingggg

     

  • June 30, 2004 at 12:16 pm

    #512739

    No room on local drives

    What is UNC path ?

  • June 30, 2004 at 12:16 pm

    #512738

    F.Y.I.  UNC paths start with \\ rather than //



    A.J.
    DBA with an attitude

  • June 30, 2004 at 12:23 pm

    #512742

    Current Error:

    BackupDiskFile::CreateMedia: Backup device 'I:\DIRECTORY\DoD_db_200406301343.BAK' failed to create. Operating system error = 3(The system cannot find the path specified.).

    Bring back any memories?

  • June 30, 2004 at 12:29 pm

    #512745

    Is I:\ your mapped drive?

    If so, did you map it under YOUR NT Logonid?

    If so, SQL Agent wouldn't know that the drive mapping exists.  Determine what Logonid SQL Agent is running under (and make sure that it is a domain id), and map your drive from that ID, or use the UNC path.  Better still, backup to a local drive.

    Steve

  • June 30, 2004 at 12:33 pm

    #512746

    Sorry, just saw your "no room on local drives" message.

    UNC:

    \\servername\c$\Directory\filename

    where "c" = server's drive letter

    Steve

  • June 30, 2004 at 12:48 pm

    #512749

    Command in job:

    sqlmaint -D DoD  -WriteHistory  -VrfyBackup -BkUpMedia DISK -BkUpDB  "\\Name\I\DIRECTORY"  -CrBkSubDir -BkExt "BAK" -DelBkUps 3days

    Error:

    [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device '\\Name\I\DIRECTORY\Database_200406301444.BAK'. Device error or device off-line. [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.    End of maintenance for database ...

    Note:  I got name from Windows Explorer name as displayed.  

Viewing 15 posts - 1 through 15 (of 34 total)

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