Updates from September, 2010 Toggle Comment Threads | Keyboard Shortcuts

  • Ali BaderEddin 2:16 am on September 28, 2010 Permalink | Reply
    Tags: createunattend, unattendfile   

    Visual Studio 2010 – Unattended Silent Install 

    You can find on MSDN How to: Create and Run an Unattended Visual Studio Setup. This post focuses on unattended installation of VS 2010 product only (without SDK) along with screenshots and hints on what to avoid. Below are the steps I have followed to create a silent unattended installation script for VS 2010.

    • Create the VS 2010 unattend configuration file
      <path to VS installation folder>\setup\setup.exe /createunattend c:\VS2010Config.ini

      Note: Make sure you run the \setup\setup.exe and not \setup.exe directly.

    • The wizard will launch… Accept license terms, select the feature you want to install, then click Save Settings.
    • Once you click “Save Settings”, the VS config file will be created at c:\VS2010Config.ini
    • Now, do the unattended installation with the command below
      <path to VS installation folder>\setup\setup.exe /unattendfile c:\VS2010Config.ini

    As you can see, the steps are pretty straight-forward. Enjoy!

    • serman durai 5:39 pm on January 25, 2012 Permalink | Reply

      i followed your step to make inf file .but i am not able to find save settings options .in that same place next options is there .Would pls help me for inf file

    • Ali BaderEddin 11:31 pm on January 25, 2012 Permalink | Reply

      Make sure you are running \setup\setup.exe not \setup.exe

      • Sudhakar 6:25 am on January 13, 2014 Permalink | Reply

        Hi Ali,

        Thanks for wonderful hint for Unattended silent Installation of VS 2010….

    • Livio 3:16 am on September 11, 2012 Permalink | Reply

      Just click “Next”, then accept the License, there you can now chose if you want to save the full settings or custom settings (chose between Full or Custom and click Save Settings).
      For me that all worked but when I try to use it it tells me the file is wrong or something. I’m using PowerShell to chose the file.

    • Livio 3:24 am on September 17, 2012 Permalink | Reply

      Forget it. It works now.

    • content spinner 3:29 am on September 4, 2014 Permalink | Reply

      Whɑt’s up to all, how is all, I think everʏ
      one is getting more from this web site, and yyour vieas
      are good in ѕսpport of new users.

    • Honza 3:01 am on October 3, 2014 Permalink | Reply

      Works good. Thank you very much.

    • shakthivel 10:38 pm on November 25, 2014 Permalink | Reply

      hi its saying unable to create.in file,how to resolve this

  • Ali BaderEddin 12:43 am on September 28, 2010 Permalink | Reply
    Tags: , IACCEPTSQLSERVERLICENSETERMS, install, quiet, setup, silent, sql server 2008 r2, SQLSYSADMINACCOUNTS, unattended   

    SQL Server 2008 R2 – Unattended Silent Install 

    There is existing documentation on MSDN about How to: Install SQL Server 2008 R2 from the Command Prompt, but this post focuses on creating a silent unattended install of SQL Server 2008 R2 (Standard or Enterprise) using a configuration file. With the instructions below, you’ll be able to create a new customized SQL silent installation in minimal time. Note that the configuration below doesn’t work with the Express edition of SQL Server 2008 R2.

    • Run setup.exe
    • Click on the installtion tab
    • Click “New Installation”
    • Go through the wizard: Enter product key, accept license, install setup support files, and select the features you want to install:
    • Continue the installation until you reach the “Ready To Install” step. Notice the path to the configuration file highlighted below in blue.

    • Now that you have the configuration file, copy it to your own folder or network share where you want to start the unattended installation.
    • Cancel setup since we’re interested in the unattended silent mode of installation; not the UI one.
    • Edit the configuration file as follows:
      • Set QUIET to “True”. This specifies that Setup will run in a quiet mode without any user interface (i.e. unattended installation)
      • Set SQLSYSADMINACCOUNTS to “BUILTIN\ADMINISTRATORS”. This will ensure that administrators on the machine are added as members of the sysadmin role. You can set its value based on your needs (Ex: SQLSYSADMINACCOUNTS=”domain\YourUser”), but this is the more generic approach.
      • Add PID and set its value to your product license key. If your setup.exe already comes preloaded with the key, there is no need to add this option to the configuration file.
      • Add IACCEPTSQLSERVERLICENSETERMS and set its value to “True”. This is to required to acknowledge acceptance of the license terms when the /Q (i.e. QUIET) parameter is specified for unattended installations.
      • Remove the ADDCURRENTUSERASSQLADMIN parameter. The reason is that this parameter can’t be used when SQLSYSADMINACCOUNTS is specified, and it only applies to Express installations.
      • Remove the UIMODE parameter as it can’t be used with the QUITE parameter.
      • Remove INSTALLSHAREDDIR, INSTALLSHAREDWOWDIR, INSTANCEDIR parameters since we want to install on the default installation directories.
      • That’s it. If you want to change the features that this setup will install, there is no need to go with the full steps again. You can simply change the value for the FEATURES parameter. For example, the features I selected (shown in first screenshot above) will generate FEATURES=SQLENGINE,SSMS,ADV_SSMS in the configuration file.  You can change that based on your needs. The full list of available feature parameters and their descriptions is located here.

    After getting the configuration file ready, you’ll need to create a batch file that will run the silent unattended setup. Simply, create a new file  “InstallSQL2008R2.bat” with extension = “.bat” with the following content, and make sure you replace <path to SQL setup folder> and <path to config file> with the proper values.

    @ECHO off
    echo Installing SQL Server 2008 R2
    time /t
    "<path to SQL setup folder>\setup.exe" /ConfigurationFile="<path to config file>"
    time /t

    All we’re doing in the script above is running SQL setup.exe and passing the configuration file as an argument to it. You can download both the batch and config files here.

    • Zulfiqar 10:17 am on April 11, 2011 Permalink | Reply

      Awesome…I bow in deference to your detailed explanation which has helped me achieve my goal…Thanks…

    • Ali BaderEddin 3:05 pm on April 11, 2011 Permalink | Reply

      You are welcome Zulficar :)

    • Nicus88 6:45 am on May 18, 2011 Permalink | Reply

      I can’t get the configurationfile from the link you put there. Don’t you have another link?

      By the way, great article

    • Ali BaderEddin 6:55 pm on May 18, 2011 Permalink | Reply

      I’ve updated the link in the post…

    • rta1212 3:12 am on October 29, 2011 Permalink | Reply

      Gardaşşş sağ olasın

    • Maheswaran 10:38 pm on November 14, 2011 Permalink | Reply

      Hi i need silent installation method Express edition of SQL Server 2008 R2. Please let me know……..

    • Jose Ignacio Montes 2:20 am on February 28, 2013 Permalink | Reply

      If you need to install quiet silence from c# the code is this:
      Care to take away all spaces in the string argumentos, I don´t know why but they go with the string and doesn´t work properly

      String sqlfile = @”\Msi\SQLEXPR_x64_ESN.exe”; //or wathever sql inst file you have
      myProcess.StartInfo.FileName = sqlfile;
      String argumentos = @”/qs /Action=Install /Features=SQL,Tools /IACCEPTSQLSERVERLICENSETERMS=””True”” /INSTANCENAME=””SQLExpress_AV”” /SQLSYSADMINACCOUNTS=””Builtin\Administrators”” /SQLSVCACCOUNT=””NT AUTHORITY\SYSTEM”” “;
      myProcess.StartInfo.Arguments = argumentos;
      myProcess.StartInfo.UseShellExecute = false;

    • Bob Ashby 12:16 pm on April 30, 2013 Permalink | Reply

      Thanks I used this also!!! wonderful!!!

    • Blas Cota 2:06 pm on July 4, 2013 Permalink | Reply

      When I try to run an install from the command line using setup.exe /CONFIGURATIONFILE=SQLSettings.ini

      I got the following errors:

      Error result: -2067529698
      Result facility code: 1220
      Result error code: 30

    • Nitish 8:13 am on March 26, 2014 Permalink | Reply

      How do I install sql 2008 sp2 enterprise NON R2 silently?

    • Ernestina 2:10 am on July 17, 2014 Permalink | Reply

      Roofing contractors have to do and is sharing a vast and diverse area.
      45, according to the proposed plans. Substantial changes in your heart.

      After jurors left the foreign service in house remodeling service both in depth research
      and find ways to go it alone. But the bottom-line is that
      they insisted upon a trustworthy roofing contractor from an analog world of good to be used to a successful
      and satisfactory completion.

    • Jimmy Kim 4:55 pm on July 29, 2014 Permalink | Reply

      Asking questions are truly nice thing if you are not understanding something fully,
      however this post offers nice understanding even.

    • self directed ira company reviews 7:29 am on August 31, 2014 Permalink | Reply

      My family all the time say that I am killing my time here at web,
      however I know I am getting experience all the time by
      reading thes fastidious articles.

    • Http://Hwe.Li 6:47 pm on September 20, 2014 Permalink | Reply

      How do you choose from a long list of suppliers
      and how do you judge the end result if you have not experienced working with
      them. For various reasons ranging from all time low real
      estate prices to high volumes of capital opportunity,
      here is a list of the top ten cities to start a business today.
      The roof not only acts as insulators during winters but also it acts as a wonderful cooling
      system during summer.

    • ecommerce search engine optimization 10:59 pm on September 20, 2014 Permalink | Reply

      Product Management And AdminCatalogue management is able to navigate, faster and
      better to beat the low cost click amounts.

      Apart from content management web design system. The designers would make the shopping and payment.
      They offer websites that are available over internet.

    • seo seminar 3:52 am on September 21, 2014 Permalink | Reply

      But a good job at localising content, the contestant would get them
      to leave rest of the color wheel from one of a variety of search engine optimization services to users operating
      a wide sets of applications.

    • website optimization tips 9:44 pm on September 22, 2014 Permalink | Reply

      In many cases, is a quality, successful web development company for
      your crucial decision. A strong referral relationship with their
      promising clients. Keep in mind of every household has an important role in either PPC or SEO when they click a button. Perhaps it is posted, and
      shows through in every web design courses is
      the only one using search engine optimization a commercial purpose.

    • search engine optimization promotion 1:18 pm on September 28, 2014 Permalink | Reply

      The industry is undergoing a sea change lead generation in this country.
      Hence there are still on your web site design. For instance, if mastered, will be
      competing against them.

    • Soundcloud Followers Free 8:02 pm on September 28, 2014 Permalink | Reply

      Wonderful blog! Do you have any hints for aspiring writers?
      I’m planning to start my own site soon but I’m a little lost on everything.
      Would you suggest starting with a free platform like WordPress or go
      for a paid option? There are so many options out there that I’m
      completely overwhelmed .. Any ideas? Thanks!

    • video seo optimization 7:10 pm on October 12, 2014 Permalink | Reply

      It creates movement and like you are left all
      alone along with sophisticated design. Along with major marketing, ecommerce, real estates and government scene.

      Let me first search engine optimization make sure they make are made through some
      scenarios for them the identical time working throughout the world.

    • M Byrd 11:47 am on February 20, 2015 Permalink | Reply

      I have created a config file, and an cmd script which includes D:\temp\mssql2008r2_Standard\ConfigurationFile.ini”. The output file in C:…bootstrap… shows my config file,but it appears to be reading some other config file

  • Ali BaderEddin 11:49 pm on September 18, 2010 Permalink | Reply
    Tags: Get-SPWebApplication, , Microsoft.SharePoint.PowerShell, Mount-SPContentDatabase, New-SPContentDatabase   

    Content DB Attach using PowerShell 

    In this post, I’ll talk about how to attach\mount a SharePoint content database to a SharePoint web application after it has been restored\attached to the SQL server. See my previous post for details on how to restore a database using PowerShell.

    SharePoint provides a cmdlet to do the content db attach. This cmdlet is called Mount-SPContentDatabase. I will create a script block wrapper for this cmdlet and show how to call it remotely. The script block below adds the SharePoint PowerShell snapin if it’s not already added, selects a web application in the farm, then calls the Mount-SPContentDatabase to do the content db attach.

    [ScriptBlock] $global:AttachSPContentDB =
        param([string] $dbName, [string] $dbServer)
            # Load the Sharepoint Cmdlets
            Write-Host "Loading SharePoint PowerShell Snapin"
            $spSnapin = Get-PSSnapin | where {$_.Name -eq "Microsoft.SharePoint.PowerShell"}
            if($spSnapin -eq $null)
                Add-PSSnapin Microsoft.SharePoint.PowerShell
                Write-Host -ForegroundColor "Green" -Object "SharePoint PowerShell Snapin loaded"
                Write-Host -ForegroundColor "Gray" -Object "SharePoint PowerShell Snapin already loaded"
            # Get  web applciation url
            Write-Host "Selecting a web application in the farm"
            $webAppUrl = (Get-SPWebApplication | select -Index 0).Url
            # Mount the database at the specified SQL server to the web application
            Write-Host "Mounting database '$dbName' at SQL server '$dbServer' to web application '$webAppUrl'"
            $newDB = Mount-SPContentDatabase $dbName -DatabaseServer $dbServer -WebApplication $webAppUrl -AssignNewDatabaseId
            Write-Host -ForegroundColor "Green" -Object "Database mounted successfully with ID: " + ($newDB.Id)
            # Return content DB Id
            return $newDB.Id.ToString()
        catch [Exception]
            Write-Host -ForegroundColor "Red" -Object $_.Exception
            return $null

    To call this script block, pass the name of the Sql server and the name of the content database. Make sure that the content database is already restored\attached to the SQL server.

    .$AttachSPContentDB "ContentDBName" "SqlServerName"

    To call the script block on a remote SharePoint WFE, you can simply run

    Invoke-Command -ComputerName "WFEName" -ScriptBlock { .$AttachSPContentDB "ContentDBName" "SqlServerName" }

    Note that the New-SPContentDatabase cmdlet has the same effect as Mount-SPContentDatabase. The only difference is that New-SPContentDatabase creates a new SQL database if there is no database with the same name already available on the SQL server.

    • warszawa 10:11 pm on September 11, 2014 Permalink | Reply

      Βardzo lubіęodcienie!

    • comprar galletas decoradas 1:05 pm on September 12, 2014 Permalink | Reply

      Ponemos la masa sobre papel de horno y la estiramos con el rodillo
      hasta tener un grosor de 4 mm. Con un cortapastas de seis cm de
      diámetro vamos haciendo las galletas.

  • Ali BaderEddin 6:38 pm on September 18, 2010 Permalink | Reply
    Tags: Add-PSSnapin, attach, database, , , Invoke-Sqlcmd, LogicalFileName, MSSQL, network path, New-PSSession, PhysicalFileName, PowerShell, ReadFileList, RelocateFiles, restore, scriptblock, SMO, Smo.Restore, Smo.Server, SqlRestore, SqlServerCmdletSnapin100, _Data.mdf, _Log.ldf   

    SQL DB Restore using PowerShell 

    If you are looking for a PowerShell script that lets you restore a database from a backup (.bak) file to MSSQL server, then this is the right place for you.

    Database Restore – SQL

    Let’s say you have a .bak file (Ex: AdventureWorks), and you would like to restore it to an SQL server using PowerShell. If you use SQL Server 2008 Management Studio and click on the “Script” button, you will get th SQL statement that will do that restore for you.

    Here is the SQL RESTORE statement that will allow you to restore the AdventureWorksLT2008R2.bak to the new database ‘NewDatabaseName’.

    RESTORE DATABASE [NewDatabaseName]
    FROM DISK = N'C:\.etc..\AdventureWorksLT2008R2.bak'
    WITH FILE = 1,
    MOVE N'AdventureWorksLT2008R2_Data'
    TO N'C:\.etc..\MSSQL\DATA\NewDatabaseName.mdf',
    MOVE N'AdventureWorksLT2008R2_Log'
    TO N'C:\.etc..\MSSQL\DATA\NewDatabaseName_1.ldf',

    If you use SQL Server 2008 R2 Management Studio, the generated SQL statement would look like this.

    RESTORE DATABASE [NewDatabaseName]
    FROM  DISK = N'C:\.etc..\AdventureWorksLT2008R2.bak'
    WITH  FILE = 1,  NOUNLOAD,  STATS = 10

    Running this statement is almost the same as the previous statement, except that the MDF and LDF files would be named AdventureWorksLT2008R2_Data.mdf and AdventureWorksLT2008R2_Log.ldf respectively (instead of having NewDatabaseName in the file names).

    If we want to create a simple PowerShell function to run the SQL Restore command, we can do so using the OSQL Utility:

    function global:RestoreDB ([string] $newDBName, [string] $backupFilePath)
        [string] $dbCommand = "RESTORE DATABASE [$newDBName] " +
        "FROM    DISK = N'$backupFilePath' " +
        "WITH    FILE = 1, NOUNLOAD, STATS = 10"
        OSQL.EXE -E -Q $dbCommand

    We can also take advantage of the Invoke-Sqlcmd command to do the same thing. Here is how the function would look like:

    function global:RestoreDB ([string] $newDBName, [string] $backupFilePath)
        [string] $dbCommand = "RESTORE DATABASE [$newDBName] " +
                              "FROM    DISK = N'$backupFilePath' " +
                              "WITH    FILE = 1, NOUNLOAD, STATS = 10"
        Invoke-Sqlcmd -Query $dbCommand

    If using Invoke-Sqlcmd fails, make sure the SqlServerCmdletSnapin100 snapin is added. You can put the below snippet in the function above before the call to Invoke-Sqlcmd.

    $sqlSnapin = Get-PSSnapin | where {$_.Name -eq "SqlServerCmdletSnapin100"}
    if($sqlSnapin -eq $null)
    Add-PSSnapin SqlServerCmdletSnapin100

    Calling this function is as simple as:

    RestoreDB "NewDatabaseName" "C:\.etc..\AdventureWorksLT2008R2.bak"

    Remote SQL Server

    In case your SQL Server is on a remote machine that doesn’t have the SQL client installed, you can use the Invoke-Command to restore the database on the remote machine. However, the function needs to be loaded to be invoked on the remote machine, so either put it in a separate file and load it remotely or convert it to a script block that can be directly passed to Invoke-Command.

    Assuming the remote SQL server name is SQLServer01, here are the two approaches to do this.

    Using a Function

    Put the RestoreDB function in a new PowerShell file; Let’s call it DatabaseRestore.ps1. To call remotely, first we need to create a PowerShell session on the remote SQL Server machine.

    $session = New-PSSession -ComputerName "SQLServer01"

    If you are using SQL Server 2008 R2, you might get the following error: “Connecting to remote server failed with the following error message : The WinRM client cannot complete the operation within the time specified. Check if the machine name is valid and is reachable over the network and firewall exception for Windows Remote Management service is enabled“. The resolution is located here. In short, go to your SQL Server 2008 R2 machine, launch the Command Prompt as administrator then run this command: winrm quickconfig.

    After creating the session, we need to load the DatabaseRestore.ps1 script on the remote machine to be able to call the RestoreDB function.

    $scriptPath = "C:\..etc..\DatabaseRestore.ps1"
    Invoke-Command -Session $session -FilePath $scriptPath

    If you get an error that the file cannot be loaded because the execution of scripts is disabled on the system, then you should make sure that the execution policy is remotesigned or unrestricted (Set-ExecutionPolicy unrestricted).

    Next step would be to simply call the function. Note that the path to the backup file must be accessible to the remote SQL machine.

    Invoke-Command -Session $session -ScriptBlock { RestoreDB "NewDatabaseName" "C:\..etc..\AdventureWorksLT2008R2.bak" }
    Using a Script Block

    In our case, using a script block is simpler. All we need to do is define the script block, then run one single Invoke-Command to do the database restore. The script block definition is almost the same as the function definition, as shown below:

    [ScriptBlock] $global:RestoreDB = {
        param ([string] $newDBName, [string] $backupFilePath)
        [string] $dbCommand = "RESTORE DATABASE [$newDBName] " +
                              "FROM    DISK = N'$backupFilePath' " +
                              "WITH    FILE = 1, NOUNLOAD, STATS = 10"
        $sqlSnapin = Get-PSSnapin | where {$_.Name -eq "SqlServerCmdletSnapin100"}
        if($sqlSnapin -eq $null)
             Add-PSSnapin SqlServerCmdletSnapin100
        Invoke-Sqlcmd -Query $dbCommand

    Make sure you don’t forget the equal sign (=) in the first line of the above script block definition. Now, to restore the DB, we call a single Invoke-Command cmdlet. Make sure that the -ArgumentList parameters are separated by comma (,) or the script will fail.

    Invoke-Command -ComputerName "SQLServer01" -ScriptBlock $RestoreDB -ArgumentList "NewDatabaseName", "C:\AdventureWorksLT2008R2.bak" 
    Function vs. Script Block

    Here are some simple guidelines on when to use a function and when to use a script block.

    • To call a function on a remote machine, it must first be defined on the remote machine. That’s why we need an Invoke-Command to load the PowerShell file containing the function, then another Invoke-Command to call the function.
    • To call a script block remotely, it doesn’t have to be defined on the remote machine. When the script block is passed as a parameter to Invoke-Command, the whole script block actually gets copied to the remote machine.
    • If your logic has any dependency on any global variable or function (Ex: it calls another script block or it calls a helper function), then it makes most sense to use a function. Define the function, global variables and helper functions in one file, then load them on the remote machine.
    • If your logic is self-contained, then use a script block. The whole logic will get copied to the remote machine.

    Database Restore – SMO

    To have more control on how we do the database restore, we should use the SMO object model. For example, our script above restores the database with name “NewDatabaseName” but the MDF and LDF file names are still AdventureWorksLT2008R2_Data.mdf and AdventureWorksLT2008R2_Log.ldf. Now, you would think, why not use the SQL statement below in our script?

    RESTORE DATABASE [NewDatabaseName]
    FROM DISK = N'C:\.etc..\AdventureWorksLT2008R2.bak'
    WITH FILE = 1,
    MOVE N'AdventureWorksLT2008R2_Data'
    TO N'C:\.etc..\MSSQL\DATA\NewDatabaseName.mdf',
    MOVE N'AdventureWorksLT2008R2_Log'
    TO N'C:\.etc..\MSSQL\DATA\NewDatabaseName_1.ldf',

    To put this in a script block, it will look as follows:

    [ScriptBlock] $global:RestoreDB = {
    param ([string] $newDBName, [string] $backupFilePath, [string] $sqlDataPath, [string] $dataLogicalName, [string] $logLogicalName)
    [string] $dbCommand = "RESTORE DATABASE [$newDBName] " +
    "FROM    DISK = N'$backupFilePath' " +
    "WITH    FILE = 1, " +
    "MOVE N'$dataLogicalName' " +
    "TO N'$sqlDataPath\$newDBName.mdf', " +
    "MOVE N'$logLogicalName' " +
    "TO N'$sqlDataPath\$newDBName.ldf', " +
    "NOUNLOAD, STATS = 10"
    $sqlSnapin = Get-PSSnapin | where {$_.Name -eq "SqlServerCmdletSnapin100"}
    if($sqlSnapin -eq $null)
    Add-PSSnapin SqlServerCmdletSnapin100
    Invoke-Sqlcmd -Query $dbCommand

    Ok, this might be good enough, but it’s not dynamic\generic. You can get the SQL data path from the registry, but you cannot get the logical names of the data and log files unless you find a way to open the .bak file and read the logical names of those files. Instead of hardcoding the logical names of the Data and Log files (i.e. pass “AdventureWorksLT2008R2_Data” and “AdventureWorksLT2008R2_Log”), we can read the file list from the .bak file using SMO (ReadFileList() method).

    PowerShell comments below provide enough details. Note that if the backup file is on a network share, the ReadFileList() method would fail. So in that case, we need to copy the file locally. That’s why the script block takes “$isNetworkPath” boolean variable to take that into consideration.

    [ScriptBlock] $global:RestoreDBSMO = {
        param([string] $newDBName, [string] $backupFilePath, [bool] $isNetworkPath = $true)
            # Load assemblies
            [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
            [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
            [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
            [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
            # Create sql server object
            $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"
            # Copy database locally if backup file is on a network share
                $fileName = [IO.Path]::GetFileName($backupFilePath)
                $localPath = Join-Path -Path $server.DefaultFile -ChildPath $fileName
                Copy-Item $backupFilePath $localPath
                $backupFilePath = $localPath
            # Create restore object and specify its settings
            $smoRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore")
            $smoRestore.Database = $newDBName
            $smoRestore.NoRecovery = $false;
            $smoRestore.ReplaceDatabase = $true;
            $smoRestore.Action = "Database"
            # Create location to restore from
            $backupDevice = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($backupFilePath, "File")
            # Give empty string a nice name
            $empty = ""
            # Specify new data file (mdf)
            $smoRestoreDataFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
            $defaultData = $server.DefaultFile
            if (($defaultData -eq $null) -or ($defaultData -eq $empty))
                $defaultData = $server.MasterDBPath
            $smoRestoreDataFile.PhysicalFileName = Join-Path -Path $defaultData -ChildPath ($newDBName + "_Data.mdf")
            # Specify new log file (ldf)
            $smoRestoreLogFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
            $defaultLog = $server.DefaultLog
            if (($defaultLog -eq $null) -or ($defaultLog -eq $empty))
                $defaultLog = $server.MasterDBLogPath
            $smoRestoreLogFile.PhysicalFileName = Join-Path -Path $defaultLog -ChildPath ($newDBName + "_Log.ldf")
            # Get the file list from backup file
            $dbFileList = $smoRestore.ReadFileList($server)
            # The logical file names should be the logical filename stored in the backup media
            $smoRestoreDataFile.LogicalFileName = $dbFileList.Select("Type = 'D'")[0].LogicalName
            $smoRestoreLogFile.LogicalFileName = $dbFileList.Select("Type = 'L'")[0].LogicalName
            # Add the new data and log files to relocate to
            # Restore the database
            "Database restore completed successfully"
        catch [Exception]
            "Database restore failed:`n`n " + $_.Exception
            # Clean up copied backup file after restore completes successfully
                Remove-Item $backupFilePath

    Here is how to call this script block (Don’t forget the dot (.) infront of the script block variable):

    .$RestoreDBSMO "NewDatabaseName" "C:\.etc..\AdventureWorksLT2008R2.bak" $false

    Same story goes for calling this function remotely.

    • Peter 3:04 am on January 29, 2014 Permalink | Reply

      I ran into an issue with “Invoke-Sqlcmd -Query $dbCommand” but fixed it with
      Invoke-Sqlcmd -Serverinstance ‘servername\instance’ -Query $dbCommand

    • detox isnt 3:26 pm on July 24, 2014 Permalink | Reply

      Hi there mates, how is everything, and what you desire to say regarding this article, in my view
      its genuinely remarkable in support of me.

Compose new post
Next post/Next comment
Previous post/Previous comment
Show/Hide comments
Go to top
Go to login
Show/Hide help
shift + esc

Get every new post delivered to your Inbox.

%d bloggers like this: