Meta

Backup Microsoft SQL Database Using T-SQL

Submitted by admin, on February 5th, 2013

This guide will show you how to backup a Microsoft SQL database using T-SQL commands rather than using the wizards in SQL Management Studio. This has the added benefit of being able to be scripted if needed.

Backing up a Microsoft SQL database:

BACKUP DATABASE [database_name] TO DISK='c:\path\to\backup\file.bak'

Replace ‘database_name’ with the name of the database you want to backup.

Make sure the account that the SQL server service is running as had access to write to the path you want to save the backup into. If not you will get an error such as “Operating system error 5(Access is denied.)”.

The backup path location should be on a physical disk and folder on the server running SQL server. This means that if you run the backup script from a client workstation, make sure the backup path references the server path, and not the client workstation’s path.

You can use UNC network paths for the destination output, however this isn’t supported by Microsoft, and you can easily run into permission issues.