SQL DBs Copy

Zach Flesher 26 Reputation points
2022-02-09T21:26:10.267+00:00

I am new to SQL and I am in the process of copying a DBs from one Instance to a new instance on the same server. Every time I try and copy the DBs I get an Invalid class string. Below are the details.

===================================

Invalid class string
(Copy Database Wizard)


Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.FolderExistsOnSqlServer(String strFolderName, String strServerName, String strServerUserName, String strServerPassword)
at Microsoft.SqlServer.Management.CopyDatabaseWizard.PackagePage.EnsurePackageFolderExist(String folderName, Application app, String serverName, String loginName, String password)
at Microsoft.SqlServer.Management.CopyDatabaseWizard.PackagePage.GetUniquePackageName()

===================================

Invalid class string


Program Location:

at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.FolderExistsOnSQLServer(String bstrFolderName, String bstrServerName, String bstrServerUserName, String bstrServerPassword)
at Microsoft.SqlServer.Dts.Runtime.Application.FolderExistsOnSqlServer(String strFolderName, String strServerName, String strServerUserName, String strServerPassword)

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,666 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Vinicius Ribeiro 1 Reputation point
    2022-02-09T21:34:41.717+00:00

    Hey @Zach Flesher ,

    If you're talking about 2 SQL Server On Premisse Instances, much easier to Backup from the Origin and Restor into the new Instance.

    You can use SSMS to facilitate that or even the TSQL commands

    Backup

    BACKUP DATABASE AdventureWorks2012
    TO DISK = 'Z:\SQLServerBackups\AdvWorksData.bak'
    WITH FORMAT;
    GO

    Restore

    RESTORE DATABASE AdventureWorks2012
    FROM AdventureWorks2012Backups;

    Refer to
    https://learn.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15
    https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver15#syntax

    0 comments No comments

  2. Erland Sommarskog 100.9K Reputation points MVP
    2022-02-09T22:29:43.013+00:00

    xh1d3x is right on track. The Copy Database Wizard is not any fun. It's a lot better to learn how to master BACKUP/RESTORE.

    When it comes to the BACKUP command, always use WITH FORMAT or WITH INIT. Else you will append a backup to an existing file. This can be useful, but more often it leads to confusion when you mistaken restore an old backup.

    The RESTORE command in your case has to be a little more complicated. Say that your database is called MyDB. Then the command would be:

    RESTORE DATABASE MyDB_copy FROM DISK = 'C:\Temp\MyDB.bak'
    WITH MOVE 'MyDB' TO   '<path>\MyDb_copy.mdf',
         MOVE 'MyDB_log' TO  '<path>\MyDb_copy.ldf'
    

    The name that follows MOVE is the logical name of the file, and is stored inside the database, and does not change when you copy the database. <path> is where you want the database to be located. Run sp_helpdb MyDB to get the path of the current database Copy that into the command and change the file names.


  3. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2022-02-10T02:31:26.617+00:00

    Hi ZachFlesher-3048,
    Welcome to Microsoft Q&A.

    I can look this up but is there an easy way to give the new instance permissions to this .bak file and the files associated with it?

    As Erland said, please right-click D:\Database folder, select Properties -> Security tab, then check if the SQL Server service account have full permission to access the file. If not, you can click Edit to change the permission or add the account.

    172973-image.png

    172974-image.png

    Regards,
    Amelia

    0 comments No comments