In SSMS when you right click on any database, under Tasks, there is option to Copy Database. One of the things the wizard asks you is whether you are copying or moving the database. It basically saves a package and runs it.

I'm on the server where the job was created, and which is also the destination server. I need to find out whether the database was copied, or moved, from the source server. How can I find that out (other than of course checking the source server)?

  • 1
    U mean restore of database or table transfers ? Restores are all logged into msdb .. restore history table. – Kin Shah Nov 17 '16 at 20:20
  • Not table transfers for sure. The entire database was copied using the options I mentioned above. – joym8 Nov 17 '16 at 20:35
  • Please caliify if your source and destination server is one and the same, If that is the case, then what kind of requirement is this that you need to copy the same db in the same instance. OR it is a different instance – Koustuv Chatterjee Nov 18 '16 at 7:23
  • Never said source/destination are the same server. Two totally different servers, one in cloud, one on-prem – joym8 Nov 18 '16 at 14:53

I think you can keep an eye on below kind of alerts to find out any change in the database movement:

EXEC sys.sp_readerrorlog @p1 = 0, @p2 = 1, @p3 = N'OFFLINE';
EXEC sys.sp_readerrorlog @p1 = 0, @p2 = 1, @p3 = N'ONLINE';

Message ID of 5084 if you're monitoring database option changes

You would indeed see a logged message in the SQL Server error log:

Setting database option OFFLINE to ON for database 'TargetDBName'.

Setting database option ONLINE to ON for database 'TargetDBName'.

Why I say is the process includes :

Issue an ALTER DATABASE command using the SET OFFLINE option to take the database off-line.

2.Move the files to their new locations.

3.Issue the appropriate ALTER DATABASE commands using the MODIFY FILE option to tell SQL Server where the new locations are.

4.Issue an ALTER DATABASE command using the SET ONLINE option to bring the database back on-line.

Finally you can cross check the below command and match the timings of creations date and the log dates:

SELECT Name,create_date, state_desc FROM sys.databases

You can even rely on Profiler traces to find out the changed locations of the existing Database files to another location and you can keep a point on who changed it too.

Hope this helps a bit...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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