Sequel 2005
Is there a way to automate backing-up a sequel 2005 database? In Sequel 2005, in the server management studio, if I right click the database and go to tasks, I can choose backup& I have it set to backup to a file on the hard drive, and it seems to work fine. I was trying to avoid paying for more BackUp Exec agents. I was hoping this is possible, because when I poked around in the server management studio it looked like there was a place to write scripts. Unfortunately, I know about nothing when it comes to writing scripts. So if this is relatively straight forward maybe I can do this, however if you have to be a scripting god to accomplish this, I guess I have to pry open the wallet.
Swindle
You can backup using the method you described, but the question I have with your method (file on disk) is what happens if the drive fails? You will have lost both your server and the backups. BackupExec is a great piece of software in my opinion. I know it's expensive, but there's a reason that it seems to be an industry standard piece of software. If by a stroke of luck, you are a non-profit, educational, or government entity you can get it dirt cheap through techsoup.org.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Stay away from 3rd party products to call the actual backup process. Microsoft doesn't support them. They are fine for backing up the resulting files :)
Use the maintenance plans as Crag says.
What edition of SQL? Express, Workgroup, Standard or Enterprise?
Use the maintenance plans as Crag says.
What edition of SQL? Express, Workgroup, Standard or Enterprise?
ASKER
I backup to disk because I found a thing called AutoIt, which is the easy way to script tasks. Knowing next to nothing about scripting, AutoIt is right up my alley, and I use it to copy the backup that Sequel creates to a secured network share. Then the network share is backed-up using Backup Exec. I dont use AutoIt to automatic the Sequel database backup because I havent found a way around the necessity to be logged in for the AutoIt script to work.
Backup Exec is nice software, but lately it seems like pulling teeth to get a hold of someone. I even have a support contract which I thought would get me to tech support quicker, but usually when I have to call for support, I wait at least 30 minutes. Once I talk to someone, the support seems pretty good, but Im getting tired of waiting.
Backup Exec is nice software, but lately it seems like pulling teeth to get a hold of someone. I even have a support contract which I thought would get me to tech support quicker, but usually when I have to call for support, I wait at least 30 minutes. Once I talk to someone, the support seems pretty good, but Im getting tired of waiting.
If you can't get the Maintenance Plan piece to work - its the best way.
Or you are using the Express edition which doesn't have them.
you can use the following command from a command line or AutoIt to backup the database:
sqlcmd -s <system1> -E -Q "backup database <database> to disk = 'C:\Temp\<database>_backup
You need to run this command using a Windows Account that has db_backupoperator access to the database.
Or you are using the Express edition which doesn't have them.
you can use the following command from a command line or AutoIt to backup the database:
sqlcmd -s <system1> -E -Q "backup database <database> to disk = 'C:\Temp\<database>_backup
You need to run this command using a Windows Account that has db_backupoperator access to the database.
ASKER
So may responses, so quick, Im over-whelmed. Thanks for the quick responses.
I clicked on Help, and click about, but I doesnt say if its Express, Workgroup, Standard or Enterprise? It just shows the version of a bunch of things.
Microsoft SQL Server Management Studio 9.00.1399
Microsoft Analysis Services Client tools 2005.090.139
MDAC 2000.086.395
Microsoft MSXML 2.6 3.0 4.0 6.0
Dont know if this is helpful, but thats what it shows.
I was going to try the Maintenance Plan thing, but my databases dont show in the list of databases, so I thought I should choose to back up all user databases ( excluding master, model, msdb, tempdb)
I clicked on Help, and click about, but I doesnt say if its Express, Workgroup, Standard or Enterprise? It just shows the version of a bunch of things.
Microsoft SQL Server Management Studio 9.00.1399
Microsoft Analysis Services Client tools 2005.090.139
MDAC 2000.086.395
Microsoft MSXML 2.6 3.0 4.0 6.0
Dont know if this is helpful, but thats what it shows.
I was going to try the Maintenance Plan thing, but my databases dont show in the list of databases, so I thought I should choose to back up all user databases ( excluding master, model, msdb, tempdb)
If your databases don't show in the list then you need to investigate why first before you set up the plan.
If it's not there the job won't see it either.
What level access do you have on SQL Server? SysAdmin or lower?
If it's not there the job won't see it either.
What level access do you have on SQL Server? SysAdmin or lower?
ASKER
I'm not sure how to tell, is there a whoami like command to run?
In the security section under logins, can you see you login or your windows account?
If not can you see a windows group that your account is a member of?
If so check the server role properties of the login.
If not can you see a windows group that your account is a member of?
If so check the server role properties of the login.
ASKER
I opened the Server Management Studio, right clicked the server, went to properties, and click the security icon. It shows that this is SQL server standard edition. The authentication method is SQL Server Authentication. I hate to say this now because Im guessing it might matter, but the database Im trying to schedule to backup was a SQL 7 database that I imported into this server. Sorry if that was an important bit of info. I know next to nothing about SQL. I was able to import the SQL 7 database with lots of help.
That's probably why it hasn't shown up in the list of databases. If you know your app supports SQL 2005 you can change the database from SQL 7 compatability to 2005.
Right click on the database and select properties.
Selection Options from the database properties and the third item down is Compatability Level.
Set this to SQL Server 2005 and click OK
It should now show in the list of databases in the maint plan
Right click on the database and select properties.
Selection Options from the database properties and the third item down is Compatability Level.
Set this to SQL Server 2005 and click OK
It should now show in the list of databases in the maint plan
ASKER
As I read the responses, I started getting the impression that I should have mentioned that the database was from a sequel 7 server. Sorry about that.