June 26, 2012 at 9:51 am
I have never used SQL server before but yesterday my boss has given me the task of learning it. First thing on the priority list is getting backups scheduled for all the databases on this particular server. It is a windows 2008 r2 server running sql server 2008 r2. I see how I can go in and run a manual backup by right clicking on the database and going to tasks/backup. However I need to be able to get backups scheduled and don't seem to see a way to do it. I have been trying to read up on the subject but all i've seen by doing a google search was something about going to management and creating a maintenance plan. However I do not see this under the management section. It seems from I'm reading is that I need to be in the sysadmin database role. I'm confused on how to do this exactly. I'm simply using the local admin account and windows authentication. I should add that I was not the one that installed or setup any of this software on the server. I'm simply thrust into the mix and need to figure it all out. I'm a bit overwhelmed right now. I know there is a tool called sql backup pro but I'm guessing this is a separate tool that is not free. Is there a way to accomplish this simply through the server management studio or do we need to purchase a separate tool in order to do this?
Thanks in advance for any assistance you can provide.
I'm feeling a bit overwhelmed at the moment!
:w00t:
June 26, 2012 at 10:13 am
Try the link below to start with. There's more you can do but this intro will get you started. Have fun!
June 26, 2012 at 10:17 am
Well, the first thing you want to find out is if this is Express Edition or not. If it's Express, you won't have the option to create a Maintenance Plan, and will have to look at how to create a backup from the command line using SQLCMD.
Quickest way to find the version, including if it's Express, is to connect to the SQL Server, click on the "New Query" button in the toolbar, and key in "Select @@version" (no quotes!) and hit either "!Execute" in the toolbar, or hit the F5 key.
The results will tell you if it's Express or not, and will look something like this:
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation [highlight=#ffff11]{Version goes here}[/highlight] Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
You will need to expand the column to see it all.
Thus far, based on what you've said, I think you likely have Express Edition, though, which means you're going to need to look into a DOS batch file or PowerShell script to backup the DBs. If it's not Express, and somehow doesn't have the Maintenance Plan option, you may have bigger problems...
Jason
(A still sort of newbie accidental DBA finally trying to give back)
June 26, 2012 at 10:21 am
Jasona is right. And to add a bit more, if you're not using the Express version, you do need to be a member of the sysadmin role.
June 26, 2012 at 10:45 am
Thanks for the reply.
Here is what mine say's it is. Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Desktop Engine on Windows NT 6.1 (Build 7600: )
I don't see anything in it regarding it being express.
So if that is the case and it's not express then how do I get the local admin into the sysadmin role? Anything I've seen online has only confused me 😉
Edit: Looking at that Microsoft SQL server 2000 is that telling me it's not server 2008 r2? If I click on hep/about in the server management studio it say's it's sql server 2008 r2.
June 26, 2012 at 10:49 am
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Desktop Engine on Windows NT 6.1 (Build 7600: )
To quote Adam Savage: "Well there's your problem!"
I don't have an SQL 2000 to play with, but I don't believe it has Maintenance Plans...
It's been quite a few years since I worked with it...
Hopefully someone else will be able to tell you how to handle this.
Although, are you sure this is the right server? You see the DBs you need to back up in Management Studio when you're connected to it?
Jason
June 26, 2012 at 10:56 am
It sounds like your local machine is running SQL 2008 R2 whereas the database server that you're connecting to is running SQL 2000.
Not too sure if you can use SQL 2008 management studio to create a maintenance plan for SQL 2000 but my guess would be no. You can try it though. Here's how you can do it using SQL 2000 Enterprise Manager.
As far as permissions, your SA or someone who's already in the sysadmin group will have to change your role.
June 26, 2012 at 11:29 am
Whelp as if I wasn't already confused enough. I talked to my boss and he said that this server is running express. However it seems to be a 2000 db imported into the 2008 express addition. If that makes any sense to any of you.
So I guess I'm looking at having to go about writing scripts to get these DB's scheduled and backing up? Can anyone point me in the right direction with maybe a powershell script for this?
June 26, 2012 at 11:48 am
gciszewski (6/26/2012)
Thanks for the reply.Here is what mine say's it is. Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Desktop Engine on Windows NT 6.1 (Build 7600: )
I don't see anything in it regarding it being express.
So if that is the case and it's not express then how do I get the local admin into the sysadmin role? Anything I've seen online has only confused me 😉
Edit: Looking at that Microsoft SQL server 2000 is that telling me it's not server 2008 r2? If I click on hep/about in the server management studio it say's it's sql server 2008 r2.
Actually, this MSDE. You could say this is the Express Edition of MS SQL Server 2000.
What did you use to run the query that returned this info and are you sure you attached to the correct server?
June 26, 2012 at 12:02 pm
Yes I am rdp'd into the server that has the sql server installed on it and has the databases.
I attached some screen screen shots of what I'm seeing.
June 26, 2012 at 12:06 pm
The named instance you connected to is SQL Server 2000. You are Using SQL Server 2008 R2 SSMS to connect. Are there other SQL Servers? Were you only assigned the that one server to work on?
June 27, 2012 at 2:34 am
I am an accidental DBA as well but I have been investigating the topic for a little while. Lets stick to simplictity and you can improve at a later stage. There are many articles on this site to progress to once you have some backups.
The simplist option is a scheduled DOS batch file and a SQL script file. The batch file would run the following command (change the bold text to your details):
sqlcmd -S sqlserver\instance -E -i c:\pathtoscript\scriptfile.sql
Make sure the batch file is ran as Administrator (assuming the administrator is also an SQL admin)
The script file (in its simplist form) would contain the following:
backup database databasename to disk='x:\folder\backupfile.bak'
go
(please can others correct me if I have incorrect code)
Make sure the location is on a different location/server. No point backing up to the same hard drive.
The above will give you a full backup of the database. You can worry later if you have Full Recovery Model meaning you can perform transaction log backups but considering your SQL is 2000 DE you will have a database size limit of 1Gb. Not much scope for expansion.
You will need to look at migrating to a newer version of SQL once you are comfortable that the backups (and restores) are covered.
June 28, 2012 at 8:51 am
OK,
Yes this is the only server right now I was asked to manage.
I guess maybe I'm not understanding how this works.
Are the databases that I'm connecting to actually on the windows 2008 server that has sql server 2008 r2 installed on?
June 28, 2012 at 8:58 am
gciszewski (6/28/2012)
Are the databases that I'm connecting to actually on the windows 2008 server that has sql server 2008 r2 installed on?
No way to tell what the Windows OS is on the server you are trying to manage.
The version of SQL installed on said server is SQL Server 2000 Desktop Edition, not 2008 (as the Select @@version said)
Now, the PC you are working directly on has the 2008 Management Studio installed, but this has little bearing on the version of SQL on the other end.
June 28, 2012 at 9:43 am
OK I'm such a nub at this I'm not even sure how to write the script file within sql management studio.
Also I've seen some posts regarding use T-SQL. Would this be a good way to go? Although I'm not even sure how to launch T-SQL.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply