sql backup files: schedule

Showing posts with label schedule. Show all posts

Thursday, March 22, 2012

Backup Scheduling

After I set a backup schedule, although it works as set, when I come back to
try to modify it the schedule is set back to some default. Is this normal?
It happens both on my SP4 installation and at my clients site. Thanks for
any enlightenment.Can you provide more details. Where do you do this? EM? What menu choices and dialogs? What exactly
does "set back to some default" mean?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"sborsher" <sborsher@.discussions.microsoft.com> wrote in message
news:97BF1104-BA0C-4DCD-86B3-94B1B17E50D8@.microsoft.com...
> After I set a backup schedule, although it works as set, when I come back to
> try to modify it the schedule is set back to some default. Is this normal?
> It happens both on my SP4 installation and at my clients site. Thanks for
> any enlightenment.|||Thanks for the reply. The backup is in SQL Server 2000 (running on Windows
2000 server). Right click a database and select All Tasks | Backup database
...
When I change the default backup settings they are set back to default the
next time I come back to Backup database although the setting I made are
working correctly. WHere are the Bacjup parameters saved?
"Tibor Karaszi" wrote:
> Can you provide more details. Where do you do this? EM? What menu choices and dialogs? What exactly
> does "set back to some default" mean?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "sborsher" <sborsher@.discussions.microsoft.com> wrote in message
> news:97BF1104-BA0C-4DCD-86B3-94B1B17E50D8@.microsoft.com...
> > After I set a backup schedule, although it works as set, when I come back to
> > try to modify it the schedule is set back to some default. Is this normal?
> > It happens both on my SP4 installation and at my clients site. Thanks for
> > any enlightenment.
>|||The options you set in this dialogs are not saved. If you select "Schedule", and Agent job is
created with the BACKUP command as you selected. You can edit that job.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"sborsher" <sborsher@.discussions.microsoft.com> wrote in message
news:1ED36100-F04F-4EDC-8EAB-F0352D957C26@.microsoft.com...
> Thanks for the reply. The backup is in SQL Server 2000 (running on Windows
> 2000 server). Right click a database and select All Tasks | Backup database
> ...
> When I change the default backup settings they are set back to default the
> next time I come back to Backup database although the setting I made are
> working correctly. WHere are the Bacjup parameters saved?
>
> "Tibor Karaszi" wrote:
>> Can you provide more details. Where do you do this? EM? What menu choices and dialogs? What
>> exactly
>> does "set back to some default" mean?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "sborsher" <sborsher@.discussions.microsoft.com> wrote in message
>> news:97BF1104-BA0C-4DCD-86B3-94B1B17E50D8@.microsoft.com...
>> > After I set a backup schedule, although it works as set, when I come back to
>> > try to modify it the schedule is set back to some default. Is this normal?
>> > It happens both on my SP4 installation and at my clients site. Thanks for
>> > any enlightenment.
>>|||Tibor,
Thanks. I found it (them).
"Tibor Karaszi" wrote:
> The options you set in this dialogs are not saved. If you select "Schedule", and Agent job is
> created with the BACKUP command as you selected. You can edit that job.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "sborsher" <sborsher@.discussions.microsoft.com> wrote in message
> news:1ED36100-F04F-4EDC-8EAB-F0352D957C26@.microsoft.com...
> >
> > Thanks for the reply. The backup is in SQL Server 2000 (running on Windows
> > 2000 server). Right click a database and select All Tasks | Backup database
> > ...
> >
> > When I change the default backup settings they are set back to default the
> > next time I come back to Backup database although the setting I made are
> > working correctly. WHere are the Bacjup parameters saved?
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Can you provide more details. Where do you do this? EM? What menu choices and dialogs? What
> >> exactly
> >> does "set back to some default" mean?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "sborsher" <sborsher@.discussions.microsoft.com> wrote in message
> >> news:97BF1104-BA0C-4DCD-86B3-94B1B17E50D8@.microsoft.com...
> >> > After I set a backup schedule, although it works as set, when I come back to
> >> > try to modify it the schedule is set back to some default. Is this normal?
> >> > It happens both on my SP4 installation and at my clients site. Thanks for
> >> > any enlightenment.
> >>
> >>
>

Backup Scheduling

Backup Schedule Troubles

Some how, I've managed to create a backup (disk backup) with three different
schedules. At 6pm each day, the database backups three times (three
different names).
What I'd like to have is... each Monday the previous Monday's backup would
be overwritten, each Tuesday the previous Tuesday's backup would be
overwritten, etc...
Now, I can't figure out how to remove the schedules, as it appears that I
only see one schedule and destination when I go in to the backup.
How do I get to a "clean slate" and accomplish my ideal backup scenario.
Thanks.Check under management, SQL Server Agent, Jobs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"09evaca" <09evaca@.discussions.microsoft.com> wrote in message
news:D8D3EAF7-90A5-495C-B9D5-37E933FDF24A@.microsoft.com...
> Some how, I've managed to create a backup (disk backup) with three different
> schedules. At 6pm each day, the database backups three times (three
> different names).
> What I'd like to have is... each Monday the previous Monday's backup would
> be overwritten, each Tuesday the previous Tuesday's backup would be
> overwritten, etc...
> Now, I can't figure out how to remove the schedules, as it appears that I
> only see one schedule and destination when I go in to the backup.
> How do I get to a "clean slate" and accomplish my ideal backup scenario.
> Thanks.

Backup Schedule Troubles

Some how, I've managed to create a backup (disk backup) with three different
schedules. At 6pm each day, the database backups three times (three
different names).
What I'd like to have is... each Monday the previous Monday's backup would
be overwritten, each Tuesday the previous Tuesday's backup would be
overwritten, etc...
Now, I can't figure out how to remove the schedules, as it appears that I
only see one schedule and destination when I go in to the backup.
How do I get to a "clean slate" and accomplish my ideal backup scenario.
Thanks.
Check under management, SQL Server Agent, Jobs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"09evaca" <09evaca@.discussions.microsoft.com> wrote in message
news:D8D3EAF7-90A5-495C-B9D5-37E933FDF24A@.microsoft.com...
> Some how, I've managed to create a backup (disk backup) with three different
> schedules. At 6pm each day, the database backups three times (three
> different names).
> What I'd like to have is... each Monday the previous Monday's backup would
> be overwritten, each Tuesday the previous Tuesday's backup would be
> overwritten, etc...
> Now, I can't figure out how to remove the schedules, as it appears that I
> only see one schedule and destination when I go in to the backup.
> How do I get to a "clean slate" and accomplish my ideal backup scenario.
> Thanks.
sql

Backup Schedule Troubles

Some how, I've managed to create a backup (disk backup) with three different
schedules. At 6pm each day, the database backups three times (three
different names).
What I'd like to have is... each Monday the previous Monday's backup would
be overwritten, each Tuesday the previous Tuesday's backup would be
overwritten, etc...
Now, I can't figure out how to remove the schedules, as it appears that I
only see one schedule and destination when I go in to the backup.
How do I get to a "clean slate" and accomplish my ideal backup scenario.
Thanks.Check under management, SQL Server Agent, Jobs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"09evaca" <09evaca@.discussions.microsoft.com> wrote in message
news:D8D3EAF7-90A5-495C-B9D5-37E933FDF24A@.microsoft.com...
> Some how, I've managed to create a backup (disk backup) with three differe
nt
> schedules. At 6pm each day, the database backups three times (three
> different names).
> What I'd like to have is... each Monday the previous Monday's backup would
> be overwritten, each Tuesday the previous Tuesday's backup would be
> overwritten, etc...
> Now, I can't figure out how to remove the schedules, as it appears that I
> only see one schedule and destination when I go in to the backup.
> How do I get to a "clean slate" and accomplish my ideal backup scenario.
> Thanks.

Backup schedule (Differential backups)

Hello all, hope someone will be able to help.
I have a number of medium-sized systems that have the following backup regime:
1. Daily full backups
2. Hourly transaction log backups
3. During the window 9p.m â' 6.a.m the system drive is backed up by TSM.
The plan is created and maintained by the SQL Maintenance plan (sqlmaint).
The old backup files deleted after 48hours as part of the plan to free up
disk space.
A couple of the database grew just over 12GB and I would like implement the
following back schedule for them:
Mon - Differential database backup
Tue - Differential database backup
Wed - Differential database backup
Thu - Differential database backup
Fri - Differential database backup
Sat - Differential database backup
Sun Full database backup
All days Transaction log backups as above
I have scheduled two jobs, one for weekly full backups and the other for
daily differential backups. My question is â'How do I rollover to the next
week?â' I need somehow override the backup device used for differential
backups at the end of the week. I would like to keep all differential
backups for the week and use append to media option. I could not figure out
how to create a separate file as sqlmaint does for complete backups.
Regards,
ruskiIt's pretty easy to create a new file name for each backup.
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
SET @.DBName = 'YourDBName'
SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Diff_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
SET @.Name = @.DBName + N' Differential Backup'
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , DIFFERENTIAL,
NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
But if you want to use the same filename and overwrite it once a week you
can do something like this:
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
SET @.DBName = 'YourDBName'
SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Diff.BAK'
SET @.Name = @.DBName + N' Differential Backup'
IF DATEPART(dw,GETDATE()) = 1
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , DIFFERENTIAL,
NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
ELSE
BACKUP DATABASE @.DBName TO DISK = @.Device WITH NOINIT , DIFFERENTIAL,
NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
Andrew J. Kelly SQL MVP
"Ruski" <Ruski@.discussions.microsoft.com> wrote in message
news:420D0400-5FD9-461C-B164-2CE18E17444B@.microsoft.com...
> Hello all, hope someone will be able to help.
> I have a number of medium-sized systems that have the following backup
> regime:
> 1. Daily full backups
> 2. Hourly transaction log backups
> 3. During the window 9p.m - 6.a.m the system drive is backed up by TSM.
> The plan is created and maintained by the SQL Maintenance plan (sqlmaint).
> The old backup files deleted after 48hours as part of the plan to free up
> disk space.
> A couple of the database grew just over 12GB and I would like implement
> the
> following back schedule for them:
> Mon - Differential database backup
> Tue - Differential database backup
> Wed - Differential database backup
> Thu - Differential database backup
> Fri - Differential database backup
> Sat - Differential database backup
> Sun Full database backup
> All days Transaction log backups as above
> I have scheduled two jobs, one for weekly full backups and the other for
> daily differential backups. My question is "How do I rollover to the next
> week?" I need somehow override the backup device used for differential
> backups at the end of the week. I would like to keep all differential
> backups for the week and use append to media option. I could not figure
> out
> how to create a separate file as sqlmaint does for complete backups.
> Regards,
> ruski
>|||Thanks, Andrew. That's exactly what I was hopping to achieve. And if I go
with the first solution, would you use xp_cmdshell for deletion of the all
old backup files at the end of the week?
Regards,
ruski
"Andrew J. Kelly" wrote:
> It's pretty easy to create a new file name for each backup.
>
> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
> SET @.DBName = 'YourDBName'
> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Diff_' +
> CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
> SET @.Name = @.DBName + N' Differential Backup'
> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , DIFFERENTIAL,
> NOUNLOAD ,
> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
>
>
> But if you want to use the same filename and overwrite it once a week you
> can do something like this:
> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
> SET @.DBName = 'YourDBName'
> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Diff.BAK'
> SET @.Name = @.DBName + N' Differential Backup'
> IF DATEPART(dw,GETDATE()) = 1
> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , DIFFERENTIAL,
> NOUNLOAD ,
> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
>
> ELSE
> BACKUP DATABASE @.DBName TO DISK = @.Device WITH NOINIT , DIFFERENTIAL,
> NOUNLOAD ,
> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
>
> --
> Andrew J. Kelly SQL MVP
>
> "Ruski" <Ruski@.discussions.microsoft.com> wrote in message
> news:420D0400-5FD9-461C-B164-2CE18E17444B@.microsoft.com...
> > Hello all, hope someone will be able to help.
> >
> > I have a number of medium-sized systems that have the following backup
> > regime:
> > 1. Daily full backups
> > 2. Hourly transaction log backups
> > 3. During the window 9p.m - 6.a.m the system drive is backed up by TSM.
> > The plan is created and maintained by the SQL Maintenance plan (sqlmaint).
> > The old backup files deleted after 48hours as part of the plan to free up
> > disk space.
> >
> > A couple of the database grew just over 12GB and I would like implement
> > the
> > following back schedule for them:
> >
> > Mon - Differential database backup
> > Tue - Differential database backup
> > Wed - Differential database backup
> > Thu - Differential database backup
> > Fri - Differential database backup
> > Sat - Differential database backup
> > Sun Full database backup
> > All days Transaction log backups as above
> >
> > I have scheduled two jobs, one for weekly full backups and the other for
> > daily differential backups. My question is "How do I rollover to the next
> > week?" I need somehow override the backup device used for differential
> > backups at the end of the week. I would like to keep all differential
> > backups for the week and use append to media option. I could not figure
> > out
> > how to create a separate file as sqlmaint does for complete backups.
> >
> > Regards,
> > ruski
> >
>
>|||That is one way to do it. See the attached example. But you can also use
other methods such as the File Scripting Object (FSO) in an Active-X job as
well.
CREATE PROCEDURE remove_old_log_files
@.DelDate DATETIME
AS
SET NOCOUNT ON
DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
SET DATEFORMAT MDY
IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #dirlist (FName VARCHAR(1000))
CREATE TABLE #Errors (Results VARCHAR(1000))
-- Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName)
exec master..xp_cmdshell 'dir /OD C:\Backups\*.trn'
SET @.Error = @.@.ERROR
IF @.Error <> 0
BEGIN
SET @.Msg = 'Error while getting the filenames with DIR '
GOTO On_Error
END
--SELECT * FROM #dirList
-- Remove the garbage
DELETE #dirlist WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL
-- Create a cursor and for each file name do the processing.
-- The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT SUBSTRING(FName,40,40) AS FName
FROM #dirlist
WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
AND SUBSTRING(FName,40,40) LIKE '%.TRN'
OPEN curDir
FETCH NEXT FROM curDir INTO @.Fname
WHILE (@.@.fetch_status = 0)
BEGIN
-- Delete the old backup files
SET @.Delete = 'DEL "C:\Backups\' + @.FName + '"'
INSERT INTO #Errors (Results)
exec master..xp_cmdshell @.Delete
IF @.@.RowCount > 1
BEGIN
SET @.Error = -1
SET @.Msg = 'Error while Deleting file ' + @.FName
GOTO On_Error
END
-- PRINT @.Delete
PRINT 'Deleted ' + @.FName + ' at ' +
CONVERT(VARCHAR(28),GETDATE(),113)
FETCH NEXT FROM curDir INTO @.Fname
END
CLOSE curDir
DEALLOCATE curDir
DROP TABLE #DirList
DROP TABLE #Errors
RETURN @.Error
On_Error:
BEGIN
IF @.Error <> 0
BEGIN
SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
RAISERROR(@.Msg,12,1)
RETURN @.Error
END
END
GO
--
Andrew J. Kelly SQL MVP
"Ruski" <Ruski@.discussions.microsoft.com> wrote in message
news:E480C761-8130-4A7B-B7EB-B032B0440C1E@.microsoft.com...
> Thanks, Andrew. That's exactly what I was hopping to achieve. And if I go
> with the first solution, would you use xp_cmdshell for deletion of the all
> old backup files at the end of the week?
> Regards,
> ruski
> "Andrew J. Kelly" wrote:
>> It's pretty easy to create a new file name for each backup.
>>
>> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
>> SET @.DBName = 'YourDBName'
>> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Diff_' +
>> CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
>> SET @.Name = @.DBName + N' Differential Backup'
>> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , DIFFERENTIAL,
>> NOUNLOAD ,
>> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
>>
>>
>> But if you want to use the same filename and overwrite it once a week you
>> can do something like this:
>> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
>> SET @.DBName = 'YourDBName'
>> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Diff.BAK'
>> SET @.Name = @.DBName + N' Differential Backup'
>> IF DATEPART(dw,GETDATE()) = 1
>> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , DIFFERENTIAL,
>> NOUNLOAD ,
>> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
>>
>> ELSE
>> BACKUP DATABASE @.DBName TO DISK = @.Device WITH NOINIT ,
>> DIFFERENTIAL,
>> NOUNLOAD ,
>> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Ruski" <Ruski@.discussions.microsoft.com> wrote in message
>> news:420D0400-5FD9-461C-B164-2CE18E17444B@.microsoft.com...
>> > Hello all, hope someone will be able to help.
>> >
>> > I have a number of medium-sized systems that have the following backup
>> > regime:
>> > 1. Daily full backups
>> > 2. Hourly transaction log backups
>> > 3. During the window 9p.m - 6.a.m the system drive is backed up by TSM.
>> > The plan is created and maintained by the SQL Maintenance plan
>> > (sqlmaint).
>> > The old backup files deleted after 48hours as part of the plan to free
>> > up
>> > disk space.
>> >
>> > A couple of the database grew just over 12GB and I would like implement
>> > the
>> > following back schedule for them:
>> >
>> > Mon - Differential database backup
>> > Tue - Differential database backup
>> > Wed - Differential database backup
>> > Thu - Differential database backup
>> > Fri - Differential database backup
>> > Sat - Differential database backup
>> > Sun Full database backup
>> > All days Transaction log backups as above
>> >
>> > I have scheduled two jobs, one for weekly full backups and the other
>> > for
>> > daily differential backups. My question is "How do I rollover to the
>> > next
>> > week?" I need somehow override the backup device used for differential
>> > backups at the end of the week. I would like to keep all differential
>> > backups for the week and use append to media option. I could not
>> > figure
>> > out
>> > how to create a separate file as sqlmaint does for complete backups.
>> >
>> > Regards,
>> > ruski
>> >
>>

Backup Schedule

Hi
In MSDE2000 i can create backup from database and schedule it
how can i do this with SQLExpr.2005?
Mex
hi Mex,
Meelis Lilbok wrote:
> Hi
> In MSDE2000 i can create backup from database and schedule it
> how can i do this with SQLExpr.2005?
SQLExpress does not (very unfortunately) provide the SQL Server Agent..
but you can achieve a similar result scheduling your maintenance activities
(say a .sql file) via the AT or SCHTASK operating system scheduler..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Backup Schedule

Hi,
What is the easiest way, to schedule database backups in the express edition
2005 within the management studio express?
Thanks for your hint.
regards
PatrickHi Patrick
"Patrick D." wrote:
> Hi,
> What is the easiest way, to schedule database backups in the express edition
> 2005 within the management studio express?
> Thanks for your hint.
> regards
> Patrick
You can use the windows scheduler to run a SQLCMD session. You may want to
look at http://www.sqldbatips.com/displaycode.asp?ID=26 to do the backups.
John|||Hello,
If you want to schedule your backups in SQL Express, you could write a T-SQL
script using BACKUP DATABASE and then use
Windows Task Schedule to call SQLCmd to run the script on what schedule you
need the frequency.
Thanks
Hari
"Patrick D." <PatrickD@.discussions.microsoft.com> wrote in message
news:6344AF78-A132-44A9-BE5B-E17A83864AB0@.microsoft.com...
> Hi,
> What is the easiest way, to schedule database backups in the express
> edition
> 2005 within the management studio express?
> Thanks for your hint.
> regards
> Patrick|||http://blogs.msdn.com/rogerwolterblog/archive/2006/04/13/575974.aspx
Backing up Express
Automating Database maintenance in SQL 2005 Express Edition Part I
http://www.sqldbatips.com/showarticle.asp?ID=27
Automating Database maintenance in SQL 2005 Express Edition Part II
http://www.sqldbatips.com/showarticle.asp?ID=29
Andrew J. Kelly SQL MVP
"Patrick D." <PatrickD@.discussions.microsoft.com> wrote in message
news:6344AF78-A132-44A9-BE5B-E17A83864AB0@.microsoft.com...
> Hi,
> What is the easiest way, to schedule database backups in the express
> edition
> 2005 within the management studio express?
> Thanks for your hint.
> regards
> Patrick

Backup Schedule

Hi
I want to take a backup of SQL Server database on weekly
basis. How can I set up things on my server ? any script
will have to be written or any tool to be used ? pl.
help..
JayHi,
You can use database maintenance plans (Enterprise Manager) to create a
backup job which can be scheduled to execute every week once.
Thanks
Hari
MCDBA
"Jay" <neessan_gen@.rediffmail.com> wrote in message
news:048201c39daf$d6959230$a501280a@.phx.gbl...
> Hi
> I want to take a backup of SQL Server database on weekly
> basis. How can I set up things on my server ? any script
> will have to be written or any tool to be used ? pl.
> help..
> Jay|||In addition to Hari's post:
Weekly? That would never go in my installation. You are essentially saying that it is OK to lose up
to one week worth of data. My baseline is database backup every day and log backup every hour.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jay" <neessan_gen@.rediffmail.com> wrote in message news:048201c39daf$d6959230$a501280a@.phx.gbl...
> Hi
> I want to take a backup of SQL Server database on weekly
> basis. How can I set up things on my server ? any script
> will have to be written or any tool to be used ? pl.
> help..
> Jay|||Create a Database Manitance Plan or create job which should start on weekly.
--
Shaju Thomas
"Jay" <neessan_gen@.rediffmail.com> wrote in message
news:048201c39daf$d6959230$a501280a@.phx.gbl...
> Hi
> I want to take a backup of SQL Server database on weekly
> basis. How can I set up things on my server ? any script
> will have to be written or any tool to be used ? pl.
> help..
> Jay|||THANKS HARI and SHAJU, appreciate your help.
Jay
>--Original Message--
>Hi,
>You can use database maintenance plans (Enterprise
Manager) to create a
>backup job which can be scheduled to execute every week
once.
>Thanks
>Hari
>MCDBA
>
>"Jay" <neessan_gen@.rediffmail.com> wrote in message
>news:048201c39daf$d6959230$a501280a@.phx.gbl...
>> Hi
>> I want to take a backup of SQL Server database on weekly
>> basis. How can I set up things on my server ? any script
>> will have to be written or any tool to be used ? pl.
>> help..
>> Jay
>
>.
>|||HI
We hawe 30 database on 3 server with multiple backup strategy. (1-30GB)
Main backup procedure: Backup database to device on network share. Backup the devices to tape.
Customized backup:
Purpose: Reduce nigtly backup time. One backup script for Multi server environment (MSX-TSX).
Easy configuration for new or migrated databases.
Reduce disk usage.
Easy restore. (We can futurely write one script with parameters for restore)
Monday - Saturday
1. - 4 logbackup / day and differential backup 22:00
2. - 4 logbackup / day and differential backup 20:00
3. - 3 logbackup / day and differential backup 20:00
4. - 1 logbackup / day and differential backup 20:00
5. - differential backup 20:00
6. - differential backup 22:00
7. - full backup 20:00 (include master and msdb)
Sunday full backup instead of differential backup
Implementation:
Create registry entry for Bakup folder (eg: HKEY_LOCAL_MACHINE\SOFTWARE\Vodafone\MSSQLbackup)
Create table in msdb:
/********************************************************/
CREATE TABLE Backups (
dbName varchar (255) COLLATE Hungarian_CI_AS NOT NULL , --Database names
MainBKP int NULL --Backup configuration identifier
) ON PRIMARY
GO
/********************************************************/
Populate this table with database name and backup id
Create the following sp in master database:
The Backup script:
/********************************************************/
SET QUOTED_IDENTIFIER OFF GO
SET ANSI_NULLS ON GO
--=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
-- AdmSpBackup: r=F6vid le=EDr=E1s
----
-- Param=E9terek:
--
----
-- T=E1bl=E1k:
-- Haszn=E1lt t=E1bl=E1k neve, hozz=E1f=E9r=E9s m=F3dja
-- ----
-- Hivatkoz=F3 elj=E1r=E1sok:
-- Mely elj=E1r=E1sb=F3l ker=FCl megh=EDv=E1sra ----
-- H=EDvott elj=E1r=E1sok:
-- Mely elj=E1r=E1sokat h=EDv meg
--
----
-- Visszat=E9r=E9si =E9rt=E9kek:
-- =C9rt=E9k: 0, ha sikeres, -101 ha hib=E1s a fut=E1s
-- Eredm=E9nyhalmaz:
-- ----
-- Tranzakci=F3k:
-- Nem haszn=E1l tranzakci=F3t
----
-- R=E9szletes le=EDr=E1s:
-- ----
-- Jegyzet: ----
-- K=E9sz=EDtette: Jakus Andr=E1s, 2003-10-20
----
-- T=F6rt=E9net:
-- -- --=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
ALTER PROCEDURE AdmSpBackup
AS
SET NOCOUNT ON
SET DATEFIRST 1
DECLARE @.set INT
DECLARE @.sql NVARCHAR(4000)
DECLARE @.name VARCHAR(100)
DECLARE @.main INT
DECLARE @.device VARCHAR(255)
DECLARE @.defPath VARCHAR(255)
DECLARE @.dbPath VARCHAR(255)
DECLARE @.tmpStr VARCHAR(200)
DECLARE @.message VARCHAR(8000)
DECLARE @.Err int
DECLARE @.SUBJECT VARCHAR(100)
---
---
SET @.set =3D DATEPART(HH, GETDATE())
SET @.message =3D 'Starting No: ' + CAST(@.set AS VARCHAR(2)) + ' backup on: ' + @.@.SERVERNAME + ' ' + CONVERT(VARCHAR
(100), GETDATE(), 20) + Char(13) + Char(13)
SELECT @.sql =3D CASE WHEN @.set =3D 7 THEN 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP IN (1, 2)'
WHEN @.set =3D 12 THEN 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP IN (1, 2, 3)'
WHEN @.set =3D 16 THEN 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP IN (1, 2, 3)'
WHEN @.set =3D 20 THEN 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP IN (2, 3, 4, 5, 7)'
WHEN @.set =3D 22 THEN 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP IN (1, 6)'
ELSE 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP =3D -1'
END
EXEC sp_executesql @.sql
---
----
SET @.tmpStr =3D REPLACE(@.@.SERVERNAME, '\', '-')
-- A registry-ben be=E1ll=EDtott ment=E9si k=F6nyvt=E1r
EXEC master..xp_regread @.rootkey=3D'HKEY_LOCAL_MACHINE',
@.key=3D'SOFTWARE\Vodafone\MSSQLbackup',
@.value_name =3D @.tmpStr,
@.value=3D@.defPath OUTPUT
-- Van-=E9 k=F6nyvt=E1ra az adatb=E1zisnak
SET @.tmpStr =3D 'master..xp_subdirs ''' + @.defPath + ''''
CREATE TABLE #Dirs (a VARCHAR(100))
insert INTO #Dirs (a) EXEC(@.tmpStr)
---
----
DECLARE bcks CURSOR FOR SELECT * FROM ##dbs
OPEN bcks
FETCH NEXT FROM bcks
INTO @.name, @.main
WHILE @.@.FETCH_STATUS =3D 0
BEGIN
/**---
---**/
/** Verify, that have subfolder for database **/
/**---
---**/
If @.name not in (select a from #Dirs)
BEGIN
=09
SET @.tmpStr =3D 'Mkdir "' + @.defPath + @.name + '"' --
Create subfolder for database
=09
EXECUTE xp_cmdshell @.tmpStr
=09
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: ' + @.tmpStr + Char(13) + Char
(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Successfully created a new backup folder: ' + @.defPath + @.name + Char(13) + Char
(13)
END
=09
END
/**---
---**/
/** Verify, that have Backup media for full database backup **/
/**---
---**/
If @.Name + '-Full' not in (SELECT logical_device_name from msdb.dbo.backupmediafamily)
BEGIN
SET @.device =3D @.Name + '-Full'
If @.device not in (select LTRIM(RTRIM(name)) from sysdevices where cntrltype =3D 2)
BEGIN
SET @.dbPath =3D @.defPath + @.Name + '\' + @.device + '.bak'
SELECT @.dbPath
=09
EXECUTE sp_addumpdevice @.devtype =3D 'Disk'
, @.logicalname =3D @.device
, @.physicalname =3D @.dbPath
, @.cntrltype =3D 2
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: sp_addumpdevice ' + @.device + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Successfully created a new backup device: ' + @.device + Char(13) + Char(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
END
=09
=09
=09
BEGIN
=09
SET @.message =3D @.message + CONVERT(VARCHAR
(100), GETDATE(), 20) + ': Backup Database ' + @.Name + ' started (Full Backup)' + Char(13) + Char(13)
BACKUP DATABASE @.Name
TO @.device
WITH
INIT
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: BACKUP DATABASE ' + @.Name + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': '+ @.Name + ' database successfully backed up to: ' + @.Device + Char(13) + Char
(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
END
END
ELSE
BEGIN
/**---
---
If @.main < 5 then backup log for @.Name
---
---**/
If @.Main < 5
BEGIN
SET @.device =3D @.Name + '-Log' + CAST(@.set AS VARCHAR(2))
If @.device not in (select LTRIM(RTRIM(name)) from sysdevices where cntrltype =3D 2)
BEGIN
SET @.dbPath =3D @.defPath + @.Name + '\' + @.device + '.trn'
SELECT @.dbPath
=09
EXECUTE sp_addumpdevice @.devtype =3D 'Disk'
, @.logicalname =3D @.device
, @.physicalname =3D @.dbPath
, @.cntrltype =3D 2
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: sp_addumpdevice ' + @.device + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Successfully created a new backup device: ' + @.device + Char(13) + Char(13)
END
=09
=09
END
SET @.message =3D @.message + CONVERT(VARCHAR
(100), GETDATE(), 20) + ': Backup Log ' + @.Name + ' started' + Char(13) + Char(13)
BACKUP LOG @.Name=09
TO @.device
WITH
INIT
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: BACKUP LOG ' + @.Name + Char
(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': '+ @.Name + ' log successfully backed up to: ' + @.Device + Char(13) + Char
(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
END
/**---
---
If @.set > 19 then backup database @.Name
If DATEPART(dw, GETDATE()) =3D 7 Then Full backup
---
---**/
If DATEPART(dw, GETDATE()) =3D 7 AND @.set > 19 --@.Name NOT IN ('master', 'msdb')
BEGIN =09
SET @.message =3D @.message + CONVERT(VARCHAR
(100), GETDATE(), 20) + ': Backup Database ' + @.Name + ' started (Full Backup)' + Char(13) + Char(13)
SET @.device =3D @.Name + '-Full'
=09
BACKUP DATABASE @.Name
TO @.device
WITH
RETAINDAYS =3D 6,
INIT
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: BACKUP DATABASE ' + @.Name + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': '+ @.Name + ' database successfully backed up to: ' + @.Device + Char(13) + Char
(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
=09
END
ELSE
/**---
---
If DATEPART(dw, GETDATE()) < 7 Then Differential backup, @.main < 7 ---
---**/
BEGIN
If @.main < 7 AND @.set > 19
BEGIN
SET @.device =3D @.Name + '-Diff'
If @.device not in (select LTRIM(RTRIM(name)) from sysdevices where cntrltype =3D 2)
BEGIN
SET @.dbPath =3D @.defPath + @.Name + '\' + @.device + '.bak'
SELECT @.dbPath
=09
EXECUTE sp_addumpdevice @.devtype =3D 'Disk'
, @.logicalname =3D @.device
, @.physicalname =3D @.dbPath
, @.cntrltype =3D 2
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: sp_addumpdevice ' + @.device + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Successfully created a new backup device: ' + @.device + Char(13) + Char(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
=09
END
=09
SET @.message =3D @.message + CONVERT(VARCHAR
(100), GETDATE(), 20) + ': Backup Database ' + @.Name + ' started (Differential Backup)' + Char(13) + Char(13)
BACKUP DATABASE @.Name TO @.device
WITH DIFFERENTIAL,
INIT
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS varchar(30)) + 'Command: BACKUP DATABASE ' + @.Name + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': '+ @.Name + ' database successfully backed up to: ' + @.Device + Char(13) + Char
(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
END
ELSE
BEGIN
SET @.device =3D @.Name + 'Full'
If @.set > 19 BEGIN
If @.device not in (select LTRIM
(RTRIM(name)) from sysdevices where cntrltype =3D 2)
BEGIN
SET @.dbPath =3D @.defPath + @.Name + '\' + @.device + '.bak'
SELECT @.dbPath
=09
EXECUTE sp_addumpdevice @.devtype =3D 'Disk'
, @.logicalname =3D @.device
, @.physicalname =3D @.dbPath
, @.cntrltype =3D 2
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS varchar(30)) + 'Command: sp_addumpdevice ' + @.device + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Successfully created a new backup device: ' + @.device + Char(13) + Char(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
=09
END
=09
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Backup Database ' + @.Name + ' started (Full Backup)' + Char(10) + Char(13)
BACKUP DATABASE @.Name
TO @.device
WITH
INIT
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST
(@.Err AS varchar(30)) + 'Command: BACKUP DATABASE ' + @.Name + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT(VARCHAR(100), GETDATE(), 20) + ': '+ @.Name + ' database successfully backed up to: ' + @.Device + Char(13) + Char(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
END
END
END
/**-- next database --
---**/
END
FETCH NEXT FROM bcks
INTO @.name, @.main
END
CLOSE bcks
DEALLOCATE bcks
/**---
---**/
/** Send message **/
/**---
---**/
SET @.message =3D @.message + 'Completed No: ' + CAST(@.set AS VARCHAR(2)) + ' backup on: ' + @.@.SERVERNAME + ' ' + CONVERT(VARCHAR(100), GETDATE(), 20)
SET @.SUBJECT =3D 'Backup report from ' + @.@.SERVERNAME
EXECUTE xp_sendmail @.Recipients =3D 'andras.jakus@.vodafone.com', --Your address here
@.Message =3D @.message,
@.Subject =3D @.SUBJECT
/**---
---**/
/** Drop alltemporary tables **/
/**---
---**/
DROP TABLE ##dbs
DROP TABLE #Dirs
/*******************************************************/
Change @.Recipients to your address
Create (Multi Server)job with one step: EXECUTE master.dbo.AdmSpBackup
Schedule the job: 07:00; 12:00; 16:00; 20:00; 22:00
>--Original Message--
>In addition to Hari's post:
>Weekly? That would never go in my installation. You are essentially saying that it is OK to lose up
>to one week worth of data. My baseline is database backup every day and log backup every hour.
>-- >Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=3Ddjq&as_ugroup=3Dmicrosoft.public.sqlserver
>
>"Jay" <neessan_gen@.rediffmail.com> wrote in message news:048201c39daf$d6959230$a501280a@.phx.gbl...
>> Hi
>> I want to take a backup of SQL Server database on weekly
>> basis. How can I set up things on my server ? any script
>> will have to be written or any tool to be used ? pl.
>> help..
>> Jay
>
>.
>sql

backup schedule

SQL server 2005 is installed but can not schedule a backup job.
In the maintenance plan when selecting a new maintenance plan or maintenance plan wizard an error is produced which is:
'Agent XPs' component is turned off as part of the security configuration for this server.
I can see that the SQL Server Agent (Agent XPs is disabled). When trying to start this by right clicking on it in object explorer, the error message is 'unable to start service SQLAgent$SQL2005 on server serverName'. Service Logon failure

How can I scedule a back up job on this server?

Any thoughts please?

"Service Logon Failure" message means that user name and pwd provided for the service are incorrect. You can fix them in services snap-in (type services.msc in commnd line or try My Computer -> Manage -> Services And Applications -> Services -> double click on SQLAgent$SQL2005 service -> Log On tab )|||

Hi,

Went to the Logon Tab.

There is already a log on setup as admin

What next?

Thanks

|||

There is already a log on setup as admin

Then provide a correct password for him.

Backup Schedule

Hi,
What is the easiest way, to schedule database backups in the express edition
2005 within the management studio express?
Thanks for your hint.
regards
Patrick
Hi Patrick
"Patrick D." wrote:

> Hi,
> What is the easiest way, to schedule database backups in the express edition
> 2005 within the management studio express?
> Thanks for your hint.
> regards
> Patrick
You can use the windows scheduler to run a SQLCMD session. You may want to
look at http://www.sqldbatips.com/displaycode.asp?ID=26 to do the backups.
John
|||Hello,
If you want to schedule your backups in SQL Express, you could write a T-SQL
script using BACKUP DATABASE and then use
Windows Task Schedule to call SQLCmd to run the script on what schedule you
need the frequency.
Thanks
Hari
"Patrick D." <PatrickD@.discussions.microsoft.com> wrote in message
news:6344AF78-A132-44A9-BE5B-E17A83864AB0@.microsoft.com...
> Hi,
> What is the easiest way, to schedule database backups in the express
> edition
> 2005 within the management studio express?
> Thanks for your hint.
> regards
> Patrick
|||http://blogs.msdn.com/rogerwolterblog/archive/2006/04/13/575974.aspx
Backing up Express
Automating Database maintenance in SQL 2005 Express Edition Part I
http://www.sqldbatips.com/showarticle.asp?ID=27
Automating Database maintenance in SQL 2005 Express Edition Part II
http://www.sqldbatips.com/showarticle.asp?ID=29
Andrew J. Kelly SQL MVP
"Patrick D." <PatrickD@.discussions.microsoft.com> wrote in message
news:6344AF78-A132-44A9-BE5B-E17A83864AB0@.microsoft.com...
> Hi,
> What is the easiest way, to schedule database backups in the express
> edition
> 2005 within the management studio express?
> Thanks for your hint.
> regards
> Patrick

Backup Schedule

Hi,
What is the easiest way, to schedule database backups in the express edition
2005 within the management studio express?
Thanks for your hint.
regards
PatrickHi Patrick
"Patrick D." wrote:

> Hi,
> What is the easiest way, to schedule database backups in the express editi
on
> 2005 within the management studio express?
> Thanks for your hint.
> regards
> Patrick
You can use the windows scheduler to run a SQLCMD session. You may want to
look at http://www.sqldbatips.com/displaycode.asp?ID=26 to do the backups.
John|||Hello,
If you want to schedule your backups in SQL Express, you could write a T-SQL
script using BACKUP DATABASE and then use
Windows Task Schedule to call SQLCmd to run the script on what schedule you
need the frequency.
Thanks
Hari
"Patrick D." <PatrickD@.discussions.microsoft.com> wrote in message
news:6344AF78-A132-44A9-BE5B-E17A83864AB0@.microsoft.com...
> Hi,
> What is the easiest way, to schedule database backups in the express
> edition
> 2005 within the management studio express?
> Thanks for your hint.
> regards
> Patrick|||http://blogs.msdn.com/rogerwolterbl.../13/575974.aspx
Backing up Express
Automating Database maintenance in SQL 2005 Express Edition Part I
http://www.sqldbatips.com/showarticle.asp?ID=27
Automating Database maintenance in SQL 2005 Express Edition Part II
http://www.sqldbatips.com/showarticle.asp?ID=29
Andrew J. Kelly SQL MVP
"Patrick D." <PatrickD@.discussions.microsoft.com> wrote in message
news:6344AF78-A132-44A9-BE5B-E17A83864AB0@.microsoft.com...
> Hi,
> What is the easiest way, to schedule database backups in the express
> edition
> 2005 within the management studio express?
> Thanks for your hint.
> regards
> Patrick

Monday, March 19, 2012

Backup procedure

Hello there!
I have SQLServer 2000 running on Windows 2000 advanced server.
I want to schedule daily full backup.
Do I have to stop SQLServer service before it starts backup?
Or can users have full access to the database while it's doing the backup?
Thanks in advanceRead the chapter on Backup and Restore in BOL (Books On-Line). This will
explain some of the unique considerations and options for backing up a SQL
server installation.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rexford" <anonymous@.discussions.microsoft.com> wrote in message
news:B5E24755-6290-44CD-9FC2-99309FB5C9D4@.microsoft.com...
> Hello there!
> I have SQLServer 2000 running on Windows 2000 advanced server.
> I want to schedule daily full backup.
> Do I have to stop SQLServer service before it starts backup?
> Or can users have full access to the database while it's doing the backup?
> Thanks in advance|||Hi,
It is not at all required to shutdown SQL server while doing a full database
backup. But try to perform the full database backup during the time zone
where user access is less.
Thanks
Hari
MCDBA
"Rexford" <anonymous@.discussions.microsoft.com> wrote in message
news:B5E24755-6290-44CD-9FC2-99309FB5C9D4@.microsoft.com...
> Hello there!
> I have SQLServer 2000 running on Windows 2000 advanced server.
> I want to schedule daily full backup.
> Do I have to stop SQLServer service before it starts backup?
> Or can users have full access to the database while it's doing the backup?
> Thanks in advance

Backup procedure

Hello there
I have SQLServer 2000 running on Windows 2000 advanced server
I want to schedule daily full backup
Do I have to stop SQLServer service before it starts backup
Or can users have full access to the database while it's doing the backup
Thanks in advanceRead the chapter on Backup and Restore in BOL (Books On-Line). This will
explain some of the unique considerations and options for backing up a SQL
server installation.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rexford" <anonymous@.discussions.microsoft.com> wrote in message
news:B5E24755-6290-44CD-9FC2-99309FB5C9D4@.microsoft.com...
> Hello there!
> I have SQLServer 2000 running on Windows 2000 advanced server.
> I want to schedule daily full backup.
> Do I have to stop SQLServer service before it starts backup?
> Or can users have full access to the database while it's doing the backup?
> Thanks in advance|||Hi,
It is not at all required to shutdown SQL server while doing a full database
backup. But try to perform the full database backup during the time zone
where user access is less.
Thanks
Hari
MCDBA
"Rexford" <anonymous@.discussions.microsoft.com> wrote in message
news:B5E24755-6290-44CD-9FC2-99309FB5C9D4@.microsoft.com...
> Hello there!
> I have SQLServer 2000 running on Windows 2000 advanced server.
> I want to schedule daily full backup.
> Do I have to stop SQLServer service before it starts backup?
> Or can users have full access to the database while it's doing the backup?
> Thanks in advance

Sunday, March 11, 2012

Backup Planning assistance

Hi all,

New to database admin and I am attempting to work out a god backup schedule.

The database is a failry critical system so minimal downtime and dataloss from failure is the key to this.

Assuming storage is not an issue for me can anyone add some help.

What I was planning was a full backup an Sunday night and Wednesday nights at 6pm using

'backup database helpdesk to BK1 with noformat, init, stats'

Differential backups each night at 6pm using

'backup database helpdesk to BK1 with differential, noformat, noinit, stats'

Then a transaction log backup every 4 hours during the day with ??

'backup log helpdesk to Bk1 with ??

This is the part I am stuck on...

Firstly should all backups be going to the same logical backup device? in this case BK1 which points to a network share or should each go to a different location?

And what switches should I be using with the TL backup?

If anyone has a good backup T-SQL script they use I would really appreciate some help.

Cheers
MarkMark

When you say minimal downtime - do you mean when the backup is being taken or on recovery of the database?

I always take the method of keeping the backup strategy as simple as possible. I personally would take a complete backup every night (if you have enoght time - ie not a 24 hour operation) and trans log backups as you see fit. This can all be set up via Enterpise Manager very easily.

As for where to store - depends on what type of crash you wish to recover from - at the extreme level thay need to be stored on external media (eg tape) so that they may be restored on another server.|||Mark

When you say minimal downtime - do you mean when the backup is being taken or on recovery of the database?

I always take the method of keeping the backup strategy as simple as possible. I personally would take a complete backup every night (if you have enoght time - ie not a 24 hour operation) and trans log backups as you see fit. This can all be set up via Enterpise Manager very easily.

As for where to store - depends on what type of crash you wish to recover from - at the extreme level thay need to be stored on external media (eg tape) so that they may be restored on another server.|||Thanks for the reply,

When I said down time I did mean time to recover, so you are correct.

I think I will take your advise and do a fullbackup each night. Cheers

Wednesday, March 7, 2012

backup of transaction log

How to schedule backup of transaction log?Use Jobs

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________

<alwinkotiya@.gmail.com> wrote in message
news:1150212250.543197.268640@.g10g2000cwb.googlegr oups.com...
> How to schedule backup of transaction log?|||Create a Database Maintenance Plan. In the Enterprise Manager goto
"<ServerName>", "Management" Right Click and say "New Maintenance Plan."

That is if you have SQL 7 or 2000 I don't know if it is different in 2005.

--
-Dick Christoph
<alwinkotiya@.gmail.com> wrote in message
news:1150212250.543197.268640@.g10g2000cwb.googlegr oups.com...
> How to schedule backup of transaction log?

Backup of transaction log

Alwin,
Create a job, use statement "backup log", and schedule the job.
AMB
"Alwin" wrote:

> How to schedule backup of transaction log?
>Thanks AMB for your quick reply ,
But I am new to sql server can you tell me stepswise solution.
Alejandro Mesa wrote:
[vbcol=seagreen]
> Alwin,
> Create a job, use statement "backup log", and schedule the job.
>
> AMB
> "Alwin" wrote:
>|||How to schedule backup of transaction log?|||Alwin,
Create a job, use statement "backup log", and schedule the job.
AMB
"Alwin" wrote:

> How to schedule backup of transaction log?
>|||Thanks AMB for your quick reply ,
But I am new to sql server can you tell me stepswise solution.
Alejandro Mesa wrote:
[vbcol=seagreen]
> Alwin,
> Create a job, use statement "backup log", and schedule the job.
>
> AMB
> "Alwin" wrote:
>|||Alwin wrote:
> Thanks AMB for your quick reply ,
> But I am new to sql server can you tell me stepswise solution.
>
> Alejandro Mesa wrote:
>
>
IF you're not familiar with SQL programming, the easiest might be to
create a maintenance plan to do the job. You can read up on maintenance
plans in Books On Line.
Regards
Steen Schlter Persson
DBA|||Alwin wrote:
> Thanks AMB for your quick reply ,
> But I am new to sql server can you tell me stepswise solution.
>
> Alejandro Mesa wrote:
>
>
IF you're not familiar with SQL programming, the easiest might be to
create a maintenance plan to do the job. You can read up on maintenance
plans in Books On Line.
Regards
Steen Schlter Persson
DBA

Backup of transaction log

How to schedule backup of transaction log?Alwin,
Create a job, use statement "backup log", and schedule the job.
AMB
"Alwin" wrote:
> How to schedule backup of transaction log?
>|||Thanks AMB for your quick reply ,
But I am new to sql server can you tell me stepswise solution.
Alejandro Mesa wrote:
> Alwin,
> Create a job, use statement "backup log", and schedule the job.
>
> AMB
> "Alwin" wrote:
> > How to schedule backup of transaction log?
> >
> >|||Alwin wrote:
> Thanks AMB for your quick reply ,
> But I am new to sql server can you tell me stepswise solution.
>
> Alejandro Mesa wrote:
>> Alwin,
>> Create a job, use statement "backup log", and schedule the job.
>>
>> AMB
>> "Alwin" wrote:
>> How to schedule backup of transaction log?
>>
>
IF you're not familiar with SQL programming, the easiest might be to
create a maintenance plan to do the job. You can read up on maintenance
plans in Books On Line.
--
Regards
Steen Schlüter Persson
DBA

Friday, February 24, 2012

backup not working

In Enterprise Manager, I created a schedule to backup the
database. But the database is not being backed up.
I am able to manually take backup of my database.
What is the best option to take complete backup.
Should I append to the media or overwrite?
Thank you in advance,
LindaDo you have Server Agent runing?
Bojidar Alexnadrov|||Hi,
Check "SQLServer Agent" service is running. If not start the service and
check if the schedule is working.
What is the best option to take complete backup. Should I append to the
media or overwrite?
Both options are fine, But due to hard disk space limitations mostly people
choose the overwrite options (WITH INIT).
But ensure that old backup file is copied / moved to a remote hard disk or
Tape before overwriting.
Thanks
Hari
MCDBA
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:1931e01c44ca6$7bc051a0$a501280a@.phx.gbl...
> In Enterprise Manager, I created a schedule to backup the
> database. But the database is not being backed up.
> I am able to manually take backup of my database.
> What is the best option to take complete backup.
> Should I append to the media or overwrite?
> Thank you in advance,
> Linda|||Linda,
Answers inline.
Linda wrote:
> In Enterprise Manager, I created a schedule to backup the
> database. But the database is not being backed up.
> I am able to manually take backup of my database.
Is SQL Server Agent running? Do you get any errors? What does the
history for the job say?
> What is the best option to take complete backup.
> Should I append to the media or overwrite?
Depends. If backing up to disk I like to create a new file each time and
not overwrite the previous, or append. If going directly to tape you may
wish to append. What are you doing?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html|||Hari,
Thank you very much for your reply!
It works now.
-Linda
>--Original Message--
>Hi,
>Check "SQLServer Agent" service is running. If not start
the service and
>check if the schedule is working.
>What is the best option to take complete backup. Should
I append to the
>media or overwrite?
>Both options are fine, But due to hard disk space
limitations mostly people
>choose the overwrite options (WITH INIT).
>But ensure that old backup file is copied / moved to a
remote hard disk or
>Tape before overwriting.
>Thanks
>Hari
>MCDBA
>
>
>"Linda" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1931e01c44ca6$7bc051a0$a501280a@.phx.gbl...
>> In Enterprise Manager, I created a schedule to backup
the
>> database. But the database is not being backed up.
>> I am able to manually take backup of my database.
>> What is the best option to take complete backup.
>> Should I append to the media or overwrite?
>> Thank you in advance,
>> Linda
>
>.
>|||Mark,
Thank you very much for your reply! The agent wasn't up.
It works now.
-Linda
>--Original Message--
>Linda,
>Answers inline.
>
>Linda wrote:
>> In Enterprise Manager, I created a schedule to backup
the
>> database. But the database is not being backed up.
>> I am able to manually take backup of my database.
>Is SQL Server Agent running? Do you get any errors? What
does the
>history for the job say?
>> What is the best option to take complete backup.
>> Should I append to the media or overwrite?
>Depends. If backing up to disk I like to create a new
file each time and
>not overwrite the previous, or append. If going directly
to tape you may
>wish to append. What are you doing?
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>.
>|||Bojidar,
Thank you very much for your reply! The Agent wasn't
running. It works now.
-Linda
>--Original Message--
>Do you have Server Agent runing?
>Bojidar Alexnadrov
>
>.
>

Thursday, March 22, 2012

Backup Scheduling

Backup Scheduling

Backup Scheduling

Backup Schedule Troubles

Backup Schedule Troubles

Backup Schedule Troubles

Backup schedule (Differential backups)

Backup Schedule

Backup Schedule

Backup Schedule

backup schedule

Backup Schedule

Backup Schedule

Monday, March 19, 2012

Backup procedure

Backup procedure

Sunday, March 11, 2012

Backup Planning assistance

Wednesday, March 7, 2012

backup of transaction log

Backup of transaction log

Backup of transaction log

Friday, February 24, 2012

backup not working

sql backup files

Blog Archive

About Me