easiest way to do a full backup of a SharePoint db from one box and restore to another

Question

I need to do a full backup of a SharePoint app running on our production server to a our test server running SharePoint.

I tried using Central Admin to back up from the prod server, zipping up the backup and then restore to the test server, but this doesnt work.

Whats the easiest way to do this?

Answer 1

stsadm works well, here's a blog post to go over the commands

Answer 2

Also of note, as of the April CU cross-farm backup/restores for publishing sites are now supported:

http://blogs.technet.com/stefan_gossner/archive/2009/05/01/red-is-green-up-is-down-and-the-unsupported-suddenly-becomes-supported.aspx

Answer 3

Ahhh, SharePoint. The undocumented features abound...

The sad truth is that Central Administration doesn't always execute commands the same way that stsadm command-line utility does (and stsadm does it correctly). The best way to ensure success is to use the stsadm tool for backup/restore.

I've had a great deal of success with backup/restore operations in SharePoint using a batch file that I keep handy (it runs nightly on a scheduled task). Here's the contents of the batch file to do the backup:

Set Day=%date:~7,2%
Set Month=%date:~4,2%
Set Year=%date:~10,4%
Set DateStamp=%Year%_%Month%_%Day%

SET BackupFileName=BACKUP_FILE_NAME
SET SiteUrl=http://YOUR_PORTAL_URL

C: 
CD \Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\BIN 
stsadm.exe -o backup -overwrite -url %SiteUrl% -filename C:\ScheduledTasks\Backups\%BackupFileName%_%DateStamp%.dat

That batch file will save the .dat file for your entire site backup in the same directory in which you execute it.

Come the point where I need to do a restore, here's the command I use:

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\BIN 
    stsadm.exe -o restore -url http://YOUR_PORTAL_URL -filename "C:\PATH_TO_BACKUP\BACKUP_FILE_TO_RESTORE.dat" -overwrite

The only thing left for you to do is to move the .dat file from one server to the other and run the restore command. While this command will overwrite the entire portal's content database, it will not bring over your SSP, or any other Central Administration settings - it is only for site collection that lives at the URL you specify in your backup batch file.

I hope this helps!