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.

iandotkelly · Accepted Answer · 2012-02-02 19:24:33Z

11

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

edited Feb 2, 2012 at 19:24

answered Jul 24, 2011 at 15:33

iandotkelly

9,1548 gold badges50 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Andrey Agibalov Over a year ago

It says: "Backup or restore requires at least one backup device. Rerun your statement specifying a backup device".

Andrey Agibalov Over a year ago

Says: "Cannot open backup device 'c:\testdb2.bak'. Operating system error 5(error not found)". Looks like the same issue that I had.

iandotkelly Over a year ago

Some versions of windows try to prevent files being created on the root c:\ folder. Have you tried creating the file somewhere else? The fact that it works when you've put a file there already (using administrator privilages perhaps) hints that this might be a permissions error

Andrey Agibalov Over a year ago

You're ABSOLUTELY RIGHT. Thanks a lot! My approach is also OK in this case.

iandotkelly Over a year ago

Ahaha. I would look at how you find the user's documents folder in t-sql. Then you could create a backup folder in there. I don't know how to do that unfortunately.

|

jwaliszko · Accepted Answer · 2012-11-08 10:12:36Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related