Showing posts with label analyzer. Show all posts
Showing posts with label analyzer. Show all posts

Sunday, March 25, 2012

Database backup?

Hi,
Two questions, in the query analyzer I typed this:
backup database hcn to disk 'D:\Microsoft SQL
Server\MSSQL\BACKUP\mybackup01232006.bak'
And I got a syntax error: Line 1: Incorrect syntax near 'D:\Microsoft SQL
Server\MSSQL\BACKUP\hcnprod01232006.bak'.
I also tried it without the 'disk' parameter as it looked in the online help
but got the same message. What did I do wrong?
Secondly, how can I direct this backup to another server? Using a share on
a different server with similar results.
I am able to perform backups using the enterprise manager to the location in
my first question but not to a network share using the UNC:
\\server\share\directory\subdirectory\fi
le.bck
Any help is greatly appreciated,
LinnYou're missing an =
backup database hcn to disk = 'D:\Microsoft SQL
Server\MSSQL\BACKUP\mybackup01232006.bak'
HTH. Ryan
"Linn Kubler" <lkubler@.chartwellwisc2.com> wrote in message
news:ePm17cCIGHA.2928@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Two questions, in the query analyzer I typed this:
> backup database hcn to disk 'D:\Microsoft SQL
> Server\MSSQL\BACKUP\mybackup01232006.bak'
> And I got a syntax error: Line 1: Incorrect syntax near 'D:\Microsoft SQL
> Server\MSSQL\BACKUP\hcnprod01232006.bak'.
> I also tried it without the 'disk' parameter as it looked in the online
> help but got the same message. What did I do wrong?
> Secondly, how can I direct this backup to another server? Using a share
> on a different server with similar results.
> I am able to perform backups using the enterprise manager to the location
> in my first question but not to a network share using the UNC:
> \\server\share\directory\subdirectory\fi
le.bck
> Any help is greatly appreciated,
> Linn
>|||Ah, I see, thanks Ryan. It's always simple once you see it. How about the
other question, any idea what I'm doing wrong there? Isn't it possible to
backup to a remote server?
Thanks,
Linn
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uPeAbgCIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> You're missing an =
>
> backup database hcn to disk = 'D:\Microsoft SQL
> Server\MSSQL\BACKUP\mybackup01232006.bak'
> --
> HTH. Ryan
> "Linn Kubler" <lkubler@.chartwellwisc2.com> wrote in message
> news:ePm17cCIGHA.2928@.TK2MSFTNGP10.phx.gbl...
>|||What is the account being used for running the services for SQL Server? Is i
t
a LocalSystem account? If so, it probably doesn't have access to the remote
location you have specified. Changing to a domain account that has access to
that location might solve the problem, but there could be security concerns
that you should read up on.
HTH
"Linn Kubler" wrote:

> Ah, I see, thanks Ryan. It's always simple once you see it. How about th
e
> other question, any idea what I'm doing wrong there? Isn't it possible to
> backup to a remote server?
> Thanks,
> Linn
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:uPeAbgCIGHA.2064@.TK2MSFTNGP09.phx.gbl...
>
>|||I'm using the sa account and I see what you mean. How does the sa account
have rights to save files to the local directories? I can't locate it
anywhere outside of SQL. How does that work?
Thanks,
Linn
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:354CE2D0-6B8F-4AC3-A747-93B0A800DBD9@.microsoft.com...
> What is the account being used for running the services for SQL Server? Is
> it
> a LocalSystem account? If so, it probably doesn't have access to the
> remote
> location you have specified. Changing to a domain account that has access
> to
> that location might solve the problem, but there could be security
> concerns
> that you should read up on.
> HTH
> "Linn Kubler" wrote:
>|||Open up the Services (Start-ControlPanel-AdminTools-Services) and look for
the Sql server services. Check what account they are running under (Log on
as). If they are logging on as LocalSystem, that might be causing the
problem. Note that you must NOT change the service account from the Services
directly. Go to Enterprise manager (assuming you are using Sql 2k), select
the server and then, the properties. You will find a way to change it there
(I don't have access to a server right now, so cannot give you the exact tab
to look at, but it should be fairly easy to find. Change it there (again,
make sure you read up about the security concerns before you do that)
"Linn Kubler" wrote:

> I'm using the sa account and I see what you mean. How does the sa account
> have rights to save files to the local directories? I can't locate it
> anywhere outside of SQL. How does that work?
> Thanks,
> Linn
> "Bob" <Bob@.discussions.microsoft.com> wrote in message
> news:354CE2D0-6B8F-4AC3-A747-93B0A800DBD9@.microsoft.com...
>
>|||On the server properties, select the "Security" Tab and at the bottom of the
tab, you will notice "Startup service account" - change it from "system
account" to "This account" and put in your domain ID/pwd that has the
necessary access. If I'm not mistaken, it will require you to restart the sq
l
server service.
"Bob" wrote:

> Open up the Services (Start-ControlPanel-AdminTools-Services) and look for
> the Sql server services. Check what account they are running under (Log on
> as). If they are logging on as LocalSystem, that might be causing the
> problem. Note that you must NOT change the service account from the Servic
es
> directly. Go to Enterprise manager (assuming you are using Sql 2k), select
> the server and then, the properties. You will find a way to change it ther
e
> (I don't have access to a server right now, so cannot give you the exact t
ab
> to look at, but it should be fairly easy to find. Change it there (again,
> make sure you read up about the security concerns before you do that)
>

Database Backup using Query analyzer

Hi there,

How can I automate may database backup using query analyser?
My routine goes this way:
1. Every 5:00pm check Enterprise manager who is currenlty logged on to sql server.
2. If there are no currently logged on, edit the date sql script (back-up, both full and incremental). Be sure there are folders created at the main server.
3. Open Query analyser: log as backup operator
4. Open the SQL File, >Run>Close

This routine will take me 15 to 30 minutes a day because of the large file size.

Is there an easier way?I would recommend using SQL Agent for scheduling the job. You may need to make your query more robust depending on the reason you check for users in the database. If you are implying that you only perform a backup if there are no users connected, then your procedure would need to loop until that condition occurs -- SQL Server can successfully backup a database with active connections, so I'm not sure why you check for that.

Friday, February 24, 2012

Data Using DTS

Hi I have a query and when I run it in SQL query analyzer and select "results to file option I get the nice header of the column in included in the file that it generates. My problem is when I want to do this in a DTS job, the header does not align as nicely. For example see below...
Query run in Query analyzer and select results in file option I get this header...
Employee ID Social Security Number Birth Date
---------------
111111111 123456789 01/01/2000
(not how it is aligned)...
Result from DTS...
"Employee ID","Social Security Number","Birth Date"
"111111111","123456789","01/01/2000"
How can I output as the query analyzer using DTS?
Thanks.

In the Export Data wizard, change the "Delimited" option to "Fixed Width".