Backing Up DPAPI Keys In Relation To SQL Server
Sep 13, 2007
Hi,
There alot of talk about Service Master and DB Master keys and the importance of backing them up seperately.
What about the DPAPI keys used to protect the Service Master keys. What procedures to I take to back those up during my database backup.
If I understand correctly, if I do a backup on one machine, and then try to restore in a different machine, I won't be able to decrypt data becuase the DPAPI keys don't "match".
This is assumming I don't use a password to protect my DB Master keys, I just rely on the service master to protect them.
Thanks for any help,
View 3 Replies
ADVERTISEMENT
Mar 29, 2007
I'm getting this warning each time I auto-generate my model. I'm using named queries with logical primary keys.
The relation property of the role "xxx" refers to the target end of relation "xxx" which is not bound to a set of uniquely contrained columns.
I've searched and can't figured out what I am being warned about and whether I need to fix something. Can you help?
Thanks,
Toni
View 1 Replies
View Related
Nov 7, 2000
Hi Listers,
Can any one clarify me on the following doubt.
I have 2 w2k servers with different domains and On which SQL2k is running on both the Servers. Both the servers don't have any Trust Relations between them.
My questions is, Is it possible to create Linked Servers between sql servers but their Operating Systems doesn't have Trust Relationships?.
Any help would be appreciated.
tks in advance,
Sam
View 3 Replies
View Related
Nov 26, 2007
Hi Group,(I am just starting with SQL Server 2005.)On SQL Server 7 I used often the nice relation schema, where I used todraw out the whole database, especially the Foreign Keys constraints.I found these relational schemas very handy to study an old database Ibuild a few years earlier that needs some updating.I tried to find such an utility in SQL Server 2005, but cannot find it.I did found the FK-constraints, but it is just an popup where I candefine them.The overview such a visual schema gave was really great and I miss it.Question: Is it gone in SQL Sevrer 2005, or do I just not know where tolook? If the latter, please guide me. :)Thanks for your time.Regards,Erwin Moller
View 1 Replies
View Related
Oct 19, 2015
how can I determine the relation between MS SQL Server user (SQL login ) and his session , when it connect to SQL Server
View 1 Replies
View Related
Jun 12, 2015
I have got a question on max degree of parallelism and CPU cores.
If max degree of parallelism = 1, this signifies that sql will use serial execution plan (unless u change it in query level with MAXDOP hint). In serial plan, will the query use all CPU cores (say in my server I have 16 core processors)?
If in serial execution plan only one thread works, then what the other threads doing ? Idle (I may have a defined max server worker thread = 32767(by default)
Unable to create a relationship between this parameters.
View 3 Replies
View Related
Apr 11, 2006
Hello again,
I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.
Here's the query for my Classes table:
Code:
CREATE TABLE Classes
(
class_id
INT
IDENTITY
PRIMARY KEY
NOT NULL,
teacher_id
INT
NOT NULL,
class_title
VARCHAR(50)
NOT NULL,
class_grade
SMALLINT
NOT NULL
DEFAULT 6,
class_tardies
SMALLINT
NOT NULL
DEFAULT 0,
class_absences
SMALLINT
NOT NULL
DEFAULT 0,
CONSTRAINT Teacher_instructs_ClassFKIndex1 FOREIGN KEY (teacher_id)
REFERENCES Users (user_id)
)
This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:
Code:
CREATE TABLE Classes_have_Grades
(
class_id
INT
PRIMARY KEY
NOT NULL,
teacher_id
INT
NOT NULL,
grade_id
INT
NOT NULL,
CONSTRAINT Grades_for_ClassesFKIndex1 FOREIGN KEY (grade_id)
REFERENCES Grades (grade_id),
CONSTRAINT Classes_have_gradesFKIndex2 FOREIGN KEY (class_id, teacher_id)
REFERENCES Classes (class_id, teacher_id)
)
Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key?
In addition, what is a "candidate" key? Will making the other fields "Candidate" keys solve my problem?
Thank you for your assistance.
View 1 Replies
View Related
Jul 16, 2014
what the best practice is for creating indexes on columns that are foreign keys to the primary keys of other tables. For example:
[Schools] [Students]
---------------- -----------------
| SchoolId PK|<-. | StudentId PK|
| SchoolName | '--| SchoolId |
---------------- | StudentName |
-----------------
The foreign key above is as:
ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools]
FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])
What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:
CREATE INDEX IX_Students_SchlId ON Students (SchoolId)
Or
CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)
In other words, what's best practice for adding an index which best supports a Foreign Key constraint?
View 4 Replies
View Related
Oct 4, 2007
Hey, please forgive me if my infomation source was incorrect. But a friend of mine mentioned to me he had hurd of a program, or code that will make an "install script" of your database. Basically it`ll back up all the tables on your server and if the worst case senario happens then you can run the file like an install script. Is this true? It sounds invalueable! If it isnt true, can someone tell me if it is possible to use code to create a "install script" of your own database? I am using "Microsoft SQL Server".Thanks in advance John
View 3 Replies
View Related
Apr 19, 2007
Hi,
I've recently created a maintenance plan, which backs up my database and transaction log. However I also have a Veritas (Backup Exec for Windows Servers) tape backup which runs at night and backs up my files. It is setup to backup the whole of my MSSQL DATA and BACKUP folders.
Today I went to do a test restore, and found that the tape backup contained nothing in the MSSQL DATA folder, but everything that was in the BACKUP folder. Is this because everything in the DATA folder is in an open state? On checking back, the tape backup has never held anything in the DATA folder. Should I be worried by this, or is it what you would expect, and is the SQL backup sufficient?
Thanks for any advice
Colin
View 2 Replies
View Related
Feb 2, 1999
hi, I have total 45 GB of data in the following servers ( 40 GB in sql server 6.5, 5GB in ftp server). both servers are in one machine(server). I want to make sure that I am doing the right thing for backup. The machine is not connected to a Lan. What is the best way to insure that my data are backed up safely?
what I am thinking of doing is to back up the database to using sql tools in the menu bar, that will backup the data to the same machine in this directory:
c:mssqlackupDBname.DAT
I will do this for all database in the sql server. Am I save to do this... I am wondering what IF the machine in which both severs are in crashed. My backup effort will be lost too. right. so what is the best way to protect my data. Do I need to buy a tape backup so I can do what I am doing Plus back up the c:mssqlackupDBname.DAT to the tape backup... I would really appreciate any suggestion in choosing any media (software/ hardware)to back up my data.
Thanks
Ali
View 4 Replies
View Related
Jul 23, 2005
I want to reformat my servers harddrive and install a newer operatingsystem (Win 2003). I am running SQL Server 2000 with about 10databases. What is the best way to backup and restore SQL Server forthis so I don't lose anything.Thanks,Rick
View 1 Replies
View Related
Jul 8, 2006
I am just getting started with MS SQL and am having to learn from scratch. I have a DB running with an ISP which I need to back up to one of my local machines. I have installed MS SQL Server 2005 and also MS SQL Server Management Studio Express. Using this, I have opened a new DB on the local machine and then accessed the DB on the remote server. If I then right click on the remote DB file and select TasksBackup, I see the backup options screen. The problem is that the destination for the backup is automatically set to a folder on the remote server and I cannot figure out how to change this so that I can download to the local machine. If I click on the 'Add' button to create a new backup path, it will only offer me a view of the remote machine, not the local file structure.
The solution is probably pretty simple, but after several hours of experimentation and searching for solutions, I still can't come up with the answer. Can anyone offer some guidance please (in idiot language - I'm new to this).
View 1 Replies
View Related
May 16, 2008
Pls let me know How I generate script for All primary keys and foreign keys in a table. Thereafter that can be used to add primary keys and foreign keys in another databse with same structure.
Also how I script default and other constraints of a table?
View 2 Replies
View Related
Aug 26, 2006
Hi I am trying to make a backup of my SQL server 2005, used with Web Developer 2005 Express, in osql.exe (can't find any other way of doing it). I get the error that "under the default settings SQL server does not allow remote connections".I connect to it fine from the Web Developer 2005 and my application . I followed another post's instructions where I enabled the TCP/IP and named pipes for "Protocols for SQLEXPRESS", but didn't work. In fact when I changed TCP/IP properties/ IP Addresses/TCP Dynamic port to nothing , and TCP Port to 1357, the server wouldn't start again !! (not doin THAT again)Regards Amanda
View 1 Replies
View Related
Apr 22, 2007
How can I back up existing SQL Server 2005 Express tables? I'm presently developing on a 4 year old laptop which is toooo sloooow and need to transfer the tables and data to a desktop system.
View 1 Replies
View Related
Nov 1, 2004
My client is changing hosting providers. We are trying to download a full backup of our MS SQL 2000 database from the old host. We can get all of the data using the Data transfer wizard in Enterprise manager. We can get all data and structure using the Data Transfer wizard with other options. However, when we do this, all of our tables get renamed with a prefix that is our username from the old host.
So, if we have a table named customers and our MSSQL login on the old host is userA, then our table gets renamed to tableA.customers.
This won't work with our applications and stored procedures. Changing tens of thousands of lines of code is not acceptable.
Does anyone know a way we can get a full copy of the database (rules, data, structure, keys, identies, etc) without having the username prefix?
Thanks for the help!
-DAGTA
View 1 Replies
View Related
Jun 4, 2007
We are attempting to backup using a maintenance plan a database to a network share.
The backup job works within a domain but fails within a workgroup?
Any thoughts?
thanks
View 1 Replies
View Related
Jul 31, 2007
Hi,
We're running SQL Server 2005 (9.00.1399.06, RTM, Standard Edition (64-bit)). A couple of times each day, all connections to the server will fail, and this behavior coincides with backup activity. What tends to happen is that after performing flawlessly for hours, SQL Server will start rejecting all connections. Over the course of the next 30 seconds or so, several hundred instances of the following error message will appear in the Application event log:
"SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems." Source: MSSQLSERVER, Category 4, Event ID 17189.
Immediately following these Error entries are Information entries that indicate some backup activity has been performed:
"Database differential changes were backed up. Database: [lots of db-specific details] ..." Source: MSSQLSERVER, Category 6, Event ID 18270.
As soon as the backups are completed, everything returns to normal and connections are once again accepted. The entire cycle takes less than a minute. Obviously, something is causing SQL Server to lock up and reject connections while it's backing up. Is there a setting that controls this, or does this behavior point to an overloaded machine?
We don't seem to be having any other performance problems at all, and there are no other Errors in the Application event log from SQL Server. If I perform a manual backup, I don't get any dropped or refused connections, even if I do it during peak query hours. So I'm stumped as to why the machine is locking up in this manner. Unfortunately there's no info in the EEMC about event ID 17189.
TIA
Shaun
View 1 Replies
View Related
Jul 15, 2002
Can somebody explain to me how to best do inserts where you have primary keys and foreign keys.l'm battling.
Is there an article on primary keys/Pk ?
View 1 Replies
View Related
Nov 22, 2007
Hello!I have a table A with fields id,startdate and other fields. id and startdateare in the primary key.In the table B I want to introduce a Foreign key to field id of table A.Is this possible? If yes, which kind of key I have to build in table A?Thx in advance,Fritz
View 6 Replies
View Related
Jul 31, 2002
I have my SQL7 server running on NT4 server. Currently the transaction log backups are on the same server but I want to back these up to a Win2000
server in another building. When creating a Backup Device in Enterprise Manager on the NT server it cannot see the drive but not the folder I have created on the Win2000 server, even though I have shared this folder out and set the permissions. Any help would be appreciated.. Thanks
View 3 Replies
View Related
Mar 30, 2004
Hi
I am getting an error while backing up a particular database named "asset" . The error says ........
Server: Msg 3132, Level 16, State 1, Line 4
The media set for database 'Asset' has 2 family members but only 1 are provided. All members must be provided.
Server: Msg 3013, Level 16, State 1, Line 4
BACKUP DATABASE is terminating abnormally.
I have created two backup devices for this database ("asset") through enterprise manager.
AssetDMP
AssetLogDMP
I have total 9 databases out of which only this database throws error. All other database backups going fine.
Here is the query that I am using to backup the database and Log.
USE master
GO
IF EXISTS (SELECT * FROM sysdatabases WHERE name = 'Asset')
BACKUP DATABASE Asset TO AssetDMP WITH NOUNLOAD, STATS=10, INIT, SKIP
GO
IF EXISTS (SELECT * FROM sysdatabases WHERE name = 'Asset')
BACKUP LOG Asset TO AssetLogDMP WITH NOUNLOAD, STATS=10, INIT, SKIP
Please Help
Thank You / Ssg
View 8 Replies
View Related
Jul 11, 2014
I'm trying to backup my databases to another backup server though I'm receiving the attached error.
the SQL service is logged on as "Network Service"
View 7 Replies
View Related
Mar 20, 2002
Hi,
I have a rather odd problem that hopefully you'll be able to shed some light on.
We want to back up the databases to a hard drive held on another server so I mapped the drive in explorer to the drive then went into Enterprise manager and tried to create a backup device and it won't see the mapped drive.
I've tried mapping to my PC and I can see that via enterprise managers backup stuff (infact any PC in the office works) but it won't see any of the servers even though we can map to them and access them via windows explorer.
I've tried when logged on via sa and the windows NT administrator and still no luck. In fact no matter what I type or do it fails and keeps telling me device error or device off line which it isn't.
On our test instance of SQL Sever we can backup to other servers but not the new live version!
Any thoughts on what might cause this to happen and how to fix it?
Help much appriciated.
Thanks Helen
View 1 Replies
View Related
Mar 20, 2002
Hi,
I have a rather odd problem that hopefully you'll be able to shed some light on.
We want to back up the databases to a hard drive held on another server so I mapped the drive in explorer to the drive then went into Enterprise manager and tried to create a backup device and it won't see the mapped drive.
I've tried mapping to my PC and I can see that via enterprise managers backup stuff (infact any PC in the office works) but it won't see any of the servers even though we can map to them and access them via windows explorer.
I've tried when logged on via sa and the windows NT administrator and still no luck. In fact no matter what I type or do it fails and keeps telling me device error or device off line which it isn't.
On our test instance of SQL Sever we can backup to other servers but not the new live version!
Any thoughts on what might cause this to happen and how to fix it?
Help much appriciated.
Thanks Helen
View 1 Replies
View Related
Jun 9, 2006
Dear sir/madam,
I am from NTU, Singapore and we have a windows 2003 server edition installed on a DELL server and it also runs MS SQL server 2000 for a project of ours. We have installed a HP tape drive on the dell server and it works fine if ntbackup is used at the command prompt but then the SQL enterprise manager does not recognize the tape drive and backing up of our database is of high priority as it contains sensitive information. But the tape drive is installed properly as the device manager does not show any error and ntbackup also recognizes it. I cant seem to figure out why SQL server 2000 cannot recognize it. It would be very helpful if you could kindly guide me on this issue.
Thanking you
Regards
Lavanya Janardhanan
View 1 Replies
View Related
Mar 1, 2006
Anyone know of a good "free" way to back up web files and SQL Server 2005 Express Database?
I was able to use Windows Server 2003 Backup utility to back up the folder where the Databases were stored, as well as the web files, with no errors.
But I have heard a lot of discussion that you can't just simply backup SQL Server data files?
I'm wondering how sound the backup I've created is...
Any suggestions?
View 1 Replies
View Related
Mar 25, 2013
i wanted to test routines using my local sql server engine and thought I could backup the mdf ldf file on the true network sql server in which I am listed as a dbowner with full permissions. In fact i can add users for the at db etc.The backup allows me to browse to a local folder
with the following being the default C:Program FilesMicrosoft SQL ServerMSSQL10_50.CMS_PROJECTMSSQLBackup
i name the file and it executes properly but when i try to restore that file to my local db that path is not even visible to me.In fact when i use exploreer I cannot even find the folders after Microsoft SQL Server!I search for .bak but nothing.How can I see that folder construction in the sql browser but never in explorer -
View 2 Replies
View Related
Apr 20, 2015
Im backing up to a network directory thats actually a mount point on a different server.My backup was slower than usual so i opened up perfmon to have a look.
When selecting the mount point from the Logical Disks section in perfmon i can see that writes/sec & write bytes/sec both show zero for a long period of time, even though the backup percent complete is increasing.Then all of a sudden the writes to the network share jump massively.
Is there some caching mechanism for backups in sql where during a backup data is only flushed to the disk periodically during backup?
View 1 Replies
View Related
Aug 13, 2007
Hi,
I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.
For example:
id [unique integer auto incremented primary key - not null],
ClientCode [unique index varchar - not null],
name [varchar null],
surname [varchar null]
isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.
Regards
Mike
View 7 Replies
View Related
Dec 6, 2007
Hi friendsI have one Table called tblCategory.I have three Column CatID, CatName, ParentIDI have many records in this TableCatID CatName ParentID1 Cat1 0 2 Cat2 13 Cat3 14 Cat4 25 Cat5 26 Cat6 07 Cat7 68 Cat8 69 Cat9 710 Cat10 711 Cat11 8 Here I have Main Category which has ParentID 0 [ Cat1 and Cat6 ]I Have Sub categories of Cat1 Which has ParentID 1(CatID 1 of Cat1) [ Cat2 and Cat 3 ]Cat 2 has also sub category with ParentID 2 (CatID 2 of Cat2) [ Cat4 and Cat5]I want result looks like asCat1 Cat6 ->>>>>ParentID 0- Cat2 -Cat7 ->>>>>ParentID 1(CatID of Root Cat1) and ParentID 6(CatID of Root Cat6)- - Cat4 - -Cat9 ->>>>>PaerntID 2(CatID of Root Cat2) and ParentID 7(CatID of Root Cat7)- - Cat5 - -Cat10 ->>>>>PaerntID 2(CatID of Root Cat2) and ParentID 7(CatID of Root Cat7) - Cat3 -Cat8 -->>>>>ParentID 1(CatID of Root Cat1) and ParentID 6(CatID of Root Cat6)--No record --Cat11 Can anybody give me solution?Thanks
View 4 Replies
View Related
Dec 29, 2003
If i add a new table to my database with a stored procedure, using that same sp or another can i also add a 1-M relationship with another table that is currently in the database. Please if you can provide an example.
Thank you,
View 1 Replies
View Related
