MS SQL Server Copy Database Wizard vs Backup / Restore Script

Question

I am working on a live SQL Server database. It was just recently migrated from another server. In the old server the backup was done using a job with Copy Database Wizard. When the database was migrated to the new server, the job wasn't restored along with the database, so the new database doesn't automatically run a backup job.

I'm not familiar with the copy database wizard, so my question would be is there any disadvantage of running a job with sql script using BACKUP and RESTORE as opposed to the Copy Database Wizard ? Is there any risk if I run the backup using script as opposed to the CDW ?

• Shanky: At any given day I would prefer backup restore as compared to copy database wizard. The latter has so many issues
• senel34: @Shanky in the old server (before I had access / managed it) a .bak file would automatically be generated. Is the .bak file generated through backup command or is it possible for CDW to have generated the bak file also?
• Tibor Karaszi: CDW can operate in two different modes. One is that it performs detach/attach. I.e., it copies the database files. Obviously not an on-line operation. The other mode is that it scripts out the CREATE commands to build your database and then uses BCP for the data. So, no SQL server backup file option.

Answer 1

Jobs are not stored in the user database, it is stored in the msdb database. If you wish to copy the exact same jobs from the previous server then, you should think of taking backup of msdb at the previous server and restore them on the new server provided few pre-requisites are met. CDW(Copy database Wizard) can't be used for system database and its applicable for only user database as described on MS site and mentioned:

Limitations and restrictions

The Copy Database Wizard is not available in the Express edition.

The Copy Database Wizard cannot be used to copy or move databases that:

Are System.

Are marked for replication.

Are marked Inaccessible, Loading, Offline, Recovering, Suspect, or in Emergency Mode.

Have data or log files stored in Microsoft Azure storage.

When using FileTables, you can't use the Copy Database Wizard on the same server because the wizard uses the same directory name.

A database cannot be moved or copied to an earlier version of SQL Server.

You can read more about SQL jobs at below links:

https://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/

https://www.mssqltips.com/sqlservertip/4921/queries-to-inventory-your-sql-server-agent-jobs/

As far as prerequisite for restoring msdb is concerned, they are listed as below:

Get the version of destination server

Get the version of source server on which the backup was created Match the versions for the source and destination servers

Ensure exclusive access to the database

Copy database has below advantages:

• Pick a source and destination server.
• Select databases to move, copy or upgrade.
• Specify the file location for the databases.
• Create logins on the destination server.
• Copy additional supporting objects, jobs, user-defined stored procedures, and error messages.
  • Schedule when to move or copy the databases.

Database backup save the database to a file in the OS file system (like a backup). You then can copy those files and import them whenever and wherever you want.

It seems when database copying was done using CDW, msdb jobs were not chosen and you are having issue of DB backup which was running on previous server on the below screen:

enter image description here

Hope above helps.

Answer 2

I suggest using regular SQL jobs, using the Ola Hallengren solutions for your backups.
Ola hallengren has a full (free) maintenance solution that you can download and deploy on your server. ola.hallengren.com
It's used by a lot of DBAs, and has been around for years.

All you have to do is deploy the scripts and add some schedules, to your full/diff and log backups job. You can also fine tune everything to fit your needs. There's code examples and options on the Ola site. For example this link gives you all the options for the backup SP.

A big benefit you get from this, is that you can simply script out everything, and deploy it on any server you want, without much hassle.

Another nice to have, is that it also includedes jobs like IndexMaintenance and IntegrityChecks, which are needed on any SQL Instance. So it's a good all in one solution.