Backup a Live SQL Database and Restore

tstrip007

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

higherho

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.

Matt2

If there are network hiccups causing issues, any particular reason you can't backup locally then copy over?

DoubleNNs

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