How to backup usinga parameter for db name

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 2000 Forums

Transact-SQL (2000)

How to backup usinga parameter for db name

Author

Topic

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-12-21 : 08:27:18

Hi,

I'm struggling to get the correct syntex to back a database using a sp as such:

Create proc [dbo].[usp_BackupTestDB]

@SnapShot varchar(100)

AS 

Backup Database 
	testDB 
to disk = 'E:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\AdhocBackups\' + @SnapShot + '.bak'

I've tried using EXEC but the quotation marks are out of place.

Any suggestions?

Thanks

Drew

Drew

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 08:32:08

[code]Create proc [dbo].[usp_BackupTestDB]

@SnapShot varchar(100)

AS
declare @sql varchar(8000)

select @sql = '
Backup Database
testDB
to disk = ''E:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\AdhocBackups\' + @SnapShot + '.bak'''

print @sql
exec (@sql)[/code]
Peter Larsson
Helsingborg, Sweden

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-12-21 : 08:32:18

You can use exec using char(39) for the quotation marks eg '......... to disk = ' + char(39) + 'E:\......' + char(39)

or you can use exec with 4 quotation marks representing the one eg ' ....... to disk = ''''e:\......

Duane.

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-12-21 : 09:07:30

Thanks for both replies. I didn't know either.

Drew

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-21 : 11:14:29

One more way:


Create proc [dbo].[usp_BackupDB]
	@DB		sysname,
	@SnapShot	varchar(100)
as

declare @backup_file	varchar(300)

set  @backup_file =
	'E:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\AdhocBackups\' +
	@SnapShot + '.bak'

Backup Database	@DB	to disk = @backup_file

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-12-22 : 11:17:04

Some ideas about things to do in automated backups:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Automating%20Backups,Backup

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources