SQL DBs Copy

Question

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)

Answer 1

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

Answer 2

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.

Answer 3

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.

Regards,
Amelia