The backup procedures in SQl Server 2005 a very complex. The following code is a simple way to conduct a backup of an entire database to a network location. You can use the back up wizard by right-clicking on your database in the Management Studio – and selecting backup.
However, I have many databases spread across multiple servers and found this bit of code very useful to copy, paste and edit for each database that i want to backup.
BACKUP DATABASE [DATABASE1] TO DISK = N‘\\NETWORK_STARAGE_LOCATION\Data\20081107\DATABASE1_200811070.BAK’ WITH PASSWORD = ‘PASSWORD’, NOFORMAT, NOINIT,
NAME = N‘DATABASE1-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS=10
GO
This will create a full backup of a database called DATABASE1 in the following directory:
\\NETWORK_STARAGE_LOCATION\Data\20081107\
to a file called
DATABASE1_200811070.BAK
You can read a lot more about SQL Server backups, such as differential backups in online docs. But I have found this helpfull when conducting regular backups of the same structure.
