Is it possible to read/write a file at privilege?

hello.
I saw some systems which were hacked by sql injection tool
And some files of the systems were changed. I guess the tool tried to
read/write files.
howerver, the user privilege is not 'sa'. Is it possible for user who
is not 'sa' to read/write files?
If it is possible, how can I prevent the tools from reading/writing
files even if my web page is injectable?dodol (Dolka1@.gmail.com) writes:
> I saw some systems which were hacked by sql injection tool
> And some files of the systems were changed. I guess the tool tried to
> read/write files.
> howerver, the user privilege is not 'sa'. Is it possible for user who
> is not 'sa' to read/write files?
It could be another user with sysadmin rights. Or execution rights might
have been granted on xp_cmdshell or sp_OAxxx.

> If it is possible, how can I prevent the tools from reading/writing
> files even if my web page is injectable?
Make sure that xp_cmdshell and the sp_OAxxx procedures are disabled.
Make sure that SQL Server runs on a domain account that has no extra
privileges. The less welcome it is in the rest of the network the better.
But the main line of defence is of course to use stored procedure or
parameterised statements and never interpolate incoming stuff into
query strings.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

is it possible to put the raiseerror in a text file or show it to the user after its been

I am in process of building a website where the user can upload files and then those files are loaded in to a sql server database. I am using some sprocs to scrub the data and then insert them into the production database.. And in my sprpc before and after updating or inserting a record or scrubing... i am returning the count by raising an error. or returning the rownumber where the error occured.. is there any way i can get the raise error part or whatever error i get while scrubing the data and relay it back to the user in a user freindly way or in a text file... or the best thing is can i open smalll window where i can show them what processing is goin on and alert them if there are any errors...

Any help will be appreciated.

Regards

Karen

Take a look forSqlException.Errors. Any SqlError object has the number of error and another informations.

PS.: To another databases, take a look forOleDbException.Errors.

|||

Hi Karenros,

Based on my understanding, you want to use Raiserror to generate an error message and pass it to the client user. Client user may get an alerting message or write it to a text file when getting the error message. If I've misunderstood you ,please feel free to tell me, thanks.

You can put your sqlcommand in a try block and in your catch block, write sqlconnection.errors.message to a text file. Please remember do not assign the severity value of your error message more than 19, or else it maybe cause terminate your connection. Sample code is like the following:

 try { con.Open(); cmd.ExecuteNonQuery(); }catch (SqlException ex) {using(StreamWriter sw=new StreamWriter("your text log file path here")) {foreach (SqlError errin ex.Errors) sw.WriteLine(err.Message+"\n"); } }

Hope my suggestion helps

|||

Chen,

Thanks for your answer. yeah and thats exactly that i wanted to do... so that user would know if the import process was successful or not...

I have tried using sqlexception before with no luck.. may be i didnt import the right header files in order for that work and i have also seen on msdn that we need a sqlinfomessage class or something like to do it.. Pls correct me if i am wrong...

anyways i am gonna give it a try and will let you know...

Regards

Karen

|||

Below is an example of how you can use the InfoMessage event handler:

First you'll have to create an event handler for this event like below:

con.Open();
con.InfoMessage +=new SqlInfoMessageEventHandler(con_InfoMessage);// here i've registered for the event
... set up the command object
cmd.NotificationAutoEnlist =true;
cmd.ExecuteNonQuery();

Then you can go on and write any code you want in that event handler method.

private void con_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
// your file writing code goes here. The eventArgs e holds errors, messages, source etc.
 // you can just use e.ToString() and everything is there for you.
}

Hope this will help.

|||

Hi Karenros,

I've tested the sqlexception code on my local machine and it does work fine. So, maybe you have made some mistakes somewhere else.

However, I think you can also trydhimant 's solution. That's really a good method to solve your problem. thanks

Is it possible to perform terms lookup on unstructured files ?

Hi,
I need to categorize a lot of html or text files according to a list of terms and I wonder if terms lookup is adequate for this. The problem is that terms lookup can only take an Oledb source as input. My files can be up to 80 Kb big and aren't columns structured.

Should I import my files in a table ? But if so, how can I import a column with more than 8000 characters ?

Thank you in advance.

I think you may have this the wrong way around. The list of terms must be stored in an OLE-DB sourced table, but the input is the data you want to examine. This can come from any upstream component. You will still need to get your data into the pipeline, but that is perhaps not quite as hard as OLE-DB. Maybe the Import Column Transform could help?

You mention a 8000 character limit, which is the limit for non-unicode strings in the varchar (T-SQL) or DT_STR (SSIS) data types. Whilst the Term transformations only support unicode data types, with their 4000 character limit, they do support the DT_NTEXT type, equivalent to the T-SQL ntext type, which allows up to 2GB of data.

|||Thank you very much for your quick reply. My mistake, you're right, I'm a new user of SSIS and I misunderstood the explanations on the lookup. I'm digging into this. Thanks again for your help.

Is it possible to ftp files using code in a SSIS Script Task?

Is it possible to ftp files using code in a Script Task? I need to read the contents of an xml file and if it has a a specific file name in there then I ftp the corresponding pdf file which is at the same location as the xml file. However I cannot do this using the provided FTP Task in SSIS, I would need to use code to do this as there are close to 50 xml files which I need to read and upload the corresponding pdf file file it meets a certain criteria.

I do not see a way of looping thru all the files in a folder unless I do this in a Script task. Any inputs or alternative comments on doing this will be appreciated.

Thanks,

MShah

In the Control Flow you should be able to use a ForEach loop to loop over all the files. Then you can use a script task (XML task might also work but not sure) to extract the information needed to create the path & filename to FTP.

You would store the create filename in a Variable and then do the FTP task in the loop to upload the files. Anyone know if the FTP task will connect to FTP once in this type of loop?

Fred

sql

Is it necessary to exclude the \data folder when using NetBackup?

I am not a big fan of paying 3rd party backup vendors for
agents, but I need to make sure that we are not risking
corrupting the database files. I have setup maintenance
plans to do the fulls and log backups to disk. We are just
beginning to roll out NetBackup 5.0 in production, and
without the SQL agent installed, it appears as though it
can backup all the data files without error. Should I
exclude the "hot files" in NetBackup, and rely on the bak
and trn files if I need to restore the whole server?
Meaning, if my prod server died, and I needed to restore
from tape to a hotspare, is it possible to restore the
database(s) if the mssql\data\*.mdf and ldf files are not
restored?
If this is a lame question, I apologize, as I am not a
DBA, but a storage guy, and we have no official SQL DBA's
in house yet.
Thanks for any and all help!
DaveYes, you want to avoid trying to back up the mdf and ldf files and instead
just back up the native SQL backup files (trn/bak) to tape. You would use
these to restore the database. BOL has plenty of detail on backup and
restore in the Administering SQL Server>Backing Up and Restoring Databases
section
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dave" <anonymous@.discussions.microsoft.com> wrote in message
news:288a01c3fc84$cf4b5850$a101280a@.phx.gbl...
> I am not a big fan of paying 3rd party backup vendors for
> agents, but I need to make sure that we are not risking
> corrupting the database files. I have setup maintenance
> plans to do the fulls and log backups to disk. We are just
> beginning to roll out NetBackup 5.0 in production, and
> without the SQL agent installed, it appears as though it
> can backup all the data files without error. Should I
> exclude the "hot files" in NetBackup, and rely on the bak
> and trn files if I need to restore the whole server?
> Meaning, if my prod server died, and I needed to restore
> from tape to a hotspare, is it possible to restore the
> database(s) if the mssql\data\*.mdf and ldf files are not
> restored?
> If this is a lame question, I apologize, as I am not a
> DBA, but a storage guy, and we have no official SQL DBA's
> in house yet.
> Thanks for any and all help!
> Dave
>
>|||Yes, I was planning on keeping the maintenance plan. I was
not sure if it is possible to restore the trn\bak files if
real SQL server datafiles were not restored (ie excluding
the \data\*.* in NetBackup policy). Is there some command
line you can run to start SQL enough to restore the files?
I've looked in the online help, but I must be missing the
obvious. What is BOL?
thanks!

>--Original Message--
>Yes, you want to avoid trying to back up the mdf and ldf
files and instead
>just back up the native SQL backup files (trn/bak) to
tape. You would use
>these to restore the database. BOL has plenty of detail
on backup and
>restore in the Administering SQL Server>Backing Up and
Restoring Databases
>section
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>"Dave" <anonymous@.discussions.microsoft.com> wrote in
message
>news:288a01c3fc84$cf4b5850$a101280a@.phx.gbl...
for
just
bak
not
DBA's
>
>.
>|||> Is there some command
> line you can run to start SQL enough to restore the files?
?
When you do a RESTORE in SQL Server, the database is created, if it doesn't
exists. If a system database is broken, you need to use REBUILDM.EXE to
create new system databases so that you can start SQL Server and then do the
restore. If the whole installation is toast, you need to install SQL Server
first instead. :-)
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Dave" <anonymous@.discussions.microsoft.com> wrote in message
news:2ad501c3fcb0$61c267c0$a001280a@.phx.gbl...
> Yes, I was planning on keeping the maintenance plan. I was
> not sure if it is possible to restore the trn\bak files if
> real SQL server datafiles were not restored (ie excluding
> the \data\*.* in NetBackup policy). Is there some command
> line you can run to start SQL enough to restore the files?
> I've looked in the online help, but I must be missing the
> obvious. What is BOL?
> thanks!
>
> files and instead
> tape. You would use
> on backup and
> Restoring Databases
> message
> for
> just
> bak
> not
> DBA's

Is it necessary to exclude the \data folder when using NetBackup?

I am not a big fan of paying 3rd party backup vendors for
agents, but I need to make sure that we are not risking
corrupting the database files. I have setup maintenance
plans to do the fulls and log backups to disk. We are just
beginning to roll out NetBackup 5.0 in production, and
without the SQL agent installed, it appears as though it
can backup all the data files without error. Should I
exclude the "hot files" in NetBackup, and rely on the bak
and trn files if I need to restore the whole server?
Meaning, if my prod server died, and I needed to restore
from tape to a hotspare, is it possible to restore the
database(s) if the mssql\data\*.mdf and ldf files are not
restored?
If this is a lame question, I apologize, as I am not a
DBA, but a storage guy, and we have no official SQL DBA's
in house yet.
Thanks for any and all help!
DaveYou must do sql backups using maintenance plan or using
SQL Agent. Make sure to backup bak and trn files on tape
if you use maintenance plan. You should not rely on "hot
files" backup in NebBackup because it doesn't always work
in case of sql files. SQL backups (through a maintenance
plan or NetBackup sql agent) is the only reliable way to
restore sql databases.
I would advise against doing backup of \data folder,
specially if backup window is limited.
hth.
>--Original Message--
>I am not a big fan of paying 3rd party backup vendors for
>agents, but I need to make sure that we are not risking
>corrupting the database files. I have setup maintenance
>plans to do the fulls and log backups to disk. We are
just
>beginning to roll out NetBackup 5.0 in production, and
>without the SQL agent installed, it appears as though it
>can backup all the data files without error. Should I
>exclude the "hot files" in NetBackup, and rely on the bak
>and trn files if I need to restore the whole server?
>Meaning, if my prod server died, and I needed to restore
>from tape to a hotspare, is it possible to restore the
>database(s) if the mssql\data\*.mdf and ldf files are not
>restored?
>If this is a lame question, I apologize, as I am not a
>DBA, but a storage guy, and we have no official SQL DBA's
>in house yet.
>Thanks for any and all help!
>Dave
>
>.
>|||Yes, you want to avoid trying to back up the mdf and ldf files and instead
just back up the native SQL backup files (trn/bak) to tape. You would use
these to restore the database. BOL has plenty of detail on backup and
restore in the Administering SQL Server>Backing Up and Restoring Databases
section
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dave" <anonymous@.discussions.microsoft.com> wrote in message
news:288a01c3fc84$cf4b5850$a101280a@.phx.gbl...
> I am not a big fan of paying 3rd party backup vendors for
> agents, but I need to make sure that we are not risking
> corrupting the database files. I have setup maintenance
> plans to do the fulls and log backups to disk. We are just
> beginning to roll out NetBackup 5.0 in production, and
> without the SQL agent installed, it appears as though it
> can backup all the data files without error. Should I
> exclude the "hot files" in NetBackup, and rely on the bak
> and trn files if I need to restore the whole server?
> Meaning, if my prod server died, and I needed to restore
> from tape to a hotspare, is it possible to restore the
> database(s) if the mssql\data\*.mdf and ldf files are not
> restored?
> If this is a lame question, I apologize, as I am not a
> DBA, but a storage guy, and we have no official SQL DBA's
> in house yet.
> Thanks for any and all help!
> Dave
>
>|||Yes, I was planning on keeping the maintenance plan. I was
not sure if it is possible to restore the trn\bak files if
real SQL server datafiles were not restored (ie excluding
the \data\*.* in NetBackup policy). Is there some command
line you can run to start SQL enough to restore the files?
I've looked in the online help, but I must be missing the
obvious. What is BOL?
thanks!
>--Original Message--
>Yes, you want to avoid trying to back up the mdf and ldf
files and instead
>just back up the native SQL backup files (trn/bak) to
tape. You would use
>these to restore the database. BOL has plenty of detail
on backup and
>restore in the Administering SQL Server>Backing Up and
Restoring Databases
>section
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>"Dave" <anonymous@.discussions.microsoft.com> wrote in
message
>news:288a01c3fc84$cf4b5850$a101280a@.phx.gbl...
>> I am not a big fan of paying 3rd party backup vendors
for
>> agents, but I need to make sure that we are not risking
>> corrupting the database files. I have setup maintenance
>> plans to do the fulls and log backups to disk. We are
just
>> beginning to roll out NetBackup 5.0 in production, and
>> without the SQL agent installed, it appears as though it
>> can backup all the data files without error. Should I
>> exclude the "hot files" in NetBackup, and rely on the
bak
>> and trn files if I need to restore the whole server?
>> Meaning, if my prod server died, and I needed to restore
>> from tape to a hotspare, is it possible to restore the
>> database(s) if the mssql\data\*.mdf and ldf files are
not
>> restored?
>> If this is a lame question, I apologize, as I am not a
>> DBA, but a storage guy, and we have no official SQL
DBA's
>> in house yet.
>> Thanks for any and all help!
>> Dave
>>
>
>.
>|||> Is there some command
> line you can run to start SQL enough to restore the files?
?
When you do a RESTORE in SQL Server, the database is created, if it doesn't
exists. If a system database is broken, you need to use REBUILDM.EXE to
create new system databases so that you can start SQL Server and then do the
restore. If the whole installation is toast, you need to install SQL Server
first instead. :-)
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dave" <anonymous@.discussions.microsoft.com> wrote in message
news:2ad501c3fcb0$61c267c0$a001280a@.phx.gbl...
> Yes, I was planning on keeping the maintenance plan. I was
> not sure if it is possible to restore the trn\bak files if
> real SQL server datafiles were not restored (ie excluding
> the \data\*.* in NetBackup policy). Is there some command
> line you can run to start SQL enough to restore the files?
> I've looked in the online help, but I must be missing the
> obvious. What is BOL?
> thanks!
> >--Original Message--
> >Yes, you want to avoid trying to back up the mdf and ldf
> files and instead
> >just back up the native SQL backup files (trn/bak) to
> tape. You would use
> >these to restore the database. BOL has plenty of detail
> on backup and
> >restore in the Administering SQL Server>Backing Up and
> Restoring Databases
> >section
> >
> >--
> >HTH
> >
> >Jasper Smith (SQL Server MVP)
> >
> >I support PASS - the definitive, global
> >community for SQL Server professionals -
> >http://www.sqlpass.org
> >
> >
> >"Dave" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:288a01c3fc84$cf4b5850$a101280a@.phx.gbl...
> >> I am not a big fan of paying 3rd party backup vendors
> for
> >> agents, but I need to make sure that we are not risking
> >> corrupting the database files. I have setup maintenance
> >> plans to do the fulls and log backups to disk. We are
> just
> >> beginning to roll out NetBackup 5.0 in production, and
> >> without the SQL agent installed, it appears as though it
> >> can backup all the data files without error. Should I
> >> exclude the "hot files" in NetBackup, and rely on the
> bak
> >> and trn files if I need to restore the whole server?
> >> Meaning, if my prod server died, and I needed to restore
> >> from tape to a hotspare, is it possible to restore the
> >> database(s) if the mssql\data\*.mdf and ldf files are
> not
> >> restored?
> >> If this is a lame question, I apologize, as I am not a
> >> DBA, but a storage guy, and we have no official SQL
> DBA's
> >> in house yet.
> >> Thanks for any and all help!
> >>
> >> Dave
> >>
> >>
> >>
> >
> >
> >.
> >

is it good idea to replicate sql server db files?

Hi.

I am wondering if it is a good idea to replicate sql server db files
using frs.

I don't really know how the frs works, so
does frs replicates the whole database from time to time or just the
portion that is changed?

Also if the db is expected to change very often, and wouldn't it make
the whole system down?

I wonder if it's a good idea just to make a backup of the database and
copy it.

What's the usual practice?[posted and mailed, please reply in news]

jaekim (jkim65@.socal.rr.com) writes:
> I am wondering if it is a good idea to replicate sql server db files
> using frs.
> I don't really know how the frs works, so
> does frs replicates the whole database from time to time or just the
> portion that is changed?
> Also if the db is expected to change very often, and wouldn't it make
> the whole system down?
> I wonder if it's a good idea just to make a backup of the database and
> copy it.
> What's the usual practice?

I'm uncertain of what your question actually is, and whatever I have never
heard of frs.

You talk about replication, but your question seems to be about backup.
Replication and backup are two quite different things.

To backup a database, you use the BACKUP command in T-SQL. There are three
ways to back up a database:

* Full backup, backup the entire database.
* Differential backup, back up the changes since the last full backup.
* Log backup, backs up the *transaction log*.

Normally you use both Full backup and Log backup. By backing up the
transaction log, you can get up-to-the-minute recovery in case of a
crash (which could be a fatal human error).

To be able to backup the transaction log you must run in Full or Bulk-logged
recovery mode. On the other hand, if you run in these modes, you must
backup the transaction log, or the log will eventually fill your disk.

It is important to understand that SQL's BACKUP command knows about
transactions, and thus you can backup the database while there is
activity in it. If you would just copy the database files outside SQL
Server, you might get a useless set of bytes on the tape.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"jaekim" <jkim65@.socal.rr.com> wrote in message
news:91d0d16b.0407040210.7116f55d@.posting.google.c om...
> Hi.
> I am wondering if it is a good idea to replicate sql server db files
> using frs.
> I don't really know how the frs works, so
> does frs replicates the whole database from time to time or just the
> portion that is changed?
> Also if the db is expected to change very often, and wouldn't it make
> the whole system down?
> I wonder if it's a good idea just to make a backup of the database and
> copy it.

I would NOT trust FRS to replicate my database.

Either as Erland suggests use BACKUP and RESTORE (look up log-shipping) or
use SQL Server's replication.

> What's the usual practice?|||On Sun, 4 Jul 2004 12:51:28 +0000 (UTC), Erland Sommarskog wrote:

> I'm uncertain of what your question actually is, and whatever I have never
> heard of frs.
> You talk about replication, but your question seems to be about backup.
> Replication and backup are two quite different things.
FRS refers to File Replication Service, provided by Windows 2000 Server and
Windows Server 2003. It basically does the same thing as rsync: copy file
changes from one server to another on a scheduled basis.

Some details:
http://www.microsoft.com/windows200...dh_frs_ncpi.asp

Your recommendations are of course correct: Database replication is best
handled by the database server, not the filesystem.

Is it a good to replace SQL script files with XML files?

I am thinking about replacing the INSERT data script
files that I have with XML files. This way I can open the XML
file using an XML Editor and see the values in a GRID and
make changes easier.

Do you see any problem with this approach?

I managed to put together some code that is exporting
a SQL table with its data to an XML file and also a code
that reads the XML file's data and inserts it into a table.

Now I am researching on XSD, td:datatype, DTD...
(I am new to XML) in order to figure out how I can
use a single xml file that will hold both the sql server
fields, the datatypes and their values.

If you have links to some sample code that has anything
to do with the datatype export and import I am working
on, can you please share them with me?

Most importantly what do you think about the idea of using
XML files vs sql scripts?

Thank youserge (sergea@.nospam.ehmail.com) writes:
> I am thinking about replacing the INSERT data script
> files that I have with XML files. This way I can open the XML
> file using an XML Editor and see the values in a GRID and
> make changes easier.
> Do you see any problem with this approach?

I know too little XML to say that whether this is good or bad. I didn't
know that there were XML Editors where you could edit grid cells.

I recognize the problem, though, because we have plenty of such files in
our shop. Our solution to the problem is Excel. (Which can be saved as
XML, but we don't do that currently.) Then we have a tool that reads the
Excel book and generates an INSERT-file from it. That file, by the way, does
not include any INSERT statements, but calls to a stored procedure that
will insert or update (or delete), so that the files easily can be rerun.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I have only started learning XML 3-4 days ago. I ran into a
newsgroup post by chance where someone was using XML
to transfer data to SQL Server.

http://visualbasic.ittoolbox.com/gr...rver-l&i=780204

So that made me wonder why I wouldn't do that?
I've been working on this since then and slowly learning more
about SELECT * FROM TABLE FOR XML AUTO, XML,
DTD, XSD files, now I need to learn XDR, I think XDR is
similar to XSD but seems to be aimed for SQL Server.
I'll post some questions on microsoft.public.xml and hopefully
I'll get some answers from people who have already done what
I am trying to do.

But one question I have is if you are using Excel, are you using
it only for the INSERT data part? What about using the same
or another Excel file to hold the table's column names and data
types?

At this point in time (with my very little knowledge of XML) I
believe it wouldn't be a good idea to replace the sql files holding
the table structures with XML files holding the equivalent in terms
of the columns and its data types. I think that is more difficult
for someone to make table changes.

Here are three links for free XML Editors.
http://www.xmlcooktop.com/

I like these two as they will show you the data in grids:

http://symbolclick.com/index.htm
http://www.xmlfox.com/download.htm

Thanks

> I know too little XML to say that whether this is good or bad. I didn't
> know that there were XML Editors where you could edit grid cells.
> I recognize the problem, though, because we have plenty of such files in
> our shop. Our solution to the problem is Excel. (Which can be saved as
> XML, but we don't do that currently.) Then we have a tool that reads the
> Excel book and generates an INSERT-file from it. That file, by the way,
> does
> not include any INSERT statements, but calls to a stored procedure that
> will insert or update (or delete), so that the files easily can be rerun.|||serge (sergea@.nospam.ehmail.com) writes:
> But one question I have is if you are using Excel, are you using
> it only for the INSERT data part? What about using the same
> or another Excel file to hold the table's column names and data
> types?

I might be misunderstanding your questions, but for that purpose a
data-modelling tool is much better in my opinion. In our shop we
use PowerDesigner from Sybase.

(Incidently, you can save the data model in XML format. But the main
point with that is if you keep the model under verison control, you
can use a standard diff tool to see the differences between two versions.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>I might be misunderstanding your questions, but for that purpose a
>data-modelling tool is much better in my opinion. In our shop we
>use PowerDesigner from Sybase.

For some reason my Outlook Express is not downloading your last
post.

I checked the demo of PowerDesigner from Sybase. Modeling tool
is something I will have to look into in the next weeks/months.

Thanks

Is it 100% safe to stop SQL Server to copy mdf/ldf files for a replicated database?

I have mainly 2 questions.
1- Other than detaching a db through SQL or backing up a db, is it 100% safe
to
stop SQL Server service and then copy the .mdf/.ndf/.ldf files? Is there any
risk
or possibility of anything going wrong this way when choosing the copy
option?
2- If the database is being replicated, what are my options to make a
backup?
I can't detach the db because SQL demands the replication to be dropped
first.
Can I stop SQL Server service and then copy the .mdf/.ndf/.ldf files?
Thank youHi
Moving the database files will leave entries in sysdatabases that reference
the old files/database. If you don't drop replication before detaching it,
then it may not work when it's attached and you will have to clean up an
inconsistent system, so it is probably better to drop first.
John
"serge" <sergea@.nospam.ehmail.com> wrote in message
news:37521BFB-16B4-498C-BEEC-F027AC55741B@.microsoft.com...
>I have mainly 2 questions.
> 1- Other than detaching a db through SQL or backing up a db, is it 100%
> safe to
> stop SQL Server service and then copy the .mdf/.ndf/.ldf files? Is there
> any risk
> or possibility of anything going wrong this way when choosing the copy
> option?
> 2- If the database is being replicated, what are my options to make a
> backup?
> I can't detach the db because SQL demands the replication to be dropped
> first.
> Can I stop SQL Server service and then copy the .mdf/.ndf/.ldf files?
> Thank you
>|||> Moving the database files will leave entries in sysdatabases that
> reference the old files/database. If you don't drop replication before
> detaching it, then it may not work when it's attached and you will have to
> clean up an inconsistent system, so it is probably better to drop first.
Thanks John, however I forgot to point out that I am not really moving
the files. I am making backup copies of the mdf/ldf files simply because
in case of restore, copying mdf/ldf and re-attaching them is much faster
than doing a restore. So physical file locations are not being changed in
this case.|||While there may be some cleverness in what you're trying to do, I'd look at
it in terms of support - PSS will give you no help if anything goes wrong
for this type of process, and at a time when you'd probably most need it -
disaster recovery. For this to be supported you'd have to look at
implementing a recognised backup strategy eg
http://msdn2.microsoft.com/en-us/library/aa237094(SQL.80).aspx
Rgds,
Paul Ibison
(www.replicationanswers.com)|||> While there may be some cleverness in what you're trying to do, I'd look
> at it in terms of support - PSS will give you no help if anything goes
> wrong for this type of process, and at a time when you'd probably most
> need it - disaster recovery. For this to be supported you'd have to look
> at implementing a recognised backup strategy eg
> http://msdn2.microsoft.com/en-us/library/aa237094(SQL.80).aspx
Thanks for the info regarding PSS support. I will also read the link
about the backup strategies.|||On Mar 26, 7:58=A0am, "serge" <ser...@.nospam.ehmail.com> wrote:
> > While there may be some cleverness in what you're trying to do, I'd look=
> > at it in terms of support - PSS will give you no help if anything goes
> > wrong for this type of process, and at a time when you'd probably most
> > need it - disaster recovery. For this to be supported you'd have to look=
> > at implementing a recognised backup strategy eg
> >http://msdn2.microsoft.com/en-us/library/aa237094(SQL.80).aspx
> Thanks for the info regarding PSS support. I will also read the link
> about the backup strategies.
try this...
CopySharp is a GUI tool for copying open/inprocess/lock files. It is
inspired by robocopy and vshadow.
CopySharp V1.0 requires .Net Framework 3.5 and VC++ 2005 Runtime.
CopySharp V1.0 requires Microsoft=AE Windows=AE Server 2003, Microsoft=AE
Windows=AE XP.
For Example:
1. Try to backup/copy your .pst file(s), while your outlook is open.
2. Try to backup/copy your .mdf/.ldf (SQL Server) files, while your
SQL Server is running.
Locate it at: http://www.amitchaudhary.com/|||<<CopySharp is a GUI tool for copying open/inprocess/lock files. >>
How do you make sure that several files are from the same point in time? A database consists of
several database files, and they of course need to be from the same point in time.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Amit" <amit.ary@.gmail.com> wrote in message
news:2036cba8-92d5-4a90-8108-2898fc1637b5@.u12g2000prd.googlegroups.com...
On Mar 26, 7:58 am, "serge" <ser...@.nospam.ehmail.com> wrote:
> > While there may be some cleverness in what you're trying to do, I'd look
> > at it in terms of support - PSS will give you no help if anything goes
> > wrong for this type of process, and at a time when you'd probably most
> > need it - disaster recovery. For this to be supported you'd have to look
> > at implementing a recognised backup strategy eg
> >http://msdn2.microsoft.com/en-us/library/aa237094(SQL.80).aspx
> Thanks for the info regarding PSS support. I will also read the link
> about the backup strategies.
try this...
CopySharp is a GUI tool for copying open/inprocess/lock files. It is
inspired by robocopy and vshadow.
CopySharp V1.0 requires .Net Framework 3.5 and VC++ 2005 Runtime.
CopySharp V1.0 requires Microsoft® Windows® Server 2003, Microsoft®
Windows® XP.
For Example:
1. Try to backup/copy your .pst file(s), while your outlook is open.
2. Try to backup/copy your .mdf/.ldf (SQL Server) files, while your
SQL Server is running.
Locate it at: http://www.amitchaudhary.com/

Is copy of database and log file enough for backup?

Hello,

i would like to copy the SQL Server Express database .mdf and .ldf files for backup. Is this ok?
Autoclose = true and recovery model = simple.

Must i detach the database before copy the 2 files or can i copy the 2 files without detach at any time? When connections are open (also remote connections).
Can i copy at any time even when transactions are active?

I would like to write a copy programm which copies the 2 files every 30 minuutes. Only 30 minutes of work could be lost.

This would be enough for me and i don't have to care for the the BACKUP and RESTORE stuff. In the past i used BACKUP and when i needed this BACKUP it did not run. Returns some error message..

Is copy ok? When is it possible? At any time or must all transactions be comitted? Must all connections (remotes too) be closed? Must the database be detached?

Is this enough to have a valid backup? Backup would be an attach of the .mdf file.

Or must i use the BACKUP and RESTORE stuff? Why?
If so, for what reason is the AUTO CLOSE property there?

Regards,

Markus

And in my opinion attach a database should be enough. ít is the users, the owners, wish to get the data stored in the database.

In the past, as i tried to use RESTORE stuff, i get an error message. From the sql server system point of view this was ok because something of the restore file did not match the STRICT criteria for restore. But i lost the data.

Therefore a attach should do it, to fullfill the wishes of the owner. To show him the data of that .mdf file. Even it this .mdf file does not meet the critierias of the current version. SQL Server should inform the owner of that, and ask if it is allowed to try to converte the file to the current format. If OK, it should do everything to save as much data as possible.

Sorry, if it sounds a bit curious, but i would like a way to do the obvious things without force the owner to take any learning effort.

Read in a blog:
http://www.sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/Default.aspx
This schould not be the case..

Markus

|||

I personaly would use the back up and restore options, this is what they are designed for. You can run these from the Management studio (Express Version) or from a raw query. If you need to schedule it you can either use the normal scheduler that is in windows or use a custom one. For one of my clients I created a windows service that copied the function on the unix cron system but on a windows machine.

The only time that I have used the attach and detach functions is when I need to move a database quickly. I have seen some people use it to install the database when the program is installed, but for this I prefer to code a solution that creates the database from scripts. Doing it this way I know that the structure and data is clean at the time of install.

|||

Thank you Glenn.

But the question was, is copy enough? And under what conditions?

In my opinion, if Sql Server Express should be a common datastore, it should be easy to backup.

Without knowing Sql Server Books online, without knowing what "scripts" are. This is stuff for a few freaks, who likes things like that. But most of the people don't like to read such stuff. Most people hate this stuff.

What is if someone use a Sql Server database (any older version) and want to sell his computer. He copies the .mdf and .ldf file to a cd. He buys a new computer. Installs a new download of Sql Server. Tries to attach the copied files. This should be the only thing he should know. And Sql Server should be the best it can do and not show an error message.

Or what is if someone send's the .mdf and .ldf file via email to another person. Who knows which version of Sql Server he is running`?

What i mean, if Sql Server want to be a datastore of everyone it should meet the needs of everyone. Don*t kow and don't need to know what BOL is or what scripts are. Perform the needs of the owners autmatically and explain him in a few simple sentences.

I think today the normal person is a bit confused.

Best regards,

Markus

|||

When you do use the attach and detach system you do not have to copy the ldf file as this is only the transaction log file, In that should only be open transactions... If you are copying the file to a new location you will need to make sure that al transactions are commited to the database. This is why I prefer to use the backup option as this makes sure that at the time of the backup all of the data is stored. If you do use the attach and detach method there are chances of loosing data.

|||

Hello Glenn,

you wrote:
> The only time that I have used the attach and detach functions is when I need to
> move a database quickly. I have seen some people use it to install the database
> when the program is installed, but for this I prefer to code a solution that creates
> the database from scripts. Doing it this way I know that the structure and data is
> clean at the time of install.

This is what i want to do when my program is installed. Install SQL Server Express with a named instance. Copy the database files and attach them. Because there ist allready data in the database files. What do you think is the risk of that way? Have you ever heard that his fails?

> When you do use the attach and detach system you do not have to copy the ldf
> file as this is only the transaction log file, In that should only be open transactions...

If i use attach and have only the .mdf file, is the .ldf file then new created?

> If you are copying the file to a new location you will need to make sure that al
> transactions are commited to the database.

Is this the case when i use Detach? When does a detach fail?

> This is why I prefer to use the backup option as this makes sure that at the time of
> the backup all of the data is stored. If you do use the attach and detach method
> there are chances of loosing data.

Are there limitations of restore and backup? When will a restore fail? What is of different version ofs sql server, differences betwen the system where the database was backed up and where it is to be restored? Is there allways compatibility or what must the user care for, that the restore will run?

Regards,
Markus

|||

>This is what i want to do when my program is installed. Install SQL Server Express with a named instance. Copy the database files and attach them. Because there ist allready data in the database files. What do you think is the risk of that way? Have you ever heard that his fails?

Well there are certain things that are not stored in the database itself. Logins, for example.

I have had problems doing exactly what you described in SQL 2000, especially when my original database had any users other than dbo.I've had problems with restore as well.

If you are shipping initial data with your product I would recommend that you do it all in code.INFORMATION_SCHEMA is your friend.I've done things using batch scripts and the command line tools.These work but are not flexible enough and don't provide sufficient error detection.

|||

>Well there are certain things that are not stored in the
>database itself. Logins, for example.
>I have had problems doing exactly what you described
>in SQL 2000, especially when my original database had
>any users other than dbo. I've had problems with
>restore as well.

But when you install a seperate named instance for your application? Do you see this problems in this case too?

|||

Yes you do see the same problems, a Named Instance is just like a completly new server install...