How to programmatically restore db? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to programmatically restore db?

Author

Topic

Sigal
Starting Member

11 Posts

Posted - 2007-12-04 : 11:39:40

Hi all, I've written a short stored procedure which restores a backup into a new db which works perfectly when called from Query Analyzer but doesn't seem to work when I call it from an ASP page, I'm getting the following error: "Create database permission denied in database 'master'."
Here is my procedure:

=========================================================
ALTER PROCEDURE [dbo].[CreateNewEshotDB]
@DBName varchar(50)
AS
BEGIN

declare @MdfFile as varchar(50);
declare @LdfFile as varchar(50);

set @MdfFile = 'D:\MDF\'+ @DBName +'.MDF';
set @LdfFile ='D:\LDF\'+ @DBName +'.LDF';

RESTORE DATABASE @DBName FROM DISK = N'C:\Program Files\MyDB.bak' WITH FILE = 1,
MOVE N'MyDB_Data' TO @MdfFile,
MOVE N'MyDB_Log' TO @LdfFile,
NOUNLOAD,
STATS = 10
END
=========================================================
Any ideas will be much appreciated.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-04 : 11:46:56

you have to set the initial catalog in your connection string.
or you can use SQL SMO .net objects to do this in an easier way:
http://vidmar.net/weblog/weblog/archive/2007/05/09/Hot-to-restore-a-SQL-Server-database-in-.NET-when.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Sigal
Starting Member

11 Posts

Posted - 2007-12-04 : 11:59:05

Hi Spirit1, thank you for your speedy reply. Unfortunately, I'm using classic ASP and I already have 'initial catalog' in my connection string but it doesn't seem to help.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-04 : 12:01:39

with what permissions are you connecting to your db?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-04 : 12:08:32

To accurately test this, connect to Query Analyzer using the same account as specified in your connection string, point the database inside Query Analyzer to same database specified in Initial Catalog in your connection string, then run the stored procedure. Do you get the same error there?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Sigal
Starting Member

11 Posts

Posted - 2007-12-04 : 12:16:55

Ah, thanks very much, you've pointed me in the right direction. I used SA permissions and it worked.

Have a wonderful day
Sigal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources