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.
Eg:
BACKUP DATABASE [testing] TO DISK='C:\temp\testing.bak'
Sample output:
Processed 312 pages for database 'testing', file 'Testing' on file 1. Processed 3 pages for database 'testing', file 'Testing_log' on file 1. BACKUP DATABASE successfully processed 315 pages in 0.487 seconds (5.039 MB/sec).
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.
