Restoring database backups in SQL Server using Query

We can restore different types of backups in SQL Server using the following T-SQL queries.

The following syntax for restoring backups are

1. full backup,

2. Differential backup,

3. Transactional-log/T-log backup,

4. File and filegroup backup,

 


1. Syntax for full backup:   

restore database <db-name> from disk='<path-name>’

Example: 

restore database TestDB  from disk=’D:\Backups\fullback 1.bak’


2. Syntax for differential backup:   

restore database <db-name> from disk='<path-name>’ 

Example: 

restore database TestDB  from disk=’D:\Backups\diffbackup1.bak’


3. Syntax for T-Log/ Transactional log backup:   

restore log <db-name> from disk='<path-name>’

restore log <db-name> from disk='<path-name>’ with norecovery/ recovery

Example: 

restore log TestDB  from disk=’D:\Backups\tranlog1.trn’

restore log TestDB  from disk=’D:\Backups\tranlog1.trn’ with norecovery/ recovery


4. Syntax for file and filegroup backup:   

restore database <db-name> file=’primary/file-name’ from disk='<path-name>’

restore database <db-name> filegroup=’primary/fg-name’ from disk='<path-name>’

Example: 

restore database TestDB file=’File1′ from disk=’D:\Backups\filebackup1.bak’

restore database TestDB filegroup=’primary’ from disk=’D:\Backups\fgback 1.bak’


 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s