0

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

Improve this question
2
  • Do you want to move your database? or just have a copy at the other server?
    – Stijn Wynants
    Commented Jul 31, 2018 at 9:46
  • just a copy at the other Server.
    – Yasser Z. Abbass
    Commented Jul 31, 2018 at 10:31

1 Answer 1

Reset to default
1

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.

Improve this answer
7
  • i have to do that for nearly 50 databases with nearly 1TB size for total, the only reason i turn to copy database method that i need them to be synchronized with the minimum downtime. so this method won't hep in my case. thanks anyway but i do prefer suggestions to solve the error.
    – Yasser Z. Abbass
    Commented Jul 31, 2018 at 10:53
  • You need to do this on a regular basis? Or just the one time?
    – Stijn Wynants
    Commented Jul 31, 2018 at 10:57
  • two times at least, One source --> two destinations.
    – Yasser Z. Abbass
    Commented Jul 31, 2018 at 11:01
  • Do the databases exist on the other two servers? Does the data have to kept in sync afterwarts, or is just the db copy enough?
    – Stijn Wynants
    Commented Jul 31, 2018 at 11:03
  • for the 1st Q : on one destination server yes, and on the other No.
    – Yasser Z. Abbass
    Commented Jul 31, 2018 at 11:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.