Re: Database Backup

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 12/17/04 

Date: Fri, 17 Dec 2004 12:20:41 -0500

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

Relevant Pages