Taking Sql Backup in Local Machine

Question

 

Hi Glenn Wilson ,

 

 I tried with the given code, it is working but it is generating the backup file in SQLServer Machine, please can u help me to take the backup at my local machine.

 

Regards

Sravan

Answer 1

If its about the sql statement then

 

Backup Database YourDatabaseName To Disk='YourDatabase.bak'

 

If its regarding SMO code

Refer

http://www.sqlservercentral.com/articles/SMO/2900/

(This site requires login to access the resources)

 

Madhu

 

 

Answer 2

Here's a PowerShell script I developed which will back up all the user databases on your server, and then perform a transaction log backup on them as well, using SMO.  It extracts the defined backup directory from your server and places the files there, and it uses the system date/time to give the files a unique name:

 

Code Block

#backup.ps1
#Performs a Full backup followed by a transaction log backup on all user databases
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")  | out-null
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer\MyInstance'
$bkdir = $s.Settings.BackupDirectory
$dbs = $s.Databases
$dbs | foreach-object {
 $db = $_
 
 if ($db.IsSystemObject -eq $False -and $db.IsMirroringEnabled -eq $False) {
  $dbname = $db.Name
  $dt = get-date -format yyyyMMddHHmmss
  $dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
  $dbbk.Action = 0
  $dbbk.BackupSetDescription = "Full backup of " + $dbname
  $dbbk.BackupSetName = $dbname + " Backup"
  $dbbk.Database = $dbname
  $dbbk.MediaDescription = "Disk"
  $dbbk.Devices.AddDevice($bkdir + "\" + $dbname + "_db_" + $dt + ".bak", 2)
  $dbbk.SqlBackup($s)
  $dt = get-date -format yyyyMMddHHmmss
  $dbtrn = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
  $dbtrn.Action = 2
  $dbtrn.BackupSetDescription = "Trans Log backup of " + $dbname
  $dbtrn.BackupSetName = $dbname + " Backup"
  $dbtrn.Database = $dbname
  $dbtrn.MediaDescription = "Disk"
  $dbtrn.Devices.AddDevice($bkdir + "\" + $dbname + "_tlog_" + $dt + ".trn", 2)
  $dbtrn.SqlBackup($s)
  }

 }

 

 

Hope that helps