How to Copy Database in SQL Server, Wizard vs Backup / Restore Script?
I am working on a live SQL Server database that 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.n 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 the script as opposed to the CDW? Don’t know how to copy database in sql server?
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 databaseCopy 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: Hope the above helps you to copy the database in sql server.