Hi All,
We will be transfering a big application onto another server. Is it possible to copy the Syslogins with passwords onto the new server from the old one it resides. I do not want to restore Master onto the new server because the Server name will be different.
I got a File with sales orders and their details.
Step 1. First I am filtering the Sales Order information and inserting it in my Sales Orders table.
Step 2.Then I am filtering the details from the sales Order and inserting them in the respective table.
My Problem is that the Sales Order File does not contain the Sales Order key (ID), this is generated by the SQL Server. How can get it in order to use it in the second step? I need it because it is a foreign key in the details table.
Any Idea?
Hello,
I'm new to SQL and need help with a query. Not sure if this is the right place.
I have 2 tables, one MASTER and one DETAIL.
The MASTER table has a masterID, name and the DETAIL table has a detailID, masterID, and value columns.
I want to return a populated MASTER table with entries based on the DETAIL.value.
SELECT MASTER.*
FROM MASTER
WHERE DETAIL.value > 3
This is a simplified version of my problem. I can't figure out how to set the relationship between MASTER.masterID and DETAIL.masterID. If I do an INNER JOIN, the number of results are based on the number of DETAIL entries. I only want one entry per MASTER entry.
Hope this makes sense.
How can I do this?
GrkEngineer
We already integrated different client data to MDS with MS Excel plugin, now we want to push back updated or new added record to source database. is it possible do using MDS? Do we have any background sync process to which automatically sync data to and from subscriber and MDS?
View 4 Replies View RelatedWhat would the reasons when some of user tables are in master db/user table section as well as those tables are in user db/user table section listed.
Thanks,
Glen
if i want to create a database, using:
CREATE DATABASE name
does it need the USE MASTER before the line? a database MUST be created using Master??
i ask this because in an example i need to create a database, so i use the next code:
CREATE DATABASE Banco
ON
(NAME='P_Banco',FILENAME='C:Documents and SettingsVictorEscritorioBancoP_Banco.mdf')
LOG ON
(NAME='P_Banco_Log',FILENAME='C:Documents and SettingsVictorEscritorioBancoP_Banco_Log.ldf')
USE Banco <-- i wrote this, because i tought i could create tables and stuff directly in my previously declared database, but it sends this error:
"Could not locate entry in sysdatabases for database 'Banco'. No entry found with that name. Make sure that the name is entered correctly."
anyway, i could do it, but first creating the database, and after that, in a separate code creating the tables with USE Banco, but my question is..why is that? or i miss something in my sql code??
Hi Y'all,
What are the advantages of placing stored procedures in the master database? Can i always use all the database-names on the instance?
Thanks!
Hi all,
I am having trouble modifying the select statement(s) below to return me only unique rows instead of duplicated rows. I give up. Can someone please jump in and give it a shot? Please help out. Thanks.
blumonde
************************************************************************************************sb.Append("SELECT TOP (@PageSize) TopicID, TopicName, Message ");
sb.Append("FROM (Select TOP (@CurrentPage*@PageSize) ROW_NUMBER() OVER (ORDER BY ftp.DateEntered DESC) AS Row, ftp.TopicID, ftp.TopicName, ft.Message ");sb.Append("FROM forumTOPIC ftp, forumTHREAD ft, forumCategory cat, forumTechnology tn ");
sb.Append("WHERE tn.TechnologyID = cat.toTechnologyID AND cat.CategoryID = ftp.toCategoryID AND tn.TechnologyName LIKE @KeyForum AND ftp.TopicID = ft.toTopicID AND FREETEXT(ft.Message, @KeyWord) ");sb.Append("GROUP BY ftp.TopicID, ftp.TopicName, ftp.DateEntered, ft.Message ");
sb.Append("ORDER BY ftp.DateEntered DESC) as t1 ");sb.Append("WHERE Row BETWEEN (@CurrentPage-1)*@PageSize+1 AND (@CurrentPage*@PageSize) ");
sb.Append("ORDER BY t1.Row ASC");
**********************************************************************************************************
I had installed a third-party software on my server and that corrupted my Master DB. Over the weekend when we bought SQL server down, we could not bring it back up. I had to re-install SQL server and then restore all my databases from the backup. Is there a better way of doing this???
Curiously
Someone who calls himself a DBA
Hi Friends ...
I need your advice and help on the following problem . Say for example I have a SQL 7.0 Server A with 30 databases and i am in the process of transfering these databases to another system which is SQL Server B with 7.0 again .
Although the Databases can be copied by various means , can i restore Master database from the Server A to Server B so that my Server B will have all the database and login information as that of Server A.
As Master database is system database , it won't allow to be dropped and hence you cannot restore .
Is there any other way to restore Master database from One SQL server 7.0 to another with the same SQL version .
Any kind of assistance will be of great help to me . Many thanks in advance .
Kind Regards
Sneha
There is a table in the master DB where the sourcode of the SP's is saved, dies anyone knows the table's name?
Regards,
Hi folks,
In SQL 7 when you query
select name, crdate from sysdatabases where name like 'xxx3%'
You get a reply with the rows of matching databases in sysdatabases. But in SQL 2000 the same command throws an error saying that the object sysdatabases is wrong. Here is the modified command
select name, crdate from master.dbo.sysdatabases where name like 'xxx3%'
Now my problem is I can include the latest piece of code and go further. But my older version of product will have this problem and will fail. Is there anyway that I can dothis on SQL 2000 to be compatible with my old product.
regards,
mahesh,
Hi,
I have a question regarding master database.I know if a master database is
corrupt we need to rebuild the master.One of the guys suggested us to
maintain a copy of master data file and log file in diffrent directory and
when master database is corrupt we can copy the files to the actual location
and restart the sql server and this should fix the issue and by this method we can save the time of rebuilding the master database.I don't know if this works.Can anyone please tell me if this works and also disadvantages if any with this method?
thanks
Mohan
Various books read suggest that the Master
database be backed up when it is changed.'
What activities does a DBA perform (or perhaps)
a user) will cause the Master database to
change?
I am aware that information about the addition
of new databases is kept in the Master.
TIA Gary
Hi,
how long a rebuildm.exe takes?
TIA
When I try to restore master, I keep getting an error message, while it is in the process of restoring, that says the connection has been broken. I assume it means the connection for single user mode. Any ideas on how to fix this??????
View 2 Replies View RelatedI tried to import a stored procedure from one Master database to another Master database, but it won't allow me. I then tried to copy and paste that system stored procedure on isql, but it still won't allow me.
Why I can do a DTS on other databases, except on Master database?
Is there any way around it?
Thanks for any help.
hi, What are the symptoms that the master database is currepted?
If I do not know the databases sort order, code page, and Unicode collation and I had a master database crash, it is important during the rebuild process to maintain same configuration,
Is there a way to run a script which tells me the configuration set up of the master database. so I know ahead of time what configurations Ican use during the rebuilding process.
Ahmed
hi, what are the reasons for rebuilding master database and what are the steps to complete this task
Thanks
Ahmed
Does anyone have anything that can automate the restoration of the master database in MS SQL 7? I need to do it on a daily basis on a back up server. I also need to do the 2 production databases and the msdb and model as well. Does anyone have any suggestions to keep me from reinventing the wheel?
Thanks,
Tim Davis
Hi All,
I am currently creating a SQLServer 7 server. This server will be used to host customer databases that I will restore on to the server. However, I want to prevent these customers accessing any other databases on the server, apart from their own. By removing the public database role from each customer database, and granting them very limited rights (basically exec rights on their own Stored Procs)on their own db, I plan to limit them to their own db. However, my problem is this:
As you cannot remove the public role from the master db, a user could easily exec the following in a stored proc to read from the master:
Select * from master..sysusers
How do I prevent the users from accessing the master in this fashion.
Will removing every permission from the public role in master be enough?
Will removing every permission from the public role in master have any other side effects?
Will removing the public role from other user dbs be enough to secure them?
Any suggestions/pointers would be appreciated.
Gary.
Master Database in SQL Server 7 has a transaction log. Using Enterprise Manager the option to back up the transaction log is greyed out. Is this because there is no need to back it up.
I don't know if there is any value, or whether it is possible to do so.
I have a number of books, none of which cover this specific question.
Can anyone help.
pargat.bhatti@uk.neceur.com
I'm trying to nail down some procedures for disaster recovery of our SQL 6.5 databases. If I rebuild master (or do a fresh install), then create the devices based on the output of sp_helpdevice, and then create the databases based on the output of sp_help_revdatabase, do I really need to RESTORE the master database? Are there any other objects in the master database (except for maybe some custom stored procedures) that I really need from my production server?
I've just recreated the device and database structures manually, so do I really need to restore master?
Another issue: the current wisdom that I've seen is that although databases need to be grown back in the same increments, the devices do not. Therefore, you can just DISK INIT them to their current size. Does anyone think this is a bad idea?
Ok I had a server crash the master was bad or corrupted. I tried to rebuild the master but that failed. I had to reinstall sql and then attach the databases.
I need to know why the master all of a sudden just stopped working. Any ideas where to look or what would cause this?
Thanks.
:eek:
This is long post but I really want some feedback on this urgently so please hang in there!
I am migrating a SQL Server over to a new server.
I have done the following and although everything appears to be working like a charm I would like to know if there are any gotchas that I have not considered.
These are the steps I used to 'move' the master db.
/*
I restored all the users databases onto the new sesrver from backups of the old (file copies and attaches would also do).
I restored a db named masterbak from a backup of the master on the old server. And again into a second db called masterbak2. I then detach masterbak2. I need to use those files later on.
So at this point I now have all the user databases that the old server had but I dont have logins, extended stored proc defs, and any user objects that were created in the old master.
Now I want to put all those missing things into the new master db. These are the steps that I used to achieve this.
*/
-- where doing open heart on sys tables so need this turned on
EXEC sp_configure 'allow updates' , 1
reconfigure WITH OVERRIDE
-- remove all database defs from the copy of the old master
delete masterbak..sysdatabases
-- and load it with all the database defs from the new master
INSERT INTO [masterbak].[dbo].[sysdatabases]([name], [dbid], [sid], [mode], [status], [status2], [crdate], [reserved], [category], [cmptlevel], [filename])
SELECT [name], [dbid], [sid], [mode], [status], [status2], [crdate], [reserved], [category], [cmptlevel], [filename]
FROM [master].[dbo].[sysdatabases]
-- SYSSERVERS
-- remove all the servers defined in the copy of the old master
delete masterbak..sysservers
-- replacing them with those that are in the new master
INSERT INTO [masterbak].[dbo].[sysservers]([srvid], [srvstatus], [srvname], [srvproduct], [providername], [datasource], [location], [providerstring], [schemadate], [topologyx], [topologyy], [catalog], [srvcollation], [connecttimeout], [querytimeout])
SELECT [srvid], [srvstatus], [srvname], [srvproduct], [providername], [datasource], [location], [providerstring], [schemadate], [topologyx], [topologyy], [catalog], [srvcollation], [connecttimeout], [querytimeout]FROM [master].[dbo].[sysservers]
-- sysfiles1
-- remove the old
delete masterbak..sysfiles1
-- replace with new
insert masterbak..sysfiles1
select * From sysfiles1
-- sysdevices
-- remove the old
delete masterbak..sysdevices
-- replace with new
insert masterbak..sysdevices
select * From sysdevices
-- sysconfigures
-- remove the old
delete masterbak..sysconfigures
-- replace with new
insert masterbak..sysconfigures
select * From sysconfigures
-- sysaltfiles
-- remove the old
delete masterbak..sysaltfiles
-- replace with new
insert masterbak..sysaltfiles
select * From sysaltfiles
-- were done
-- turn off sys updates
EXEC sp_configure 'allow updates' , 0
reconfigure WITH OVERRIDE
/*
this leaves
syslogins intact
sysmessages intact
sysoledbusers intact
sysremotelogins intact (no records in mine anyway)
all other system tables are left intact as we want to import all the objects into the new server
*/
/*
now stop the SQL server instance;
rename the physical files master.mdf and mastlog.ldf to masterORIG.mdf and mastlogOrig.ldf
rename masterbak.mdf and mastlogbak.ldf to master.mdf and mastlog.ldf.
rename masterbak2.mdf and mastlogbak2.ldf to masterbak.mdf and mastlogbak.ldf.
I wanted to preserve the new master files so didnt rename to masterbak but I suppose you could.
Start up SQL server and everything should be fine.
Configuration settings are as expected, databases started up ok and file locations are ok, logins both SQL and NT are ok, all master user objects are present.
User and login SIDs match.
A linked server access that was setup failed but after redoing security on the linked server it was ok? Sysoledbusers holds the security for this but not sure about this one.
Important:
You need to check sql log for any errors.
It should be clean.
If you have auto start procs that reference reg keys that are not on the new server you with be told which key is missing and you can export key from old server and import into new server.
Dlls of course will need to come accross but again error messages will indicate the dll that is missing and its path.
So we now have a server that starts up clean but its of a different name of the original of course and since we have a myarid of external servers etc that depend on the server name we are going to keep the old name as well.
So now we change the server name, requires reboot, start up QA and logging into to each 'new' instance name, run the following
-- 'SQLSERV02 -> SQLSERV01'
sp_helpserver
sp_dropserver 'SQLSERV02'
sp_addserver 'SQLSERV01', local
and that should be that.
you might get this :
-- Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44
-- There are still remote logins for the server 'SQLSERV02'.
sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
-- have a look at it
select srvid from master.dbo.sysservers where srvname = 'SQLSERV02'
select * from master.dbo.sysxlogins
where srvid = 0 and
not(ishqoutmap = 1 and
xstatus&192 = 192 and
sid is null and
name is null and
password is null)
-- get rid of it
delete master.dbo.sysxlogins
where srvid = 0 and
not(ishqoutmap = 1 and
xstatus&192 = 192 and
sid is null and
name is null and
password is null)
sp_configure 'allow updates',0
RECONFIGURE WITH OVERRIDE
My last step is to get all those jobs and log shipping over to the new server. And I have not yet done this so am not sure if any further open heart is required.
But the theory is that I just need to restore an msdb taken from the old server.
Naturally you will need to put in place the same file path structure on the new server for those scripts / jobs that create files on disk - such as backup files.
I will find out about msdb soon enough as this is going down tonight.
Can anyone see any obvious gotchas in this method?
cheers and thanks for your feedback
*/
I've got a new (my first) SQL2000/SP2 installation in which I've set up a vanilla database maintenance plan, including backup of master & msdb with my user databases. My user databases are backing up, but master & msdb fail every time with "[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode." Presumably this is caused by Integrity checks being set to perform before every backup.
First, this should work as I'd think we want to do the integrity checks; however, in the interest of getting a backup I've unchecked that box on the Integrity form. Unfortunately, my "unchecking" doesn't stay; I've 'applied' and 'okayed', but the next time I open the maintenance plan form that box is checked again!
Any suggestions appreciated.
TIA, Al
I have a SQL 2000 server that when they installed it they changed the default settings of the locations of the data files. Master included. I now want to restore master to a standard location. However everytime I do it, I then cannot log onto SQL Server. Any suggestions!!
View 1 Replies View RelatedHi, Folks !
What happens with all jobs after rebuilding master db if it got corrupted. Will they be working correctly ? If no, then why ?
Thanx in advance,
serg
hi,
how long a rebuildm.exe takes to rebuild a master DB?
TIA
Hey! I downloaded the sql engine and made a bunch of tables in visual basic. They were called new1, new2, new3, and new4. So I deleted new1.mdf, new1.ldf, new2.mdf, etc... but I notice I still can't recreate them. So I'm thinking there's remnants in master.mdf or something?? How would I go about getting rid of all the old tables, even ones whose names I've forgotten, so I can have a clean slate again? (btw I don't have sql server, just the desktop engine)
View 4 Replies View RelatedALL,
When I attempt to restore a master database I am having some issues. I am using SQL Server 2000. I have set the start up option with -c -m to set the database in single user mode.
When I run the restore from enterprise manager I am get an popup window that is titled "SQL-DMo(odbc SQLSTATE:HY000)" with not message and then the database conection is lost. Thus the master datbase is appears to it was not resotred. As when I close enterprise manager and then restart the MSSQLSERVER service the database is in the same state.
Please advise on how i can get the Database restored.
Thanks
Chris
Could anyone guide me how to restore Master, msdb to different server from the backup files.
thanks in advance.
Hi all,
I am new to this forum.could any one help me when and how to use master..spt_values ?
Thanks,
Mathi