Backup a Live SQL Database and Restore

tstrip007tstrip007 Member Posts: 308 ■■■■□□□□□□
Having a hell of a time trying to back up a live SQL db and restore to a different SQL server. SQL will not let me backup to a network location so I have tried using Acronis to backup the .mdf and .ldf files of the database I want. My db guy had trouble on the restore.

Anyone here have experience doing this? I need to be able to backup to a network location...

Comments

  • higherhohigherho Member Posts: 882
    Is this MSSQL? If so open up your Management Studio application and log into the DB. Once here schedule a MSSQL job to backup the DB and execute. Select the location and run the job.
  • Matt2Matt2 Member Posts: 97 ■■□□□□□□□□
    If there are network hiccups causing issues, any particular reason you can't backup locally then copy over?
  • DoubleNNsDoubleNNs Member Posts: 2,015 ■■■■■□□□□□
    I'd recommend backing up to localhost then copying the backup over the network as well. But I can understand that sometimes available space on the localhost may prevent this, especially w/ very, very, large DBs.

    If it's MySQL, you can use mysqldump over the network:
    mysqldump -P3306  -h192.168.20.151 -uroot -p database > c:/my.sql
    
    MySQLDump to local machine from remote server connected via SSH - Stack Overflow


    You can also use SELECT *... INTO OUTFILE... in MySQL to backup a database a table at a time by exporting to CVS(which is desired in some situations), but aren't able to do so over the network.

    Using INTO OUTFILE from a Remote Client | SQLProNews
    Goals for 2018:
    Certs: RHCSA, LFCS: Ubuntu, CNCF CKA, CNCF CKAD | AWS Certified DevOps Engineer, AWS Solutions Architect Pro, AWS Certified Security Specialist, GCP Professional Cloud Architect
    Learn: Terraform, Kubernetes, Prometheus & Golang | Improve: Docker, Python Programming
    To-do | In Progress | Completed
Sign In or Register to comment.