PowerShell to backup & restore SQL Server 2008 databases

Hi,
   Here we go.
http://www.sswug.org/articles/viewarticle.aspx?id=44909

Hi,
I am trying to use it on my sql server 2008 install here and it is returning an error below.
Somewhere it says I need to load SqlServer.SmoExtended to use SMO backup in SQL Server 2008. What is this and how do I install/load it?

Default Backup Directory:
Unable to index into an object of type Microsoft.SqlServer.Management.Smo.Datab
aseCollection.
At line:1 char:25
+ $db = $server.Databases[$ <<<< dbToBackup]
Exception calling "SqlBackup" with "1" argument(s): "Backup failed for Server '
test-srv'. "
At line:1 char:21
+ $smoBackup.SqlBackup( <<<< $server)

backup.txt

Are you running the PoSh script directly on your server?  If you did a server-only install, then you're probably missing the assemblies that getinstalled along with the SQL management studio/client tools.

Once you're sure the managment studio has been installed, open powershell and run the cmdlets to load the SQL assemblies...

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")

Select allOpen in new window

If you get any errors, post them back here.  If no errors, then try running the script again.

Hi ,
     Please reference to this article :
http://www.sqlmusings.com/2009/05/31/how-to-backup-sql-server-databases-using-smo-and-powershell/

Sorry gents but I have working SQL server management studio here.
I am new to SQL server 2008 and I don't understand "If you did a server-only install, then you're probably missing the assemblies that getinstalled along with the SQL management studio/client tools."

Could you please explain what components I need and where to locate those components on net? I am using trial 180 days sql 2008.

SQL server 2008 install details are as below :

Microsoft SQL Server Management Studio                                    10.0.1600.22 ((SQL_PreRelease).080709-1414 )
Microsoft Data Access Components (MDAC)                                    2000.085.1132.00 (xpsp.080413-0852)
Microsoft MSXML                                    2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer                                    8.0.6001.18702
Microsoft .NET Framework                                    2.0.50727.3615
Operating System                                    5.1.2600

Yes, I see that the assemblies do appear to be loading when you run the script.

Are you running the script on the server itself?  The script as you've posted it is written to backup a local install of SQL.

The null return value after "Default Backup Directory" indicates that the script is not connecting to a valid SQL instance.  Consequently, everything else fails.

If you are running the script on an admin workstation to backup a server remotely, you'll need to do a lot of editing.  It will be easier for you to run the script on the server itself.

I don't see where "test-srv" has been added in the script you posted here.  Please post the script as you're running it now, and indicate whether or not the computer running the script is also where SQL server is installed.  That will help.

Hi Mate,
I am 100% running it on the server itself. The script is as it was run on the machine. For security reasons I have only changed the machine name with test-srv(localhost) else everything is as I am running here.

Inside the script  backupdirectory set as
>>$backupDirectory = $server.Settings.BackupDirectory

I am not sure of variable value "$server.Settings.BackupDirectory". Is it easy to add print/echo stmts to see environment values?

Thanks

I suggest opening up a PowerShell window, alongside your script in a text editor.  Copy/paste each line from the script to the PoSh window.  This will let you see the results line by line.

In the PoSh window, you can get the value of any variable by typing the variable by itself at the prompt.

I think your problem lies in the command

$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"

Select allOpen in new window

This line should appear *exactly* like this in your script.  If you've changed any of it to reflect the actual name of your server - for example, "(local)" - try changing it back.

After you execute the command manually, type

$server.version

Select allOpen in new window

What do you get back?  If you've successfully connected to the SQL server, your output should look like this:

Major  Minor  Build  Revision
-----  -----  -----  --------
10     50     1617   -1

Select allOpen in new window

If instead you get back nothing, then you're connected to nothing.

The rest of your errors are the result of no connection to the SQL database.

Hope that helps!

Hi,
The command "$server.version" returns nothing.

Please see log below

PS P:\> $dbToBackup = "master"
PS P:\> [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.S
MO") | Out-Null
PS P:\> [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.S
moExtended") | Out-Null
PS P:\> [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.C
onnectionInfo") | Out-Null
PS P:\> [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.S
moEnum") | Out-Null
PS P:\> $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(loc
al)"
PS P:\> $server.version
PS P:\>

Yup - not connected to the SQL instance.  The rest of the errors make sense, as the Smo.Server object ($server) is null.

Are you running the script with an account that has sa/dbo permissions on the SQL server?  What do you get if you run the following command?

>sqlcmd -s localhost -Q "use master"

We're looking for

Changed database context to 'master'.

We're getting warmer...

Hi,
Next Error, Is this related to surface area configuration?
I looked for that config option but don't see any? Is that changed in 2008?

P:\>sqlcmd -s localhost -Q "use master"
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or in
stance-specific error has occurred while establishing a connection to SQL Server
. Server is not found or not accessible. Check if instance name is correct and i
f SQL Server is configured to allow remote connections. For more information see
 SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.
DBA-000329.png

Yup.  It's either your server protocols *or* you're running a named instance of SQL (instead of the default that these scripts & test commands are looking for).

You can check the server protocols by running Start > Programs > Microsoft SQL Server 2008 R2 > Configuration Tools > SQL Server Configuration Manager.  Navigate to SQL Server Configuration Manager (Local) > SQL Server Network Configuration > Protocols for MSSQLSERVER.  What is enabled there?

If the last node in the MMC path above is something *other* than "Protocols for MSSQLSERVER" then it looks like SQL was installed as a named instance.  The instance name is whatever's in place of "MSSQLSERVER".

Let me know!

Hi,
Please see attached snap for details. Do I need to enable the disabled protocols?

Thanks
DBA-000330.png

Issue Resolved

Hi Netjgrnaut,
I can see my backup done now successfully. Many Thanks for your help.

Do you know how can I schedule this backup thru SQL Agent  everynight?

Regards

Glad that did the trick.  :-)

Actually, you'd schedule it through Task Scheduler.  It's not a SQL server action, but rather a locally excuted script.

Try this...
http://www.searchmarked.com/windows/how-to-schedule-a-windows-powershell-script.php

If you need help troubleshooting the process, please open up a fresh question.

Thanks!

Sure, Many thanks.