Taking Sql Backup in Local Machine

Wed, 02 Jan 2008 15:00:03 Z

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

Taking Sql Backup in Local Machine

Thu, 03 Jan 2008 12:05:11 Z

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

Taking Sql Backup in Local Machine

Thu, 03 Jan 2008 17:41:23 Z

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