I routinely take a backup from the production SQL 2005 Server and restore it to my dev system. I had previously published my code for backup and restore, which used backup devices but I found that was not necessary.
I have a folder on the production server named E:\SQL_Bill which is shared for me alone and is the target for my backup. I then zip and copy the file to my thumb drive, then copy and unzip the file to my dev system in the default backup folder.
Here is the backup script:
BACKUP DATABASE [lampsPAR]
TO DISK = N'E:\SQL_Bill\wm_LampsPAR.bak'
NAME = N'lampsPAR-Full Database Backup',
STATS = 10
and here is the restore script:
RESTORE DATABASE [LampsPAR]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\wm_LampsPAR.bak'
FILE = 1,
STATS = 10
To create these scripts, all I had to do is use SSMS to set up my backup, but then I clicked the script button to generate the script in a new window. I then cancelled the backup & saved the script as a .sql file. I did the same for the restore script. What could be easier than that.