Question:: I know there is a switch to take a database that was upgraded to 7.0 back to 6.5. How does it work? If I upgrade a database and for 2 weeks input
data and then decide I need to go back to 6.5 -- do I lose the data from those 2 weeks or does it revert all information back to 6.5
We need to perform some maintainence on our database server which may take quite some time. We have a standby server which we constantly apply logs to.
We are planning to recover the standby server and use it while the production server is being maintained.
My question is how to I get back to using the original production machine.
The only way I can think of is to do a full backup on the standby machine,
do some transaction log backup and then restore these on the original production machine.
Is there any way to use the full backup from the original production machine and well as logs from the standby machine. When I try this I get the message that the database has not been rolled forward far enough.
In summary:
1. Backup full database on Prod Server
2. Restore full database on Standby Server
3. Backup transaction log (1) from Prod Server
4. Restore transaction log (1) on Standby Server
5. Recover database on Standby Server
6. Make modifications on Standby Server
7. Backup transaction log (2) on Standby Server
8. Restore full database on Prod Server
9. Restore transaction log (1) on Prod Server
10. Attempt to restore transaction log (2) fails.
I have user called DBA_USER he has db_owner access on (123DB) database ....
But when ever my machine restarted the user was getting access error (i.e.. you don't have access on this database) and i checked his access on that particular database his access got reverted back ....
Here my question is why this particular user getting this type of error ....
For the second time in two days, a completely rewritten procedure (using "alter procedure" statement) has reverted to an earlier version after hours of testing and confirmation. In both cases, there was 4-6 hours from successful final testing to discovery of the "reversion."
Since sql server has no memory of earlier procedure versions, I can only conclude:
1) someone restored an earlier version of the db (log says no)
2) someone acidentally ran a script for the old procedure (no evidence for that)
3) the database crashed and was rolled back as part of restore (but alter procedure is a ddl and, as such, is auto-commited so it cannot be rolled back)
None of these 3 possibilities seems to have occurred. Is there another possibility I'm missing?
The following list of actions leads to a corrupt database on SQL Server 2005.
Create a database snapshot
Drop a table in the database
Backup the database
Restore from backup
Revert to the snapshot
I'm not entirely surprised that it results in a corruption, what is surprising is that I can revert from a snapshot after restoring. There needs to be some kind of check to prevent reverting to a snapshot in a case like this. Until SQL Server prevents you from doing it, I'd recommend a best practice is to delete all snapshots before you restore a database so that you cannot do this by accident.
I have installed MSSQL 2005 on my D: drive of my windows 2003 server, but now need to install MSSQL version 2000 b/c I realized I have one less license than I thought I had. I have build no databases, only the default information. Can I install another instance (being the 2000 instance) on the same drive? If I can't, can I install it on the Crive without a lot of issues???? Or would it be easier to remove 2005 and just start from scratch? The sql version is 9.0. Any help or comments would be greatly appreciated.!!!!!
I have a SQL Server 2008 R2 database.
I have modified the autogrowth configuration several times. The strangest thing... they keep reverting back to the original configuration.
Hi I've got a sql server 2000 database that when running is runnign fine. About 9 months ago I altered one of the stored procedures and ever since then when the machine is rebooted the stored procedure is "reverted" back to the old sproc... ???
is there any way I can recrete a sproc in a job that runs every day?? why would it be doing this?
I have two inline selects against a table with a nonclustered columnstore on SQL 2014 (12.0.2000). Both execute in batch mode and when I inner-join the two, they continue to execute in batch mode. When I cross join them, one executes in row mode. Below is some SQL to simulate the issue.
-- The purpose of this script is to demonstrate that
-- two queries against a columnstore index that each execute in batch mode
-- will continue to execute in batch mode when inner joined.
-- However, one of the queries will execute in row mode when cross-joined.
-- Create function to return 0 to n rows
IF OBJECT_ID('dbo.IntCount') IS NOT NULL
DROP FUNCTION dbo.IntCount;
[Code] .....
Hello,I'm trying to create a simple back up in the SQL Maintenance Plan that willmake a single back up copy of all database every night at 10 pm. I'd likethe previous nights file to be overwritten, so there will be only a singleback up file for each database (tape back up runs every night, so each daysback up will be saved on tape).Every night the maintenance plan makes a back up of all the databases to anew file with a datetime stamp, meaning the previous nights file stillexists. Even when I check "Remove files older than 22 hours" the previousnights file still exists. Is there any way to create a back up file withoutthe date time stamp so it overwrites the previous nights file?Thanks!Rick
View 5 Replies View RelatedSince as soon as you extend your mdx datasets manually you can no longer switch back into design mode without losing your changes, right?
If that's the case, is there some way to disable design mode completely? i'm finding that the GUI has the tendency to SILENTLY revert the dataset editor back to design mode while I'm busy editing a layout, thereby losing my carefully crafted MDX.
New to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.
When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on the roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?
I need to run two reports each of A5 Size to run back to page and print on single A4 paper means in 1st half Sale bill will be printed and in second half Gate Pass Will Be Printed both report will be on same page and size and shape should be maintained. How to do it.
View 4 Replies View RelatedHello,I am hoping you can help me with the following problem; I need to process the following steps every couple of hours in order to keep our Sql 2000 database a small as possible (the transaction log is 5x bigger than the db).1.back-up the entire database2.truncate the log3.shrink the log4.back-up once again.As you may have determined, I am relatively new to managing a sql server database and while I have found multiple articles online about the topics I need to accomplish, I cannot find any actual examples that explain where I input the coded used to accomplish the above-mentioned steps. I do understand the theory behind the steps I just do not know how to accomplish them!If you know of a well-documented tutorial, please point me in the right direction.Regards.
View 2 Replies View RelatedWe have an issue with a 3 node SQL 2012 Always on availability group. Normal operation is node 1 (primary replica) with node 2 and node 3 as secondary replicas.After some patching, SQL wasn't running on node 1 hence the AG flipped over to node 2. This went unnoticed for some time and the transaction log for one of the AG databases became full on node 2 and node 3. (I think this is because it couldn't commit the transactions on node 1 so couldn't truncate it's t-log?) The DB is using synchronous replication btw.So I started SQL on node 1 and flipped the AG back to node 1 (with a data loss warning but I accepted this).Now the issue is that on node 2 and 3, the DB in question is stuck in a "Reverting / In Recovery" State. I've tried various commands such as ALTER DATABASE SET ONLINE, RESTORE DATABASE WITH RECOVERY etc but these fail stating unable to obtain a lock on the DB.
The weird thing is that on node 1 the state of the DB is "synchronised".how to resolve this issue on node 2 and 3? I've left them overnight (in case they were rolling back transactions, the DB is fairly large) but nothing seems to have happened. remove the DB from the AG in node 2 and 3 and add it back in again, ie recreate the replication?
Does anybody know of a way to rollback SQL Server 2005
databases back to SQL Server 2000? Is there a way of doing it without
resorting to Copy Database Wizard? I love to find a way of attaching a SS 2005 database
to a SS 2000 instance without any issues.
I recently upgraded to SS 2005 and I am very unhappy with the SS 2005 and I
want to rollback to SS 2000, which was a lot more stable. I am having
several major issues that are affecting my whole company's day-to-day
operations and the managers are not happy. Some of the issues include
night time batch running very sluggish for no apparent reason. This is a
biggest problem because it only occurs once or so a week and causes a disturbance
with the daily activities when the night time processing isn€™t completed on
time. The rest of the time, the batch processing runs great, even a little better then on SS 2000. I
don't believe it is a matter of my application needing to be retuned because if
that was the case, then why isn't it running sluggish every night? Also,
it's never the same day that the sluggish behavior occurs. If it was occurring
on the same night, then I would have something to investigate within our
application, but it doesn't. Another issue that I am having involves a
night time job that restores a copy of the production database to the Data
Warehouse server to be used for updating the data warehouse. Again, most
of the time it runs great (~2 1/2 hours), but once or twice a week, it goes
stupid and takes 6 1/2 hours for no apparent reason. Again, it is not happening
the same day either, which could give me something to invesigate. On SS 2000, this same job ran flawlessly. Never I did I run into situation that the
database restoration took that long to run. Even another issue involves a SQL Server Agent Job that was put into suspended
state. What's a suspended state and how can I get it out of suspended
state? I can find no information about suspended state in BOL. I
did a Google and nothing came up. If this suspended state was put
in for security reasons, great, but then tell me how I can remove the suspended
state. I am also not happy with the
fact that I can't get accurate information about the queries that are actively
running at that particular moment. In SS 2000, when I noticed high CPU
usage on the server, I would run the sp_who2 active stored proc and it would
show me all the active thread and how much CPU it was consuming. I would
then find the running threads with the highest CPU numbers and investigate the
query and see if we could improve it. Now in SS 2005, I get in the same
situation and run the sp_who2 stored proc, and there is no smoking gun.
All of the active threads are showing very little CPU usage, which I am very
suspect of. What the heck happen to sp_who2? I looked at some of
the other ways of looking at running processes (i.e... sys.sysprocesses) and
they don't appear to be giving the information that I need.
I am very unhappy and I just want to roll back to SS 2000 and wait a couple of
years before I upgrade to SS 2005.
Dave Brown
What is the simplist way to back up SQL database?
View 1 Replies View Relatedanyone try to convert a 7.0 database back to 6.5? Is there a way to move the data back or does the 6.5 to 7.0 upgrade change field identifiers or anything else prohibiting the move?
Thanks
How to back up DTS package?
View 1 Replies View RelatedHi all,
We need to restore a back up of database from one machine to another machine. The sql server's are running separately on two of the machines.
We rebuilt the MASTER database on the machine which needs to be restored as there was a conflict in the LOCALE and SORT ORDER.
We rebuilt the Master using the RebuiltM.exe
After that we successfully restored the database. AS BOL suggessted that MSDB and Model had to be restored, thosee two were also done.
But we are not able to see all LOGINS / USERS that were there in the original database.
Are we missing anything that will show up all the users once we restore database from the dump.
Help.
-Rajamani
Hi all,
what do i do in the event that my disk is full during back up? Is there anyway to plan for this ahead of time?
thanks!
Please help.
We need back up database on remote SQL Server backup device,
because first one doesn't have space for backup file.
Give mi hint what to do.
Thank you very much,
Alona
hi,
how do we take back up of data from a table to a text file.
write data from table to a text file or excel sheet.
thank u
lak
how do i back up my database
View 2 Replies View RelatedHi all,
I know how to backup database form full back, diffrential backupp. I ' d like to know how to restore database from transaction log file not from backup file.
Thanks fro reading.
hi,
whats the best way for me to back up an instance within my sql server 2005?
(all databases within instance)
thanx
What is the best practice for a SQL back up? We are installing Symantec Backup Exec. Is it really necessary to pay for the SQL backup agent? What does the agent do for you that the regular backups within SQL doesn't give you?
View 9 Replies View RelatedHi,
How do we take Transactional Log Backup using sqlserver 2000? Can we take only transactional log back up?
I wrote a stored procedure. It work properly.
But I want to catch if any error occurs while executing it. And I want to make roll Back on error .And send the error OUTPUT
How can I roolBack the command below and How can I send the error over OUTPUT parameter?
execute(@cmdS)
Thanks.
As I tested, I found with Transaction logs and Full Backup can support any changing of database.
So I doubt that Do I need to back up by diffrential style ?
Please suggest me.
Hi,
I am trying to connect to a SQL DB and pull data from it. But i never get back any rows. I have run the query, it works fine and i get back rows. But htis does not seem to work. HELP ?
Attaching my code
TryDim ConnStr As String = System.Configuration.ConfigurationManager.ConnectionStrings("Engineering_Health_SiteConnectionString").ToStringURLConn = New Data.SqlClient.SqlConnection(ConnStr)
URLConn.Open()
Dim param1 As New Data.SqlClient.SqlParameter("@FG", Data.SqlDbType.NVarChar)
Dim param2 As New Data.SqlClient.SqlParameter("@SS", Data.SqlDbType.NVarChar)
Dim query As String = "Engineering_Health_Site.dbo.usp_GetSiteURL"Dim comm As New Data.SqlClient.SqlCommand(query)
comm.Connection = URLConn
comm.CommandType = Data.CommandType.StoredProcedureparam1.Value = Me.DropDownList1.SelectedValue.Trim
comm.Parameters.Add(param1)param2.Value = Me.DropDownList2.SelectedValue.Trim
comm.Parameters.Add(param2)Dim adap As New Data.SqlClient.SqlDataAdapter(comm)Dim dt As New Data.DataTable
adap.Fill(dt)For Each row As Data.DataRow In dt.Rows
Response.Write(row(1).ToString)
NextCatch ex As Exception
Response.Write(ex.Message)
Finally
URLConn.Close()
End Try
Hello all ASP'ers, I'm very familiar with .net code and data access as it is what I do for a living. However, what I don't do a lot is make my own web project from scratch. All our DB connections are handled through a DB class that we instansiate and use, quite easy. BTW, we hit an Oracle DB and code in C# in VS 2008 Team Developer.Here's my (simple I hope) dilemma.I'm using VS2008, C#, SQL server database. I put a text box and a button on the front page (of my web page) for users to enter their email address for a newsletter. I created the DB by right-mouse-clicking on my project icon in the Solution Explorer, add new item, then selected SQLServer Database, it and saved it. It now appears in the Solution Explorer. I've added the table and columns I need but for the life of me I cannot connect to it. I've seen TONS of connection string examples but I need help getting the proper syntax for my situationI have not created a user ID or a password for the database. What do I put in for the DataSource and the database?I dragged the database icon into my code and got the local path wher it resides:C:FilesWebProjects2008WbProjApp_DataNews.mdfHere is the most common connection string I see for SQL Server:SqlConnection Conn = new SqlConnection(" + "Data Source=Your server name;" + <------ What goes here?"database=Your Database Name;" + <------ What goes here?"UID=UserID of the server;" + <------ Can I get rid of this?"PWD=password of the server;"); <------ Can I get rid of this?I've done a different project using an Access DB and have that guy up on the web working great, I just can't get htis one connected and the data a' flowin'.Please help, this is my personal website and I want to use SQL Server but I'm getting frustrated and just might go back to Access, aaaaggghhhhhhh...Once I get past the Conn.Open(); in a try/catch I'll be good. I've got the rest of the data commands in place and ready to go. Here is a link to a visual to help you see what I'm doing, it's just a shot of my solution explorer.
View 3 Replies View RelatedCan any body help with sample code to backup (sql server 2005 express)database using VB.net on click of a button?
Thanks