Wednesday, March 2, 2016

How can I backup a SQL Database to a folder on my computer?

You can do it using SQL Server Management Studio(SSMS) or Windows PowerShell. If you are a 'click' expert you would choose SSMS, but if you are a 'dba' type you would rather choose the key board.

The backup-sqldatabase commandlet in Windows Powershell can do it in no time at all. There are only two lines of code.

Let me show the database Northwind in the Object Browser of my SQL Server 2012 Instance.
The name of the instance with the domain is Hodentek8\RegencyPark. On this instance there are several databases. we will backup 'Northwind'


Launch Windows PowerShell ISE by typing in 'Windows PowerShell ISE' in the searchbox('cortana') in Windows 10.


In the Script Pane of Windows PowerShell ISE type in the following lines as shown:

$sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") "Hodentek8\RegencyPark"
Backup-SqlDatabase -ServerInstance "Hodentek8\RegencyPark"
                   -Database "Northwind"
                   -BackupFile "\\HODENTEK8\Users\Public\nwind.bak"

After this code is processed the backup file will be found in the UNC location shown above.

You may want to review this post:

No comments: