If you have backed-up a database with password protection such as:
BACKUP DATABASE [DATABASE] TO DISK = N‘\\NETWORK_STARAGE_LOCATION\database.BAK’ WITH PASSWORD = ‘PASSWORD’,
NOFORMAT, NOINIT, NAME = N‘DATABASE-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS=10
GO
As outlined in a previous post http://robbamforth.wordpress.com/2008/11/07/sql-server-2005-database-backup/
Then you will be required to restore the database programmatically as both Enterprise Manger and SQL Server Management Studio do not support the password option when restoring with the GUI wizard.
To view the files available in the backup:
RESTORE FILELISTONLY FROM DISK='\\NETWORK_STARAGE_LOCATION\database.BAK'
WITH PASSWORD = 'password'
To run a full simple backup:
RESTORE DATABASE Northwind FROM DISK='\\NETWORK_STARAGE_LOCATION\database.BAK’
WITH PASSWORD = 'password'
This post shows how to restore a backup to a different location to where it was taken: http://robbamforth.wordpress.com/2009/11/18/sql-%e2%80%93-how-to-restore-a-database-backup-to-a-different-location/
You may be required to restore a database backup to a location different from the location of the original backup. If you backed up with a password then this will need to be done programmatically.
The following example shows how to use a backup taken from a database called Database and restore it to a new location on a new server with the name Database2.
RESTORE DATABASE [NEW_DATABASE]
FROM DISK = N’\\NETWORK_STARAGE_LOCATION\database.BAK’
WITH password = ‘password’,
FILE = 1,
MOVE N’database’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\database2.mdf’,
MOVE N’database_log’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\database2.log,
NOUNLOAD, STATS = 10
GO
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.
