Actually I have very big size of databse. Also its back up is obvious that also biger size.
In case of only data loss of one table or view or sp or change then restore whole database then get back previous backup database & restore table i.e. it consuming too much time.
I want to restore only one table or view in faster way. how i can do it. plz give any suggestions
What is the best way (short of backing up the entire DB) to make a copy of a Table so that It can be easily restored. We have a table that we want to make some serious changes to, but I want to make sure I can restore if if I need to (if the changes don't work)
View 3 Replies View RelatedI have a table called Lab_results and I have backed it up to "lab_results_backup032215". how do I restore the back up to the original table. I tried :
select * into Lab_results from lab_results_backup032215
But get error saying the table already exist?
Hello,
I'm in trouble, I have just dropped a table from a sql dbase and now need to recreate it. I have no backup and I am panicking!!! I need to know if I can recrreate them .
Any and all help appreciated
DVNC
is there a way to restore certain tables from a full backup?
View 8 Replies View RelatedI did a full backup of a db from one server(Express2005) and trying to restore it to a different instance of SQL2005 on the same development machine. (Also had some fulltext columns if that means anything)
Many failures but finally got it to report all was successful except the icon in Object Explorer shows (Restoring...) with no indication of any real activity going on. It's a tiny database with hardly any data in it.
Just not sure what the heck is going on there. It also won't let me into the database until this the (Restoring...) goes away.
Any advice on how to get this thing finished out?
With all the new functionality, can 2014 now restore a single table from a standard backup without using any third party tools? I have looked, but can't see this listed as a feature (though that doesn't mean it's not there, maybe I've just missed it).
View 6 Replies View RelatedWhen I run:select * from testtableI get this error message:Invalid object name 'testtable'.However, if I run:select * from testuser.testtableIt works. I'm logged in as testuser, and testuser is the databaseobject owner and tableowner. This was a restored database from adifferent server in SQL Server 2000. The testuser login was in theprevious database. I created the same login in this new test databaseand made it database owner.I've also tried running: sp_change_users_login 'Update_One','testuser', 'testuser'It completed successfully but I still get:select * from testtableInvalid object name 'testtable'.I've also tried creating a different user and making it database ownerand when I change table object owner to this new user, I still get thesame problem. I need to specify:select * from newtestuser.testtableEven though I'm logged in as newtestuser.I also then tried changing table owner to dbo. This seems to worklogged in as either user, but I want to have the tables owned by theuser not dbo. How can I resolve this?Any help would be appreciated.Erin
View 1 Replies View RelatedHi everyone,
I use sql 2005. What is the best practice for dealing with large table (more than million rows)? Table Partition, View or other?
Can you please give some suggestions? It will be very helpful if you can post some references or examples.
Thank you!
I need to recover some data in a table but i'm not 100% sure the right way to do this safely.
I'll need to query the two tables to compare the before and after but how do i go about restoring/attaching the backup database to SQL without causing conflicts?
If I restore, I assume this would just overwrite which is obviously the worst thing that can happen. if i attach the backup, how does this affect the current live DB? how do i make sure that it's not getting accessed and mistaken for the live DB?
The SQL server is 2008 R2 running as a VM.
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid
there is a table - TableA in my DB
which iam using in a stored procedure now the table is normalised and the same date is spread in 6 diff tables.
my admin is creating a View which is just like TableA.
my question is do i have to change anything in my stored procedure as TableA is a view and not a table anymore.
HiComplete newbie to sql server. I have a Companies table from which I wouldlike to create a Clients view as follows;SELECT ID, Company, CompanyTypeFROM dbo.CompaniesWHERE (CompanyType = N'Client')Such that when I insert a record into this view it automatically setsCompany Type to Client. Just so I can treat the view as just another tablewithout having to worry about setting the correct company type. Is there away to do this either via views or some other way?ThanksRegards
View 1 Replies View RelatedIf anyone can help... I have a function that takes one parameter andbring back quite a lot of records. but with a conventional function thefield size has a limit of 8000 and i can not use field type 'text'. thelimit has been reached and the view does not bring back results. anoption i found was to use a table function. but with my limitedexperience with table udf's, I am failing to use it within a view. I ampassing a value from the view as a parameter to use in the function andit doesnt work.Please help.*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies View RelatedI am in a scenario where my tables are refreshed every morning by a batch update. I have built a few views off of one table. To increase speed I would like to take all the rows from one of the view s and insert them into their own table. I know this can be done with some T-SQL but I'm a noob to it and don't know how to specifically do it.Any detailed help would be greatly appreciated. -Nate
View 1 Replies View RelatedHi
I'm designing a database for my final year project at University, and have run into a bit of a dilemma with one of the tables. Basically, it's an equipment loan database where both students and lecturers need to be able to book and borrow equipment. To avoid having two separate bookings tables (i.e. StudentBookings and LecturerBookings) I've got students and lecturers combined into one table called 'Borrowers'. The trouble comes because the clients want the lecturer storing in the booking information table, which can get quite messy because lecturer information is stored in the same table as borrower information meaning that I basically need to relate both the Booking.BorrowerID and Booking.LecturerID to Borrowers.BorrowerID.
In theory, I think this could be solved by creating a View called Lecturers that pulls the required information for Lecturers out of the Borrowers table and then link Booking.LecturerID to the ID of the view. In practice, I've got a couple of queries:
1) Is it possible to perform a JOIN on a view in the way that I would need to here?
2) To keep things nice and clear, is it possible to 'rename' the BorrowerID to LecturerID whilst creating the View?
Sorry if some of this isn't too clear. Please ask any questions to clarify what I mean if you need to.
Cheers
Jon
Normally i can use Web Matrix to connect to a SQL table. e.g. Dim queryString As String = "SELECT [MyTable].* FROM [MyTable]"Is is possible to connect to a SQL View. The reason is because i have generated a view using UNIONS and JOINS and some columns are generated by concatenating columns together (ie.. has alias columns).I also need to use VB.NET to refer to these alias columns.
We have a table created by an application, and a view that joins the table with other tables.
For some reason we are now unable to drop the table or the view. In Enterprise Manager the drop table dialog comes up, we click 'Drop All' and then the hour glass comes up and never goes away. No errors are returned, the process just never returns control to the client, the same when trying to remove the view. Using Query Analyzer is no different.
However stopping and starting the server resolves the problem for a while, but eventually the same problem starts happening. The table is created, populated and dropped using stored procedures called from a web page via asp script. This process may occur numerous times and hasn't been a problem until the last day or so when the developer added a couple of smallint columns to the table.
Anyone know what could be the problem here?
How do you get the design view of a table in sql server query analyser?
I know in oracle its desc table_name.
Thanks
Hi all,
What's different from SELECT FROM A TABLE to SELECT FROM A VIEW? Which is faster? Can anyone explain my result following?
Example for:
-- TABLE:
set statistics io on
select * from TABLE_NAME
-->Table 'TABLE_NAME'. Scan count 2, logical reads 106, physical reads 0, read-ahead reads 0
-- VIEW:
Create view VIEW_NAME as SELECT * FROM TABLE_NAME
set statistics io on
select * from VIEW_NAME
-->Table 'TABLE_NAME'. Scan count 1, logical reads 53, physical reads 0, read-ahead reads 0.
Hi,
How can I hide all the system tables so that only user tables show in table view.
Thank you very much!
John
Can anyone help me with displaying several table properites at once. I know I can use sp_help 'tablename' to get one at time. What is the best method to get several databases at a time?
View 5 Replies View RelatedHello
I am trying to insert records to view table.
Msg 4405, Level 16, State 1, Line 6
View or function 'table1' is not updatable because the modification affects multiple base tables.
table1 is view table
My Synatx is
insert into table1
select table1.col1, table2.col2
from table2, table3
where table2_id = table3_id
what is wrong with these.
Thanks
We would like to copy sensitive prod data into a devleopment environment and use a view to alter some of the sensitive rows. (incl. ssn's)
We have views that will "scrub" the data and substitute ssn's with an alternate key for R.I.
Some would like us to use replication to copy from our scrubbed views to our dev tables. Anyone have suggestions on how to accomplish this?
Only ops has access to distributors on-site, including my laptop :( so I can't do much experimenting and have to back-seat drive.
I am working on a program for the Pocket PC platform, which will piggy-back on a helpdesk program which stores it data in an SQL server. I have no problems with getting information from the server, or any connectivity issues.
I have found that it is easy to synchronize an access .mdb file, or an ODBC Source to a .cdb file on the PPC. I have a data provider for the SQL database in question, and the "Import Database" function works fine; it pulls up a listing of all the tables and fields in the helpdesk database.
My problem is as follows. The table used to store the memo information is at least 20-30 megs. I only need a few key entries from that table. I also need to make the sync process as simple as possible (that's why i was looking into activesync), but there does not appear to be ANY way to import an SQL View, as opposed to a table.
SQL CE appears to be one possible solution, but we are running SQL 7, and the CE server tools require SQL 2000.
So, is there a way to trick activesync into sync'ing a VIEW instead of a table?
Do you have ANY OTHER ideas, which would be simple come time to sync?
I have a table that has thousands of rows inserted daily (rows are seldom updated or deleted)
The table is also involved in frequent non-simple select statements. It currently has about a million rows.
Out of the 15 odd columns in the table, I can see about 6 that would benefit being indexed to speed up the select statements.
Before I do this, I was wondering if people think that perhaps I should create an indexed view that all select statements use, rather than adding indexes directly to the table.
Can anyone advise me the performance benefits/disadvantages of indexed views over indexed tables?
Thanks
problem:
Have a table with Data and Dependencies(Foreign Keys) and Stored Procedures, views etc.
Need the Data in that table to put in different order.
For exampl, put older years in the begining or end so when sorted by year, you will get right data.
Question is: If i use view it will be based on the table with Wrong entry order and if i use Temporary Table
each time stored procedure is run, it will be created and overhead.
Example of the table is below with Wrong order.
I should have entered the old years first.
Note this is example and not the actual table!
ID Yr Title/Model Serial#
---------------------------------
1 2005 Toyota Camery IXp12365555
2 2006 Honda Accord XJi9770009
3 2007 Honda Accord XJi9000009
4 2004 Honda Accord XJi9880009
5 2005 Honda Accord XJi9009009
6 2007 Honda Accord XJi9078009
If this example is not right, my bottom line is this:
I have entered the Data in a table and i'm getting wrong resutls when i order by Desc or Asc and now either have to delete all rows and re-enter it or have someother clever way!
I have two tables, a data table (MainTable) that contains a user initials and other empty rows and then i have a user table.
I also have an inner join view with the initials from both tables as the joining field.
I want the users to be able to modify the data table (MainTable) but also display the users name field.
here is the sql of the view:
SELECT dbo.MainTable.DateCreated, dbo.InitialsListing.Initials, dbo.MainTable.CustomerNumber, dbo.InitialsListing.AdvocateName,
dbo.MainTable.Supervisor, dbo.MainTable.Complete, dbo.MainTable.FirstNotify, dbo.MainTable.SecondNotify, dbo.MainTable.ThirdNotify,
dbo.MainTable.AdvocateInitials
FROM dbo.InitialsListing INNER JOIN
dbo.MainTable ON dbo.InitialsListing.Initials = dbo.MainTable.AdvocateInitials
I am trying to create a view using to three queries below and I get the error message Views or functions are not allowed on temporary tables. Is there a way to do that or is there a way to combine the three queries below so I don't have to use a temp table so I create a view?
--Query 1
SELECT * INTO #MOVEMENTS
FROM [GW_DW].[dbo].[DimStatusHistory] d
WHERE TransferFromToProgram<>''
AND d.Status=12
;
--Query 2
SELECT DISTINCT
[Code] ....
I want to create a view from table j25_teamlist2014 with the 7 columns shown below. But I only want the rows which don't have a Status of 'Not Available'.This sql works fine apart from the Team Name and Team Captain columns display the team which is an interger ie Row 1 shows 1 in the Team Name and Team Captain columns instead of 'My Team' and 'Jo Bloggs'.Also when viewing the original table and the new view the structures are slightly different.
CREATE VIEW j25_availableteamlist2014 AS
SELECT Team, Day, Time, Lane, Team Name, Team Captain, Status
FROM j25_teamlist2014
WHERE Status NOT LIKE 'N%'
MVP's and the likeI am looking for suggestions , confirmationLet me start by saying bar none, performance is paramount with thequeries to be retured off this view/table query.To that end I am completley donormailzing the data into a view (or atable) with all possible legal combinations ( I have seen this refferedto as Croation Method ?) Its ends up at 3 total rows per account onaverage.THis query is to be executed only from the web, so once again theperformance is paramount.Ok here is what I have so far I have a set of related tables 5 or sothat are for the most part 1 to 1 but in the case of one particulartable its an average raqtion of 20 to 1.Broken all out with 150k customer rows it ends up at 500k total rows inthe viewAbout 7 cols are dynamic aggregates (Sum(tran_AMT)) , min(tran_amt) ,etcI had it in a view and with 1/5 million rows (In the view) it performedadequatley, I will most likeley get to somewhere around 10 milliontotal rows (In the view)I looked into indexing it but thats not going to happen because I amdoing several subquery's , outer joins etc.The View with a Select * returns in 40 SecondsNow i ran into an issue with recursing this query into several seperatesubqueries and hit the 256 table limit, after only about 15 recursions.Now the data only gets updated once , perhaps twice a day, in a batchtransaction.So I Put all the data from the view into a table, and indexed that, Ican return all rows in 12 seconds and any refining conditions I throwat it, like Date > 12/1/2003 (then it executes in under 6 second_ onlyslices the query time down big time (A very good thing)Since its only updates 2x a day I drop and recreate the table in my DTS, and I have triggers on the other tables for Online updates (usuallyno more that 100 a day) but to ensure the table is ALWAYS current itgets recreated on import of additional rows.Is there a name to this maddness ? My co-workers aree leary of it butthey come from a dbase background, denomailzing it into a table andthen indexing the table I couldnt be happeier with ther performance.Are there any lurking gremlins in this design ?I cannot see any pitfalls with doing this , or are there some ?,basically I am denomailizing for read-only query performance.They are concerned about the denorilization for performance, eventhough its only on a read only table (was and could be a view I justcant index this particualr view)So I guess Im looking for a , "Sounds OK" or "Sounds Great" from theSQL Gods ....Chris
View 2 Replies View RelatedIt is possible to drop the table without dropping the view referencingit. How do I force integrity?Madhivanan
View 9 Replies View RelatedI have a list of users that I want to restrict access to tables in a database. The goal is to allow the users to use select statements on the views instead of the tables. How can this be accomplished?
View 14 Replies View Related