There are multiple reasons why a user may want to make a copy of the database. Sometimes a user wants to copy the database to the same server and sometime wants to copy the database on a different server. The important point is that DBA and Developer may want copies of their database for various purposes. I copy my database for backup purpose. However, when we hear coping database – the very first thought which comes to our mind is – Backup and Restore or Attach and Detach. Both of these processes have their own advantage and disadvantages. The matter of the fact, those methods is much efficient and recommended methods.
However, if you just want to copy your database as it is and do not want to go for advanced feature. You can just use the copy feature of the SQL Server. Here are the settings, which you can use to copy the database.
SQL in Sixty Seconds Video
I have attempted to explain the same subject in simple words over in following video.
Here are the blog posts I have previously written on the subject of SA password. You can read it over here:
- Copy Database from Instance to Another Instance – Copy Paste in SQL Server
- Copy Database With Data – Generate T-SQL For Inserting Data From One Table to Another Table
- Copy Data from One Table to Another Table – SQL in Sixty Seconds #031 – Video
- Generate Script for Schema and Data – SQL in Sixty Seconds #021 – Video
You can subscribe to my YouTube Channel for frequent updates.
Reference: Pinal Dave (https://blog.sqlauthority.com)
One more reason is why we should use Copy Database is if we want to downgrade the database from high end version to low end version. As we know that the backup-restore cannot work because of format mismatch between different versions. Here in my organization one peculiar case was to restore SQL 2012 database .bak file on 2008 R2 version and for that I had used Copy Database task.