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.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|||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.|||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 }
}|||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.

:D|||Relative path means ... specifying no drive or directory .. just the filename|||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.|||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|||I check on it already, is pointing to the sql path. D:\MSSQL7\Backup\|||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 !!!!!!!!

backup across network

SQL Server newbie question ~ how can I backup a database to a directory in mounted network drive. when I attempt, the server tells me the drive/device is inaccessible. but from the command line, no problem accessing the directory. i have done research on the web and MSN site -- and the info states that it may have to do with permissions. if I can see the drive from command line -- can SQL Server "NOT" see it also? please let me know where I'm falling off the brain wagon...tiaMaybe I'm missing the point, but I've been told not to specify logical drive letters (like D:), rather to use server names, for instance \\server\ etc.|||Before a couple of days I have to solve the same problem by massive google search on the web :)
The problem is that the account on which the SQL server runs hasn't got privileges to see the network. By default, when installed SQL server is executing under system account 'LocalSystem' which hasn't got rights to view the network. Even if you try to backup the database from Enterprise Manager and select a file the dialog just shows local hard drives without network.
So to make backup accross network the user that SQL Server runs onto has to be changed to someone who has the rights for seeing network - fixed user 'Administrator' or any account that access the computer with administrator rights. In Enterprise Manager right-click on SQL server and select properties from the context menu. In the Properties window go to Security tab page, and select radio button 'This account' at the bottom of the window. Next to the radio button appear textboxes for user name and password where name and password of user who can see the network is entered.
Also when specifying the path in 'backup database' command use the notation '//server_name/shared_directory/file_name'. Directory where the backup will be made have to be shared on the network and the computer where SQL server runs have to be able to access it (sharing to 'Everyone' group will do this).|||'//server_name/shared_directory/file_name'

You might be better off using \\servername\directory\filename. Just an fyi.

01010111011010000111100100100000011000010111001001 10010100100000011101000110100001100101011100110110 01010010000001110000011001010110111101110000011011 00011001010010000001110011011011110010000001110011 011101000111010101110000011010010110010000111111

BackUp - Device Activation Error

The directory structure where database previously was NOT
the same directory structure on the server you are trying
to restore database to.
Edit the directory structure on the 'Options' tab to
change the old directory to the new directory where you
want the data & log files to reside (If you are restoring
through E.M. If not, use MOVE option-See B.O.L.).

>--Original Message--
>Hi,
>I am trying to restore a database from a different server
to my server and
>also with a different name.
>But it gives me an error.
>ODBC-SQLState 42000
>Device Activation Error.
>Does anyone know what this is ?
>Any help is appreciated.
>
>.
>Thank you very much it worked.
But i had to go to the server rather than my own machine.
I guess it was the directory structure.
warm regards,
Ajit.
"James" <anonymous@.discussions.microsoft.com> wrote in message
news:377601c4c10f$23686190$a301280a@.phx.gbl...[vbcol=seagreen]
> The directory structure where database previously was NOT
> the same directory structure on the server you are trying
> to restore database to.
> Edit the directory structure on the 'Options' tab to
> change the old directory to the new directory where you
> want the data & log files to reside (If you are restoring
> through E.M. If not, use MOVE option-See B.O.L.).
>
> to my server and

BackUp - Device Activation Error

The directory structure where database previously was NOT
the same directory structure on the server you are trying
to restore database to.
Edit the directory structure on the 'Options' tab to
change the old directory to the new directory where you
want the data & log files to reside (If you are restoring
through E.M. If not, use MOVE option-See B.O.L.).

>--Original Message--
>Hi,
>I am trying to restore a database from a different server
to my server and
>also with a different name.
>But it gives me an error.
>ODBC-SQLState 42000
>Device Activation Error.
>Does anyone know what this is ?
>Any help is appreciated.
>
>.
>
Thank you very much it worked.
But i had to go to the server rather than my own machine.
I guess it was the directory structure.
warm regards,
Ajit.
"James" <anonymous@.discussions.microsoft.com> wrote in message
news:377601c4c10f$23686190$a301280a@.phx.gbl...[vbcol=seagreen]
> The directory structure where database previously was NOT
> the same directory structure on the server you are trying
> to restore database to.
> Edit the directory structure on the 'Options' tab to
> change the old directory to the new directory where you
> want the data & log files to reside (If you are restoring
> through E.M. If not, use MOVE option-See B.O.L.).
>
> to my server and

Backup

I keep getting the following message: The complete backup directory is
invalid
What is the problem'Hi,
Could u please post us the command you are trying. As well as tell us how
are trying to backup and from where you are gtting this error.
Thanks
Hari
SQL Server MVP
"S Shulman" wrote:
> I keep getting the following message: The complete backup directory is
> invalid
> What is the problem'
>
>

Backup

I keep getting the following message: The complete backup directory is
invalid
What is the problem'Hi,
Could u please post us the command you are trying. As well as tell us how
are trying to backup and from where you are gtting this error.
Thanks
Hari
SQL Server MVP
"S Shulman" wrote:

> I keep getting the following message: The complete backup directory is
> invalid
> What is the problem'
>
>

Backup

I keep getting the following message: The complete backup directory is
invalid
What is the problem?
Hi,
Could u please post us the command you are trying. As well as tell us how
are trying to backup and from where you are gtting this error.
Thanks
Hari
SQL Server MVP
"S Shulman" wrote:

> I keep getting the following message: The complete backup directory is
> invalid
> What is the problem?
>
>

Backup

Is there any way by which I can get the database which I had created
from the Registry? Due to a sudden power failure, the MSSQL7 directory
under the C:\ drive got deleted. I had the DB backup in the BACKUP
directory but since the entire MSSQL7 directory got deleted, I have
lost the backup.
I know there isn't any way to get back the DB but still asking this
question keeping my fingers crossed.
Thanks,
ArpanWhat do you mean by "from the registry" ?
Jens Suessmeyer.|||Afraid not.
Everything is held in the data files.
A bit late now but you really should copy the backups off the machine as
soon as they are complete otherwise you are stuck if you lose the disk.
Think about why you take backups.
"Arpan" wrote:
> Is there any way by which I can get the database which I had created
> from the Registry? Due to a sudden power failure, the MSSQL7 directory
> under the C:\ drive got deleted. I had the DB backup in the BACKUP
> directory but since the entire MSSQL7 directory got deleted, I have
> lost the backup.
> I know there isn't any way to get back the DB but still asking this
> question keeping my fingers crossed.
> Thanks,
> Arpan
>