I have run into some problems with logfiles and backup logfiles (MS
SQL server). I have read much about them but uptil now I dont seem to
grasp how it works. Specially the part of working with backup log
files.
Can someone please help me with this?
What I want todo is to create backup log files, so I can restore the
database to any point in time before a failure occured. But using this
method create's a problem with the backup log files. They get very
big.
So how can I use backup log files without getting to large backup log
files?
How can I shrink on a regular basis the backup log files and still be
able to restore the database?
I have run into some problems with logfiles and backup logfiles (MS SQL server). I have read much about them but uptil now I dont seem to grasp how it works. Specially the part of working with backup log files.
Can someone please help me with this?
What I want todo is to create backup log files, so I can restore the database to any point in time before a failure occured. But using this method create's a problem with the backup log files. They get very big.
So how can I use backup log files without getting to large backup log files?
How can I shrink on a regular basis the backup log files and still be able to restore the database?
Hi,Everytime a job starts it puts logging into the system (application) loggingfiles.(Messages)How can I prevent SQL from doing that.Arno de Jong, The Netherlands.
Any assistance is appreciated.I would like to know if there is a way to specify the path for databaselog files to be located during the SQL2005 Standard installation.Under the Advanced button there is the ability to specify a differentpath for data files but nothing for logs.Thanks in advance,John
Note: Please refer to my previous message to see the current VS2005 C# code I am using to create my database.
This database will be used for short term operations. In otherwords, a user will create the database, generate a lot of data that will be created using:
SqlBulkCopy bcp;
Then, there will be 'activity'... updates, queries, deletes, etc for a few days and then the user will at a minimum remove all of the data and at some future point in time, will recreate the data and do the process all over.
In otherwords, I am not concerned about backups or transaction logging or recovery or any of these 'normal' issues of using a SQL database.
That said, how do I use SMO to keep the LDF limited in size and as small as possible (assuming I can't turn of the tranaction log to begin with).
2nd question is...I read soething about being able to log ONLY the exection of a Bulk Copy operation. How do I specify that? This is my SQLBulkCopy code.
Currently we are running a full backup on Fridays and differentials monday through thursday. With the Logs being truncated. we are using Veritas 10d with a 5 tape auto loader.
As far as recovery goes will this be sufficient?
we have about 40 users doing normal work hours.
My main concern is the transaction logs and making sure that I will be able to recover my data.
also we do not have an open file option and cannot do backups during the day, only at night.
Backup Log ..... with no_truncate -- How to get it to work.
Applicable to SQL Server 7.0 Date 8/14/00 andrews_gary_w@solarturbines.com
Introduction to the "Backup Log .with no_truncate" feature of SQL Server 7.0.
On occasion a database may become corrupt or a database file may be inaccessible. Ideally, in these cases you want to be able to restore and recover the data back to the current point in time.
This requires that the Transaction Log be backed up prior to the restore process beginning.
Microsoft has provided the "Backup Log with no_truncate" feature for use in cases such as this. However, this feature works only under very specific conditions and thus is the topic of this document.
Background.
When a database is defined, it always has a primary file group. In some cases this may be the only file group defined for the database. The primary filegroup contains, at a minimum, the system tables for the database being defined. The systems tables hold metadata describing the database. Where no other filegroups have been defined, the primary file group will also contain the user defined tables/indexes.
Essential requirement
"Backup Log with no_truncate" must be able to access the "metadata" located in the primary file group, or it will not work. This also means that this part of the database must not be corrupt or that the file containing this part of the database must not be inaccessible.
Specific conditions required for "Backup Log .. with no_truncate" to work.
a. The database needs to be defined so that the user tables/indexes are contained in one or more file groups other than the primary filegroup. b. Logging of changes to the database must be in effect. c. A full backup must exist d. Changes are made (logged) to the user database. e. No non-logged operations must have been performed since the last full backup. f. The primary filegroup file(s) must be available. g. The primary filegroup data must not be corrupted. Or at least the portion needed by the backup operation must not be corrupted. I am not 100% sure about this issue. h. The data in the filegroups other than primary may be corrupted or these files may be completely missing. Process to restore/recover.
a. When problem detected (data file(s) inaccessible or database is corrupted), if primary filegroup is accessible, then perform the Backup Log . with no_truncate. b. Then perform the restore process using the "with replace" option for the database. This will get back any missing file(s) and recover you right up to when the last log backup was taken. c.Post restore/recover process, your database will not be corrupt or missing any data file(s)
Bottom line
To achieve maximum recoverability, the following is recommended.
a. Define databases so that user data is not put in the primary file group. i.e. Define not only a primary filegroup, but also one or more "other" filegroups. b. Place user data in the "other" filegroup(s), NOT in the primary filegroup. c. Place the primary filegroup on a disk drive separate from the "other" filegroup(s). d. Place the log on a disk drive by itself.
Under the above scenario, at least three different disk drives would be needed to accommodate a single database.
Some sources recommend you put the primary file group and the transaction log on the same disk drive (mirrored). This would of course reduce the maximum number of disks needed to two. This topic, from my perspective, is another issue to be separately addressed and thus is not covered any further in this document.
Remember, even if the database files are isolated as recommended above:
a. Should the primary filegroup file(s) be inaccessible, then the Backup Log . with no_truncate option will not work.
b. Should the primary filegroup file(s) be corrupt, then depending on where the corruption is determines whether or not the Backup Log .. with no_truncate option will work.
We have Dell Optiplex GX1 PCs with Seagate ST22000 IDE tape backup devices, running NT Workstation or NT Server and SQL 7.0 with SP1, NTSP5. SQL 6.5 will backup successfully to these devices, but SQL 7.0 will not. The most common error message is 18257, Device or media does not support ... 42000 Syntax error, permission violation, or other nonspecific error...
i know this topic has been covered and i've searched but i can't find the answer.
-SQL server is a diffrent server than my backup server -i've installed the sql agent option on the backup exec server -i've installed the client network utility on the remote server
the backup server doesn't see the SQL server databases. any thoughts
I try to load data into a memOpt table (INSERT INTO ... SELECT ... FROM ...). The source table has a size about 1 Gb and 13 Mio Rows. During this load the LDF File grows to size of 350 GB (until the space if the disk is run out of space). The Server has about 110 GB Memory for the SQL Server reserved. The tempdB doesn't grow. The Bucket Size in the create statement has a size of 262144. The Hash key as 4 fields`(2 fields have the datatype int,1 has smallint, 1 has varchar(200). ) The disk for the datafiles has still space for the datafiles (incl. the hekaton files).
How can I reduce the size of the ldf files during the load of the data ?
I am running the latest MSDE with the SQL Server 2000 client tools on Windows 2K Server on my production server. I have a simliar setup on my development machine except it is running Windows 2K (non-server). I have the same issue on both machines (I am wondering if it is related to the structure of the database). When I open Enterprise Manager, connect to the server, navigate to the Management folder, and add a new management plan, I experience the following error.
I create a management plan that includes nothing but a complete database backup (i.e. no reorganization of index pages, logging, etc.). I set the backup schedule to occur every Sun at 12:00AM and hit OK. Sometimes I get the following error :"Error 8114: Error converting data type int to tinyint" sometimes not. Either way, though, the same thing happens, the schedule for the backup is not saved. If I reopen the management plan and go to the "Complete Backup" tab, there is no schedule in the schedule box.
I have found this link:http://www.technologyone.org/new-4581847-3733.html
Which appears to be the same issue that I am having, however, there is no resolution there. I was wondering if anyone had any ideas.
Hello I have restored a SQL 2000 backup of my database, ever since my web applications are unable to use any of the stored procedures. I get the following error: Could not find stored procedure 'xxx'.
If I use enterprise manager and go to the procedures tab the procedure appears there.
Any ideas what is up?
PS: The username has been changed, before the backup it was "user1" now its something else. For some reason it still shows that the procedure object is owned by "user1". Could this be the problem?
I am doing a complete backup on a sql 7 db and then doing a completerestore (with overwrite existing db) on a sql 2000 server. This is nowour hot standby server. I have the process automated and it worksgreat. The only problem I have now is the logins dont work.I have tried running EXEC sp_change_users_login 'Report', and thelogins appear.However, when I run EXEC sp_helplogins 'joe', the results are empty.So, I am guessing all I need is a sp that will re-associate my loginswith the correct db and grant the appropriate permissions.If anyone has any ideas that would be great.I have also considered doing a log ship instead of a full backup andrestore. Does anyone have any suggections or good examples of how tomake that happen?Thanx
Normal] From: XXX "(DEFAULT)" Time: XXX SQL statement: BACKUP DATABASE [msdb] TO VIRTUAL_DEVICE = "Data Protector_(DEFAULT)_msdb_06_00_14" WITH NAME = 'Data Protector: 2007/08/01 0064', DIFFERENTIAL, BLOCKSIZE = 4096, MAXTRANSFERSIZE = 65536; [Warning] From: XXX "(DEFAULT)" Time: XXX Error has occurred while executing a SQL statement. Error message: '<Microsoft SQL-DMO (ODBC SQLState: 42000):bdb> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot perform a differential backup for database "msdb", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.'
Ive tried to do a full followed straight after by a diff but doent help.
I've some really big problems with SQL Server 2005 Express. I Recently, I had 2 instances on my machine, one was a SQL Server 2000 Developer and the other a 2005 Express Version. The 2000 version was not necessary anymore, so I unstalled it, since then, the Express version keeps having probems.
Under the Server Properties -> Database settings -> Database Locations I've changed the path to D:sqldatamssql, but now, the programm always takes the "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData" to store and load the databases, no matter what I do.
And there are more problems see here:
Restoring does not work======================= I wanted to restore a database called "fw40_admin" from a backup file (.bak), but that didn't work at all, it always says this as an error:
"System.Data.SqlClient.SqlError: The operating system returned the error '5(error not found)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'D:sqldatamssqlfw40_admin.mdf'. (Microsoft.SqlServer.Express.Smo)"
But as soon as I type in the installation path "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData" inside the "Restore As" under "Options", it works.
Why does the backup only work in "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData", BUT the database locations under the server properties is indicated with "D:sqldatamssql"????
Attaching does not work======================= Attaching an existing database does not work either. Most databases (mdf) are located on the path D:sqldatamssql, but as soon as I press
Attach Databases -> Add
The dialog window appears with the "D:sqldatamssql" path, but it is unable to find any *.mdfs altough THERE ARE .mdfs in this directory. But Express can find databases in the selected installation: "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData". Why can't Express see the directory on the D: partition???
Hello!I have a question about the SqlDataSource object.If i make an SqlDataSource with the following sql statement: "SELECT COUNT(id) AS recordCount FROM tblCategory"How do i get the recordCount value to a Variable.Im writing in C#.<asp:SqlDataSource ID="SqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT COUNT(id) AS recordCount FROM [tblCategory]"></asp:SqlDataSource>
The objective is to configure on a dev machine. Selecting New Database User is the easy part as the ***ASPNET user can be selected from the drop down listbox. What follows and what to do next is a myriad of choices. What needs to be done next? Is there a step-by-step document somewhere that you can refer?
I'm trying to do a fulltext search which returns the adjacent words also in the result, like u do a google search and it returns the paragraph containing the searched phrase.
Hi,Trying to get a grip on the "join" thing :)Up until now, I allways used this kinda method:"(select t1.a from t1 where t1.b in (select t2.ab from t2 where t2.b=0))"How can this be accomplished using joins? And if you have the time pleaseexplain the "bits" :)Thank you.--?TH
In a DataFlowTask with several OLE DB Destinations, how can I "last", before ending this DFT execute a storedProcedure?
This storedprocedure is used for saving metadata (taskname, rowcounts etc) regarding this DFT and I dont want to add an ExecuteSQLTask after the DFT in the Control Flow
i have the following Problem: i need to have a database-design in which there are a variable number of customers , a variable numbers of products and a price per product per user. My solution looks like this:
Table Customer: CustomerID ...
Table Products: ProductID ...
Table Prices: CustomerID ProductID Price
Now my question: is it possible to get a Pricelist with one customer and all his prices in one row? E.g.:
CustomerID ; PriceProduct1 ; PriceProduct2 ; ....
So one Price-Column per Product, one Row per Customer? Can i do something like that with an sql-statement , view or stored-procedure so the number of Columns in the result depends on the number of Products and should be "dynamic" - which means when i add a new product to the product-table a new price-column is appended to the result?
I would like to gain data from a temporaly table created by an EXEC command. e.g. EXEC('SELECT col1, col2, col3 FROM Table WHERE ...') - that's right. But I would like to use it: SELECT * FROM _ThisTempTableTheExecCommandHasCreatedRigthNow WHERE...
I know that a function can return a table but an sp cannot.
This came from our actuaries, - create a formula generation and processing engine.
They have a dozen of well-normalized base tables that contain statistical data on competitors, broken down by state, county, city, and zip.
The result should contain aggregated breakdown by the same set of data elements, but also include along with other things an input formula and its result for the Base Rate.
The whole thing is a mini-Monte Carlo simmulation.
THE TRICK: If the formula today looks like:
x = ((a + b) * (c - d + e)) / (a * (c - d) * (f - d) * (e + d))
then tomorrow it may look like:
x = a * b * c * d * e * f
THE REAL TRICK: It should not be based on dynamic SQL!!!
