Backup strategi

Hi all
I'm in the starting phase of creating a backup strategi for a new SQL Server box. It's a quite big installation with many databases and quite large databases (hundreds of Gbyte). I'm very famililiar with the SQL Server backup technology but I need some examples of strategies to "open up my mind". Does anybody have liks to examples for SQL Server backup strategies that will be very helpful. Maybe I don't have to reinvent the wheel..
Thanks!
FredrikHi,
Perform a full backup on the weekend and differential backups during the
week days , Hourly transaction log backups.
Have a look into the below link which contains the backup / restore
concepts with example:-
http://www.databasejournal.com/features/mssql/article.php/2232371
Thanks
Hari
MCDBA
"Fredrik" <anonymous@.discussions.microsoft.com> wrote in message
news:9322E3DE-20AB-422C-BAB5-0E66259B24FF@.microsoft.com...
> Hi all!
> I'm in the starting phase of creating a backup strategi for a new SQL
Server box. It's a quite big installation with many databases and quite
large databases (hundreds of Gbyte). I'm very famililiar with the SQL Server
backup technology but I need some examples of strategies to "open up my
mind". Does anybody have liks to examples for SQL Server backup strategies
that will be very helpful. Maybe I don't have to reinvent the wheel..
> Thanks!!
> Fredrik|||Fredrik..
We backup all databases using SQL LiteSpeed, which I highly recommend. Each backup job has three steps. After the DB is backed up locally to the SQL server in step 1, the second step is a CmdExec, which deletes the previous backup file located on a file server...
DEL /Q \\fileserver\SQLServers\SQLServerName\Database\
The third step copies the backup file from the SQL server to the file server..
COPY \\SQLServerName\E$\MSSQL\Backup\Database \\fileserver\SQLServers\SQLServerName\Database
We also do this for the hourly or 15 minute tran. log backups (depending on how much activity the DB has) that we do.
Tape backups are done locally on the file server every night.
Not overly complex, but I think it's a step in the right direction for covering ourselves in the case of a disaster
Let me know if you have any questions or suggestions. I'm always open to reviewing our current procedures
sean

Backup strategi

Hi all!
I'm in the starting phase of creating a backup strategi for a new SQL Server box. It's a quite big installation with many databases and quite large databases (hundreds of Gbyte). I'm very famililiar with the SQL Server backup technology but I need some ex
amples of strategies to "open up my mind". Does anybody have liks to examples for SQL Server backup strategies that will be very helpful. Maybe I don't have to reinvent the wheel..
Thanks!!
Fredrik
Hi,
Perform a full backup on the weekend and differential backups during the
week days , Hourly transaction log backups.
Have a look into the below link which contains the backup / restore
concepts with example:-
http://www.databasejournal.com/featu...le.php/2232371
Thanks
Hari
MCDBA
"Fredrik" <anonymous@.discussions.microsoft.com> wrote in message
news:9322E3DE-20AB-422C-BAB5-0E66259B24FF@.microsoft.com...
> Hi all!
> I'm in the starting phase of creating a backup strategi for a new SQL
Server box. It's a quite big installation with many databases and quite
large databases (hundreds of Gbyte). I'm very famililiar with the SQL Server
backup technology but I need some examples of strategies to "open up my
mind". Does anybody have liks to examples for SQL Server backup strategies
that will be very helpful. Maybe I don't have to reinvent the wheel..
> Thanks!!
> Fredrik
|||Thanks.
Acctually I was looking for solutions that are more complex . Not that my intention is to make a very complex solution but I really like to know what my options are.
I found one link that I thougt was interesting.
http://www.microsoft.com/technet/pro.../spltmirr.mspx
/Fredrik
|||Fredrik...
We backup all databases using SQL LiteSpeed, which I highly recommend. Each backup job has three steps. After the DB is backed up locally to the SQL server in step 1, the second step is a CmdExec, which deletes the previous backup file located on a file
server...
DEL /Q \\fileserver\SQLServers\SQLServerName\Database\*
The third step copies the backup file from the SQL server to the file server...
COPY \\SQLServerName\E$\MSSQL\Backup\Database \\fileserver\SQLServers\SQLServerName\Database
We also do this for the hourly or 15 minute tran. log backups (depending on how much activity the DB has) that we do.
Tape backups are done locally on the file server every night.
Not overly complex, but I think it's a step in the right direction for covering ourselves in the case of a disaster.
Let me know if you have any questions or suggestions. I'm always open to reviewing our current procedures.
sean
sql

Backup strategi

Hi all!
I'm in the starting phase of creating a backup strategi for a new SQL Server
box. It's a quite big installation with many databases and quite large data
bases (hundreds of Gbyte). I'm very famililiar with the SQL Server backup te
chnology but I need some ex
amples of strategies to "open up my mind". Does anybody have liks to example
s for SQL Server backup strategies that will be very helpful. Maybe I don't
have to reinvent the wheel..
Thanks!!
FredrikHi,
Perform a full backup on the weekend and differential backups during the
week days , Hourly transaction log backups.
Have a look into the below link which contains the backup / restore
concepts with example:-
http://www.databasejournal.com/feat...cle.php/2232371
Thanks
Hari
MCDBA
"Fredrik" <anonymous@.discussions.microsoft.com> wrote in message
news:9322E3DE-20AB-422C-BAB5-0E66259B24FF@.microsoft.com...
> Hi all!
> I'm in the starting phase of creating a backup strategi for a new SQL
Server box. It's a quite big installation with many databases and quite
large databases (hundreds of Gbyte). I'm very famililiar with the SQL Server
backup technology but I need some examples of strategies to "open up my
mind". Does anybody have liks to examples for SQL Server backup strategies
that will be very helpful. Maybe I don't have to reinvent the wheel..
> Thanks!!
> Fredrik|||Thanks.
Acctually I was looking for solutions that are more complex . Not that my in
tention is to make a very complex solution but I really like to know what my
options are.
I found one link that I thougt was interesting.
http://www.microsoft.com/technet/pr...n/spltmirr.mspx
/Fredrik|||Fredrik...
We backup all databases using SQL LiteSpeed, which I highly recommend. Each
backup job has three steps. After the DB is backed up locally to the SQL s
erver in step 1, the second step is a CmdExec, which deletes the previous ba
ckup file located on a file
server...
DEL /Q \\fileserver\SQLServers\SQLServerName\Da
tabase\*
The third step copies the backup file from the SQL server to the file server
..
COPY \\SQLServerName\E$\MSSQL\Backup\Database
\\fileserver\SQLServers\SQLSer
verName\Database
We also do this for the hourly or 15 minute tran. log backups (depending on
how much activity the DB has) that we do.
Tape backups are done locally on the file server every night.
Not overly complex, but I think it's a step in the right direction for cover
ing ourselves in the case of a disaster.
Let me know if you have any questions or suggestions. I'm always open to re
viewing our current procedures.
sean

Backup Maintenance Plan -> MSDE

Hi
I have a problem creating a backup maintenance plan on MDSE 2000 using the
wizard. I want the backup to be written to disk and not into the default
backup directory. First I set the schedule to Sundays at 02:00 and in the
next prompt I set it to write the backup to D:\SQLBUP. So far everything
looks ok but when I try to set that old files should be removed, I can only
set the number but not of what (hours, days, weeks etc). That dropdown menu
is completely blank. So I skip it and set the file extension to BAK. I finish
the rest of the wizard with the default values.
When the maintenance plan is finished I go back and look at its properties.
Strangely enough the path I set for the backup is now blank and so is the
schedule!
Even if I try to set it again in the properties and then click ok and open
it again it is once more lost!
Can anybody tell me why?
Is it because it is MSDE or...?
Regards
Wayne
I have the same Problem. Do you have an answer?
Thanks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||I don't know why the gui is doing that (do you have the latest service packs
installed?) but it should be easy enough to fix. Since the removal of old
backups is not stored in metadata tables you can edit this pretty easy. It
is simply an option to the xp_sqlmaint call.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
9EEB1492-1A6C-4F16-B752-D86A325127CA -VrfyBackup -BkUpMedia DISK -BkUpDB
"C:\Data\Backups" -DelBkUps 4WEEKS -BkExt "BAK"'
The above is what the job step looks like for a Full backup plan that I just
created with a delete of 4 weeks. check out BooksOnLine under "sqlmaint
utility" for details on what each of the options are. So just edit the job
to be the way you want it with regards to the delete part.
Andrew J. Kelly SQL MVP
"Gaston Oxman" <oxmangas@.hotmail.com> wrote in message
news:%23SektOLEFHA.1564@.TK2MSFTNGP09.phx.gbl...
> I have the same Problem. Do you have an answer?
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Backup maintenance plan - MSDE 2000

Hi
I have a problem creating a backup maintenance plan on MDSE 2000 using the
wizard. I want the backup to be written to disk and not into the default
backup directory. First I set the schedule to Sundays at 02:00 and in the
next prompt I set it to write the backup to D:\SQLBUP. So far everything
looks ok but when I try to set that old files should be removed, I can only
set the number but not of what (hours, days, weeks etc). That dropdown menu
is completely blank. So I skip it and set the file extension to BAK. I finish
the rest of the wizard with the default values.
When the maintenance plan is finished I go back and look at its properties.
Strangely enough the path I set for the backup is now blank and so is the
schedule!
Even if I try to set it again in the properties and then click ok and open
it again it is once more lost!
Can anybody tell me why?
Is it because it is MSDE or...?
Regards
Wayne
hi Wayne,
Wayne Gore wrote:
> Hi
> I have a problem creating a backup maintenance plan on MDSE 2000
> using the wizard. I want the backup to be written to disk and not
> into the default backup directory. First I set the schedule to
> Sundays at 02:00 and in the next prompt I set it to write the backup
> to D:\SQLBUP. So far everything looks ok but when I try to set that
> old files should be removed, I can only set the number but not of
> what (hours, days, weeks etc). That dropdown menu is completely
> blank. So I skip it and set the file extension to BAK. I finish the
> rest of the wizard with the default values.
> When the maintenance plan is finished I go back and look at its
> properties. Strangely enough the path I set for the backup is now
> blank and so is the schedule!
> Even if I try to set it again in the properties and then click ok and
> open it again it is once more lost!
> Can anybody tell me why?
> Is it because it is MSDE or...?
> Regards
> Wayne
no, it's becouse of the maintenance plan wizard, that mess all thins a
little..
my advice, keep away from maintenance plan ad write your own Ttransact-SQL
job to perform all required steps..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||I am having the exact problem; Blank menus..
Initially it was the NT\admin auth error so I
edited the job itself and added the -S -U -P and got passed that error.
Then I notice the -UseDefDir, and when you look at the Grid tab you see the
error
"Could not find the directory for database xxxxx . backups may not succeed."
I tried using the -BkUpDB and the path, then the Grid changed to the
sqlmaint /?
giving me all of the options and no other errors.
Is there any thing out there ??
I'm using msde 2000 sp3
"Andrea Montanari" wrote:

> hi Wayne,
> Wayne Gore wrote:
> no, it's becouse of the maintenance plan wizard, that mess all thins a
> little..
> my advice, keep away from maintenance plan ad write your own Ttransact-SQL
> job to perform all required steps..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>

Backup Maintenance Plan - Best Practices

Hi All,

I'm about to embark on creating a maintenance plan to back up all databases on one of our SQL 2005 servers. I am looking for some advice on best practices for doing this.

I have it in my mind that i want to be taking a full database backup once a week, with differential backups on a daily basis and transactional backups performed every 2 to 4 hours.

Do i need to create three maintenance plans for this, i.e. 1 for full, 1 for differential, and 1 for transactional?

If i want to only keep the backups from the last week, is this done by setting up a maintenance cleanup task in the full backup plan to clear all bak files that are a week old?
If so i'll also probably require one to remove the trn files also.

When using the backup command from the context menu in SSMS there is an option to name the backup set. How does this work when using maintenance plans as i haven't been able to find this option whilst trying out some of the features?

I'm sure to have more questions on this subject, but any help on the above queries would be most appreciated.

TIA,

GrantNo. As long as you are running SP2, you can do all of this using subtasks. So, you can create a single maintenance plan and then add subtasks for the full, differential, and tran log. Each of the subtasks can have their own schedule.