[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SMO Attach Database without Log

Posted on 2007-07-24
11
Medium Priority
?
1,464 Views
Last Modified: 2010-08-05
Hello,
I am trying to attach and detach a database for SQL 2000 inside of my program without a log file and I am having a few problems with it. Any help would be greatly appreciated.

1) A few notes I only want to use SMO, I do not want to use SQLDMO on the client computers so the code snippet "SQLDMO Attach-Detach" is out.
 2) I am using SQL 2000.
3) I want to do this only by C# or VB.Net.

SQLDMO Attach-Detach
' Connect to the MSDE/SQL Server
Dim dmoServer As New SQLDMO.SQLServer
dmoServer.Connect(serverName, logIn, password)

' Attach the Database
Dim strResult As String = dmoServer.AttachDBWithSingleFile(attachDatabaseAs, fi.FullName)
If (strResult IsNot Nothing) Then
dmStop(strResult, "SQL Attach Error for " + attachDatabaseAs)
Return False
End If

I am attaching the database with only the database (.MDF) file. For some reason it grabs another databases LDF (Log File) and makes that the log file. (Not sure why? Any answers to that?)

Attach Database SQL SMO
Dim svc As New Microsoft.SqlServer.Management.Common.ServerConnection
With svc
.ApplicationName = Application.ProductName
.ServerInstance = serverName
.StatementTimeout = 30
.LoginSecure = False
.Login = userName
.Password = password
End With

Dim server As New SqlServerSmo.Server(svc)
sc.Add(fi.FullName)
server.AttachDatabase(attachDatabaseAs, sc)

I then tried to delete the logfile and create a new log file. I could not get that to work. It would have two log files with the database.

Delete Log File Create New Log File
If server.Databases(attachDatabaseAs).LogFiles.Count > 0 Then _
server.Databases(attachDatabaseAs).LogFiles.Item(0).MarkForDrop(True)
server.Alter()
Dim logFile As New Microsoft.SqlServer.Management.Smo.LogFile( _
server.Databases(attachDatabaseAs), String.Format("{0}_Log", attachDatabaseAs), _
String.Format("{0}\{1}_Log.LDF", strDirectory, attachDatabaseAs))

logFile.GrowthType = SqlServerSmo.FileGrowthType.Percent
logFile.Growth = 10
logFile.Create()

Pretty much I am looking for the solution I had with SQLDMO but with the SMO instead. (dmoServer.AttachDBWithSingleFile). Does anyone know of any way to do this?

If there is no way to do it through SQL SMO is there anyway to run a script without connecting to a database, just the server?

Again thank you in advance for any help I receive.

If there is anything I did not explain enough please let me know.

I have also posted this question on the MSDN forms.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1786146&SiteID=1
You can take a better look at the code there.

Thank you.
0
Comment
Question by:ndonhauser
  • 7
  • 4
11 Comments
 
LVL 14

Expert Comment

by:twoboats
ID: 19555663
This is how you would normally go about it

1) Detach database and move your mdf to save location.
2) Create new databse of same name, same files, same file location and same file size.
3) Stop SQL server.
4) Swap mdf file of just created DB to your saved one.
5) Start SQL. DB will go suspect.
6) ALTER DATABSE <your db> SET EMERGENCY
ALTER DATABASE <your db> SET SINGLE_USER
7) DBCC CHECKDB (<your db>, REPAIR_ALLOW_DATA_LOSS)
8) ALTER DATABASE <your db> SET MULTI_USER
ALTER DATABSE <your db> SET ONLINE
0
 
LVL 1

Author Comment

by:ndonhauser
ID: 19556427
Thank you for the comment. I am looking for a way to do it by C# or VB.Net coding only. I normally would not have access to this computer and it needs to be done through my program.

Thank you.
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19556913
You could try the

sp_attach_single_file_db

stored proc
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 14

Accepted Solution

by:
twoboats earned 2000 total points
ID: 19556924
sp_attach_single_file_db
Attaches a database having only one data file to the current server.

Syntax
sp_attach_single_file_db [ @dbname = ] 'dbname'
    , [ @physname = ] 'physical_name'

Arguments
[@dbname =] 'dbname'

Is the name of the database to be attached to the server. dbname is sysname, with a default of NULL.

[@physname =] 'phsyical_name'

Is the physical name, including path, of the database file. physical_name is nvarchar(260), with a default of NULL.

Return Code Values
0 (success) or 1 (failure)

Result Sets
None

Remarks
When sp_attach_single_file_db attaches the database to the server, it builds a new log file and performs additional cleanup work to remove replication from the newly attached database.

Used sp_attach_single_file_db only on databases that were previously detached from the server using an explicit sp_detach_db operation.

Examples
This example detaches pubs and then attaches one file from pubs to the current server.

EXEC sp_detach_db @dbname = 'pubs'
EXEC sp_attach_single_file_db @dbname = 'pubs',
   @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

0
 
LVL 14

Expert Comment

by:twoboats
ID: 19556927
But this will only work if the mdf has been detached cleanly
0
 
LVL 1

Author Comment

by:ndonhauser
ID: 19557742
Thank you again for the comments. This will also not work because there are a lot of cases where the database is not detached correctly.

Thank you for your help. Is there anything else that you can think of?
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19558231
If it hasn't been detached cleanly, then afaik, the first post is the only way (although that syntax is for 2005 - it's the same process for 2000, though slightly different syntax)

If db's are not being detached cleanly, you are aware that you may lose data?

0
 
LVL 1

Author Comment

by:ndonhauser
ID: 19573956
Yes. I do realize that I may loose data if its not clearly detached. Most of the time its when people are cleaning up a computer with our program on it and they do not realize that our program is on the computer and they will just take a backup of the hard drive and then they want to attach the database still.

Thank you for the help. I will look into doing that process, when I know of the results and it works I will mark you as accepted.

Thank you very much!
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19575216
You're welcome
0
 
LVL 1

Author Comment

by:ndonhauser
ID: 19592322
Thank you again for your help. I did not use your solution. I decided not to support anyone just giving me the mdf file anymore for attaching the database. Again thank you for your help. I will mark your answer as accepted.

0
 
LVL 14

Expert Comment

by:twoboats
ID: 19592399
No problem - happy to help
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question