How to find the scheduled subscriptions status on Report Server

If you deplyed reports on Report Server then you need to setup subscriptions to send an reports(pdf,excel) to users by email but how do you find scheduled reports send to user or not.

Use ReportServer
go
SELECT SubscriptionID,Description,LastStatus,EventType,
LastRunTime,DeliveryExtension,Version
FROM Subscriptions

SQL Server Code name DENALI CTP3

The future version SQL Server Denali is available here to download

http://www.microsoft.com/sqlserver/en/us/default.aspx

Subscriptions cannot be created because the credentials used to run the report are not stored or if a linked report the link is no longer valid

Always you should setup credentials stored securely in the report server for the report before you try to setup subscriptions to send report email with pdf format.

Report Manager, select your report.  Click the Properties tab, then select 'Data Sources' on the left side of the page.  Make sure the 'A custom data source' is the radio button selected, then click the radio button for 'Credentials stored securely in the report server'.  Type in your credentials to run the report and if it's a domain account, then check the box for 'Use as Windows credentials when connecting to the data source'.  Click Apply at the bottom and you should be able to create a subscription.

How to check AUTO_CREATE_STATISTICS is enabled for database

use master
go
SELECT name AS 'Name',
    is_auto_create_stats_on AS "Auto Create Stats",
    is_auto_update_stats_on AS "Auto Update Stats",
    is_read_only AS "Read Only"
FROM sys.databases
WHERE database_ID > 4;
GO
-- Also you can check list of statistics created on your database
use Mydb
go
SELECT OBJECT_NAME(s.object_id) AS object_name,
    COL_NAME(sc.object_id, sc.column_id) AS column_name,
    s.name AS statistics_name
FROM sys.stats AS s Join sys.stats_columns AS sc
    ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
WHERE s.name like '_WA%'
ORDER BY s.name;

When updating statistics with UPDATE STATISTICS or sp_updatestats, Microsoft recommend keeping
AUTO_UPDATE_STATISTICS set to ON so that the query optimizer continues to routinely update statistics.

Find the datbase restore history for your server

-- Find the restore history for your database

use msdb
go
select rh.restore_history_id ,rh.restore_date, rh.user_name, rh.destination_database_name,
rh.restore_type,rf.restore_history_id, rf.file_number, rf.destination_phys_drive,rf.destination_phys_name
from restorehistory rh
join restorefile rf
on rh.restore_history_id = rf.restore_history_id
where rh.destination_database_name = 'mytestdb'
order by rh.restore_date desc

-- Please note Restore_Type value D = Full and L= Log

All SQL Server Backups taken Last time

-- All SQL Server Backups taken Last time
SELECT d.name AS DatabaseName,
MAX(b.backup_finish_date) AS Last_backup_finish_date
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type = 'D' -- Full Backup
WHERE d.database_id NOT IN (2, 3) -- Ignore tempdb and model GROUP BY d.name
ORDER BY 2 DESC
--Show details of the successful Backup operations performed on this server in the past

SELECT d.name AS DatabaseName,
b.backup_finish_date AS Last_backup_finish_date
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type IN( 'D') -- Full Backup
WHERE d.database_id NOT IN (2, 3) -- Ignore tempdb and model ORDER BY 2 DESC

How to copy RDL files from Report Server Manager

If you want to copy production reporting files rdl into testing server to deploy. The following steps are easiest method to do.

Solution:
Open your Report on Report Manager
Then Click properties of Report
Under the Report Definition you will see Edit and Update link
If you click on the Edit link you can Open or Save the RDL files