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
Showing posts with label current. Show all posts
Showing posts with label current. Show all posts
Thursday, March 29, 2012
Database Name
Hi,
Can anyone tell me how to return the current database name using T-SQL.
I have a stored proc that runs in a number of databases and would like qualify some dynamic sql with the database name. I know DB_Name exists but requires the DB_ID and DB_ID needs the DB_Name. Is there a function that just returns the current database i.e.
USE TestDB
GO
SELECT currentDBName?
Use the following function..
Select db_name()
|||Cheers...
sqldatabase 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...
> > 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!
>
>|||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','Pubs'))
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)
>
> >
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...
> > 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!
>
>|||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','Pubs'))
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)
>
> >
Sunday, March 11, 2012
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
>
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_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.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
>
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.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
>
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
>
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
>
Subscribe to:
Posts (Atom)