Questions tagged [system-databases]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1 vote
0 answers
55 views

How to restart SQL Server Instance after restore master

At work we have a productive SQL Server working, and we create backups of each database periodically, including system databases master, msdb and model. I'm trying to restore the whole datawarehouse ...
santiago arnesano's user avatar
-3 votes
1 answer
477 views

System dbs have different collation

i am new on this forum.i have installed an instance of sql srv 2016 sp2 called DNF containing 2 dbs and also the system dbs.after providing the instance to the dev team the realise that they need to ...
Toni's user avatar
  • 1
0 votes
1 answer
721 views

What is an ideal retention policy for SQL Server system database backups?

We have defined a data retention policy for our user databases, where our full backups are retained for 90 days, diffs are retained for 30 days, and logs are retained for 48 hours. This meets our ...
Chad's user avatar
  • 103
3 votes
1 answer
175 views

Finding the names of objects in the Resource database [duplicate]

I'm looking at the sys.dm_exec_function_stats view to find scalar function stats. That view also includes rows from a database with an id 32767, which I've found is the Resource database. Some of ...
Zikato's user avatar
  • 4,403
0 votes
2 answers
651 views

How can you repair a MySQL 8.0 Installation after changing `character_set_system` from utfmb3 to utfmb4 fails?

NOTE: My issue was due to a syntax error in the my.cnf file, not anything to do with errors on converting to utf8mb4. I still do not have any idea how you do that. I posted an answer explaining what ...
shelleybutterfly's user avatar
0 votes
0 answers
1k 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 ...
variable's user avatar
  • 3,533
-3 votes
2 answers
119 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/...
variable's user avatar
  • 3,533
-1 votes
1 answer
462 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 ...
variable's user avatar
  • 3,533
1 vote
2 answers
70 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 ...
variable's user avatar
  • 3,533
0 votes
1 answer
175 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 ...
TechnoCaveman's user avatar
2 votes
3 answers
861 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 (...
Aleksey Vitsko's user avatar
1 vote
3 answers
7k views

Postgresql Backup all databases separate files

I need to create an automatic backup of all databases from PostgreSQL (running on a Windows machine). I need to know if it is possible to get a backup of all databases but in separate files. Using ...
Anisa Ibro's user avatar
0 votes
1 answer
1k 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 ...
Rauf Asadov's user avatar
  • 1,253
-1 votes
1 answer
272 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 ...
user211640's user avatar
1 vote
1 answer
7k 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 /* ...
Jones smith's user avatar
0 votes
1 answer
40 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 ...
anaigini's user avatar
0 votes
0 answers
30 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 ...
J.D.'s user avatar
  • 37.1k
0 votes
0 answers
120 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
10 votes
3 answers
2k 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 ...
Ronaldo's user avatar
  • 5,069
0 votes
1 answer
267 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 ...
SE1986's user avatar
  • 1,950
1 vote
2 answers
287 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 ...
J. Doe's user avatar
  • 113
0 votes
1 answer
143 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.
Rauf Asadov's user avatar
  • 1,253
2 votes
1 answer
627 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, ...
theeranitp's user avatar
0 votes
1 answer
296 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 ...
user9516827's user avatar
  • 1,305
1 vote
2 answers
215 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 ...
AiB's user avatar
  • 123
8 votes
2 answers
22k 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\...
Marcello Miorelli's user avatar
4 votes
3 answers
1k views

Locating the Resource system database

There is a database called the 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 ...
igelr's user avatar
  • 2,120
2 votes
1 answer
619 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 ...
Tom Brown's user avatar
0 votes
0 answers
352 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 ...
SQL_NoExpert's user avatar
  • 1,049
1 vote
3 answers
4k 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 ...
Francesco Mantovani's user avatar
1 vote
2 answers
4k 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 ...
Jac's 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 ...
EvilDr's user avatar
  • 840
3 votes
2 answers
195 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
195 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 ...
NikhilC's user avatar
  • 23
-1 votes
1 answer
10k 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 ...
Aaron's user avatar
  • 1
1 vote
2 answers
320 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, ...
xrpapak's user avatar
  • 51
0 votes
1 answer
269 views

Rebuild master database only in sqlserver 2012

How can I rebuild system database master only when I rebuild system database. Is it possible?
manoj's user avatar
  • 1
0 votes
1 answer
391 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 ...
Magier's user avatar
  • 4,777
3 votes
1 answer
235 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 ...
Windsor19's user avatar
2 votes
1 answer
198 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 ...
Marcello Miorelli's user avatar
0 votes
1 answer
559 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-...
GHauan's user avatar
  • 603
0 votes
2 answers
2k 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 ...
Dan Forbes's user avatar
3 votes
1 answer
3k 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.
Mike's user avatar
  • 31
1 vote
2 answers
566 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 ...
MattyZDBA's user avatar
  • 1,933
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 ...
AKDiscer's user avatar
  • 1,365
1 vote
2 answers
1k 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 ...
Damien Golding's 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 ...
Mark Freeman's user avatar
  • 2,171
0 votes
1 answer
42 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 ...
pepoluan's user avatar
  • 187
4 votes
2 answers
17k 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 ...
WhoIsNinja's 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 ...
Tony's user avatar
  • 11