Backup/Restore the database using T-SQL (MSSQL2005, 2008)

Question

I need to be able to backup and restore my database using tsql. The approach I'm using is:

-- backup
backup database testdb1 to disk='c:\testdb1.bak'

-- restore
alter database testdb1 set single_user with rollback immediate
drop database testdb1
restore database testdb1 from disk='c:\testdb1.bak'

This works fine, but requires having the existing file at c:\testdb1.bak. It's not a problem as long as I have SQL server installed locally, but what do I do if I connect to the server remotely? Any solutions for getting rid of this requirement?

For me, it doesn't really matter what the name and path to this file is, I just need to be sure that I would be able to restore the DB if my alter scripts go wrong.

Thanks.

---

Update The problem was that creating files at the root of c:\ is prohibited by some versions of Windows. Using C:\1\ is fine.

Answer 1

You need to create a new media set at the same time.

Adapted from this MSDN page:

BACKUP DATABASE testdb1
TO DISK = 'c:\testdb1.bak'
  WITH FORMAT,
    MEDIANAME = 'MyBackups',
    NAME = 'Full backup of my database'
GO

And make sure you have permissions to create the file in the root c:\ folder