Hi,
I plan to use Log Shipping on SQL 2000 to have warm standby database. I understand current procedure but have a question wether I can do full normal backups of my database server without screwing up my Log Shipping process? Also can I do transaction log backups as well (separate from the ones used for log shipping)?
Hi guys..
I had created a Integration services project within my local system.All my packages are running fine.I added it to source control.Now i added this project from source control to another machine.It is failing to run...The path it is trying to execute is the location of the where i actually created my project.
How can i make it work.let me know
Is it safe to backup while the database is running 1000 transaction/sec.? If yes - why should I buy Veritas Backup Exec Server Edition and Veritas Backup Exec Online Backup Pack ?
Michael
I have some questions on backups and log shipping.
Before I get to them, though, the goal:
Back up a SQL 2000 database (~5GB in size) completely every day.
Back up its transaction log hourly.
Maintain a warm backup DB server using log shipping.
Here is what I have done so far, which has led to my questions:
I have stopped using maintenance plans for backups, feeling that
then the process would be less 'black boxy'
I first created two jobs to perform the daily db and hourly tlog backups
that saved the backups in files using the naming formats
<name>_db_<yymmdd>.bak and <name>_tlog_<yymmddnnss>.bak.
Issue 1: doing backups this way means that it's a little difficult for the
log shipping jobs to figure out the filenames (esp. the tlog ones), if log
shipping is going to use the files generated by the backup jobs.
Issue 2: I thought maybe I'd have log shipping generate its OWN backup
files, but would that cause problems with the transaction log? Say the
normal tlog backup fires, then fifteen minutes later the shipping tlog
backup fires. Would the shipping tlog backup file be missing the transactions
that were backed up during the normal log backup?
Issue 3: To try to help with the naming issue, I tried switching the backups from
creating new files each time to file devices whose names would be constant.
This worked, but since my database is about 5GB in size, that meant
that, with expiring backups after 7 days, the database backup device
would settle in at about 25GB (I skip weekends) and I'd have to copy
or cab-copy-uncab that file over to my warm server every day. That
seemed a little inefficient. Does anybody have alternative ideas?
My last question is for general info:
Where does SQL Server keep the information on the backups that exist?
Whether I was using individual files or file devices, I was able to go to
Database-All Tasks-Restore Database... in Enterprise Manager and it
would show me the backups that existed. I imagine these must be
stored in a system table somewhere, but I did not see any obvious place
to look (no 'sysbackups' table, etc.).
Many thanks in advance for your input!
Geoff
Quick question..
If I'm log-shipping data from a database can I still perform a full backup of it each night without it affecting the log sequence?
We have a SQL 2012 server instance that has log shipping set up to another SQL 2012 server to provide a warm standby for a forward facing application. The databases on the primary server occasionally are required to be backed up and restored to a development environment, completely different server. Is there a way to schedule full backups with log shipping enabled?
View 2 Replies View RelatedI've got log shipping set up, and everything seems to be working fine, but the log files are not being deleted from the primary server despite configuring log shipping to retain them for 3 days. I see no errors concerning the log shipping, but did not configure a monitor. What process is responsible for deleting the older log backups, and how can I look for errors. I could simply set up a jog to delete the older files, but that will only mask the issue.
View 3 Replies View RelatedHello,
I would like to know if anyone has a recommended method for having backups to tape while running log shipping. For example, is it possible to copy the transaction log backups used for log shipping to tape and apply them to a Full Database Restore from a 3rd Party Backup tool such as Veritas?
My goal is to be able to do a point-in-time restore from tape and still be able to use SQL Server 2005 Log Shipping.
Thanks,
Erik
Afternoon,
I have a few Log Shipped DBs that are working great.
Currently they are set to fire off every 15 minutes 24/7.
My question is this ... I need to get FULL backups of the source DBs in order to restore them on certain Dev boxes.
If I were to execute the full backup on one of these Log Shipped DBs ... how would it affect the log shipping process?
Is there a special method to accomplish this?
As a side note, what would be some concerns/issues if in being able to create the FULL backups and not interupt log shipping, I were to create the backup using a 3rd party tool like Quest LiteSpeed?
I sure wish we were on Enterprise, then I could create a mirror and then snapshot off it to create my backups BUT ... that is not the case as we stand today.
Thanks
My backups are running 5-6 hours on SQL2000. I'm sure they only used to take 1 hour or so. On another server, backing up the same database (both about 50 gig), the backup only takes 45 min - 1 hour. What can I look at to see why it's taking so long ?
(edit) I'm using a maint plan to backup to disk
I've got a server (SQL 2K, Win2K) where the backupshave started running long.The database is a bit largish -- 150GB or so. Up untillast month, the backups were taking on the order of4 to 5 hours -- depending on the level of activity on theserver.I'm using a T-SQL script in the SQLAgent to run thebackups. Native SQL backup to an AIT tape drive.Now, for no apparent reason, the backups are takingon the order of 24 to 26 hours. The backups completesuccessfully -- no errors, just taking an outrageouslylong time to complete. DBCCs check out AOK, noproblems with the database.No changes to the machine. No hardware changes. Nosoftware changes. Weird.Multiple tape media have been tried -- it's not a caseof a tape going bad.We've had no problems with this box for almost 4years. Now it's gettin' jiggy with us!Any ideas on where to start with this one?Thanks in advance.
View 1 Replies View Relatedwe have MS SQL 2005 Standard and we have setup Maint. Plans with the SQL Server Management Studio front end.
i didn't set this up, so if it's completely crazy, i'm just the messenger :)
we have two maintenance plans:
maintenance plan A (to generalize) runs its full back up, then generates its differential backups and transactional logs.
then maintenance plan B runs its full back up and generates differential backups on a second drive without transactional logs.
what appears to happen with this is MS SQL will only let us restore from the most recent differential, so if maintenance plan B's differential ran last, it renders maintenance plan A's differentials essentially useless.
the reason it was setup this way was that in theory, we wanted A to backup to one drive and B to backup to a drive that gets rotated for offsite backup.
if we're breaking diff backups, this isn't optimal. anyone have a better method for creating backups on primary backup drive and a swap out drive?
thanks
When trying to open the log shipping status report, I get an error: user must be in the master database. The jobs appear to be running correctly.
View 0 Replies View RelatedHey All,
I am currently backing up a SQL 2000 Database by doing full backups everynight. I would like to cut this back to once a week and do differential backups during the week. BUT... How can I do differential backups without appending or overwriting the night befores backup? I'm sure this can be done through command line but im not exactly sure of the process. If anyone can help please post...
Thanks,
Jared
Hi there,
We have a production database that was generated by a vendor. The vendor wants us to test a new version of their software. This testing process will take several months. The users want the testing to be as real time as possible. I have developed a series of scripts that will back up our databases and ship them over to our test environment on a nightly basis. We also of course have nightly backups. As a general rule, we do full backups once a week and differentials on a nightly basis.
We are a phone company that has transactions being applied to the database 7 X 24.
My question is this: Is there a way (an option or something) that when my backup of the production database which is destined for the test environment runs, I can tell it to not set the flags that indicate a backup has been done. What I want to avoid is the differential backup process from being 'Confused' about what backup it is doing a differential for.
I would appreciate any help or insight you can give me.
Thanks! - Eric-
I am trying to create a failover cluster without the log shipping in 2012 as i've done it with a static instance with some database.Is the "AlwaysON" feature the solution when an application creates random and numerous databases within the instance and we need a failover scenario ?
View 9 Replies View Relatedhi,
i am facing a problem in creating labels usin Report Builder.
i need to create a 4X6 label containing 4 rows.
But in Report Builder , when i try to create a table format, it only allowing one row.
How to insert another row and place data in it?
i was successfully developed labels using report server project in VS 2005.
but it's not possible in Report Builder.
please help me out.if not understand quesion well, please feel free to ask.
thanks
-Praveen.
Greetings All, I was hoping that someone out there has seen a problemsimilar to the one I am seeing. I have one file with several "createfunction" statements in it. When I try to run it through C# I geterrors because it does not like the "GO" statements.Second, when I break them up into individual files and then call themfrom C# and try to execute them I get funky results if there is avariable in an update statement. C# gets back to me that the variablemust be declared?TFD
View 1 Replies View RelatedThe space allocated to the Log in question is 180 GB. During this time period I was running TLog backups every 5 minutes, yet the log continued to chew through to 80 GB used, even after the process was complete and a final TLog backup had been taken. It continued to stay very large until the Full backup was complete -- or something else that I'm unaware of completed. Like every other DBA I typically take a TLog backup to shrink the log, but what appeared to be the case here was the Full completed and it released the used log space. All said, will Transaction Log backups not free up the log during Full backups?
View 3 Replies View RelatedI am about to replace a SQL Server which is replicated to about 25 computers. The replacement PC (and SQL Server) will have the same name. I intend to generate a Create Replication Script before shutting down the old PC and to run it on the new one.
Should I also create and use a Remove Replication script and run it on the old PC before shutting it down? I am thinking that I might need to do this to remove Replication infromation from the subcribers before running the Create script.
In other words, is it OK (or not OK) to run the Create script with the subcribers already in existence? Is there possibly a way to run the Create script on the sever and skip updating of the subcribers?
Thanks,
Dick Campbell
Hi
I could not able to find Forums in regards to 'Log Shipping' thats why posting this question in here. Appriciate if someone can provide me answers depends on their experience.
Can we switch database recovery model when log shipping is turned on ?
We want to switch from Full Recovery to Bulk Logged Recovery to make sure Bulk Insert operations during the after hours load process will have some performance gain.
Is there any possibility of loosing data ?
Thanks
Hi there,I am trying to create a Sql Server 2005 Express database by running Sql Scripts. I have a script that has CREATE statements for tables and procedures. What I'd like it to do is create a new database with a name and a file location I specify.I searched high and low, tried lots of options with the SqlCmd tool but I keep getting authentication or other errors.Is this even possible? If so, does someone know of a good reference? I know I can create a new database and tables from within VWD but I need to create this database based on a .sql file....Thanks in advance....Imar
View 22 Replies View RelatedI have a code that is working, and want to create a store procedure in order to put the information of the running code into a new table.
View 7 Replies View RelatedHi,
I 'm sure I am missing something obvious, hopefully someone could point it out. After a failover log shipping, I want to fail back to my inital Primary server database; however, my database is marked as loading. How can I mark it as normal?
I did the failover as follow:
I did a failover log shipping from the 2 server Sv1 (Primary) and Sv2 (Secondary) by doing the following
1) Stop the primary database by using sp_change_primary_role (Sv1)
2) Change the 2nd server to primary server by running sp_change_secondary_role (Sv2)
3) Change the monitor role by running sp-change_monitor_role (Sv2)
4) Resolve the log ins - (Sv2)
5) Now I want to fail back - I copy the TRN files to Sv1 - use SQL Ent to restore the database at point in time. The task is done; however, the database is still mark as loading. I could not use sp_dboption.
I appreciate any suggestion.
Thanks in advance
I have two servers: progress as transational server and mssql as warehouseserver.I did DTS that "pumps" data from progress to mssql (via ODBC). Copying thedata has to be done once a day, but sometimes there is a need to do it onuser's demand.I'd like to ensure that I properly understood the method of running DTS I'vejust found using Google.As I can see, there are at least two methods (except of scheduling):- using dtsrun - which requires user running the DTS to have adminprivileges to use xp_smdshell- using sp_start_job - which requires creating a job prior to running theDTS.I think of using the second one as it seems to be more secure - am I right?Are there any hidden traps? What else should I do?--PL(remove "nie.spamuj.bo.w.ryj" from my email address)
View 2 Replies View RelatedI am trying to persuade our security people to allow access to RS from external facing web sites. The web sites will talk to the SOAP interface over SSL so the firewall will need to open port 443 between the web server in the DMZ and the RS server inside the firewall.
Any credentials passed will be encrypted, so the main questions is around parameters to reports and the possibility of SQL Injection attacks. I need a white paper or other document that will convince them that RS will not allow SQL Injection or scripting attacks and properly validates parameters.
Does any such thing exist?
Hi. Sorry if I am asking a stupid question since I am an absolutely beginnerin SQL Server. Here is the question . . .About 13 hours ago, I got my SQL Server 2000 to index a table which has 104million records. At first the CPU usage was high. But after an hour or two,the process has seemed dead and the Enterprise Manager has had no response.The CPU usuage dropped to zero and has been jumping between 0 to 5%. Theharddisk indicator has been blinking at a rate of roughly three times pertwo seconds.Is this normal? Has anyone got any idea how long the process would take? Ihave assigned 1.8GB of RAM to the SQL service and is currently taking upabout 1GB.
View 7 Replies View RelatedCan anyone tell me how I can copy store procedures from SQL Server 2000 to Source Safe 6.0? I right-click the store procedure and cop
y it but when I got to the visual source safe it will not work. I therefore went through the soruce code control on Tool menu which I cannot see such commands. I do really need your help.
Regards,
I've recently planned to make a program that uses remote MSSQL connection. I'm not such an expert in security.
Please can someone tell me if it safe? and how can I improve my security. I know how to block SQL injection, but me fear is that some will hack my software for personal use.
Thanks, RedEyez.
I have a table that stores a value called "LocationNumber" as a varchar. I didn't create the database, I just use it. Anyway I usually sort the list by Casting the LocationNumber to an Integer. If I don't cast it, it trys to sort it lexiographically i.e. 0, 1, 21, 3, 345, 9 instead of 0, 1, 2, 3, 9, 21, 345.
It works 99.9% of the time, but the other 0.1% of the time it runs into some text that somehow made its way into the database field, and it causes an error.
Is there a way to safely cast a number in SQL? So that it just returns 0 when the value is not a number.
Impossible to connect to my mssql 2000 database
web.config connexion string >>>
<add key="DataSourceSql" value="Initial Catalog=angiras;Data Source=localhost;Uid=sa;Pwd=;" />
or
<add key="DataSourceSql" value="Initial Catalog=angiras;Data Source=localhost;" />
connexion code >>>
Private ConnexionSQL As SqlConnection
Public Sub OpenConnexionSQL()
ConnexionSQL = New SqlConnection(ConfigurationSettings.AppSettings("DataSourceSql").ToString)
ConnexionSQL.Open()
End Sub
when I try >>>
OpenConnexionSQL
I get an error
any idea ?
thank you
Hi guys, I've been thinking about this problem now for some time but somehow I don't know if my "solution" for it is right. I'd like to read your opinion.
There is a Capital table with Capital_Nr, Capital_Name, Capital_Population, Country_Nr and Country_Name as attributes.
I know the table is chaotic so I brought it to 3NF :
Capital table : Capital_Nr, Capital_Name, Capital_Population, Country_Nr(foreign key)
Country table : Country_Nr and Country_Name
Ok so I guess the table should be now in 3NF, but what intrigues me is in what NF the table originally was. I tried then to use Codd's definition of 2NF : "a 1NF table is in 2NF if and only if none of its non-prime attributes are functionally dependent on a part (proper subset) of a candidate key". In my opinion the original candidate keys could only be {Capital_Nr},{Country_Nr} and {Country_Name}, each one of them single, i.e. separate from each other. So, as there is no composite candidate key, I can affirm that the original table was in 2NF. Am I right ?
Thanks !
I am wondering what normal disk I/O should be.
i know it verys depending on use but im looking for an average.
here is an idea of what we have
there is about 10 centers doing replication to our primary server.
we have about 80 users connecting directoy to our primary server using MS Dynamics through CITRIX.
we have a few other apps use the database as well however i am fairly certin its Dynamics generating our disk IO
Hardware wise we have a powerful blade connected to a raid 5 SAN with 15000 rpm disks.
normaly the disk IO stays fairly low but every so often it goes crazy and im thinking it shouldn't
Below is a sample of our disk IO from perfmon over 2 minutes or so. as you can see everything looks ok untill 04/15/2008 10:12:49.470 when the Disk I/O % goes above 100%
"04/15/2008 10:12:23.470","2.8300181121159178"
"04/15/2008 10:12:24.470","0"
"04/15/2008 10:12:25.470","3.950025280161793"
"04/15/2008 10:12:26.470","0"
"04/15/2008 10:12:27.470","0"
"04/15/2008 10:12:28.470","5.9800382722449426"
"04/15/2008 10:12:29.470","7.7400495363170325"
"04/15/2008 10:12:30.470","3.4500220801413128"
"04/15/2008 10:12:31.470","0"
"04/15/2008 10:12:32.470","4.180026752171214"
"04/15/2008 10:12:33.470","3.8600247041581071"
"04/15/2008 10:12:34.470","0"
"04/15/2008 10:12:35.470","142.71091334984544"
"04/15/2008 10:12:36.470","0"
"04/15/2008 10:12:37.470","1.1500073600471041"
"04/15/2008 10:12:38.470","0.81000518403317789"
"04/15/2008 10:12:39.470","0"
"04/15/2008 10:12:40.470","2.1400136960876548"
"04/15/2008 10:12:41.470","10.230065472419025"
"04/15/2008 10:12:42.470","4.5800293121875981"
"04/15/2008 10:12:43.470","0"
"04/15/2008 10:12:44.470","0"
"04/15/2008 10:12:45.470","14.500092800593926"
"04/15/2008 10:12:46.470","6.730043072275663"
"04/15/2008 10:12:47.470","1.6300104320667652"
"04/15/2008 10:12:48.470","7.3500470403010585"
"04/15/2008 10:12:49.470","264.66169383484055"
"04/15/2008 10:12:50.470","324.18207476527851"
"04/15/2008 10:12:51.470","536.17343150996169"
"04/15/2008 10:12:52.470","270.31172999507197"
"04/15/2008 10:12:53.470","331.97212462159757"
"04/15/2008 10:12:54.470","333.84213658967417"
"04/15/2008 10:12:55.470","435.57278766584108"
"04/15/2008 10:12:56.470","442.31283080211716"
"04/15/2008 10:12:57.470","685.92438991609549"
"04/15/2008 10:12:58.470","295.1618890360898"
"04/15/2008 10:12:59.470","61.470393410517829"
"04/15/2008 10:13:00.470","56.360360706308519"
"04/15/2008 10:13:01.470","45.260289665853861"
"04/15/2008 10:13:02.470","224.58143732119885"
"04/15/2008 10:13:03.470","1022.0365410338626"
"04/15/2008 10:13:04.470","1114.6271336136551"
"04/15/2008 10:13:05.470","241.52154573789269"
"04/15/2008 10:13:06.470","506.79324347675828"
"04/15/2008 10:13:07.470","374.88239924735518"
"04/15/2008 10:13:08.470","359.5723012627281"
"04/15/2008 10:13:09.485","427.62119831413077"
"04/15/2008 10:13:10.485","400.15256097639025"
"04/15/2008 10:13:11.485","1076.2868882360847"
"04/15/2008 10:13:12.485","325.94208602935055"
"04/15/2008 10:13:13.485","449.36287592240592"
"04/15/2008 10:13:14.485","344.28220340610181"
"04/15/2008 10:13:15.485","569.57364527132972"
"04/15/2008 10:13:16.485","775.02496015974498"
"04/15/2008 10:13:17.485","729.54466908588222"
"04/15/2008 10:13:18.485","778.70498371189581"
"04/15/2008 10:13:19.485","1080.1269128122422"
"04/15/2008 10:13:20.485","754.56482921490704"
"04/15/2008 10:13:21.485","999.11639434492372"
"04/15/2008 10:13:22.485","701.36448873272786"
"04/15/2008 10:13:23.485","276.26176807531567"
"04/15/2008 10:13:24.485","252.13161364232732"
"04/15/2008 10:13:25.485","533.27341294984296"
"04/15/2008 10:13:26.485","759.54486108711092"
"04/15/2008 10:13:27.485","817.11522953746908"
"04/15/2008 10:13:28.485","129.41082822930068"
"04/15/2008 10:13:29.485","158.94101722251023"
"04/15/2008 10:13:30.485","160.55102752657618"
"04/15/2008 10:13:31.485","655.87419759486454"
"04/15/2008 10:13:32.485","444.49284475420637"
"04/15/2008 10:13:33.485","272.47174381916045"
"04/15/2008 10:13:34.485","94.730606275880163"
"04/15/2008 10:13:35.485","355.28227380655238"
"04/15/2008 10:13:36.485","898.29574909279427"
"04/15/2008 10:13:37.485","1309.3783800216322"
"04/15/2008 10:13:38.485","88.200564483612695"
"04/15/2008 10:13:39.485","1.2300078720503811"
"04/15/2008 10:13:40.485","4.9400316162023437"
"04/15/2008 10:13:41.485","217.90139456892524"
"04/15/2008 10:13:42.485","373.26238887928884"
"04/15/2008 10:13:43.485","73.760472067021226"
"04/15/2008 10:13:44.485","1.4100090240577541"
"04/15/2008 10:13:45.485","34.960223745431975"
"04/15/2008 10:13:46.485","15.990102336654955"
"04/15/2008 10:13:47.485","63.580406914604247"
"04/15/2008 10:13:48.485","26.560169985087906"
"04/15/2008 10:13:49.485","11.150071360456707"
"04/15/2008 10:13:50.485","15.250097600624644"
"04/15/2008 10:13:51.485","17.010108864696736"
"04/15/2008 10:13:52.485","12.210078144500125"
"04/15/2008 10:13:53.485","95.650612163917842"
"04/15/2008 10:13:54.485","38.740247937586801"
"04/15/2008 10:13:55.485","5.6000358402293777"
"04/15/2008 10:13:56.485","1.6600106240679942"
"04/15/2008 10:13:57.485","19.590125376802412"
"04/15/2008 10:13:58.485","2.8100179841150981"
"04/15/2008 10:13:59.485","631.48404149786563"
"04/15/2008 10:14:00.485","579.14370651972172"
"04/15/2008 10:14:01.485","1.0600067840434177"
"04/15/2008 10:14:02.485","15.870101568650039"
"04/15/2008 10:14:03.485","112.28071859659903"
"04/15/2008 10:14:04.485","15.660100224641438"
"04/15/2008 10:14:05.485","4.9800318722039822"
"04/15/2008 10:14:06.485","25.44016281704203"
"04/15/2008 10:14:07.485","16.130103232660691"
"04/15/2008 10:14:08.485","5.9300379522428939"
"04/15/2008 10:14:09.485","4.8400309761982481"
"04/15/2008 10:14:10.485","2.6400168961081349"
"04/15/2008 10:14:11.485","19.430124352795858"
"04/15/2008 10:14:12.485","42.55027232174286"
"04/15/2008 10:14:13.485","37.550240321538055"
"04/15/2008 10:14:14.485","1.210007744049562"
"04/15/2008 10:14:15.485","15.930101952652498"
"04/15/2008 10:14:16.485","20.550131520841735"
"04/15/2008 10:14:17.485","4.0900261761675267"
"04/15/2008 10:14:18.485","8.0100512643280908"
"04/15/2008 10:14:19.485","1.6000102400655365"
"04/15/2008 10:14:20.485","2.3300149120954372"
"04/15/2008 10:14:21.485","1.6200103680663558"
"04/15/2008 10:14:22.485","10.730068672439504"
"04/15/2008 10:14:23.485","4.4600285441826832"
"04/15/2008 10:14:24.485","9.0300577923698704"
"04/15/2008 10:14:25.485","0"
"04/15/2008 10:14:26.485","15.550099520636932"
"04/15/2008 10:14:27.485","2.970019008121652"
"04/15/2008 10:14:28.485","64.580413314645213"
"04/15/2008 10:14:29.485","71.850459842942996"
"04/15/2008 10:14:30.485","53.220340610179903"
"04/15/2008 10:14:31.485","32.620208769336124"
"04/15/2008 10:14:32.485","1.0000064000409603"
"04/15/2008 10:14:33.485","4.8400309761982481"
"04/15/2008 10:14:34.485","8.2700529283387425"
"04/15/2008 10:14:35.485","11.160071424457115"