Solved

PowerShell to backup & restore SQL Server 2008 databases

Posted on 2011-09-14
18
1,929 Views
Last Modified: 2012-05-12
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
Comment
Question by:crazywolf2010
  • 9
  • 7
  • 2
18 Comments
 
LVL 9

Expert Comment

by:mimran18
ID: 36535911
0
 

Author Comment

by:crazywolf2010
ID: 36536392
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
 
LVL 6

Expert Comment

by:netjgrnaut
ID: 36539149
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
 
LVL 9

Expert Comment

by:mimran18
ID: 36540914
0
 

Author Comment

by:crazywolf2010
ID: 36541536
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
 
LVL 6

Expert Comment

by:netjgrnaut
ID: 36543119
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
 

Author Comment

by:crazywolf2010
ID: 36543230
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
 
LVL 6

Expert Comment

by:netjgrnaut
ID: 36543313
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
 

Author Comment

by:crazywolf2010
ID: 36543534
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 6

Expert Comment

by:netjgrnaut
ID: 36543948
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
 

Author Comment

by:crazywolf2010
ID: 36544104
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
 
LVL 6

Expert Comment

by:netjgrnaut
ID: 36544456
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
 

Author Comment

by:crazywolf2010
ID: 36548114
Hi,
Please see attached snap for details. Do I need to enable the disabled protocols?

Thanks
DBA-000330.png
0
 
LVL 6

Accepted Solution

by:
netjgrnaut earned 500 total points
ID: 36549023
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
 

Author Closing Comment

by:crazywolf2010
ID: 36549448
Issue Resolved
0
 

Author Comment

by:crazywolf2010
ID: 36549455
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
 
LVL 6

Expert Comment

by:netjgrnaut
ID: 36549579
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
 

Author Comment

by:crazywolf2010
ID: 36549629
Sure, Many thanks.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article will help you understand what HashTables are and how to use them in PowerShell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now