backup SQL Server database w/o Ent. Mgr.

[supmktg]

I need to make an emergency backup of a linked SQL server database. The database is hosted and I do not have Enterprise Manager. Is there another way to do this?

Thanks,
Sup

 

[WayneRyan]

Sup,

Sort of an example, but with a RESTORE command instead of BACKUP.

'
' Open a connection
'
Dim strConnection As String
strConnection = "Driver={SQL Server};" & _
                "Server=YourServer;" & _
                "Database=Master;Trusted_Connection=YES"

Set DbConnection = New ADODB.Connection
With DbConnection
  .Mode = adModeReadWrite
  .Properties("Prompt") = adUseClient
  Call .Open(strConnection)
  End With
'
' Send a RESTORE command (Sorry, don't have BACKUP syntax)
'                        (Temp has some filespec in it)
'                        (BACKUP should be simpler, don't need the MOVE
'                         option as below)
'
' I'd imagine the BACKUP command would be much simpler text.
'
sql = "RESTORE DATABASE Work_DB_ARCHIVE " & vbCrLf & _
      "FROM DISK = '" & Temp & "' " & vbCrLf & _
      "With Move 'Logical_Data' TO 'F:\Data_ARCHIVE.mdf', " & vbCrLf & _
      "Move 'Logical_log' TO 'E:\Log_ARCHIVE_log.ldf' "
DbConnection.Execute (sql)

hth,
Wayne

 

[supmktg]

Wayne,

Thank you for your reply! I appreciate your help, not just on this post, but on many issues for which I often search and find your responses!

I've searched and found the following 'Backup' code:

"BACKUP DATABASE mydatabasenameishere TO DISK = 'C:\SQLBackup'", , adExecuteNoRecords

My first problem is that I don't have a trusted connection, I'm using sql server authentication. The login fails and pops up the sql server login dialog box. I'm using this connection string:

strConnection = "Driver={SQL Server};" & _
"Server=myserveraddressishere;" & _
"Database=mydatabasenameishere;" & _
"Uid = myuseridishere;" & _
"Pwd = mypasswordishere;"

I can bypass this problem by unchecking 'trusted connection' and entering my username and password in the pop up dialog box.

My next problem is that I then get an error:

Error -2147217900 ([Microsoft][SQL Server ODBC Driver][SQL Server]Processed 1888 pages for database 'mydatabasename',file 'mydatabasename_data' on file 1)

Any clue how I might get past these issues?

Thanks,
Sup