Use PowerShell to Migrate SQL Server Instances (db, logins, jobs, etc)

Migrates databases with simple or complex file structures via backup & restore or detach & attach. Also migrates logins with passwords, SIDs, server & db permission sets, server & db roles, job server, credentials, linked servers, server triggers, backup devices, CMSs..

 
 
 
 
 
4.9 Star
(49)
6,175 times
Add to favorites
Databases
6/21/2016
E-mail Twitter del.icio.us Digg Facebook
Sign in to ask a question


  • Module could not be loaded
    1 Posts | Last post May 24, 2017
    • I installed dbatools using
      
      Install-Module dbatools
      
      and that went fine but when I try to execute
      
      Copy-SqlLogin -Source oldserver -Destination newserver
      
      I get the error
      
      Copy-SqlLogin : The 'Copy-SqlLogin' command was found in the module 'dbatools', but the module could not be loaded.
      For more information, run 'Import-Module dbatools'.
      At line:1 char:1
      + Copy-SqlLogin -Source oldserver -Destination newserver
      + ~~~~~~~~~~~~~
          + CategoryInfo          : ObjectNotFound: (Copy-SqlLogin:String) [], CommandNotFoundException
          + FullyQualifiedErrorId : CouldNotAutoloadMatchingModule
  • Copy-SqlLogin with -Logins and -SyncOnly parameters -> Error
    1 Posts | Last post January 05, 2017
    • Hi Chrissy,
      
      When you use Copy-SqlLogin with -Logins and -SyncOnly parameters you'll get the next error:
      Get-ParamSqlLogins : Cannot process argument transformation on parameter 'SqlCredential'. Cannot convert the
      "System.Collections.Hashtable" value of type "System.Collections.Hashtable" to type
      "System.Management.Automation.PSCredential".
      
      This error is displayed by Sync-SqlLoginPermissions.ps1 because from Copy-SqlLogin you call this script with $loginparms and this is a "System.Collections.Hashtable" value.
      
      Thanks.
  • WARNING: Can't access remote Sql directories
    1 Posts | Last post December 01, 2016
    • Hi
      Great tool. I am using Copy_SQLDatabase. I am getting above error, even though I can map the directories on the server, that I am running the function. Appreciate your help in this regard. Thanks
  • Copy-SqlDatabase for only one DB
    2 Posts | Last post November 30, 2016
    • Hi,
      
      I just discovered dbatool, and it looks great. I have a question however with Copy-SqlDatabase. Is it possible to only copy one specific database from one sql server to another. When I did "get-help -Name Copy-SqlDatabase -Full" I see in example 1 a parameter called -Database which seems to indicate this, however in the list of parameters when i type out the syntax it's not available. It seems that i can only copy all non-support db's or everything including support db's.
      
      How do you go about doing this for only 1 db, say because you need to upgrade/migrate it do to vendor support?
    • Hey John,
      Yep, it works with just one database. Please update -- I just fixed a population bug. It doesn't show up in Get-Help even though the documentation is there. I didn't know that. PowerShell's native dynamic params aren't great, so we'll be switching to TabExpansion++. Should be by this time next month --its our most often asked question.
  • The term 'git' is not recognized as the name of a cmdlet,
    1 Posts | Last post November 08, 2016
    • PS C:\Users\Hp> git clone https://github.com/sqlcollaborative/dbatools
      git : The term 'git' is not recognized as the name of a cmdlet, function,
      script file, or operable program. Check the spelling of the name, or if a path
      was included, verify that the path is correct and try again.
      At line:1 char:1
      + git clone https://github.com/sqlcollaborative/dbatools
      + ~~~
          + CategoryInfo          : ObjectNotFound: (git:String) [], CommandNotFound
         Exception
          + FullyQualifiedErrorId : CommandNotFoundException
      
  • Import-Module : module cannot be imported because its manifestcontains one or more member
    1 Posts | Last post November 07, 2016
    • Source server powershell 2.0 
      destination server powershell 3.0 
      
      The modules are getting imported in 3.0 but not importing in powershell 2.0
      PS E:\dbatools-master> import-module .\dbatools.psd1
      Import-Module : The 'E:\dbatools-master\dbatools.psd1' module cannot be imported because its manifest contains one or m
      ore members that are not valid. The valid manifest members are ('ModuleToProcess', 'NestedModules', 'GUID', 'Author', '
      CompanyName', 'Copyright', 'ModuleVersion', 'Description', 'PowerShellVersion', 'PowerShellHostName', 'PowerShellHostVe
      rsion', 'CLRVersion', 'DotNetFrameworkVersion', 'ProcessorArchitecture', 'RequiredModules', 'TypesToProcess', 'FormatsT
      oProcess', 'ScriptsToProcess', 'PrivateData', 'RequiredAssemblies', 'ModuleList', 'FileList', 'FunctionsToExport', 'Var
      iablesToExport', 'AliasesToExport', 'CmdletsToExport'). Remove the members that are not valid ('RootModule'), then try
      to import the module again.
      At line:1 char:14
      + import-module <<<<  .\dbatools.psd1
          + CategoryInfo          : InvalidData: (E:\dbatools-master\dbatools.psd1:String) [Import-Module], InvalidOperation
         Exception
          + FullyQualifiedErrorId : Modules_InvalidManifestMember,Microsoft.PowerShell.Commands.ImportModuleCommand
      
      
      what is the resolution
  • Logins
    2 Posts | Last post September 21, 2016
    • Hi Chrissy.
      
      Is there any way to just migrate the logins that belong to a particular database when you migrate the database?
      I saw the option :
      Copy SqlLogin -source sql01 -Destination sql02 -Logins user1 , user2 , User3 , etc. But if you have like a hundred logins !? Would be nice to just " say " i want to migrate login That belongs to "this " database .
      
      many regards
    • Bump
  • Export-SqlLogin typo in help?
    1 Posts | Last post August 19, 2016
    • Hi,
      
      Trying out the Export-SqlLogin I kept getting the error "Cannot bind positional parameters because no names were given." I looked at the source code and the file name parameter should apparently be -FilePath not -FileName as the examples have it.
      
      Great scripts, thanks! 
  • Errors with ReuseSourceFolderStructure and Job Migration
    1 Posts | Last post August 03, 2016
    • Chrissy, many thanks for your beautifully crafted tool. 
      
      During my tests I encountered following errors:
      
      Building file structure inventory for 17 databases
      
      ######### Database: Archive_QA #########
      Start-SqlMigration : Database migration reported the following error Cannot validate argument on parameter 'filepath'.
      The argument is null or empty. Provide an argument that is not null or empty, and then try the command again.
      At line:1 char:1
      + Start-SqlMigration -DetachAttach -Reattach -Force -ReuseSourceFolderS ...
      + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
          + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
          + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Start-SqlMigration
      
      
      Start-SqlMigration : Job Server migration reported the following error The regular expression pattern
      'STWSQL\MSSQLPRIOSTEST13' is not valid.
      At line:1 char:1
      + Start-SqlMigration -DetachAttach -Reattach -Force -ReuseSourceFolderS ...
      + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
          + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
          + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Start-SqlMigration
      
      Could this be related to having multiple instances?
      
      Apart from that everything is fine.
      
      Cheers
      Gerardo
      
  • Error while Collecting Credential logins and passwords
    1 Posts | Last post May 27, 2016
    • Hi at first i want to say that i love your script, but i have a Problem with it.
      This are my Start Prameters: 
      Start-SqlMigration -Source xxx-Destination xxx\xxx-BackupRestore -NetworkShare \\xxx\xxx -Force
      The Script is starting and i get the first error Message at:
      Collecting Credential logins and passwords on xxx 
      TerminatingError(): "Can't open DAC connection :("
      >> TerminatingError(): "Can't establish DAC connection to mymedissqlcra from mymedissqlcra. Quitting."
      
      Second Error:
      Collecting Linked Server logins and passwords on 
      Ausnahme beim Aufrufen von "Open" mit 0 Argument(en):  "Netzwerkbezogener oder instanzspezifischer Fehler beim Herstellen einer Verbindung mit SQL Server. Der Server wurde nicht gefunden, oder auf ihn kann nicht zugegriffen werden. Überprüfen Sie, ob der Instanzname richtig ist und ob SQL
      erver Remoteverbindungen zulässt. (provider: SQL Network Interfaces, error: 42 - Es konnte keine Dedicated Administrator Connection (DAC) auf dem
      Standardport eingerichtet werden. Vergewissern Sie sich, dass DAC aktiviert ist.)"
      
      i hove you can help
      
      Thanks,
      Daniel
1 - 10 of 60 Items