Need backup script for SQL Server 2000

cgtyoder
cgtyoder used Ask the Experts™
on
I have inherited a Windows 2000 (Pro SP4) PC running some restaurant software with SQL Server 2000.  The owner doesn't want to spend a lot of $$$ for SQL backup software, and I am no SQL genius.  Is there some handy script I can plug in somewhere which will make a simple full copy of the DB or something daily?

I see some apparently useful info at http://www.akadia.com/services/sqlsrv_programming.html#Backup%20a%20Database but I'm not sure where this goes.

And btw where is the Enterprise Manager executable?  I can't seem to find it.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
>And btw where is the Enterprise Manager executable?
its an mmc
start -> programs -> microsoft sql server -> enterprise manager


you can schedule a Job or create a maintenace task to do the backup
Here is a quick and dirty. However, you would be better off following aneesh's recommendation to create a maintenance plan instead that will simplify backing up multiple databases and scheduling such backups.
Here is pretty much all you need to know about maintenance plans, backup & restores -after all, what good is a backup if you don't know how to use it?
http://technet.microsoft.com/en-us/library/cc966495.aspx 

BACKUP DATABASE 
	[DATABASENAME] 
TO  DISK = N'D:\BackupFolder\BackupFileName.bak' 
WITH 
	NOFORMAT, 
	INIT,  
	NAME = N'[Backup Set Name]', 
	SKIP, 
	NOREWIND, 
	NOUNLOAD,  
	STATS = 10
GO

Open in new window

Author

Commented:
I have a backup system of that W2K PC in place already.  The only thing that isn't getting backed up is the active SQL server DB files.  If the whole system crashes, the people responsible for the restaurant sw will do the restore.  I just need to make sure there are backups (I do have a RAID 1 disk array in place, so that of course helps to keep the system from crashing.)

aneeshattingal: I should have mentioned, there is no such entry under Start->All Programs.  Hence my asking what .exe it is.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
myrotarycar, thanks for the info. I am not familiar enough with SQL server to know where this script goes to be able to run it. Can you point me to the location?
  • Go to START-->RUN
  • Type MMC and press ENTER
  • That will bring up the Microsoft Management Console
  • Click on File-->Add/Remove Snap-in
  • Click on Add
  • Scroll down on the pop up until you find "Microsoft SQL Enterprise Manager" and click Add
  • Click Close
  • Click OK
That will bring up Enterprise Manager. Even on SQL2000 you have some niffty wizards that will walk you through creating maintenance plans & back up / restores.
Good luck!
Commented:
As it turns out, there was already a backup in place of the SQL server, so no need to do anything.  Thanks anyways.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial