Questions tagged [system-databases]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
0 answers
19 views

An error occurred during service master key initialization (sql error log)

I have migrated sql server 2017 to new hardware. The sql error log shows following error log whenever the sql server service is restarted. An error occurred during service master key initialization I ...
user avatar
  • 2,130
0 votes
0 answers
22 views

How to deal with encryption keys during a migration?

I am migrating sql server 2017 to another hardware. I have backed up the system dbs, user dbs and restored it on the destination. Now I am reading about the concept of service master key which is ...
user avatar
  • 2,130
-1 votes
0 answers
33 views

After a server OS drive crash, how to restore user dbs after restoring the system dbs? [duplicate]

Assuming sql server OS drive crashes and data/log files are safe in their own drives. I installed sql server with same version on a new machine. Then I restore the system databases (master, msdb, ...
user avatar
  • 2,130
-3 votes
2 answers
61 views

Is the master db aware of user dbs?

Where does SQL Server record the presence/paths of user dbs? I tried following approaches: Approach 1. I restored a master db on svr2 from a backup from svr1 Apprpach 2. I replaced the master db data/...
user avatar
  • 2,130
-1 votes
1 answer
46 views

Where does sql server hold the paths of data and log files?

Sql server configs (logins/roles/server configs) are stored in masterdb Jobs/schedules are stored in msdb Where are the following values stored: Data and log file path of system dbs Data and log ...
user avatar
  • 2,130
1 vote
2 answers
54 views

What is the practical purpose of backing up the system databases?

I know that system databases hold logins, security info, jobs, etc. Assuming I have backup of all system and user databases, and the SQL server crashes, then I will have to re-install the SQL server ...
user avatar
  • 2,130
0 votes
1 answer
37 views

Reorg SYSIBM tables to reduce extents (XT) - change priqty & secQTY

I have a new DB2 V12 database on z/OS 2.4 z13, with hundreds of programs to rebind, such as: sysibm.syspackage, sys.columns, sys.tables, etc. These will grow, hence the need to reorganize the ...
user avatar
2 votes
3 answers
383 views

Will SQL Server go offline if it loses network connectivity to SAN where master and msdb system databases reside?

I have a setup where 3 servers combined into Availability Group All 3 servers have local directly attached SSD drives, and user database files are hosted on these drives But the system databases (...
user avatar
0 votes
3 answers
2k views

Postgresql Backup all databases seperate files

I need to create an automatic backup of all databases from PostgreSQL ( running on a Windows machine ). I need to know if is possible to get backup of all databases but on separate files? Using ...
user avatar
0 votes
1 answer
533 views

What is difference between starting Instance with -T3608 and with -f?

I simulated a corruption of model database on test environment just for learning purposes. While reading others blogs and articles about it I stuck on the question, what is difference between starting ...
user avatar
  • 1,163
-1 votes
1 answer
78 views

Ola Hallengren Database Backup keeps failing (for both SYSTEM and USER databases) [closed]

Both USER and SYSTEM backup database jobs have been running fine until some weeks back when I checked my alerts (bearing in mind that this is on DEV where I do my own testing etc). I am just confused ...
user avatar
1 vote
1 answer
3k views

ERROR 1067 (42000): Invalid default value for 'uploadedDate'

Hi everyone i am getting an error trying to run the below query ERROR 1067 (42000): Invalid default value for 'uploadedDate' CREATE TABLE `filenew` ( `id` int(11) NOT NULL AUTO_INCREMENT /* ...
user avatar
0 votes
1 answer
30 views

Error when assigning a role to user

I have created a role called "role1" with these steps : postgres=# CREATE ROLE role1; CREATE ROLE postgres=# GRANT CONNECT ON DATABASE paybills TO role1; GRANT postgres=# \c paybills You are now ...
user avatar
0 votes
0 answers
27 views

How to migrate the SQL Server instance and system databases to a different drive (mapped to a different letter) on the same server? [duplicate]

I'm tasked with moving our SQL Server instance and system databases from the existing drive it's on to a new drive on the same server. I know I can stop the SQL Server service, literally move the ...
user avatar
  • 24.1k
0 votes
0 answers
56 views

Need an ERD of the schema mentioned

Need the erd of the schema for doctor, patient and appointment database. scenario is given as under: Clinic Management Entity-Relationship Model -Doctor has a unique id, name, address, qualification ...
user avatar
8 votes
3 answers
1k views

Is it possible for master, tempdb, model and msdb to have a database_id other than 1,2,3,4 respectively?

SQL Server System Databases, as far as I know, always have those same IDs and I have seen lots of maintenance scripts on the internet relying on the predicate WHERE database_id > 4 to exclude them ...
user avatar
  • 4,384
0 votes
1 answer
77 views

SQL Server Command Line install - Default database directories

Is there any way using the Command Line install method, I can specify a custom location for the system databases? I want them to be in D:\SqlServer\Data or is this only way to do this to install ...
user avatar
  • 1,450
1 vote
2 answers
173 views

Does restoring a database in Microsoft SQL Server add objects to Resource Database?

As far as I know the resource database contains information on all objects in its server instance, so I would think it's only natural that when you restore a database from a backup file, part of the ...
user avatar
  • 113
0 votes
1 answer
119 views

Does Data Migration Advisor migrate system databases?

I would like to know if Data Migration Assistant copies system databases (master, model, msdb) into the new server.
user avatar
  • 1,163
2 votes
1 answer
337 views

SQL statement to hide all system databases in Server from a server login [duplicate]

I want to create a Windows authentication server login and make it cannot see any system databases e.g.master, model. I know that we can control view definition in database level e.g. database user, ...
user avatar
0 votes
1 answer
170 views

Impact of moving the ldf and mdf files temporarily for few hours [duplicate]

I want to copy my database files(ldf and mdf) into a separate box for few hours as there is a disk space issue in my current sql server box. Currently i have mdf in one drive(:D) and ldf in another ...
user avatar
  • 1,285
1 vote
2 answers
131 views

How to check if sysadmin logins have a master database as a default database in T-SQL?

I want to check whether all the sysadmin users in my sql server instance have a master database as their default database AND if not change their default database to master database. I tried the ...
user avatar
  • 123
5 votes
1 answer
12k views

Could not obtain exclusive lock on database 'model' - who is using it?

when trying to run the following query in order to create a new database: CREATE DATABASE [Lunch] CONTAINMENT = NONE ON PRIMARY ( NAME = N'Lunch', FILENAME = N'E:\Data Files\...
user avatar
1 vote
2 answers
1k views

How to Locate Resource System Database in MS SQL Server

There is a database called Resource database, which I know that is read only and contains information about system objects and does not save any user related data or metadata. I am using SSMS to ...
user avatar
  • 2,006
2 votes
1 answer
428 views

When is autoclose OK to be not OK

By chance, I happened to see that the autoclose feature on SQL dbs (I'm using SQL 2008) shouldn't be on and I found that all of my SQL dbs were set to have autoclose on. I've set autoclose to off on ...
user avatar
0 votes
0 answers
163 views

Expanding Cluster Drive space

I will need to expand my existing cluster drive (sql cluster with 2 node active and passive) which has sys and user database data files. My san admin told he will need to delete all the existing data ...
user avatar
  • 1,007
1 vote
3 answers
3k views

Find a specific table and column value across all databases

This is a question about logic that could help me understand more how to iterate into my DBs. On my server I have several AdventureWorks* databases: I have a query that cold help me iterate through ...
user avatar
1 vote
2 answers
3k views

How to move SSISDB database

I have a SQL 2016 instance installed. It has SSISDB for SQL server integration services installed. Now because it is taking up all the space on our system drive, I need to move its data and log files ...
user avatar
  • 11
4 votes
3 answers
2k views

Does SQL Server re-create system databases if they are lost/missing?

We have a third-party server backup tool running every 24 hours, which backs up everything (files and database in preparation for a bare-metal restore). After much investigation, it emerged that the ...
user avatar
  • 778
3 votes
2 answers
140 views

Where are SQL Server catalog views persisted?

It seems that sys catalog views can be found under every database, either built in or user created. Are they all actual view definitions created under each of the database or just "links" to somewhere?...
user avatar
1 vote
1 answer
142 views

Restoring system databases on Availability group replicas

I'll start this off by saying I'm very new to availability groups. Looking at information on availability groups, it looks like you can't add system databases to a group. MSDN seems to indicate that ...
user avatar
  • 23
-1 votes
1 answer
8k views

Adding data to a newly added column

I added an extra column to my table and now need to populate it against the data already there. As a dummy example, I have a table already named customers with CustomerID, FirstName, LastName. I've ...
user avatar
  • 1
1 vote
2 answers
315 views

Do user database settings affect maintenance job times for system databases?

I have an SQL Server 2008 and for maintenance solutions I use Ola Hallengren scripts. Recently, out of 15 databases I have on this server, I made two changes in three of them (not system databases, ...
user avatar
0 votes
1 answer
256 views

Rebuild master database only in sqlserver 2012

How can I rebuild system database master only when I rebuild system database. Is it possible?
user avatar
  • 1
0 votes
1 answer
349 views

Retrieve linked servers out of system database files?

I have the data and log files of a SQL-Server that has crashed. It's user databases have been restored. I am looking for the jobs and linked servers that have been there before the crash. I attached ...
user avatar
  • 4,595
3 votes
1 answer
161 views

Migrating system and user databases to a new server by moving the LUN

I have to replace a server. The system and user databases all reside on the E: drive which is a SAN LUN. The method they want to employ is to remove the LUN from the current server and present it to ...
user avatar
2 votes
1 answer
168 views

Permissions to be granted to developers to investigate jobs, queries, and packages

I have just installed a new sql server 2014 server, migrated some databases there and connected some applications to them. These servers have also SSAS and SSIS. I have a group of developers there ...
user avatar
0 votes
1 answer
481 views

Order to restore system and user databases

We are going to move an sql instance to a new physical server (SQL SERVER 2008 r2). The answer to this question explains how Agent jobs and ssis-packages will be moved by restoring the master and msdb-...
user avatar
  • 563
0 votes
2 answers
1k views

SQL Server: Enable Full-Text Search on model Database

I want to use the Microsoft Data-Tier Application framework and its associated .Net API to manage the schema of several SQL Server databases, including the model database. However, when I try deploy ...
user avatar
3 votes
1 answer
2k views

want to find out which databases are used in last 30 days or not

I have about 120 databases in my SQL. I want to migrate so I want to know which databases are not used in last 30 days.
user avatar
  • 31
1 vote
2 answers
532 views

How Can I Read Where TempDB's Files Will Be Created After Restoring Master DB?

I'm restoring to an alternate server in a DR drill and have restored master but can't restart the instance, even with /f and /m switches. The log is indicating tempdb can't be created, probably ...
user avatar
  • 1,913
2 votes
2 answers
3k views

CHECKDB fails on MSDB and Master system databases

I get the following error within my maintenance plans: Executing the query "DBCC CHECKDB(N'master') WITH NO_INFOMSGS " failed with the following error: "The database could not be exclusively ...
user avatar
  • 1,355
1 vote
2 answers
855 views

Does leaving a connection open for a matter of seconds cause deadlocks?

I have been testing a system application and have noticed that it is slow when it connects to the database overseas. This is to be expected to some extent, but there is a big difference with the ...
user avatar
3 votes
2 answers
5k views

Where can I get the master.dacpac file for SQL Server 2012?

I am using Visual Studio 2012 Professional with SQL Server Data Tools with a SQL Server 2012 standard edition database instance. I have been unable to locate a master.dacpac file on either my ...
user avatar
  • 2,008
0 votes
1 answer
39 views

SQL Server 2005 Whole-Migration, which databases should I recreate?

In relation to my question about SQL Server 2005 migration procedure, I am re-creating the databases on the new server. I noted that the old server has the following databases (outside of System ...
user avatar
  • 187
4 votes
2 answers
15k views

Change drive letter (which contains system dbs)

Is it possible to safely change the drive letter for a volume which holds only system databases? What precautions should be taken and how should it be done? I know I can just go to computer ...
user avatar
1 vote
2 answers
3k views

SQL Server 2008 R2 not starting after moving model database

SQL Server 2008 R2 not starting after moving model database. Any ideas? Error: 945, Severity: 14, State: 2. Any ideas? Sql 2008 R2 will not start with trace -f -T3608 or single user mode -m. Need ...
user avatar
  • 11