Database Backup

We need to create a back up at a specific time for a client.
Now they have specified that the timings must be exact. The question I
have is this.
Say the back up takes 20 minutes to perform. Will any transactions
that take place in that 20 mins be logged in the database? Or is the
database locked for those transactions and then added to the database
when the backup has finished?
We can't unfortunately take the server off-line while it is in the
back up process.
Many Thanks in advance for your help.
MarkMark,
Go ahead. SQL Server backups are good for all committed transactions at
the time the backup completes. Note: open transactions are not included
in the backup file at the time the backup completes.
The database is not locked during the backup, so you can modify the data
in there and it will still be captured, if it is committed by the time
the backup finishes. There are some restrictions like you can't grow or
shrink the data files during a backup.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Mark wrote:
> We need to create a back up at a specific time for a client.
> Now they have specified that the timings must be exact. The question I
> have is this.
> Say the back up takes 20 minutes to perform. Will any transactions
> that take place in that 20 mins be logged in the database? Or is the
> database locked for those transactions and then added to the database
> when the backup has finished?
> We can't unfortunately take the server off-line while it is in the
> back up process.
> Many Thanks in advance for your help.
> Mark|||Hi
The database dump is consistent and point in time correct at the end of the
dump as the dump always copies in the un-truncated commited log transactions.
In effect, the dump process takes the pages off the database, writes them to
the dump, without locking, and once the DB is dumped, takes the transaction
log and adds it's contents to the same file.
So, a restore is a restore of the Db and a transaction log replay.
The dump is a copy of the database at 10:20 and not at 10:00 if the dump
takes 20 minutes.
Regards
Mike
"Mark" wrote:
> We need to create a back up at a specific time for a client.
> Now they have specified that the timings must be exact. The question I
> have is this.
> Say the back up takes 20 minutes to perform. Will any transactions
> that take place in that 20 mins be logged in the database? Or is the
> database locked for those transactions and then added to the database
> when the backup has finished?
> We can't unfortunately take the server off-line while it is in the
> back up process.
> Many Thanks in advance for your help.
> Mark
>|||In addition tot he other posts (in case it isn't obvious already):
If they have a need to have a backup from a certain point in time, say 2 pm:
Do the db backup at a some time (perhaps 2pm). After the db backup, do a log backup. You can now
restore the db backup and then the log backup in which you stop at 2 pm.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark" <mroffey@.hotmail.com> wrote in message
news:1da0fab5.0410210430.6c03d311@.posting.google.com...
> We need to create a back up at a specific time for a client.
> Now they have specified that the timings must be exact. The question I
> have is this.
> Say the back up takes 20 minutes to perform. Will any transactions
> that take place in that 20 mins be logged in the database? Or is the
> database locked for those transactions and then added to the database
> when the backup has finished?
> We can't unfortunately take the server off-line while it is in the
> back up process.
> Many Thanks in advance for your help.
> Mark|||Does this mean that for the duration of the backup, committed data is never
flushed to the data file(s)? Thus, the longer the backup, the larger the
trx log file will grow, regardless of the recovery model?
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!|||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...
> >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?
>
>|||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...
> >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?
>
>|||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...
> >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?
>
>|||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,
> 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...
>> >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?
>>|||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,
> >
> > 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...
> >> >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?
> >>
> >>
> >>
>
>|||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...
> > 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...
> >> >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?
> >>
> >>
> >>
>
>|||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...
> 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...
>> > 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...
>> >> >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?
>> >>
>> >>
>> >>
>>|||Andrew or anyone,
I'm trying to write a short sql stmts to attached to a folder and query that
folder for *.bak and if the*.bak is older than a certain amount of days, then
I want to delete them.
Is this possible using tsql?
"Andrew J. Kelly" wrote:
> 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...
> > 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...
> >> > 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...
> >> >> >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?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||You can't do this strictly with TSQL but this is as close as you will get.
There is a sample sp that will delete old log file backups based on the
timestamp in the name of the files.
-- Removing Older Backup Files --
-- Remove any log files older than 7 days
DECLARE @.Error INT, @.D DATETIME
SET @.D = DATEADD(dd,-7,GETDATE())
EXEC @.Error = remove_old_log_files @.D
SELECT @.Error
--
CREATE PROCEDURE remove_old_log_files
@.DelDate DATETIME
AS
SET NOCOUNT ON
DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
-- Used later when we cast strings to Datetimes
SET DATEFORMAT MDY
-- Create a table to hold the results of the DIR command
IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #dirlist (FName VARCHAR(1000))
-- Create a table to hold any errors
IF OBJECT_ID('tempdb..#Errors') IS NOT NULL
DROP TABLE #Errors
CREATE TABLE #Errors (Results VARCHAR(1000))
-- Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName)
exec @.Return = master..xp_cmdshell 'dir /OD C:\Data\Backups\*.Bak'
SET @.Error = @.@.ERROR
IF @.Error <> 0 OR @.Return <> 0
BEGIN
IF @.Return = 1
SET @.Error = -1
SET @.Msg = 'Error while getting the filenames with DIR '
GOTO On_Error
END
-- Remove the garbage
DELETE #dirlist WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL OR
FName LIKE '%<DIR>%'
-- Create a cursor and for each file name do the processing.
-- The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT SUBSTRING(FName,40,40) AS FName
FROM #dirlist
WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
AND SUBSTRING(FName,40,40) LIKE '%.BAK'
ORDER BY CAST(SUBSTRING(FName,1,20) AS DATETIME)
OPEN curDir
FETCH NEXT FROM curDir INTO @.Fname
WHILE (@.@.fetch_status = 0)
BEGIN
-- Delete the old backup files
SET @.Delete = 'DEL "C:\Data\Backups\' + @.FName + '"'
INSERT INTO #Errors (Results)
exec @.Return = master..xp_cmdshell @.Delete
IF @.@.RowCount > 1 OR @.Return = 1
BEGIN
SET @.Error = -1
SET @.Msg = 'Error while Deleting file ' + @.FName
GOTO On_Error
END
PRINT 'Deleted ' + @.FName + ' at ' +
CONVERT(VARCHAR(28),GETDATE(),113)
FETCH NEXT FROM curDir INTO @.Fname
END
CLOSE curDir
DEALLOCATE curDir
DROP TABLE #DirList
DROP TABLE #Errors
RETURN @.Error
On_Error:
BEGIN
IF @.Error <> 0
BEGIN
SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
RAISERROR(@.Msg,12,1)
RETURN @.Error
END
END
GO
Andrew J. Kelly SQL MVP
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:041D4B77-15AB-42F8-80B6-AB740408D913@.microsoft.com...
> Andrew or anyone,
> I'm trying to write a short sql stmts to attached to a folder and query
> that
> folder for *.bak and if the*.bak is older than a certain amount of days,
> then
> I want to delete them.
> Is this possible using tsql?
> "Andrew J. Kelly" wrote:
>> 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...
>> > 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...
>> >> > 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...
>> >> >> >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?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Thanks.. I'm going through the code and I'm running into an error when you
use the directory list command. I'm not able to view the temporary table to
see if data is being stored.
"Andrew J. Kelly" wrote:
> You can't do this strictly with TSQL but this is as close as you will get.
> There is a sample sp that will delete old log file backups based on the
> timestamp in the name of the files.
> -- Removing Older Backup Files --
> -- Remove any log files older than 7 days
> DECLARE @.Error INT, @.D DATETIME
> SET @.D = DATEADD(dd,-7,GETDATE())
> EXEC @.Error = remove_old_log_files @.D
>
> SELECT @.Error
> --
> CREATE PROCEDURE remove_old_log_files
> @.DelDate DATETIME
> AS
> SET NOCOUNT ON
> DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
> DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
> -- Used later when we cast strings to Datetimes
> SET DATEFORMAT MDY
> -- Create a table to hold the results of the DIR command
> IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
> DROP TABLE #DirList
> CREATE TABLE #dirlist (FName VARCHAR(1000))
> -- Create a table to hold any errors
> IF OBJECT_ID('tempdb..#Errors') IS NOT NULL
> DROP TABLE #Errors
> CREATE TABLE #Errors (Results VARCHAR(1000))
> -- Insert the results of the dir cmd into a table so we can scan it
> INSERT INTO #dirlist (FName)
> exec @.Return = master..xp_cmdshell 'dir /OD C:\Data\Backups\*.Bak'
> SET @.Error = @.@.ERROR
> IF @.Error <> 0 OR @.Return <> 0
> BEGIN
> IF @.Return = 1
> SET @.Error = -1
> SET @.Msg = 'Error while getting the filenames with DIR '
> GOTO On_Error
> END
> -- Remove the garbage
> DELETE #dirlist WHERE
> SUBSTRING(FName,1,2) < '00' OR
> SUBSTRING(FName,1,2) > '99' OR
> FName IS NULL OR
> FName LIKE '%<DIR>%'
>
> -- Create a cursor and for each file name do the processing.
> -- The files will be processed in date order.
> DECLARE curDir CURSOR READ_ONLY LOCAL
> FOR
> SELECT SUBSTRING(FName,40,40) AS FName
> FROM #dirlist
> WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
> AND SUBSTRING(FName,40,40) LIKE '%.BAK'
> ORDER BY CAST(SUBSTRING(FName,1,20) AS DATETIME)
> OPEN curDir
> FETCH NEXT FROM curDir INTO @.Fname
> WHILE (@.@.fetch_status = 0)
> BEGIN
>
> -- Delete the old backup files
> SET @.Delete = 'DEL "C:\Data\Backups\' + @.FName + '"'
> INSERT INTO #Errors (Results)
> exec @.Return = master..xp_cmdshell @.Delete
>
> IF @.@.RowCount > 1 OR @.Return = 1
> BEGIN
> SET @.Error = -1
> SET @.Msg = 'Error while Deleting file ' + @.FName
> GOTO On_Error
> END
> PRINT 'Deleted ' + @.FName + ' at ' +
> CONVERT(VARCHAR(28),GETDATE(),113)
> FETCH NEXT FROM curDir INTO @.Fname
> END
> CLOSE curDir
> DEALLOCATE curDir
> DROP TABLE #DirList
> DROP TABLE #Errors
> RETURN @.Error
> On_Error:
> BEGIN
> IF @.Error <> 0
> BEGIN
> SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
> RAISERROR(@.Msg,12,1)
> RETURN @.Error
> END
> END
> GO
>
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:041D4B77-15AB-42F8-80B6-AB740408D913@.microsoft.com...
> > Andrew or anyone,
> >
> > I'm trying to write a short sql stmts to attached to a folder and query
> > that
> > folder for *.bak and if the*.bak is older than a certain amount of days,
> > then
> > I want to delete them.
> >
> > Is this possible using tsql?
> >
> > "Andrew J. Kelly" wrote:
> >
> >> 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...
> >> > 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...
> >> >> > 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...
> >> >> >> >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?
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Do you mean this line: INSERT INTO #dirlist (FName)
exec @.Return = master..xp_cmdshell 'dir /OD C:\Data\Backups\*.Bak'
Can you be so kind as to list exactly what the error is that you are
getting? I don't understand why you can't see this table? Are you running
the select in the same connection as the one that created the table? If not
you won't be able to see it since it is a local temporary table. You really
need to be a little more specific on what you are doing and what the real
errors are that you are getting otherwise I am simply guessing.
--
Andrew J. Kelly SQL MVP
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:61C4CAE3-8398-4905-AE4C-411D6A55DA12@.microsoft.com...
> Thanks.. I'm going through the code and I'm running into an error when you
> use the directory list command. I'm not able to view the temporary table
> to
> see if data is being stored.
> "Andrew J. Kelly" wrote:
>> You can't do this strictly with TSQL but this is as close as you will
>> get.
>> There is a sample sp that will delete old log file backups based on the
>> timestamp in the name of the files.
>> -- Removing Older Backup Files --
>> -- Remove any log files older than 7 days
>> DECLARE @.Error INT, @.D DATETIME
>> SET @.D = DATEADD(dd,-7,GETDATE())
>> EXEC @.Error = remove_old_log_files @.D
>>
>> SELECT @.Error
>> --
>> CREATE PROCEDURE remove_old_log_files
>> @.DelDate DATETIME
>> AS
>> SET NOCOUNT ON
>> DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
>> DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
>> -- Used later when we cast strings to Datetimes
>> SET DATEFORMAT MDY
>> -- Create a table to hold the results of the DIR command
>> IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
>> DROP TABLE #DirList
>> CREATE TABLE #dirlist (FName VARCHAR(1000))
>> -- Create a table to hold any errors
>> IF OBJECT_ID('tempdb..#Errors') IS NOT NULL
>> DROP TABLE #Errors
>> CREATE TABLE #Errors (Results VARCHAR(1000))
>> -- Insert the results of the dir cmd into a table so we can scan it
>> INSERT INTO #dirlist (FName)
>> exec @.Return = master..xp_cmdshell 'dir /OD
>> C:\Data\Backups\*.Bak'
>> SET @.Error = @.@.ERROR
>> IF @.Error <> 0 OR @.Return <> 0
>> BEGIN
>> IF @.Return = 1
>> SET @.Error = -1
>> SET @.Msg = 'Error while getting the filenames with DIR '
>> GOTO On_Error
>> END
>> -- Remove the garbage
>> DELETE #dirlist WHERE
>> SUBSTRING(FName,1,2) < '00' OR
>> SUBSTRING(FName,1,2) > '99' OR
>> FName IS NULL OR
>> FName LIKE '%<DIR>%'
>>
>> -- Create a cursor and for each file name do the processing.
>> -- The files will be processed in date order.
>> DECLARE curDir CURSOR READ_ONLY LOCAL
>> FOR
>> SELECT SUBSTRING(FName,40,40) AS FName
>> FROM #dirlist
>> WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
>> AND SUBSTRING(FName,40,40) LIKE '%.BAK'
>> ORDER BY CAST(SUBSTRING(FName,1,20) AS DATETIME)
>> OPEN curDir
>> FETCH NEXT FROM curDir INTO @.Fname
>> WHILE (@.@.fetch_status = 0)
>> BEGIN
>>
>> -- Delete the old backup files
>> SET @.Delete = 'DEL "C:\Data\Backups\' + @.FName + '"'
>> INSERT INTO #Errors (Results)
>> exec @.Return = master..xp_cmdshell @.Delete
>>
>> IF @.@.RowCount > 1 OR @.Return = 1
>> BEGIN
>> SET @.Error = -1
>> SET @.Msg = 'Error while Deleting file ' + @.FName
>> GOTO On_Error
>> END
>> PRINT 'Deleted ' + @.FName + ' at ' +
>> CONVERT(VARCHAR(28),GETDATE(),113)
>> FETCH NEXT FROM curDir INTO @.Fname
>> END
>> CLOSE curDir
>> DEALLOCATE curDir
>> DROP TABLE #DirList
>> DROP TABLE #Errors
>> RETURN @.Error
>> On_Error:
>> BEGIN
>> IF @.Error <> 0
>> BEGIN
>> SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
>> RAISERROR(@.Msg,12,1)
>> RETURN @.Error
>> END
>> END
>> GO
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> news:041D4B77-15AB-42F8-80B6-AB740408D913@.microsoft.com...
>> > Andrew or anyone,
>> >
>> > I'm trying to write a short sql stmts to attached to a folder and query
>> > that
>> > folder for *.bak and if the*.bak is older than a certain amount of
>> > days,
>> > then
>> > I want to delete them.
>> >
>> > Is this possible using tsql?
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> 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...
>> >> > 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...
>> >> >> > 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...
>> >> >> >> >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?
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||When I try to query the temp table within the same connection, it says the
object is invalid. So I'm not sure the script is working because I can't view
the table.
I don't have any more specific errors. Sorry if I was vague in my previous
messages.
"Andrew J. Kelly" wrote:
> Do you mean this line: INSERT INTO #dirlist (FName)
> exec @.Return = master..xp_cmdshell 'dir /OD C:\Data\Backups\*.Bak'
> Can you be so kind as to list exactly what the error is that you are
> getting? I don't understand why you can't see this table? Are you running
> the select in the same connection as the one that created the table? If not
> you won't be able to see it since it is a local temporary table. You really
> need to be a little more specific on what you are doing and what the real
> errors are that you are getting otherwise I am simply guessing.
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:61C4CAE3-8398-4905-AE4C-411D6A55DA12@.microsoft.com...
> > Thanks.. I'm going through the code and I'm running into an error when you
> > use the directory list command. I'm not able to view the temporary table
> > to
> > see if data is being stored.
> >
> > "Andrew J. Kelly" wrote:
> >
> >> You can't do this strictly with TSQL but this is as close as you will
> >> get.
> >> There is a sample sp that will delete old log file backups based on the
> >> timestamp in the name of the files.
> >>
> >> -- Removing Older Backup Files --
> >>
> >> -- Remove any log files older than 7 days
> >> DECLARE @.Error INT, @.D DATETIME
> >> SET @.D = DATEADD(dd,-7,GETDATE())
> >>
> >> EXEC @.Error = remove_old_log_files @.D
> >>
> >>
> >> SELECT @.Error
> >>
> >> --
> >> CREATE PROCEDURE remove_old_log_files
> >> @.DelDate DATETIME
> >>
> >> AS
> >>
> >> SET NOCOUNT ON
> >>
> >> DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
> >> DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
> >>
> >> -- Used later when we cast strings to Datetimes
> >> SET DATEFORMAT MDY
> >>
> >> -- Create a table to hold the results of the DIR command
> >> IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
> >> DROP TABLE #DirList
> >>
> >> CREATE TABLE #dirlist (FName VARCHAR(1000))
> >>
> >> -- Create a table to hold any errors
> >> IF OBJECT_ID('tempdb..#Errors') IS NOT NULL
> >> DROP TABLE #Errors
> >>
> >> CREATE TABLE #Errors (Results VARCHAR(1000))
> >>
> >> -- Insert the results of the dir cmd into a table so we can scan it
> >> INSERT INTO #dirlist (FName)
> >> exec @.Return = master..xp_cmdshell 'dir /OD
> >> C:\Data\Backups\*.Bak'
> >>
> >> SET @.Error = @.@.ERROR
> >>
> >> IF @.Error <> 0 OR @.Return <> 0
> >> BEGIN
> >> IF @.Return = 1
> >> SET @.Error = -1
> >> SET @.Msg = 'Error while getting the filenames with DIR '
> >> GOTO On_Error
> >> END
> >>
> >> -- Remove the garbage
> >> DELETE #dirlist WHERE
> >> SUBSTRING(FName,1,2) < '00' OR
> >> SUBSTRING(FName,1,2) > '99' OR
> >> FName IS NULL OR
> >> FName LIKE '%<DIR>%'
> >>
> >>
> >> -- Create a cursor and for each file name do the processing.
> >> -- The files will be processed in date order.
> >> DECLARE curDir CURSOR READ_ONLY LOCAL
> >> FOR
> >> SELECT SUBSTRING(FName,40,40) AS FName
> >> FROM #dirlist
> >> WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
> >> AND SUBSTRING(FName,40,40) LIKE '%.BAK'
> >> ORDER BY CAST(SUBSTRING(FName,1,20) AS DATETIME)
> >>
> >> OPEN curDir
> >>
> >> FETCH NEXT FROM curDir INTO @.Fname
> >> WHILE (@.@.fetch_status = 0)
> >> BEGIN
> >>
> >>
> >> -- Delete the old backup files
> >> SET @.Delete = 'DEL "C:\Data\Backups\' + @.FName + '"'
> >>
> >> INSERT INTO #Errors (Results)
> >> exec @.Return = master..xp_cmdshell @.Delete
> >>
> >>
> >> IF @.@.RowCount > 1 OR @.Return = 1
> >> BEGIN
> >> SET @.Error = -1
> >> SET @.Msg = 'Error while Deleting file ' + @.FName
> >> GOTO On_Error
> >> END
> >>
> >> PRINT 'Deleted ' + @.FName + ' at ' +
> >> CONVERT(VARCHAR(28),GETDATE(),113)
> >>
> >> FETCH NEXT FROM curDir INTO @.Fname
> >> END
> >>
> >> CLOSE curDir
> >> DEALLOCATE curDir
> >>
> >> DROP TABLE #DirList
> >> DROP TABLE #Errors
> >>
> >> RETURN @.Error
> >>
> >> On_Error:
> >> BEGIN
> >> IF @.Error <> 0
> >> BEGIN
> >> SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
> >> RAISERROR(@.Msg,12,1)
> >> RETURN @.Error
> >> END
> >> END
> >> GO
> >>
> >>
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> >> news:041D4B77-15AB-42F8-80B6-AB740408D913@.microsoft.com...
> >> > Andrew or anyone,
> >> >
> >> > I'm trying to write a short sql stmts to attached to a folder and query
> >> > that
> >> > folder for *.bak and if the*.bak is older than a certain amount of
> >> > days,
> >> > then
> >> > I want to delete them.
> >> >
> >> > Is this possible using tsql?
> >> >
> >> > "Andrew J. Kelly" wrote:
> >> >
> >> >> 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...
> >> >> > 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...
> >> >> >> > 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...
> >> >> >> >> >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?
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Sonya,
The temp table is created inside of a stored procedure. If you run the sp
and it creates a temp table it only lives for the duration of the sp
execution. So by the time you query for it the table has been destroyed.
This is how local temp tables work and is the whole purpose of them. If you
wan to play around with it you need to remove the code from the sp and run
it as a standard batch in query analyzer.
--
Andrew J. Kelly SQL MVP
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:D7770A92-6EEA-4B7D-8C9C-6A9DFBC92046@.microsoft.com...
> When I try to query the temp table within the same connection, it says the
> object is invalid. So I'm not sure the script is working because I can't
> view
> the table.
> I don't have any more specific errors. Sorry if I was vague in my previous
> messages.
> "Andrew J. Kelly" wrote:
>> Do you mean this line: INSERT INTO #dirlist (FName)
>> exec @.Return = master..xp_cmdshell 'dir /OD
>> C:\Data\Backups\*.Bak'
>> Can you be so kind as to list exactly what the error is that you are
>> getting? I don't understand why you can't see this table? Are you
>> running
>> the select in the same connection as the one that created the table? If
>> not
>> you won't be able to see it since it is a local temporary table. You
>> really
>> need to be a little more specific on what you are doing and what the real
>> errors are that you are getting otherwise I am simply guessing.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> news:61C4CAE3-8398-4905-AE4C-411D6A55DA12@.microsoft.com...
>> > Thanks.. I'm going through the code and I'm running into an error when
>> > you
>> > use the directory list command. I'm not able to view the temporary
>> > table
>> > to
>> > see if data is being stored.
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> You can't do this strictly with TSQL but this is as close as you will
>> >> get.
>> >> There is a sample sp that will delete old log file backups based on
>> >> the
>> >> timestamp in the name of the files.
>> >>
>> >> -- Removing Older Backup Files --
>> >>
>> >> -- Remove any log files older than 7 days
>> >> DECLARE @.Error INT, @.D DATETIME
>> >> SET @.D = DATEADD(dd,-7,GETDATE())
>> >>
>> >> EXEC @.Error = remove_old_log_files @.D
>> >>
>> >>
>> >> SELECT @.Error
>> >>
>> >> --
>> >> CREATE PROCEDURE remove_old_log_files
>> >> @.DelDate DATETIME
>> >>
>> >> AS
>> >>
>> >> SET NOCOUNT ON
>> >>
>> >> DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
>> >> DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
>> >>
>> >> -- Used later when we cast strings to Datetimes
>> >> SET DATEFORMAT MDY
>> >>
>> >> -- Create a table to hold the results of the DIR command
>> >> IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
>> >> DROP TABLE #DirList
>> >>
>> >> CREATE TABLE #dirlist (FName VARCHAR(1000))
>> >>
>> >> -- Create a table to hold any errors
>> >> IF OBJECT_ID('tempdb..#Errors') IS NOT NULL
>> >> DROP TABLE #Errors
>> >>
>> >> CREATE TABLE #Errors (Results VARCHAR(1000))
>> >>
>> >> -- Insert the results of the dir cmd into a table so we can scan it
>> >> INSERT INTO #dirlist (FName)
>> >> exec @.Return = master..xp_cmdshell 'dir /OD
>> >> C:\Data\Backups\*.Bak'
>> >>
>> >> SET @.Error = @.@.ERROR
>> >>
>> >> IF @.Error <> 0 OR @.Return <> 0
>> >> BEGIN
>> >> IF @.Return = 1
>> >> SET @.Error = -1
>> >> SET @.Msg = 'Error while getting the filenames with DIR '
>> >> GOTO On_Error
>> >> END
>> >>
>> >> -- Remove the garbage
>> >> DELETE #dirlist WHERE
>> >> SUBSTRING(FName,1,2) < '00' OR
>> >> SUBSTRING(FName,1,2) > '99' OR
>> >> FName IS NULL OR
>> >> FName LIKE '%<DIR>%'
>> >>
>> >>
>> >> -- Create a cursor and for each file name do the processing.
>> >> -- The files will be processed in date order.
>> >> DECLARE curDir CURSOR READ_ONLY LOCAL
>> >> FOR
>> >> SELECT SUBSTRING(FName,40,40) AS FName
>> >> FROM #dirlist
>> >> WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
>> >> AND SUBSTRING(FName,40,40) LIKE '%.BAK'
>> >> ORDER BY CAST(SUBSTRING(FName,1,20) AS DATETIME)
>> >>
>> >> OPEN curDir
>> >>
>> >> FETCH NEXT FROM curDir INTO @.Fname
>> >> WHILE (@.@.fetch_status = 0)
>> >> BEGIN
>> >>
>> >>
>> >> -- Delete the old backup files
>> >> SET @.Delete = 'DEL "C:\Data\Backups\' + @.FName + '"'
>> >>
>> >> INSERT INTO #Errors (Results)
>> >> exec @.Return = master..xp_cmdshell @.Delete
>> >>
>> >>
>> >> IF @.@.RowCount > 1 OR @.Return = 1
>> >> BEGIN
>> >> SET @.Error = -1
>> >> SET @.Msg = 'Error while Deleting file ' + @.FName
>> >> GOTO On_Error
>> >> END
>> >>
>> >> PRINT 'Deleted ' + @.FName + ' at ' +
>> >> CONVERT(VARCHAR(28),GETDATE(),113)
>> >>
>> >> FETCH NEXT FROM curDir INTO @.Fname
>> >> END
>> >>
>> >> CLOSE curDir
>> >> DEALLOCATE curDir
>> >>
>> >> DROP TABLE #DirList
>> >> DROP TABLE #Errors
>> >>
>> >> RETURN @.Error
>> >>
>> >> On_Error:
>> >> BEGIN
>> >> IF @.Error <> 0
>> >> BEGIN
>> >> SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
>> >> RAISERROR(@.Msg,12,1)
>> >> RETURN @.Error
>> >> END
>> >> END
>> >> GO
>> >>
>> >>
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> >> news:041D4B77-15AB-42F8-80B6-AB740408D913@.microsoft.com...
>> >> > Andrew or anyone,
>> >> >
>> >> > I'm trying to write a short sql stmts to attached to a folder and
>> >> > query
>> >> > that
>> >> > folder for *.bak and if the*.bak is older than a certain amount of
>> >> > days,
>> >> > then
>> >> > I want to delete them.
>> >> >
>> >> > Is this possible using tsql?
>> >> >
>> >> > "Andrew J. Kelly" wrote:
>> >> >
>> >> >> 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...
>> >> >> > 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...
>> >> >> >> > 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...
>> >> >> >> >> >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?
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Ok, I was doing that as well. I'll have to remove or reference the return
function differently. Thank you for all of your help. The message that I
received is the following:
Server: Msg 178, Level 15, State 1, Line 92
A RETURN statement with a return value cannot be used in this context.
Server: Msg 178, Level 15, State 1, Line 99
A RETURN statement with a return value cannot be used in this context.
"Andrew J. Kelly" wrote:
> Sonya,
> The temp table is created inside of a stored procedure. If you run the sp
> and it creates a temp table it only lives for the duration of the sp
> execution. So by the time you query for it the table has been destroyed.
> This is how local temp tables work and is the whole purpose of them. If you
> wan to play around with it you need to remove the code from the sp and run
> it as a standard batch in query analyzer.
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:D7770A92-6EEA-4B7D-8C9C-6A9DFBC92046@.microsoft.com...
> > When I try to query the temp table within the same connection, it says the
> > object is invalid. So I'm not sure the script is working because I can't
> > view
> > the table.
> >
> > I don't have any more specific errors. Sorry if I was vague in my previous
> > messages.
> > "Andrew J. Kelly" wrote:
> >
> >> Do you mean this line: INSERT INTO #dirlist (FName)
> >> exec @.Return = master..xp_cmdshell 'dir /OD
> >> C:\Data\Backups\*.Bak'
> >>
> >> Can you be so kind as to list exactly what the error is that you are
> >> getting? I don't understand why you can't see this table? Are you
> >> running
> >> the select in the same connection as the one that created the table? If
> >> not
> >> you won't be able to see it since it is a local temporary table. You
> >> really
> >> need to be a little more specific on what you are doing and what the real
> >> errors are that you are getting otherwise I am simply guessing.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> >> news:61C4CAE3-8398-4905-AE4C-411D6A55DA12@.microsoft.com...
> >> > Thanks.. I'm going through the code and I'm running into an error when
> >> > you
> >> > use the directory list command. I'm not able to view the temporary
> >> > table
> >> > to
> >> > see if data is being stored.
> >> >
> >> > "Andrew J. Kelly" wrote:
> >> >
> >> >> You can't do this strictly with TSQL but this is as close as you will
> >> >> get.
> >> >> There is a sample sp that will delete old log file backups based on
> >> >> the
> >> >> timestamp in the name of the files.
> >> >>
> >> >> -- Removing Older Backup Files --
> >> >>
> >> >> -- Remove any log files older than 7 days
> >> >> DECLARE @.Error INT, @.D DATETIME
> >> >> SET @.D = DATEADD(dd,-7,GETDATE())
> >> >>
> >> >> EXEC @.Error = remove_old_log_files @.D
> >> >>
> >> >>
> >> >> SELECT @.Error
> >> >>
> >> >> --
> >> >> CREATE PROCEDURE remove_old_log_files
> >> >> @.DelDate DATETIME
> >> >>
> >> >> AS
> >> >>
> >> >> SET NOCOUNT ON
> >> >>
> >> >> DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
> >> >> DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
> >> >>
> >> >> -- Used later when we cast strings to Datetimes
> >> >> SET DATEFORMAT MDY
> >> >>
> >> >> -- Create a table to hold the results of the DIR command
> >> >> IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
> >> >> DROP TABLE #DirList
> >> >>
> >> >> CREATE TABLE #dirlist (FName VARCHAR(1000))
> >> >>
> >> >> -- Create a table to hold any errors
> >> >> IF OBJECT_ID('tempdb..#Errors') IS NOT NULL
> >> >> DROP TABLE #Errors
> >> >>
> >> >> CREATE TABLE #Errors (Results VARCHAR(1000))
> >> >>
> >> >> -- Insert the results of the dir cmd into a table so we can scan it
> >> >> INSERT INTO #dirlist (FName)
> >> >> exec @.Return = master..xp_cmdshell 'dir /OD
> >> >> C:\Data\Backups\*.Bak'
> >> >>
> >> >> SET @.Error = @.@.ERROR
> >> >>
> >> >> IF @.Error <> 0 OR @.Return <> 0
> >> >> BEGIN
> >> >> IF @.Return = 1
> >> >> SET @.Error = -1
> >> >> SET @.Msg = 'Error while getting the filenames with DIR '
> >> >> GOTO On_Error
> >> >> END
> >> >>
> >> >> -- Remove the garbage
> >> >> DELETE #dirlist WHERE
> >> >> SUBSTRING(FName,1,2) < '00' OR
> >> >> SUBSTRING(FName,1,2) > '99' OR
> >> >> FName IS NULL OR
> >> >> FName LIKE '%<DIR>%'
> >> >>
> >> >>
> >> >> -- Create a cursor and for each file name do the processing.
> >> >> -- The files will be processed in date order.
> >> >> DECLARE curDir CURSOR READ_ONLY LOCAL
> >> >> FOR
> >> >> SELECT SUBSTRING(FName,40,40) AS FName
> >> >> FROM #dirlist
> >> >> WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
> >> >> AND SUBSTRING(FName,40,40) LIKE '%.BAK'
> >> >> ORDER BY CAST(SUBSTRING(FName,1,20) AS DATETIME)
> >> >>
> >> >> OPEN curDir
> >> >>
> >> >> FETCH NEXT FROM curDir INTO @.Fname
> >> >> WHILE (@.@.fetch_status = 0)
> >> >> BEGIN
> >> >>
> >> >>
> >> >> -- Delete the old backup files
> >> >> SET @.Delete = 'DEL "C:\Data\Backups\' + @.FName + '"'
> >> >>
> >> >> INSERT INTO #Errors (Results)
> >> >> exec @.Return = master..xp_cmdshell @.Delete
> >> >>
> >> >>
> >> >> IF @.@.RowCount > 1 OR @.Return = 1
> >> >> BEGIN
> >> >> SET @.Error = -1
> >> >> SET @.Msg = 'Error while Deleting file ' + @.FName
> >> >> GOTO On_Error
> >> >> END
> >> >>
> >> >> PRINT 'Deleted ' + @.FName + ' at ' +
> >> >> CONVERT(VARCHAR(28),GETDATE(),113)
> >> >>
> >> >> FETCH NEXT FROM curDir INTO @.Fname
> >> >> END
> >> >>
> >> >> CLOSE curDir
> >> >> DEALLOCATE curDir
> >> >>
> >> >> DROP TABLE #DirList
> >> >> DROP TABLE #Errors
> >> >>
> >> >> RETURN @.Error
> >> >>
> >> >> On_Error:
> >> >> BEGIN
> >> >> IF @.Error <> 0
> >> >> BEGIN
> >> >> SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
> >> >> RAISERROR(@.Msg,12,1)
> >> >> RETURN @.Error
> >> >> END
> >> >> END
> >> >> GO
> >> >>
> >> >>
> >> >>
> >> >> --
> >> >> Andrew J. Kelly SQL MVP
> >> >>
> >> >>
> >> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> >> >> news:041D4B77-15AB-42F8-80B6-AB740408D913@.microsoft.com...
> >> >> > Andrew or anyone,
> >> >> >
> >> >> > I'm trying to write a short sql stmts to attached to a folder and
> >> >> > query
> >> >> > that
> >> >> > folder for *.bak and if the*.bak is older than a certain amount of
> >> >> > days,
> >> >> > then
> >> >> > I want to delete them.
> >> >> >
> >> >> > Is this possible using tsql?
> >> >> >
> >> >> > "Andrew J. Kelly" wrote:
> >> >> >
> >> >> >> 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...
> >> >> >> > 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...
> >> >> >> >> > 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...
> >> >> >> >> >> >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?
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||You can't use RETURN if you are not in a stored procedure. Just comment
that line out for your testing.
--
Andrew J. Kelly SQL MVP
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:D62D330E-685E-4048-8635-FC6943AC9F50@.microsoft.com...
> Ok, I was doing that as well. I'll have to remove or reference the return
> function differently. Thank you for all of your help. The message that I
> received is the following:
> Server: Msg 178, Level 15, State 1, Line 92
> A RETURN statement with a return value cannot be used in this context.
> Server: Msg 178, Level 15, State 1, Line 99
> A RETURN statement with a return value cannot be used in this context.
>
> "Andrew J. Kelly" wrote:
>> Sonya,
>> The temp table is created inside of a stored procedure. If you run the
>> sp
>> and it creates a temp table it only lives for the duration of the sp
>> execution. So by the time you query for it the table has been destroyed.
>> This is how local temp tables work and is the whole purpose of them. If
>> you
>> wan to play around with it you need to remove the code from the sp and
>> run
>> it as a standard batch in query analyzer.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> news:D7770A92-6EEA-4B7D-8C9C-6A9DFBC92046@.microsoft.com...
>> > When I try to query the temp table within the same connection, it says
>> > the
>> > object is invalid. So I'm not sure the script is working because I
>> > can't
>> > view
>> > the table.
>> >
>> > I don't have any more specific errors. Sorry if I was vague in my
>> > previous
>> > messages.
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Do you mean this line: INSERT INTO #dirlist (FName)
>> >> exec @.Return = master..xp_cmdshell 'dir /OD
>> >> C:\Data\Backups\*.Bak'
>> >>
>> >> Can you be so kind as to list exactly what the error is that you are
>> >> getting? I don't understand why you can't see this table? Are you
>> >> running
>> >> the select in the same connection as the one that created the table?
>> >> If
>> >> not
>> >> you won't be able to see it since it is a local temporary table. You
>> >> really
>> >> need to be a little more specific on what you are doing and what the
>> >> real
>> >> errors are that you are getting otherwise I am simply guessing.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> >> news:61C4CAE3-8398-4905-AE4C-411D6A55DA12@.microsoft.com...
>> >> > Thanks.. I'm going through the code and I'm running into an error
>> >> > when
>> >> > you
>> >> > use the directory list command. I'm not able to view the temporary
>> >> > table
>> >> > to
>> >> > see if data is being stored.
>> >> >
>> >> > "Andrew J. Kelly" wrote:
>> >> >
>> >> >> You can't do this strictly with TSQL but this is as close as you
>> >> >> will
>> >> >> get.
>> >> >> There is a sample sp that will delete old log file backups based on
>> >> >> the
>> >> >> timestamp in the name of the files.
>> >> >>
>> >> >> -- Removing Older Backup Files --
>> >> >>
>> >> >> -- Remove any log files older than 7 days
>> >> >> DECLARE @.Error INT, @.D DATETIME
>> >> >> SET @.D = DATEADD(dd,-7,GETDATE())
>> >> >>
>> >> >> EXEC @.Error = remove_old_log_files @.D
>> >> >>
>> >> >>
>> >> >> SELECT @.Error
>> >> >>
>> >> >> --
>> >> >> CREATE PROCEDURE remove_old_log_files
>> >> >> @.DelDate DATETIME
>> >> >>
>> >> >> AS
>> >> >>
>> >> >> SET NOCOUNT ON
>> >> >>
>> >> >> DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
>> >> >> DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
>> >> >>
>> >> >> -- Used later when we cast strings to Datetimes
>> >> >> SET DATEFORMAT MDY
>> >> >>
>> >> >> -- Create a table to hold the results of the DIR command
>> >> >> IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
>> >> >> DROP TABLE #DirList
>> >> >>
>> >> >> CREATE TABLE #dirlist (FName VARCHAR(1000))
>> >> >>
>> >> >> -- Create a table to hold any errors
>> >> >> IF OBJECT_ID('tempdb..#Errors') IS NOT NULL
>> >> >> DROP TABLE #Errors
>> >> >>
>> >> >> CREATE TABLE #Errors (Results VARCHAR(1000))
>> >> >>
>> >> >> -- Insert the results of the dir cmd into a table so we can scan
>> >> >> it
>> >> >> INSERT INTO #dirlist (FName)
>> >> >> exec @.Return = master..xp_cmdshell 'dir /OD
>> >> >> C:\Data\Backups\*.Bak'
>> >> >>
>> >> >> SET @.Error = @.@.ERROR
>> >> >>
>> >> >> IF @.Error <> 0 OR @.Return <> 0
>> >> >> BEGIN
>> >> >> IF @.Return = 1
>> >> >> SET @.Error = -1
>> >> >> SET @.Msg = 'Error while getting the filenames with DIR '
>> >> >> GOTO On_Error
>> >> >> END
>> >> >>
>> >> >> -- Remove the garbage
>> >> >> DELETE #dirlist WHERE
>> >> >> SUBSTRING(FName,1,2) < '00' OR
>> >> >> SUBSTRING(FName,1,2) > '99' OR
>> >> >> FName IS NULL OR
>> >> >> FName LIKE '%<DIR>%'
>> >> >>
>> >> >>
>> >> >> -- Create a cursor and for each file name do the processing.
>> >> >> -- The files will be processed in date order.
>> >> >> DECLARE curDir CURSOR READ_ONLY LOCAL
>> >> >> FOR
>> >> >> SELECT SUBSTRING(FName,40,40) AS FName
>> >> >> FROM #dirlist
>> >> >> WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) <
>> >> >> @.DelDate
>> >> >> AND SUBSTRING(FName,40,40) LIKE '%.BAK'
>> >> >> ORDER BY CAST(SUBSTRING(FName,1,20) AS DATETIME)
>> >> >>
>> >> >> OPEN curDir
>> >> >>
>> >> >> FETCH NEXT FROM curDir INTO @.Fname
>> >> >> WHILE (@.@.fetch_status = 0)
>> >> >> BEGIN
>> >> >>
>> >> >>
>> >> >> -- Delete the old backup files
>> >> >> SET @.Delete = 'DEL "C:\Data\Backups\' + @.FName + '"'
>> >> >>
>> >> >> INSERT INTO #Errors (Results)
>> >> >> exec @.Return = master..xp_cmdshell @.Delete
>> >> >>
>> >> >>
>> >> >> IF @.@.RowCount > 1 OR @.Return = 1
>> >> >> BEGIN
>> >> >> SET @.Error = -1
>> >> >> SET @.Msg = 'Error while Deleting file ' + @.FName
>> >> >> GOTO On_Error
>> >> >> END
>> >> >>
>> >> >> PRINT 'Deleted ' + @.FName + ' at ' +
>> >> >> CONVERT(VARCHAR(28),GETDATE(),113)
>> >> >>
>> >> >> FETCH NEXT FROM curDir INTO @.Fname
>> >> >> END
>> >> >>
>> >> >> CLOSE curDir
>> >> >> DEALLOCATE curDir
>> >> >>
>> >> >> DROP TABLE #DirList
>> >> >> DROP TABLE #Errors
>> >> >>
>> >> >> RETURN @.Error
>> >> >>
>> >> >> On_Error:
>> >> >> BEGIN
>> >> >> IF @.Error <> 0
>> >> >> BEGIN
>> >> >> SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
>> >> >> RAISERROR(@.Msg,12,1)
>> >> >> RETURN @.Error
>> >> >> END
>> >> >> END
>> >> >> GO
>> >> >>
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Andrew J. Kelly SQL MVP
>> >> >>
>> >> >>
>> >> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> >> >> news:041D4B77-15AB-42F8-80B6-AB740408D913@.microsoft.com...
>> >> >> > Andrew or anyone,
>> >> >> >
>> >> >> > I'm trying to write a short sql stmts to attached to a folder and
>> >> >> > query
>> >> >> > that
>> >> >> > folder for *.bak and if the*.bak is older than a certain amount
>> >> >> > of
>> >> >> > days,
>> >> >> > then
>> >> >> > I want to delete them.
>> >> >> >
>> >> >> > Is this possible using tsql?
>> >> >> >
>> >> >> > "Andrew J. Kelly" wrote:
>> >> >> >
>> >> >> >> 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...
>> >> >> >> > 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...
>> >> >> >> >> > 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...
>> >> >> >> >> >> >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?
>> >> >> >> >> >>
>> >> >> >> >> >>
>> >> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>