Need Tech Review Of MSSQL Backup Chapter For O'Reilly Book
May 28, 2006
My name is W. Curtis Preston, and I'm the author of "Backup & Recovery"
(formerly "Unix Backup & Recovery") from O'Reilly & Associates.
(http://www.oreilly.com/catalog/unixbr/ )
We're updating the book for 2006 and adding a chapter on SQL Server
backup and recovery. Someone has already written what appears to be a
strong chapter. The problem is that I'm not qualified to review it,
since I'm not that familiar with SQL Server
Can anyone in this group step up to the plate and review the SQL Server
chapter? You would be mentioned in the acknowledgments of the book and
receive an autographed copy of the book.
I'm getting the following errow when I cut and paste script into the query design
There is an error in the query. Invalid object name 'FactResellerSales'. Invalid object name 'DimEmployee'. Invalid object name 'DimTime'. Invalid object name 'DimSalesTerritory'.
I've tried some of everything to resolve this issue. Need help... Not sure what I'm doing wrong.
Few years daily backup-job was fine but now it gives error:
Msg 3023, Level 16, State 1, Server srvr1, Procedure , Line 1 [Microsoft][ODBC SQL Server Driver][SQL Server]Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operations on a database must be serialized. Reissue the statement after the current backup, CHECKALLOC, or file manipulation operation is completed. Msg 3013, Level 16, State 1, Server srvr1, Procedure , Line 1 [Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.
==================
any ideas what could be reason of this error... and maybe how to fix it? DB file size - about 5GB free disk space - about 8GB (+5GB for backup file)
Ok a network tech put an executive assistants database on sql with the upsize wizard in access. Only problem is that she cant input anything into the database, my guess is that its due to no Primary key in the new sql table. I have had this problem before am I correct in my assumptions???
i need some help. im going back for second interview for dba job and l am been told that there's going to be a test. Not writing but just task to perform. what should i expect? i want to be ready? thanks for hint
I just got their demo but having problems with running ApexSQLDiff , any feedback how it works for others? Maybe there are some other tools worth looking at ? Thanks for any suggestions
Something happened, they are sending me to this conf on May 23-28. This is the first conference since 1999 that I would be attending. Personally I consider this to be the best conference, and I can attend it every day. But it would be interesting to see some of you. Anybody else going?
Hi I'm an experienced programmer, but just don't have much experience with ASP or MSSQL. I inherited a website and now need to set up an automated backup system for a fairly large (40MB) MSSQL database hosted by a 3rd party provider of reasonable quality with ASP and SQL Server 2000. Let's avoid ASP.NET solutions since the website I inherited is written in "straight up" ASP (old school).
I have access to the database, login etc. I'm thinking I should combine an ASP script with the database connectivity and write daily/weekly backups to a dir on the hosting server.
I am evaluating demo version of Lockwood Tech Audit Tool and having problems with "undo" module. When I click "UNDO" it not only freezes application but entire PC, did any of you experienced same sort of problem ??? Thanks
ok, here is an interesting one. We have multiple jobs that run the following SQL Backups to Permanent Backup devices.
Backup Database DBX to DBXFull with NoInit - 1 am Backup Database DBX to DBXDiff with Differential, NoInit - 12 pm Backup Log DBX to DBXLog with NoInit - every hour on the hour
the devices are in path E:SQLBackups<databasename> E: is on a SAN
Every Night E: is backed up by legato, When Legato Starts to backup the directory, at the hour a transaction log backup occurs. The Backup Fails and The Database Is Taken OFFLINE.
Questions are as follows. What is causing the db to be taken offlline? How can i stop the db from being taken offline while still mainting my current backup strategy.
Temporarily we have stopped tlog backups during the directory backup phase (legato) and then resume them when it is complete.
Difficulty Level: we will not be using Legato's SQL Plugin for the backups, they have to be performed with jobs and SQL Backup.
Can SQL 2000 initiate backups to 3rd party applications (like LiteSpeedand BrightStor's SQL Backup Agent)? My goal is to be able to managebackup and restores completely through SQL Enterprise Manager and QueryAnalyzer without having to logon to the Server and using the 3rd partybackup application's software. Is this possible through SQL?Thank you,Stephanie
We're a very small company, I’m the “technical” director who has evolved enough skill in a wide variety of tasks (network setup, machine config, email systems, html, asp, database...) and then one day you notice that parts of the system are starting to get way more complex and troublesome than the layman knowledge you have can cope with... Well, I think I’ve got to that point and I need some outside help to get our system to the next level!
OK, some rough details to start with. We run a small but fast-growing vehicle tracking system that sends back a LOT of data via GPRS to our SQL 2000 Enterprise server hosted on a dedicated server in London. The physical machine is a P4 3.2Ghz Dual-core Dell rackmount with 2GB RAM and 2 x 76GB SCSI disks in a RAID 1 array. This is partitioned into a 15GB C: partition and a 51GB D: partition. The system paging file is set to be 1536MB and is on the C: partition. The server is used for everything we do... it runs Smartermail email server (only about 5 or 6 domains and a few users, hardly used at all), SQL server as mentioned, web server & the proxy software that receives incoming data from our tracking devices.
There are 9 or 10 active databases on the SQL server. 8 of them take up less than a gigabyte between them and are sparingly used. The main “active” database on the SQL server is the tracking system – and this is big... As our tracking devices send in data every 10 – 30 seconds, the database is hit with hundreds of thousands of events per day. On a weekday, some half a million rows of data are written to the main “events” table on the database. Over 7 days from 26th November to 2nd December, almost exactly 3 million rows of data were written to the events table. We undertake to hold 3 months or so of data “live” for our customers and I periodically archive data off. I’ve been too busy to archive recently and the database is holding data on the events table going back to July 1st. The physical .mdf file is just under 30GB on partition d: at present. The plan is to drop the active data stored to only 1 – 2 months, but this still leaves a 12GB .mdf file.
The worrying thing with this is that this is only 700 or so devices writing to us at present... we aim to have thousands out there soon! We are looking into how we can hugely improve system performance and look to the future. Our hosting company is recommending VMWare virtual servers and SAN storage, but I’m not entirely sure that is the best way forward.
Our non-tech MD thinks the way forward is to have one database per customer and can't understand when I tell him I think that's bad as it will create all the system tables and bits & pieces for EVERY customer if we do that, right? Also it would be a nightmare to add a new column to a table as I'd have to update every single version of the database too... I want to avoid this unless I'm missing something and this is actually the best way to go forward?
I've had someone mention horizontal partitioning to me? not sure what implications this has to coding and table naming? Or is it all one big database spread among separate servers?
Currently our server is drowning on disk access and it's only going to get worse... any suggestions or links to reading online that I can do would be great, thanks!
Hello, we are running MSSQL 2005 express on a windows 2003 machine. I have looked in SQL Server Management Studio for close to an hour and could not find a way to setup automatic backups of databases. Where is this feature, and short tutorial would be great. Thanks!
Hi, I am using an Enterprise Manager and i have created a Database Maintainance Plan, can anybody guide me as to how to create backup on a remote machine.
I also mapped that device(say M/c B) onto my server box(M/c A) but i am not able to see that particular location in my backup activity screen.
Thanks for looking at this for me. Here is the SQL Script as it stands so far. UPDATE TableImportHistorySET ImportStopTime = CONVERT(numeric, GETDATE(), 108), Elapsetime = CONVERT(numeric,GETDATE(), 108) - CAST((SELECT TOP 1 ImportStartTime FROM TableImportHistory WHERE TableName = 'ARCHRG'ORDER BY DataImportID DESC) AS numeric), Successful = 1WHERE (DataImportID = (SELECT TOP 1 DataImportID AS LastID FROM TableImportHistory WHERE TableName = 'ARCHRG') ORDER BY DataImportID DESC)) The Elapetime update portion of this statement is where I'm getting stuck (if I take it out the statement works) I get the error "ERROR converting Data type NVARCHAR to Numeric" I've changed the data type in the Table from numric to varchar, nvarchar, decemial nothing works and not sure what I should set the Starttime and StopTime datatypes in the table to (any suggestions please) Elapsetime = CONVERT(numeric,GETDATE(), 108) - CAST((SELECT TOP 1 ImportStartTime FROM TableImportHistory WHERE TableName = 'ARCHRG'ORDER BY DataImportID DESC) AS numeric) Are there better ways to do this statement?
...we coded round most of the issues and deadlocks and things seemed to improve for quite a while but we have recently run into performance problems again of late.
The CPUs on our SQL box often thrash away at 100% and our ColdFusion website has begun running painfully slow.
Our developers/hosts have suggested we might need to look for a 3rd party SQL guru who could look at the SQL box, do some tracing etc. and perhaps make some recomendations around optimising the DB architecture or changing the way we run certain queries. At the moment we have only gut instinct on which bits of the DB are likely to be being hit most hard.
Our website has grown from being very small to being really quite busy and it's possible we are running into shortcomings with the design of the DB that need to be tackled before we can expand further.
I'm not sure what the protocol is (I see there is a Jobs part of the site) but I wondered about the possibility of getting one of you guys in for a short while to review our server and database, for a fee of course. I'm not sure how long it would take to review that kind of data and get a feel for the usage?
We are based in the UK and whilst I guess it could be done remotely it might be easiest if the person was UK based too.
I'm as much interested in feedback about the idea (it might be not workable/a good idea for example) as I am to people offering their services.
Don't know if this is the appropriate forum. I am looking for an experienced SS consultant to review our setup, hardware architecture, recovery plan, and to provide high-level advice moving forward. My company is a CRM hosted software provider with a dynamic, metadata-based product built in Visual Studio 2005. Currently we run on SS 2000, but plan to migrate to SS 2005 or 2008. We anticipate quite a bit of growth and want to make sure that we are on the right path. Let me know if you are interested or know someone who is.
This is my 1st attempt at designing a database, and I have not finished it completely, but I would like somebody to review and critique for me. I really don't want to make any mistakes with this and I would appreciate any expertise out there to direct, recommend, suggest improvements and/or changes, PLEASE.
Thank you for considering this,,,if you provide me with your e-mail, I can send you a script.
First of all, this is my initial thread here on dbforums. I come from the land of Broadband Reports and would like to say, Hello fellow DB enthusiasts. :)
I'm not a novice to relational databases (Access MDBs), but new to implementing a db via SQL SERVER (2000 in this case) and using Access Data Projects.
Sample Data would be 1 | CRYS | CRYSTAL STAIRS 2 | MAOF | MEXICAN AMERICAN FOUNDATION 3 | PATH | PATHWAYS 4 | CCRC | CHILD CARE RESOURCE CENTER 5 | CHSC | CHILDREN'S HOME SOCIETY OF CALIFORNIA ==========================================
THE REMAINING "FKs" FROM SCHOOL ARE SIMILAR, as is other tables and their relationships. The design of the foreign keys were made using sql and the keyword "REFERENCES" and "FOREIGN KEY."
My questions are: :confused: (1) Is the use of FK as a Constraint any different than using an INDEX and how? (2) Should I Alter the Tables to include CASCADING Up/Down? (3) Are the use of CHARs Ok for the Keys? (4) Have I over/under-normalized any of the relationships?
Hello, I've proposed to the head of IT at my organisation to head an database development export group to: - Provide assistance in improving the performance of existing relational databases - Provide assistance for the development of new database, e.g. correct construction of indexes; data contention, etc. - Creation of database development standards - Enforce the above standards for new databases so that the database is not deployed to production servers until it passes the standards.
I'd really like this to be a success as it should improve our rather crappy databases, basically because the people currently creating the databases don't know much about relational design.
Can anyone please post their experiences of setting something like this up, or working on such an expert group where they work? I'd really like to hear the good and bad experiences and what and (what not) to avoid.
is there a way using sqlexpress to review the most recent set of sql commandssubmitted to a database?I remember something about setting up logging in sql server 2000, but don'tsee much about this on google or the news group for 2005.thanksJeff Kish
I have an automation project to review the software. The software is using MSVS2012 and uses DBs from MSSQL2012. Is it possible to restore the DB files on my local laptop to review the code completed thus far? If so what other software would I need besides MSQLserver 2012 on my local laptop.  I'm able to use MSSQL server 2012 on my local laptop, but the DBs are on servers.Â
Here is a script I wrote that analyzes datasets and returns all the minimal composite and unary keys that uniquely identify records. I wrote it because I frequently have to analyze client spreadsheets and non-normalized data tables.
On my desktop server it took about two minutes to analyze 2000 permutations of a table with 50 columns and 5000 records.
Please try it out for me and let me know if it chokes on anything, or if you see any ways it could be improved!
I'm having 2 issues on my first "real" SSRS report. 1. How to do a print preview via the preview tab. For print rendering, I have no print preview button on the preview toolbar. Is this normal? MS documention said that there is a Print Preview button. (BOL May 2007 - Designing & Creating reports -> working with report designer -> debugging and Publishing reports (Print Preview)) This report will always be rendered via the printer and it is a "pain" to have to always print it to see changes. The print layout option comes up all black with nothing viewable.
2. How do I reset page numbers back to 1 for report groups? I see no option for this. My report group requires a new page and I want page numbering to start at 1 when the grouping changes.
Note: I'm running VS2005 via Vista (with VS2005 Vista fix installed). SQL2005 SP2 also installed.
Any advice on this will be appreciated. I've spent too much time already where this should be obvious.
Not seeing the Review Data Type Mapping Screen in SQL Server Import and Export Wizard?
Is there only a certain version where that screen shows up?
I am trying to import data from an MS Access application to SQL Server and all of the connections are good, but some of the data isn't and if I let it migrate using this tool it crashes on the bad data and there is no data that migrates. The Review Data Type Mapping screen will allow me to bypass the records in error and load the rest. however, I can;t do that if I cannot see the screen.
How many result-rows does mssql return should be used asynchronous method to use mssql cursor, can get the best performance in any time in any result offset?
i want to make the cursor fast in any time whatever how many results returned
i was planning to create a database migration tool .. its a certain database of a DMS (document management system) to another DMS (two different DMS)... from DMS using msde 2000 server .. and tranfer to a DMS using a postgre sql or mssql .. depends ..
they have different table structures and names . . :D
i was thing of what language shall i use.. or what language is the best to work on this kind of project :)
