Friday, March 29, 2013

[MS SQL Server] Restore Master Database

[MS SQL Server] Restore Master Database

Restore Master Database

Posted: 29 Mar 2013 05:00 AM PDT

I'm testing what to do in scenario where the Master database is corrupted beyond the ability for SQL Service to launch or the Master DB drive is lost. In this scenario we do have system database backups but you need to have the instance running to restore backups, and to do that, you need Master db that allows the instance to start.The SQL version is 2008 R2 SP2. I stopped the service and renamed master.dbf to master.old. I tried starting SQL service and got an error as expected.Based on my reading I did the following:1) Open a command line window and go to C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release2) Run this command:setup /ACTION=REBUILDDATABASE /INSTANCENAME=<instance name> /SQLSYSADMINACCOUNTS=<account> /SAPWD=<sa password.This ends with an error, with the following from summary.txt: Exception summary:The following is an exception stack listing the exceptions in outermost to innermost order Inner exceptions are being indentedException type: Microsoft.SqlServer.Setup.Chainer.Workflow.NoopWorkflowException Message: The state of your SQL Server installation was not changed after the setup execution. Please review the summary.txt logs for further details.I found one thread where someone thought this was dues tot he instance initially installed as EVAL version sunsequently being upgraded to full liscense. This is also true of the instance I am using. What else might it be?

Call powershell in SQL agent job

Posted: 29 Mar 2013 03:04 AM PDT

I use SQL server agent job to call a powershell script. I am using Type operating system(Cmdexec)In the job command window, I type:powershell.exe "D:\PowershellScripts\Write-VolToDb.ps1 'MySQLserver\v2012' dba"OR powershell.exe "& D:\PowershellScripts\Write-VolToDb.ps1 MySQLserver\v2012 dba"It is not working. The scipt self is OK, but I just cannot figure out what is the correct syntax to call it.The 'MySQLserver\v2012' is my server name\instanceName, dba is the database name. They are two parameter of the powershell.What is correct syntax of it, Thanks--------------------------------------------------------------------------------

3-Node, 3-Instance SQL Cluster -- Static IP address and Port 1433 question

Posted: 28 Mar 2013 06:48 AM PDT

I searched hard and many people have a similar question, but I cannot find a clear answer. I have a 3-node Windows Server 2008 R2 cluster, with 3 Named Instances of SQL Server 2012 w/SP1. All 64-bit. I only used static IP addresses for everything, including the SQL Instances. I thought the 3 SQL Instances would use Port 1433 because there are 3 separate named instances, with 3 separate static IP addresses. But no: each Instance has a dynamically assigned port, and therefore won't work because we have firewalls. Shouldn't each Instance just be listening on Port 1433? Can I make each Instance listen on Port 1433?Here are all the details for one of the Instances:In Cluster Manager, under Services and Applications, I click on InstanceA, then underneath the Server Name there is the static IP address I assigned when installing this Instance. Good so far.Under SQL Server Configuration Manager, under SQL Native Client 11.0 Configuration (32-bit), under Client Protocols, under TCP/IP the Default Port is 1433. Also good.Under SQL Server Network Configuration I see my 3 Instances. I click on Protocols for InstanceA, then under TCP/IP there are a lot of IP addresses. Under the IP address that corresponds to the one under Cluster Manager, TCP Dynamic Ports = 0, and under TCP Port it is blank. Uh oh! Also, all the way at the bottom under IPAll it says TCP Dynamic Ports: 61024, and TCP Port again is blank. This is where I thought I would just see Port 1433.Now in SQL Server Management Studio, under SQL Server Logs, there is an entry (I hid the IP address, but it is the same as in Cluster Manager): [b]Server is listening on [ 000.000.000.000 <ipv4> 61024].[/b]So there is that same non-standard, dynamically-assigned port.So again, why isn't this Instance using Port 1433? Shouln't it be? How can I make all 3 Instances listen on Port 1433?Thanks in advance!BTW, I read all of these, none seems helpful:[url=http://support.microsoft.com/kb/823938]http://support.microsoft.com/kb/823938[/url][url=http://support.microsoft.com/kb/318432]http://support.microsoft.com/kb/318432[/url][url=http://blogs.msdn.com/b/sqlblog/archive/2009/07/17/how-to-configure-sql-server-to-listen-on-different-ports-on-different-ip-addresses.aspx]http://blogs.msdn.com/b/sqlblog/archive/2009/07/17/how-to-configure-sql-server-to-listen-on-different-ports-on-different-ip-addresses.aspx[/url]
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog