Restoring Tables To Alternate Location
Oct 29, 1998How do I restore a table to a location other than the table as it exists now?
Did that make sense?
ADVERTISEMENT
Applying Snapshot From Alternate Location
Apr 3, 2006Hi
We are using HTTPS merge replication - is it possible to specify an alternative location when applying the initial snapshot ?
Thanks
Bruce
Alternate Snapshot Location For Transactional Replication
Jun 2, 2006Hi,
I have transactional replication setup with SQL 2000 on W2K3 cluster using updateable push subscribers. While setting up replication, we chose default location for snapshot folder that resides on non-clustered drive. Is there a way to change this location without disturbing current replication setup. I looked at the 'alternate snapshot location' solution, but it requires snapshot re-initialization. I am trying to do this with minimal effort and downtime.
Thanks,
np70
Subscriber Alternate Snapshot Location And Web Sync - 2005 Express
Dec 7, 2005Hi,
View 6 Replies View RelatedRestoring A Db From A Remote Location
Oct 13, 1998We have one development server and another production server. I am trying to restore a production dat file to the development server. I can get as far as adding the path name of the remote file (e.g. serverd$directoryfilename), but I can`t seem to enable the "Restore Now" button. The file actually displays in the Devices and Files section, but when I click it, nothing happens.
There happens to be another pre-existing device that does respond to my click - the device is added to the "Backup Information" section of the screen, and the Restore Now button is enabled - but that isn`t really the one I want to restore from.
Is there a step I`m missing?
Thanks in advance,
Lisa
Restoring Master To A Different Location
Aug 20, 2007I am trying to restore master to a new server but location of data and log files is different in this new server. The previous location was 'D:Program FilesMicrosoft SQL ServerMSSQLdata" the current location is in the C: Drive; when trying to restore using MOVE:
RESTORE DATABASE master FROM DISK ='\PfileserversqlbackupMDFfilesmaster_db_20070 8170121.BAK' WITH RECOVERY ,
MOVE 'master' TO 'C:Program FilesMicrosoft SQL Serverdatamaster.mdf',
MOVE 'mastlog' TO 'C:Program FilesMicrosoft SQL ServerMSSQLdatamastlog.ldf'
I get the following error message:
The system database cannot be moved by RESTORE
Is there a way you can restore master to a different location?
Thanks,
Carlos
Restoring Database To A Different Name And Location
Jan 22, 2004Can someone please tell me what's wrong with my script below? I am trying to restore a database backup to a different database name and location.
RESTORE DATABASE test
FROM 'C:mybackup.bak'
WITH MOVE 'h_data' TO 'C:Program FilesSQL2000MSSQLDataTest_data.mdf'
Thanks
SHK
Restoring Master Db To New Location: Sysnsobjs Err
Jun 15, 2007I am attempting to restore a master database backup to a new location
(new_masterdb) and am getting the following error:
System.Data.SqlClient.SqlError: There is already an object named 'sysnsobjs' in the database. (Microsoft.SqlServer.Smo)
There is empty so I am not sure why it is finding this object. How can I get around this?
Location Of Logging For Temp Tables
Sep 9, 2005I have a stored procedure that creates a temp table that is used toinsert data from a dynamic query. My question is does the logging ofthe insert occur in the local database running the stored procedure ordoes it occur in the tempdb database? My log file for local databaseis growing significantly and I want to rule out that the temp table isnot writing to the local database log. I have done some testing and itappears that is not but I just confirm my findings.
View 2 Replies View RelatedErrors Restoring Tables
Aug 9, 1998A client recently changed his sort order and character set definitions. He is now unable to restore any of his tables. Prior to making the change he carried out the following steps;
1. Printed out the schema(scripts) for each database,
2. BCP`d all data out of SQL.
He the proceeded to make the change (via SQL Setup, rebuild master database option), then he recreated each device (data and log), recreated each database (data and log), ran each schema to rebuild each database (structure, views, etc) and then BCP`d all data back into databases.
He has been doing a full backup everyday since. He needs to restore a table but when he does so SQL reports that the schema is different between source and destination, no changes have been made.
Error When Restoring Tables
Aug 20, 1998Hi !!
I have this problem when I dumped 10 tables to a new-created-dump device.
I dropped the tables and recreated it with the same layout as before.
When i try to load the tables again eith the command "load table from dumdevice" the first table works fine but the second table gets the error message:
"Schema differs between source table......."
Where the source table according to the eroor message is the first table that
I sucessfully loaded though I didn`t mentioned it in the command.
What can I do, Am I not allowed to dump several tables to the same dumpdevice !!!
Please Help
Pederico
Restoring Database Leaves Me With (Restoring...) Message
Jan 9, 2007I 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?
Alternate Way
Jan 29, 2008Hi all!
This is my sql statment that works perfectly
select name,id,case when maths>=35 then 'pass' else 'fail' end as maths,
case when science>=35 then 'pass' else 'fail' end as science,
case when tamil>=35 then 'pass' else 'fail' end as tamil ,
case when english>=35 then 'pass' else 'fail' end as english,
case when social>=35 then 'pass' else 'fail' end as social
from student
I dont want to use case in my querey? Is there any way can i replace
case with something?
Thanks in advance!
Rownum Alternate In MS-SQL
Jul 17, 2007I want to get 100 rows from particular record and onward. in oracle i can use rownum and in mySql i have function limit ... i want to know what is the ms-sql alternate for it.
I want to get 100 rows onward to one particular data ... how can i ?
Alternate Names
Aug 6, 2007I need to implement support for alternate names on the database I’m working on.
When a user looks up: “Antony Bigglesworth” the query would return both: Antony Bigglesworth as well as Tony Bigglesworth, would both exist.
Anyone has a best practice on this or has done this before?
Alternate To Decode
Sep 30, 2006hi i have a view in oracle. in that i am using decode function.
same query i want to write it in sqlserver. what it is the alternate
to decode.
this is a cross tab query
SELECT code, SUM(DECODE(field1, 4, Present_Value, 0)) AS c1, SUM(DECODE(field1, 5, Present_Value, 0)) AS c2,
SUM(DECODE(field1, 6, Present_Value, 0)) AS c3,SUM(DECODE(field1, 9, Present_Value, 0)) AS c4
FROM (SELECT field1,Code, Present_Value FROM table1) DERIVEDTBL GROUP BY code
thanks
suji
Any Alternate Method Available ?
Aug 21, 2007I've a question regarding SSRS 2005.
Is there an efficient scripting method to update the connection string for ALL reports that reside on a reporting/web server? "(automating the process, rather than having to change the data source for each individual report that resides on that server)".
All suggestions are highly appreciated .
Thanks,
Alternate To A Not In Query
Jul 23, 2005-- tested schema below ---- create tables --create table tbl_test(serialnumber char(12))gocreate table tbl_test2(serialnumber char(12),exportedflag int)go--insert data --insert into tbl_test2 values ('123456789010',0)insert into tbl_test2 values ('123456789011',0)insert into tbl_test2 values ('123456789012',0)insert into tbl_test2 values ('123456789013',0)insert into tbl_test2 values ('123456789014',0)insert into tbl_test2 values ('123456789015',0)insert into tbl_test2 values ('123456789016',0)insert into tbl_test2 values ('123456789017',0)insert into tbl_test2 values ('123456789018',0)insert into tbl_test2 values ('123456789019',0)insert into tbl_test values ('123456789011')insert into tbl_test values ('123456789012')insert into tbl_test values ('123456789013')insert into tbl_test values ('123456789014')insert into tbl_test values ('123456789015')-- query --Select serialnumber from tbl_test2where serialnumbernot in (select serialnumber from tbl_test) andexportedflag=0This query runs quite fast with only the data above but when bothtables get million plus rows, the query simply bogs down. Is there abetter way to write this query?
View 5 Replies View RelatedAlternate To Cursors
Oct 1, 2007Hi,I have a situation where I am loading data into a stagingtable for multiple data sources. My next step is to pick up therecords from the staging table and compare with the data in thedatabase and based on the certain conditions, decide whether to insertthe data into the database or update an existing record in thedatabase. I have to do this job as an sp and schedule it to run on theserver as per the requirements. I thought that cursors are the onlyoption in this situation. Can anyone suggest if there is any other wayto achieve this in SQL 2005 please.ThanksSeshadri
View 1 Replies View RelatedAlternate To Sql Web Assistant
Jul 27, 2007I am currently with sql 2k5 and using web assistant to generate some HTML files and email the same automatically to a set of users.
these html files that are generated are processed via a JOB in sql server.
I am in process of replacing of the web assistant procedures.
Is this possible to make this with XQuery of SQL Server 2K5 and convert it into HTML files with Stored Procedure.
Is there any other possible way to do it?
Alternate For The SQL Query
May 21, 2008Dear All
i want to know some alternate for the below query
"Update STR_RGSTR_DFLT " & _
" Set GST_SVY_FREQ_P = " & iNewSurveyPct & _
" Where dhc_co_c = " & iCo & " and store_i = " & iStore & _
" And RGSTR_PROTO_C not in (0,3,4,8,10,11,12,13)"
in this the values in the where clause 'not in' i.e (0,3,4,8,10,11,12,13) are directly given
but we want some alternate solution.
the entire script is writen in VBScript. these values are already populated in an array like proto_code_ary = {0,3,4,8,10,11,12,13}
shall we use that array directly without hard coding the values in the query?
If any one find a solution please let me know.
Thanks & Regards
S.Manikandan
Setting Up An Alternate Login To Sa
Oct 25, 2007In times past I connected my web app to SQL Server by embedding the following in the web.config file: <connectionStrings> <add name="XyzApp_DB" connectionString="Data Source=MyServerSQLExpress;Database=XyzApp;User ID=sa;Password=secret_password"/> </connectionStrings> And it worked fine. But it was pointed out to me that this wasn't a very proper way to do things. So I was advised to set things up with a different connection string: <connectionStrings>
<add name="XyzApp_DB" connectionString="Data
Source=MyServerSQLExpress;Database=XyzApp;User
ID=Xyz_Admin;Password=different_password"/>
</connectionStrings>
To get this to work, I've followed these instructions: A
more secure login than “sa� is required to access your application. In the “connectionStrings� section of
“Web.config� you’ll find a User ID called “Xyz_Admin�. Here are the steps to ensure that your
DB has this login id:
a. Using
the Microsoft SQL Server Management tool, look in the root for the “Security�
folder and open it. Inside that, open
the “Logins� folder.
b. If
“Xyz_Admin� doesn’t exist then right-click on “Logins� and choose “New Login�:
i.
Enter “Xyz_Admin� in the “Login name� textbox.
ii.
Click on “User Mapping� on the left side and in the
‘Map’ column check the box beside “XyzApp�.
iii.
Go back to the “General� page and choose SQL Server
Authentication.
iv.
Specify any secret password you wish.
v.
You can uncheck “Enforce password expiration� if you
wish.
vi.
Specify “XyzApp� as the default database and press
OK.
c.
Open “Databases�, right-click on “XyzApp� and
select “New Query�. Run this query: sp_change_users_login 'update_one',
'xyz_admin', 'xyz_admin'
Now, I was pretty sure that this procedure was working fine but it doesn't seem to work for me today. I know that the client computer I'm using can connect to the DB on the server because I temporarily changed the User Id to "sa" and it connected fine. But when I change it back to "Xyz_Admin" it does not. So I'm thinking that there's something incorrect with the procedure above but I don't know what.Any ideas?Robert W.
Candidate,composite And Alternate Key
Jun 6, 2008Hi What is the difference between Candidate key , composite key and alternate key. I went through many websites but I didn't get examples. There were only definitions. Can anyone please tell me the site or blogs that elaborate this concept RegardsKaran
View 2 Replies View RelatedUrgent - Alternate For Rowid
Apr 6, 2001Hi,
What is the alternate for Rowid (in Oracle) in SQL Server ?? Can u tell me ...
urs
VJ
Alternate FTP Client For NT And SQL Server?
Feb 24, 2000We just erected a new firewall that only allows PASV FTP. Now, my SQL Server can't call master..xp_cmdshell "ftp ...".
Does anyone know how to make the NT FTP client work in PASV (passive) mode or another FTP client that can be called from xp_cmdshell and works in passive more?
Alternate For Linked Server?
Apr 26, 2004I need to join a remote table which has 20 million rows to few local tables on my SQL Server. Currently Linked server query is used but it's just hanging for ever..is there any alternative for this?
Thanks.
Alternate Database Toggle
Mar 19, 1999Hi I need some help on an architecture issue.
I have a production database with input from 3 locations outstation. I need to bring down this server once in a while for tuning and other administrative stuff. Right now I'm in no position to shut the server down. Is there any way I can do this.
Also how can I have 2 databases and toggle between the 2 seamlessly without effecting the users. Does it have to be a mirror on the devices ? What do the professionals usually do
I have heard about clusters. Is this a solution for my problem and what is a cluster anyway ?
Vijay
Alternate Way Of Row Number() Function
May 22, 2008Hi all,
I AM USING ROW_NUMBER() FUNCTION IN MY QUREY IN SQL 2005 BUT IT IS GIVING ME PROBLEM IN SQL 2000.
SO CAN U TELL ME WHAT IS THE ALTERNATE WAY TO ACHIVE SAME RESULT.
MY QUREY IS:
select row_number() over (order by SalesYTD asc) as rownum, CountryRegionName, FirstName, LastName, SalesYTD, SalesLastYear from Sales.SalesPerson
CAN U TELL ME HOW CAN I REPLACE ROW_NUMBER() FUNCTION.
THANKS IN ADVANCE
-JOHN
Alternate Colours On Each Line
Mar 3, 2008I now have a report thanks to help on this site :) but the report is difficult to read.
Is there a way to colour code each line e.g every 2nd line blue or something?
Regards
Ian
Alternate Synchronisation Partners
Jun 8, 2006Yes, I know synchronisation to alternate partners is deprecated in SQL2005 but....
In SQL2000 there is a Sync Partners tab in the publication properties dialog that allows you tick a checkbox for each co-publisher to be enabled as an alternate synchronisation partner. What is the equivalent in SQL2005?
I've set up replication in SQL2000 following these instructions http://support.microsoft.com/?kbid=321176 and it works. Now I'm trying to do the same thing in SQL2005 but I can't find a substitute for steps 10 & 11 in the section "Set Up the Alternate Synchronisation Partner". What's the answer?
Thanks in advance.
SQL DMO - Specify Alternate Database Name When Creating Backup
Apr 20, 2005I'm building an ASP.NET application that uses SQL DMO to automate database dumps. This will be used by our DBAs to simplify the transfer of databases between server environments.
As part of the process, I need to create a backup of an online database and then restore with a different name to run cleanup scripts against it to remove unneeded audit data, etc. I've tried setting objSqlBackup.BackupSetName to accomplish this, but it appears the backup files retain the original database name internally. When attempting to restore the backup, this results in an error saying that the database already exists.
Does anyone have a code sample or link that explains how an alternate name can be specified? In SQL Server Enterprise Manager, there is a field for specifying an alternate name when creating a backup. Given that SQL DMO is supposed to offer the same functionality, there must be a way...
FYI, The full process is as follows: - Create backup of selected database - Restore database with a different name (this is where I need help) - Run cleanup scripts to clear out unnecessary audit data, etc. - Create backup of "cleaned" database - Create zip file of database backup
Thanks in advance for any help you can provide!
#temp Table/Alternate Key Issue...
Mar 7, 2007Hi:
I created following temp table in a proc as
create table #tempOutput
(objID int identity (1,1) primary key clustered,
DriverID varchar(10) not null,
StatusID int not null
constraint AK_tempOutput_DriverID_StatusID
unique (DriverID, StatusID),
Note varchar(50))
I run the proc first time ok. but the second time got the error:
AK_tempOutput_DriverID_StatusID exists already....
after trace to tempdb -----
select * from tempdb..sysobjects
where name = 'AK_tempOutput_DriverID_StatusID'
name id xtype parent_obj
AK_tempOutput_DriverID_StatusID 849652640 UQ 833652583
select * from tempdb..sysobjects where ID = 833652583
Now, the temp table tempOutput becomes
#tempOutput_____________________________0000000002 A1
But I could not drop this table in tempDB,
and I could restart the SQLserver to clean the tempdb at this time.
after I renamed the AK_tempOutput_DriverID_StatusID to AK_tempOutput_DriverID_StatusID_1 in the create sql statement,
the proc recreated and exec works.
Is there a way without restart SQL server to clean the Alternate key?
Is there a way without restart SQL server to clean the temp table?
Also, how to prevent the issue?
This happens in both SQL2005 ent and SQL2000 ent.
I think the exec proc ending should drop the #temp automatically and thus corresponding PK/AK?
thanks
David
Backup/Restore - An Alternate Method?
Nov 4, 1998For a backup strategy, I was thinking of just copying over all the system and user database device (DAT) files (of course after stopping all
the SQL services at a time when no one is accessing the database server). Then at the time of restoration, I would copy the files back to their
original location.
Does anyone see any problems with this strategy?