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.