There are many reasons for moving a database from one SQL Server instance to another instance, for example:
RESTORE HEADERONLY FROM DISK = 'R:\mydb.bak'
RESTORE FILELISTONLY FROM DISK = 'R:\mydb.bak'
WITH FILE = 1
RESTORE DATABASE mydb FROM DISK = 'R:\mydb.bak'
MOVE 'mydb' TO 'C:\mydb.mdf'
,MOVE 'mydb_log' TO 'C:\mydb_log.ldf'
FROM sys.database_principals AS d
WHERE NOT EXISTS
FROM sys.server_principals AS s
WHERE s.sid = d.sid
AND type_desc = 'SQL_USER'
AND name NOT IN('guest', 'INFORMATION_SCHEMA', 'sys')
ALTER USER Joe WITH LOGIN = Joe
WHERE name NOT IN ('syspolicy_purge_history')
SELECT * FROM sys.configurations
SELECTThe database has a collation, which among other things serves as default collation when you create tables. The instance also has a collation, which acts as a default collation for when a new database is created. Both can be overridden using the COLLATE clause. You want to make sure that moving the database from one instance to another won't cause any collation related problems (like "collation conflict" errors in your application). You can check the server collation using SERVERPROPERTY('Collation'). You see the database collation in sys.databases and also in the output from RESTORE HEADERONLY.
SERVERPROPERTY('Collation') AS server_collation
,DATABASEPROPERTYEX(DB_NAME(),'Collation') AS database_collation
SELECTThe login who performs the restore, attaches or creates the destination database will be the owner of the database, as seen from the instance (master database) perspective. You can see this in the sid column in sys.databases. However, if you use a binary method to move the database (restore or attach), then the owner (according to the database) will be the same as before (possibly an orphaned user, see above). A problem is if the login who used to own the database (which on the old instance can access the database as the dbo user, being the owner) is no longer the owner on the new instance. I.e., make sure that the login that used to own the database will be able to access it in the destination instance, with proper privileges. I typically have sa as owner for most of my databases. Here's an example for how to change owner for a database:
(SELECT SUSER_SNAME(owner_sid) FROM sys.databases WHERE database_id = DB_ID()) AS owner_according_to_master
,(SELECT SUSER_SNAME(sid) FROM sys.database_principals WHERE name = 'dbo') AS owner_according_to_the_app_database
ALTER AUTHORIZATION ON DATABASE::AdventureWorksDW2008R2 TO sa
SELECT * FROM sys.servers WHERE is_linked = 1
WHERE message_id > 50000
SELECT nameIt is very unlikely that your code extracts an email address based on an operator name using above table in msdb and then uses that email with for instance sp_send_dbmail. Unlikely, but possible.
SELECT nameAlerts aren't tied to a specific databases (well, it can be limited to a certain database...), so your app won't stop working if you don't carry over your alerts. But it is likely that you also want Alerts on the destination server if you have such on the source server.
SELECT * FROM sys.filegroups WHERE type_desc= 'FILESTREAM_DATA_FILEGROUP'
SELECT * FROM sys.database_files WHERE type_desc= 'FILESTREAM'