MSSQLTips

http://www.mssqltips.com/tip.asp?tip=1174

 just wanted to point out that the stored process sp_executesql is not in SQL Server 2005 Express.  I've looked in the sp folder and nothing is there. 

This is stored under the Extended Stored Procedures -> System Extended Stored Procedures

Try running this code below to see if this works:

use master
go

sp_executesql N'SELECT * FROM sys.databases'

 Hello,

   I am very thankful to the code which i got it from you on taking the backup. I am a .net Programmer.

I have an requirement as to take the backup on different paths (as you have gave "c:\Backup") the path will change any no. of time, so i am planning to send a parameter of path, i tried to run the stored proc as this. but this gives a problem.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_BackupDatabase]    Script Date: 06/22/2008 03:54:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author: Edgewood Solutions
-- Create date: 2007-02-07
-- Description: Backup Database
-- Parameter1: databaseName
-- Parameter2: backupType F=full, D=differential, L=log
-- =============================================
alter PROCEDURE [dbo].[sp_BackupDatabasess] 
       @databaseName sysname, @backupType CHAR(1),
       @backuppath varchar(200)
AS
BEGIN
       SET NOCOUNT ON;

       DECLARE @sqlCommand NVARCHAR(1000)
       DECLARE @dateTime NVARCHAR(20)

       SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') +
       REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','') 

       IF @backupType = 'F'
               SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
               ' TO DISK = '+@backuppath + @databaseName + '_Full_' + @dateTime + '.BAK'''
       
       IF @backupType = 'D'
               SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
               ' TO DISK = '+@backuppath + @databaseName + '_Diff_' + @dateTime + '.BAK'' WITH DIFFERENTIAL'
       
       IF @backupType = 'L'
               SET @sqlCommand = 'BACKUP LOG ' + @databaseName +
               ' TO DISK = '+@backuppath + @databaseName + '_Log_' + @dateTime + '.TRN'''
       
       EXECUTE sp_executesql @sqlCommand
END

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'c:'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ''.

I would be very glad, if you can help me. prashanthganathe@gmail.com.

Thanks & Regards,

prashanthganathe@gmail.com.

You need to fix the quoting around the path.

The line should be like this

       IF @backupType = 'F'
               SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
               ' TO DISK = '''+@backuppath + @databaseName + '_Full_' + @dateTime + '.BAK'''

You need to  have three single quotes before the @backuppath variable

I love this script, but how would Iike to use it to backup another database, that may have users in it at the time.  Is there a way to run this script to an attached database that may be active?

 This script also needs brackets eg: [ ] around the database name to support names with characters such as hyphens ( - )

I am using VIsta, and I am receiving the following error:

This is a permissions problem.  The SQL Server service account probably does not have rights to the folder where you are trying to create the backup files. 

By default the NetworkService account is probalby being used for SQL Express which has limited rights.

You can either grant rights to this account to create files in this folder or you can use a different account for the SQL Server service or write to a folder where this account has permissions to create files.

Thanks,

 I am not familiar with any of these, what would you recommend doing, and how can I do that?

 Thank you very much for your help.

 Ken

Thanks

Great Script

Worked first time

Best I have ever found

you'll probably want to see this page, http://devzone.advantagedatabase.com/dz/webhelp/Advantage9.0/server1/sp_restoredatabase.htm

which talks about using sp_RestoreDatabase to use the data.

sorry *this* is the page that talks about having to use it:

http://devzone.advantagedatabase.com/dz/webhelp/Advantage9.0/server1/sp_backupdatabase.htm

Kendjr, if you look in Configuration Manager you will probably see that the SQL Server service is using the Network Service account.  This account probably has limited access to the folder you are trying to back up to.

You can either change the account in Configuration Manager to use the LocalSystem account

or

you can give write permissions to the Network Service account to the folder where you want to create the backup

or

you can try to save the backup to the default folder which is probably something like this:C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup

I found this tip, and have tried to set it up. I actually got the SQL part all set up, and if I simple run the command in the backup.sql file (to call the stored procedure), it makes the backup without problem.

 However, for some reason when I go to place it on in the Scheduled Task list, it doesn't seem to work. Here's the command line I'm using:

"sqlcmd -S dbServer-1 -E -i C:\backup script\Backup.sql"

 Do I need to add the SQL Server name to the Server (i.e. dbServer-1\SQLExpress)? Or does anyone have any other ideas why the schedule might not be working? Like I said, I've tested the stored procedure, so I know that part works; so it has to be in the scheduling part...

 Any help would be greatly appreciated. Thank you.