Hello all,
Is there a way to do a selective restore? I need to create a dev db and the prod backup is 17 gig and I have a space crunch on the server. My idea was to restore the complete backup and subsequently shrink the db size after truncating data in tables which are not frequently used? Is that a good idea. Any help will be appreciated.
Thanks in advance!
HI,
Is it possible in SQL Server to restore Databases partially?
In the sense, I want to backup just 2 tables from a database & restore them, instead of having to take a backup of the entire database & restore the entire database.
One way to do would be to export data & then importing data for only those selective tables, but I dont want to do that, bcause that would probably take more time & can be error prone.
Thanks
Raman.
Hi. I have a SQL statement currently ending with "ORDER BY sc.TypeID, sc.ObjetID DESC"where the TypeID can be of value 1,2,3,4,5,6,7,8, 9 and 10. What I would like to achieve is to selectively order the datas by TypeID, meaning in this case that I want ALL the TypeID of value "1" to come first and then all the rest ("2" and higher) un-ordered. This is important that the rest remains unordered by their TypeID as I only want them to be ordered by their ID (ObjetID DESC). Is that possible? If yes how?Thank you.Francis
View 4 Replies View RelatedHi,
I need to be able to export from an existing database, all fields with a certain column id. I have been pointed in the direction of bcp, however I am having difficulty finding the right syntax.
Thanks,
D.
(P.S. MSSQL 2000 Server)
I have
table T2
id amount ss
1142
2151
3162
4172
table T3
idamount ss
131
142
251
362
472
481
491
I run
UPDATE t2, t3 SET t2.amount = t3.amount
WHERE t2.id1=t3.id and t3.ss=1;
T2 becomes
ID1amount ss
132
251
3162
492
but I want t2 to be
ID1amount ss
132
251
3162
4 82
how can I do that?
Thanks
Is it possible to use replication for inserts and updates only for some tables and inserts updates and deletes on others. I want to be able to delete data from the source database but not my replicated database. Does anyone know of a way to use replication to facilite this?
Thanks for any input you can provide.
I'm in the current mode of migration from WIn2k and I 'm trying to setup user where they are only allowed access to one table.
If I grant thenm read /write access to the db they can obviously see all of the db due to the size of the db I do not wish to go down the root of denying every table as there is over 100. Is there an easier way??
I was trying to use the case statement in where clause so that the comparison (> , <, =) can be made depending upon the value of the local variable @fl.
PLease tell me how to do that. Or suggest me how can I make selective comparison (<,>,=) in where clause depending upon a local variable. Please note that I donot want to use if/else satement and write select statement everytime based on the local variable as in that case the code length will be huge. Please suggest me a coensized query.
SQL that I queried:
use pubs
go
declare @fl int
select fl = 3
select * from titles
where case fl when 1 then price > 90
when 2 then price < 90
when 3 then price = 90
end
The following error is given
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '>'.
Can I transfer data from one database to the other for only selected records
pls help
thanks
sunthar
I need to 'copy' selective (filtered) rows from table 'A' to table 'B' on a regular basis, but only if they do not exist in table 'B'.
If rows get deleted from table 'A' they must remain table 'B'. If rows change in table 'A' (unlikely) they should be update in table 'B'
What feature of SQL should I be using?
- triggerering a stored procedure on Insert
- replication
- SSIS
Hi.
I am using Visual Studio 2005 professional and SQL Server 2005 Express Edition.
I am having a bit of an issue at the moment with my project. I have a large SQL server database (over 600,000 rows of data decoded from a text datafile and stored by my C# program) where I need to select a series of distinct rows.
I have a datacolumn of varchar(10) called UID which is assigned to every row in my database. It's not a unique code so it can happen several times. By selecting a specific UID code, I can narrow down my selection to a specific range of rows (which are incremental in order). This range will contain many names.
What I want to do is find a UID which will have one of two names. I have so far done this:
SELECT DISTINCT F1.UID FROM MyDataBase F1
INNER JOIN MyDataBase F2 ON
F1.LineIndex < F2.LineIndex AND
F1.UID = F2.UID AND
F1.Names IN (Bob', 'John', 'Peter', 'Sarah', 'Anne', 'Stewart', 'David', 'Alan', 'Linda') AND
F2.Names IN ('Bob', 'John', 'Peter', 'Sarah', 'Anne', 'Stewart', 'David', 'Alan', 'Linda')
This will return any UID which contains at least two of these names. The LineIndex ensures that the comparison doesn't happen on the same row of data (it's a column set to type int with IDENTITY(1,1) which is also my PRIMARY KEY). This is partially what I want, the selection of UID rows which will have at least two names from the list. Also, the same name will never appear twice in the same UID list.
Now, the problem I am facing is that I want a select a UID range with names in the order I have specified, so if I want a UID list where the names I want are Peter and Linda, I only want to return a set of UID rows where Peter appears earlier in the returned rows than Linda.
It is possible that other UIDs have the same names, but in reverse order, starting Linda, Alan, David etc...John, Bob. I would want to discard these entries.
Can anyone please give me any hints as to how I could get around this?
Thanks in advance,
Sean
I've got a report linking to a SSAS cube. It has 2 row groups Region and Country. The Country group is a subset of the Region group and works fine, it collapses and expands ok.
Now, due to a boss who doesn't like the way this looks, I have to find another way to display it. I was wondering if it is possible to have a report parameter that could be selected and the appropriate column grouping be displayed depending on the value.
i.e. User selects Report Parameter value 'Region' and the Region row group becomes visible, the Country group becomes invisible. The exact opposite happening if the user selects Country.
I can't find any obvious way to do this. Any ideas?
Rob
Hi,
I am trying to do selective updates for rows where a column matches a column in another table. I want to do something like this, only 'this' does not work, and nothing else I could think of (I tried joins also) worked. What am I missing? I hope this explanation makes sense.
UPDATE queryresultsmodel SET queryresultsmodel.tableforcedoutdate = getdate()
Where Exists (Select tablename from queryresultsmodel q inner join orphanul o on q.tablename = o.name)
Thanks for any help,
Judith
We have a Table that contains a schedule. The schedule has certain pieces of information that are required to be updated by certain members of the
organization, specifically Shift, Start Date, and Line. Each record also has a column for the customer that it is for.
The schedule has a Column, [CSR] which lists the name of the person who is responsible for updating it. Format of that is domainnamejohndoe
What we would like to do is block domainnamejohndoe from being able to update or insert on any of the records that he is not responsible for.
So if he attempts to change the shift for example, it denies the change and possible pops up an error indicating that the change was blocked because he is not the responsible party.
Input in this is through a query which views the table data in MS Access. It cannot be moved to a Form without some serious redesign, so I cannot put the block in there. I assumed that this could be blocked with a Trigger that checks the [CSR] column, matches it to the logged in user through the suser_sname(suser_sid()) and then either allows or denies the update.
So is this a viable idea, or should I explore the move to an input form and make the change there in MS Access via VBA.
At first we started with just an audit trail, but I started to think that the audit trail would be needed only for records that should be allowed to be changed and that we should deny changes to those who are not allowed to make them.
Below is the audit trail trigger, I figure that the change should occur somewhere within that first IF statement, or make the first IF the second, and the first should be the permission check.
At this point I am stuck as to how to block the updates. I tried using Deny but that was not working. Filtering the results by only showing the logged in user their records is not an option as they need visibility to the entire schedule.
This is for SQL 2000 SP4/Windows 2000 SP4
CREATE TRIGGER audit_mschange
ON dbo.T_PP_Table_2
FOR update AS
IF (UPDATE([Start Dt]) OR Update(shift) or Update
(comments)
or Update(status) or Update (line))
BEGIN
INSERT INTO T_MS_RB_AUDIT
(ord_no,line_tank_old,line_tank_new,shift_old,shif t_new,comments_old,comments_new,status_old,status_ new,trx_dt,trx_username,
start_dt_old,start_dt_new)
SELECT ins.[PP Ord No],
del.line,ins.line,
del.shift,ins.shift,
del.comments,ins.comments,
del.status, ins.status,
getdate(),
suser_sname(suser_sid()),
del.[Start Dt],
ins.[Start Dt]
FROM inserted ins, deleted del
WHERE ins.[PP Ord No]=del.[PP Ord No] AND
ins.autonumber=del.autonumber AND
(ins.status <> del.status OR ins.[Start Dt] <>
del.[Start Dt] or ins.status <> del.status
or ins.comments <> del.comments or ins.line <>
del.line )
END
Say I have a report with 3 pages, each page has a different dataset and takes some processing time.
is it possible to give the user the option to just run the first page say if thats all they need?
Reduce the processing time of the report to only that that is necessary?
Hi,
I have a problem that I would like help with.
I have to write an SQL Server 2000 stored procedure that returns rows from a table (a SELECT with an ORDER BY). A front end system calls the stored procedure and displays the returned rows. If there is more than one screen's worth of data (ie more than 20 rows returned from the table) then there is a requirement that the stored procedure only returns the rows for the screen ie, for screen 1 I need to return rows 1-20, for screen2 I need to return rows 21-40, screen 3 = rows 41-60.
The screen number will be passed into the stored procedure so I can work out what rows to return, the only problem is how can I tell SQL Server to only select the required rows (ie, say rows 21 to 40 from the returned rows). I can't see any arguments that allow selective rows to be returned (apart from TOP).
Any ideas??
Regards,
Chris
Dear netters,We are looking to build a SQL Server database that will be hooked up toa Web server (WebLogic or a .NET).The database will house data for multiple customers, and therequirement is to have no customer see other customer data. Web serverwill be responsible for authenticating users (ids and passwords will bemaintained there/possibly stored in the database, but will not beactual database logins) -- and establishing pooled connections to thedatabase, using some sort of a proxy login.Ideally, we do not want to have the database itself visible to "theworld", it should reside behind a firewall, and be accessible throughthe Web server, which will do all authentication and routing.We want to have either tables or views defined in such a way that:1. Users can only see their own data and NEVER can see anybody else's.2. Users' access to the database is logged (this includes updates aswell as queries).3. We would like to provide some sort of a reporting mechanism -- whereusers can form their own queries. So, something like a Crystal or anAccess front end, that users are typically familiar with... The problemis that this requires users having direct access to the database (?),and opens up tables (unless, there is a way to create parameterizedviews that will always restrict access to a subset of records).Any advice on how to best implement this is greatly appreciated.Thank you in advance!
View 4 Replies View RelatedI am beginning design for a project to maintain an on-line archive of historical data for a production database, and then truncate old, rarely-used data from the production database. I am considering a method whereby we (A) replicate data changes from the production database to an archive database and (B) truncate old data from the production database according to predetermined business rules.
I am trying to determine whether SQL Server merge or transactional replication can be used to accomplish the replication portion of this requirement.
Given this scenario, there are two types of modifications that are made to the production database: (1) application inserts, deletes and updates which should be replicated, and (2) truncation deletes (as described in "B", above) which should not be replicated.
My question is: Will I be able to control the replication process such that application deletes are replicated, but "truncation" deletes are not? Could static row filters somehow be used to accomplish this?
Hi all.
I have been banging my head against a brick wall with this for a couple of days now. I am wrighting a custom front end for one of our SQLServer DB's and need to run a select agains the DateTime Field. The field Format is [DD/MM/YYYY HH:MM:SS]. I am tring to get the select to pull all the records on a set date only (keeping the time available but not selecting against it) so that the brass can see at what time on a given day an action was performed. Problem is, the way the programing software works, I can only build the SQL using string information. I have tried CONVERT and CAST both ways (converting the date to varchar and converting the string to date and also converting them both at the same time). Everything that I have tried returns an empty data set when used with the WHERE LIKE criteria (oh yeah, I'm also using LIKE % wildecards to retrive all the times against a specific date) but does populate when no filter is applied.
This is where my SQL is at just now:
SELECT Files.FileID, Useres.UserName, FileStatusLog.StatusCode, FileStatusLog.StatusDate
FROM (Files INNER JOIN FileStatusLog ON Files.FileID = FileStatusLog.FileID) INNER JOIN Users ON FileStatusLog.UserID = Users.UserID
WHERE (((CONVERT(varchar(10), FileStatusLog.StatusDate,103) LIKE '%{variable}%'));
This also returns an emtpy data set when the LIKE is replaced with ='{exact date entry in format: DD/MM/YYYY}', but does return the full table when the WHERE is removed and also when the LIKE is set to just a bare '%' with no other filter values.
Because of the fact that the results are returned when LIKE '%' is used I am not even sure if it is the CONVERT that is causing the problem. I have even tried assigning the escape character manualy to '+' incase the / values of the date in the LIKE variable where escaping other characters. Needless to say this did not make life any better.
As you may have noticed already SQL is not exactly my strong suit, but no one else here even knows what it is, never mind what it's used for, so I got droped with the task. Also - The DB was created by a third party so I have no write permissions to the DB.
Any help with this would be greatly appreciated as it's gotten personal now
Thaks in advance for any assistance
I have a filetable that contains a binary file. I need to do a selective read of the file stored in the file table. I can write a C# CLR function that will open the file, read n bytes the from a starting byte. Or I can write a SQL statement that reads the stream in the filetable into a VARBINARY variable using SUBSTRING beginning at the starting byte (offset from 1) for the same n bytes.
Both give me the same result. However, the SQL statement takes considerably longer to read. I know there is overhead in reading through SQL (interpreted language), but the difference in performance is substantial, and I can only attribute this performance degradation if SQL first tries to "load" the entire stream before it identifies the portion of the stream that it needs to read beginning at the starting byte offset.
I wonder if this is the case or if there is another option to read a stream from a filetable directly through SQL queries that is more efficient.
While migrating Report services in SQL Server 2005 to 2014, I am trying to restore the Encryption Key in RS Configuration Manager in2014. But I cannot click the 'Restore' button in RS Configuration Manager. So if I should be grant more right to do so or any other action?
View 2 Replies View RelatedIn Windows Server 2012. How do I do a System Restore to a previous restore point?I need to install the 64 bit and 32 bit Oracle Client Install for connections in SSIS and to create Oracle Linked Servers.
If you make a mistake it is not fun removing it. Sometimes it corrupts the machine and it is difficult to uninstall since there is not an Oracle Universal installer for Oracle 11g.If you install the 32 bit before the 64 you mess up the machine.how to create a restore point.
I am looking for a SQL Backup/Restore tools which can restore multiple environments. Here is high level requirements.
1. We have 4 DBs, range from 1 TB - 1.5 TB Each Database. When we restore to QA, DEV, or Staging, we usually restore 4 of them.
2. I am looking for the speed to complete restoring between 1 - 2 hours for 4 DBs.
I am evaluating the Dephix Software but the setup is very complex and its given us a lot of issues with Windows Authentions, and failure in the middle of the backup. I used Guess Software many years ago but can't find it on the web site any more. Speed is very important for us mean complete restoring as fast as possible. We are on SQL 2012 and SQL 2008 R2.We are currently using NETAPP Technology and I have Redgate Backup Tool but I am mainly looking for fast Restore Process.
Hello,
Would some one please help me with the syntex on how to run "restore filelistonly" or restore verifyonly" on a SQL backup which has multiple filesets?? My backups locations are as follow:
RESTORE VERIFYONLY
From disk = 'E:syndicated_databank__bkup_01.bak',
'E:syndicated_databank__bkup_02.bak',
€˜E:syndicated_databank__bkup_03.bak€™,
€˜E:syndicated_databank__bkup_04.bak€™, €˜E:syndicated_databank__bkup_05.bak€™
I tried to do a restore with the above, I got error The label 'E' has already been declared. Label names must be unique within a query batch or stored procedure.
Please advise!!
I have seen this before. A 2000 restore fails, leaving the database thinking it is being restored but the restore job failed and errors when it is restarted. EM is clueless. I believe there is a proc to reset some flag. Can you share it with me???
Thanks!
I need to restore test DB from production backup but once it is restored I would need all the permissions of sql logins and windows AD account intact in test Db as it was before.
View 4 Replies View Related11/19
Trying to keep out sysadmins & sa during/between database RESTORE
Configuration:
WINNT Server Enterprise 4.0 w/SP5
SQL Server 7 Enterprise & SP1
2 SQL Servers:
Production Server
Standby server
I Backup (full backup) databases to disk on primary server (logical backup devices are physicaly located on a Standby server (dedicated gigabit NIC in each server for this process). Transaction logs are applied to the Standby server throughout the day.
Problem:
How to keep out "sa" and sysadmins from a database while I'm restoring (or between restores) to a standby server?
The database being restored cannot be in use during a restore.
If a DBA forgets that this process is happening, the statement fails (RESTORE)for the database they happen to be in at the time of the restore.
Example restore statement:
Standby Server -
RESTORE DATABASE databasename FROM database_dd WITH DBO_ONLY, REPLACE, STANDBY = 'g:Mssql7FromPrimaryDatabaseName_undo.ldf'
I could restrict Domain sysadmin access and change sa password. I could also put the database in "Single user" mode, however this could become problem if my process disconnects and then someone else connects - then my process is locked out. What I'm really looking for is to lock out all activity for a database that is in "standby mode" except for RESTORE processes.
Any ideas??
Wade
wadej@vailresorts.com
hi all,
i created a database backup in SQLServer2005 by the following option in one server,
Right click on Database -> Tasks -> BackUp -> And i created in one path.
when i trying to restore the this database on another server i'm getting the following error,
Restore failed for Server 'IPDDFZ2066ATL2'. (Microsoft.SqlServer.Smo)
System.Data.SqlClient.SqlError: RESTORE cannot process database 'xeroworld' because it is in use by this session. It is recommended that the master database be used when performing this operation. (Microsoft.SqlServer.Smo)
please help me how to solve this problem,
Thanks!
Hi everyone,I have an sql server running in my company.if one day the sql stop working for some reason and I want to make a restore, what should i do? what is the best scenario.could u help by any reference or article.Thanks.
View 1 Replies View RelatedHi,
I have a SQL 7 database that uses one MDF, two NDF files. I backed up this database and am trying to restore it another database on another server. However this target database is only using one MDF. I assumed it was possible to use the WITH MOVE option and move the two NDF's into this MDF but it doesn't work.
Anyone know how I can achive this?
Thanks,
Sundar.
How can I if possible restore a backup from tape(taken from databaseA)
to databaseB both have the same structure just different names?
Windows NT 4 EE - SQL 7.0 SP1
We use two backup methods:
1: thru SQL Server jobs (Maintenance Plans),
2: thru ARCServe IT (CA).
I think that there are 4 tables ( BackupMediaFamily, backupset,backupfile,backupmediaset)in msdb that are updated everytime a backup is taken.
This happen even with the ARCServe IT backups.
The result is that when I go thru EM to do a restore operation I find a list of all my SQL backups + the ARCServe IT backups in the "general tab" of RESTORE DATABASE of EM.
Suppose I have this situation:
2001/06/11 21:00 full SQL backup (on disk)
2001/06/12 12:00 full ARCServe IT backup (on tape)
2001/06/12 17:00 differential SQL Backup (on disk)
If I want to combine my last differential disk backup with my full SQL disk Backup,there is no way to do it thru EM, because as soon as I choose the differential backup, EM associate this one with the ARCServe IT backup that is taken on tape.
So, I dont need the ARCServe IT informations on my msdb tables. Is there a way to resolve this problem and thererfore eliminate the ARCServe IT informations on msdb tables?
Thank you very much!
Hi,
I restored a DB and while restoring I selected the Optio - "Leave DB non-operational but able to restore additional T-log".
I want to add more T-logs (one every 2 hours)and at the same time I also want this DB to be operational.
Is it possible to add T-log to an operational DB?Is this not log-shiiping is all about?
I have sql2k/win2k.
Any thoughts?