Hello everyone!
I'd like to know which method is better for making a copy of an
existing database in SQL Server 2000, restoring from a backup of the
original, or using DTS to copy the objects to the new database. Any
caveats or drawbacks to either method? I'm just trying to lay the
groundwork for internal policies for managing our databases, and I just
want to make sure we use the best method.
Thanks!
I want to make a copy of our production database so I can do some testing stuff on it...
I basically want to just save the complete database with a name like testingXYZ.
Can I just do that through the normal file explore manager or is there some SQL Things I need to do?
I have a 6.5 db that I want to create a copy of, I can move it to eithera machine running 7 or 2000. I am more used to doing this in Oracle sopls bear with me...I don't want to have any downtime on the 6.5 db, so I'm thinking perhapsimport is the best way to go? I am assuming that 6.5 backups are notcomaptible with either 7 or 2000 restore, or I'd go that route.The database in question is fairly simple, pretty well just tables fullof data, no stored procs...suggestions?TIA
View 4 Replies View RelatedI need to make a backup of a table so that if I mess it up I can put the data back to where I started...
I am going to insert some new rows and update values in some existing rows. So before I do this and find out I messed it up...how do I make a copy of Table A as Table B?
I would actually like to make a copy to table B, then run my changes into B, make sure all is correct, and then transfer the revised data from B over to table A replacing its data. What approach would be best here to accomplish this?
Thanks
I created my database on a remote server. It now has lots of tables and stored procedures
When I created it, the server created a schema named for me, the user, so all my tables and stored procedures are named like johndoe.tablename.
I would like to rename the schema to something less personal more professional.
Can it be as simple as "ALTER SCHEMA johndoe RENAME professional"?
Also, I would like to create a local copy of the databse, so I can develop offline, without the 2 second delay.
SQL Server Management Studio Express lets me generate scripts, which I have been using to make backups.
Can I use the script file to recreate the database on my local machine?
Thanks
Hi,
I set up DB mirror between a primary (SQL1) and a mirror (SQL2); no witness. I have a problem when I issue command:
alter database DBmirrorTest
Set Partner = N'TCP://SQL2.mycom.com:5022';
go
The error message is:
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.
I have the steps below prior to the command. (Note that both servers' service accounts use the same domain account. The domain account I login to do db mirror setup is a member of the local admin group.)
1. backup database DBmirrorTest on SQL1
2. backup database log
3. copy db and log backup files to SQL2
4. restore db with norecovery
5. restore log with norecovery
6. create endpoints on both SQL1 and SQL2
CREATE ENDPOINT [Mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER)
7. enable mirror on mirror server SQL2
:connect SQL2
alter database DBmirrorTest
Set Partner = N'TCP://SQL1.mycom.com:5022';
go
8. Enable mirror on primary server SQL1
:connect SQL1
alter database DBmirrorTest
Set Partner = N'TCP://SQL2.mycom.com:5022';
go
This is where I got the error.
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy
Thanks for any help,
KT
Please see the detail of tables with fields in SQL server 2000.·TableStudent .Table Good Qualities( It is a list of Students) (It is a list of Good Qualities)1-Student Id (P.Key)1- Serial No. (P. Key)2-Student Name 2- Good Quality· Table Bad Qualities .Table Future Plans(It is a list of Bad Qualities) (It is a list of Plans)1-Serial No. (P. Key) 1- Serial No. (P. Key)2-Bad Quality 2- Future Plans·Table-Personality1-Student Name/ID2-Good Qualities3-Bad Qualities4-Future PlansData will store in Personality table like thatStudent IDG.Q.No B.Q.NoF.P.No101 3 2 1101 4 5 NUll101 8 Null Null202 4 8 9202Null 3 1Required ResultNow I have to generate a report of One student andsometimes of many students comprising of their Name, G.qualities, BadQualities and Future Plan. To generate report User input will be onlystudent id.I Need help in making relationship of these tables as well as inMaking a Query. Any suggestion to get the required result. Speciallyplease look at the Personality Table because I do not understandPrimary key for it. Should I just include the Serial No. as Primarykey.Please make necessary changes to get the required result.I shall be really thankful to you for your kind favor.
View 3 Replies View RelatedHi,
I have formatted my server because of serious problem and i did not backup my database. I have only a phisical copy of the disk containing data on another disk. :( How I can recover my db? Thank you in advance.
Hello Experts,
Can anyone tell me, how can I make SQL server database empty.I mean i need to keep only restore the table structur, no table data.
Environment is SQL Server 2000 64 bit.I restore from a script 'my' database, this works fine. However, allthe tables are also found in master, no data though.Anyone experienced this?
View 2 Replies View RelatedHello,
if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.
Or is the datebase name part of the .mdf file?
Regards
Markus
Hi,
I use Sql server express 2005.I have two databases: db1 and db2.I use Studio Management. I connect with my administrator account 'admin'.
1) When i rightclick on db1 (and also on db2) and i take the properties / general, i see for both db: owner = servernameadmin .When i take properties / Files, i see for db1: owner = servernameadmin , but for db2, it remains empty.why and does it mean that db2 has no owner? But then, why do i see in properties / general : owner = servernameadmin?
2) i want to make 'admin' owner of db2 (for creating a database diagram), so in properties / Files, i click on the button with three dots.A windows "select database owner" appears, i click on button 'browse', but then all i see is:built-in administartorsbuilt-in userssa...
Whatever i choose (e.g; built-in administrators), i get the message: an entity of database cannot be owned by a role, group ... error 15353).
Why do i not get the account 'admin' in the browse windows and how to make 'admin' owner of db2?
Thanks for helpT.
Hi All,
I am a bit new to using MS-SQL, up till now my main adminstration was in Oracle.
I have a question, my developers make changes to my MS-SQL databases, however, they always login as a local admin or use their domain WIndows accounts - with the given privileges. Then they amend the database.
My idea is to avoid logging into an MS-SQL server without using a Remote Desktop. So they will need some kind of a client.
What is your experience and what would be the best way to do this? Meaning, what software can I use that does not require a license fee?
With Oracle I can install the Oracle client which has an SQLPLUS to login remotely. Does MS-SQL have something similar?
Thanks in advance.
I did use Google to search, I also have an MS-SQL Developer Edition, but this seems to come with another server itself.
Regards,
Richard.
Hi
I have copied a sql server backup file. I would like to make it a live database. How would I do this??
Hello
I am trying to maintain a small database on my iPAQ, can somebody tell me that would database be created in SQL Server Mobile on my iPaq and can my VB.net based Windows CE application can access data from the database on my iPAQ
tHNAKS
hello
here is my code int a = Convert.ToInt32(Request.QueryString["ProductID"]);
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand command4 = new SqlCommand("myStoredProcedure", conn);
command4.CommandType = CommandType.StoredProcedure;
DbParameter param2 = command4.CreateParameter();
param2.ParameterName = "@ProductID";
param2.DbType = DbType.Int32;
param2.Value = a;
command4.Parameters.Add(param2);
param2 = command4.CreateParameter();
param2.Direction = ParameterDirection.Output;
param2.ParameterName = "@UserName";
param2.DbType = DbType.String;
command4.Parameters.Add(param2);
conn.Open();
command4.ExecuteReader();
conn.Close();
Label3.Text = command4.Parameters["@UserName"].Value.ToString();
}Exception Details: System.InvalidOperationException: String[1]: the Size property has an invalid size of 0.Line 48:
Line 49: conn.Open();
Line 50: command4.ExecuteReader();
Line 51: conn.Close();
and here is my stored procedure ALTER PROCEDURE dbo.myStoredProcedure
--
(@ProductID int,
@UserName varchar(255) OUTPUT)
AS
SET @UserName = (SELECT UserId FROM Products WHERE ProductID = @ProductID)
what is wrong? i never seen that error and i don't know from where could it came from
please help me,
thanks
How would I hide the sa account to be accessible under Logins for a restricted User in sql server 2005 and also other folders like Replication, Managment, etc need to be hidden too.
any idea,
thank,s
VWD 2005 Express. Visual Basic. Sql Server 2005. I am trying to establish a connection to a SQL database. The connection (and all other database reads and writes) work fine in debug and in "view in browser" from VWD. I am running the VWD on the same server that hosts the site. When on my server (Windows 2003 Server) under IIS, the connection attempt times out.
Below is the code that is failing (only on the server when deployed):
Private Shared Sub OpenConn(ByVal cmd As SqlCommand, ByVal Success As Boolean)
Dim conn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("GoodNews_IntranetConnectionString").ToString)
'The connection string resolves to ("Data Source=HQSERVERHQSERVER;Initial Catalog=GoodNews_Intranet;Integrated Security=True"
Try
conn.Open()
cmd.Connection = conn
Success = TrueCatch ex As Exception
Success = False
End Try
End Sub
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. Compiler Error Message: BC30456: 'InserParameters' is not a member of 'System.Web.UI.WebControls.SqlDataSource'.Source Error:
Line 15: registrationDataSource.InsertCommand = "INSERT TO Reputation (firstname, lastname)VALUES(@First Name, @Last Name)"
Line 16:
Line 17: registrationDataSource.InserParameters.Add("firstname", firstname.txt)
Line 18: registrationDataSource.InserParameters.Add("lastname", lastname.txt)
Line 19: Source File: C:UsersQaiphyx
eputationDefault.aspx.vb Line: 17
Show Detailed Compiler Output:
I am developing a web site with asp.net 2.0 and c#, using Visual Studio 2005 and Microsoft SQL Server 2005 ( I am still learning about these technologies and languages ).I transferred my web site files to my NEW computer. The web site worked fine when running inside Visual Studio, however when I tried to run it in its PUBLISHED format it was giving this error message:Server Error in '/MA' Application. Cannot open database "SiteData" requested by the login. The login failed.Login failed for user 'CENTAURUSASPNET'.I solved the problem by running SQL Server Management Stdio, right clicking “SiteData� database -> properties -> permissions->view server permissions then I chose “CENTAURUSASPNET� and since I DID NOT KNOW what grants are ESSENTIAL for the functioning of the database, I GRANTED EVERYTHING ( I ticked ALL the permissions).
But since, probably, a lot of GRANTS are not only unnecessary but also may compromise the security of the web site, I would like to know what are the ESSENTIAL OR MINIMUM GRANTS NECESSARY for making the database functional to the web site and what grants may represent a security risk.
I I work for a small business who has a somewhat successful ecommerce site. Our website is based on some VERY large DB's with 10's of millions of records, and 40+GB of images, so we choose to self host so we could easily manage the data. As things have grown and we've cobbled a fair amount of integration between the website databases and our internal systems. Fast forward 4-5 years, the website has grown to the point that self hosting is costing us a fortune in bandwidth costs, so I'm thinking of moving to a co-location facility. If I do that, I need to send the website tables along with it. Is there a replication stradagy that would allow us to have two independant DB's, both able to take updates that would sync with one another? My other thought is to spit the tables on two different DB's and let some of the data live in the co-location facility.. but I am worried about the performance impact for local users.
View 3 Replies View RelatedWe have database when trying to make read only throwing below error: with stack dump
Location:Â Â Â Â Â Â Â Â Â Â Â Â Â recovery.cpp:4517
Expression:Â Â Â Â Â Â Â Â m_recoveryUnit->IsIntendedUpdateable ()
SPID:Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 51
Process ID:Â Â Â Â Â Â Â Â Â 6448
Msg 926, Level 14, State 1, Line 1
Database 'XXXX' cannot be opened. It has been marked SUSPECT by recovery.
See the SQL Server errorlog for more information.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
Msg 3313, Level 21, State 2, Line 1
During redoing of a logged operation in database 'XXXX', an error occurred at log record ID (0:0:0). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
Msg 3414, Level 21, State 1, Line 1
An error occurred during recovery, preventing the database 'XXXX' (database ID 7) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
Investigation DONE:
- DBcc checkdb shown CleanÂ
- database is online and able to access
-Detached database and attached with rebuild log, still could not bring database read_only
SQL version : SQL server 2008 SP4.Â
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!
Hello I am a software developer with minimal SQL server administration skills. Currently I am using SQL Server 2000.I need to know if there is a way to copy a particular table from a database, and to copy the table into a different database.Basically on a project I am working on we are using a table named "Customers" from a database named QTR. We need to copy this database table into a different database named "Research". How can this be done? Is if very complicated?
View 1 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 guys,
I've been assigned the task of setting up access to our SQL Server 2005 box. A consultant developing for us has accessing to 2 databases and I've set this up fine. It appears however that one of these databases is re-copied over to the server every night to keep data reasonably current.
I'm not interesting in changing this method as I'm not the maintainer (as yet).
Basically I would like to know if I've setup access to this database (it works fine), when the database is updated (with an SSIS package) the account seems to get deleted. Do the original permissions from the source database overwrite those of its destination?
Cheers
I'm using SQL Server Management Studio Express and I'm trying to figure out how to copy a table(s) from my local database to my web hosting database. I know how to do it in 2000, but it's completely different now. Is this feature not allowed on SSMSE? If so, then how do I deploy database tables to a web host?Also, how do you add local database(s) to SSMSE? I tried to use 'attach database' in SSMSE and it wouldn't allow me to navigate to My Documents folder where the database resides. Thanks...
View 8 Replies View RelatedI want to make data changes in read_only database , that's why i must set database read_write. While database is at read_write mode, i want to be sure that no one makes change in database.
For this aim, i write the code below, but i suspect that after setting the database read_write, till the setting database
single_user ,is it possible get DML script from another user. Is the code below enough for this operation. Or is there another way?
Reminding: Read_only database can not be set single_user mode. That's why, first you must set database read_write.
The code;
use master
alter database xxx set read_write
with rollback immediate
alter database xxx set single_user
with rollback immediate
use xxx
update tablexxx set columnxxx=yyy
use master
alter database xxx set read_only
with rollback immediate
alter database xxx set multi_user
with rollback immediate
i attached adventure works in sql server 2008 and it showing as read only ,make it read write or remove read only tag from database.
View 11 Replies View RelatedIf I want to copy the data from Table1 in Database A to Table2 in
Database B but Table1 column name is code , Table 2 column name is
vesselcode.
(Code = vesselcode)
How to copy all data from Table1 in Database A to Table2 in Database B ?
Do I need to write the SQL statment ? and Can I use Server Enterprise
Manager Tool?Thx a lot.
Hello,I need to copy a table from an 8i oracle database to a sqlserver 2000 database.Is it possible to use the command "COPY FROM ... TO ..." ?So, what is the correct syntax ?Thanks for your helpCyril
View 1 Replies View RelatedHi,
I have a complicated sql server mobile database (.sdf) and need to create a SQL SERVER database with the same tables. How can I do it without scripting the whole thing? I thought of using the views.information_schema databases, but it is still a lot of coding.
thanks
I'm taking a database(read-only) backup from one server and restoring it on other server. As soon as restore is done it is bringing database into single-user read-only mode.
why it is bringing the database into single user mode ?