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,453 times
Add to favorites
Databases
6/21/2016
E-mail Twitter del.icio.us Digg Facebook
Sign in to ask a question


  • Timeout when trying to connect to named instance
    1 Posts | Last post November 10, 2017
    • I'm trying to migrate a named instance, and I'm getting a timeout when I run start-sqlmigration.  I get the same timeout in SSMS, but if I increase the timeout to 25 seconds in SSMS, it connects.  Is there any way to specify a timeout parameter when running the start-sqlmigration command?
  • Would like to enhance script Copy-SqlLogin to drop all login before creatin or recreation.
    2 Posts | Last post October 24, 2017
    • Hi ,
      
      The script Copy-SqlLogin is really helpful for me to transfer login to DR on regular basis. Can you please assist me setting up the dropping of login already exists in destination. So i don't need to use force option each time. This will drop all login and recreate all using "Copy-SqlLogin".
    • I was able to figure it out on my own. Great scripts though.
  • 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! 
1 - 10 of 61 Items