"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns9915F848C37AYazorman@127.0.0.1...
> Morgan (mor***@heaven.com) writes:
>>   I want to ask something according my MS-SQL 2000 sp4 if u can help me.
>>   I want to make a job that automatically every day restores a .bak file
>> of a database to another database.
>>  My problem is that my Maintenance Plan every days saves bak files to
>> folder C:\MP. with date label as testdb_20070406 for example.
>>  I made a job with this T-SQL command :
>>
>> RESTORE DATABASE testdb
>> FROM DISK = 'C:\MP\testdb_20070406.bak'
>> go
>>
>> but this doesnt work as i want to,because i must define the name of the
>> bak file : 'C:\MP\testdb_20070406.bak'
>
> One option is to stop using maintenance plans, and just set up a regular
> scheduled job that backups the database. This job could also perform the
> restore. In order to avoid overwriting backups directly the next night,
> you could have several jobs that you run on alternate days.
>
> If you want to stick to maintenance jobs, you could have some dynamic SQL:
>
> DECLARE @sql nvarchar(4000)
> SELECT @sql = 'RESTOREDDATABASE testdb FROM DISK = ''testdb_' +
>               convert(char(8), getdate(), 112) + '.bak'''
> EXEC (@sql)
>
> Note that this can be a little iffy, the jobs runs around midnight.
>
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

"Morgan" <cip***@aol.com> wrote in message news:eNoNiTMgHHA.4916@TK2MSFTNGP06.phx.gbl...
>
> ok,thanks Erland.
> And a last question because i search it here
> http://doc.ddart.net/mssql/sql70/ba-bz.htm
> and i didnt find something.
> How to make through T-SQL a database backup with the option "Overwrite
> Existing Media" activated??
>
> Thanks a lot.
> Morgan.
>
>
>
>
> "Erland Sommarskog" <esq***@sommarskog.se> wrote in message
> news:Xns9915F848C37AYazorman@127.0.0.1...
>> Morgan (mor***@heaven.com) writes:
>>>   I want to ask something according my MS-SQL 2000 sp4 if u can help me.
>>>   I want to make a job that automatically every day restores a .bak file
>>> of a database to another database.
>>>  My problem is that my Maintenance Plan every days saves bak files to
>>> folder C:\MP. with date label as testdb_20070406 for example.
>>>  I made a job with this T-SQL command :
>>>
>>> RESTORE DATABASE testdb
>>> FROM DISK = 'C:\MP\testdb_20070406.bak'
>>> go
>>>
>>> but this doesnt work as i want to,because i must define the name of the
>>> bak file : 'C:\MP\testdb_20070406.bak'
>>
>> One option is to stop using maintenance plans, and just set up a regular
>> scheduled job that backups the database. This job could also perform the
>> restore. In order to avoid overwriting backups directly the next night,
>> you could have several jobs that you run on alternate days.
>>
>> If you want to stick to maintenance jobs, you could have some dynamic SQL:
>>
>> DECLARE @sql nvarchar(4000)
>> SELECT @sql = 'RESTOREDDATABASE testdb FROM DISK = ''testdb_' +
>>               convert(char(8), getdate(), 112) + '.bak'''
>> EXEC (@sql)
>>
>> Note that this can be a little iffy, the jobs runs around midnight.
>>
>>
>>
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>>
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
>