Step by step backup/restore using T-SQL

 

This article mainly for SQL server learners, who is going to become a DBA, in this article I am going to explain the backup and restore using T-SQL.

Why I choose the T-SQL instead of GUI?

Below are reasons,

I have seen a number of articles and blogs shows the GUI.

Anyone can use the GUI to restore the database, if they don’t well in SQL.

Again most of the DBAs hasn’t used the command and they don’t know the T-SQL syntax to restore the database. I recommend using the T-SQL to restore the database because it’s the greatest tool when you are migrating a huge number of databases (Not to right click each database to restore) and time saver.

Backup:

I enjoyed when I read BrentOzar’s backup best practice articles. I have recommended my readers too.

Let’s see the funny story

You have some problem with your mobile phone. You lost all your contact numbers even your girlfriend (wife) number too. Suddenly, she called to you then you asked who are you? …

So, keep doing the backup regularly otherwise ready to update your resume – :)

Before going to restore section keep verify your backups regularly (sometimes your backup might be damaged)

Important: Backup is the free D/R solution, when you lost your database.

 

Step by step restore: Backup has been taken in D drive.

USE MASTER
GO
BACKUP DATABASE DBA_TEST TO DISK='D:\BACKUP\DBA_TEST.BAK' with copy_only

 


Filelistonly returns the logical file names, physical files of data, log locations & file sizes.


USE MASTER
GO
RESTORE FILELISTONLY FROM DISK = 'BACKUP LOCATION'
RESTORE FILELISTONLY FROM DISK ='D:\BACKUP\DBA_TEST.BAK'

 




Create the same copy\content of the database DBA_new from DBA_test. Use XP_fixeddrives to find the drive free space.


EXEC XP_FIXEDDRIVES
SELECT * FROM SYS.MASTER_FILES


“sys.master_files” will show the current database files location. All the companies have certain rules like, data file will go one separate physical LUN (RAID level) and the log file will go another physical LUN (RAID level). It will help to improve the performance.

So, before going to restore checks and ask the same.

Restore database <database name> from disk =’backup location’
With
Move ‘logical name of data file’ to ’data file location’,
Move ‘logical name of log file’ to ’log file location’

RESTORE DATABASE DBA_NEW FROM DISK ='D:\BACKUP\DBA_TEST.BAK'
WITH
MOVE 'DBA' TO 'D:\DATA\DBA.MDF',
MOVE 'DBA_LOG' TO 'E:\LOG\DBA_LOG.LDF'


You can also overwrite the database by using REPLACE option if you don’t need the existing database.
For more : Look the restoration options  WITH options (like overwrite(REPLACE),move etc).
RESTORE DATABASE DBA_TEST FROM DISK ='D:\BACKUP\DBA_TEST.BAK' WITH REPLACE
After complete a restoration its good to change the logical name. But It will not cause anything if you aren’t changing it.
SELECT * FROM SYS.MASTER_FILES
ALTER DATABASE DBA_NEW MODIFY FILE
(NAME = DBA,NEWNAME = DBA_NEW )
ALTER DATABASE DBA_NEW MODIFY FILE
(NAME = DBA_LOG,NEWNAME = DBA_NEW_LOG )


The database restoration has done in different machine then,  transfers the logins and the passwords  by using this Microsoft script.

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

Share this
15 Comments - Leave a comment
  1. Anonymous says:

    Dude,
    Nicely written.I ll show this my team too.

    pradeep

  2. Anonymous says:

    muthukkumaran
    It's really clear & great post.
    thanks and keep it up.(linkedin)

  3. muthukkumaran says:

    Hi,
    You can enter your email id and subscribe the upcoming articles. (See the right side top corner)

  4. deepalakshmi says:

    grt wrk!!!!!!!!!!!!!
    really helpfl to DBA's….

  5. Mark Willium says:

    Thank you for recommending a new article but you can also restore corrupt or damaged sql server database by Stellar Phoenix SQL Recovery application.

  6. Muthukkumaran Kaliyamoorthy says:

    @mark,
    Thanks for sharing.I ll try it.

  7. Peter says:

    Hi,

    One question. I want to automate my backup and restore process. This is fine but I want to prevent my SQL job which does the restore to not begin until I the backup process has finished. How can i get this job to execute once the backup has finished?

  8. Muthukkumaran says:

    Peter,

    You can do this. I'll give you 2-way.

    1.Check the system table BACKUPSET in MSDB it has the backup finished details

    2.Check the system table SYSJOBHISTORY in MSDB it has also help your requirement.

    Look the script to get the backup details here

    Backup details Script

    I suggest you to ask the question in MSDN and SQLservercentral. We'll help you.

    Thanks for your question.

  9. [...] have already written an article about Step by step backup/restore using T-SQL. But it’s not fully focus the restoration [...]

  10. LONGOR says:

    Thankyou for sharing Step by step backup/restore using T-SQL Sql server Blog Forum with us keep update bro love your article about Step by step backup/restore using T-SQL Sql server Blog Forum .

  11. Thanks Muthukkumaran kaliyamoorthy for the Awesome share of information, it was very helpful to me. I really enjoy the manner in which you have framed your issues regarding this situation, keep up the great work. All the Best. John

Leave a comment

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="">

Current ye@r *


Welcome , today is Monday, September 22, 2014