Backup database without stored procedures

I have a requirement to ship a backup of our database to a customer at
regular intervals. Unfortunately our stored procedures are proprietary and
can't go with the backup. Is there a product out there that can back up a
database without the stored procedures?
I can't encrypt all the stored procedures because that just creates too much
of a headache in maintenance. The database is backed up nightly now to a
full backup.
One solution might be to replicate the database to another database on the
same server. I can't risk any performance degredation so it would have to
be the least intrusive replication model. A lot of lag is fine since the
database will only be shipped out wly. Log shipping won't work too well
since I dump the transaction log hourly after backing it up.
Any other suggestions?
Thanks,
DaveNo. [Backup Database] will backup all db objects. Replication seems like the
best approach here.
Else, you could:
1. backup -> restore to new db -> drop sproc -> backup -> ship
2. backup -> ship -> drop sproc after restore
-oj
"David D Webb" <spivey@.nospam.post.com> wrote in message
news:u9P1Du$BFHA.3416@.TK2MSFTNGP09.phx.gbl...
>I have a requirement to ship a backup of our database to a customer at
>regular intervals. Unfortunately our stored procedures are proprietary and
>can't go with the backup. Is there a product out there that can back up a
>database without the stored procedures?
> I can't encrypt all the stored procedures because that just creates too
> much of a headache in maintenance. The database is backed up nightly now
> to a full backup.
> One solution might be to replicate the database to another database on the
> same server. I can't risk any performance degredation so it would have to
> be the least intrusive replication model. A lot of lag is fine since the
> database will only be shipped out wly. Log shipping won't work too
> well since I dump the transaction log hourly after backing it up.
> Any other suggestions?
> Thanks,
> Dave
>
>|||Can you be more specific as to what they need to do with the db once they
get it? A db isn't of much good without the sp's. Do they just need a
schema or the actual data? Do they need all the tables or just a few?
Andrew J. Kelly SQL MVP
"David D Webb" <spivey@.nospam.post.com> wrote in message
news:u9P1Du$BFHA.3416@.TK2MSFTNGP09.phx.gbl...
>I have a requirement to ship a backup of our database to a customer at
>regular intervals. Unfortunately our stored procedures are proprietary and
>can't go with the backup. Is there a product out there that can back up a
>database without the stored procedures?
> I can't encrypt all the stored procedures because that just creates too
> much of a headache in maintenance. The database is backed up nightly now
> to a full backup.
> One solution might be to replicate the database to another database on the
> same server. I can't risk any performance degredation so it would have to
> be the least intrusive replication model. A lot of lag is fine since the
> database will only be shipped out wly. Log shipping won't work too
> well since I dump the transaction log hourly after backing it up.
> Any other suggestions?
> Thanks,
> Dave
>
>|||They are not going to do a damn thing with it. They just WANT it. (-; They
may eventually run ad-hoc queries against it with some reporting tools, I
suppose. It should just contain the schema (all tables) and data - just no
sps, triggers, or udfs. Constraints, indexes, keys, etc are fine.
Thanks,
Dave
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uEtmQz$BFHA.3664@.TK2MSFTNGP14.phx.gbl...
> Can you be more specific as to what they need to do with the db once they
> get it? A db isn't of much good without the sp's. Do they just need a
> schema or the actual data? Do they need all the tables or just a few?
> --
> Andrew J. Kelly SQL MVP
>
> "David D Webb" <spivey@.nospam.post.com> wrote in message
> news:u9P1Du$BFHA.3416@.TK2MSFTNGP09.phx.gbl...
>|||> I can't encrypt all the stored procedures because that just creates too
> much of a headache in maintenance.
This shouldn't be an issue if you keep your database objects under source
control and use those scripts to build your database. However, WITH
ENCRYPTION is actually obfuscation so a determined person can still reverse
engineer the text.
Hope this helps.
Dan Guzman
SQL Server MVP
"David D Webb" <spivey@.nospam.post.com> wrote in message
news:u9P1Du$BFHA.3416@.TK2MSFTNGP09.phx.gbl...
>I have a requirement to ship a backup of our database to a customer at
>regular intervals. Unfortunately our stored procedures are proprietary and
>can't go with the backup. Is there a product out there that can back up a
>database without the stored procedures?
> I can't encrypt all the stored procedures because that just creates too
> much of a headache in maintenance. The database is backed up nightly now
> to a full backup.
> One solution might be to replicate the database to another database on the
> same server. I can't risk any performance degredation so it would have to
> be the least intrusive replication model. A lot of lag is fine since the
> database will only be shipped out wly. Log shipping won't work too
> well since I dump the transaction log hourly after backing it up.
> Any other suggestions?
> Thanks,
> Dave
>
>|||I don't know how large it is but how about restoring the backup to a machine
locally, dropping all the sps' and detaching it. Then give them the
detached db. It will be void of sp's and they can just attach it on their
end. It's faster than a restore as well.
Andrew J. Kelly SQL MVP
"David D Webb" <spivey@.nospam.post.com> wrote in message
news:ephEm5$BFHA.2460@.TK2MSFTNGP14.phx.gbl...
> They are not going to do a damn thing with it. They just WANT it. (-;
> They may eventually run ad-hoc queries against it with some reporting
> tools, I suppose. It should just contain the schema (all tables) and
> data - just no sps, triggers, or udfs. Constraints, indexes, keys, etc
> are fine.
> Thanks,
> Dave
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uEtmQz$BFHA.3664@.TK2MSFTNGP14.phx.gbl...
>|||We have source control, but I have 53 copies of the database in production
on our servers, each with minor additions. Its easily managed, but I still
need to run SQL Compare for sanity sake. Encryption is out of the question.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uhRYxRACFHA.1836@.tk2msftngp13.phx.gbl...
> This shouldn't be an issue if you keep your database objects under source
> control and use those scripts to build your database. However, WITH
> ENCRYPTION is actually obfuscation so a determined person can still
> reverse engineer the text.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "David D Webb" <spivey@.nospam.post.com> wrote in message
> news:u9P1Du$BFHA.3416@.TK2MSFTNGP09.phx.gbl...
>|||You could easily write a procedure ( proprietary again I suppose :) to
create a new database, and then copy all of the data out. Something like:
declare @.copyOfDatabase sysname
set @.copyOfDatabase = 'copyOfDatabase'
exec('create database copyOfDatabase on (name = ''copy'' , filename = ''c:'
+ @.copyOfDatabase + '.mdf'')')
declare @.cursor cursor, @.query varchar(8000)
set @.cursor = cursor for
select 'select * into copyOfDatabase.' + table_schema + '.' + table_name +
' from ' + table_schema + '.' + table_name
from information_schema.tables where table_type = 'base table'
open @.cursor
fetch from @.cursor into @.query
WHILE @.@.FETCH_STATUS = 0
BEGIN
exec(@.query)
fetch from @.cursor into @.query
END
This is pretty basic, but it will move tables structure only. I would
suggest you probably want to add indexes and constraints back from a script,
but this script could easily be extended to do just that.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"David D Webb" <spivey@.nospam.post.com> wrote in message
news:u9P1Du$BFHA.3416@.TK2MSFTNGP09.phx.gbl...
>I have a requirement to ship a backup of our database to a customer at
>regular intervals. Unfortunately our stored procedures are proprietary and
>can't go with the backup. Is there a product out there that can back up a
>database without the stored procedures?
> I can't encrypt all the stored procedures because that just creates too
> much of a headache in maintenance. The database is backed up nightly now
> to a full backup.
> One solution might be to replicate the database to another database on the
> same server. I can't risk any performance degredation so it would have to
> be the least intrusive replication model. A lot of lag is fine since the
> database will only be shipped out wly. Log shipping won't work too
> well since I dump the transaction log hourly after backing it up.
> Any other suggestions?
> Thanks,
> Dave
>
>|||Good idea. Probably better than what I suggested for sure.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ekPzPUACFHA.2460@.TK2MSFTNGP14.phx.gbl...
>I don't know how large it is but how about restoring the backup to a
>machine locally, dropping all the sps' and detaching it. Then give them
>the detached db. It will be void of sp's and they can just attach it on
>their end. It's faster than a restore as well.
> --
> Andrew J. Kelly SQL MVP
>
> "David D Webb" <spivey@.nospam.post.com> wrote in message
> news:ephEm5$BFHA.2460@.TK2MSFTNGP14.phx.gbl...
>|||you might want to check out DB Ghost (http://www.dbghost.com) - you could se
t
up a scheduled synchronization at the least intrusive time, synchronizing
only tables and data to a database that then can be backed up and shipped ou
t.
"David D Webb" wrote:

> I have a requirement to ship a backup of our database to a customer at
> regular intervals. Unfortunately our stored procedures are proprietary an
d
> can't go with the backup. Is there a product out there that can back up a
> database without the stored procedures?
> I can't encrypt all the stored procedures because that just creates too mu
ch
> of a headache in maintenance. The database is backed up nightly now to a
> full backup.
> One solution might be to replicate the database to another database on the
> same server. I can't risk any performance degredation so it would have to
> be the least intrusive replication model. A lot of lag is fine since the
> database will only be shipped out wly. Log shipping won't work too wel
l
> since I dump the transaction log hourly after backing it up.
> Any other suggestions?
> Thanks,
> Dave
>
>

Backup and recovery of SQL Server using VB.net

Hi,

I have a small application in which i'm using Sql Server as Database. my requirement is how to take the backup of the entire database or some tables from the database when there is any delete from the database. My requirement is to do from the VB.net application.Hope i delivered my question correctly. Any little help is beneficial to me.

-regards

GRK

Try this one out...

Dim oDevice As New SQLDMO.BackupDevice
Dim BACKUP As New SQLDMO.BACKUP
Dim SERVER As New SQLServer

Private Sub Form_Load()
On Error Resume Next 'If the device already exists an error will result if you try to add it again so just resume next cos its already there

With oDevice
.Type = SQLDMODevice_DiskDump
.Name = "NorthwindBakUp"
.PhysicalLocation = "C:\Documents and Settings\Administrator\Desktop\BACKUP.bak"
End With

SERVER.Connect "Sanjib", "sa"
SERVER.BackupDevices.Add oDevice
BACKUP.Action = SQLDMOBackup_Database
BACKUP.Database = "Northwind"
BACKUP.Devices ="NorthwindBakUp"
BACKUP.BackupSetDescription = "Full BackUp"
BACKUP.BackupSetName = "By Sanjib"

BACKUP.SQLBackup SERVER

End Sub

|||

Hi,

Here's my backup class of one of my project that uses Microsoft.SqlServer.Management:

public class BackupManager

{

Server srvSql;

SaveFileDialog saveBackupDialog = new SaveFileDialog();

OpenFileDialog openBackupDialog = new OpenFileDialog();

DatabaseCache dbCache = DatabaseCache.Instance;

private string GetAppPath()

{

return System.IO.Path.GetDirectoryName

(System.Windows.Forms.Application.ExecutablePath);

}

private void Connect()

{

ServerConnection srvConn = new

ServerConnection(dbCache.SqlConnection);

srvSql = new Server(srvConn);

}

private string GetDatabase(SqlConnection conn)

{

string[] connArray = conn.ConnectionString.Split(';');

string toMatch = "AttachDbFilename=";

string match = null;

foreach (string item in connArray) {

if (item.StartsWith(toMatch))

{

match = item.Substring(toMatch.Length);

break;

}

}

if (!String.IsNullOrEmpty(match) ) {

match = match.Replace("|DataDirectory|", GetAppPath());

} else {

throw new Exception(

"Could not extract database path from connection string");

}

return match;

}

public bool MakeBackup()

{

// If there was a SQL connection created

if (srvSql == null) {

Connect();

}

if (srvSql != null)

{

string path = GetAppPath();

path = path += @."\Backup";

if (!Directory.Exists(path))

{

Directory.CreateDirectory(path);

}

saveBackupDialog.InitialDirectory = path;

saveBackupDialog.DefaultExt = "bak";

// If the user has chosen a path

// where to save the backup file

if (saveBackupDialog.ShowDialog() == DialogResult.OK)

{

// Create a new backup operation

Backup bkpDatabase = new Backup();

// Set the backup type to a database backup

bkpDatabase.Action = BackupActionType.Database;

// Set the database that we want to perform a backup on

bkpDatabase.Database = GetDatabase(dbCache.SqlConnection);

// Set the backup device to a file

BackupDeviceItem bkpDevice = new BackupDeviceItem(saveBackupDialog.FileName, DeviceType.File);

// Add the backup device to the backup

bkpDatabase.Devices.Add(bkpDevice);

// Perform the backup

bkpDatabase.SqlBackup(srvSql);

} else {

return false;

}

} else {

throw new Exception("Could not connect to database.");

}

return true;

}

public void RestoreBackup()

{

//database must first be unloaded before calling this.

// If there was a SQL connection created

if (srvSql == null)

{

Connect();

}

if (srvSql != null)

{

openBackupDialog.InitialDirectory = "./Backup";

openBackupDialog.DefaultExt = "bak";

// If the user has chosen the file from which he wants the database to be restored

if (openBackupDialog.ShowDialog() == DialogResult.OK)

{

// Create a new database restore operation

Restore rstDatabase = new Restore();

// Set the restore type to a database restore

rstDatabase.Action = RestoreActionType.Database;

// Set the database that we want to perform the restore on

rstDatabase.Database = GetDatabase(dbCache.SqlConnection);

// Set the backup device from which we want to restore, to a file

BackupDeviceItem bkpDevice = new BackupDeviceItem(openBackupDialog.FileName, DeviceType.File);

// Add the backup device to the restore type

rstDatabase.Devices.Add(bkpDevice);

// If the database already exists, replace it

rstDatabase.ReplaceDatabase = true;

// Perform the restore

try

{

rstDatabase.SqlRestore(srvSql);

}

catch (FailedOperationException ex)

{

Log.WriteLine( "" );

Log.WriteLine( "Error: (BackupManager.RestoreBackup)" );

Log.Write( ex );

throw new Exception("Error while restoring backup.", ex);

}

}

}

else

{

throw new Exception("Could not connect to database.");

}

}

}

It's not generic so you'll have to change some of the code (and translate to vb...) You can see the basic idee from the code above.

Good luck,
Charles

|||

Hi ,

forgive me for the late reply.I tried the above code it is running after small changes to the code.thanks a lot

-regards

GRK

|||

Hi,

i hav the same problem...

" I have a small application in which i'm using Sql Server as Database. my requirement is how to take the backup of the entire database or some tables from the database when there is any delete from the database. My requirement is to do from the VB.net application"

i used tht abov code.. bt it dnt wok..

so.. plz snd me the code... plz its urgent.....

thnks in advance

siva

|||brallient|||

where i can find DatabaseCache class i downloaded and install SQLServer2005_XMO.msi

but still i cant see that class, other code works fine.

Thanks

|||

Hi,

It's normal, DatabaseCache is my own class that contains all the data of my application :)

Basically you need to remove that and replace dbCache.SqlConnection in the Connect function with the connection you use for your database.

Charles

|||

my connectionString is

connectionString = "Data Source=localhost,1433;Network Library=DBMSSOCN;Initial Catalog=myDb; User ID=sa;Password=saabc;";

when i replaced dbCache.SqlConnection with my own opened connection as "openedConnection", it compiled and executed, but when i click on backup button, i caught by following exception

"Could not extract database path from connection string"

Could you please change the code according to my connection.

Thanks

|||

Ok,

My code assumes that a database file is used. So in your case the GetDatabase method fails to find the db file from the connection string.

Try replacing bkpDatabase.Database = GetDatabase(dbCache.SqlConnection); by

bkpDatabase.Database = "myDb";

or better yet to modify the GetDatabase method to take into account the Initial Catalog key.

Charles

|||Thanks, let me check it

Backup and recovery of SQL Server using VB.net

Hi,

I have a small application in which i'm using Sql Server as Database. my requirement is how to take the backup of the entire database or some tables from the database when there is any delete from the database. My requirement is to do from the VB.net application.Hope i delivered my question correctly. Any little help is beneficial to me.

-regards

GRK

Try this one out...

Dim oDevice As New SQLDMO.BackupDevice
Dim BACKUP As New SQLDMO.BACKUP
Dim SERVER As New SQLServer

Private Sub Form_Load()
On Error Resume Next 'If the device already exists an error will result if you try to add it again so just resume next cos its already there

With oDevice
.Type = SQLDMODevice_DiskDump
.Name = "NorthwindBakUp"
.PhysicalLocation = "C:\Documents and Settings\Administrator\Desktop\BACKUP.bak"
End With

SERVER.Connect "Sanjib", "sa"
SERVER.BackupDevices.Add oDevice
BACKUP.Action = SQLDMOBackup_Database
BACKUP.Database = "Northwind"
BACKUP.Devices ="NorthwindBakUp"
BACKUP.BackupSetDescription = "Full BackUp"
BACKUP.BackupSetName = "By Sanjib"

BACKUP.SQLBackup SERVER

End Sub

|||

Hi,

Here's my backup class of one of my project that uses Microsoft.SqlServer.Management:

public class BackupManager

{

Server srvSql;

SaveFileDialog saveBackupDialog = new SaveFileDialog();

OpenFileDialog openBackupDialog = new OpenFileDialog();

DatabaseCache dbCache = DatabaseCache.Instance;

private string GetAppPath()

{

return System.IO.Path.GetDirectoryName

(System.Windows.Forms.Application.ExecutablePath);

}

private void Connect()

{

ServerConnection srvConn = new

ServerConnection(dbCache.SqlConnection);

srvSql = new Server(srvConn);

}

private string GetDatabase(SqlConnection conn)

{

string[] connArray = conn.ConnectionString.Split(';');

string toMatch = "AttachDbFilename=";

string match = null;

foreach (string item in connArray) {

if (item.StartsWith(toMatch))

{

match = item.Substring(toMatch.Length);

break;

}

}

if (!String.IsNullOrEmpty(match) ) {

match = match.Replace("|DataDirectory|", GetAppPath());

} else {

throw new Exception(

"Could not extract database path from connection string");

}

return match;

}

public bool MakeBackup()

{

// If there was a SQL connection created

if (srvSql == null) {

Connect();

}

if (srvSql != null)

{

string path = GetAppPath();

path = path += @."\Backup";

if (!Directory.Exists(path))

{

Directory.CreateDirectory(path);

}

saveBackupDialog.InitialDirectory = path;

saveBackupDialog.DefaultExt = "bak";

// If the user has chosen a path

// where to save the backup file

if (saveBackupDialog.ShowDialog() == DialogResult.OK)

{

// Create a new backup operation

Backup bkpDatabase = new Backup();

// Set the backup type to a database backup

bkpDatabase.Action = BackupActionType.Database;

// Set the database that we want to perform a backup on

bkpDatabase.Database = GetDatabase(dbCache.SqlConnection);

// Set the backup device to a file

BackupDeviceItem bkpDevice = new BackupDeviceItem(saveBackupDialog.FileName, DeviceType.File);

// Add the backup device to the backup

bkpDatabase.Devices.Add(bkpDevice);

// Perform the backup

bkpDatabase.SqlBackup(srvSql);

} else {

return false;

}

} else {

throw new Exception("Could not connect to database.");

}

return true;

}

public void RestoreBackup()

{

//database must first be unloaded before calling this.

// If there was a SQL connection created

if (srvSql == null)

{

Connect();

}

if (srvSql != null)

{

openBackupDialog.InitialDirectory = "./Backup";

openBackupDialog.DefaultExt = "bak";

// If the user has chosen the file from which he wants the database to be restored

if (openBackupDialog.ShowDialog() == DialogResult.OK)

{

// Create a new database restore operation

Restore rstDatabase = new Restore();

// Set the restore type to a database restore

rstDatabase.Action = RestoreActionType.Database;

// Set the database that we want to perform the restore on

rstDatabase.Database = GetDatabase(dbCache.SqlConnection);

// Set the backup device from which we want to restore, to a file

BackupDeviceItem bkpDevice = new BackupDeviceItem(openBackupDialog.FileName, DeviceType.File);

// Add the backup device to the restore type

rstDatabase.Devices.Add(bkpDevice);

// If the database already exists, replace it

rstDatabase.ReplaceDatabase = true;

// Perform the restore

try

{

rstDatabase.SqlRestore(srvSql);

}

catch (FailedOperationException ex)

{

Log.WriteLine( "" );

Log.WriteLine( "Error: (BackupManager.RestoreBackup)" );

Log.Write( ex );

throw new Exception("Error while restoring backup.", ex);

}

}

}

else

{

throw new Exception("Could not connect to database.");

}

}

}

It's not generic so you'll have to change some of the code (and translate to vb...) You can see the basic idee from the code above.

Good luck,
Charles

|||

Hi ,

forgive me for the late reply.I tried the above code it is running after small changes to the code.thanks a lot

-regards

GRK

|||

Hi,

i hav the same problem...

" I have a small application in which i'm using Sql Server as Database. my requirement is how to take the backup of the entire database or some tables from the database when there is any delete from the database. My requirement is to do from the VB.net application"

i used tht abov code.. bt it dnt wok..

so.. plz snd me the code... plz its urgent.....

thnks in advance

siva

|||brallient|||

where i can find DatabaseCache class i downloaded and install SQLServer2005_XMO.msi

but still i cant see that class, other code works fine.

Thanks

|||

Hi,

It's normal, DatabaseCache is my own class that contains all the data of my application :)

Basically you need to remove that and replace dbCache.SqlConnection in the Connect function with the connection you use for your database.

Charles

|||

my connectionString is

connectionString = "Data Source=localhost,1433;Network Library=DBMSSOCN;Initial Catalog=myDb; User ID=sa;Password=saabc;";

when i replaced dbCache.SqlConnection with my own opened connection as "openedConnection", it compiled and executed, but when i click on backup button, i caught by following exception

"Could not extract database path from connection string"

Could you please change the code according to my connection.

Thanks

|||

Ok,

My code assumes that a database file is used. So in your case the GetDatabase method fails to find the db file from the connection string.

Try replacing bkpDatabase.Database = GetDatabase(dbCache.SqlConnection); by

bkpDatabase.Database = "myDb";

or better yet to modify the GetDatabase method to take into account the Initial Catalog key.

Charles

|||Thanks, let me check it