How to bring a restored database from the "restoring" state back to operational

If you are trying to find a solution to bring your database out of the "restoring" state after a restore, this is the right place you should be.

When you use restore statement the with the "with norecovery" option, after the restore, the database will be put into a 'restoring' state. To bring it back online and operational, you need the follow script:

restore database db_name with recovery

To avoid yourself from this situation, always run the restore with the "with recovery" option.

More info about recovery state after a restore operation can be found at the "Restore Database (Options Page)" at MSDN.

Script to monitor backup and restore progress

I have been recently busy with tuning the restore performance on a testing server. The original disk array was configure to be RAID5 and the restore process was super slow which cost 16 hours to restore a 300 GB database backed up from prod. I've also double-checked CPU and memory stats, none is believed to be the bottleneck. So we decided to downgrade RAID5 to RAID0 since our testing environment doesn't require fault tolerance.

Right now, I am testing the restore again to see if the RAID0 can actually bring the expected performance benefits. However, the restore script I am using was accidentally set to have the argument with "stats = 100" (should've used stats = 1) which will not report progress info until the full restore is finished. So the questions becomes how to monitor a restore process when it is running in the background.

Luckily enough, there are at least two scripts that can be found through Google to monitor the progress of a backup/restore process. The first one is hidden in a thread in SQLServerCentral's forum and the other one is here.

Needless to say, I would like to post them in this post as well.

The first script can only monitor "restore" progress:

SELECT
A.name,
B.total_elapsed_time/60000 AS [Running Time in minutes],
B.estimated_completion_time/60000 AS [Remaining in minutes],
B.percent_complete as [% of completion],
(SELECT TEXT FROM sys.dm_exec_sql_text(B.sql_handle))AS Command
FROM
MASTER.sys.sysdatabases A inner join sys.dm_exec_requests B
on A.dbid = B.database_id
WHERE
B.command LIKE '%RESTORE%'
order by B.percent_complete desc,B.total_elapsed_time/60000 desc

The second script give more general info about both "restore" and "backup".

SELECT command,

s.text,

start_time,

percent_complete,

CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '

+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '

+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,

CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '

+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '

+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,

dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

How to rename a database?

sp_renamedb is the stored proc that can be used. The problem you might encountered is the exclusive lock might not be assigned properly. The best practice is to set the db to single user mode before changing its name and back to multi-user mode afterwards.

ALTER DATABASE old_db_name
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
SP_RENAMEDB old_db_name, new_db_name
Go
ALTER DATABASE new_db_name
SET MULTI_USER
GO

How to drop all the foreign key constraints in a database

Sometimes, foreign keys become annoying in a secondary database where reporting is the main purpose while integrity is not. So dropping all the foreign keys may become a user request from time to time.

The keyword to this question is the catalog view "sys.foreignkeys". And you can go to here to read about about how to retrieve all the foreign key constraints from a database and why catalog view is preferred.

Here is the script to delete all the foreign keys from a database.

use db_name
go

declare @fk_name varchar(100)
declare @tb_name varchar(100)
declare @cmd varchar(200)

declare cur_fk cursor
for
select name as ForeignKey_Name, OBJECT_NAME(parent_object_id) as Table_Name from sys.foreign_keys
order by Table_Name


open cur_fk

fetch next from cur_fk into @fk_name, @tb_name
while @@FETCH_STATUS = 0
begin
set @cmd = 'alter table ' + @tb_name + ' drop constraint ' + @fk_name
select @cmd
exec (@cmd)
fetch next from cur_fk into @fk_name, @tb_name
end

close cur_fk
deallocate cur_fk

How to drop all the user stored procedures from a database

A very informative script is here. But since the original posted script has very annoying line numbers, I would rather re-post the same code here again. And now you can copy and paste it to SSMS very easily. Also something worth mentioning is that the INFORMATION_SCHEMA.ROUTINES view actually contains user defined functions' info, so the SQL command "Drop procedure" will fail if you have any function defined in your target database. Therefore, I changed the following script to use sys.sysobjects view to retrieve the full list of names of user stored procedures.


-- this sets up the test database

-- Drop the database if it already existt

USE master
GO

IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'testdb'
)

DROP DATABASE testdb
GO

CREATE DATABASE testdb
GO

USE testdb
GO

CREATE PROC UserStoredProcedure_Sample1
AS
SELECT 'SQL Server rocks'
GO

CREATE PROC UserStoredProcedure_Sample2
AS
SELECT 'SQL Server rocks'
GO

SET NOCOUNT ON
-- to do this we have to use EXEC instead of sp_executesql

-- sp_executesql does not accept a DROP command in the SQL String

DECLARE @UserStoredProcedure VARCHAR(100)
DECLARE @Command VARCHAR(100)


DECLARE UserStoredProcedureCursor CURSOR SCROLL STATIC READ_ONLY FOR
select name from sys.sysobjects where xtype = 'P'

/*
SELECT
SPECIFIC_NAME
FROM
INFORMATION_SCHEMA.ROUTINES
*/


OPEN UserStoredProcedureCursor


FETCH NEXT FROM UserStoredProcedureCursor
INTO @UserStoredProcedure
WHILE (@@FETCH_STATUS = 0) BEGIN
SET @Command = 'DROP PROCEDURE ' + @UserStoredProcedure
-- display; visual check

SELECT @Command
-- when you are ready to execute, uncomment below

EXEC (@Command)

FETCH NEXT FROM UserStoredProcedureCursor

INTO @UserStoredProcedure

END

CLOSE UserStoredProcedureCursor

DEALLOCATE UserStoredProcedureCursor

SET NOCOUNT OFF

Job notification fails to send emails

This may be another rookie mistake, but I believe it is worth mentioning it here. I have two servers under my administration and one uses maintenance plan to send out emails after a backup operation is done and on the other one I tried to use job notification to send out email to my account when a "move database copies" PowerShell script job is complete. Both servers' email profiles have been set up properly and test emails have been sent successfully too. However, the one with the maintenance plan does send emails successfully every night while the other one with job notification kept silent for quite a few days in a row and I was suspecting the connectivity of the smtp server it uses. But after some research, the reason actually turned out to be a missed configuration check box on the "Alert System" tab of SQL Server Agent Properties. Strangely, on the first server where the maintenance plan is located and used to send out notification emails, this check box is left empty too. So the conclusion is that maintenance plan, which in essence is an SSIS package, does not require this "Enable mail profile" option to be turned on but email-sending jobs do. Also don't forget to restart SQL Server Agent service after the setting is changed.

How to determine whether a file exists or not in PowerShell script?

This is purely for my personal note, cmdlet "test-path" is the keyword:

if (Test-Path "file_lccation")
{
write-host "the file is there"
}
else
{
Write-Host "the file is missing"
}

PowerShell script to script all tables and stored procedures in a database

The script to script all the tables is available here.

Here I made my part of scripting all the stored procedures (excluding system stored procedures) from the ubiquitous database "pubs".

#the full path of the file that you want to script the stored procs to
$scriptfile = "C:\temp\StoredProcs.sql"

#if the script file already exists, remove it
if (Test-Path $scriptfile)
{
Remove-Item -Path $scriptfile
}

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$MyScripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter")
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "localhost"
$procs = $srv.databases["pubs"].StoredProcedures
$MyScripter.Server=$srv

$MyScripter.Options.FileName = $scriptfile
#AppendTofile has to be 'true' in order that all the procs' scripts will be appended at the end
$MyScripter.Options.AppendToFile = "true"

foreach ($proc in $procs)
{
#Assuming that all non-system stored procs have proper naming convention and don't use prefixes like "sp_" or "xp_"
if ( $proc.Name.IndexOf("sp_") -eq -1 -and $proc.Name.IndexOf("xp_") -eq -1 )
{
$MyScripter.Script($proc)|out-null
}
}

SQL Server connection string format for PowerShell scripting

This might look very primitive, but since I am a newbie to PowerShell scripting, it does no harm to write them down here and reference them in a later time when needed.

For sql server authentication:
"Server=server_name or server_ip;Database=db_name;Integrated Security=False;UID=login_name;Password=password"

For windows authentication:
"Server=server_name or server_ip;Database=db_name;Integrated Security=True"

Another website which is dedicated to connection strings might also come in handy, the link is here.

But shockingly, I can't find the above PowerShell script connection strings on the website.

Where to find the staring time of a sql server database restore?

I just encountered this problem today. And composed a post on SCC:

Hello everyone

This might sound very silly due to my novice to DB administration work. Yesterday I started restoring a database with a size of 350GB from a 75GB backup on a testing server from 11pm, and this afternoon at 3pm, when I remote-desktoped the server I found the restore has already finished. I then checked the sql server log in the log file viewer but can't see anything has been logged around 11pm yesterday and there are only two records around 6am early this morning saying that "Starting up database db_name" and "The database is marked RESTORING and is in a state that does not allow recovery to run". Also, there is another log around 2:20pm today stating that "Restore is complete on database db_name. The database is now available".

So my question is whether this restore started around 11pm yesterday when I starting running the restore script or it actually happened around 6am this morning? How to determine the starting time of a restore then? Or did I totally miss something really important here? Thanks very much in advance for your advice!

regards,
Ning

Feed-backs came surprisingly quick that the msdb.dbo.restorehistory is the table I need to look at to retrieve the starting time info for all the restore actions against all the databases in the instance. But it still seems strange to me that SQL Server log does not record the staring time of a restore action.