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

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 开发者_Go百科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.

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

Just to supplement the knowledge base for restoring SQL Server database with TSQL - below there is a script handling the issue of processes which can prevent the restoration, restore the database from remote backup file and finally grant the access for some user. Reference for some options used can be found on MSDN.

/************** Restore SQL Server database with TSQL **************/

DECLARE @SQL AS VARCHAR(20), @spid AS INT     
SELECT @spid = MIN(spid) FROM master..sysprocesses WHERE dbid = db_id('YourDbName') AND spid != @@spid    

-- Kill processes that currently use the database and could block the restoration     
while (@spid IS NOT NULL)
BEGIN
    print 'Killing process ' + CAST(@spid AS VARCHAR) + ' ...'
    SET @SQL = 'kill ' + CAST(@spid AS VARCHAR)
    EXEC (@SQL)

    SELECT @spid = MIN(spid) FROM master..sysprocesses WHERE dbid = db_id('YourDbName') AND spid != @@spid
END 
GO

-- Restore the database and bring it back online, so it can be accessed
RESTORE DATABASE YourDbName FROM DISK = '\\path\to\backup.bak' WITH REPLACE, RECOVERY;
GO   

-- Restore the user
USE YourDbName;
DROP USER YourUserName;
GO

ALTER authorization ON DATABASE::YourDbName TO YourUserName;
GO

Alternatively, if such script is used quite often, a stored procedure can be created:

CREATE PROCEDURE RestoreFromSomewhere
AS
BEGIN
    -- The script above
END
GO