SQL Server Admin 2014 :: Copy Resource Database MDF For Upgrading
Feb 11, 2015
Copy mssqlsystemresource.mdf of a recently upgraded server and paste to an old server have same effect of upgrading via .exe installation?
My idea is to save time and administrative efforts in upgrades (Service Packs and/or Cumulative Updates) using this method.
According to BOL:
The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server.
What is the best approach for a read only copy of a database that is ~ 1TB. The primary database is fed nightly with an ETL process. We are currently trying to duplicate the ETL to read only server but that process is not going well. So we are looking at other options to let SQL make the copy.
The primary database is on a Win12R2 with SQL 12 or 14, a 2 node A/P failover cluster.
The read only copy will be on a Win12R2 with SQL 12 or 14. It is not a requirement to fail over to the read only copy if the primary should go down.
What would best the approach to accomplish the end result?
On one of our SQL Server 2014 boxes each database has a copy-only full backup made every night, in addition to the maintenance plan schedule of a full backup weekly, daily differential backups and log backups.
When performing a PIT restore in SSMS the restore file list lists the most recent copy-only backup as the full backup to use, not the most recent plan full backup. I noticed that using SSMS 2008 to start a PIT restore on the 2014 box does not have this problem, and lists the correct restore file sequence (ignores the copy-only backups).
I did tried the encryption on server "A" for database "AdventureWorks2012". Then I tried to restore to server "B". There was the certificate issue, and I thought "of course : it's encrypted ! Let's deactivate it". So here I go "ALTER DATABASE AdventureWorks2012 SET ENCYRPTION OFF".I look at sys.databases : not encrypted.I backup using no encryption, I verify using msdb.dbo.backupset : not encrypted.
I move my backup to my other server where encryption was never configured (so no certificate, nothing...), and I have the error : Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xFA130E58C999C4919B8975999C83A75A403B11D8'. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
And have chosen the destination - unstructered (flat) file. But the wizard proposes to export only one table (dbo.Acocount) and all the others from the list are not exported. How can I export ALL the data into one file.I need to do this to edit the syntax in the editor and then import this data and database structure into Postgresql
I have two databases like each other that one is the backup of another. Each DB have 2 filegroups. I want to replace one filegroup from one db to another. How do I do this? Or how do I backup and then restore?
Query to show logins that don't have any permissions within the SQL instance? I'm tasked with doing some cleanup and have found some cases where the database was deleted or moved to another server but the logins that used it were not deleted. I'd like to identify them to research.
For instance a query to show logins that have no permissions in any of the existing databases would be handy. I'm thinking it would be complicated by the need to loop through all of the existing databases and then outer join it to the list of instance level logins. Going to try to write something like that but was hoping that a script already exists.
I have multiple SQL 2008 severs with databases. Also, 1 mirroring server in place.
Since my database count is increasing can i have only 1 mirroring server. Is there any limit of db at mirroring server. I would have approx. 150 databases.
I want to Replace The Big Log database with A new one ( A database with same structure).But current DB has many connection .
This is my plan :
1- Create a new database with same structure.
2- Rename current database to olddb with this code :
USE master GO EXEC sp_dboption CurDataBase, 'Single User', True EXEC sp_renamedb 'CurDataBase', 'OldDataBase' GO 3- Rename Newdb to current DB. USE master GO EXEC sp_renamedb 'NewDataBase', 'CurDataBase'
is it true ? and Tsql code is ok ? (dont forget many of connection to curdatabase (that Is a log db) and loss some seconds data is not problems)
My database went into suspected mode. and after we had run some script, it came out from the suspected mode. but we encountered this error while opening table in database.
2009-11-02 15:46:42.90 spid51 Error: 824, Severity: 24, State: 2. 2009-11-02 15:46:42.90 spid51 SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:43686; actual 0:0). It occurred during a read of page (1:43686) in database ID 23 at offset 0x0000001554c000 in file 'H:MSSQL.SQL2008MSSQLDATAmy_db.mdf'.
Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
I tried to copy db from server to server by sa user ( sql login) but this error raised and the copy failed
Executed as user: NT ServiceSQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 9:55:24 AM Progress: 2015-05-11 09:55:24.45 Source: 10_32_0_201_10_32_0_202_Transfer Objects Task Task just started the execution.: 0% complete End Progress Error: 2015-05-11 09:56:31.87 Code: 0x00000000 Source: 10_32_0_201_10_32_0_202_Transfer Objects Task
An error occurred while transferring data. See the inner exception for details. StackTrace: at Microsoft.SqlServer. Management. Smo. Transfer. TransferData()The Execution method succeeded, but the
[code]....
number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:55:24 AM Finished: 9:56:32 AM Elapsed: 67.892 seconds. The package execution failed. The step failed.
Is there a better way to deal with the virtual log files?...I see several approaches in dealing/decreasing the virtual log files for a database..want to know what's the best n safest approach, from the masters here?
I have an environment with MS-SQL Server 2014 and always-on availability group configured (on 2-nodes).
I'm writing a Powershell Script which removes the database from the availability group (on the primary server) and then SHOULD drop the database on the secondary Server.
That works most of the time, but not always...
When it fails I get the error message:
Cannot drop database "Customer_2" because it is currently in use.
When i check the secondary DB-Server (sp_who2) while the script is running, i see that there is a process for the DB "Customer_2" with Status="background", Command="DB STARTUP" and LastWaitType="REDO_THREAD_PENDING WORK".
As soon as the script fails, this process for "Customer_2" disapears.
This happens always only on the second database in the availability group.
Why is the process still there, even after I removed the database from the Availability Group on the primary node.
If I remove the database from the availability group manually, the "background" process on the secondary node for that database disappears..
1) We are providing a e governance solution for an organization,where we are providing a centralized database,Client have provided 5 Database server for the same.how can we position the Database Server? there are 5000 Concurrent users and 25000 users,SAN Storage for approx. 60 TB,Database size of 2 TB and growth of 1 TB every year
2) How many instance can we have for above said Case?
We are in web site development company,Previously we don't have proxy configuration, after implementing Proxy , we have an issue to connect a remote database.
The error pops "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. Error 53".
I've got reporting services on a different box from the database and I can see all the reports, but when I try to setup a subscription, I get this weird error:
The SQL Agent service is not running. This operation requires the SQL Agent service. (rsSchedulerNotResponding)
The same error happens when I connect to the database server via management studio and try to run a job.
We have a 2 node clustered instance(SQL 2014) with 26 databases and we would like to enable alwayson for one of the databases for reporting (only one secondary and do not need high availability setup). I'm thinking if the reporting application/queries can explicitly connect to the secondary database(Instance namedatabase name) without using a listener and setup the secondary in asynchronous commit mode. Read about the REDO thread blocking due to reporting workload. How does this affect if I implement the secondary in this way.
I have SQL 2014. When I try to restore a user database using SSMS GUI, the Restore Database Pop up box never pops up. This happens for any database on this server at any time. Sometimes I get the pop up, some times I dont get.
So I tried to click on Databases on Top and Restore Database, and then select the db that I need to restore from Drop down, then it shows "creating restore plan selecting backups" but it takes forever.
We have full backup and trn log backups every 30 mins. So is it trying to get all these backup files in the background causing this issue? If yes then how to overcome this?
I would like to know what happens if i shrink the database with truncate only option and do a full backup or transaction log backup ? are the full backup or transaction log backup valid? I know that the performance of the database is bad if i shrink the database. What happens to full backup or transaction log backups?
I'm trying to figure out why this SQL Agent job keeps failing.
We used management studio, and connected as mydomainmyuser, and developed a script to take a backup file from a network share and restore it. It worked fine in SSMS under that login.
After we got it working , we created a SQL Agent job on the same server to run the script, and set the agent job to run under that account that we tested with.
This is the error message we got:
"Executed as user: mydomainmyuser. Create Database permission denied in database master'.
So, I gave that login the rights to Create Database and Create Any Database. Then the error message changed to:
"Executed as user: mydomainmyuser. User does not have permission to RESTORE database 'mydatabase'. [SQLSTATE 42000][ERROR 3013] RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed. "
* I can't use SA for the job account, because the SA account doesn't have rights to see the network folder where the backup file sits, so it has to run under the domain account. * The user is a member of the dbcreator role - and the serveradmin and sysadmin roles * The user is a member of dbowner on the database I am trying to overwrite with the restore * I have given the user the rights CREATE DATABASE and CREATE ANY DATABASE
The only suspicious thing I found was that it appears the server was renamed at one time. When I looked at the login in management studio, I was not able to change some of the rights. On the Securables page, it shows the server name as "MyServer-New", but the server name is "MyServer". It is a replacement, and I suspect that when they did the replacement they named it "MyServer-New", set everything up, then renamed it.
I found this post listed below, and ran the script (shown below), and it showed that the server name was MyServer and the ServerInstanceName is MyServer-New
[URL] .....
SELECT HOST_NAME() AS 'host_name()', @@servername AS 'ServerNameInstanceName', SERVERPROPERTY('servername') AS 'ServerName', SERVERPROPERTY('machinename') AS 'Windows_Name', SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS 'NetBIOS_Name', SERVERPROPERTY('instanceName') AS 'InstanceName', SERVERPROPERTY('IsClustered') AS 'IsClustered'
I can't reset SQL until the next maintenance window to test changing the server name as outlined in the post.
Am I on the right track with the name change messing up permissions, or is there something else I need to check?
We use SQL server always on feather on my database and we distribute statement on main database server and mirror database server for raise performance.
My police for split statement is DML (insert, update and delete) statement go to main DB and Read Data (select) statement go to mirror DB.
I want know can I use different index on main DB and mirror Database?
Because some index are used in mirror DB not used in main database.
What is the best way to restore a database from a folder of backups (including full, diff and log backups) without using the backup history in msdb?
I have a restore process that restores all backups on a regular schedule in order to fully verify their integrity. To do this, I use the backup history in msdb on each server that I'm monitoring. I had a thought the other day that I would be in trouble if I lost msdb. Then my backup history would only be as good as the last backup of msdb.
What I'd like to do is read a folder of backup files and generate a restore script up to a specified time. Would I use RESTORE HEADERONLY to do this? If so, would I use PowerShell to traverse each file in the folder?
This is my first deployment of an always on availability group for SQL 2014 and I'm trying to get my custom backup procedure to handle all databases appropriately depending on the primary group. Basiscally I want the system databases and all databases that don't participate in the availability group to be backed up on both nodes and those that do participate backed up ONLY on the primary server. I've looked at the sys.fn_hadr_backup_is_preferred_replica funcation, but would like to only have to test for a single databases existance in the availability group. If the one database is in the group, only backup the system databases and those that don't participate, otherwise backup everydatabase. This would be the case for both full backups and transaction logs.
