Automate Database backup & Restore T-SQL

Automate Sql Server Database backup & Restore Using Sqlcmd

Added by Nasir Mahmood updated on Thursday, April 10, 2014

Problem:

Lets we have to take SQL server’s database backup and then restore it several time in a day using Management Studio. Oh no what a tedious work! Is it not?

Wait here is a good news, we can automate whole process and reduce all work to just a single click. How? Just read this step by step guide and enjoy.
(Note: This tutorial is using SQL Server 2008 R2 and SQLCmd 9)

Prerequisites:

Backup Database:

In this step we will create a bat file which takes database backup by just single click.

First of all open your favorite text editor and write down following code.

    path_to_SQLCMD -U userid -P password -S instance_name
        -Q "BACKUP DATABASE db_backup_name TO DISK='path_to_db_backup_name.bak' WITH INIT"
    pause

Now let’s explain what is going on

  • path_to_SQLCMD: Path to sqlcmd.exe default location is C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE
  • -U: Login ID of sql server database
  • -P: Password of sql server database
  • -S: Sql server address and instance name i.e use localhost if default instance and use localhost\instance if have instance
  • -Q: Command line query here we use T-Sql query to take backup of database
    • BACKUP DATABASE: Specifies a complete database backup
    • db_backup_name: Name of database to take backup
    • TO DISK=: Specifies a disk file for backup
    • WITH INIT: Specifies that all backup sets should be overwritten and don't append.
  • pause: Pause is used to wait for user input

Now save above code as .bat and double click on file.After running script you will see following output

Backup database using sqlcmd

Restore Database:

In this step we will create a bat file which is used to restore database backup by just single click.

First of all open your favorite text editor and write down following code.

    path_to_SQLCMD -U userid -P password -S instance_name
        -Q "RESTORE DATABASE db_backup_name FROM DISK='path_to_db_backup_name.bak' WITH REPLACE"
    pause

Now let’s explain what is going on

  • path_to_SQLCMD: Path to sqlcmd.exe default location is C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE
  • -U: Login ID of sql server database
  • -P: Password of sql server database
  • -S: Sql server address and instance name i.e use localhost if default instance and use localhost\instance if have instance
  • -Q: Command line query here we use T-Sql query to restore backup of database
    • RESTORE DATABASE: Specifies a complete database restore
    • db_backup_name: Name of database to restore
    • TO DISK=: Specifies a disk file for restore
    • WITH REPLACE: Specifies that SQL Server should create the specified database and its related files even if another database already exists with the same name.
  • pause: Pause is used to wait for user input

Now save above code as .bat and double click on file.After running script you will see following output

Restore database using sqlcmd

References:

SQLCmd Utility
BACKUP (Transact-SQL)
RESTORE (Transact-SQL)
RESTORE Arguments (Transact-SQL)

About

29 Tutorials
25 Snippets
6 Products

More

Contact Us

Contact us

Stay Connected