interface supported: dbo

Showing posts with label dbo. Show all posts

Friday, March 30, 2012

Northwind database in SQL Server Express is busted and gone,after SqlCommand fails in VB2005Expr

Hi all,

I tried to use dbo.tables of Northwind database in SQL Server Express to do SqlCommand to populate a DataTable in DataSet. The SqlCommand failed in the project execution and I found that Northwind database in SQL Server Express is busted and gone (just the name "Northwind" remained in the SQL Server Management Studio Express). How can I prevent it from happening in my "SqlCommand-SqlConnection" project? Please help and advise.

I tried to repair my "Northwind" database by using the SQL2000SampleDb.msi of Northwind and pubs Sample Databases for SQL Server 2000 downloaded from the http://www.microsoft.com/downloads. My "pubs" database is still in my SQL Server Management Studio Express. How can I just repair my "Northwind" database by using the Microsoft SQL2000SampleDb.msi program? Please help and advise this matter too.

Thanks in advance,

Scott Chang

You should try to Drop or Delete the Northwind database and then recreate it from script or attach a fresh copy of the mdf file that you've download.

As to how to prevent the initial problem, you'd have to tell us what you did in the first place before anyone could offer a suggestion as to what might have gone wrong.

Mike

|||

Hi Mike, Thanks for your response.

(1). I deleted the name of "Northwind" in the SQL Server Express. Then I tried to recreate it from script by executing the following two things at the command prompt:

cd C:\SQL Server 2000 Sample Databases

sqlcmd -S .\SQLExpress -i instnwnd.sql

I got the following errors:

Changed database context to 'master'.
Msg 1802, Level 16, State 4, Server NAB-WK-EN\SQLEXPRESS, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Msg 5170, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 1
Cannot create file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\northwnd.ldf' because it already exists. Change the file path or the file name, and retry the operation.
Msg 15010, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Procedure sp_dboption, Line 64
The database 'Northwind' does not exist. Use sp_helpdb to show available databases.
Available databases:
--
master
tempdb
model
msdb
ChemDatabase
ssmsExpressDB
newDB
pubs
Halloween
AdventureWorks
AdventureWorksDW
CamReynoldGISKeyR1
shcDB
C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.1\MSSQL\DATA\NORTHWND.MDF
Msg 15010, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Procedure sp_dboption, Line 64
The database 'Northwind' does not exist. Use sp_helpdb to show available databases.
Available databases:
--
master
tempdb
model
msdb
ChemDatabase
ssmsExpressDB
newDB
pubs
Halloween
AdventureWorks
AdventureWorksDW
CamReynoldGISKeyR1
shcDB
C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.1\MSSQL\DATA\NORTHWND.MDF
Msg 911, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 1
Could not locate entry in sysdatabases for database 'Northwind'. No entry found with that name. Make sure that the name is entered correctly.
Msg 3726, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 2
Could not drop object 'dbo.Customers' because it is referenced by a FOREIGN KEY constraint.
Msg 3726, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 2
Could not drop object 'dbo.Employees' because it is referenced by a FOREIGN KEY constraint.
Msg 2714, Level 16, State 6, Server NAB-WK-EN\SQLEXPRESS, Line 1
There is already an object named 'Employees' in the database.
Msg 1913, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 1
The operation failed because an index or statistics with name 'LastName' already exists on table 'dbo.Employees'.
Msg 1913, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 1
The operation failed because an index or statistics with name 'PostalCode' already exists on table 'dbo.Employees'.
Msg 2714, Level 16, State 6, Server NAB-WK-EN\SQLEXPRESS, Line 2
There is already an object named 'Customers' in the database.
Msg 1913, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 1

..........................................................................................................

Msg 1779, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 2
Table 'Territories' already has a primary key defined on it.
Msg 1750, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 2
Could not create constraint. See previous errors.
Msg 2714, Level 16, State 4, Server NAB-WK-EN\SQLEXPRESS, Line 2
There is already an object named 'FK_Territories_Region' in the database.
Msg 1750, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 2
Could not create constraint. See previous errors.
Msg 1779, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 2
Table 'EmployeeTerritories' already has a primary key defined on it.
Msg 1750, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 2
Could not create constraint. See previous errors.
Msg 2714, Level 16, State 4, Server NAB-WK-EN\SQLEXPRESS, Line 2
There is already an object named 'FK_EmployeeTerritories_Employees' in the database.
Msg 1750, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 2
Could not create constraint. See previous errors.
Msg 2714, Level 16, State 4, Server NAB-WK-EN\SQLEXPRESS, Line 3
There is already an object named 'FK_EmployeeTerritories_Territories' in the database.
Msg 1750, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 3
Could not create constraint. See previous errors.

////////////////////////////////////////////////////////////////////////////////////////////////

I think I did not delete the busted "Northwind" database in SQLExpress completely. I do not know how to attach a fresh copy of the mdf file that I have downloaded either. Please help and advise me how to resolve this problem.

(2). I feel that my "Northwind" database in SQL Server Express was busted, when I tried to run the following code statements (of my SqlBulkCopy project):

using System;

using System.Data;

using System.Data.SqlClient;

namespace SqlBulkCopySample

.........................................

private void btnStart_Click(object sender, EventArgs e)

{

String sourceConnectionString=

"Data Source=.SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True";

String destinationConnectionString=

"Data Source=.SQLEXPRESS;Initial Catalog=SqlBulkCopySample;Integrated Security=True";

DataTable data=SelectDataFromSource(sourceConnectionString);

CopyDataToDestination(destinationConnectionString, data);

}

private DataTable SelectDataFromSource(String connectionString)

{

DataTable data=new DataTable();

using (SqlConnection = new SqlConnection(connectionString))

{

SqlCommand command = new SqlCommand("SelectOrders", connection);

command.CommandType = CommandType.StoredProcedure;

connection.Open();

SqlDataReader reader = command.ExecuteReader();

data Load(reader);

}

return data;

}

................................

...............................

/////////////////////////////////////////////////////////////////////////////////

I did not know how to create the stored procedure 'SelectOrders' in SQL Server Express yet. When I ran the SqlBulkCopySample, I got the following error:

SqlException was unhandled

Could not find stored procedure 'SelectOrders'

that were pointing to the secend staement of the following code statements:

connection.Open()

SqlDataReader reader = command.ExecuteReader();

Mike, please help again and tell me how I can prevent the "Northwind" database from being busted, if there is a mistake or wrong logic in the code statements of my ADO.NET 2.0-Visual C# 2005 Express (or VB 2005 Express) programming.

Thanks in advance,

Scott Chang

|||

OK, from the list of available database it seems that you already have the Northwind database copied to your SQL directory and attached it as a user instance. In order to recreate it from scratch, you will have to deatch the database from the user instance (by closing the application which is using it), drop the files of the Northwind database in the mentioned directory and then afterwards create the database from scratch using the script.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens, Thanks for your response and instructions.

I have a hard time to follow your instructions because I had deleted the name "Northwind" in my SQLEXPRESS already and I have the files of the Northwind database in my C:\SQL Server 2000 Sample Databases folder. I tried the two above-mentioned commands at the command prompt again and I got the same error output. Could you please help me again and give me more clearer instructions for re-installing the Northwind database?

Thanks again,

Scott Chang

|||Scott,

Kindly check whether the physical files(mdf & ldf of northwind) for Northwind db is available at "C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.1\MSSQL\DATA" path. If yes try attaching the db in SQL Express. If this option fails, then delete those files and create the db as mentioned by jens.

|||

Hi,

restart the service to make sure that no user instances are hanging around. Then see the sys.database_files to investigate if there are any database using the physical files. If not, try to attach them using a TSQL command or the SSMS Gui. if you have any further questions please come back.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens and Vidhya, Thanks for your responses and instructions.

(i) I deleted the Northwind.mdf and Northwind_log.LDF files in my C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder. Then I executed the 2 commands to re-install the Northwind database. It re-installed successfully.

(ii) This morning, I ran the following project:

///////////////-Form1.vb-///////////////////

Imports System

Imports System.ComponentModel

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports System.Text

Imports System.Collections.Generic

Imports System.Drawing

Imports System.Windows.Forms

Public Class Form1

Dim bcp As SqlBulkCopy

Dim strConn As String = "Data Source=.\SQLExpress;" & _

"Initial Catalog=Northwind;Integrated Security=True"

Dim cn As New SqlConnection(strConn)

Dim options As SqlBulkCopyOptions = SqlBulkCopyOptions.Default

Dim txn As SqlTransaction = Nothing

'Supply a SqlConnction object, a value from SqlBulkCopyOptions

'and a SqlTransction object

bcp = New SqlBulkCopy(cn, options, txn)

Public Sub New()

' This call is required by the Windows Form Designer.

InitializeComponent()

' Add any initialization after the InitializeComponent() call.

End Sub

End Class

//////////////////////////////////////////////////////////////////////

It did not work and I got an error "Declaration Expected" on "bcp" of

bcp = New SqlBulkCopy(cn, options, txn).

I changed that code statement as a comment {

'bcp = New SqlBulkCopy(cn, options, txn)} and then I ran it again - it ran successfully without any error mesages.

When I checked the Databases of SQL Server Management Studio Express, I found that the name "Northwind" was there only and nothing was attached to the "Northwind" database!!!! When I clicked on "Northwind", I got the following error message:

Microsoft SQL Server Management Studio Express

Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)

Additional information:

One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup. (Microsoft SQL Server, Error: 5173)

Now, my "Northwind" database in SQL Server Management Studio Express is busted again!!! I am very surprised to see this happened again. Could you please tell me why it happens and how to prevent it from happening?

Thanks,

Scott Chang

Northwind database in SQL Server Express is busted and gone,after SqlCommand fails in VB2005Expr

Hi all,

Thanks in advance,

Scott Chang

You should try to Drop or Delete the Northwind database and then recreate it from script or attach a fresh copy of the mdf file that you've download.

As to how to prevent the initial problem, you'd have to tell us what you did in the first place before anyone could offer a suggestion as to what might have gone wrong.

Mike

|||

Hi Mike, Thanks for your response.

(1). I deleted the name of "Northwind" in the SQL Server Express. Then I tried to recreate it from script by executing the following two things at the command prompt:

cd C:\SQL Server 2000 Sample Databases

sqlcmd -S .\SQLExpress -i instnwnd.sql

I got the following errors:

..........................................................................................................

////////////////////////////////////////////////////////////////////////////////////////////////

(2). I feel that my "Northwind" database in SQL Server Express was busted, when I tried to run the following code statements (of my SqlBulkCopy project):

using System;

using System.Data;

using System.Data.SqlClient;

namespace SqlBulkCopySample

.........................................

private void btnStart_Click(object sender, EventArgs e)

{

String sourceConnectionString=

"Data Source=.SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True";

String destinationConnectionString=

"Data Source=.SQLEXPRESS;Initial Catalog=SqlBulkCopySample;Integrated Security=True";

DataTable data=SelectDataFromSource(sourceConnectionString);

CopyDataToDestination(destinationConnectionString, data);

}

private DataTable SelectDataFromSource(String connectionString)

{

DataTable data=new DataTable();

using (SqlConnection = new SqlConnection(connectionString))

{

SqlCommand command = new SqlCommand("SelectOrders", connection);

command.CommandType = CommandType.StoredProcedure;

connection.Open();

SqlDataReader reader = command.ExecuteReader();

data Load(reader);

}

return data;

}

................................

...............................

/////////////////////////////////////////////////////////////////////////////////

I did not know how to create the stored procedure 'SelectOrders' in SQL Server Express yet. When I ran the SqlBulkCopySample, I got the following error:

SqlException was unhandled

Could not find stored procedure 'SelectOrders'

that were pointing to the secend staement of the following code statements:

connection.Open()

SqlDataReader reader = command.ExecuteReader();

Thanks in advance,

Scott Chang

|||

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens, Thanks for your response and instructions.

Thanks again,

Scott Chang

|||Scott,

|||

Hi,

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens and Vidhya, Thanks for your responses and instructions.

(ii) This morning, I ran the following project:

///////////////-Form1.vb-///////////////////

Imports System

Imports System.ComponentModel

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports System.Text

Imports System.Collections.Generic

Imports System.Drawing

Imports System.Windows.Forms

Public Class Form1

Dim bcp As SqlBulkCopy

Dim strConn As String = "Data Source=.\SQLExpress;" & _

"Initial Catalog=Northwind;Integrated Security=True"

Dim cn As New SqlConnection(strConn)

Dim options As SqlBulkCopyOptions = SqlBulkCopyOptions.Default

Dim txn As SqlTransaction = Nothing

'Supply a SqlConnction object, a value from SqlBulkCopyOptions

'and a SqlTransction object

bcp = New SqlBulkCopy(cn, options, txn)

Public Sub New()

' This call is required by the Windows Form Designer.

InitializeComponent()

' Add any initialization after the InitializeComponent() call.

End Sub

End Class

//////////////////////////////////////////////////////////////////////

It did not work and I got an error "Declaration Expected" on "bcp" of

bcp = New SqlBulkCopy(cn, options, txn).

I changed that code statement as a comment {

'bcp = New SqlBulkCopy(cn, options, txn)} and then I ran it again - it ran successfully without any error mesages.

Microsoft SQL Server Management Studio Express

Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)

Additional information:

Thanks,

Scott Chang

sql

Northwind database in SQL Server Express is busted and gone,after SqlCommand fails in VB2005Expr

Hi all,

Thanks in advance,

Scott Chang

You should try to Drop or Delete the Northwind database and then recreate it from script or attach a fresh copy of the mdf file that you've download.

As to how to prevent the initial problem, you'd have to tell us what you did in the first place before anyone could offer a suggestion as to what might have gone wrong.

Mike

|||

Hi Mike, Thanks for your response.

(1). I deleted the name of "Northwind" in the SQL Server Express. Then I tried to recreate it from script by executing the following two things at the command prompt:

cd C:\SQL Server 2000 Sample Databases

sqlcmd -S .\SQLExpress -i instnwnd.sql

I got the following errors:

..........................................................................................................

////////////////////////////////////////////////////////////////////////////////////////////////

(2). I feel that my "Northwind" database in SQL Server Express was busted, when I tried to run the following code statements (of my SqlBulkCopy project):

using System;

using System.Data;

using System.Data.SqlClient;

namespace SqlBulkCopySample

.........................................

private void btnStart_Click(object sender, EventArgs e)

{

String sourceConnectionString=

"Data Source=.SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True";

String destinationConnectionString=

"Data Source=.SQLEXPRESS;Initial Catalog=SqlBulkCopySample;Integrated Security=True";

DataTable data=SelectDataFromSource(sourceConnectionString);

CopyDataToDestination(destinationConnectionString, data);

}

private DataTable SelectDataFromSource(String connectionString)

{

DataTable data=new DataTable();

using (SqlConnection = new SqlConnection(connectionString))

{

SqlCommand command = new SqlCommand("SelectOrders", connection);

command.CommandType = CommandType.StoredProcedure;

connection.Open();

SqlDataReader reader = command.ExecuteReader();

data Load(reader);

}

return data;

}

................................

...............................

/////////////////////////////////////////////////////////////////////////////////

I did not know how to create the stored procedure 'SelectOrders' in SQL Server Express yet. When I ran the SqlBulkCopySample, I got the following error:

SqlException was unhandled

Could not find stored procedure 'SelectOrders'

that were pointing to the secend staement of the following code statements:

connection.Open()

SqlDataReader reader = command.ExecuteReader();

Thanks in advance,

Scott Chang

|||

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens, Thanks for your response and instructions.

Thanks again,

Scott Chang

|||Scott,

|||

Hi,

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens and Vidhya, Thanks for your responses and instructions.

(ii) This morning, I ran the following project:

///////////////-Form1.vb-///////////////////

Imports System

Imports System.ComponentModel

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports System.Text

Imports System.Collections.Generic

Imports System.Drawing

Imports System.Windows.Forms

Public Class Form1

Dim bcp As SqlBulkCopy

Dim strConn As String = "Data Source=.\SQLExpress;" & _

"Initial Catalog=Northwind;Integrated Security=True"

Dim cn As New SqlConnection(strConn)

Dim options As SqlBulkCopyOptions = SqlBulkCopyOptions.Default

Dim txn As SqlTransaction = Nothing

'Supply a SqlConnction object, a value from SqlBulkCopyOptions

'and a SqlTransction object

bcp = New SqlBulkCopy(cn, options, txn)

Public Sub New()

' This call is required by the Windows Form Designer.

InitializeComponent()

' Add any initialization after the InitializeComponent() call.

End Sub

End Class

//////////////////////////////////////////////////////////////////////

It did not work and I got an error "Declaration Expected" on "bcp" of

bcp = New SqlBulkCopy(cn, options, txn).

I changed that code statement as a comment {

'bcp = New SqlBulkCopy(cn, options, txn)} and then I ran it again - it ran successfully without any error mesages.

Microsoft SQL Server Management Studio Express

Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)

Additional information:

Thanks,

Scott Chang

Northwind database in SQL Server Express is busted and gone,after SqlCommand fails in VB2005Expr

Hi all,

Thanks in advance,

Scott Chang

You should try to Drop or Delete the Northwind database and then recreate it from script or attach a fresh copy of the mdf file that you've download.

As to how to prevent the initial problem, you'd have to tell us what you did in the first place before anyone could offer a suggestion as to what might have gone wrong.

Mike

|||

Hi Mike, Thanks for your response.

(1). I deleted the name of "Northwind" in the SQL Server Express. Then I tried to recreate it from script by executing the following two things at the command prompt:

cd C:\SQL Server 2000 Sample Databases

sqlcmd -S .\SQLExpress -i instnwnd.sql

I got the following errors:

..........................................................................................................

////////////////////////////////////////////////////////////////////////////////////////////////

(2). I feel that my "Northwind" database in SQL Server Express was busted, when I tried to run the following code statements (of my SqlBulkCopy project):

using System;

using System.Data;

using System.Data.SqlClient;

namespace SqlBulkCopySample

.........................................

private void btnStart_Click(object sender, EventArgs e)

{

String sourceConnectionString=

"Data Source=.SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True";

String destinationConnectionString=

"Data Source=.SQLEXPRESS;Initial Catalog=SqlBulkCopySample;Integrated Security=True";

DataTable data=SelectDataFromSource(sourceConnectionString);

CopyDataToDestination(destinationConnectionString, data);

}

private DataTable SelectDataFromSource(String connectionString)

{

DataTable data=new DataTable();

using (SqlConnection = new SqlConnection(connectionString))

{

SqlCommand command = new SqlCommand("SelectOrders", connection);

command.CommandType = CommandType.StoredProcedure;

connection.Open();

SqlDataReader reader = command.ExecuteReader();

data Load(reader);

}

return data;

}

................................

...............................

/////////////////////////////////////////////////////////////////////////////////

I did not know how to create the stored procedure 'SelectOrders' in SQL Server Express yet. When I ran the SqlBulkCopySample, I got the following error:

SqlException was unhandled

Could not find stored procedure 'SelectOrders'

that were pointing to the secend staement of the following code statements:

connection.Open()

SqlDataReader reader = command.ExecuteReader();

Thanks in advance,

Scott Chang

|||

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens, Thanks for your response and instructions.

Thanks again,

Scott Chang

|||Scott,

|||

Hi,

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens and Vidhya, Thanks for your responses and instructions.

(ii) This morning, I ran the following project:

///////////////-Form1.vb-///////////////////

Imports System

Imports System.ComponentModel

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports System.Text

Imports System.Collections.Generic

Imports System.Drawing

Imports System.Windows.Forms

Public Class Form1

Dim bcp As SqlBulkCopy

Dim strConn As String = "Data Source=.\SQLExpress;" & _

"Initial Catalog=Northwind;Integrated Security=True"

Dim cn As New SqlConnection(strConn)

Dim options As SqlBulkCopyOptions = SqlBulkCopyOptions.Default

Dim txn As SqlTransaction = Nothing

'Supply a SqlConnction object, a value from SqlBulkCopyOptions

'and a SqlTransction object

bcp = New SqlBulkCopy(cn, options, txn)

Public Sub New()

' This call is required by the Windows Form Designer.

InitializeComponent()

' Add any initialization after the InitializeComponent() call.

End Sub

End Class

//////////////////////////////////////////////////////////////////////

It did not work and I got an error "Declaration Expected" on "bcp" of

bcp = New SqlBulkCopy(cn, options, txn).

I changed that code statement as a comment {

'bcp = New SqlBulkCopy(cn, options, txn)} and then I ran it again - it ran successfully without any error mesages.

Microsoft SQL Server Management Studio Express

Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)

Additional information:

Thanks,

Scott Chang

Northwind database in SQL Server Express is busted and gone,after SqlCommand fails in VB2005Expr

Hi all,

Thanks in advance,

Scott Chang

You should try to Drop or Delete the Northwind database and then recreate it from script or attach a fresh copy of the mdf file that you've download.

As to how to prevent the initial problem, you'd have to tell us what you did in the first place before anyone could offer a suggestion as to what might have gone wrong.

Mike

|||

Hi Mike, Thanks for your response.

(1). I deleted the name of "Northwind" in the SQL Server Express. Then I tried to recreate it from script by executing the following two things at the command prompt:

cd C:\SQL Server 2000 Sample Databases

sqlcmd -S .\SQLExpress -i instnwnd.sql

I got the following errors:

..........................................................................................................

////////////////////////////////////////////////////////////////////////////////////////////////

(2). I feel that my "Northwind" database in SQL Server Express was busted, when I tried to run the following code statements (of my SqlBulkCopy project):

using System;

using System.Data;

using System.Data.SqlClient;

namespace SqlBulkCopySample

.........................................

private void btnStart_Click(object sender, EventArgs e)

{

String sourceConnectionString=

"Data Source=.SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True";

String destinationConnectionString=

"Data Source=.SQLEXPRESS;Initial Catalog=SqlBulkCopySample;Integrated Security=True";

DataTable data=SelectDataFromSource(sourceConnectionString);

CopyDataToDestination(destinationConnectionString, data);

}

private DataTable SelectDataFromSource(String connectionString)

{

DataTable data=new DataTable();

using (SqlConnection = new SqlConnection(connectionString))

{

SqlCommand command = new SqlCommand("SelectOrders", connection);

command.CommandType = CommandType.StoredProcedure;

connection.Open();

SqlDataReader reader = command.ExecuteReader();

data Load(reader);

}

return data;

}

................................

...............................

/////////////////////////////////////////////////////////////////////////////////

I did not know how to create the stored procedure 'SelectOrders' in SQL Server Express yet. When I ran the SqlBulkCopySample, I got the following error:

SqlException was unhandled

Could not find stored procedure 'SelectOrders'

that were pointing to the secend staement of the following code statements:

connection.Open()

SqlDataReader reader = command.ExecuteReader();

Thanks in advance,

Scott Chang

|||

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens, Thanks for your response and instructions.

Thanks again,

Scott Chang

|||Scott,

|||

Hi,

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens and Vidhya, Thanks for your responses and instructions.

(ii) This morning, I ran the following project:

///////////////-Form1.vb-///////////////////

Imports System

Imports System.ComponentModel

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports System.Text

Imports System.Collections.Generic

Imports System.Drawing

Imports System.Windows.Forms

Public Class Form1

Dim bcp As SqlBulkCopy

Dim strConn As String = "Data Source=.\SQLExpress;" & _

"Initial Catalog=Northwind;Integrated Security=True"

Dim cn As New SqlConnection(strConn)

Dim options As SqlBulkCopyOptions = SqlBulkCopyOptions.Default

Dim txn As SqlTransaction = Nothing

'Supply a SqlConnction object, a value from SqlBulkCopyOptions

'and a SqlTransction object

bcp = New SqlBulkCopy(cn, options, txn)

Public Sub New()

' This call is required by the Windows Form Designer.

InitializeComponent()

' Add any initialization after the InitializeComponent() call.

End Sub

End Class

//////////////////////////////////////////////////////////////////////

It did not work and I got an error "Declaration Expected" on "bcp" of

bcp = New SqlBulkCopy(cn, options, txn).

I changed that code statement as a comment {

'bcp = New SqlBulkCopy(cn, options, txn)} and then I ran it again - it ran successfully without any error mesages.

Microsoft SQL Server Management Studio Express

Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)

Additional information:

Thanks,

Scott Chang

Friday, March 23, 2012

Non-SA can't view Activity Monitor with VIEW SERVER STATE perm.

I have a developer who has dbo privileges in master and msdb, and also has
VIEW SERVER STATE and VIEW ANY DATABASE. According to SQL Server 2005
documentation she should be able to view the Activity Monitor, but instead
gets the following error:
TITLE: Microsoft SQL Server Management Studio
--
Cannot show requested dialog.
ADDITIONAL INFORMATION:
Unable to execute requested command.
Unable to launch Activity Monitor. You may not have sufficient permissions.
(ActivityMonitor)
SELECT permission denied on object 'tables', database 'mssqlsystemresource',
schema 'sys'.
SELECT permission denied on object 'partitions', database
'mssqlsystemresource', schema 'sys'.
Changed database context to '<db1>'.
Changed database context to '<db2>'.
Changed database context to '<db3>'.
Changed database context to '<db4>'.
Changed database context to '<db5>'.
Changed database context to '<db6>'.
Changed database context to 'tempdb'. (Microsoft SQL Server, Error: 229)
It popped up last week, and I read that Reporting Services running may have
an effect on it, so I stopped the service and she was able to get in with no
problem. No changes have been made to the server since I stopped the
service, but this error just started again today. No updates or patches hav
e
been installed on the server recently.
I have tried granting permission to 'tables' and 'partition' for master and
mssqlsystemresource db's, and to individual system tables but I run into
errors every time. I'm pretty new to SQL Server 2005 so please explain
everything in very simple terms!
THANKS!
Details: OS is Windows Server 2003 (clustered) and I have SQL Server 2005 SP
1 installed.Augspies (Augspies@.discussions.microsoft.com) writes:
> I have a developer who has dbo privileges in master and msdb, and also has
> VIEW SERVER STATE and VIEW ANY DATABASE. According to SQL Server 2005
> documentation she should be able to view the Activity Monitor, but instead
> gets the following error:
>...
> SELECT permission denied on object 'tables', database
> 'mssqlsystemresource',
> schema 'sys'.
> SELECT permission denied on object 'partitions', database
> 'mssqlsystemresource', schema 'sys'.
That's a tricky one. It would indicate that there are locks on tables
in msqlsystemresource. And mssqlsystemresource is unavailable to
everyone, including sa, but if you're in the sysadmin role, the
check may be bypassed so you don't see the error.
I don't really know what to suggest, but you could try my aba_lockinfo
which gives about the same information as the Actvity Monitor, but
without any GUI. You can get it from
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
Maybe I would be more worried over the locks in mssqlsystemresource.
That does not really sound normal to me...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Saturday, February 25, 2012

No Truncate Option in Log Backup

I need to peform a log backup with no truncate. My regular maintenance
plan runs like this:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
-DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
Where in here do I add the no truncate option?Techhead wrote:
> I need to peform a log backup with no truncate. My regular maintenance
> plan runs like this:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
>
> Where in here do I add the no truncate option?
>
You don't. You'll have to run the BACKUP command directly, not from a
maintenance plan.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular basis? This
option is only there in order for you to do the last log backup on a corrupt database. See:
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:44E33CF4.6030307@.realsqlguy.com...
> Techhead wrote:
>> I need to peform a log backup with no truncate. My regular maintenance
>> plan runs like this:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
>> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
>> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
>> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
>>
>> Where in here do I add the no truncate option?
> You don't. You'll have to run the BACKUP command directly, not from a maintenance plan.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Why would I not want to truncate? Because I have a third party backup
solution doing it for me and it will not work if my maintenance plan
and 3rd party backup truncate at the same time. I still want to do a
full backup in SQL but not truncate... maybe a copy of the tran logs
will work, but no truncate.
Tibor Karaszi wrote:
> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular basis? This
> option is only there in order for you to do the last log backup on a corrupt database. See:
> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:44E33CF4.6030307@.realsqlguy.com...
> > Techhead wrote:
> >> I need to peform a log backup with no truncate. My regular maintenance
> >> plan runs like this:
> >>
> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
> >>
> >>
> >> Where in here do I add the no truncate option?
> >>
> >
> > You don't. You'll have to run the BACKUP command directly, not from a maintenance plan.
What is the backup command to use?
> >
> >
> > --
> > Tracy McKibben
> > MCDBA
> > http://www.realsqlguy.com|||Techhead wrote:
> Why would I not want to truncate? Because I have a third party backup
> solution doing it for me and it will not work if my maintenance plan
> and 3rd party backup truncate at the same time. I still want to do a
> full backup in SQL but not truncate... maybe a copy of the tran logs
> will work, but no truncate.
>
Just curious, have you attempted to RESTORE a complete database,
including these transaction log backups? I don't fully understand why
you have a third-party solution in place, AND you're using the SQL
BACKUP command.
Most folks here will tell you to avoid third-party backup agents for
your databases, and instead use the native BACKUP and RESTORE commands
to dump the database to disk files, and then backup those files to tape.
The third-party tools can be unreliable and difficult to restore from,
particularly a point-in-time restore.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> Why would I not want to truncate? Because I have a third party backup
> solution doing it for me and it will not work if my maintenance plan
> and 3rd party backup truncate at the same time.
I strongly encourage you to read the article I posted a link to. You will see that even with
NO_TRUNCATE, you cannot "skip" restoring one of the log backup "in between". The article was written
for SQL Server 2000, I haven't tried it on 2005 yet.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Techhead" <jorgenson.b@.gmail.com> wrote in message
news:1156284873.311342.152930@.p79g2000cwp.googlegroups.com...
> Why would I not want to truncate? Because I have a third party backup
> solution doing it for me and it will not work if my maintenance plan
> and 3rd party backup truncate at the same time. I still want to do a
> full backup in SQL but not truncate... maybe a copy of the tran logs
> will work, but no truncate.
>
> Tibor Karaszi wrote:
>> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular basis? This
>> option is only there in order for you to do the last log backup on a corrupt database. See:
>> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:44E33CF4.6030307@.realsqlguy.com...
>> > Techhead wrote:
>> >> I need to peform a log backup with no truncate. My regular maintenance
>> >> plan runs like this:
>> >>
>> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
>> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
>> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
>> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
>> >>
>> >>
>> >> Where in here do I add the no truncate option?
>> >>
>> >
>> > You don't. You'll have to run the BACKUP command directly, not from a maintenance plan.
>
> What is the backup command to use?
>> >
>> >
>> > --
>> > Tracy McKibben
>> > MCDBA
>> > http://www.realsqlguy.com
>|||But I don't want to restore using SQL, I want to use my 3rd party
backup solution to do the restore. THis solution can restore SQL DB's
to point-in-time using truncated logs that the software backed up. i
just don't want SQL truncating when my backup software does.
Tibor Karaszi wrote:
> > Why would I not want to truncate? Because I have a third party backup
> > solution doing it for me and it will not work if my maintenance plan
> > and 3rd party backup truncate at the same time.
> I strongly encourage you to read the article I posted a link to. You will see that even with
> NO_TRUNCATE, you cannot "skip" restoring one of the log backup "in between". The article was written
> for SQL Server 2000, I haven't tried it on 2005 yet.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> news:1156284873.311342.152930@.p79g2000cwp.googlegroups.com...
> > Why would I not want to truncate? Because I have a third party backup
> > solution doing it for me and it will not work if my maintenance plan
> > and 3rd party backup truncate at the same time. I still want to do a
> > full backup in SQL but not truncate... maybe a copy of the tran logs
> > will work, but no truncate.
> >
> >
> > Tibor Karaszi wrote:
> >> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular basis? This
> >> option is only there in order for you to do the last log backup on a corrupt database. See:
> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:44E33CF4.6030307@.realsqlguy.com...
> >> > Techhead wrote:
> >> >> I need to peform a log backup with no truncate. My regular maintenance
> >> >> plan runs like this:
> >> >>
> >> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> >> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
> >> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
> >> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
> >> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
> >> >>
> >> >>
> >> >> Where in here do I add the no truncate option?
> >> >>
> >> >
> >> > You don't. You'll have to run the BACKUP command directly, not from a maintenance plan.
> >
> >
> > What is the backup command to use?
> >> >
> >> >
> >> > --
> >> > Tracy McKibben
> >> > MCDBA
> >> > http://www.realsqlguy.com
> >|||Lets see if I get this right. I might have missed what you are doing. You want to do something like:
1 Backup database (disk or tape)
2 Tape backup log
3 Disk backup log with NO_TRUNCATE
4 Tape backup log
5 Disk backup log with NO_TRUNCATE
For above scenario, you will be able to restore 1, 2, 3, but not further. See
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp for more details. Can you please
elaborate if above is not your scenario.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Techhead" <jorgenson.b@.gmail.com> wrote in message
news:1157648034.276585.67180@.b28g2000cwb.googlegroups.com...
> But I don't want to restore using SQL, I want to use my 3rd party
> backup solution to do the restore. THis solution can restore SQL DB's
> to point-in-time using truncated logs that the software backed up. i
> just don't want SQL truncating when my backup software does.
> Tibor Karaszi wrote:
>> > Why would I not want to truncate? Because I have a third party backup
>> > solution doing it for me and it will not work if my maintenance plan
>> > and 3rd party backup truncate at the same time.
>> I strongly encourage you to read the article I posted a link to. You will see that even with
>> NO_TRUNCATE, you cannot "skip" restoring one of the log backup "in between". The article was
>> written
>> for SQL Server 2000, I haven't tried it on 2005 yet.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Techhead" <jorgenson.b@.gmail.com> wrote in message
>> news:1156284873.311342.152930@.p79g2000cwp.googlegroups.com...
>> > Why would I not want to truncate? Because I have a third party backup
>> > solution doing it for me and it will not work if my maintenance plan
>> > and 3rd party backup truncate at the same time. I still want to do a
>> > full backup in SQL but not truncate... maybe a copy of the tran logs
>> > will work, but no truncate.
>> >
>> >
>> > Tibor Karaszi wrote:
>> >> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular basis?
>> >> This
>> >> option is only there in order for you to do the last log backup on a corrupt database. See:
>> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> >> news:44E33CF4.6030307@.realsqlguy.com...
>> >> > Techhead wrote:
>> >> >> I need to peform a log backup with no truncate. My regular maintenance
>> >> >> plan runs like this:
>> >> >>
>> >> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> >> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
>> >> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
>> >> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
>> >> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
>> >> >>
>> >> >>
>> >> >> Where in here do I add the no truncate option?
>> >> >>
>> >> >
>> >> > You don't. You'll have to run the BACKUP command directly, not from a maintenance plan.
>> >
>> >
>> > What is the backup command to use?
>> >> >
>> >> >
>> >> > --
>> >> > Tracy McKibben
>> >> > MCDBA
>> >> > http://www.realsqlguy.com
>> >
>|||Actually this is what I want:
1. Backup Database to Disk
2. Backup Log to Disk NO_TRUNCATE
3. Backup Database to Tape
4. Backup Log to Tape TRUNCATE
Tibor Karaszi wrote:
> Lets see if I get this right. I might have missed what you are doing. You want to do something like:
> 1 Backup database (disk or tape)
> 2 Tape backup log
> 3 Disk backup log with NO_TRUNCATE
> 4 Tape backup log
> 5 Disk backup log with NO_TRUNCATE
> For above scenario, you will be able to restore 1, 2, 3, but not further. See
> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp for more details. Can you please
> elaborate if above is not your scenario.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> news:1157648034.276585.67180@.b28g2000cwb.googlegroups.com...
> > But I don't want to restore using SQL, I want to use my 3rd party
> > backup solution to do the restore. THis solution can restore SQL DB's
> > to point-in-time using truncated logs that the software backed up. i
> > just don't want SQL truncating when my backup software does.
> >
> > Tibor Karaszi wrote:
> >> > Why would I not want to truncate? Because I have a third party backup
> >> > solution doing it for me and it will not work if my maintenance plan
> >> > and 3rd party backup truncate at the same time.
> >>
> >> I strongly encourage you to read the article I posted a link to. You will see that even with
> >> NO_TRUNCATE, you cannot "skip" restoring one of the log backup "in between". The article was
> >> written
> >> for SQL Server 2000, I haven't tried it on 2005 yet.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> >> news:1156284873.311342.152930@.p79g2000cwp.googlegroups.com...
> >> > Why would I not want to truncate? Because I have a third party backup
> >> > solution doing it for me and it will not work if my maintenance plan
> >> > and 3rd party backup truncate at the same time. I still want to do a
> >> > full backup in SQL but not truncate... maybe a copy of the tran logs
> >> > will work, but no truncate.
> >> >
> >> >
> >> > Tibor Karaszi wrote:
> >> >> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular basis?
> >> >> This
> >> >> option is only there in order for you to do the last log backup on a corrupt database. See:
> >> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> >> >> news:44E33CF4.6030307@.realsqlguy.com...
> >> >> > Techhead wrote:
> >> >> >> I need to peform a log backup with no truncate. My regular maintenance
> >> >> >> plan runs like this:
> >> >> >>
> >> >> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> >> >> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
> >> >> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
> >> >> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
> >> >> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
> >> >> >>
> >> >> >>
> >> >> >> Where in here do I add the no truncate option?
> >> >> >>
> >> >> >
> >> >> > You don't. You'll have to run the BACKUP command directly, not from a maintenance plan.
> >> >
> >> >
> >> > What is the backup command to use?
> >> >> >
> >> >> >
> >> >> > --
> >> >> > Tracy McKibben
> >> >> > MCDBA
> >> >> > http://www.realsqlguy.com
> >> >
> >|||So you will only have one transaction log backup between each database backup?
Why do you want to use NO_TRUNCATE for the log backup to disk?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Techhead" <jorgenson.b@.gmail.com> wrote in message
news:1158096627.443342.147600@.b28g2000cwb.googlegroups.com...
> Actually this is what I want:
> 1. Backup Database to Disk
> 2. Backup Log to Disk NO_TRUNCATE
> 3. Backup Database to Tape
> 4. Backup Log to Tape TRUNCATE
>
> Tibor Karaszi wrote:
>> Lets see if I get this right. I might have missed what you are doing. You want to do something
>> like:
>> 1 Backup database (disk or tape)
>> 2 Tape backup log
>> 3 Disk backup log with NO_TRUNCATE
>> 4 Tape backup log
>> 5 Disk backup log with NO_TRUNCATE
>> For above scenario, you will be able to restore 1, 2, 3, but not further. See
>> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp for more details. Can you please
>> elaborate if above is not your scenario.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Techhead" <jorgenson.b@.gmail.com> wrote in message
>> news:1157648034.276585.67180@.b28g2000cwb.googlegroups.com...
>> > But I don't want to restore using SQL, I want to use my 3rd party
>> > backup solution to do the restore. THis solution can restore SQL DB's
>> > to point-in-time using truncated logs that the software backed up. i
>> > just don't want SQL truncating when my backup software does.
>> >
>> > Tibor Karaszi wrote:
>> >> > Why would I not want to truncate? Because I have a third party backup
>> >> > solution doing it for me and it will not work if my maintenance plan
>> >> > and 3rd party backup truncate at the same time.
>> >>
>> >> I strongly encourage you to read the article I posted a link to. You will see that even with
>> >> NO_TRUNCATE, you cannot "skip" restoring one of the log backup "in between". The article was
>> >> written
>> >> for SQL Server 2000, I haven't tried it on 2005 yet.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
>> >> news:1156284873.311342.152930@.p79g2000cwp.googlegroups.com...
>> >> > Why would I not want to truncate? Because I have a third party backup
>> >> > solution doing it for me and it will not work if my maintenance plan
>> >> > and 3rd party backup truncate at the same time. I still want to do a
>> >> > full backup in SQL but not truncate... maybe a copy of the tran logs
>> >> > will work, but no truncate.
>> >> >
>> >> >
>> >> > Tibor Karaszi wrote:
>> >> >> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular
>> >> >> basis?
>> >> >> This
>> >> >> option is only there in order for you to do the last log backup on a corrupt database. See:
>> >> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://www.solidqualitylearning.com/
>> >> >>
>> >> >>
>> >> >> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> >> >> news:44E33CF4.6030307@.realsqlguy.com...
>> >> >> > Techhead wrote:
>> >> >> >> I need to peform a log backup with no truncate. My regular maintenance
>> >> >> >> plan runs like this:
>> >> >> >>
>> >> >> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> >> >> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
>> >> >> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
>> >> >> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
>> >> >> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
>> >> >> >>
>> >> >> >>
>> >> >> >> Where in here do I add the no truncate option?
>> >> >> >>
>> >> >> >
>> >> >> > You don't. You'll have to run the BACKUP command directly, not from a maintenance plan.
>> >> >
>> >> >
>> >> > What is the backup command to use?
>> >> >> >
>> >> >> >
>> >> >> > --
>> >> >> > Tracy McKibben
>> >> >> > MCDBA
>> >> >> > http://www.realsqlguy.com
>> >> >
>> >
>|||Tibor Karaszi wrote:
> So you will only have one transaction log backup between each database backup?
Actually, after a disk backup, my tape backup will back up the log and
truncate every 4 hours.
> Why do you want to use NO_TRUNCATE for the log backup to disk?
Because, as I stated earlier, the tape backup does the truncation. Why
would I want my disk backup and tape backup both truncating? You would
have to restore from both disk and tape in order to do a complete log
restore to point-in-time.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> news:1158096627.443342.147600@.b28g2000cwb.googlegroups.com...
> > Actually this is what I want:
> >
> > 1. Backup Database to Disk
> > 2. Backup Log to Disk NO_TRUNCATE
> > 3. Backup Database to Tape
> > 4. Backup Log to Tape TRUNCATE
> >
> >
> > Tibor Karaszi wrote:
> >> Lets see if I get this right. I might have missed what you are doing. You want to do something
> >> like:
> >>
> >> 1 Backup database (disk or tape)
> >> 2 Tape backup log
> >> 3 Disk backup log with NO_TRUNCATE
> >> 4 Tape backup log
> >> 5 Disk backup log with NO_TRUNCATE
> >>
> >> For above scenario, you will be able to restore 1, 2, 3, but not further. See
> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp for more details. Can you please
> >> elaborate if above is not your scenario.
> >>
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> >> news:1157648034.276585.67180@.b28g2000cwb.googlegroups.com...
> >> > But I don't want to restore using SQL, I want to use my 3rd party
> >> > backup solution to do the restore. THis solution can restore SQL DB's
> >> > to point-in-time using truncated logs that the software backed up. i
> >> > just don't want SQL truncating when my backup software does.
> >> >
> >> > Tibor Karaszi wrote:
> >> >> > Why would I not want to truncate? Because I have a third party backup
> >> >> > solution doing it for me and it will not work if my maintenance plan
> >> >> > and 3rd party backup truncate at the same time.
> >> >>
> >> >> I strongly encourage you to read the article I posted a link to. You will see that even with
> >> >> NO_TRUNCATE, you cannot "skip" restoring one of the log backup "in between". The article was
> >> >> written
> >> >> for SQL Server 2000, I haven't tried it on 2005 yet.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> >> >> news:1156284873.311342.152930@.p79g2000cwp.googlegroups.com...
> >> >> > Why would I not want to truncate? Because I have a third party backup
> >> >> > solution doing it for me and it will not work if my maintenance plan
> >> >> > and 3rd party backup truncate at the same time. I still want to do a
> >> >> > full backup in SQL but not truncate... maybe a copy of the tran logs
> >> >> > will work, but no truncate.
> >> >> >
> >> >> >
> >> >> > Tibor Karaszi wrote:
> >> >> >> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular
> >> >> >> basis?
> >> >> >> This
> >> >> >> option is only there in order for you to do the last log backup on a corrupt database. See:
> >> >> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://www.solidqualitylearning.com/
> >> >> >>
> >> >> >>
> >> >> >> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> >> >> >> news:44E33CF4.6030307@.realsqlguy.com...
> >> >> >> > Techhead wrote:
> >> >> >> >> I need to peform a log backup with no truncate. My regular maintenance
> >> >> >> >> plan runs like this:
> >> >> >> >>
> >> >> >> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> >> >> >> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
> >> >> >> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
> >> >> >> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
> >> >> >> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> Where in here do I add the no truncate option?
> >> >> >> >>
> >> >> >> >
> >> >> >> > You don't. You'll have to run the BACKUP command directly, not from a maintenance plan.
> >> >> >
> >> >> >
> >> >> > What is the backup command to use?
> >> >> >> >
> >> >> >> >
> >> >> >> > --
> >> >> >> > Tracy McKibben
> >> >> >> > MCDBA
> >> >> >> > http://www.realsqlguy.com
> >> >> >
> >> >
> >|||Techhead wrote:
> Tibor Karaszi wrote:
>> So you will only have one transaction log backup between each database backup?
> Actually, after a disk backup, my tape backup will back up the log and
> truncate every 4 hours.
>> Why do you want to use NO_TRUNCATE for the log backup to disk?
> Because, as I stated earlier, the tape backup does the truncation. Why
> would I want my disk backup and tape backup both truncating? You would
> have to restore from both disk and tape in order to do a complete log
> restore to point-in-time.
You could really save yourself some trouble, and avoid a potential
restore failure, by eliminating this third-party log backup. Use the
native SQL backup command to do your backups to disk, and then let the
tape software backup those disk files. That's what any experienced DBA
does, because those third-party gizmos are unreliable, slow, and awkward
to restore from.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> You would
> have to restore from both disk and tape in order to do a complete log
> restore to point-in-time.
Did you read below article I posted earlier?
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
If not, please do. You cannot skip restoring a log backup, even if it was taken using NO_TRUNCATE.
Period. If you chose to not believe me, you are welcome, even though I have proof in above article.
It will be your head when you need to do a production restore.
In 2005, we have a new option for the backup command named COPY_ONLY. It will do what you want.
I really cannot say more on this. Again, read the article, and then it is up to you whether you want
to be able to restore from your log backups or not.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Techhead" <jorgenson.b@.gmail.com> wrote in message
news:1158255181.850317.47190@.h48g2000cwc.googlegroups.com...
> Tibor Karaszi wrote:
>> So you will only have one transaction log backup between each database backup?
> Actually, after a disk backup, my tape backup will back up the log and
> truncate every 4 hours.
>> Why do you want to use NO_TRUNCATE for the log backup to disk?
> Because, as I stated earlier, the tape backup does the truncation. Why
> would I want my disk backup and tape backup both truncating? You would
> have to restore from both disk and tape in order to do a complete log
> restore to point-in-time.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Techhead" <jorgenson.b@.gmail.com> wrote in message
>> news:1158096627.443342.147600@.b28g2000cwb.googlegroups.com...
>> > Actually this is what I want:
>> >
>> > 1. Backup Database to Disk
>> > 2. Backup Log to Disk NO_TRUNCATE
>> > 3. Backup Database to Tape
>> > 4. Backup Log to Tape TRUNCATE
>> >
>> >
>> > Tibor Karaszi wrote:
>> >> Lets see if I get this right. I might have missed what you are doing. You want to do something
>> >> like:
>> >>
>> >> 1 Backup database (disk or tape)
>> >> 2 Tape backup log
>> >> 3 Disk backup log with NO_TRUNCATE
>> >> 4 Tape backup log
>> >> 5 Disk backup log with NO_TRUNCATE
>> >>
>> >> For above scenario, you will be able to restore 1, 2, 3, but not further. See
>> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp for more details. Can you please
>> >> elaborate if above is not your scenario.
>> >>
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
>> >> news:1157648034.276585.67180@.b28g2000cwb.googlegroups.com...
>> >> > But I don't want to restore using SQL, I want to use my 3rd party
>> >> > backup solution to do the restore. THis solution can restore SQL DB's
>> >> > to point-in-time using truncated logs that the software backed up. i
>> >> > just don't want SQL truncating when my backup software does.
>> >> >
>> >> > Tibor Karaszi wrote:
>> >> >> > Why would I not want to truncate? Because I have a third party backup
>> >> >> > solution doing it for me and it will not work if my maintenance plan
>> >> >> > and 3rd party backup truncate at the same time.
>> >> >>
>> >> >> I strongly encourage you to read the article I posted a link to. You will see that even
>> >> >> with
>> >> >> NO_TRUNCATE, you cannot "skip" restoring one of the log backup "in between". The article
>> >> >> was
>> >> >> written
>> >> >> for SQL Server 2000, I haven't tried it on 2005 yet.
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://www.solidqualitylearning.com/
>> >> >>
>> >> >>
>> >> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
>> >> >> news:1156284873.311342.152930@.p79g2000cwp.googlegroups.com...
>> >> >> > Why would I not want to truncate? Because I have a third party backup
>> >> >> > solution doing it for me and it will not work if my maintenance plan
>> >> >> > and 3rd party backup truncate at the same time. I still want to do a
>> >> >> > full backup in SQL but not truncate... maybe a copy of the tran logs
>> >> >> > will work, but no truncate.
>> >> >> >
>> >> >> >
>> >> >> > Tibor Karaszi wrote:
>> >> >> >> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular
>> >> >> >> basis?
>> >> >> >> This
>> >> >> >> option is only there in order for you to do the last log backup on a corrupt database.
>> >> >> >> See:
>> >> >> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
>> >> >> >>
>> >> >> >> --
>> >> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> >> http://www.solidqualitylearning.com/
>> >> >> >>
>> >> >> >>
>> >> >> >> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> >> >> >> news:44E33CF4.6030307@.realsqlguy.com...
>> >> >> >> > Techhead wrote:
>> >> >> >> >> I need to peform a log backup with no truncate. My regular maintenance
>> >> >> >> >> plan runs like this:
>> >> >> >> >>
>> >> >> >> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> >> >> >> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
>> >> >> >> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
>> >> >> >> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
>> >> >> >> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >> Where in here do I add the no truncate option?
>> >> >> >> >>
>> >> >> >> >
>> >> >> >> > You don't. You'll have to run the BACKUP command directly, not from a maintenance
>> >> >> >> > plan.
>> >> >> >
>> >> >> >
>> >> >> > What is the backup command to use?
>> >> >> >> >
>> >> >> >> >
>> >> >> >> > --
>> >> >> >> > Tracy McKibben
>> >> >> >> > MCDBA
>> >> >> >> > http://www.realsqlguy.com
>> >> >> >
>> >> >
>> >
>|||Tibor Karaszi wrote:
>> You would
>> have to restore from both disk and tape in order to do a complete log
>> restore to point-in-time.
> Did you read below article I posted earlier?
> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> If not, please do. You cannot skip restoring a log backup, even if it
> was taken using NO_TRUNCATE. Period. If you chose to not believe me, you
> are welcome, even though I have proof in above article. It will be your
> head when you need to do a production restore.
> In 2005, we have a new option for the backup command named COPY_ONLY. It
> will do what you want.
> I really cannot say more on this. Again, read the article, and then it
> is up to you whether you want to be able to restore from your log
> backups or not.
Seems to be a theme around here this week, folks insist on using square
wheels on their car, in spite of all the suggestions given to use the
proper round ones... My guess is the third-party backup software has a
nice shiny GUI, or he convinced his boss that he MUST spend $$,$$$$ to
buy it, and now can't make it work...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I am an experienced DBA and 99% of all my other SQL backups are within
SQL and backed up to disk. Then tape comes around and backs those up. I
100% agree that this is the preferred method and this is what I do for
about 15 SQL servers EXCEPT for 1 particular instance. My point in this
posting is not to argue about what method works best. Don't worry about
what I want to do in this special circumstance. What I do is nobody's
business. How I do it is the whole point in this posting. Please stick
to the objective and leave the opinionated comments for another group
or time.
Tracy McKibben wrote:
> Techhead wrote:
> > Tibor Karaszi wrote:
> >> So you will only have one transaction log backup between each database backup?
> > Actually, after a disk backup, my tape backup will back up the log and
> > truncate every 4 hours.
> >> Why do you want to use NO_TRUNCATE for the log backup to disk?
> >
> > Because, as I stated earlier, the tape backup does the truncation. Why
> > would I want my disk backup and tape backup both truncating? You would
> > have to restore from both disk and tape in order to do a complete log
> > restore to point-in-time.
> You could really save yourself some trouble, and avoid a potential
> restore failure, by eliminating this third-party log backup. Use the
> native SQL backup command to do your backups to disk, and then let the
> tape software backup those disk files. That's what any experienced DBA
> does, because those third-party gizmos are unreliable, slow, and awkward
> to restore from.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Tibor Karaszi wrote:
> > You would
> > have to restore from both disk and tape in order to do a complete log
> > restore to point-in-time.
> Did you read below article I posted earlier?
> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
Yes I did and it has nothing to do with what I am doing.
> If not, please do.
You cannot skip restoring a log backup
"I am not skipping a log backup" *remember, my tape backup does the log
backup and truncates.
even if it was taken using NO_TRUNCATE.
> Period. If you chose to not believe me, you are welcome, even though I have proof in above article.
> It will be your head when you need to do a production restore.
> In 2005, we have a new option for the backup command named COPY_ONLY. It will do what you want.
There we go. COPY_ONLY. Just what I wanted to hear! This will solve my
issue once and for all.
SQL does a DB backup to disk and a COPY of the log to disk.
Tape does a DB backup to tape and a log backup to tape with TRUNCATION.
> I really cannot say more on this. Again, read the article, and then it is up to you whether you want
> to be able to restore from your log backups or not.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> news:1158255181.850317.47190@.h48g2000cwc.googlegroups.com...
> > Tibor Karaszi wrote:
> >> So you will only have one transaction log backup between each database backup?
> > Actually, after a disk backup, my tape backup will back up the log and
> > truncate every 4 hours.
> >>
> >> Why do you want to use NO_TRUNCATE for the log backup to disk?
> >
> > Because, as I stated earlier, the tape backup does the truncation. Why
> > would I want my disk backup and tape backup both truncating? You would
> > have to restore from both disk and tape in order to do a complete log
> > restore to point-in-time.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> >> news:1158096627.443342.147600@.b28g2000cwb.googlegroups.com...
> >> > Actually this is what I want:
> >> >
> >> > 1. Backup Database to Disk
> >> > 2. Backup Log to Disk NO_TRUNCATE
> >> > 3. Backup Database to Tape
> >> > 4. Backup Log to Tape TRUNCATE
> >> >
> >> >
> >> > Tibor Karaszi wrote:
> >> >> Lets see if I get this right. I might have missed what you are doing. You want to do something
> >> >> like:
> >> >>
> >> >> 1 Backup database (disk or tape)
> >> >> 2 Tape backup log
> >> >> 3 Disk backup log with NO_TRUNCATE
> >> >> 4 Tape backup log
> >> >> 5 Disk backup log with NO_TRUNCATE
> >> >>
> >> >> For above scenario, you will be able to restore 1, 2, 3, but not further. See
> >> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp for more details. Can you please
> >> >> elaborate if above is not your scenario.
> >> >>
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> >> >> news:1157648034.276585.67180@.b28g2000cwb.googlegroups.com...
> >> >> > But I don't want to restore using SQL, I want to use my 3rd party
> >> >> > backup solution to do the restore. THis solution can restore SQL DB's
> >> >> > to point-in-time using truncated logs that the software backed up. i
> >> >> > just don't want SQL truncating when my backup software does.
> >> >> >
> >> >> > Tibor Karaszi wrote:
> >> >> >> > Why would I not want to truncate? Because I have a third party backup
> >> >> >> > solution doing it for me and it will not work if my maintenance plan
> >> >> >> > and 3rd party backup truncate at the same time.
> >> >> >>
> >> >> >> I strongly encourage you to read the article I posted a link to. You will see that even
> >> >> >> with
> >> >> >> NO_TRUNCATE, you cannot "skip" restoring one of the log backup "in between". The article
> >> >> >> was
> >> >> >> written
> >> >> >> for SQL Server 2000, I haven't tried it on 2005 yet.
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://www.solidqualitylearning.com/
> >> >> >>
> >> >> >>
> >> >> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> >> >> >> news:1156284873.311342.152930@.p79g2000cwp.googlegroups.com...
> >> >> >> > Why would I not want to truncate? Because I have a third party backup
> >> >> >> > solution doing it for me and it will not work if my maintenance plan
> >> >> >> > and 3rd party backup truncate at the same time. I still want to do a
> >> >> >> > full backup in SQL but not truncate... maybe a copy of the tran logs
> >> >> >> > will work, but no truncate.
> >> >> >> >
> >> >> >> >
> >> >> >> > Tibor Karaszi wrote:
> >> >> >> >> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular
> >> >> >> >> basis?
> >> >> >> >> This
> >> >> >> >> option is only there in order for you to do the last log backup on a corrupt database.
> >> >> >> >> See:
> >> >> >> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> >> >> >> >>
> >> >> >> >> --
> >> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> >> http://www.solidqualitylearning.com/
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> >> >> >> >> news:44E33CF4.6030307@.realsqlguy.com...
> >> >> >> >> > Techhead wrote:
> >> >> >> >> >> I need to peform a log backup with no truncate. My regular maintenance
> >> >> >> >> >> plan runs like this:
> >> >> >> >> >>
> >> >> >> >> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> >> >> >> >> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
> >> >> >> >> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
> >> >> >> >> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
> >> >> >> >> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >> >> Where in here do I add the no truncate option?
> >> >> >> >> >>
> >> >> >> >> >
> >> >> >> >> > You don't. You'll have to run the BACKUP command directly, not from a maintenance
> >> >> >> >> > plan.
> >> >> >> >
> >> >> >> >
> >> >> >> > What is the backup command to use?
> >> >> >> >> >
> >> >> >> >> >
> >> >> >> >> > --
> >> >> >> >> > Tracy McKibben
> >> >> >> >> > MCDBA
> >> >> >> >> > http://www.realsqlguy.com
> >> >> >> >
> >> >> >
> >> >
> >|||Tracy McKibben wrote:
> Tibor Karaszi wrote:
> >> You would
> >> have to restore from both disk and tape in order to do a complete log
> >> restore to point-in-time.
> >
> > Did you read below article I posted earlier?
> > http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> >
> > If not, please do. You cannot skip restoring a log backup, even if it
> > was taken using NO_TRUNCATE. Period. If you chose to not believe me, you
> > are welcome, even though I have proof in above article. It will be your
> > head when you need to do a production restore.
> >
> > In 2005, we have a new option for the backup command named COPY_ONLY. It
> > will do what you want.
> >
> > I really cannot say more on this. Again, read the article, and then it
> > is up to you whether you want to be able to restore from your log
> > backups or not.
> Seems to be a theme around here this week, folks insist on using square
> wheels on their car, in spite of all the suggestions given to use the
> proper round ones... My guess is the third-party backup software has a
> nice shiny GUI, or he convinced his boss that he MUST spend $$,$$$$ to
> buy it, and now can't make it work...
Unlike some people in this world, I can take a square wheel and turn it
into a round one.
Anyways, without Tracy's "in-the-box" suggestions, I've been able to
make it work and I can restore either way. How did I do it? We'll if
you can take your square MCDBA exam books and turn them into round ones
then I'll tell you.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Friday, March 30, 2012

Northwind database in SQL Server Express is busted and gone,after SqlCommand fails in VB2005Expr

Northwind database in SQL Server Express is busted and gone,after SqlCommand fails in VB2005Expr

Northwind database in SQL Server Express is busted and gone,after SqlCommand fails in VB2005Expr

Northwind database in SQL Server Express is busted and gone,after SqlCommand fails in VB2005Expr

Northwind database in SQL Server Express is busted and gone,after SqlCommand fails in VB2005Expr

Friday, March 23, 2012

Non-SA can't view Activity Monitor with VIEW SERVER STATE perm.

Saturday, February 25, 2012

No Truncate Option in Log Backup

interface supported

Blog Archive

About Me