How to Restore and Backup Transact-SQL
Although SQL Server has a wizard that allows administrators to back-up and restore database files, there is also a command line utility that is capable of accomplishing advanced database operations / commands on data stores. The command line interface allows admins to input T-SQL commands that are able to achieve the same results as the wizard application.
Step 1 – Select the “Start” menu button followed by choosing the “All Programs” and “SQL Server” menu options.
Step 2 – Select the “SQL Server Management Studio” program icon which will launch the management console for the SQL database.
Step 3 – After the management console launches, right-click the database name located on the left-hand side of the program window.
Step 4 – Choose the “New Query” menu option which will launch the T-SQL editor. The editor window is where the backup and restore SQL code will be entered.
Step 5 – Enter the following commands to backup the database:
backup database myTransactionalDatabase
to disk = ‘C:\myTransactionalBackup.bak’
with format;
go
The “myTransactionalDatabase” name should be replaced with the specific database name that you intend to backup. The drive specification ion the “to disk” line should be replaced with the specific drive and path to save the backup file. The location can be on any accessible local or network drive from the computer.
Step 6 – Enter the following command to restore the backup file created in step 5:
restore database myTransactionalDatabase