SQL Server 2005 Restore One Schema Only

Is it possible to restore only the schema and stored procedures from a MS SQL database backup?

I don't think so, but you could restore it to a temporary database and then script it all from there.

How to do a partial database backup and restore?

You could create a script that copies each of the schemas to a separate database (backup_Accounting, backup_Secretary, backup_Shared), and then creates a backup file for each of those databases. If you ever need to do a restore, you can restore the backup file into the appropriate database and then run a script to copy the data back into the main DB.

SQL Server database backup restore on lower version

No, is not possible to downgrade a database. 10.50.1600 is the SQL Server 2008 R2 version. There is absolutely no way you can restore or attach this database to the SQL Server 2008 instance you are trying to restore on (10.00.1600 is SQL Server 2008). Your only options are:

upgrade this instance to SQL Server 2008 R2 or
restore the backup you have on a SQL Server 2008 R2 instance, export all the data and import it on a SQL Server 2008 database.

How to recover database from MDF in SQL Server 2005?

I found the following document on Experts Exchange.

patrikt:
You will have data loss but it can be done.


1. Detach database and move your mdf to save location.
2. Create new databse of same name, same files, same file location and same file size.
3. Stop SQL server.
4. Swap mdf file of just created DB to your save one.
5. Start SQL. DB will go suspect.
6. ALTER DATABASE yourdb SET EMERGENCY
7. ALTER DATABASE yourdb SET SINGLE_USER
8. DBCC CHECKDB (yourdb, REPAIR_ALLOW_DATA_LOSS)
9. ALTER DATABASE yourdb SET MULTI_USER
10. ALTER DATABASE yourdb SET ONLINE

How to restore to a different database in SQL Server?

You can create a new db then use the "Restore Wizard" enabling the Overwrite option or:

View the contents of the backup file:

RESTORE FILELISTONLY FROM DISK='c:\your.bak'

note the logical names of the .mdf & .ldf from the results, then:

RESTORE DATABASE MyTempCopy FROM DISK='c:\your.bak'
WITH 
   MOVE 'LogicalNameForTheMDF' TO 'c:\MyTempCopy.mdf',
   MOVE 'LogicalNameForTheLDF' TO 'c:\MyTempCopy_log.ldf'

This will create the database MyTempCopy with the contents of your.bak.

(Don't create the MyTempCopy, it's created during the restore)

Example (restores a backup of a db called 'creditline' to 'MyTempCopy'):

RESTORE FILELISTONLY FROM DISK='e:\mssql\backup\creditline.bak'

>LogicalName
>--------------
>CreditLine
>CreditLine_log

RESTORE DATABASE MyTempCopy FROM DISK='e:\mssql\backup\creditline.bak'
WITH 
   MOVE 'CreditLine' TO 'e:\mssql\MyTempCopy.mdf',
   MOVE 'CreditLine_log' TO 'e:\mssql\MyTempCopy_log.ldf'

>RESTORE DATABASE successfully processed 186 pages in 0.010 seconds (144.970 MB/sec).