Backup status/timestamp

Hi,
I was wondering if anyone had a sql script that can pull the last backup
timestamp as displayed when you check database properties in Enterprise
manager.
Thanks
RustyThe data you're after is stored in msdb, a query like this :-
select top 1 *
from msdb..backupset
where database_name = 'pubs' and type = 'D'
order by backup_finish_date desc
Have a look in SQL BOL for an explanation to what all the columns mean.
HTH. Ryan
"Rusty" <Rusty@.discussions.microsoft.com> wrote in message
news:2183A80E-9299-431D-A9D2-26E318B13E8A@.microsoft.com...
> Hi,
> I was wondering if anyone had a sql script that can pull the last backup
> timestamp as displayed when you check database properties in Enterprise
> manager.
> Thanks
> Rusty|||Thanks!
I found this as well while googling.
http://searchsqlserver.techtarget.c...1178991,00.html
This is perfect cause I can code a Cold Fusion web page for the help desk to
monitor.
But Im having a devil of a time getting it to run. The table thats supposed
to be populated by the stored procedure keeps coming up with nothing in it.
#tmp is populating normally. #tmp2 was coming up with no rows either. I foun
d
what I thought was a bug where it refers to inserting data into #t instead o
f
#tmp. I changed #t to #tmp and now #tmp2 is showing at least a field but no
data.
Now Im stuck.
"Ryan" wrote:

> The data you're after is stored in msdb, a query like this :-
> select top 1 *
> from msdb..backupset
> where database_name = 'pubs' and type = 'D'
> order by backup_finish_date desc
> Have a look in SQL BOL for an explanation to what all the columns mean.
> --
> HTH. Ryan
>
> "Rusty" <Rusty@.discussions.microsoft.com> wrote in message
> news:2183A80E-9299-431D-A9D2-26E318B13E8A@.microsoft.com...
>
>

Backup status/timestamp

Hi,
I was wondering if anyone had a sql script that can pull the last backup
timestamp as displayed when you check database properties in Enterprise
manager.
Thanks
RustyThe data you're after is stored in msdb, a query like this :-
select top 1 *
from msdb..backupset
where database_name = 'pubs' and type = 'D'
order by backup_finish_date desc
Have a look in SQL BOL for an explanation to what all the columns mean.
--
HTH. Ryan
"Rusty" <Rusty@.discussions.microsoft.com> wrote in message
news:2183A80E-9299-431D-A9D2-26E318B13E8A@.microsoft.com...
> Hi,
> I was wondering if anyone had a sql script that can pull the last backup
> timestamp as displayed when you check database properties in Enterprise
> manager.
> Thanks
> Rusty|||Thanks!
I found this as well while googling.
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1178991,00.html
This is perfect cause I can code a Cold Fusion web page for the help desk to
monitor.
But Im having a devil of a time getting it to run. The table thats supposed
to be populated by the stored procedure keeps coming up with nothing in it.
#tmp is populating normally. #tmp2 was coming up with no rows either. I found
what I thought was a bug where it refers to inserting data into #t instead of
#tmp. I changed #t to #tmp and now #tmp2 is showing at least a field but no
data.
Now Im stuck.
"Ryan" wrote:
> The data you're after is stored in msdb, a query like this :-
> select top 1 *
> from msdb..backupset
> where database_name = 'pubs' and type = 'D'
> order by backup_finish_date desc
> Have a look in SQL BOL for an explanation to what all the columns mean.
> --
> HTH. Ryan
>
> "Rusty" <Rusty@.discussions.microsoft.com> wrote in message
> news:2183A80E-9299-431D-A9D2-26E318B13E8A@.microsoft.com...
> > Hi,
> >
> > I was wondering if anyone had a sql script that can pull the last backup
> > timestamp as displayed when you check database properties in Enterprise
> > manager.
> >
> > Thanks
> > Rusty
>
>

backup speed SQL 2000

I'm running a manually written nightly backup script on all dbs on a
server. it's a dev server FYI.
the script does the following for all databases:
dbcc checkdb
dbcc dbreindex (on all relevant tables) -- this is done once a week
only
backup transaction with no_log
backup database
now... the duration for this is around 2 hours, given the number &
size of the databases. however, on the night I do the dbcc dbreindex,
the duration drops to 5 minutes. i've added the dbcc dbreindex to run
on other nights, and yes it drops down to 5 minutes each time. next
time it runs without, back up to 2 hours.....
why?
if any other info is required, i will supply.
Specify an output file and check for error messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<bmwbase-newsgroup@.yahoo.com> wrote in message
news:1179959856.718711.314480@.x35g2000prf.googlegr oups.com...
> I'm running a manually written nightly backup script on all dbs on a
> server. it's a dev server FYI.
> the script does the following for all databases:
> dbcc checkdb
> dbcc dbreindex (on all relevant tables) -- this is done once a week
> only
> backup transaction with no_log
> backup database
> now... the duration for this is around 2 hours, given the number &
> size of the databases. however, on the night I do the dbcc dbreindex,
> the duration drops to 5 minutes. i've added the dbcc dbreindex to run
> on other nights, and yes it drops down to 5 minutes each time. next
> time it runs without, back up to 2 hours.....
> why?
> if any other info is required, i will supply.
>

backup speed SQL 2000

I'm running a manually written nightly backup script on all dbs on a
server. it's a dev server FYI.
the script does the following for all databases:
dbcc checkdb
dbcc dbreindex (on all relevant tables) -- this is done once a week
only
backup transaction with no_log
backup database
now... the duration for this is around 2 hours, given the number &
size of the databases. however, on the night I do the dbcc dbreindex,
the duration drops to 5 minutes. i've added the dbcc dbreindex to run
on other nights, and yes it drops down to 5 minutes each time. next
time it runs without, back up to 2 hours.....
why?
if any other info is required, i will supply.Specify an output file and check for error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<bmwbase-newsgroup@.yahoo.com> wrote in message
news:1179959856.718711.314480@.x35g2000prf.googlegroups.com...
> I'm running a manually written nightly backup script on all dbs on a
> server. it's a dev server FYI.
> the script does the following for all databases:
> dbcc checkdb
> dbcc dbreindex (on all relevant tables) -- this is done once a week
> only
> backup transaction with no_log
> backup database
> now... the duration for this is around 2 hours, given the number &
> size of the databases. however, on the night I do the dbcc dbreindex,
> the duration drops to 5 minutes. i've added the dbcc dbreindex to run
> on other nights, and yes it drops down to 5 minutes each time. next
> time it runs without, back up to 2 hours.....
> why?
> if any other info is required, i will supply.
>sql

backup speed SQL 2000

I'm running a manually written nightly backup script on all dbs on a
server. it's a dev server FYI.
the script does the following for all databases:
dbcc checkdb
dbcc dbreindex (on all relevant tables) -- this is done once a week
only
backup transaction with no_log
backup database
now... the duration for this is around 2 hours, given the number &
size of the databases. however, on the night I do the dbcc dbreindex,
the duration drops to 5 minutes. i've added the dbcc dbreindex to run
on other nights, and yes it drops down to 5 minutes each time. next
time it runs without, back up to 2 hours.....
why?
if any other info is required, i will supply.Specify an output file and check for error messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<bmwbase-newsgroup@.yahoo.com> wrote in message
news:1179959856.718711.314480@.x35g2000prf.googlegroups.com...
> I'm running a manually written nightly backup script on all dbs on a
> server. it's a dev server FYI.
> the script does the following for all databases:
> dbcc checkdb
> dbcc dbreindex (on all relevant tables) -- this is done once a week
> only
> backup transaction with no_log
> backup database
> now... the duration for this is around 2 hours, given the number &
> size of the databases. however, on the night I do the dbcc dbreindex,
> the duration drops to 5 minutes. i've added the dbcc dbreindex to run
> on other nights, and yes it drops down to 5 minutes each time. next
> time it runs without, back up to 2 hours.....
> why?
> if any other info is required, i will supply.
>

backup size and initial database size

SQL server 2000
Hi
How can i find
1. Information about all/current backup files, size of backup file on
server using sql query or a script.
2. Initial size of all databases (when they were installed) and current size
of all databases.
Thanks
ontario, canada
I have information about all database backups for last two years (Including
database size in table Backupset in MSDB.
Does % increase in database backup size reflects the % increase in database
size?
ontario, canada
"db" wrote:
> SQL server 2000
> Hi
> How can i find
> 1. Information about all/current backup files, size of backup file on
> server using sql query or a script.
> 2. Initial size of all databases (when they were installed) and current size
> of all databases.
> Thanks
> ontario, canada
|||I am using
select database_name,database_creation_date,backup_start_ date,backup_size
from backupset order by database_name,backup_size desc
to get the bacup size information. I get the information.
I want to select a subset of records which give me information for backupset
for one day of every month (10-...200?). I.e monthwise backup set
information for 10-jan, 10-feb-10-mar...etc). When I issue FOLLOWING COMMAND
select database_name,database_creation_date,backup_start_ date,backup_size
from backupset WHERE BACKUP_START_DATE LIKE '10-...200?) order by
database_name,backup_size desc
I do not get the desired result. What is correct sql query?
ontario, canada
"db" wrote:
[vbcol=seagreen]
> I have information about all database backups for last two years (Including
> database size in table Backupset in MSDB.
> Does % increase in database backup size reflects the % increase in database
> size?
> --
> ontario, canada
> "db" wrote:
|||Select ...
Where BACKUP_START_DATE >= '20000101' And Day(BACKUP_START_DATE) = 10
Tom
"db" <db@.discussions.microsoft.com> wrote in message
news:BCC341DB-9254-43E5-B4FC-08F90F86A325@.microsoft.com...[vbcol=seagreen]
>I am using
> select database_name,database_creation_date,backup_start_ date,backup_size
> from backupset order by database_name,backup_size desc
> to get the bacup size information. I get the information.
> I want to select a subset of records which give me information for
> backupset
> for one day of every month (10-...200?). I.e monthwise backup set
> information for 10-jan, 10-feb-10-mar...etc). When I issue FOLLOWING
> COMMAND
> select database_name,database_creation_date,backup_start_ date,backup_size
> from backupset WHERE BACKUP_START_DATE LIKE '10-...200?) order by
> database_name,backup_size desc
> I do not get the desired result. What is correct sql query?
>
> --
> ontario, canada
>
> "db" wrote:

backup size and initial database size

SQL server 2000
Hi
How can i find
1. Information about all/current backup files, size of backup file on
server using sql query or a script.
2. Initial size of all databases (when they were installed) and current size
of all databases.
Thanks
--
ontario, canadaI have information about all database backups for last two years (Including
database size in table Backupset in MSDB.
Does % increase in database backup size reflects the % increase in database
size'
--
ontario, canada
"db" wrote:
> SQL server 2000
> Hi
> How can i find
> 1. Information about all/current backup files, size of backup file on
> server using sql query or a script.
> 2. Initial size of all databases (when they were installed) and current size
> of all databases.
> Thanks
> ontario, canada|||I am using
select database_name,database_creation_date,backup_start_date,backup_size
from backupset order by database_name,backup_size desc
to get the bacup size information. I get the information.
I want to select a subset of records which give me information for backupset
for one day of every month (10-...200?). I.e monthwise backup set
information for 10-jan, 10-feb-10-mar...etc). When I issue FOLLOWING COMMAND
select database_name,database_creation_date,backup_start_date,backup_size
from backupset WHERE BACKUP_START_DATE LIKE '10-...200?) order by
database_name,backup_size desc
I do not get the desired result. What is correct sql query?
ontario, canada
"db" wrote:
> I have information about all database backups for last two years (Including
> database size in table Backupset in MSDB.
> Does % increase in database backup size reflects the % increase in database
> size'
> --
> ontario, canada
> "db" wrote:
> > SQL server 2000
> > Hi
> > How can i find
> > 1. Information about all/current backup files, size of backup file on
> > server using sql query or a script.
> > 2. Initial size of all databases (when they were installed) and current size
> > of all databases.
> > Thanks
> > ontario, canada|||Select ...
Where BACKUP_START_DATE >= '20000101' And Day(BACKUP_START_DATE) = 10
Tom
"db" <db@.discussions.microsoft.com> wrote in message
news:BCC341DB-9254-43E5-B4FC-08F90F86A325@.microsoft.com...
>I am using
> select database_name,database_creation_date,backup_start_date,backup_size
> from backupset order by database_name,backup_size desc
> to get the bacup size information. I get the information.
> I want to select a subset of records which give me information for
> backupset
> for one day of every month (10-...200?). I.e monthwise backup set
> information for 10-jan, 10-feb-10-mar...etc). When I issue FOLLOWING
> COMMAND
> select database_name,database_creation_date,backup_start_date,backup_size
> from backupset WHERE BACKUP_START_DATE LIKE '10-...200?) order by
> database_name,backup_size desc
> I do not get the desired result. What is correct sql query?
>
> --
> ontario, canada
>
> "db" wrote:
>> I have information about all database backups for last two years
>> (Including
>> database size in table Backupset in MSDB.
>> Does % increase in database backup size reflects the % increase in
>> database
>> size'
>> --
>> ontario, canada
>> "db" wrote:
>> > SQL server 2000
>> > Hi
>> > How can i find
>> > 1. Information about all/current backup files, size of backup file on
>> > server using sql query or a script.
>> > 2. Initial size of all databases (when they were installed) and current
>> > size
>> > of all databases.
>> > Thanks
>> > ontario, canada

Backup Script Revisions Help

We recently made some changes to our hourly transaction log backups script
that allows us to backup the logs more than once an hour. The new script is
running well, but the old backups are not deleted from the disk, although the
retaindays option is set to 0.
I have done some research on this, but I cannot find an answer. I am sure
this is not a permissions issue, as the script does run successfully.
New Script (successful, but old backups are not deleted):
DECLARE @.FileNameAS VARCHAR(200)
DECLARE @.HoursAS CHAR(02)
DECLARE @.MinsAS CHAR(02)
DECLARE @.SecsAS CHAR(02)
DECLARE @.HoursMins AS VARCHAR(40)
DECLARE @.ScriptTimeAS DATETIME
SET @.ScriptTime = GETDATE()
-- Determine if time value is < 2 digits
SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
SET @.Mins= substring(convert(varchar, @.ScriptTime, 108), 4, 2)
SET @.Secs= substring(convert(varchar, @.ScriptTime, 108), 7, 2)
-- PRINT @.Hours
-- PRINT @.Mins
-- PRINT @.Secs
-- PRINT convert(varchar, @.ScriptTime, 108)
SET @.HoursMins = @.Hours + @.Mins + @.Secs
-- PRINT @.HoursMins
SET @.FileName ='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
-- PRINT @.FileName
Old Script (backups were deleted successfully)
declare @.FileName1 as varchar(100)
set @.FileName1='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
getdate(),108),2)+ '.bak'
backup log DatabaseA
to disk =@.FileName1
WITH RETAINDAYS = 0
Can someone please give me some guidance on how to modify our new script to
ensure that backups are deleted successfully? Thanks in advance for your
help.
Try using NOINIT instead of RETAINDAYS.
"Wen" wrote:

> We recently made some changes to our hourly transaction log backups script
> that allows us to backup the logs more than once an hour. The new script is
> running well, but the old backups are not deleted from the disk, although the
> retaindays option is set to 0.
> I have done some research on this, but I cannot find an answer. I am sure
> this is not a permissions issue, as the script does run successfully.
> New Script (successful, but old backups are not deleted):
> DECLARE @.FileNameAS VARCHAR(200)
> DECLARE @.HoursAS CHAR(02)
> DECLARE @.MinsAS CHAR(02)
> DECLARE @.SecsAS CHAR(02)
> DECLARE @.HoursMins AS VARCHAR(40)
> DECLARE @.ScriptTimeAS DATETIME
> SET @.ScriptTime = GETDATE()
> -- Determine if time value is < 2 digits
> SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
> SET @.Mins= substring(convert(varchar, @.ScriptTime, 108), 4, 2)
> SET @.Secs= substring(convert(varchar, @.ScriptTime, 108), 7, 2)
> -- PRINT @.Hours
> -- PRINT @.Mins
> -- PRINT @.Secs
> -- PRINT convert(varchar, @.ScriptTime, 108)
> SET @.HoursMins = @.Hours + @.Mins + @.Secs
> -- PRINT @.HoursMins
> SET @.FileName ='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
> BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
> -- PRINT @.FileName
>
> Old Script (backups were deleted successfully)
> declare @.FileName1 as varchar(100)
> set @.FileName1='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
> getdate(),108),2)+ '.bak'
> backup log DatabaseA
> to disk =@.FileName1
> WITH RETAINDAYS = 0
> Can someone please give me some guidance on how to modify our new script to
> ensure that backups are deleted successfully? Thanks in advance for your
> help.

Backup Script Revisions Help

We recently made some changes to our hourly transaction log backups script
that allows us to backup the logs more than once an hour. The new script is
running well, but the old backups are not deleted from the disk, although th
e
retaindays option is set to 0.
I have done some research on this, but I cannot find an answer. I am sure
this is not a permissions issue, as the script does run successfully.
New Script (successful, but old backups are not deleted):
DECLARE @.FileName AS VARCHAR(200)
DECLARE @.Hours AS CHAR(02)
DECLARE @.Mins AS CHAR(02)
DECLARE @.Secs AS CHAR(02)
DECLARE @.HoursMins AS VARCHAR(40)
DECLARE @.ScriptTime AS DATETIME
SET @.ScriptTime = GETDATE()
-- Determine if time value is < 2 digits
SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
SET @.Mins = substring(convert(varchar, @.ScriptTime, 108), 4, 2)
SET @.Secs = substring(convert(varchar, @.ScriptTime, 108), 7, 2)
-- PRINT @.Hours
-- PRINT @.Mins
-- PRINT @.Secs
-- PRINT convert(varchar, @.ScriptTime, 108)
SET @.HoursMins = @.Hours + @.Mins + @.Secs
-- PRINT @.HoursMins
SET @.FileName ='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
-- PRINT @.FileName
Old Script (backups were deleted successfully)
declare @.FileName1 as varchar(100)
set @.FileName1='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
getdate(),108),2)+ '.bak'
backup log DatabaseA
to disk =@.FileName1
WITH RETAINDAYS = 0
Can someone please give me some guidance on how to modify our new script to
ensure that backups are deleted successfully? Thanks in advance for your
help.Try using NOINIT instead of RETAINDAYS.
"Wen" wrote:

> We recently made some changes to our hourly transaction log backups script
> that allows us to backup the logs more than once an hour. The new script
is
> running well, but the old backups are not deleted from the disk, although
the
> retaindays option is set to 0.
> I have done some research on this, but I cannot find an answer. I am sure
> this is not a permissions issue, as the script does run successfully.
> New Script (successful, but old backups are not deleted):
> DECLARE @.FileName AS VARCHAR(200)
> DECLARE @.Hours AS CHAR(02)
> DECLARE @.Mins AS CHAR(02)
> DECLARE @.Secs AS CHAR(02)
> DECLARE @.HoursMins AS VARCHAR(40)
> DECLARE @.ScriptTime AS DATETIME
> SET @.ScriptTime = GETDATE()
> -- Determine if time value is < 2 digits
> SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
> SET @.Mins = substring(convert(varchar, @.ScriptTime, 108), 4, 2)
> SET @.Secs = substring(convert(varchar, @.ScriptTime, 108), 7, 2)
> -- PRINT @.Hours
> -- PRINT @.Mins
> -- PRINT @.Secs
> -- PRINT convert(varchar, @.ScriptTime, 108)
> SET @.HoursMins = @.Hours + @.Mins + @.Secs
> -- PRINT @.HoursMins
> SET @.FileName ='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
> BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
> -- PRINT @.FileName
>
> Old Script (backups were deleted successfully)
> declare @.FileName1 as varchar(100)
> set @.FileName1='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
> getdate(),108),2)+ '.bak'
> backup log DatabaseA
> to disk =@.FileName1
> WITH RETAINDAYS = 0
> Can someone please give me some guidance on how to modify our new script t
o
> ensure that backups are deleted successfully? Thanks in advance for your
> help.

Backup Script Revisions Help

We recently made some changes to our hourly transaction log backups script
that allows us to backup the logs more than once an hour. The new script is
running well, but the old backups are not deleted from the disk, although the
retaindays option is set to 0.
I have done some research on this, but I cannot find an answer. I am sure
this is not a permissions issue, as the script does run successfully.
New Script (successful, but old backups are not deleted):
DECLARE @.FileName AS VARCHAR(200)
DECLARE @.Hours AS CHAR(02)
DECLARE @.Mins AS CHAR(02)
DECLARE @.Secs AS CHAR(02)
DECLARE @.HoursMins AS VARCHAR(40)
DECLARE @.ScriptTime AS DATETIME
SET @.ScriptTime = GETDATE()
-- Determine if time value is < 2 digits
SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
SET @.Mins = substring(convert(varchar, @.ScriptTime, 108), 4, 2)
SET @.Secs = substring(convert(varchar, @.ScriptTime, 108), 7, 2)
-- PRINT @.Hours
-- PRINT @.Mins
-- PRINT @.Secs
-- PRINT convert(varchar, @.ScriptTime, 108)
SET @.HoursMins = @.Hours + @.Mins + @.Secs
-- PRINT @.HoursMins
SET @.FileName ='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
-- PRINT @.FileName
Old Script (backups were deleted successfully)
declare @.FileName1 as varchar(100)
set @.FileName1='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
getdate(),108),2)+ '.bak'
backup log DatabaseA
to disk =@.FileName1
WITH RETAINDAYS = 0
Can someone please give me some guidance on how to modify our new script to
ensure that backups are deleted successfully? Thanks in advance for your
help.Try using NOINIT instead of RETAINDAYS.
"Wen" wrote:
> We recently made some changes to our hourly transaction log backups script
> that allows us to backup the logs more than once an hour. The new script is
> running well, but the old backups are not deleted from the disk, although the
> retaindays option is set to 0.
> I have done some research on this, but I cannot find an answer. I am sure
> this is not a permissions issue, as the script does run successfully.
> New Script (successful, but old backups are not deleted):
> DECLARE @.FileName AS VARCHAR(200)
> DECLARE @.Hours AS CHAR(02)
> DECLARE @.Mins AS CHAR(02)
> DECLARE @.Secs AS CHAR(02)
> DECLARE @.HoursMins AS VARCHAR(40)
> DECLARE @.ScriptTime AS DATETIME
> SET @.ScriptTime = GETDATE()
> -- Determine if time value is < 2 digits
> SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
> SET @.Mins = substring(convert(varchar, @.ScriptTime, 108), 4, 2)
> SET @.Secs = substring(convert(varchar, @.ScriptTime, 108), 7, 2)
> -- PRINT @.Hours
> -- PRINT @.Mins
> -- PRINT @.Secs
> -- PRINT convert(varchar, @.ScriptTime, 108)
> SET @.HoursMins = @.Hours + @.Mins + @.Secs
> -- PRINT @.HoursMins
> SET @.FileName ='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
> BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
> -- PRINT @.FileName
>
> Old Script (backups were deleted successfully)
> declare @.FileName1 as varchar(100)
> set @.FileName1='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
> getdate(),108),2)+ '.bak'
> backup log DatabaseA
> to disk =@.FileName1
> WITH RETAINDAYS = 0
> Can someone please give me some guidance on how to modify our new script to
> ensure that backups are deleted successfully? Thanks in advance for your
> help.sql

Backup Script Completing But Not Working

I have a SQL Server Agent job that executes the script listed below (using a step of type "Analysis Services Command") to do a backup of an SSAS database on a nightly basis. The job takes a while (say 15 mins or so) and appears to be busily doing something (meaning the CPU's on the server have activity). However, after the script completes, there is no backup file anywhere to be found. Anyone have any idea what might be happening?

BTW, if I execute this command manually as an XMLA command while in a query window in SSMS, it seems to complete successfully (producing the .abf file) every time...

Dave Fackler

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>DatabaseXYZ</DatabaseID>
</Object>
<File>DatabaseXYZ.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>

There is a problem with SQL Agent not reporting propertly case of failed XMLA command.

It should have been fixed in SP2. Try installing CTP of SP2 and see if you are geting failure of SQL Agent Job.

Another suggestion. Make sure your SQL Agent job has server name defined correctly.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward,

I'll take a look at SP2. However, even if that resolves the issue of SQL Agent not reporting when an XMLA command fails, it doesn't really help with WHY the XMLA command would be failing in the first place. The server name is definitely defined correctly and the server seems to be doing something while the command executes. But no .abf file is generated. Is there some way I can troubleshoot this issue to determine why the XMLA command might be failing?

Dave F.

|||

Once you get a fix in SQL Agent you'll be able to get a useful error message telling you the nature of the problem. And that should help troubleshooting SQL Agent job.

For now you can try installing CTP of SP2.

Another idea for you is to use ascmd utility that allows you to execute XMLA commands. It has great functionality allows for having parametric XMLA command, where you can simply plug name of your DB and that would take care backing up your database. It also allows logging output of XMLA command's execution. Once you get it working, you can use SQL Agent to schedule backup using ascmd.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I am running SQL 2005 with SP2 and have the same issue.

When I use a SQL Agent job with a Sql Server Analysis Services Command it does not report any error or failure in that command.

An example command is:

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Object>
<DatabaseID>PGPDailySales</DatabaseID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>

If the cube definition or data has errors so this command fails, the Agent Job step still reports success.

How can this issue be reported to Microsoft so it may be fixed in the next version?

Backup Script Completing But Not Working

I have a SQL Server Agent job that executes the script listed below (using a step of type "Analysis Services Command") to do a backup of an SSAS database on a nightly basis. The job takes a while (say 15 mins or so) and appears to be busily doing something (meaning the CPU's on the server have activity). However, after the script completes, there is no backup file anywhere to be found. Anyone have any idea what might be happening?

BTW, if I execute this command manually as an XMLA command while in a query window in SSMS, it seems to complete successfully (producing the .abf file) every time...

Dave Fackler

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>DatabaseXYZ</DatabaseID>
</Object>
<File>DatabaseXYZ.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>

There is a problem with SQL Agent not reporting propertly case of failed XMLA command.

It should have been fixed in SP2. Try installing CTP of SP2 and see if you are geting failure of SQL Agent Job.

Another suggestion. Make sure your SQL Agent job has server name defined correctly.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward,

I'll take a look at SP2. However, even if that resolves the issue of SQL Agent not reporting when an XMLA command fails, it doesn't really help with WHY the XMLA command would be failing in the first place. The server name is definitely defined correctly and the server seems to be doing something while the command executes. But no .abf file is generated. Is there some way I can troubleshoot this issue to determine why the XMLA command might be failing?

Dave F.

|||

Once you get a fix in SQL Agent you'll be able to get a useful error message telling you the nature of the problem. And that should help troubleshooting SQL Agent job.

For now you can try installing CTP of SP2.

Another idea for you is to use ascmd utility that allows you to execute XMLA commands. It has great functionality allows for having parametric XMLA command, where you can simply plug name of your DB and that would take care backing up your database. It also allows logging output of XMLA command's execution. Once you get it working, you can use SQL Agent to schedule backup using ascmd.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I am running SQL 2005 with SP2 and have the same issue.

When I use a SQL Agent job with a Sql Server Analysis Services Command it does not report any error or failure in that command.

An example command is:

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Object>
<DatabaseID>PGPDailySales</DatabaseID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>

If the cube definition or data has errors so this command fails, the Agent Job step still reports success.

How can this issue be reported to Microsoft so it may be fixed in the next version?

Backup script

Hi,

I'm currently working on a backup script for my MSSQL databases. I have managed to make a working backup command but I have one problem.
I need every new backup file to have a uniqe filename, for example backup_UNIQE.bak. How can I make a variable that can do this?

Alexander

Here's the basis of one that can be used which uses the date as a unique identifier:

Code Snippet

declare @.filename nvarchar(2000)

set @.filename = 'c:\yourbackupdirectory\dbname_bk_' + convert(varchar,getdate(),112) + '.bak'

backup database northwind
to disk = @.filename

HTH!

|||

Hi,

Can you stick a date on the end of it? See below and then pass the filename into you script somehow.

declare @.datet varchar(10), @.filename varchar(20)

set @.datet = convert(varchar(10),getdate(),112)

set @.filename = 'backup'+@.datet+'.bak'

print @.filename

will give you

backup20070824.bak

You could always add the time to it :

set @.datet = replace(convert(varchar(8),getdate(),114),':', '')

Any help to you?

Cheers

Matt

|||Thank you both! This worked like a charm!

Matt Tolhurst:

Can you please explain this one: set @.datet = replace(convert(varchar(8),getdate(),114),':', '') ?

I'm not TSQL coder yet, and I would appricitate it very much!

- Alexander
|||

Alexander Davidsen wrote:

Can you please explain this one: set @.datet = replace(convert(varchar(8),getdate(),114),':', '') ?

Hi,

That bit is simply taking the time part of the getdate function and removing the colons, as you don't really want to put colons in the file name.

declare @.datet2 datetime, @.datet varchar(10), @.filename varchar(20)

set @.datet2 = getdate()

print @.datet2 --

Aug 24 2007 11:07AM

set @.datet = convert(varchar(10),@.datet2,112)

print @.datet

20070824

here is the time part:

set @.datet = convert(varchar(8),@.datet2,114)

print @.datet

11:07:44

set @.datet = replace(convert(varchar(8),@.datet2,114),':', '')

print @.datet

110744

Any clearer?

Matt

|||Thanks Matt

This made it alot clearer!

Have a nice weekend!

- Alexander

Backup script

Hello Friends,
I am looking for a script to automate my sql2000 databases backup. Currently
I am able to backup all databases but next backup will override my last
backup. Is there a script I can use which backup a database and then asign it
a new file name to it so I can keep backup of all databases for a week. Also
is it possible to create a job to delete all backups which are older than one
week?
I thank you for your help...
Regards,
Jamil
I would think the easiest way to do this is using Maintenance Plans from
Enterprise Manager.
http://www.aspfaq.com/
(Reverse address to reply.)
"Jamil Ahsan" <JamilAhsan@.discussions.microsoft.com> wrote in message
news:74EE2CC0-DF32-47D2-8D92-C78CE88A93D8@.microsoft.com...
> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup.
Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign
it
> a new file name to it so I can keep backup of all databases for a week.
Also
> is it possible to create a job to delete all backups which are older than
one
> week?
> I thank you for your help...
> Regards,
> Jamil
|||The following scripts will backup the database and name the backup along with
the date...
try to create the following script:
After executing the following scripts, you may add another VB script to
delete any files more than 7 days old
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
DECLARE cur_DBs CURSOR STATIC LOCAL FOR
SELECT Catalog_Name FROM Information_Schema.Schemata
WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
OPEN cur_DBs FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
PRINT 'Backing up database ' + @.DBName
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD , NAME =
@.Name, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
"Jamil Ahsan" wrote:

> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup. Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign it
> a new file name to it so I can keep backup of all databases for a week. Also
> is it possible to create a job to delete all backups which are older than one
> week?
> I thank you for your help...
> Regards,
> Jamil
|||http://www.mindsdoor.net/SQLAdmin/Ba...Databases.html
This allows you to specify how long you want the backup files to be retained.
Also make sure yyou test the backups - this wil do it automatically
http://www.mindsdoor.net/SQLAdmin/s_...estBackup.html
http://www.mindsdoor.net/SQLAdmin/s_...estBackup.html
"Jamil Ahsan" wrote:

> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup. Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign it
> a new file name to it so I can keep backup of all databases for a week. Also
> is it possible to create a job to delete all backups which are older than one
> week?
> I thank you for your help...
> Regards,
> Jamil

Backup script

I want to create a simple SQL 2005 database backup script to run a scheduled
times. This was easy with SQL 2000 but I can't find a similar process in
2005. Can anyone tell me where I can find it? Thanks.
David
David,
In SQL Server Management Studio under the node Management / Maintenance
Plans you can create a maintenance plan using the Maintenance Plan Wizard
that backs up the databases. If you back up to disk using the appropriate
BACK UP DATABASE settings you should also be sure to check and fill out the
information for the MAINTENANCE CLEANUP TASK so that old backups get
deleted.
Is that what you are looking for?
RLF
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OFL4DY5MIHA.5400@.TK2MSFTNGP04.phx.gbl...
>I want to create a simple SQL 2005 database backup script to run a
>scheduled times. This was easy with SQL 2000 but I can't find a similar
>process in 2005. Can anyone tell me where I can find it? Thanks.
> David
>
|||Alternatively,
In SQL Server 2005 every nearly window is scriptable. So, go to a database
and right click on it and Tasks\Back up... make your changes from the GUI
and you'll see a button on top of the Back Up Database window which is
labeled "Script". There is an arrow near it, click on it and you'll see your
choices.
You can use these scripts to create Jobs from the SQL Server Agent.
Ekrem nsoy
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OFL4DY5MIHA.5400@.TK2MSFTNGP04.phx.gbl...
>I want to create a simple SQL 2005 database backup script to run a
>scheduled times. This was easy with SQL 2000 but I can't find a similar
>process in 2005. Can anyone tell me where I can find it? Thanks.
> David
>
|||Thank you both. That is what I needed.
David
"Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
news:D82FA9CF-70A7-4802-A7C2-8C1BA3B053E8@.microsoft.com...
> Alternatively,
> In SQL Server 2005 every nearly window is scriptable. So, go to a database
> and right click on it and Tasks\Back up... make your changes from the GUI
> and you'll see a button on top of the Back Up Database window which is
> labeled "Script". There is an arrow near it, click on it and you'll see
> your choices.
> You can use these scripts to create Jobs from the SQL Server Agent.
> --
> Ekrem nsoy
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:OFL4DY5MIHA.5400@.TK2MSFTNGP04.phx.gbl...
>
sql

Backup script

Hello Friends,
I am looking for a script to automate my sql2000 databases backup. Currently
I am able to backup all databases but next backup will override my last
backup. Is there a script I can use which backup a database and then asign i
t
a new file name to it so I can keep backup of all databases for a week. Also
is it possible to create a job to delete all backups which are older than on
e
week?
I thank you for your help...
Regards,
JamilI would think the easiest way to do this is using Maintenance Plans from
Enterprise Manager.
http://www.aspfaq.com/
(Reverse address to reply.)
"Jamil Ahsan" <JamilAhsan@.discussions.microsoft.com> wrote in message
news:74EE2CC0-DF32-47D2-8D92-C78CE88A93D8@.microsoft.com...
> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup.
Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign
it
> a new file name to it so I can keep backup of all databases for a week.
Also
> is it possible to create a job to delete all backups which are older than
one
> week?
> I thank you for your help...
> Regards,
> Jamil|||The following scripts will backup the database and name the backup along wit
h
the date...
try to create the following script:
After executing the following scripts, you may add another VB script to
delete any files more than 7 days old
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
DECLARE cur_DBs CURSOR STATIC LOCAL FOR
SELECT Catalog_Name FROM Information_Schema.Schemata
WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
OPEN cur_DBs FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
PRINT 'Backing up database ' + @.DBName
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD , NAME =
@.Name, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
"Jamil Ahsan" wrote:

> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup. Current
ly
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign
it
> a new file name to it so I can keep backup of all databases for a week. Al
so
> is it possible to create a job to delete all backups which are older than
one
> week?
> I thank you for your help...
> Regards,
> Jamil|||http://www.mindsdoor.net/SQLAdmin/B...lDatabases.html
This allows you to specify how long you want the backup files to be retained
.
Also make sure yyou test the backups - this wil do it automatically
http://www.mindsdoor.net/SQLAdmin/s...testBackup.html
http://www.mindsdoor.net/SQLAdmin/s...testBackup.html
"Jamil Ahsan" wrote:

> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup. Current
ly
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign
it
> a new file name to it so I can keep backup of all databases for a week. Al
so
> is it possible to create a job to delete all backups which are older than
one
> week?
> I thank you for your help...
> Regards,
> Jamil

Backup script

I want to create a simple SQL 2005 database backup script to run a scheduled
times. This was easy with SQL 2000 but I can't find a similar process in
2005. Can anyone tell me where I can find it? Thanks.
DavidDavid,
In SQL Server Management Studio under the node Management / Maintenance
Plans you can create a maintenance plan using the Maintenance Plan Wizard
that backs up the databases. If you back up to disk using the appropriate
BACK UP DATABASE settings you should also be sure to check and fill out the
information for the MAINTENANCE CLEANUP TASK so that old backups get
deleted.
Is that what you are looking for?
RLF
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OFL4DY5MIHA.5400@.TK2MSFTNGP04.phx.gbl...
>I want to create a simple SQL 2005 database backup script to run a
>scheduled times. This was easy with SQL 2000 but I can't find a similar
>process in 2005. Can anyone tell me where I can find it? Thanks.
> David
>|||Alternatively,
In SQL Server 2005 every nearly window is scriptable. So, go to a database
and right click on it and Tasks\Back up... make your changes from the GUI
and you'll see a button on top of the Back Up Database window which is
labeled "Script". There is an arrow near it, click on it and you'll see your
choices.
You can use these scripts to create Jobs from the SQL Server Agent.
Ekrem nsoy
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OFL4DY5MIHA.5400@.TK2MSFTNGP04.phx.gbl...
>I want to create a simple SQL 2005 database backup script to run a
>scheduled times. This was easy with SQL 2000 but I can't find a similar
>process in 2005. Can anyone tell me where I can find it? Thanks.
> David
>|||Thank you both. That is what I needed.
David
"Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
news:D82FA9CF-70A7-4802-A7C2-8C1BA3B053E8@.microsoft.com...
> Alternatively,
> In SQL Server 2005 every nearly window is scriptable. So, go to a database
> and right click on it and Tasks\Back up... make your changes from the GUI
> and you'll see a button on top of the Back Up Database window which is
> labeled "Script". There is an arrow near it, click on it and you'll see
> your choices.
> You can use these scripts to create Jobs from the SQL Server Agent.
> --
> Ekrem nsoy
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:OFL4DY5MIHA.5400@.TK2MSFTNGP04.phx.gbl...
>

Backup script

I want to create a simple SQL 2005 database backup script to run a scheduled
times. This was easy with SQL 2000 but I can't find a similar process in
2005. Can anyone tell me where I can find it? Thanks.
DavidDavid,
In SQL Server Management Studio under the node Management / Maintenance
Plans you can create a maintenance plan using the Maintenance Plan Wizard
that backs up the databases. If you back up to disk using the appropriate
BACK UP DATABASE settings you should also be sure to check and fill out the
information for the MAINTENANCE CLEANUP TASK so that old backups get
deleted.
Is that what you are looking for?
RLF
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OFL4DY5MIHA.5400@.TK2MSFTNGP04.phx.gbl...
>I want to create a simple SQL 2005 database backup script to run a
>scheduled times. This was easy with SQL 2000 but I can't find a similar
>process in 2005. Can anyone tell me where I can find it? Thanks.
> David
>|||Alternatively,
In SQL Server 2005 every nearly window is scriptable. So, go to a database
and right click on it and Tasks\Back up... make your changes from the GUI
and you'll see a button on top of the Back Up Database window which is
labeled "Script". There is an arrow near it, click on it and you'll see your
choices.
You can use these scripts to create Jobs from the SQL Server Agent.
--
Ekrem Önsoy
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OFL4DY5MIHA.5400@.TK2MSFTNGP04.phx.gbl...
>I want to create a simple SQL 2005 database backup script to run a
>scheduled times. This was easy with SQL 2000 but I can't find a similar
>process in 2005. Can anyone tell me where I can find it? Thanks.
> David
>|||Thank you both. That is what I needed.
David
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:D82FA9CF-70A7-4802-A7C2-8C1BA3B053E8@.microsoft.com...
> Alternatively,
> In SQL Server 2005 every nearly window is scriptable. So, go to a database
> and right click on it and Tasks\Back up... make your changes from the GUI
> and you'll see a button on top of the Back Up Database window which is
> labeled "Script". There is an arrow near it, click on it and you'll see
> your choices.
> You can use these scripts to create Jobs from the SQL Server Agent.
> --
> Ekrem Önsoy
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:OFL4DY5MIHA.5400@.TK2MSFTNGP04.phx.gbl...
>>I want to create a simple SQL 2005 database backup script to run a
>>scheduled times. This was easy with SQL 2000 but I can't find a similar
>>process in 2005. Can anyone tell me where I can find it? Thanks.
>> David
>

Backup script

Hello Friends,
I am looking for a script to automate my sql2000 databases backup. Currently
I am able to backup all databases but next backup will override my last
backup. Is there a script I can use which backup a database and then asign it
a new file name to it so I can keep backup of all databases for a week. Also
is it possible to create a job to delete all backups which are older than one
week?
I thank you for your help...
Regards,
JamilI would think the easiest way to do this is using Maintenance Plans from
Enterprise Manager.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Jamil Ahsan" <JamilAhsan@.discussions.microsoft.com> wrote in message
news:74EE2CC0-DF32-47D2-8D92-C78CE88A93D8@.microsoft.com...
> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup.
Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign
it
> a new file name to it so I can keep backup of all databases for a week.
Also
> is it possible to create a job to delete all backups which are older than
one
> week?
> I thank you for your help...
> Regards,
> Jamil|||The following scripts will backup the database and name the backup along with
the date...
try to create the following script:
After executing the following scripts, you may add another VB script to
delete any files more than 7 days old
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
DECLARE cur_DBs CURSOR STATIC LOCAL FOR
SELECT Catalog_Name FROM Information_Schema.Schemata
WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
OPEN cur_DBs FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
PRINT 'Backing up database ' + @.DBName
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD , NAME =@.Name, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
"Jamil Ahsan" wrote:
> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup. Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign it
> a new file name to it so I can keep backup of all databases for a week. Also
> is it possible to create a job to delete all backups which are older than one
> week?
> I thank you for your help...
> Regards,
> Jamil|||http://www.mindsdoor.net/SQLAdmin/BackupAllDatabases.html
This allows you to specify how long you want the backup files to be retained.
Also make sure yyou test the backups - this wil do it automatically
http://www.mindsdoor.net/SQLAdmin/s_CopyLatestBackup.html
http://www.mindsdoor.net/SQLAdmin/s_RestoreLatestBackup.html
"Jamil Ahsan" wrote:
> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup. Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign it
> a new file name to it so I can keep backup of all databases for a week. Also
> is it possible to create a job to delete all backups which are older than one
> week?
> I thank you for your help...
> Regards,
> Jamil

Backup Schedule

Hi
I want to take a backup of SQL Server database on weekly
basis. How can I set up things on my server ? any script
will have to be written or any tool to be used ? pl.
help..
JayHi,
You can use database maintenance plans (Enterprise Manager) to create a
backup job which can be scheduled to execute every week once.
Thanks
Hari
MCDBA
"Jay" <neessan_gen@.rediffmail.com> wrote in message
news:048201c39daf$d6959230$a501280a@.phx.gbl...
> Hi
> I want to take a backup of SQL Server database on weekly
> basis. How can I set up things on my server ? any script
> will have to be written or any tool to be used ? pl.
> help..
> Jay|||In addition to Hari's post:
Weekly? That would never go in my installation. You are essentially saying that it is OK to lose up
to one week worth of data. My baseline is database backup every day and log backup every hour.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jay" <neessan_gen@.rediffmail.com> wrote in message news:048201c39daf$d6959230$a501280a@.phx.gbl...
> Hi
> I want to take a backup of SQL Server database on weekly
> basis. How can I set up things on my server ? any script
> will have to be written or any tool to be used ? pl.
> help..
> Jay|||Create a Database Manitance Plan or create job which should start on weekly.
--
Shaju Thomas
"Jay" <neessan_gen@.rediffmail.com> wrote in message
news:048201c39daf$d6959230$a501280a@.phx.gbl...
> Hi
> I want to take a backup of SQL Server database on weekly
> basis. How can I set up things on my server ? any script
> will have to be written or any tool to be used ? pl.
> help..
> Jay|||THANKS HARI and SHAJU, appreciate your help.
Jay
>--Original Message--
>Hi,
>You can use database maintenance plans (Enterprise
Manager) to create a
>backup job which can be scheduled to execute every week
once.
>Thanks
>Hari
>MCDBA
>
>"Jay" <neessan_gen@.rediffmail.com> wrote in message
>news:048201c39daf$d6959230$a501280a@.phx.gbl...
>> Hi
>> I want to take a backup of SQL Server database on weekly
>> basis. How can I set up things on my server ? any script
>> will have to be written or any tool to be used ? pl.
>> help..
>> Jay
>
>.
>|||HI
We hawe 30 database on 3 server with multiple backup strategy. (1-30GB)
Main backup procedure: Backup database to device on network share. Backup the devices to tape.
Customized backup:
Purpose: Reduce nigtly backup time. One backup script for Multi server environment (MSX-TSX).
Easy configuration for new or migrated databases.
Reduce disk usage.
Easy restore. (We can futurely write one script with parameters for restore)
Monday - Saturday
1. - 4 logbackup / day and differential backup 22:00
2. - 4 logbackup / day and differential backup 20:00
3. - 3 logbackup / day and differential backup 20:00
4. - 1 logbackup / day and differential backup 20:00
5. - differential backup 20:00
6. - differential backup 22:00
7. - full backup 20:00 (include master and msdb)
Sunday full backup instead of differential backup
Implementation:
Create registry entry for Bakup folder (eg: HKEY_LOCAL_MACHINE\SOFTWARE\Vodafone\MSSQLbackup)
Create table in msdb:
/********************************************************/
CREATE TABLE Backups (
dbName varchar (255) COLLATE Hungarian_CI_AS NOT NULL , --Database names
MainBKP int NULL --Backup configuration identifier
) ON PRIMARY
GO
/********************************************************/
Populate this table with database name and backup id
Create the following sp in master database:
The Backup script:
/********************************************************/
SET QUOTED_IDENTIFIER OFF GO
SET ANSI_NULLS ON GO
--=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
-- AdmSpBackup: r=F6vid le=EDr=E1s
----
-- Param=E9terek:
--
----
-- T=E1bl=E1k:
-- Haszn=E1lt t=E1bl=E1k neve, hozz=E1f=E9r=E9s m=F3dja
-- ----
-- Hivatkoz=F3 elj=E1r=E1sok:
-- Mely elj=E1r=E1sb=F3l ker=FCl megh=EDv=E1sra ----
-- H=EDvott elj=E1r=E1sok:
-- Mely elj=E1r=E1sokat h=EDv meg
--
----
-- Visszat=E9r=E9si =E9rt=E9kek:
-- =C9rt=E9k: 0, ha sikeres, -101 ha hib=E1s a fut=E1s
-- Eredm=E9nyhalmaz:
-- ----
-- Tranzakci=F3k:
-- Nem haszn=E1l tranzakci=F3t
----
-- R=E9szletes le=EDr=E1s:
-- ----
-- Jegyzet: ----
-- K=E9sz=EDtette: Jakus Andr=E1s, 2003-10-20
----
-- T=F6rt=E9net:
-- -- --=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
ALTER PROCEDURE AdmSpBackup
AS
SET NOCOUNT ON
SET DATEFIRST 1
DECLARE @.set INT
DECLARE @.sql NVARCHAR(4000)
DECLARE @.name VARCHAR(100)
DECLARE @.main INT
DECLARE @.device VARCHAR(255)
DECLARE @.defPath VARCHAR(255)
DECLARE @.dbPath VARCHAR(255)
DECLARE @.tmpStr VARCHAR(200)
DECLARE @.message VARCHAR(8000)
DECLARE @.Err int
DECLARE @.SUBJECT VARCHAR(100)
---
---
SET @.set =3D DATEPART(HH, GETDATE())
SET @.message =3D 'Starting No: ' + CAST(@.set AS VARCHAR(2)) + ' backup on: ' + @.@.SERVERNAME + ' ' + CONVERT(VARCHAR
(100), GETDATE(), 20) + Char(13) + Char(13)
SELECT @.sql =3D CASE WHEN @.set =3D 7 THEN 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP IN (1, 2)'
WHEN @.set =3D 12 THEN 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP IN (1, 2, 3)'
WHEN @.set =3D 16 THEN 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP IN (1, 2, 3)'
WHEN @.set =3D 20 THEN 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP IN (2, 3, 4, 5, 7)'
WHEN @.set =3D 22 THEN 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP IN (1, 6)'
ELSE 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP =3D -1'
END
EXEC sp_executesql @.sql
---
----
SET @.tmpStr =3D REPLACE(@.@.SERVERNAME, '\', '-')
-- A registry-ben be=E1ll=EDtott ment=E9si k=F6nyvt=E1r
EXEC master..xp_regread @.rootkey=3D'HKEY_LOCAL_MACHINE',
@.key=3D'SOFTWARE\Vodafone\MSSQLbackup',
@.value_name =3D @.tmpStr,
@.value=3D@.defPath OUTPUT
-- Van-=E9 k=F6nyvt=E1ra az adatb=E1zisnak
SET @.tmpStr =3D 'master..xp_subdirs ''' + @.defPath + ''''
CREATE TABLE #Dirs (a VARCHAR(100))
insert INTO #Dirs (a) EXEC(@.tmpStr)
---
----
DECLARE bcks CURSOR FOR SELECT * FROM ##dbs
OPEN bcks
FETCH NEXT FROM bcks
INTO @.name, @.main
WHILE @.@.FETCH_STATUS =3D 0
BEGIN
/**---
---**/
/** Verify, that have subfolder for database **/
/**---
---**/
If @.name not in (select a from #Dirs)
BEGIN
=09
SET @.tmpStr =3D 'Mkdir "' + @.defPath + @.name + '"' --
Create subfolder for database
=09
EXECUTE xp_cmdshell @.tmpStr
=09
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: ' + @.tmpStr + Char(13) + Char
(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Successfully created a new backup folder: ' + @.defPath + @.name + Char(13) + Char
(13)
END
=09
END
/**---
---**/
/** Verify, that have Backup media for full database backup **/
/**---
---**/
If @.Name + '-Full' not in (SELECT logical_device_name from msdb.dbo.backupmediafamily)
BEGIN
SET @.device =3D @.Name + '-Full'
If @.device not in (select LTRIM(RTRIM(name)) from sysdevices where cntrltype =3D 2)
BEGIN
SET @.dbPath =3D @.defPath + @.Name + '\' + @.device + '.bak'
SELECT @.dbPath
=09
EXECUTE sp_addumpdevice @.devtype =3D 'Disk'
, @.logicalname =3D @.device
, @.physicalname =3D @.dbPath
, @.cntrltype =3D 2
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: sp_addumpdevice ' + @.device + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Successfully created a new backup device: ' + @.device + Char(13) + Char(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
END
=09
=09
=09
BEGIN
=09
SET @.message =3D @.message + CONVERT(VARCHAR
(100), GETDATE(), 20) + ': Backup Database ' + @.Name + ' started (Full Backup)' + Char(13) + Char(13)
BACKUP DATABASE @.Name
TO @.device
WITH
INIT
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: BACKUP DATABASE ' + @.Name + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': '+ @.Name + ' database successfully backed up to: ' + @.Device + Char(13) + Char
(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
END
END
ELSE
BEGIN
/**---
---
If @.main < 5 then backup log for @.Name
---
---**/
If @.Main < 5
BEGIN
SET @.device =3D @.Name + '-Log' + CAST(@.set AS VARCHAR(2))
If @.device not in (select LTRIM(RTRIM(name)) from sysdevices where cntrltype =3D 2)
BEGIN
SET @.dbPath =3D @.defPath + @.Name + '\' + @.device + '.trn'
SELECT @.dbPath
=09
EXECUTE sp_addumpdevice @.devtype =3D 'Disk'
, @.logicalname =3D @.device
, @.physicalname =3D @.dbPath
, @.cntrltype =3D 2
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: sp_addumpdevice ' + @.device + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Successfully created a new backup device: ' + @.device + Char(13) + Char(13)
END
=09
=09
END
SET @.message =3D @.message + CONVERT(VARCHAR
(100), GETDATE(), 20) + ': Backup Log ' + @.Name + ' started' + Char(13) + Char(13)
BACKUP LOG @.Name=09
TO @.device
WITH
INIT
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: BACKUP LOG ' + @.Name + Char
(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': '+ @.Name + ' log successfully backed up to: ' + @.Device + Char(13) + Char
(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
END
/**---
---
If @.set > 19 then backup database @.Name
If DATEPART(dw, GETDATE()) =3D 7 Then Full backup
---
---**/
If DATEPART(dw, GETDATE()) =3D 7 AND @.set > 19 --@.Name NOT IN ('master', 'msdb')
BEGIN =09
SET @.message =3D @.message + CONVERT(VARCHAR
(100), GETDATE(), 20) + ': Backup Database ' + @.Name + ' started (Full Backup)' + Char(13) + Char(13)
SET @.device =3D @.Name + '-Full'
=09
BACKUP DATABASE @.Name
TO @.device
WITH
RETAINDAYS =3D 6,
INIT
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: BACKUP DATABASE ' + @.Name + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': '+ @.Name + ' database successfully backed up to: ' + @.Device + Char(13) + Char
(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
=09
END
ELSE
/**---
---
If DATEPART(dw, GETDATE()) < 7 Then Differential backup, @.main < 7 ---
---**/
BEGIN
If @.main < 7 AND @.set > 19
BEGIN
SET @.device =3D @.Name + '-Diff'
If @.device not in (select LTRIM(RTRIM(name)) from sysdevices where cntrltype =3D 2)
BEGIN
SET @.dbPath =3D @.defPath + @.Name + '\' + @.device + '.bak'
SELECT @.dbPath
=09
EXECUTE sp_addumpdevice @.devtype =3D 'Disk'
, @.logicalname =3D @.device
, @.physicalname =3D @.dbPath
, @.cntrltype =3D 2
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: sp_addumpdevice ' + @.device + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Successfully created a new backup device: ' + @.device + Char(13) + Char(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
=09
END
=09
SET @.message =3D @.message + CONVERT(VARCHAR
(100), GETDATE(), 20) + ': Backup Database ' + @.Name + ' started (Differential Backup)' + Char(13) + Char(13)
BACKUP DATABASE @.Name TO @.device
WITH DIFFERENTIAL,
INIT
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS varchar(30)) + 'Command: BACKUP DATABASE ' + @.Name + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': '+ @.Name + ' database successfully backed up to: ' + @.Device + Char(13) + Char
(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
END
ELSE
BEGIN
SET @.device =3D @.Name + 'Full'
If @.set > 19 BEGIN
If @.device not in (select LTRIM
(RTRIM(name)) from sysdevices where cntrltype =3D 2)
BEGIN
SET @.dbPath =3D @.defPath + @.Name + '\' + @.device + '.bak'
SELECT @.dbPath
=09
EXECUTE sp_addumpdevice @.devtype =3D 'Disk'
, @.logicalname =3D @.device
, @.physicalname =3D @.dbPath
, @.cntrltype =3D 2
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS varchar(30)) + 'Command: sp_addumpdevice ' + @.device + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Successfully created a new backup device: ' + @.device + Char(13) + Char(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
=09
END
=09
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Backup Database ' + @.Name + ' started (Full Backup)' + Char(10) + Char(13)
BACKUP DATABASE @.Name
TO @.device
WITH
INIT
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST
(@.Err AS varchar(30)) + 'Command: BACKUP DATABASE ' + @.Name + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT(VARCHAR(100), GETDATE(), 20) + ': '+ @.Name + ' database successfully backed up to: ' + @.Device + Char(13) + Char(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
END
END
END
/**-- next database --
---**/
END
FETCH NEXT FROM bcks
INTO @.name, @.main
END
CLOSE bcks
DEALLOCATE bcks
/**---
---**/
/** Send message **/
/**---
---**/
SET @.message =3D @.message + 'Completed No: ' + CAST(@.set AS VARCHAR(2)) + ' backup on: ' + @.@.SERVERNAME + ' ' + CONVERT(VARCHAR(100), GETDATE(), 20)
SET @.SUBJECT =3D 'Backup report from ' + @.@.SERVERNAME
EXECUTE xp_sendmail @.Recipients =3D 'andras.jakus@.vodafone.com', --Your address here
@.Message =3D @.message,
@.Subject =3D @.SUBJECT
/**---
---**/
/** Drop alltemporary tables **/
/**---
---**/
DROP TABLE ##dbs
DROP TABLE #Dirs
/*******************************************************/
Change @.Recipients to your address
Create (Multi Server)job with one step: EXECUTE master.dbo.AdmSpBackup
Schedule the job: 07:00; 12:00; 16:00; 20:00; 22:00
>--Original Message--
>In addition to Hari's post:
>Weekly? That would never go in my installation. You are essentially saying that it is OK to lose up
>to one week worth of data. My baseline is database backup every day and log backup every hour.
>-- >Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=3Ddjq&as_ugroup=3Dmicrosoft.public.sqlserver
>
>"Jay" <neessan_gen@.rediffmail.com> wrote in message news:048201c39daf$d6959230$a501280a@.phx.gbl...
>> Hi
>> I want to take a backup of SQL Server database on weekly
>> basis. How can I set up things on my server ? any script
>> will have to be written or any tool to be used ? pl.
>> help..
>> Jay
>
>.
>sql