Re: Database Backup

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Sonya (Sonya_at_discussions.microsoft.com)
Date: 12/17/04 

Date: Fri, 17 Dec 2004 08:45:01 -0800

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?
> >>
> >>
> >>
>
>
>