[ANSWERED] Can I use NTBackup to backup MSSQL 2005 databases?

[link470]

Hey guys,

 

I'm using Microsoft SQL Server 2005 Express edition. As anyone here who manages these servers knows, it's not very easy to configure a backup from these servers as there's no Maintenance plans available [someone please correct me if I'm wrong, obviously]. But I'm wondering, would it be safe to backup these databases by simply backing up the %PROGRAMFILES%\Microsoft SQL Server\MSSQL.1 folder containing the Data folder [where it appears the databases are kept]? Since NTBackup uses shadow copies I don't think I'd need to stop the service. I can if neccessary via a script that runs just before the backup does, and then restart it afterwords with another script [or script a stop service>backup>start service all in one script]. But back to the main point Would this work? I've transferred databases from that folder before to another server and had it work perfectly fine.

 

Thanks!

Edited August 19, 2011 by link470

[localzuk]

It wouldn't back up the LDF or MDF files without stopping the service - what is a better idea is scripting MSSQL to run a backup routine before your NTBackup runs and then backup the resulting .bak files instead.

 

You really should run the internal backup routines in MSSQL in order to truncate your LDF files, else they'll grow bigger and bigger.

 

At least, that's my take on it from my experiences.

[link470]

Thanks for your reply localzuk, do you know how I can do this in Microsoft SQL 2005 Express Management Studio? I've never found a good way to schedule almost anything. If you have an example of a script and how I can schedule it, that would be greatly appreciated!

 

Thanks again.

[localzuk]

I don't backup using scripts any more - its all done using BackupAssist, however you can do it via a Windows scheduled task, and an sql script

 

eg.

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -i"c:\path\to\sqlbackupScript.sql" as the command and there's a simple backup script here -

Simple script to backup all SQL Server databases

[link470]

I'll give that a try! Thanks a lot.

 

::EDIT::

 

This method appears to work great. Thanks again!

Edited August 20, 2011 by link470

[elsiegee40]

Oh, what a well timed post! I shall be looking at this too