Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2123
  • Last Modified:

PowerShell to backup & restore SQL Server 2008 databases

Hi All,
I am new to powershell and looking for help to carry SQL server admin thru powershell.

The task is simple. I have new installation of SQL server 2008 and I wish to develop powershell scripts to backup & restore databases.
My databases are in FULL recovey mode. The script should backup databases on a device & truncate logs.

The restore script should accept 1)DB_name and 2) Date & time as input and restore database until point in time from backup.

Many Thanks
0
crazywolf2010
Asked:
crazywolf2010
  • 9
  • 7
  • 2
1 Solution
 
mimran18Commented:
0
 
crazywolf2010Author Commented:
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
0
 
netjgrnautCommented:
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")

Open in new window


If you get any errors, post them back here.  If no errors, then try running the script again.
0
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
mimran18Commented:
0
 
crazywolf2010Author Commented:
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
0
 
netjgrnautCommented:
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.
0
 
crazywolf2010Author Commented:
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
0
 
netjgrnautCommented:
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)"

Open 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

Open 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

Open 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!
0
 
crazywolf2010Author Commented:
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:\>
0
 
netjgrnautCommented:
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...
0
 
crazywolf2010Author Commented:
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
0
 
netjgrnautCommented:
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!
0
 
crazywolf2010Author Commented:
Hi,
Please see attached snap for details. Do I need to enable the disabled protocols?

Thanks
DBA-000330.png
0
 
netjgrnautCommented:
Nope.  Your protocols are fine for this script.  If you're looking to make your database accessible from anywhere other than the local server, you've got work to do - but that's a whole 'nother topic...

You're running a named instance of SQL (named SQLEXPRESS).  The script is written to connect to the default instance.

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

Open in new window


To this...
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "localhost\SQLEXPRESS"

Open in new window


Test using the proceedure from ealier (copy/pasting the lines one-by-one from the script into a PoSh window).  After you enter the line above, type

>$server.version

...and you should get back something like

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

If that's the case, then you should be good to go with the slightly modified script.

Hope that works for you!
0
 
crazywolf2010Author Commented:
Issue Resolved
0
 
crazywolf2010Author Commented:
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
0
 
netjgrnautCommented:
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!
0
 
crazywolf2010Author Commented:
Sure, Many thanks.
0

Featured Post

Who's Defending Your Organization from Threats?

Protecting against advanced threats requires an IT dream team – a well-oiled machine of people and solutions working together to defend your organization. Download our resource kit today to learn more about the tools you need to build you IT Dream Team!

  • 9
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now