database name

Is there a way to determine the current database the session is using? I want
to use this to determine if a script should proceed. It should only proceed
if the current database is not a system database.
I was thinking something like,
IF (select dbid < 6) THEN RAISERROR.
Thoughts? Thanks!
Check books online for db_name.
If you execute something like:
select db_name()
you will get the current database name.
-Sue
On Mon, 13 Sep 2004 16:41:12 -0700, "Bevo"
<Bevo@.discussions.microsoft.com> wrote:

>Is there a way to determine the current database the session is using? I want
>to use this to determine if a script should proceed. It should only proceed
>if the current database is not a system database.
>I was thinking something like,
>IF (select dbid < 6) THEN RAISERROR.
>Thoughts? Thanks!
|||if ( db_name != 'mydb') Raiserror ('Incorrect database',16,1)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:4A5919FA-B5A6-4B2E-BF05-F681FBA08019@.microsoft.com...
> Is there a way to determine the current database the session is using? I
want
> to use this to determine if a script should proceed. It should only
proceed
> if the current database is not a system database.
> I was thinking something like,
> IF (select dbid < 6) THEN RAISERROR.
> Thoughts? Thanks!
|||I will not know the name of the database to be used, but I will know that it
is not a system database.
Can I be sure that all database ids greater than 6 are not system databases?
If so, the logic would be this:
if ( db_id(db_name()) < 6) Raiserror ('Must not be a system database',16,1)
"Wayne Snyder" wrote:

> if ( db_name != 'mydb') Raiserror ('Incorrect database',16,1)
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:4A5919FA-B5A6-4B2E-BF05-F681FBA08019@.microsoft.com...
> want
> proceed
>
>
|||There is no need for db_name().
e.g.
If (db_id()<6) Raiserror ('Must not be a system included database',16,1)
Howerver, it's best to be explicit because you would never know if there
would be more than 6 system included databases. Plus, the db_id for the 7th
might be >6. Thus,
If (db_name() in ('master','msdb','tempdb','model','Northwind','Pub s'))
Raiserror ('Must not be a system included database',16,1)
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:10284D82-E541-46F4-B3C3-C76AA021BA40@.microsoft.com...
> I will not know the name of the database to be used, but I will know that
it
> is not a system database.
> Can I be sure that all database ids greater than 6 are not system
databases?
> If so, the logic would be this:
> if ( db_id(db_name()) < 6) Raiserror ('Must not be a system
database',16,1)[vbcol=seagreen]
>
sql

database maintenance plan using T-Sql and current_timestamp

hi can anyone help, i have a database maintenance plan which is set to
run hourly at the moment it overwrites the current database but i want
to append it with a current_timestamp, here is the T-SQL i am using
BACKUP DATABASE [company name] TO DISK = N'E:\Hourly
Backup\Company\Company backup- Hourly.BAK' WITH INIT , NOUNLOAD ,
RETAINDAYS = 1, NAME = N'company backup - Hourly', SKIP , STATS =
10, DESCRIPTION = N'Hourly Backup job runs from 7 - 9', FORMAT ,
MEDIANAME = N'RSUK_Hourly', MEDIADESCRIPTION = N'company Hourly
Backup'
My systemdb maintenance plan automatically appends the datestamp to the file
name:
BACKUP DATABASE [model] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\model_db_200602190913.BAK' WITH INIT , NOUNLOAD ,
NOSKIP , STATS = 10, NOFORMAT
Yours does not appear to be doing so. Did you create it using the wizard?
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.expertsrt.com - not your average tech Q&A site
"blueboy" <matt_meech@.hotmail.com> wrote in message
news:1140358625.726394.236770@.f14g2000cwb.googlegr oups.com...
> hi can anyone help, i have a database maintenance plan which is set to
> run hourly at the moment it overwrites the current database but i want
> to append it with a current_timestamp, here is the T-SQL i am using
> BACKUP DATABASE [company name] TO DISK = N'E:\Hourly
> Backup\Company\Company backup- Hourly.BAK' WITH INIT , NOUNLOAD ,
> RETAINDAYS = 1, NAME = N'company backup - Hourly', SKIP , STATS =
> 10, DESCRIPTION = N'Hourly Backup job runs from 7 - 9', FORMAT ,
> MEDIANAME = N'RSUK_Hourly', MEDIADESCRIPTION = N'company Hourly
> Backup'
>
|||Set the device to a variable that has the timestamp appended to it like so:
DECLARE @.Device NVARCHAR(500)
SET @.Device = N'E:\HourlyBackup\Company\Company backup- Hourly_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
BACKUP DATABASE [company name] TO DISK = @.Device ...
Andrew J. Kelly SQL MVP
"blueboy" <matt_meech@.hotmail.com> wrote in message
news:1140358625.726394.236770@.f14g2000cwb.googlegr oups.com...
> hi can anyone help, i have a database maintenance plan which is set to
> run hourly at the moment it overwrites the current database but i want
> to append it with a current_timestamp, here is the T-SQL i am using
> BACKUP DATABASE [company name] TO DISK = N'E:\Hourly
> Backup\Company\Company backup- Hourly.BAK' WITH INIT , NOUNLOAD ,
> RETAINDAYS = 1, NAME = N'company backup - Hourly', SKIP , STATS =
> 10, DESCRIPTION = N'Hourly Backup job runs from 7 - 9', FORMAT ,
> MEDIANAME = N'RSUK_Hourly', MEDIADESCRIPTION = N'company Hourly
> Backup'
>
|||Andrew,
Firstly many thanks but im still having problems could you look over
this T-Sql for me
DECLARE @.Device NVARCHAR(500)
SET @.Device = N'E:\Hourly Backup\company\company backup- Hourly_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
BACKUP DATABASE [Company] TO DISK = @.Device = N'E:\Hourly
Backup\Company\Company backup- Hourly_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK' WITH INIT ,
NOUNLOAD , RETAINDAYS = 1, NAME = N'Company backup - Hourly', SKIP ,
STATS = 10, DESCRIPTION = N'Hourly Backup job runs from 7 - 9',
FORMAT , MEDIANAME = N'RSUK_Hourly', MEDIADESCRIPTION = N'Company
Hourly Backup'
Any guidance would be very much appreciated.
Matt
|||You just need the "DISK = @.Device" not "Disk = @.Device = N'E:..."
Andrew J. Kelly SQL MVP
"blueboy" <matt_meech@.hotmail.com> wrote in message
news:1140444907.948174.219680@.g14g2000cwa.googlegr oups.com...
> Andrew,
> Firstly many thanks but im still having problems could you look over
> this T-Sql for me
> DECLARE @.Device NVARCHAR(500)
>
> SET @.Device = N'E:\Hourly Backup\company\company backup- Hourly_' +
> CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
>
> BACKUP DATABASE [Company] TO DISK = @.Device = N'E:\Hourly
> Backup\Company\Company backup- Hourly_' +
> CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK' WITH INIT ,
> NOUNLOAD , RETAINDAYS = 1, NAME = N'Company backup - Hourly', SKIP ,
> STATS = 10, DESCRIPTION = N'Hourly Backup job runs from 7 - 9',
> FORMAT , MEDIANAME = N'RSUK_Hourly', MEDIADESCRIPTION = N'Company
> Hourly Backup'
> Any guidance would be very much appreciated.
> Matt
>
|||Andrew,
Many thanks that has done the trick if i wanted to add the time what
would i need to add?
Once again many Thanks
|||Use one of the other options for CONVERT(). See BooksOnLine for the
available formats.
Andrew J. Kelly SQL MVP
"blueboy" <matt_meech@.hotmail.com> wrote in message
news:1140451723.194913.69430@.z14g2000cwz.googlegro ups.com...
> Andrew,
> Many thanks that has done the trick if i wanted to add the time what
> would i need to add?
> Once again many Thanks
>

database maintenance plan using T-Sql and current_timestamp

hi can anyone help, i have a database maintenance plan which is set to
run hourly at the moment it overwrites the current database but i want
to append it with a current_timestamp, here is the T-SQL i am using
BACKUP DATABASE [company name] TO DISK = N'E:\Hourly
Backup\Company\Company backup- Hourly.BAK' WITH INIT , NOUNLOAD ,
RETAINDAYS = 1, NAME = N'company backup - Hourly', SKIP , STATS =
10, DESCRIPTION = N'Hourly Backup job runs from 7 - 9', FORMAT ,
MEDIANAME = N'RSUK_Hourly', MEDIADESCRIPTION = N'company Hourly
Backup'My systemdb maintenance plan automatically appends the datestamp to the file
name:
BACKUP DATABASE [model] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\model_db_20060219091
3.BAK' WITH INIT , NOUNLOAD ,
NOSKIP , STATS = 10, NOFORMAT
Yours does not appear to be doing so. Did you create it using the wizard?
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.expertsrt.com - not your average tech Q&A site
"blueboy" <matt_meech@.hotmail.com> wrote in message
news:1140358625.726394.236770@.f14g2000cwb.googlegroups.com...
> hi can anyone help, i have a database maintenance plan which is set to
> run hourly at the moment it overwrites the current database but i want
> to append it with a current_timestamp, here is the T-SQL i am using
> BACKUP DATABASE [company name] TO DISK = N'E:\Hourly
> Backup\Company\Company backup- Hourly.BAK' WITH INIT , NOUNLOAD ,
> RETAINDAYS = 1, NAME = N'company backup - Hourly', SKIP , STATS =
> 10, DESCRIPTION = N'Hourly Backup job runs from 7 - 9', FORMAT ,
> MEDIANAME = N'RSUK_Hourly', MEDIADESCRIPTION = N'company Hourly
> Backup'
>|||Set the device to a variable that has the timestamp appended to it like so:
DECLARE @.Device NVARCHAR(500)
SET @.Device = N'E:\HourlyBackup\Company\Company backup- Hourly_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
BACKUP DATABASE [company name] TO DISK = @.Device ...
Andrew J. Kelly SQL MVP
"blueboy" <matt_meech@.hotmail.com> wrote in message
news:1140358625.726394.236770@.f14g2000cwb.googlegroups.com...
> hi can anyone help, i have a database maintenance plan which is set to
> run hourly at the moment it overwrites the current database but i want
> to append it with a current_timestamp, here is the T-SQL i am using
> BACKUP DATABASE [company name] TO DISK = N'E:\Hourly
> Backup\Company\Company backup- Hourly.BAK' WITH INIT , NOUNLOAD ,
> RETAINDAYS = 1, NAME = N'company backup - Hourly', SKIP , STATS =
> 10, DESCRIPTION = N'Hourly Backup job runs from 7 - 9', FORMAT ,
> MEDIANAME = N'RSUK_Hourly', MEDIADESCRIPTION = N'company Hourly
> Backup'
>|||Andrew,
Firstly many thanks but im still having problems could you look over
this T-Sql for me
DECLARE @.Device NVARCHAR(500)
SET @.Device = N'E:\Hourly Backup\company\company backup- Hourly_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
BACKUP DATABASE [Company] TO DISK = @.Device = N'E:\Hourly
Backup\Company\Company backup- Hourly_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK' WITH INIT ,
NOUNLOAD , RETAINDAYS = 1, NAME = N'Company backup - Hourly', SKIP ,
STATS = 10, DESCRIPTION = N'Hourly Backup job runs from 7 - 9',
FORMAT , MEDIANAME = N'RSUK_Hourly', MEDIADESCRIPTION = N'Company
Hourly Backup'
Any guidance would be very much appreciated.
Matt|||You just need the "DISK = @.Device" not "Disk = @.Device = N'E:..."
Andrew J. Kelly SQL MVP
"blueboy" <matt_meech@.hotmail.com> wrote in message
news:1140444907.948174.219680@.g14g2000cwa.googlegroups.com...
> Andrew,
> Firstly many thanks but im still having problems could you look over
> this T-Sql for me
> DECLARE @.Device NVARCHAR(500)
>
> SET @.Device = N'E:\Hourly Backup\company\company backup- Hourly_' +
> CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
>
> BACKUP DATABASE [Company] TO DISK = @.Device = N'E:\Hourly
> Backup\Company\Company backup- Hourly_' +
> CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK' WITH INIT ,
> NOUNLOAD , RETAINDAYS = 1, NAME = N'Company backup - Hourly', SKIP ,
> STATS = 10, DESCRIPTION = N'Hourly Backup job runs from 7 - 9',
> FORMAT , MEDIANAME = N'RSUK_Hourly', MEDIADESCRIPTION = N'Company
> Hourly Backup'
> Any guidance would be very much appreciated.
> Matt
>|||Andrew,
Many thanks that has done the trick if i wanted to add the time what
would i need to add'
Once again many Thanks|||Use one of the other options for CONVERT(). See BooksOnLine for the
available formats.
Andrew J. Kelly SQL MVP
"blueboy" <matt_meech@.hotmail.com> wrote in message
news:1140451723.194913.69430@.z14g2000cwz.googlegroups.com...
> Andrew,
> Many thanks that has done the trick if i wanted to add the time what
> would i need to add'
> Once again many Thanks
>