Link to home
Create AccountLog in
Avatar of intelliwyse
intelliwyse

asked on

Free SQL backup?

I need a way to backup an SQL database without spending thousands on backup software. I'm running SQL 2005 Standard Edition single CPU x64 and I've got a backup system that is backing up the entire server's state every few minutes to an alternate box, but it's not SQL aware, so I'm worried that if the database becomes corrupted, I'll have to restore the whole server's image, which is time consuming. All I really need to technically do is script a backup job that will simply copy my database even to the same hard disk. So long as I can script it, I'm even ok with taking the database offline during the copy process, since this particular database is only used from 7am-8pm daily. Any thoughts?
Avatar of chapmandew
chapmandew
Flag of United States of America image

backup software comes with sql server


in a query window:

backup database databasename to disk = 'c:\backupfile.bak' with stats = 5
You can use the command from chapmandew in the query window to backup any database on the server.

Your backup system should not backup .mdf (database data files) or .ldf (database log files). Change the settings to ignore these files. I agree with you, your backup system could corrupt the database.

You do not need to take the database off line. SQL is smart enough to perform a backup while being accessed.

Avatar of intelliwyse
intelliwyse

ASKER

SQL isn't my strong suit, so let me make sure I'm doing this right:
Open the SQL Server Managment Studio
Click New Query
Type: backup database databasename to disk = 'c:\backupfile.bak' with stats = 5 (where "databasename" is the name of my database)
Save the query by clicking File, Save... etc

Once the file is saved, I have a .SQL file. How do I go about scripting this file to execute? How do I make sure the query logs into the SQL server with the correc credentials?
Also, take a look in SQL Server Books Online about recovery model (simple versus full recovery).  With full recovery you will be able to restore to a specific point in time regardless of how often your full backups are taken as long as you backup your log files along with your database backups.  As ptjcb mentions, do not mistake log backups with backing up the .ldf file - log backups must be scheduled with SQLs builtin backup utilty as well.
What's the syntax to schedule log file backups as well as the full DB?
--log backup
backup log databasename to file = 'c:\filename.trn'

I gave you the full backup syntax earlier.

here is an article on how its done

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx
Which edition of SQL Server? If you have standard or enterprise, you can use the SQL Agent to schedule the backup. If you are using SQL Express, you can use Windows Task Scheduler.

This link walks you through the steps if you have SQL Express
http://blogs.orcsweb.com/jeremy/archive/2006/11/06/Automating-Database-Backups-with-Microsoft-SQL-Server-2005-Express-Edition.aspx

Oh this is good stuff. I love it! Great article, thanks. And the Books Online got me exactly where I needed to be.
 Got it all scripted and set up.

Just to make sure that when my database gets corrupt, and I go to restore the thing that I've got everything I need, here's an example of the script that SQL 2005 generated for me:

BACKUP DATABASE [IBSCore] TO  DISK = N'D:\DB-BU\Thursday\Tuesday-IBSCORE-DBBU' WITH  DESCRIPTION = N'Thursday Database Backup', NOFORMAT, INIT,  NAME = N'Thursday - IBSCore-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Is this script getting my transaction logs, since I'm running a FULL backup?

What I'm doing is backing the DB up to D:\DB-BU\dayoftheweek\backupfile.bak so I'm scripting 7 backup jobs which will run once per week. Do even need to bother with this, especially if I'm copying my transaction log files via another source, and saving multiple states of the raw transaction log files? Sorry, most of my database knowledge stems from Exchange Server, not direct SQL so my way of thinking may need some adjustment.
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account