Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2003
    Location
    Malaysia
    Posts
    9

    Red face Unanswered: Backup database with SQL to backup directory

    I had write out a sql command to backup the database.

    BACKUP DATABASE NorthWind
    to disk = 'c:\NorthWind_20031113.bak'
    with name = 'NorthWind_backup'

    However, I intend to backup the database to the SQL server default backup directory, "\MSSQL7\Backup". How can I do that? Given that I don't know the SQL server were installed in C drive or D drive.

    Another question was, can I do a integrity check using the sql command on the backup copy that i had created??

    I'll be appreciate on the help that you offered.
    Thanks.

  2. #2
    Join Date
    Oct 2003
    Location
    Ireland
    Posts
    54
    I tried this and it works, ie put the backup into the MSSQL/Backup folder

    BACKUP DATABASE NorthWind
    to disk = 'NorthWind_20031113.bak'
    with name = 'NorthWind_backup'

    Trick seems to be to not to specify a location and by default it used the backup folder

    Mark

  3. #3
    Join Date
    Nov 2003
    Location
    Malaysia
    Posts
    9
    Thank for your reply. I had try on the solution you given, however, the backup copy was placed to my "winnt\system32" directory. I had try both on my own pc and to backup remotely on the database server. Both of the trial was same where the backup copy being placed to the window system directory.

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    The Holy book says :

    If a relative path name is entered for a backup to disk, the backup file is placed in the default backup directory. This directory is set during installation and stored in the BackupDirectory registry key under

    KEY_LOCAL_MACHINE\Software\Microsoft\ MSSQLServer\MSSQLServer.
    Reffering again from the Holy Book for your second question
    RESTORE VERIFYONLY
    Verifies the backup but does not restore the backup. Checks to see that the backup set is complete and that all volumes are readable. However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. If the backup is valid, Microsoft® SQL Server™ 2000 returns the message: "The backup set is valid."

    Syntax
    RESTORE VERIFYONLY
    FROM < backup_device > [ ,...n ]
    [ WITH
    [ FILE = file_number ]
    [ [ , ] { NOUNLOAD | UNLOAD } ]
    [ [ , ] LOADHISTORY ]
    [ [ , ] PASSWORD = { password | @password_variable } ]
    [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
    [ [ , ] { NOREWIND | REWIND } ]
    ]

    < backup_device > ::=
    {
    { 'logical_backup_device_name' | @logical_backup_device_name_var }
    | { DISK | TAPE } =
    { 'physical_backup_device_name' | @physical_backup_device_name_var }
    }
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Nov 2003
    Location
    Malaysia
    Posts
    9
    If a relative path name is entered for a backup to disk, the backup file is placed in the default backup directory. This directory is set during installation and stored in the BackupDirectory registry key under

    KEY_LOCAL_MACHINE\Software\Microsoft\ MSSQLServer\MSSQLServer.


    I also found the statement above at the "Holy Book", however, i can't really understand what it means by relative path name. If I had to go to registry to read for the path, then as I know I can't do it.
    Actually now I'm writing a simple program to do the database backup at the client pc and the database server only have MSDE but no enterprise manager. So, I can't check for the backup path from the registry value cause my program was running at the client pc.
    I'm really grateful for the help you offered. Thanks for the answer for the second question.


  6. #6
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Relative path means ... specifying no drive or directory .. just the filename
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  7. #7
    Join Date
    Nov 2003
    Location
    Malaysia
    Posts
    9
    I had tried it before, but the backup copy were being create at the system directory, "c:\winnt\system32" insteed of the SQL Backup folder.

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    what does the BackupDirectory registry key under

    KEY_LOCAL_MACHINE\Software\Microsoft\ MSSQLServer\MSSQLServer point to ???

    You can see it by opening regedit and navigating to the key
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  9. #9
    Join Date
    Nov 2003
    Location
    Malaysia
    Posts
    9
    I check on it already, is pointing to the sql path. D:\MSSQL7\Backup\

  10. #10
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Originally posted by coffytan
    I check on it already, is pointing to the sql path. D:\MSSQL7\Backup\
    Still backup gos to systerm Winnt ,, wierd !!!!!!!!
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •