title: Format string inside sql query when use stored procedure in Sql Server 2005
body: <p>I have a query inside my stored procedure:</p>
  
  <pre><code>IF @backup = 0
      BEGIN
         SELECT sql = 'BACKUP DATABASE MyDatabase TO DISK="'+@file+'"';
      END
  </code></pre>
  
  <p><code>@file</code> is <code>nvarchar(MAX)</code> and <code>backup</code> is <code>int</code>.</p>
  
  <p>If I execute the stored procedure like:</p>
  
  <pre><code>DECLARE @return_value int
  
  EXEC    @return_value = [dbo].[usp_cp_backup_restore_db]
          @backup = 0,
          @file = N'C:\backupFiles\MyDatabase.bak'
  </code></pre>
  
  <p>but the result becomes:
  <code>BACKUP DATABASE ContentPlatform TO DISK="C:\backupFiles\MyDatabase.bak"</code></p>
  
  <p>I want to appear <code>BACKUP DATABASE ContentPlatform TO DISK='C:\backupFiles\MyDatabase.bak'</code>
  because the correct format is </p>
  
  <pre><code>BACKUP
  DATABASE MyDatabase TO
  DISK='C:\MyDatabase.bak'
  </code></pre>
  
  <p>accordingly with <a href="http://dwhanalytics.wordpress.com/2011/03/15/sql-serverexclusive-access-could-not-be-obtained-because-the-database-is-in-usethe-tail-of-the-log-for-the-database-has-not-been-backed-up/" rel="nofollow">http://dwhanalytics.wordpress.com/2011/03/15/sql-serverexclusive-access-could-not-be-obtained-because-the-database-is-in-usethe-tail-of-the-log-for-the-database-has-not-been-backed-up/</a></p>
  
  <p>Any suggestions ?</p>
  
  <p>Thanks</p>