Hi BigBrains!
                  I need to take backup of my current database,(SQL-SERVER 2000), from my VB code. My client is novice user, and confuses himself while taking  backup of his database, through SQL-Server's console.
                  So, I need to make a simple application, which will take the backup...and will resore the same as required.
Help me out!                  

____________________________
Bite the bit
sanc1@rediffmail.com

Hi.
SQL 2000 databases are (usually) a single file. You can back it up by copying the physical file. The SQLDMO will give you the filename if you need it.
Note, this will not perform a logged backup and will not account for logged-in users.
Hope this helps,
Kieron


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

If you lookup "backup" in the books online, it has a LOT of info there, but basically it's this:

Syntax
Backing up an entire database:

BACKUP DATABASE { database_name | @database_name_var }
TO < backup_device > [ ,...n ]
[ WITH
    [ BLOCKSIZE = { blocksize | @blocksize_variable } ]
    [ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
    [ [ , ] DIFFERENTIAL ]
    [ [ , ] EXPIREDATE = { date | @date_var }
        | RETAINDAYS = { days | @days_var } ]
    [ [ , ] PASSWORD = { password | @password_variable } ]
    [ [ , ] FORMAT | NOFORMAT ]
    [ [ , ] { INIT | NOINIT } ]
    [ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
    [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
    [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
    [ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
    [ [ , ] { NOSKIP | SKIP } ]
    [ [ , ] { NOREWIND | REWIND } ]
    [ [ , ] { NOUNLOAD | UNLOAD } ]
    [ [ , ] RESTART ]
    [ [ , ] STATS [ = percentage ] ]
]

Backing up specific files or filegroups:

BACKUP DATABASE { database_name | @database_name_var }
    < file_or_filegroup > [ ,...n ]
TO < backup_device > [ ,...n ]
[ WITH
    [ BLOCKSIZE = { blocksize | @blocksize_variable } ]
    [ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
    [ [ , ] DIFFERENTIAL ]
    [ [ , ] EXPIREDATE = { date | @date_var }
        | RETAINDAYS = { days | @days_var } ]
    [ [ , ] PASSWORD = { password | @password_variable } ]
    [ [ , ] FORMAT | NOFORMAT ]
    [ [ , ] { INIT | NOINIT } ]
    [ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
    [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
    [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
    [ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
    [ [ , ] { NOSKIP | SKIP } ]
    [ [ , ] { NOREWIND | REWIND } ]
    [ [ , ] { NOUNLOAD | UNLOAD } ]
    [ [ , ] RESTART ]
    [ [ , ] STATS [ = percentage ] ]
]

Backing up a transaction log:

BACKUP LOG { database_name | @database_name_var }
{
    TO < backup_device > [ ,...n ]
    [ WITH
        [ BLOCKSIZE = { blocksize | @blocksize_variable } ]
        [ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
        [ [ ,] EXPIREDATE = { date | @date_var }
            | RETAINDAYS = { days | @days_var } ]
        [ [ , ] PASSWORD = { password | @password_variable } ]
        [ [ , ] FORMAT | NOFORMAT ]
        [ [ , ] { INIT | NOINIT } ]
        [ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
        [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
        [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
        [ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
        [ [ , ] NO_TRUNCATE ]
        [ [ , ] { NORECOVERY | STANDBY = undo_file_name } ]
        [ [ , ] { NOREWIND | REWIND } ]
        [ [ , ] { NOSKIP | SKIP } ]
        [ [ , ] { NOUNLOAD | UNLOAD } ]
        [ [ , ] RESTART ]
        [ [ , ] STATS [ = percentage ] ]
    ]
}

< backup_device > ::=
    {
        { logical_backup_device_name | @logical_backup_device_name_var }
        |
        { DISK | TAPE } =
            { 'physical_backup_device_name' | @physical_backup_device_name_var }
    }

< file_or_filegroup > ::=
    {
        FILE = { logical_file_name | @logical_file_name_var }
        |
        FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }
    }

Truncating the transaction log:

BACKUP LOG { database_name | @database_name_var }
{
    [ WITH
        { NO_LOG | TRUNCATE_ONLY } ]
}

Arguments
DATABASE

Specifies a complete database backup. If a list of files and filegroups is specified, only those files and filegroups are backed up.



Note  During a full database or differential backup, Microsoft® SQL Server™ backs up enough of the transaction log to produce a consistent database for when the database is restored. Only a full database backup can be performed on the master database.


{ database_name | @database_name_var }

Is the database from which the transaction log, partial database, or complete database is backed up. If supplied as a variable (@database_name_var), this name can be specified either as a string constant (@database_name_var = database name) or as a variable of character string data type, except for the ntext or text data types.

< backup_device >

Specifies the logical or physical backup device to use for the backup operation. Can be one or more of the following:

{ logical_backup_device_name } | { @logical_backup_device_name_var }
Is the logical name, which must follow the rules for identifiers, of the backup device(s) (created by sp_addumpdevice) to which the database is backed up. If supplied as a variable (@logical_backup_device_name_var), the backup device name can be specified either as a string constant (@logical_backup_device_name_var = logical backup device name) or as a variable of character string data type, except for the ntext or text data types.

{ DISK | TAPE } =
'physical_backup_device_name' | @physical_backup_device_name_var
Allows backups to be created on the specified disk or tape device. The physical device specified need not exist prior to executing the BACKUP statement. If the physical device exists and the INIT option is not specified in the BACKUP statement, the backup is appended to the device.
When specifying TO DISK or TO TAPE, enter the complete path and file name. For example, DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Mybackup.dat' or TAPE = '\\.\TAPE0'.



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

If using a network server with a Uniform Naming Convention (UNC) name or using a redirected drive letter, specify a device type of disk.

When specifying multiple files, logical file names (or variables) and physical file names (or variables) can be mixed. However, all devices must be of the same type (disk, tape, or pipe).

Backup to tape is not supported on Windows 98.



____________________________
Everywhere's Local (classifieds, job postings, & more for everycity in the world - user entered)

Thanx Guys! [JLRodgers,stickleprojects]
                
                  Once again, I found the cool solutions of my prob. in an unexpected time...thanx once again...keep it up guys.

Gotta check it out...till then
Bye


____________________________
Bite the bit
sanc1@rediffmail.com

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ERROR I FACED WHILE TRYING TO TAKE BACKUP FROM MY CODE through SQLDMO

Run-time error '-2147218298(80040c86)':

[Microsoft][ODBC SQL Server Driver][SQL Server] No entry in sysdevices for backup device 'C:\Documents'.Update sysdevices and rerun statement.[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> MY CODE

Dim BACKUP As New SQLDMO.BACKUP
Dim SERVER As New SQLServer

Private Sub Form_Load()

SERVER.Connect "Sanjib", "sa"

BACKUP.Action = SQLDMOBackup_Database
BACKUP.Database = "Northwind"
BACKUP.Devices = "C:\Documents and Settings\Administrator\Desktop\BACKUP.bak"
BACKUP.BackupSetDescription = "Full BackUp"
BACKUP.BackupSetName = "By Sanjib"

BACKUP.SQLBackup SERVER

End Sub

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>   Help me out guys....

____________________________
Bite the bit
sanc1@rediffmail.com

specify another folder to place yr backup file. use a short folder name eg: c:\test\

the error is because sql dont support long filenames
if u must use c:\documents and settings....convert it to shorter
c:\docs~1 etc

Hi Mr. hungheykwun,
                              Thanx a lot for the suggestion. I'll go through that point...

Bye

____________________________
Bite the bit
sanc1@rediffmail.com

I know this is an old post but the advice provided by
Mr. hungheykwun is incorrect and if there are other people like me who search the forums for solutions:

Dim oDevice As New SQLDMO.BackupDevice
Dim BACKUP As New SQLDMO.BACKUP
Dim SERVER As New SQLServer

Private Sub Form_Load()
On Error Resume Next 'If the device already exists an error will result if you try to add it again so just resume next cos its already there

With oDevice
  .Type = SQLDMODevice_DiskDump
  .Name = "NorthwindBakUp"
  .PhysicalLocation = "C:\Documents and Settings\Administrator\Desktop\BACKUP.bak"
End With

SERVER.Connect "Sanjib", "sa"
SERVER.BackupDevices.Add oDevice
BACKUP.Action = SQLDMOBackup_Database
BACKUP.Database = "Northwind"
BACKUP.Devices ="NorthwindBakUp"
BACKUP.BackupSetDescription = "Full BackUp"
BACKUP.BackupSetName = "By Sanjib"

BACKUP.SQLBackup SERVER

End Sub



____________________________
I'd rather be fishing

Thanx Mr.RayZore
                           I guess this time it will work. I'll obviously let you know about the result. Thanx a lot buddy, for the detailed help.
with regards

____________________________
Bite the bit
sanc1@rediffmail.com

quote:

---

hungheykwun wrote:
specify another folder to place yr backup file. use a short folder name eg: c:test

the error is because sql dont support long filenames
if u must use c:documents and settings....convert it to shorter
c:docs~1 etc

---

I'm building a VB.NET application that uses SQL DMO to automate database dumps.  This will be used by our DBAs to simplify the transfer of databases between server environments.

As part of the process, I need to create a backup of an online database and then restore with a different name to run cleanup scripts against it to remove unneeded audit data, etc.  I've tried setting objSqlBackup.BackupSetName to accomplish this, but it appears the backup files retain the original database name internally.

Does anyone have a code sample or link that explains how this might be done?

FYI, The full process is as follows:
- Create backup of selected database
- Restore database with a different name (this is where I need help)
- Run cleanup scripts to clear out unnecessary audit data, etc.
- Create backup of "cleaned" database
- Create zip file of database backup

Guys

I had a problem with my backup database using sql server 2000..

This code was coppied

Dim oDevice As New SQLDMO.BackupDevice
Dim BACKUP As New SQLDMO.BACKUP
Dim SERVER As New SQLServer



With oDevice
  .Type = SQLDMODevice_DiskDump
  .Name = "NWBakUp"
  .PhysicalLocation = "C:\VIC\BACKUP.bak"
End With

'error  here''''''''''''''''''''''''''''''
SERVER.Connect "(Local)", "sa''
'''''''''''''''
'login failed for user
'not associate with a trusted sql server registration

SERVER.BackupDevices.Add oDevice
BACKUP.Action = SQLDMOBackup_Database
BACKUP.Database = "myDBASE"
BACKUP.Devices = "vBaCkUp"
BACKUP.BackupSetDescription = "Full BackUp"
BACKUP.BackupSetName = "By vyx"

BACKUP.SQLBackup SERVER

i was log as windows NT authentication mode

Please help me further guys.
Thanks in advance
vyx

Hello. I was writing a reply to this but it has grown a bit so I posted an article here, in the Articles and tutorials section.
I hope you'll find it helpful.

____________________________
Real Programmer can count up to 1024 on his fingers