Red Gate forums :: View topic - use variable for database name in backup command
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 7
SQL Backup 7 forum

use variable for database name in backup command

Search in SQL Backup 7 forum
Post new topic   Reply to topic
Jump to:  
Author Message
fredmut



Joined: 21 Jun 2012
Posts: 5

PostPosted: Mon Jun 25, 2012 6:26 pm    Post subject: use variable for database name in backup command Reply with quote

Is it possible to pass in a TSQL like variable for the database name so I don't have to maintain a hard coded list of databases to backup or exclude? i.e.
EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE @dbname ...... " '

I'm not finding that addressed in the documentation.
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2277

PostPosted: Tue Jun 26, 2012 2:41 am    Post subject: Reply with quote

No, that isn't possible. If you don't want to maintain a hardcoded list of database names, consider the following options:

- to back up all system databases
You could use the SYSTEM option e.g.
Code:
EXEC master..sqlbackup '-sql "BACKUP SYSTEM DATABASES TO DISK = [E:\Backups\<AUTO>]"'

- to back up all user databases
You could use the USER option e.g.
Code:
EXEC master..sqlbackup '-sql "BACKUP USER DATABASES TO DISK = [E:\Backups\<AUTO>]"'

- to back up all database except a subset
You could use the EXCLUDE option e.g.
Code:
EXEC master..sqlbackup '-sql "BACKUP DATABASES EXCLUDE [model, AdventureWorks] TO DISK = [E:\Backups\<AUTO>]"'

_________________
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
Back to top
View user's profile Send private message Send e-mail
fredmut



Joined: 21 Jun 2012
Posts: 5

PostPosted: Tue Jun 26, 2012 3:06 pm    Post subject: Reply with quote

This is a pretty severe limitation as it assumes a database hasn't been created, moved, taken offline, changed recovery model, put in single user mode or any number of other possibilities that could cause an error. So, for a server with, let's say more than a dozen or so databases I have to constantly monitor and adjust my backup jobs. Please add this to your feature request list.
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2277

PostPosted: Tue Jun 26, 2012 4:47 pm    Post subject: Reply with quote

Quote:
This is a pretty severe limitation as it assumes a database hasn't been created, moved, taken offline, changed recovery model, put in single user mode or any number of other possibilities that could cause an error.

Taking the example of the EXCLUDE option, e.g.

Code:
EXEC master..sqlbackup '-sql "BACKUP DATABASES EXCLUDE [model, AdventureWorks] TO DISK = [E:\Backups\<AUTO>]"'


SQL Backup does the following:

- get a list of databases from the sysdatabases table
- discard databases in recovery, offline, or read-only mode from the list
- discard the explicitly named databases from the list i.e. model and AdventureWorks
- back up the remaining databases

This addresses the following situations you mentioned:

- databases that hasn't been created - SQL Backup obtains the base list of databases from SQL Server every time it runs, so even for new databases, as long as they are online, they will be backed up

- moved - not sure what you mean, but if it's still online, it will be backed up

- taken offline - as mentioned, SQL Backup ignores these databases

- changed recovery model - has no impact on the outcome of a full database backup. If you use BACKUP LOGS instead, SQL Backup will only attempt to back up databases using the full or bulk-logged recovery models.

- any number of other possibilities - we believe we have addressed the most common possibilities. We are always open to suggestions to further improve the reliability of this feature.

While I mentioned that it isn't possible to use a variable containing a database name in the SQL Backup backup statement, it is however possible to form the command using 2 or more strings, and run them as a single command string e.g.

Code:
DECLARE @dbname nvarchar(256)
SET @dbname = N'pubs'

DECLARE @sql nvarchar(1024)
SET @sql = N'-sql "BACKUP DATABASE ' + @dbname + ' TO DISK = [e:\Backups\<AUTO>]"'

DECLARE @exitcode INT
DECLARE @sqlerrorcode INT

EXEC master..sqlbackup @sql, @exitcode OUTPUT, @sqlerrorcode OUTPUT
SELECT @exitcode, @sqlerrorcode

_________________
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group