CODEWITHSUNDEEP
sql-server
user1578700
user1578700

Reputation: 31

Restore stored procedure

I have a database in SQL Server 2008 R2 and I created this stored procedure for restoring databases:

CREATE PROCEDURE [dbo].[usp_DBRestore]
@DBName nvarchar(60)
,@BackName nvarchar(120)
,@OutMessage nvarchar(4000) output
--,
--@DataName varchar(60),
--@DataFileName varchar(120),
--@LogName varchar(60),
--@LogFileName varchar(120)
AS

BEGIN TRY
    USE [master]

    ALTER DATABASE @DBName SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
    RESTORE DATABASE @DBName FROM
        DISK = @BackName WITH
        FILE = 1,  NOUNLOAD, 
        REPLACE,
        PASSWORD = 'TEST'
    SET @OutMessage = 'OK';
    ALTER DATABASE @DBName SET  MULTI_USER WITH ROLLBACK IMMEDIATE
END TRY
BEGIN CATCH

    ALTER DATABASE @DBName SET  MULTI_USER WITH ROLLBACK IMMEDIATE

    INSERT [dbo].[ErrorLog]
  (
   [UserName], 
   [ErrorNumber], 
   [ErrorSeverity], 
   [ErrorState], 
   [ErrorProcedure], 
   [ErrorLine], 
   [ErrorMessage] 
   )
   VALUES(
          CONVERT(sysname, CURRENT_USER), 
          ERROR_NUMBER(),
          ERROR_SEVERITY(),
          ERROR_STATE(),
          ERROR_PROCEDURE(),
          ERROR_LINE(),
          ERROR_MESSAGE()

         )
END CATCH

When I execute code I see this error :

a USE database statement is not allowed in a procedure, function or trigger.

How can I solve this error?

Upvotes: 3

Views: 7730

Answers (3)

BDurga
BDurga

Reputation: 11

You can create a linked server and have that referenced in your stored procedure. For example. LinkedServer.database.[dbo].StoredProcedure

Check out this How to create the linked server for SQL Server 2008 where we have the database from 2000 and 2005

Upvotes: 0

marc_s
marc_s

Reputation: 755381

You cannot do this in that way - you basically have two options:

  1. stick to a stored procedure, but in that case, you have to use dynamic SQL. Your stored procedure creates a string of SQL statements, which allows it to use USE master and it allows it to dynamically set the database name etc., and then it executes that SQL statement using sp_executesql @sqlRestoreStatement. If you want to check this out, you MUST be all means read (and understand) Erland Sommarskog's seminal article The Curse and Blessings of Dynamic SQL

  2. you can use a regular SQL script, possibly with SQLCMD placeholders (if you have SQLCMD mode enabled in your SQL Server Management Studio) and execute the restore from a regular script (which you can put into your own template folder, for instance). In that case, you'd have something like:

    :setvar dbname YourDatabaseNameHere

DECLARE @FileName NVARCHAR(255)
SET @FileName = N'D:\YourBackupDirectory\SomeDatabase.bak' 

RESTORE DATABASE [$(dbname)]
FROM DISK = @FileName
WITH FILE = 1,  
MOVE N'YourDatabase_Data' TO N'D:\MSSQL\Data\$(dbname).mdf',  
MOVE N'YourDatbase_Log' TO N'D:\MSSQL\Data\$(dbname)_Log.ldf',  
NOUNLOAD, REPLACE,  
STATS = 2
GO

    With this setup, you can easily use the SQL script as a template and restore any kind of database using it.

Upvotes: 1

Shailesh
Shailesh

Reputation: 1218

You don't need the USE statement. Best is to remove Use statement and create / Alter this sp on master database itself.

If you want to take a backup execute this SP from master DB. I can not see any other way out.

Upvotes: 0

Related Questions