Cluster DR

My production cluster disks are corruptd - i would like to find out how to
restore from backups
I have all system backups and mdf and ldf files of the databases - I would
like to keep the logins, jobs etc
Thanks
Restoring a cluster is almost exactly the same as restoring a stand-alone
SQL Server. The only difference is that when you restire the master
database, the disks have to be online using the cluster tool, but leave the
SQL Server service offline. You then start and stop it in single-user mode
using the command-line. See BOL for details on how to restore a SQL Server.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"John Smith" <bukusu@.gmail.com> wrote in message
news:el5gBC7GGHA.532@.TK2MSFTNGP15.phx.gbl...
> My production cluster disks are corruptd - i would like to find out how to
> restore from backups
> I have all system backups and mdf and ldf files of the databases - I would
> like to keep the logins, jobs etc
>
> Thanks
>

Close connection of SQLExpress!

Hi,

I write a .NET Windows Form that connect to SQLExpress datafile. After updating data, I want to zip the .mdf file and send email. However, I got an exeption that the .mdf file is used by other thread so I cant zip. Even I try to close all connection, I still cant zip.

Is there any way to detach/unlock .mdf file connecting by SQLExpress?

MA.

Hi,

if you are sure you closed down all conenctions, you can use the sp_detach command to detach the database. if you want to close all connection from the server side first you will have to use the ALTER DATABASE command first and change the state of the database to a SINGLe or admin mode.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Unfortunaterly, I dont control the connection but I want to force to close all connection from the client side (using C# code).

MA.

|||

Hi MA,

Jens is exactly correct, you'll need to force the database into single user mode:

ALTER DATABASE pubs
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

This will rollback all transactions and then you can detach the database as you like. I'm not sure what you mean you don't control the connections. If you can not connect to the database, then you can not close it's connections and you will not be able to close the connections.

Mike

Close all open connections

Before I can drop an mdf file form the server, all connections needs to be closed. how can I force to close this connection. The solution explained on this blog don't seems to work in my case http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/9/Default.aspx

I'm using SQL express, with visual studio pro 2005.

Thx for you quick responses

best regards

Luc N

please verify the code I've used

Dim svr As Server

svr = Nothing

svr = New Server(".\SQLEXPRESS")

'attach database

Dim con As New SqlConnection(svr.ConnectionContext.ConnectionString)

Dim com As New SqlCommand("sp_detach_db", con)

Dim d As Database

con.Open()

For Each d In svr.Databases

If Not d.IsSystemObject Then

'com.CommandType = Data.CommandType.StoredProcedure

'com.Parameters.Add(New SqlParameter("@.dbname", d.Name))

'com.ExecuteNonQuery()

'MsgBox(d.UserName.ToString())

d.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted

SqlConnection.ClearAllPools()

d.Drop()

'com.CommandText = "DROP DATABASE " & d.Name.ToString

'com.CommandType = CommandType.Text

'com.ExecuteNonQuery()

'End If

End If

Next

com.Connection.Close()

Why do you think that the information form the blog is not working for you ?

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

I've still got the message 'cannot drop the database because.......................'

I've modified my code , which seems to be working,

please reply your comments on this code

thx Luc

Try

Dim svr As Server

svr = Nothing

svr = New Server(".\SQLEXPRESS")

'attach database

Dim con As New SqlConnection(svr.ConnectionContext.ConnectionString)

Dim d As Database

con.Open()

For Each d In svr.Databases

If Not d.IsSystemObject Then

Exit For

End If

Next

If InStr(d.Name, "exp", CompareMethod.Text) <> 0 Then

svr.KillAllProcesses(d.Name)

d.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted

SqlConnection.ClearAllPools()

d.Drop()

con.Close()

End If

Catch ex As Exception

' MsgBox(ex.Message)

End Try

|||As I pointed out in the comment, they fixed the behaviour in the Service Pack, so KillDatabase should work for you.

ClickOnce path names are too long for SQL Server Express identifiers

I'm posting this in the ClickOnce forums as well...

My application doesn't include the .mdf and .ldf files, rather it creates the database the first time the application is run using a script that is included in the build. I want to create the database in the ClickOnce data directory. The problem I'm having is that SQL Server complains when I execute the CREATE DATABASE command as follows:

CREATE DATABASE [C:\Documents and Settings\xxxxx\Local Settings\Apps\2.0\Data\PQCK6EXN.5KG\AW630RPT.VGO\ifie..tion_014028c05b1d6ec6_0001.0002_0e86966f19503c89\DataiFieldMobile.mdf] ON PRIMARY
( NAME = N'myDatabase', FILENAME = N'C:\Documents and Settings\xxxxxx\Local Settings\Apps\2.0\Data\PQCK6EXN.5KG\AW630RPT.VGO\ifie..tion_014028c05b1d6ec6_0001.0002_0e86966f19503c89\Data\myDatabase.mdf' , SIZE = 3136KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'myDatabase_log', FILENAME = N'C:\Documents and Settings\xxxx\Local Settings\Apps\2.0\Data\PQCK6EXN.5KG\AW630RPT.VGO\ifie..tion_014028c05b1d6ec6_0001.0002_0e86966f19503c89\Data\myDatabase_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
END

Here's the error:

System.Data.SqlClient.SqlException "The identifier that starts with 'C:\Documents and Settings\xxxxxx\Local Settings\Apps\2.0\Data\PQCK6EXN.5KG\AW630RPT.VGO\ifie..tion_014028c05b1d6ec6_0001.00' is too long. Maximum length is 128."

The problem lies in the name of the database. This is restricted to 128 characters only since it is an identifier like table name or index name or column name. So you need to change the database name by not specifying the full path of the MDF file there.|||

Umachandar Jayachandran - MS wrote:

The problem lies in the name of the database. This is restricted to 128 characters only since it is an identifier like table name or index name or column name. So you need to change the database name by not specifying the full path of the MDF file there.

Thanks for the prompt reply... however, I am not a T-SQL expert... how exactly do I change my script? Do I create a variable for the database name and then use it in the CREATE DATABASE command?

|||

I'm using the script generated by SQL Server. The first thing I tried doing was replacing the pathnames with a token. The application replaces the tokens with the ClickOnce data path, etc. Here's the first part of the script... How exactly do I need to modify the script?

USE [master]

GO

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'D:\PROJECTS\DOTNET\TABLETPC\IFIELD\APP\DATA\IFIELDMOBILE.MDF')

BEGIN

CREATE DATABASE [D:\PROJECTS\DOTNET\TABLETPC\IFIELD\APP\DATA\IFIELDMOBILE.MDF] ON PRIMARY

( NAME = N'iFieldMobile', FILENAME = N'D:\Projects\DotNet\TabletPC\Release\iField 1.2.0.20\App\Data\iFieldMobile.mdf' , SIZE = 2112KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N'iFieldMobile_log', FILENAME = N'D:\Projects\DotNet\TabletPC\Release\iField 1.2.0.20\App\Data\iFieldMobile_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

END

GO

EXEC dbo.sp_dbcmptlevel @.dbname=N'D:\PROJECTS\DOTNET\TABLETPC\IFIELD\APP\DATA\IFIELDMOBILE.MDF', @.new_cmptlevel=90

GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

begin

EXEC [D:\PROJECTS\DOTNET\TABLETPC\IFIELD\APP\DATA\IFIELDMOBILE.MDF].[dbo].[sp_fulltext_database] @.action = 'enable'

end

GO

ALTER DATABASE [D:\PROJECTS\DOTNET\TABLETPC\IFIELD\APP\DATA\IFIELDMOBILE.MDF] SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE [D:\PROJECTS\DOTNET\TABLETPC\IFIELD\APP\DATA\IFIELDMOBILE.MDF] SET ANSI_NULLS OFF

GO

ALTER DATABASE [D:\PROJECTS\DOTNET\TABLETPC\IFIELD\APP\DATA\IFIELDMOBILE.MDF] SET ANSI_PADDING OFF

GO

ALTER DATABASE [D:\PROJECTS\DOTNET\TABLETPC\IFIELD\APP\DATA\IFIELDMOBILE.MDF] SET ANSI_WARNINGS OFF

GO

|||The issue is that the MDF file name cannot exceed 128 characters if you are using it as database name also. So you have to either specify a different name for the database or specify a shorter MDF file name. I am not sure what generates this script. Maybe you will have to post this question in the SQL Server Express forum here.

Clear/Purge Log files

The MDF file is 800 MB but the LDF file is 5 GB. I run the "shrink database"
but this does not seems to reduce the mdf or ldf filesize.
What I did is detach the database, delete the ldf file, re-attach the
database to create a new ldf file. If I do not do so, the application cannot
work (hang!) because the ldf file is too huge and it takes ages to commit a
transaction. Is there a "better" way to control the ldf file like
auto-purging ? Should I restrict the log file size to a specific filesize
like 500MB ? Does this mean it will auto-purge each time it reach 500MB for
the ldf file ?
Thanks
The way you manage your log file is driven by your database recovery plan.
If your recovery plan is to restore from your last full backup and not apply
transaction log backups, then change your database recovery model to SIMPLE.
This will keep your log size reasonable by removing committed transactions
from the log. The log will still need to be large enough to accommodate
your largest single transaction. If you want to reduce potential data loss,
you should use the BULK_LOGGED or FULL recovery model and backup your log
periodically.
The proper way to shrink files is with DBCC SHRINKFILE. See the Books
Online for details. You should not need to do this as part of routine
maintenance.
Hope this helps.
Dan Guzman
SQL Server MVP
"Carlos" <wt_know@.hotmail.com> wrote in message
news:OueiitTPEHA.620@.TK2MSFTNGP10.phx.gbl...
> The MDF file is 800 MB but the LDF file is 5 GB. I run the "shrink
database"
> but this does not seems to reduce the mdf or ldf filesize.
> What I did is detach the database, delete the ldf file, re-attach the
> database to create a new ldf file. If I do not do so, the application
cannot
> work (hang!) because the ldf file is too huge and it takes ages to commit
a
> transaction. Is there a "better" way to control the ldf file like
> auto-purging ? Should I restrict the log file size to a specific filesize
> like 500MB ? Does this mean it will auto-purge each time it reach 500MB
for
> the ldf file ?
> Thanks
>
>
|||Hi,
Instead of detaching , delete the LDF file, Attach the database ytou should
have tried the below steps:-
Alter database <dbname> set single_user with rollback immediate
go
backup log <dbname> to disk='d:\backup\dbname.trn1'
go
dbcc shrinkfile('logical_log_name',truncateonly)
go
Alter database <dbname> set multi_user
After executing the above you can execute the below command check the log
file size and usage,
dbcc sqlperf(logspace)
Like Dan suggested go for SIMPLE recovery model if your data is not critical
or you not require a transaction log based recovery (POINT_IN_TIME).
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:uTGdG0TPEHA.2256@.TK2MSFTNGP10.phx.gbl...
> The way you manage your log file is driven by your database recovery plan.
> If your recovery plan is to restore from your last full backup and not
apply
> transaction log backups, then change your database recovery model to
SIMPLE.
> This will keep your log size reasonable by removing committed transactions
> from the log. The log will still need to be large enough to accommodate
> your largest single transaction. If you want to reduce potential data
loss,[vbcol=seagreen]
> you should use the BULK_LOGGED or FULL recovery model and backup your log
> periodically.
> The proper way to shrink files is with DBCC SHRINKFILE. See the Books
> Online for details. You should not need to do this as part of routine
> maintenance.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Carlos" <wt_know@.hotmail.com> wrote in message
> news:OueiitTPEHA.620@.TK2MSFTNGP10.phx.gbl...
> database"
> cannot
commit[vbcol=seagreen]
> a
filesize
> for
>
|||Thanks for the advices ! :-)
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23MWo0lUPEHA.3328@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Instead of detaching , delete the LDF file, Attach the database ytou
should
> have tried the below steps:-
> Alter database <dbname> set single_user with rollback immediate
> go
> backup log <dbname> to disk='d:\backup\dbname.trn1'
> go
> dbcc shrinkfile('logical_log_name',truncateonly)
> go
> Alter database <dbname> set multi_user
> After executing the above you can execute the below command check the log
> file size and usage,
> dbcc sqlperf(logspace)
> Like Dan suggested go for SIMPLE recovery model if your data is not
critical[vbcol=seagreen]
> or you not require a transaction log based recovery (POINT_IN_TIME).
> Thanks
> Hari
> MCDBA
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:uTGdG0TPEHA.2256@.TK2MSFTNGP10.phx.gbl...
plan.[vbcol=seagreen]
> apply
> SIMPLE.
transactions[vbcol=seagreen]
> loss,
log[vbcol=seagreen]
> commit
> filesize
500MB
>