Identifying System/User Databases
Nov 12, 2002Hi,
i´m looking for TSQL-Code (7.0/2000) to identify, if a SQL Server Database ist a SystemDatabase or a UserDatabase. In the sysdatabases there is no information abount that.
Skol,
Peter
ADVERTISEMENT
Identifying Difference Btwn System And User Objects??
Mar 17, 2000Does anyone know the logic that Enterprise Manager/SQL Server uses to distinguish system objects from user objects. I've looked through all the sysobjects in the database and there doesn't seem to be any unique logic for seperating all objects. User tables are easily distinguished from system tables (U/S) but thats not the case with stored procedures and views. I'm looking for suggestions...
Thanks,
Adam
Restricting Data Access To System Database And Visibility To All Other User Databases...!
Jan 31, 2008Hi,
How would I set permission for SQL Server 2005 "User A" to prevent access to System and other user databases, also How to hide the databases that "User A" has no rights to. I mean, when User A logs in, All other user databases are not visible to him/her.
Thanks,
SQL Server Admin 2014 :: Changing Drive Letter For System Database And User Databases
Oct 18, 2013I have system database and user database file are present in G,H and W drive.The process is going to be - copy data from G to S, H to T, W to U. Rename G to X, H to Y and W to Z. Rename S to G, T to H and U to W. Reboot the servers. The original G, H and W will then be X, Y and Z. The old S will be the new G, old T will be H and old U will be W. My question is that after doing this whether my SQL server will start or not
View 8 Replies View RelatedIdentifying Databases
May 6, 2008Hi,can anybody tell me how to identify all the databases from all the servers that are being accessed by a particular group
View 11 Replies View RelatedSQL 2012 :: Identifying User With Row Locked
Feb 3, 2015Is there a way to identify what user has a locked row in a table?
View 2 Replies View RelatedSystem.Data.SqlClient.SqlException: Login Failed For User ''. The User Is Not Associated With A Trusted SQL Server Connection.
May 17, 2006
Hi all,
Can someone explain it to me why I am getting the following error when I try to connect SQL server express with .NET 2.0?
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Login failed for user ''. The user is not associated with a trusted SQL Server connection.Here is my code and i am using windows authentication:
<%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SqlClient" %>
<%
Dim connAkaki As SqlConnection Dim cmdSelectAuthers As SqlCommand Dim dtrAuthers As SqlDataReader connAkaki = New SqlConnection("Server=.SQLEXPRESS;database=akaki") connAkaki.Open() cmdSelectAuthers = New SqlCommand("select Firstname from UserTableTest", connAkaki) dtrAuthers= cmdSelectAuthers.ExecuteReader() While dtrAuthers.Read() Response.Write("<li>") Response.Write(dtrAuthers("Firstname")) End While dtrAuthers.Close() connAkaki.Close() %>
Programmatically Adding A User To The System User Role
Dec 27, 2006We have been working on an application that will be using a forms-authenticated report server (RS2005) as a reporting back-end. Using the reporting services web service I have been able to assign permissions to objects in reporting services no problem. The issue is that each user needs to be added to the System User role to be able to use the report builder properly. I can't seem to find a way to do this programmatically. Any idea?
View 1 Replies View RelatedSystem Databases
Jun 21, 2004I want to split the data and log files of the system databases to separate hard disks. How can i accomplish this and is this a good idea to do so ?
View 4 Replies View RelatedSystem Databases
Jun 18, 2008Hi,
How do I check he data file size, transaction log size and autogrowth setting of each of the system databases please?
I am not sure if sp_helpdb works for these?
Thanks
Move System Databases?
Apr 16, 2002Hi,
I would like to move system databases (master, model, and tempdb) from one disk to another. What would be the best way to do it?
your help is appriciated.
Cheers
System Databases Not Showing
Oct 25, 2000Hi,
I installed desktop edition of SQL Server7.0 but System databases are not displaying.only showing pubs and northwind only.any body help me.
Thank you
Novah
Backup System Databases
Feb 1, 2005hi,
I have just started a new job and they have nightly backups of the master, msdb and model databases.
But why would you need a nightly backup of the model database? surely the model database never changes, so would a one time backup not be sufficient?
Please advise me if I am incorrect
Cheers
Shrink System Databases
Apr 28, 2015I have some questions :
1.Is it OK to shrink Master, model ? the transaction log for those databases are full .
2.Is it OK to set the Recovery model of Database MODEL as SIMPLE ? at the moment it is in FULL recovery model and the transaction log is Full too
Do System Databases Need To Be Restored?
Sep 11, 2007The consultant hired as our Remote DBA for SQL Server 2005 recently changed the recovery model on our user database from Full to Simple. He explained this was necessary because the transaction log was growing very large and backing up the database when the model was set to Full was not shrinking it. Now, the master database and the msdb database are backed up daily along with our user database.
When we need to restore our user database, do we need to restore the master database and the msdb database too? What order should the databases be restored in?
How To Detach System Databases...
Jul 20, 2005We have C:, D: and E: drives on our server box. C: drive ispartitioned and is big enough only to hold Operating system files. D:and E: drives are what were supposed to be used by developers / dba'sto store / create SQL Server (system and user databases).Well, some developers installed the entire SQL Server named instanceand their system and user defined databases on the C: drive. Is thereany way to move the system databases (master, msdb, distributionetc.,) from the C: to the D: / E: drives?Appreciate any feedback.ThanksJagannathan Santhanam
View 3 Replies View RelatedRebuilding System Databases
Nov 29, 2007As an effort to rebuild system databases, I ran following command
start/wait C:Setup.exe /qn INSTANCENAME="DEV$SQL02" REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD="strongpassword"
I brought setup.exe file from installation cd to c drive of the server. My SQL Server is DEV and SQL Instance is SQL02. While running above command in command prompt (from C: prompt) I have an error masage that said 'setup failed' without any specific cause given for the failure.
I am doing above as part of disaster/recovery procedure in test environment.
Could anyone please shed some lights on what are the steps or what went wrong above while rebuilding master databases.
Thanks a lot
Mike
How Can I Move The System Databases?
Oct 26, 2005I have been attempting to detach and reattach the model database. I tried to set the Trace Flag 3608, but it is having no effect.
View 17 Replies View RelatedRestore All System Databases
Feb 8, 2007Hi All,
I need to do a restore of all my system databases, master, msdb and model, and of course my production database.
The Windows 2003 OS and Sql Server 2005 have both been reinstalled on the server.
I understand I need to be in Single User Mode to restore the master database and I just wanted to clarify the procedure for a successful restore.
I've found this page: http://msdn2.microsoft.com/en-us/library/ms190679.aspx which has instructions for restoring a master database.
So, all I have to do is:
1. Be in single user mode
2. Using SQLCMD run:
C:> sqlcmd
1> RESTORE DATABASE master FROM DISK = 'Z:MyBackLocationmaster.bak';
2> GO
From the instructions:
"After master is restored, the instance of SQL Server shuts down and terminates the sqlcmd process"
My Questions:
When I've restored the master do I repeat the steps above to restore the msdb and model databases?
Are there any other pitfalls?
Thanks for any advice
Melt
System Databases & Truncate Log On Checkpoint
Jul 7, 1999I am new to SQL Server 7 and have inherited a server built by a consultant that is no longer here. I have noticed that the system databases (master, msdb & model) are completed backed up on a nightly basis and are all set with truncate log on checkpoint. Is this the proper way to have things set up?
Thanks.
Mike
SQL 2012 :: System Databases Moving
Jan 11, 2014whenever we are moving system databases from one drive to another, do we need to move the path physically? or it automatically moves.I want to try the rebuild the sql server. So I want to corrupt the master database so, I deleted the master mdf file and restore it back but instead of database corruption it is giving the following error message. How can I corrupt the master database and can practice the rebuild the server.
And one more thing when I try to use the repair option I couldn't able to get all the things back to normal. The database engine service, replication is not working but SSAS, SSRS things are showing successfully repaired.
2014-01-10 21:34:26.44 Server Error: 26055, Severity: 16, State: 1.
2014-01-10 21:34:26.44 Server The SQL Server failed to initialize VIA support library [QLVipl.dll]. This normally indicates the VIA support library does not exist or is corrupted. Please repair or disable the VIA network protocol. Error: 0x7e.
2014-01-10 21:34:26.44 Server Error: 17182, Severity: 16, State: 1.
2014-01-10 21:34:26.44 Server TDSSNIClient initialization failed with error 0x7e, status code 0x60. Reason: Unable to initialize the VIA listener. The specified module could not be found.
[code]....
System Databases On Different Disk Partition
Aug 19, 2007Hi all,
I'm having some performance-wise thoughts about my new sql-server 2005 installation ...
I have my SQL installed on one partition (the system dbs are also on that partition by default), my regular databases (non system dbs) are on different partition.
The question is - if my sys dbs are on different partition, could I experience some performance issues ?
One senario that I can think of is when the SQL looks for SPs starting with sp_ in the master DB, the disk will have to check a different partition. Perhaps such senario was solved using some kind of caching methods on the sql server itself.
Hope my thread is in the right forum.
Cheers everyone.
Rebuilding System Databases On A Cluster
Jan 5, 2007I am having trouble rebuilding the SQL 2005 system databases on my cluster.
The Cluster hardware failed completely and I am recovering from scratch (but only 1 node at this time)
The Win2K3 OS is recovered and communicating with the domain.
Cluster services have started and the quorum is online.
The clustered SQL instance is offline because there is no master.mdf present.
I run the setup command as per BOL:
start /wait setup.exe /qn VS=<VSName> INSTANCENAME=<InstanceName> REINSTALL=SQL_Engine REBUILDDATABASE=1 ADMINPASSWORD=<StrongPassword> SAPWD=<NewStrongPassword> SQLACCOUNT=<domainuser> SQLPASSWORD=<DomainUserPassword> AGTACCOUNT=<domainuser> AGTPASSWORD=<DomainUserPassword>
The Summary.log reports the following:
Machine : CLUSTER1
Product : Microsoft SQL Server 2005
Product Version: 9.00.1399.06
Install : Successful
Log File : C:Program FilesMicrosoft SQL ServerSetup BootstrapLOGFilesSQLSetup0001_CLUSTERSVR1_SQL.log
--------------------------------------------------------------------------------
Setup succeeded with the installation, inspect the log file completely for status on all the components.
However the Microsoft SQL ServerMSSQL.1MSSQLDATA directory is empty. There are no system databases.
Without a rebuilt Master I cannot bring the clustered SQL instance online to then restore my database backups.
Any ideas?
D.
Moving System Databases SQL 2005
Mar 16, 2007I am in the process of moving my system databases to another volume. I have accommplished the first section pertaining to master database.
I have reached step 3 in the Resource database move section down below.
It states to change the FILENAME path to match the new location of the master database. Do not change the name of the database or the file names.
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= 'new_path_of_mastermssqlsystemresource.mdf');
I changed the query to point ot the new location of the master, (E:MSSQLData)
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= E:MSSQLDatamssqlsystemresource.mdf');
I get the following error when I run the query:
Could not locate entry in sysdatabases for database mssqlsystemresource. No entry found with that name. Make sure that the name is entered correctly.
What am I doing wrong? My syntax must be incorrect. But I can't figure it. Anybody done this before.
These are the steps per msdn.
****************************************************************************************************************
To move the master database, follow these steps.
1. From the Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In the SQL Server 2005 Services node, right-click the SQL Server (MSSQLSERVER) service and choose Properties.
3. In the SQL Server (MSSQLSERVER) Properties dialog box, click the Advanced tab.
4. Edit the startup parameters values to point to the planned location for the master database data and log files and click Apply. Moving the error log file is optional.
The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data and log files.
-dC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmaster.mdf;-eC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG;-lC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmastlog.ldf
If the planned relocation for the master data file is E:SQLData and the planned relocation for the log file is F:SQLLog, the parameter values would be changed as follows:
-dE:SQLDatamaster.mdf;-eC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG;-lF:SQLLogmastlog.ldf
5. Stop the MSSQLSERVER service.
6. Physically move the files to the new location.
7. Restart the MSSQLSERVER service.
8. Verify the file change.
SELECT name, physical_name, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('master');
********************************************************************************************
To move the Resource database, follow these steps.
1. Stop the MSSQLSERVER service if it is started.
2. Start the service in minimal mode. To do this, at the command prompt, enter <SQLPath>innsqlservr -c -f -T3608 where <SQLPath> is the path for the instance of ssNoVersion. For example, C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL. This will start the instance of ssNoVersion for master-only recovery.
3. Run these queries. Change the FILENAME path to match the new location of the master database. Do not change the name of the database or the file names.
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= 'new_path_of_mastermssqlsystemresource.mdf');
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= 'new_path_of_mastermssqlsystemresource.ldf');
4. Make sure the Resource database is set to read-only by running this query:
ALTER DATABASE mssqlsystemresource SET READ_ONLY;
5. Physically move the files to the new location.
6. Restart the MSSQLSERVER service.
How To Retrieve All Columns Of All Databases With System Sp.
Apr 27, 2008
hi
i want to retrieve all columns for all tables (for all databases) by this system sp :
Code Snippetexec sp_msforeachtable 'exec sp_columns ''?''' but when i execute this script, sql server return number of empty result set, how to solve this problem ?
thanks.
How Do You Hide The Default System Databases (i.e., Master)?
Feb 5, 2001How do you hide the default system databases so that they cannot be viewed? Also when it it time for me to access them how do I unhide the databases?
View 1 Replies View RelatedSystem Databases In Full Recovery Mode
Mar 6, 2006Hi
What are the pros and cons of putting the system databases in full recovery mode
I am asked by my manager to keep everything simple and uniform.We have all the user databases in Full recovery mode and he wants all the system databases as well to be full recovery mode.
I would like to get the points where in i can argue that, there is no point in having the system databases in full recovery mode.Unfortunately i dont see anything related to this in BOL or internet.
Plz help
Thanks
Sreenath
SQL Server 2008 :: Shrink System Databases
Apr 28, 2015I have some questions :
1.Is it OK to shrink Master, model ? the transaction log for those databases are full .
2.Is it OK to set the Recovery model of Database MODEL as SIMPLE ? at the moment it is in FULL recovery model and the transaction log is Full too
Move Mdf Files For System Databases To Another Location
Mar 18, 2008
Hello there,
I've been told that it is good practice to keep mdf and ldf files in another location... We have it in place for all our user databases, however mdf and ldf files for our system dbs are still at the same location. I was wondering what is the right way of splitting those should be?
System Databases Folders Disappeared From SSMS 9.0
Jun 14, 2007Suddently System Databases Folder is not showing on Studio 9.0 for all the SQL servers instance. Do you know why ? Is there any option other than De-attach them?
Sql Server 2000 System Databases Diagram(poster).
Sep 24, 2004Hi,
I was looking for system databases diagram(poster). If any one has this could you please point me to the URL where i can find it!!
Thanks in advance.
2005 Migration Do You Have To Migrate The 2000 System Databases?
Sep 20, 2007Hi all
I was just wondering on if I have to migrate the systems databases from 2000 ie. Master , Model & MSDB ??
Thanks
Restore SMK And System Databases (credentials And Linked Servers)
Apr 28, 2008Hi there
I have a concern about restore whole SQL server environment.
1st server:
x86 SQL Server 2005 EE SP2 + hotfix 3159 is installed on Windows 2003 SP1 R2. SQL Server is running under Local System Account. Server is not part of AD.
2nd server
Win XP SP2, x86 SQL Server 2005 DE SP2 + hotfix 3159. SQL Server is running under Local Admin Account. Server is part of AD. Server has Credentials and linked servers
I'm trying to move all databases from server 2 to server 1
For this I am doing:
1) Create backup SMK
2) Detach all user databases
3) Backup all system databases
4) Copy all this stuff to server 1
5) Run Server 1 in single-mode (-m)
6) Restore master database
7) Try to restore backup SMK that was done on step 1 and get this error message:
Msg 15329, Level 16, State 11, Line 2
The current master key cannot be decrypted. If this is a database master key, you should attempt to open it in the session before performing this operation. The FORCE option can be used to ignore this error and continue the operation but the data encrypted by the old master key will be lost.
My understanding is that credential secrets, linked server login passwords are lost and should be recreated. DbMK can be restored using passwords.
As far as I know this can be avoided if the same domain account is using for both servers. But the question still this - if I have stand alone server, using local system account for the SQL Server service and have encrypted objects - I cannot restore SQL Server on another box (say in case disaster lost original one) without loosing credentials and linked servers even if I have all backups including backup of SMK.
Sounds gloomily for me. Any idea what I missed?
PS This links
http://blogs.msdn.com/lcris/archive/2005/09/23/473464.aspx
http://blogs.msdn.com/lcris/archive/2005/07/08/437048.aspx
http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx
http://blogs.msdn.com/lcris/archive/2006/04/10/572678.aspx
and all relative topics in BOL were studied very carefully
Regards,
Gennadi