Database Backup

I want to add a datetime stamp on my backup file names but what I have is not
adding to the filename.
Does anyone know how to do this?
It would help if you gave a little more background or at least showed what
you were doing now. Here is an example of adding a datetime to a backup
device name but I don't know hot it fits your situation.
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')
WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB')
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
RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
PRINT '--- '
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
Andrew J. Kelly SQL MVP
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:7500995A-A50E-4C69-BF45-FA9CDB70AEBF@.microsoft.com...
>I want to add a datetime stamp on my backup file names but what I have is
>not
> adding to the filename.
> Does anyone know how to do this?
|||I also want to do this for the transaction log files.
"Andrew J. Kelly" wrote:

> It would help if you gave a little more background or at least showed what
> you were doing now. Here is an example of adding a datetime to a backup
> device name but I don't know hot it fits your situation.
> 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')
> WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB')
>
> 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
> RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
> PRINT '--- '
> FETCH NEXT FROM cur_DBs INTO @.DBName
> END
> CLOSE cur_DBs
> DEALLOCATE cur_DBs
>
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:7500995A-A50E-4C69-BF45-FA9CDB70AEBF@.microsoft.com...
>
>
|||Sorry. Currently I am performing complete backups to a network drive. I want
to modify the backups by appending the date and time stamp to the backup
filenames. Then run either a batch file or DTS package to clean-up the folder
by deleting SQL backups after 7 days.
"Andrew J. Kelly" wrote:

> It would help if you gave a little more background or at least showed what
> you were doing now. Here is an example of adding a datetime to a backup
> device name but I don't know hot it fits your situation.
> 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')
> WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB')
>
> 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
> RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
> PRINT '--- '
> FETCH NEXT FROM cur_DBs INTO @.DBName
> END
> CLOSE cur_DBs
> DEALLOCATE cur_DBs
>
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:7500995A-A50E-4C69-BF45-FA9CDB70AEBF@.microsoft.com...
>
>
|||Andrew,
Thanks worked. How can I add the time to it as well. I am going to create a
DTS package to run this query.
"Andrew J. Kelly" wrote:

> It would help if you gave a little more background or at least showed what
> you were doing now. Here is an example of adding a datetime to a backup
> device name but I don't know hot it fits your situation.
> 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')
> WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB')
>
> 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
> RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
> PRINT '--- '
> FETCH NEXT FROM cur_DBs INTO @.DBName
> END
> CLOSE cur_DBs
> DEALLOCATE cur_DBs
>
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:7500995A-A50E-4C69-BF45-FA9CDB70AEBF@.microsoft.com...
>
>
|||Just change the CONVERT to use an different format and make the variable
large enough to handle the extra characters. You will have to look at
CONVERT in BOL to see which suites your requirement best. The only thing is
that when you add the time portion you usually have to use a format that
inserts dashs or slashes. See if this works for you:
LEFT(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20),
GETDATE(),120),'-',''),':',''),' ',''),12)
Andrew J. Kelly SQL MVP
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:3661E6F5-DECE-4A20-BFD1-70927898E1C7@.microsoft.com...[vbcol=seagreen]
> Andrew,
> Thanks worked. How can I add the time to it as well. I am going to create
> a
> DTS package to run this query.
> "Andrew J. Kelly" wrote:
|||That worked thank you.
"Andrew J. Kelly" wrote:

> Just change the CONVERT to use an different format and make the variable
> large enough to handle the extra characters. You will have to look at
> CONVERT in BOL to see which suites your requirement best. The only thing is
> that when you add the time portion you usually have to use a format that
> inserts dashs or slashes. See if this works for you:
> LEFT(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20),
> GETDATE(),120),'-',''),':',''),' ',''),12)
>
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:3661E6F5-DECE-4A20-BFD1-70927898E1C7@.microsoft.com...
>
>
|||Andrew,
Do you know how to access a network drive within Enterprise Mgr?
I'm trying to restore a db where the backups are located on the network. I
can see other network drives but cannot view the I need. I've already checked
the permissions and made sure the drive was shared. Do you have any
suggestions?
"Andrew J. Kelly" wrote:

> Just change the CONVERT to use an different format and make the variable
> large enough to handle the extra characters. You will have to look at
> CONVERT in BOL to see which suites your requirement best. The only thing is
> that when you add the time portion you usually have to use a format that
> inserts dashs or slashes. See if this works for you:
> LEFT(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20),
> GETDATE(),120),'-',''),':',''),' ',''),12)
>
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:3661E6F5-DECE-4A20-BFD1-70927898E1C7@.microsoft.com...
>
>
|||Don't use EM, use a script instead. EM has some limitations and things like
this can be much more controlled through scripts. The restore syntax is
pretty simple and there are examples in BOL. If you have troubles with it
post your script and we can help.
Andrew J. Kelly SQL MVP
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:B202FDF2-5301-4E8E-9558-F2E0505F563C@.microsoft.com...[vbcol=seagreen]
> Andrew,
> Do you know how to access a network drive within Enterprise Mgr?
> I'm trying to restore a db where the backups are located on the network. I
> can see other network drives but cannot view the I need. I've already
> checked
> the permissions and made sure the drive was shared. Do you have any
> suggestions?
> "Andrew J. Kelly" wrote: