[EASY] How to backup a SQL Express database using SQLCMD

Posted on Updated on

Here are the steps I am doing to backup a SQL express database.  In this example, I am going to demonstrate how to backup a Skype for Business SQL express database.

Step 1:  Connect to your SQL Express Database Engine instance using SSMS.  Make sure to put the instance name.

SQLExpress_SSMS

Step 2:  Once you are connected, find the database you want to backup, right-click > Tasks > Backup

Backup_Express

Step 3:  On the Backup Database window, make the changes you would like to make (backup type, destination, etc).  Click the drop-down for Script and select ‘Script Action to New Query Window’

backup_script

Step 4:  On the new Query window, make the final changes.  You can change the path to a UNC path if you wish.  Run the script to make sure it works.

BACKUP DATABASE [lyss] TO DISK = N’\\path_to_backup_directory\lyss.bak’ WITH NOFORMAT, NOINIT, NAME = N’lyss-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

If you wish to backup more databases on 1 script, repeat step 2 and select ‘Script action to Clipboard’ and put all scripts into one query window and then save it as one SQL file.

Step 5:  Save the script to a .SQL file

Step 6:  Create a scheduled task if you wish and under Actions, browse to the location of the SQLCMD file.

“C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE”

And add the arguments as:  -S \<instance name> -i <path to the SQL file you saved in step 5>

express_scheduled

If you are not using a scheduled task.  Simply run the command:

SQLCMD -S \<instance name> -i  <path of the SQL file you saved in step 5>

 

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