Restore and backup script questions

SQL Server Developer >> Restore and backup script questions

Hi all,

Using SQL 2000 SP4

I have a bak file where when I back up I have RETAINDAYS = 10.

Well I'm changing that to 3 days. So how do I get rid of old sets that I
don't need in the bak file?
And how do I always restore in the scrip below the most current backup set?
File = 1 doesn't work, not the latest backupset?

ALTER DATABASE MyDatabaseNew SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE MyDatabaseNew
FROM DISK = N'E:\Database\MyDatabase\Bak\MyDatabase_old.bak'
WITH RECOVERY, REPLACE, FILE = 1, STATS = 10
ALTER DATABASE MyDatabaseNew SET MULTI_USER

thanks
gv

SQL Server149

SQL Server Developer >> Restore and backup script questions All that REATINDAYS do is to not allow you to overwrite with INIT prior top the specified number of
days. Overwrite in SQL Server (INIT) is all or nothing. You can't say, "keep the three most recent"
with less than working with several backup devices/files. Google and you will find scripts that
create backups files based on date and also removes old backup files (just as the maint plans do).

> And how do I always restore in the scrip below the most current backup set?
> File = 1 doesn't work, not the latest backupset?

Push the result from RESTORE HEADERONLY into some table, loop the table to find out the highest FILE
number and do restore of that. Of course, you also have to handle whether you have log and diff
backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Hi all,
>
> Using SQL 2000 SP4
>
> I have a bak file where when I back up I have RETAINDAYS = 10.
>
> Well I'm changing that to 3 days. So how do I get rid of old sets that I don't need in the bak
> file?
> And how do I always restore in the scrip below the most current backup set?
> File = 1 doesn't work, not the latest backupset?
>
>
> ALTER DATABASE MyDatabaseNew SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> RESTORE DATABASE MyDatabaseNew
> FROM DISK = N'E:\Database\MyDatabase\Bak\MyDatabase_old.bak'
> WITH RECOVERY, REPLACE, FILE = 1, STATS = 10
> ALTER DATABASE MyDatabaseNew SET MULTI_USER
>
>
> thanks
> gv
>

SQL Server Developer >> Restore and backup script questions
> Hi all,
>
> Using SQL 2000 SP4
>
> I have a bak file where when I back up I have RETAINDAYS = 10.
>
> Well I'm changing that to 3 days. So how do I get rid of old sets that I
> don't need in the bak file?
> And how do I always restore in the scrip below the most current backup set?
> File = 1 doesn't work, not the latest backupset?
>
> ALTER DATABASE MyDatabaseNew SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> RESTORE DATABASE MyDatabaseNew
> FROM DISK = N'E:\Database\MyDatabase\Bak\MyDatabase_old.bak'
> WITH RECOVERY, REPLACE, FILE = 1, STATS = 10
> ALTER DATABASE MyDatabaseNew SET MULTI_USER
>
> thanks
> gv

I think I understand what you're trying to do, that is store multiple
backups in a single backup file, and have the "old" ones automatically
discarded. It doesn't quite work that way. I would suggest that you
NOT store multiple backups like this. Instead, you should store each
backup as a seperate file, with some sort of date/time stamp in the
filename. Makes it easier to manage them that way. Here's a script
that will automate the backup of all databases, including the date/
time stamping, and there's also a link to a cleanup script:
http://realsqlguy.blogspot.com/2007/02/automating-database-backups.html

SQL Server Developer >> Restore and backup script questions Thanks all for your help
gv

> Hi all,
>
> Using SQL 2000 SP4
>
> I have a bak file where when I back up I have RETAINDAYS = 10.
>
> Well I'm changing that to 3 days. So how do I get rid of old sets that I
> don't need in the bak file?
> And how do I always restore in the scrip below the most current backup
> set?
> File = 1 doesn't work, not the latest backupset?
>
>
> ALTER DATABASE MyDatabaseNew SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> RESTORE DATABASE MyDatabaseNew
> FROM DISK = N'E:\Database\MyDatabase\Bak\MyDatabase_old.bak'
> WITH RECOVERY, REPLACE, FILE = 1, STATS = 10
> ALTER DATABASE MyDatabaseNew SET MULTI_USER
>
>
> thanks
> gv
>