Filename for database dump no longer with timestamp?

Hi there!

When performing a database dump to a file with SQL Server 6.5, the timestamp was automatically used as the extension for the file. After migrating to SQL Server 2000, this no longer seems to be the case :-(

Is there a way to have the timestamp included in the filename (apart from writing a Windows script to change the filename after the file has been created, of course)?

Rgds,
MauritsOriginally posted by hafkenscheid
Hi there!

When performing a database dump to a file with SQL Server 6.5, the timestamp was automatically used as the extension for the file. After migrating to SQL Server 2000, this no longer seems to be the case :-(

Is there a way to have the timestamp included in the filename (apart from writing a Windows script to change the filename after the file has been created, of course)?

Rgds,
Maurits
In SQL7 if you go in the EM unser the Server Group -> Management -> DB Maint Plans and build a mint plan. I think the auto-delete after days puts the date time stamp on the file name.

Probably hasn't changed in SQL2K|||That's it! Thank you very, very much!

Regards,
Maurits

file names in backup and restore output

I have an isql script that runs and does a full dump of the database - I
use a -i to indicate the input file and a -o for the output file -
everything works fine - but when I look at the output file - I was
expecting to see the name of the dump file that I just created ( like in
Sybase ) but it is not there - and the same is true with the load
process - in the output from the restore there is no mention of the file
name that was used as input for the restore - since these run at night it is
important to be able to look at the output and know if the right file was
used - I'm hoping there is a parameter that I need to set in order for the
file info. to be included in the output - ?
thanks,
--
JohnWhat version of SQL Server? ISQL is a really old tool which was superseded by OSQL.EXE in 7.0 and
now SQLCMD.EXE in 2005. Anyhow, check your the -e switch of ISQL.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John" <John@.discussions.microsoft.com> wrote in message
news:83513629-DBFC-4F69-82FD-5E62F0659BD4@.microsoft.com...
>I have an isql script that runs and does a full dump of the database - I
> use a -i to indicate the input file and a -o for the output file -
> everything works fine - but when I look at the output file - I was
> expecting to see the name of the dump file that I just created ( like in
> Sybase ) but it is not there - and the same is true with the load
> process - in the output from the restore there is no mention of the file
> name that was used as input for the restore - since these run at night it is
> important to be able to look at the output and know if the right file was
> used - I'm hoping there is a parameter that I need to set in order for the
> file info. to be included in the output - ?
> thanks,
> --
> John|||This may help ? it a bit rough but you can tydy it up
-----
CREATE procedure [sp_dba_backupinfo]
@.DBName varchar(100) = '%'
AS
IF @.DBName <> '%'
set @.DBName = @.DBName+'%'
select
substring(database_name,1,24) as DATABASE_NAME,
right(backup_start_date,19) as BACKUP_START,
right(backup_finish_date,19) as BACKUP_FINISH,
--round(backup_size/1024/1024,0) as MB,
str(backup_size/1024/1024,6,0) as MB,
position,
substring(logical_device_name,1,24) as LOGICAL_DEVICE,
substring(physical_device_name,1,100) as PHYSICAL_DEVICE
from msdb..backupmediafamily a, msdb..backupset b
where a.media_set_id = b.media_set_id
--and database_name='lamda1p_interfaces_store'
and database_name like @.DBName
--database_name in ('lamdace','metadatace','security')
--and backup_start_date > '2003-09-01 01:00:00.000'
--and logical_device_name = 'lamda1p_interfaces_store'
--and physical_device_name not like '\\.%'
--and physical_device_name like 'Ba%'
--order by database_name,backup_start_date desc
order by database_name asc,backup_start_date desc
-----
CREATE procedure [sp_dba_restoreinfo]
@.DBName varchar(100) = '%'
AS
IF @.DBName <> '%'
set @.DBName = @.DBName+'%'
--select @.DBName
SET NOCOUNT ON
/*
select --xtype,
'msdb.dbo.'+name from msdb.dbo.sysobjects
where
name like '%backup%' and xtype in ('U','P')
or name like '%restore%' and xtype in ('U','P')
order by 1 desc
*/
SELECT
RH.[restore_type],
CONVERT(char(20),RH.[restore_date],120) [restore_date],
CONVERT(char(20),BS.[database_creation_date],120) [database_creation_date],
CONVERT(char(20),BS.[backup_start_date],120) [backup_start_date],
CONVERT(char(20),BS.[backup_finish_date],120) [backup_finish_date],
right(BS.[machine_name],12) [machine_name],
--right(BS.[server_name],12) [server_name],
right(BS.[database_name],35) [database_name],
right(RH.[destination_database_name],35) [destination_database_name],
str(BS.backup_size/1024/1024,6,0) as MB,
--RF.[restore_history_id],
--RF.[file_number],
--RF.[destination_phys_drive],
--RH.[restore_history_id],
--RH.[user_name],
--RH.[backup_set_id],
--RH.[replace],
--RH.[recovery],
--RH.[restart],
--RH.[stop_at],
--RH.[device_count],
--RFG.[restore_history_id],
--RFG.[filegroup_name],
--BS.[backup_set_id],
--BS.[backup_set_uuid],
--BS.[media_set_id],
--BS.[first_family_number],
--BS.[first_media_number],
--BS.[last_family_number],
--BS.[last_media_number],
--BS.[catalog_family_number],
--BS.[catalog_media_number],
--BS.[position],
--BS.[expiration_date],
--BS.[software_vendor_id],
--BS.[name],
--BS.[description],
--BS.[user_name],
--BS.[software_major_version],
--BS.[software_minor_version],
--BS.[software_build_version],
--BS.[time_zone],
--BS.[mtf_minor_version],
--BS.[first_lsn],
--BS.[last_lsn],
--BS.[checkpoint_lsn],
--BS.[database_backup_lsn],
--BS.[type],
--BS.[sort_order],
--BS.[code_page],
--BS.[compatibility_level],
--BS.[database_version],
right(RF.[destination_phys_name],45) [destination_phys_name],
+'End'
from
msdb.dbo.restorefile RF,
msdb.dbo.restorehistory RH,
msdb.dbo.restorefilegroup RFG,
msdb.dbo.backupset BS
--msdb.dbo.backupmediaset
--msdb.dbo.backupmediafamily
--msdb.dbo.backupfile
where RF.restore_history_id = RH.restore_history_id
and RH.restore_history_id = RFG.restore_history_id
and RH.backup_set_id = BS.backup_set_id
--and RH.restore_date > getdate() -31
and RH.destination_database_name like @.dbname
order by restore_date desc
-----
"Tibor Karaszi" wrote:
> What version of SQL Server? ISQL is a really old tool which was superseded by OSQL.EXE in 7.0 and
> now SQLCMD.EXE in 2005. Anyhow, check your the -e switch of ISQL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:83513629-DBFC-4F69-82FD-5E62F0659BD4@.microsoft.com...
> >I have an isql script that runs and does a full dump of the database - I
> > use a -i to indicate the input file and a -o for the output file -
> > everything works fine - but when I look at the output file - I was
> > expecting to see the name of the dump file that I just created ( like in
> > Sybase ) but it is not there - and the same is true with the load
> > process - in the output from the restore there is no mention of the file
> > name that was used as input for the restore - since these run at night it is
> > important to be able to look at the output and know if the right file was
> > used - I'm hoping there is a parameter that I need to set in order for the
> > file info. to be included in the output - ?
> >
> > thanks,
> >
> > --
> > John
>

file names in backup and restore output

I have an isql script that runs and does a full dump of the database - I
use a -i to indicate the input file and a -o for the output file -
everything works fine - but when I look at the output file - I was
expecting to see the name of the dump file that I just created ( like in
Sybase ) but it is not there - and the same is true with the load
process - in the output from the restore there is no mention of the file
name that was used as input for the restore - since these run at night it i
s
important to be able to look at the output and know if the right file was
used - I'm hoping there is a parameter that I need to set in order for the
file info. to be included in the output - ?
thanks,
JohnWhat version of SQL Server? ISQL is a really old tool which was superseded b
y OSQL.EXE in 7.0 and
now SQLCMD.EXE in 2005. Anyhow, check your the -e switch of ISQL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John" <John@.discussions.microsoft.com> wrote in message
news:83513629-DBFC-4F69-82FD-5E62F0659BD4@.microsoft.com...
>I have an isql script that runs and does a full dump of the database - I
> use a -i to indicate the input file and a -o for the output file -
> everything works fine - but when I look at the output file - I was
> expecting to see the name of the dump file that I just created ( like in
> Sybase ) but it is not there - and the same is true with the load
> process - in the output from the restore there is no mention of the file
> name that was used as input for the restore - since these run at night it
is
> important to be able to look at the output and know if the right file was
> used - I'm hoping there is a parameter that I need to set in order for t
he
> file info. to be included in the output - ?
> thanks,
> --
> John|||This may help ? it a bit rough but you can tydy it up
----
--
CREATE procedure [sp_dba_backupinfo]
@.DBName varchar(100) = '%'
AS
IF @.DBName <> '%'
set @.DBName = @.DBName+'%'
select
substring(database_name,1,24) as DATABASE_NAME,
right(backup_start_date,19) as BACKUP_START,
right(backup_finish_date,19) as BACKUP_FINISH,
--round(backup_size/1024/1024,0) as MB,
str(backup_size/1024/1024,6,0) as MB,
position,
substring(logical_device_name,1,24) as LOGICAL_DEVICE,
substring(physical_device_name,1,100) as PHYSICAL_DEVICE
from msdb..backupmediafamily a, msdb..backupset b
where a.media_set_id = b.media_set_id
--and database_name='lamda1p_interfaces_store'
and database_name like @.DBName
--database_name in ('lamdace','metadatace','security')
--and backup_start_date > '2003-09-01 01:00:00.000'
--and logical_device_name = 'lamda1p_interfaces_store'
--and physical_device_name not like '\\.%'
--and physical_device_name like 'Ba%'
--order by database_name,backup_start_date desc
order by database_name asc,backup_start_date desc
----
--
CREATE procedure [sp_dba_restoreinfo]
@.DBName varchar(100) = '%'
AS
IF @.DBName <> '%'
set @.DBName = @.DBName+'%'
--select @.DBName
SET NOCOUNT ON
/*
select --xtype,
'msdb.dbo.'+name from msdb.dbo.sysobjects
where
name like '%backup%' and xtype in ('U','P')
or name like '%restore%' and xtype in ('U','P')
order by 1 desc
*/
SELECT
RH.[restore_type],
CONVERT(char(20),RH.[restore_date],120) [restore_date],
CONVERT(char(20),BS.[database_creation_date],120) [database_creation
_date],
CONVERT(char(20),BS.[backup_start_date],120) [backup_start_date],
CONVERT(char(20),BS.[backup_finish_date],120) [backup_finish_date],
right(BS.[machine_name],12) [machine_name],
--right(BS.[server_name],12) [server_name],
right(BS.[database_name],35) [database_name],
right(RH.[destination_database_name],35) [destination_database_name]
,
str(BS.backup_size/1024/1024,6,0) as MB,
--RF.[restore_history_id],
--RF.[file_number],
--RF.[destination_phys_drive],
--RH.[restore_history_id],
--RH.[user_name],
--RH.[backup_set_id],
--RH.[replace],
--RH.[recovery],
--RH.[restart],
--RH.[stop_at],
--RH.[device_count],
--RFG.[restore_history_id],
--RFG.[filegroup_name],
--BS.[backup_set_id],
--BS.[backup_set_uuid],
--BS.[media_set_id],
--BS.[first_family_number],
--BS.[first_media_number],
--BS.[last_family_number],
--BS.[last_media_number],
--BS.[catalog_family_number],
--BS.[catalog_media_number],
--BS.[position],
--BS.[expiration_date],
--BS.[software_vendor_id],
--BS.[name],
--BS.[description],
--BS.[user_name],
--BS.[software_major_version],
--BS.[software_minor_version],
--BS.[software_build_version],
--BS.[time_zone],
--BS.[mtf_minor_version],
--BS.[first_lsn],
--BS.[last_lsn],
--BS.[checkpoint_lsn],
--BS.[database_backup_lsn],
--BS.[type],
--BS.[sort_order],
--BS.[code_page],
--BS.[compatibility_level],
--BS.[database_version],
right(RF.[destination_phys_name],45) [destination_phys_name],
+'End'
from
msdb.dbo.restorefile RF,
msdb.dbo.restorehistory RH,
msdb.dbo.restorefilegroup RFG,
msdb.dbo.backupset BS
--msdb.dbo.backupmediaset
--msdb.dbo.backupmediafamily
--msdb.dbo.backupfile
where RF.restore_history_id = RH.restore_history_id
and RH.restore_history_id = RFG.restore_history_id
and RH.backup_set_id = BS.backup_set_id
--and RH.restore_date > getdate() -31
and RH.destination_database_name like @.dbname
order by restore_date desc
----
--
"Tibor Karaszi" wrote:

> What version of SQL Server? ISQL is a really old tool which was superseded
by OSQL.EXE in 7.0 and
> now SQLCMD.EXE in 2005. Anyhow, check your the -e switch of ISQL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:83513629-DBFC-4F69-82FD-5E62F0659BD4@.microsoft.com...
>