I would like to know if Data Migration Assistant copies system databases (master, model, msdb) into the new server.

I have installed an instance of SQL Server 2016 SP2 called DNF containing 2 DBs and also the system DBs. After providing the instance to the dev team, they realised that they need to change the collation. They changed the collation for the 2 DBs by themselves and asked me to make the change for the system DB. I took a backup of all databases and rebuilt the system DB. It works fine and all the dbs changed to new collation. I made a mistake by restoring the msdb, model and finally I tried to restore the master. I succeeded to restore the model and msdb and faced a lot of issues when coming to themaster. I ran the query select name, collation_name from sys.databases and found that the model and msdb have the old collation, and this is logic and was a mistake from me. I tried to rebuild again, but collation refused to change on model and msdb and found the sql server and sql server for the instance stopped and won't start anymore. The situation now is : master and the 2 non system DBs have the new collation and model, msdb have the old one, and the services won't start. What is the way to solve this issue? Does an instance repair will solve this issue? Or there is something else that I can do? .

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 tablespace for performance. Maintenance window is one hour, so just one tables space is being done. 1. Is reorg run on system tables the same as for user tables? 2. Is there special IBEGENR (sp?) VSAM redefine needed for system tables. Sample JCL:

//REORGS1 EXEC PGM=DSNUTILB,PARM='MT03,REORG1DB' 
   //STEPLIB  DD  DSN=DSNC10.SDSNLOAD,DISP=SHR 
   //SYSREC   DD  DSN=&&SYSREC,DISP=(NEW,DELETE,DELETE),       
   //             SPACE=(TRK,(9000,5000),,,ROUND),UNIT=SYSDA 
 LISTDEF TBLSLIST                                     
     INCLUDE TABLESPACES DATABASE TR032             
 REORG                                                
    TABLESPACE LIST TBLSLIST   COPYDDN(CDD1) LOG NO  
    SHRLEVEL REFERENCE                               
    SORTKEYS SORTDATA SORTDEVT SYSDA                 
    STATISTICS TABLE(ALL) INDEX(ALL)

`

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 into another SQL Server instance as a test, to write documentation in case of total loss. I was able to restore the datawarehouse databases, msdb and model, but the problem started when I restored master. I restored it in single-user mode, with replace and it worked, but then my SQL Server instance stopped and now I can't restart it. It says something like 'request error or the service did not response in time'. I've checked the log folder and here's a part of the error message: > Service Master Key could not be decrypted using one of its encryptions. See sys.key_encryptions for details. > An error occurred during Service Master Key initialization. SQLErrorCode=33095, State=8, LastOsError=0. And > Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf for file number 1. OS error For each of the databases. Is a restore of the master database necessary or can I pass on it? What will I lose? Hope you guys can help me with this issue, and sorry for my English.

Similiar question : we are trying to secure a machine with backups on a Hyper-V host which also runs a MSSQL database in a small company and we don't want to back up a partition where all VM disks reside, however I've noticed that someone installing the database service left MSSQL16.MSSQLSERVER folder with system databases and the Resource database on the same drive (non-default location, F:\SQL\MSSQL16.MSSQLSERVER). I want these files to be covered with backups "just in case" and I have normal SQL backups in place, but right now if I restore the full machine backup, these files won't be there. I've seen people suggesting to backup these files, reinstall MSSQL during the installation pick a new installation. Some places suggest copying files to a new location while the DBs are off, changing some registry edits and edits in SQL Server configuration Manager. Ultimately, what would be the best solution in my scenario? I'll add that MDF/LDF files for production databases are on separate disks.

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 were helping with the connections and doing all the last developments on packages, and data warehousing stuff. Now that everything is working, I want to remove the sysadmin permissions that I had previously granted them. Now, these guys they have some requirements, that I am willing to grant them, so that it is less weight on my shoulders: > 1) They need to see query plans, 2) they need to identify missing > indexes and other expensive operations like key lookups, etc > > 3) they need to query sys views and tables - monitor what procedures > are running, collect WAIT STATS, etc > > 4) they also need to troubleshoot JOBS - so they need to be able to > see them, see the job history, in order to find out what went wrong > > 5) they also need to see everything in the SSIS integration catalog, > so that when something goes wrong inside one of there packages they > are able to figure it out by themselves I have granted them with the following **SERVER permissions**: > 1) view server state > 2) view any definition on the **MSDB database**: I got some ideas from "SQL Server Agent Fixed Database Roles " and finally on the **SSISDB database** Anything else that I should be aware of?

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 pg_dumpall creates a script that has all databases in it. But I need each database in a separate file.

SQL Server [System Databases](https://learn.microsoft.com/en-us/sql/relational-databases/databases/system-databases?view=sql-server-ver15) , 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 from the actions of the script. Also, if I run SELECT name, schema_id FROM sys.schemas; on a new user database, I get: name schema_id dbo 1 guest 2 INFORMATION_SCHEMA 3 sys 4 db_owner 16384 db_accessadmin 16385 db_securityadmin 16386 db_ddladmin 16387 db_backupoperator 16389 db_datareader 16390 db_datawriter 16391 db_denydatareader 16392 db_denydatawriter 16393 I ran that query on two different instances, one a SQL Server 2016 and the other a SQL Server 2005, and both returned that same result. Questions: ---------- 1. Is there any situation (or sql server version) in which the system databases master, tempdb, model and msdb would have a database_id other than 1,2,3,4 respectively? 2. Can I **really** trust on the fact that the schemas I listed will always have the same IDs on any instance of SQL Server so that I can write maintenance scripts based on those IDs?

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 business needs, and works well. We use Ola Hallengren's SQL Server Backup solution. In addition, we take full backups of our system files (master, model, msdb, SSISDB). I recently noticed that these backups have no cleanup time defined, leading to indefinite retention. I've looked for what is the best practice concerning retention of system database backups, but cannot find any guidance. Just that it is best practice to take full backups of these databases. I'd love to get these backups configured properly, and recover some of our drive space.

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\Lunch.mdf' , SIZE = 110592KB , FILEGROWTH = 1048576KB ), FILEGROUP [DATA] ( NAME = N'Lunch_Data', FILENAME = N'E:\Data Files\Lunch_Data.ndf' , SIZE = 110592KB , FILEGROWTH = 1048576KB ), FILEGROUP [NONCLUSTERED_INDEXES] ( NAME = N'Lunch_nonclusteredindexes', FILENAME = N'E:\Data Files\Lunch_nonclusteredindexes.ndf' , SIZE = 110592KB , FILEGROWTH = 1048576KB ) LOG ON ( NAME = N'Lunch_log', FILENAME = N'F:\logFiles\Lunch_log.ldf' , SIZE = 524288KB , FILEGROWTH = 524288KB ) GO Getting the following error message: > Msg 1807, Level 16, State 3, Line 1 > > Could not obtain exclusive lock on database 'model'. > > Retry the operation later. > > Msg 1802, Level 16, State 4, Line 1 > > CREATE DATABASE failed. > > Some file names listed could not be created. Check related errors. what is using the model database and does not allow me to obtain the exclusive lock?

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 recently restored the master, msdb databases from older hardware to new hardware. Linked servers don't work for example if I try to create a linked server it gives following error: An error occurred during service master key decryption. Same error when creating credential. I understand this is something related to the SMK (service master key). Doesn't the SMK already exist since I have restored the master database (assuming the SMK is held in the master db)? Why am I getting these errors? Why can I not create/access linked sever and credentials?

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 manage my database instance and I can only see four system databases - master, model, msdb, tempdb. I'd like to know where can I find the *Resource* database using SSMS? Or is the database intentionally invisible for users?

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](https://learn.microsoft.com/en-us/sql/relational-databases/databases/resource-database?view=sql-server-ver16#accessing-the-resource-database). Some of these IDs can be passed to OBJECT_NAME() or OBJECT_DEFINITION() Metadata functions but some IDs return NULL

SELECT SERVERPROPERTY('ResourceVersion')  AS 'ResourceVersion'
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime') AS 'ResourceLastUpdateDateTime' 

SELECT 
	defs.database_id
	, defs.object_id
	, OBJECT_NAME(defs.object_id) AS objName
	, OBJECT_DEFINITION(defs.object_id) AS objDef
	, OBJECT_NAME(defs.object_id, defs.database_id) AS objNameDB
	, OBJECT_DEFINITION(defs.object_id, defs.database_id) AS objDefDB
FROM sys.dm_exec_function_stats AS defs
WHERE defs.database_id = 32767

I can find the fn_hadr_is_primary_replica function (object_id = -665919249) in the sys.all_objects or in sys.all_sql_modules, but not the object_id = -706609674. Is there any way how to find the object name or definition of the -706609674?

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 management > storage and change drive letter but can have negative consequences on SQL Server operation?

*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 the issue was.* #### Background I was writing some code to access the INFORMATION_SCHEMA. I wanted to make a MySql data source so that I could create a data set out of it and generate the select/fill/etc. functionality from a typed DataSet. When I connected to the DB without a DB it worked fine, we just updated everything to uft8mb4 from the new utf8mb3 default, because .NET does not support that character set. When I tried to connect to INFORMATION_SCHEMA, everything looked okay except when I tried to open the tables, I got the .NET doesn't support utfmb3 error. #### Changing character_set_system I went to look at the Server variables on Workbench, and noticed that everything was updated to utf8mb4 except for the character_set_system. I had read that you have to be careful updating that but well, I added character_set_system utfmb4 to my.cnf and rebooted in one fell swoop, and only a second later did I realize what I had done. At that point I get an error message telling me to run systemctl status mysqld.service and journalctl -xe. I snipped what seemed to be the relevant portions of those logs (domain names have been changed to protect the guilty): ##### journalctl -xe

Jun 06 10:19:21 servername.tld polkitd: Registered Authentication Agent for unix-process:32382:26043948 (system bus name :1.186 [/usr/bin/pkttyagent --notify-fd 5 --fallback]
Jun 06 10:19:21 servername.tld systemd: Starting MySQL Server...
-- Subject: Unit mysqld.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel 
--
-- Unit mysqld.service has begun starting up.
Jun 06 10:19:31 servername.tld systemd: mysqld.service: main process exited, code=exited, status=1/FAILURE
Jun 06 10:19:31 servername.tld systemd: Failed to start MySQL Server.
-- Subject: Unit mysqld.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel 
--
-- Unit mysqld.service has failed.
--
-- The result is failed.
Jun 06 10:19:31 servername.tld systemd: Unit mysqld.service entered failed state.
Jun 06 10:19:31 servername.tld systemd: mysqld.service failed.
Jun 06 10:19:31 servername.tld polkitd: Unregistered Authentication Agent for unix-process:32382:26043948 (system bus name :1.186, object path /org/freedesktop/PolicyKit1/Aut

##### systemctl status mysqld.service

Jun 06 10:19:31 ● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Mon 2022-06-06 10:19:31 EDT; 7min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html 
  Process: 32415 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=1/FAILURE)
  Process: 32388 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 32415 (code=exited, status=1/FAILURE)
   Status: "Server startup in progress"

Jun 06 10:19:21 servername.tld systemd: Starting MySQL Server...
Jun 06 10:19:31 servername.tld systemd: mysqld.service: main process exited, code=exited, status=1/FAILURE
Jun 06 10:19:31 servername.tld systemd: Failed to start MySQL Server.
Jun 06 10:19:31 servername.tld systemd: Unit mysqld.service entered failed state.
Jun 06 10:19:31 servername.tld systemd: mysqld.service failed. systemd: mysqld.service failed.

At this point I figured I really broke things, because I had seen people warning not to change those without knowing what you were doing. Which, clearly, I did not. I went back to my.cnf and changed the setting to character_set_system utf8mb3 I would say 'changeed it back' but I didn't, since it wasn't like that to begin with. I just added the setting. Upon restarting, I got the same message about checking the errors, the errors are practically identical, so trying to set it to mb3 didn't make a difference. Anyway, I've searched dba/serverfault/stackoverflow/google for how to fix this, and I saw lots of people warning not to do it, but, I, at least, was unable to find anyone who had actually been reckless enough to do it. Maybe we can back up the data somewhere and then restore a backup from last week, and then put the data back, worst case? I'm just not familiar enough with the back end to know what horrors I have done to the data.

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/log files of svr2 with with copy from svr1 In both approaches, the GUI creates the user dbs on svr2 in recovery pending mode. Hence I want to ask where exactly does sql server store the information about whether user dbs are attached to it and their paths.

Sql server configs (logins/roles/server configs) are stored in masterdb Jobs/schedules are stored in msdb Where are the following values stored: 1. Data and log file path of system dbs 2. Data and log file path of user dbs. I want to know this because when I restore master db on another server then it doesn't auto create the user dbs in the GUI.

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 which will have its own system databases. It does not make sense to restore the system db backups that pertain to the crashed server onto the new server (I don't think this is even allowed). So, what is the practical purpose of backing up the system databases?

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 params to start SMS to move Model back.

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, database role but I don't want to configure them to all system databases. Therefore, I started by: 1. Create Windows authentication login 2. Create custom server role 3. Deny view definition to a custom server role 4. Add an login to a role I hope the login shouldn't see all system databases but it still can see master and tempdb under System Databases node. This is the screenshot of the result. and these are my SQL statements. -- Create login from Windows User USE MASTER GO CREATE LOGIN [domain\web_user] FROM WINDOWS WITH DEFAULT_DATABASE = MyTestDB GO USE MyTestDB GO CREATE USER web_user FOR LOGIN [domain\web_user] WITH DEFAULT_SCHEMA = dbo GO EXEC sp_addrolemember @rolename = 'DB_DataReader', @membername = 'web_user' GO --Create custom server role, deny view definition and add login to role USE master GO CREATE SERVER ROLE junior GO DENY VIEW ANY DEFINITION TO [junior] GO ALTER SERVER ROLE junior ADD MEMBER [domain\web_user] ​ GO