Sql-server – Microsoft SQL Server error 14545

sql serversql-server-2008sql-server-2016

I was trying to use copy Database wizard from one server to another to make a certain database identical and i got the error

(The @flags parameter is not valid for a job step of type 'SSIS'. (Microsoft SQL Server, Error: 14545)).

Some info:

  • The Source Server is SQL Server 2008 R2 and the destination is SQL Server 2016.

  • On Transfer Method I selected the second option (use the sql management object method) to keep the source database online.

Any advice on how to solve this or where to check will be appreciated.

enter image description here

Best Answer

Since you want to copy your database to another server I'd suggest doing this by the concept of backup restore. This is example code but you can easily also use the GUI (http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backups), make sure that the service account has access the place you are going to write your backup too

BACKUP DATABASE [YourDatabaseName] TO  DISK = N'\\someshare\YourDatabase.bak' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'YourDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

and then Restore the database on the destination server, also here make sure that the service account of SQL Server has access to the location where the backup is stored

USE [master]
RESTORE DATABASE [YourDatabase] 
FROM  DISK = N'\\someshare\YourDatabase.bak' WITH  FILE = 1,  
MOVE N'DatabaseFileName' TO N'SomeDisk\YourDatabase.mdf',  
MOVE N'DatabaseFileName2' TO N'SomeDisk\YourDatabase.ndf',  
MOVE N'N'DatabaseLogFileName' TO N'SomeDisk\YourDatabase.ldf',  NOUNLOAD,  STATS = 5

Or you can use DBA-Tools to do it for you using powershell

Check out https://dbatools.io/functions/backup-dbadatabase/ & https://dbatools.io/functions/restore-dbadatabase/

EDIT

Using dba-tools you can easily script this for a list of databases.

Because you want minimum of downtime, I suggest to restore the full db-backup to the server & leave it in recovery mode, and then start applying all logs to the database in recovery until you have all data in sync which you want in sync and restore the last log with recovery

For the destination server where you already have the databases on, I would suggest restoring the db's to a different name and then renaming the old db when you have restored the database to that side. This way the downtime will only be the rename of the db, which will require a DB level lock.

The way of Backup restore is more reliable, especially for the amount of data that has to be copied.

Related Question