Backing up database

Hi All. I currently have a SQL server database which I
back up nightly using a job defined in SQL server and a
USB 2.0 external HD. My question is: I have created a .bat
file to do the backup to the external drive. The file
shuts down all SQL services that are running first, it
backes up the file (both .mdf/.ldf) then it restarts the
server and the servces (sql agent etc). The database is
only about 1/2 GB at this point so it takes about 10
minutes to back the whole \Data directory up. What I'd
like to know is, is there any implications for the
database or server using such a procedure every night 5
nights a week? Or it it completely fine? Thank you for
your help
Leo"Leo" <anonymous@.discussions.microsoft.com> wrote in message
news:03b501c3ae2a$61c166b0$a501280a@.phx.gbl...
> Hi All. I currently have a SQL server database which I
> back up nightly using a job defined in SQL server and a
> USB 2.0 external HD. My question is: I have created a .bat
> file to do the backup to the external drive. The file
> shuts down all SQL services that are running first, it
> backes up the file (both .mdf/.ldf) then it restarts the
> server and the servces (sql agent etc). The database is
> only about 1/2 GB at this point so it takes about 10
> minutes to back the whole \Data directory up. What I'd
> like to know is, is there any implications for the
> database or server using such a procedure every night 5
> nights a week? Or it it completely fine? Thank you for
> your help
>
Well that's not how most people do SQL Server backups.
First off, you only have 1 backup. If you have a problem you don't catch
for a couple of days, or a datafile corruption that prevents SqlServer from
restarting you would be in trouble.
Second, your transaction logs will just keep growing. You have to do a log
file backup to truncate them.
Third, you have to shut down the database.
In Enterprise Manager you can easilly set up a Database Maintence Plan for
the server. This will create SQL Agent jobs to perform database and/or log
backups on a schedule. For each schedule you can set a retention period for
the backup files. So you can set up one schedule to do nightly backups
which are retained for say, 2 weeks, and another to to monthly backups which
are retained for a year.
David|||Leo,
This is kind of offline backup strategy that you are opting for. If your business permits taking
application offline then you can implement this strategy otherwise you can opt for native SQL Server
backup (Inclusive of full /differential/transaction log backups). This can be done while database is
online and no need to shutdown the server. Taking backups through SQL Server native backups will
give you flexibility to restore database to a particular point of time or taking differential
backups etc, which will not be possible if you are taking cold / OS level backup by shutting down
the server. You can see more help on backup and restore commands in Books online.
Refer to following url as well.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_63eh.asp
--
- Vishal|||Thank you gentlemen. I should have clarified that I do
daily backups through SQL server of course. I back up all
the databases that way. This *cold* way of backing up is
just an *extra* step I take just in case my whole drive
fails, or something happens that I cannot in no way
restore from the backup tape or the backup folder in SQL.
All I wanted to know was if there would be a problem for
the database shutting down SQL server every night and
doing the cold backup. I have no problem shutting it down
for 15 minutes at night since the production server is
only used from 9-5. Thanks anyway..
Leo
>--Original Message--
>Hi All. I currently have a SQL server database which I
>back up nightly using a job defined in SQL server and a
>USB 2.0 external HD. My question is: I have created
a .bat
>file to do the backup to the external drive. The file
>shuts down all SQL services that are running first, it
>backes up the file (both .mdf/.ldf) then it restarts the
>server and the servces (sql agent etc). The database is
>only about 1/2 GB at this point so it takes about 10
>minutes to back the whole \Data directory up. What I'd
>like to know is, is there any implications for the
>database or server using such a procedure every night 5
>nights a week? Or it it completely fine? Thank you for
>your help
>Leo
>.
>|||This (may) be better than using a bat file, I use it to chuck a copy over to
another server, as a secondary backup to a Backup Job, I just run it in task
scheduler.
'************************************************************************
'VBSCRIP to backup database's and email results
'1 Novenber 2003 Don Grover
'************************************************************************
'Open connection to SQL Server
Set Conn = CreateObject("ADODB.Connection")
Conn.Open _
"Provider=SQLOLEDB" & _
";Data Source=assoftsvr" & _
";Integrated Security=SSPI"
'Call the backup routine
DoBackup "GTS"
DoBackup "CokeShop"
DoBackup "BGINFO"
Conn.Close
Set Conn = Nothing
Sub DoBackup(sDbName)
'Sub accepts a databse name and backs up to predefined location on another
server
On Error Resume Next
Conn.Execute _
"BACKUP DATABASE " & sDbName & _
" TO DISK='\\nassvr\Backups\" & sDbName & "_db_" &
formatdatetime(now,1) & ".bak'" & _
" WITH INIT"
Conn.Execute _
"BACKUP LOG " & sDbName & _
" TO DISK='\\nassvr\Backups\" & sDbName & "_log_" &
formatdatetime(now,1) & ".bak'" & _
" "
If Err.Number = 0 Then
SendEmail sDbName,"SUCCESS"
Else
SendEmail sDbName,"BACKUP FAILED " & Err.Number & "Desc: " &
Err.Description
End If
Err.Clear
On Error Goto 0
End Sub
Sub SendEmail(sDatabaseName,sMessage)
Set objEmail = CreateObject("CDO.Message")
objEmail.From = "support@.assoft.com.au"
objEmail.To = "Don Grover <dgrover@.assoft.com.au>"
objEmail.Subject = "Backed up " & sDatabaseName & " on " &
formatdatetime(now,1)
objEmail.Textbody = "Backup " & sMessage & " of " & sDatabaseName & " to
\\NASSvr...."
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
"assoftsvr"
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.Send
Set objEmail = Nothing
End Sub
'Wscript.Echo "Backup complete"sql