Hi Bhavesh,

Keep in mind that SQL Server Management Studio is just a GUI and it could be installed on a (Citrix-)server without the SQL Database engine. From SSMS you can connect to any server where the database engine is running, for as far firewall and permissions are set correctly. So it could easily be that the database you are connecting to is not on the Citrix server where you have started SSMS.

So first of all: you'll need to know the servername of the database you connect to (i.e. source server; you mention connect from Citrix). Next you'll need to know the servername of the database server where you want to copy the data to (i.e. target server; you mention as local server). On the source server you can create and schedule a job that creates a backup of the database. Let it create a backupfile to a general location (share) where you have access to from both the source and the target server. On the target server you can setup and schedule a job to restore the database using the backup file on the share.

Keep the name of the backupfile unchanged for each backup taken, just replace the existing file. That way you can create a fixed BACKUP and RESTORE command without the need to dynamically use a different backupfilename each time. Restore the database using the WITH REPLACE option to overwrite the existing database. Keep sufficient time between the start of the backup and the start of the restore. You cannot start the restore untill the backup is finsished.

If you don't want to copy the complete database over from the source to the target server you can look at creating a SSIS package to only copy selected tables/rowsets between the databases.