I am trying to do a full backup for an MSSQL database using go.
However, I tested the connection to the MSSQL server and it works like charm!
By executing also a query into the code SELECT @@VERSION
It gives me the version and that has been connected to mssql using the credentials in the code below.
By running a query BACKUP DATABASE name_db TO DISK = '/tmp/database.bak' it fails with the following error
Error with Backing up the DBmssql: BACKUP DATABASE is terminating abnormally
I am sure that I am missing something, any ideas/thoughts would be much appreciated.
BTW, I am on Ubuntu 16.04 and the mssql is installed there, I am using go version 1.9.
This is my code:
package main
import (
_ "github.com/denisenkom/go-mssqldb"
"database/sql"
"context"
"log"
"fmt" )
var server = "server_url"
var port = 1433
var user = "user"
var password = "pass"
var db *sql.DB
func main() {
var err error
// Create connection string
connString := fmt.Sprintf("server=%s;user id=%s;password=%s;
port=%d", server, user, password, port)
// Create connection pool
db, err = sql.Open("sqlserver", connString)
if err != nil {
log.Fatal("Error creating connection pool: "
+ err.Error())
}
log.Printf("Connected!
")
defer db.Close()
Backup()
//Restore()
}
func Backup(){
ctx := context.Background()
err := db.PingContext(ctx)
if err != nil {
log.Fatal("Error pinging database :( " + err.Error())
}
var result string
err = db.QueryRowContext(ctx, "BACKUP DATABASE tempdb
TO DISK= '/tmp/Backup.bak' WITH NOFORMAT, NOINIT,
SKIP, NOREWIND, NOUNLOAD, STATS = 10;").Scan(&result)
//err = db.QueryRowContext(ctx, "select @@version").Scan(&result)
if err != nil {
log.Fatal("Error with Backing up the DB", err.Error())
}
fmt.Printf("%s
",result)
}
I have found the solution which is very silly.. You can find it in the 1st reply.
That was obvious though, the code is clean and working as expected.
The only issue was from MSSQL, where it have a specific value set for the timeout queries.
That's why I succeeded executing other SQL queries like: SELECT @@VERSION from the example above in the Q.
Though, I set the value of the timeout for remote queries to 0 this SQL query:
EXEC SP_CONFIGURE 'remote query timeout', 0
reconfigure
EXEC sp_configure
The 0 in remote query timeout means that the timeout is unlimited.