backup get date

use msdb
select bmf.media_set_id,bmf.physical_device_name ,bset.database_name,bset.backup_finish_date,bset.type from backupmediafamily bmf
inner join backupset bset
on bmf.media_set_id=bset.media_set_id
where bset.backup_finish_date>'2010-05-10' and bset.type='d'

Most Recent Database Backup for Each Database - Detailed

-------------------------------------------------------------------------------------------
--Most Recent Database Backup for Each Database - Detailed
-------------------------------------------------------------------------------------------
SELECT 
   A.[Server], 
   A.last_db_backup_date, 
   B.backup_start_date, 
   B.expiration_date,
   B.backup_size, 
   B.logical_device_name, 
   B.physical_device_name,  
   B.backupset_name,
   B.description
FROM
   (
   SELECT  
       CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
       msdb.dbo.backupset.database_name, 
       MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
   FROM    msdb.dbo.backupmediafamily 
       INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
   WHERE   msdb..backupset.type = 'D'
   GROUP BY
       msdb.dbo.backupset.database_name 
   ) AS A
   
   LEFT JOIN 

   (
   SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_start_date, 
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   msdb.dbo.backupset.backup_size, 
   msdb.dbo.backupmediafamily.logical_device_name, 
   msdb.dbo.backupmediafamily.physical_device_name,  
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily 
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE  msdb..backupset.type = 'D'
   ) AS B
   ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
ORDER BY 
   A.database_name

Most Recent Database Backup for Each Database

-------------------------------------------------------------------------------------------
--Most Recent Database Backup for Each Database
-------------------------------------------------------------------------------------------
SELECT 
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name, 
   MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM   msdb.dbo.backupmediafamily 
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE  msdb..backupset.type = 'D'
GROUP BY
   msdb.dbo.backupset.database_name 
ORDER BY 
   msdb.dbo.backupset.database_name 

Database Backups for all databases For Previous Week

--Database Backups for all databases For Previous Week
---------------------------------------------------------------------------------
SELECT 
   CONVERT(CHAR(100), SERVERPROPERTY('ROOM\SKHA2007')) AS Server,
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_start_date, 
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   CASE msdb..backupset.type 
       WHEN 'D' THEN 'Database' 
       WHEN 'L' THEN 'Log' 
   END AS backup_type, 
   msdb.dbo.backupset.backup_size, 
   msdb.dbo.backupmediafamily.logical_device_name, 
   msdb.dbo.backupmediafamily.physical_device_name,  
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily 
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
ORDER BY 
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_finish_date

Differential Database Backups for SQL Server

Differential Database Backups for SQL Server

Problem
I know the 'typical' backups are full database backups executed every day.  I have looked into transaction log backups and I am not sure if that is something I can support.  I have seen some information on differential backups, but I am not sure exactly how they work, how they are different and how they could help me?  Could you please explain some of the considerations with differential backups to determine if they would be beneficial to me and my situation? 
Solution
Depending on the frequency of data changes in your SQL Server database(s) and the amount of time\data that your business is willing to lose, should dictate your overall backup strategy.  If a days worth of data is acceptable to lose, then using the full backup strategy could be a viable one, if not, then you need to start looking into differential and transaction log backups.  Regardless of the situation, make sure you understand the business needs and make sure your backup and recovery solution will meet the needs.  Unfortunately, Murphy's law will kick into place one of these days and having the right solution in place can make all of the difference in the world for you and your organization.
Should I consider differential backups in my overall backup strategy?
As far as the differential backups are concerned, they are a viable backup option and may or may not be helpful in a number of situations (see the advantages and disadvantages section below).  Understanding how the differential backups work and how the recovery process would work is vital when you need to restore your databases in short order and get your SQL Server back up and running. 
How do the differential backups work from a backup perspective?

• The catalyst in the differential backup process is issuing a full database backup. 
• Then the differential backups can be issued at a regular interval depending on your needs. 
• For example, every 2 hours from 7:00 AM to 7:00 PM or at 7:00 AM, 12:00 PM and 7:00 PM.  The schedule is up to you, but keep this in mind during the restoration process outlined in the next section.
• If data changes on any one of the pages in an extent, a flag is set at the extent level to indicate that the extent must be backed up.

What is sample syntax for a differential database backup?

SQL Server 2005

BACKUP DATABASE AdventureWorks TO DISK = 'C:\Temp\DatabaseBackups\AdventureWorks_Full.bak' GO BACKUP DATABASE AdventureWorks TO DISK = 'C:\Temp\DatabaseBackups\AdventureWorks_Diff_1.bak' WITH DIFFERENTIAL GO BACKUP DATABASE AdventureWorks TO DISK = 'C:\Temp\DatabaseBackups\AdventureWorks_Diff_2.bak' WITH DIFFERENTIAL GO

SQL Server 2000

BACKUP DATABASE Northwind TO DISK = 'C:\Temp\DatabaseBackups\Northwind_Full.bak' GO BACKUP DATABASE Northwind TO DISK = 'C:\Temp\DatabaseBackups\Northwind_Diff_1.bak' WITH DIFFERENTIAL GO BACKUP DATABASE Northwind TO DISK = 'C:\Temp\DatabaseBackups\Northwind_Diff_2.bak' WITH DIFFERENTIAL GO

How do the differential backups work from a restore perspective?
Depending on your backup schedule and the time when the failure occurred would dictate the detailed steps that would need to be taken.  If you need to have your current SQL Server databases back up and running, then the full backup would be restored followed by the last differential and then transaction logs if you are issuing them. 
If you are trying to keep 2 SQL Servers in sync via differential backups, then at a high level, the full backup would need to be restored followed by the differential backups.

• To take this down a notch, the full backup would need to be restore using the WITH NORECOVERY option.
• The differential backups would also need to be restored using the WITH NORECOVERY option with the exception of the last differential backup (if no transaction log backups need to be restored) which would use the WITH RECOVERY option.
• Example code is shown in the section below.

What is the sample syntax for a differential database restore?

SQL Server 2005

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\Temp\DatabaseBackups\AdventureWorks_Full.bak' WITH NORECOVERY GO RESTORE DATABASE AdventureWorks FROM DISK = 'C:\Temp\DatabaseBackups\AdventureWorks_Diff_1.bak' WITH NORECOVERY GO RESTORE DATABASE AdventureWorks FROM DISK = 'C:\Temp\DatabaseBackups\AdventureWorks_Diff_2.bak' WITH RECOVERY GO

SQL Server 2000

RESTORE DATABASE Northwind FROM DISK = 'C:\Temp\DatabaseBackups\Northwind_Full.bak' WITH NORECOVERY GO RESTORE DATABASE Northwind FROM DISK = 'C:\Temp\DatabaseBackups\Northwind_Diff_1.bak' WITH NORECOVERY GO RESTORE DATABASE Northwind FROM DISK = 'C:\Temp\DatabaseBackups\Northwind_Diff_2.bak' WITH RECOVERY GO

What are the advantages and disadvantages for using differential database backups and restores?

• Advantages
• May need less time for the backup if a small percentage of extents are marked to be backed up.
• May use less storage for the backups if a small percentage of extents are marked to be backed up.
• May be able to keep more backups on disk as compared to full backups because the differential backups are much smaller.
• May use less bandwidth when moving backup files around the network because the files are smaller as compared to the full backups.
• Disadvantages
• Depending on the time interval between the last differential backup and the failure, data loss may occur.  If the amount of data loss is unacceptable, then transaction log backups are needed.
• Point in time recovery
• The restore time may not be any faster if the most recent full backup and the last differential backup must be restored either from local disk or tape as compared to issuing full backups on a daily basis and restoring the last full backup.
• If data is changed across a high percentage of extents, the differential backups may be as large as the full backups.  This means that the time and disk savings for the backups is minimal and the restore time is still as long as the full restore.
• May need more storage from a recovery perspective because the last full backup and the differential backup would be needed on disk for the fastest possible restore with the native tools.
• The backups on disk or tape are not encrypted with the native tools.
• May not be able to use the differential backups as a portion of an automated backup and recovery process to update development, test, pre-production or training environments.
• COPY_ONLY Backups with SQL Server 2005
• A third party solution that compresses and encrypts the backups may be the best source for storage savings for your backups and time savings for your restore process.
• SQL Server 2005 Backup Product Options

Next Steps

• Determine if differential backups would be time and disk savers based on the data changes in your environment.
• Determine if the frequency of the differential backups would be acceptable or if moving to transaction log backups is needed.
• Determine if the need to restore a number of backups would be too time consuming and leveraging a third party product would be a better solution.
• Conduct some testing in your environment and see if differential backups would be beneficial for particular databases by understanding how the data changes then determine the potential time and disk savings.
• Check out the MSSQLTips.com Backup and Recovery tips.
• Stay tuned for tips covering the new partial differential backups and file differential backups.

DATABASE BACKUPS

DATABASE BACKUPS

FULL BACKUP:

BACKUP DATABASE [sales] TO  DISK =

'C:\backup\sales_200906291032.bak' with noinit, stats=10

[noinit= append to the existing backkup set & stats=10 while processing the backup it shows the percentage of the backup increment]

TRANSACTION LOG BACKUP:

BACKUP LOG [salesdetails] to  DISK =

'C:\backup\sales_200906291044.trn' with noinit, stats=10

RESTORING FULL BACKUP:

RESTORE DATABASE [EMP] FROM  DISK = N'C:\BACKUP\empfull.bak' WITH  FILE = 1, STANDBY ='C:\Program Files...',  REPLACE,  STATS = 10 [REPLACE MEANS OVERWIRTE THE EXSISTING DATABASE]

RESTORING LOG BACKUP:

RESTORE LOG[EMP] FROM  DISK ='C:\BACKUP\emp.TRN' WITH  FILE = 1,REPLACE,  STATS = 10

How to change the recovery model from full to bulk-logged

USE [master]

GO

ALTER DATABASE [EMP] SET RECOVERY BULK_LOGGED WITH NO_WAIT

GO

DROPE DATABASE:

USE [master]

GO

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'EMP')

DROP DATABASE [EMP]

HELP USER:

EXEC sp_helpuser (this will give list of all users In a database)

LIST ALL DATABASES:

sp_databases

LIST OF ALL TABLES IN THE CURRENT DATABASE:

EXEC sp_tables

CHECK CONSTRAINTS FOR TABLES:

This example shows all constraints for the authors table.

USE pubs

EXEC sp_helpconstraint authors

ADD SERVER:

sp_addserver 'ACCOUNTS'

sp_spaceused:

List out space allocation of a database

Sp_spaceused tablename:

List out space allocation of a table

Script for creating login with role membership:

Use master

go

create login basker with password= 'basker', default_database=[master], check_expiration= off, check_policy= off

go

use pubs

go

create user basker for login basker

go

use pubs

go

exec sp_addrolemember 'db_ddladmin', 'basker'

go

 CHECK DATABASE INTEGRITY:

The Check Database Integrity task performs internal consistency checks of the data and index pages within the database.

REORGANISE INDEX:

The Reorganize Index task defragments and compacts clustered and non-clustered indexes on tables and views. This will improve index-scanning performance.

REBUILD INDEX:

The Reorganize Index task defragments and compacts clustered and non-clustered indexes on tables and views. This will improve index-scanning performance.

UPDATED STATATICS:

The Update Statistics task ensures the query optimizer has up-to-date information about the distribution of data values in the tables. This allows the optimizer to make better judgments about data access strategies.