SQLMint
You cant restore a database backwards from SQL Server 2008 to a SQL 2005 Instance.
One trick you can use is to script the entire database, and ensure you select in the advanced options as shown below, SQL 2005.
Then run the script at the destination SQL 2005 instance.
Remember to change the database file paths in the output SQL script as needed.
Issue:
A restored database from one instance to the other keeps showing below when attempting to say, alter a stored procedure.
The database owner SID recorded in the master database differs from the database owner SID recorded in database
Solution applied: Assuming XYZ is the name of the database and contosoadmin is the name of the sa or sysadmin domain account usually assigned as owner in the destination SQL instance. contoso here represents the domain name.
Run:
ALTER DATABASE XYZ SET TRUSTWORTHY ON
ALTER AUTHORIZATION ON Database::archive TO [contosoadmin]
Quick steps to restore the SQL Server system database – msdb from a valid backup file, especially useful if you run SSIS packages stored in the msdb database and SQL server agent jobs. Doing this will bring back all SSIS packages as well as existing job schedules.
– Set msdb database to restricted access
USE [master]
GO
ALTER DATABASE [msdb] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
GO
– shut down the SQL server agent service for the SQL instance you are running
– restore the msdb database from a good backup file
– restart the SQL server agent service