sql backup files: file

Showing posts with label file. Show all posts

Tuesday, March 27, 2012

Backup SQL Tables

Good morning :)

i have a remote SQL Server tables and a remote Access.mdb file with the same tables in it.
i need to find a way to transfer the data from the SQL tables to the Access tables, with SQL commands, without being dependent in the SQL enterprise manager.

does anybody have a good idea?

thanks a lot,Why do you want to transfer the data from MS SQL to Access ?

backup SQL server databases

Hi,
At the moment I use a SQL job to make dumps of SQL server databases to
the file system. A few hours later I backup these files to tape.
Now I want to connect both processes. In the backup program I can
start .bat files and catch the error level of the batch file. But is
it possible to start a SQL server job from command line and to catch
the outcome of the SQL backup process (so that the backup to tape only
starts when the backup to disk succeeds)?
Many thanks in advance,
Stijn.
You can start a job using sp_start_job. But jobs are executed asynchronously. This means that you
cannot reliably "wait" until end of execution and then report back the error. So, I suggest you grab
whatever TSQL you have in your job, put it in a script file and execute that using SQLCMD.EXE (or
OSQL.EXE depending on version of SQL Server).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<stijn.calders@.gmail.com> wrote in message
news:1183967597.475367.257660@.o61g2000hsh.googlegr oups.com...
> Hi,
> At the moment I use a SQL job to make dumps of SQL server databases to
> the file system. A few hours later I backup these files to tape.
> Now I want to connect both processes. In the backup program I can
> start .bat files and catch the error level of the batch file. But is
> it possible to start a SQL server job from command line and to catch
> the outcome of the SQL backup process (so that the backup to tape only
> starts when the backup to disk succeeds)?
> Many thanks in advance,
> Stijn.
>

backup SQL server databases

Hi,
At the moment I use a SQL job to make dumps of SQL server databases to
the file system. A few hours later I backup these files to tape.
Now I want to connect both processes. In the backup program I can
start .bat files and catch the error level of the batch file. But is
it possible to start a SQL server job from command line and to catch
the outcome of the SQL backup process (so that the backup to tape only
starts when the backup to disk succeeds)?
Many thanks in advance,
Stijn.You can start a job using sp_start_job. But jobs are executed asynchronously. This means that you
cannot reliably "wait" until end of execution and then report back the error. So, I suggest you grab
whatever TSQL you have in your job, put it in a script file and execute that using SQLCMD.EXE (or
OSQL.EXE depending on version of SQL Server).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<stijn.calders@.gmail.com> wrote in message
news:1183967597.475367.257660@.o61g2000hsh.googlegroups.com...
> Hi,
> At the moment I use a SQL job to make dumps of SQL server databases to
> the file system. A few hours later I backup these files to tape.
> Now I want to connect both processes. In the backup program I can
> start .bat files and catch the error level of the batch file. But is
> it possible to start a SQL server job from command line and to catch
> the outcome of the SQL backup process (so that the backup to tape only
> starts when the backup to disk succeeds)?
> Many thanks in advance,
> Stijn.
>

backup SQL server databases

Hi,
At the moment I use a SQL job to make dumps of SQL server databases to
the file system. A few hours later I backup these files to tape.
Now I want to connect both processes. In the backup program I can
start .bat files and catch the error level of the batch file. But is
it possible to start a SQL server job from command line and to catch
the outcome of the SQL backup process (so that the backup to tape only
starts when the backup to disk succeeds)?
Many thanks in advance,
Stijn.You can start a job using sp_start_job. But jobs are executed asynchronously
. This means that you
cannot reliably "wait" until end of execution and then report back the error
. So, I suggest you grab
whatever TSQL you have in your job, put it in a script file and execute that
using SQLCMD.EXE (or
OSQL.EXE depending on version of SQL Server).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<stijn.calders@.gmail.com> wrote in message
news:1183967597.475367.257660@.o61g2000hsh.googlegroups.com...
> Hi,
> At the moment I use a SQL job to make dumps of SQL server databases to
> the file system. A few hours later I backup these files to tape.
> Now I want to connect both processes. In the backup program I can
> start .bat files and catch the error level of the batch file. But is
> it possible to start a SQL server job from command line and to catch
> the outcome of the SQL backup process (so that the backup to tape only
> starts when the backup to disk succeeds)?
> Many thanks in advance,
> Stijn.
>

Backup SQL Database in asp.net

Hi ,

Anyone knows ,How to backup sql Database in asp.net .and I need to get a .bak file through asp.net.

Pls help me.

Tamil

Assuming that the client has the required permissions to perform a backup (database permissions and permissions on the directory where you're saving the file), then you can run the [BACKUP DATABASE] command via the SQLCommand object.

Set the command type to Text, provide the appropriate SQL, and executeNonQuery. There's an MSDN example here (search the page for BACKUP) :http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.deletecommand.aspx

But note that if you have a large-ish database, you may want to up the timeout property to ensure the backup completes.

|||

Hi I got the backup database locally.But now i need to get backup for remote server.

Here is my code.

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.IO;

using System.Collections.Generic;

using System.Text;

using System.Text.RegularExpressions;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

using s= SQLDMO;

publicpartialclassDefault3 : System.Web.UI.Page

{

protectedvoid Page_Load(object sender,EventArgs e)

{

Panel1.Visible =false;

Label3.Visible =false;

if (!IsPostBack)

{

SQLDMO.SQLServer oSQLServer =new SQLDMO.SQLServer();

SQLDMO.Restore oRestore =new SQLDMO.Restore();

SQLDMO.Database oDatabase =new SQLDMO.Database();

oSQLServer.Connect("COMP11\\SQLEXPRESS","sa","123");

foreach (SQLDMO.Database dbin oSQLServer.Databases)

{

drpdb.Items.Add(db.Name);

}

protectedvoid Butback_Click(object sender,EventArgs e)

{

Microsoft.SqlServer.Management.Common.ServerConnection serverConnection =new Microsoft.SqlServer.Management.Common.ServerConnection("COMP11\\SQLEXPRESS");

Server s;

s =newServer(serverConnection);Database db;

db = s.Databases[drpdb.SelectedItem.Text];

int rec;

rec =Convert.ToInt32(db.DatabaseOptions.RecoveryModel);

Backup sbackup =newBackup();

sbackup.Action =BackupActionType.Database;

sbackup.BackupSetDescription ="Full Backup";

sbackup.BackupSetName ="Database Backup";

sbackup.Database = drpdb.SelectedItem.Text;

BackupDeviceItem dbi;

string path = drpdb.SelectedItem.Text +".bak";

string checkpath ="D:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Backup\\" + path;if (File.Exists(checkpath)==true)

{

Panel1.Visible =true;

Label2.Text ="";Label3.Visible =false;

}

else

{

Label3.Visible =true;

Panel1.Visible =false;dbi =newBackupDeviceItem(path,DeviceType.File);

sbackup.Devices.Add(dbi);

sbackup.Incremental =false;

sbackup.LogTruncation =BackupTruncateLogType.Truncate;

sbackup.SqlBackup(s);

Label3.Text ="Full Backup complete.";

sbackup.Devices.Remove(dbi);

}

protectedvoid Button1_Click(object sender,EventArgs e)

{

Microsoft.SqlServer.Management.Common.ServerConnection serverConnection =new Microsoft.SqlServer.Management.Common.ServerConnection("COMP11\\SQLEXPRESS");

Server s;

s =newServer(serverConnection);Database db;

db = s.Databases[drpdb.SelectedItem.Text];

int rec;

rec =Convert.ToInt32(db.DatabaseOptions.RecoveryModel);

Backup sbackup =newBackup();

sbackup.Action =BackupActionType.Database;

sbackup.BackupSetDescription ="Full Backup";

sbackup.BackupSetName ="Database Backup";

sbackup.Database = drpdb.SelectedItem.Text;

BackupDeviceItem dbi;

string path = drpdb.SelectedItem.Text +".bak";

string checkpath ="D:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Backup\\" + path;

File.Delete(checkpath);

dbi =newBackupDeviceItem(path,DeviceType.File);

sbackup.Devices.Add(dbi);

sbackup.Incremental =false;sbackup.LogTruncation =BackupTruncateLogType.Truncate;

sbackup.SqlBackup(s);

Label3.Visible =true;

Label3.Text = drpdb.SelectedItem.Text +"Backup complete.";

sbackup.Devices.Remove(dbi);

}

protectedvoid Button2_Click(object sender,EventArgs e)

{

Panel1.Visible =true;Label2.Text ="Selected " + drpdb.SelectedItem.Text +" Database fully not Completed!";

}

Please help me!

Tamil

|||

Anyoneknows

I need to get Backup a database from Remote Server.

Tamil

|||

Hi,

check this

http://www.codeplex.com/SqlWebAdmin

|||

hi Mehedi Hasan

I opened that URL.but i didnt understand ,Is it match my requirement?I need to get a backup for SQL Database from remote Server.I already done for local backup.But i need to get a backup for SQL Databases from REmote Server.

Please help me with example.

Tamil

|||

hi friends,,

I got it how to backup database in remote Server.We need to give login secure false then it connects the other server.

Tamil

Sunday, March 25, 2012

backup size and initial database size

SQL server 2000
Hi
How can i find
1. Information about all/current backup files, size of backup file on
server using sql query or a script.
2. Initial size of all databases (when they were installed) and current size
of all databases.
Thanks
ontario, canada
I have information about all database backups for last two years (Including
database size in table Backupset in MSDB.
Does % increase in database backup size reflects the % increase in database
size?
ontario, canada
"db" wrote:
> SQL server 2000
> Hi
> How can i find
> 1. Information about all/current backup files, size of backup file on
> server using sql query or a script.
> 2. Initial size of all databases (when they were installed) and current size
> of all databases.
> Thanks
> ontario, canada
|||I am using
select database_name,database_creation_date,backup_start_ date,backup_size
from backupset order by database_name,backup_size desc
to get the bacup size information. I get the information.
I want to select a subset of records which give me information for backupset
for one day of every month (10-...200?). I.e monthwise backup set
information for 10-jan, 10-feb-10-mar...etc). When I issue FOLLOWING COMMAND
select database_name,database_creation_date,backup_start_ date,backup_size
from backupset WHERE BACKUP_START_DATE LIKE '10-...200?) order by
database_name,backup_size desc
I do not get the desired result. What is correct sql query?
ontario, canada
"db" wrote:
[vbcol=seagreen]
> I have information about all database backups for last two years (Including
> database size in table Backupset in MSDB.
> Does % increase in database backup size reflects the % increase in database
> size?
> --
> ontario, canada
> "db" wrote:
|||Select ...
Where BACKUP_START_DATE >= '20000101' And Day(BACKUP_START_DATE) = 10
Tom
"db" <db@.discussions.microsoft.com> wrote in message
news:BCC341DB-9254-43E5-B4FC-08F90F86A325@.microsoft.com...[vbcol=seagreen]
>I am using
> select database_name,database_creation_date,backup_start_ date,backup_size
> from backupset order by database_name,backup_size desc
> to get the bacup size information. I get the information.
> I want to select a subset of records which give me information for
> backupset
> for one day of every month (10-...200?). I.e monthwise backup set
> information for 10-jan, 10-feb-10-mar...etc). When I issue FOLLOWING
> COMMAND
> select database_name,database_creation_date,backup_start_ date,backup_size
> from backupset WHERE BACKUP_START_DATE LIKE '10-...200?) order by
> database_name,backup_size desc
> I do not get the desired result. What is correct sql query?
>
> --
> ontario, canada
>
> "db" wrote:

backup size and initial database size

SQL server 2000
Hi
How can i find
1. Information about all/current backup files, size of backup file on
server using sql query or a script.
2. Initial size of all databases (when they were installed) and current size
of all databases.
Thanks
--
ontario, canadaI have information about all database backups for last two years (Including
database size in table Backupset in MSDB.
Does % increase in database backup size reflects the % increase in database
size'
--
ontario, canada
"db" wrote:
> SQL server 2000
> Hi
> How can i find
> 1. Information about all/current backup files, size of backup file on
> server using sql query or a script.
> 2. Initial size of all databases (when they were installed) and current size
> of all databases.
> Thanks
> ontario, canada|||I am using
select database_name,database_creation_date,backup_start_date,backup_size
from backupset order by database_name,backup_size desc
to get the bacup size information. I get the information.
I want to select a subset of records which give me information for backupset
for one day of every month (10-...200?). I.e monthwise backup set
information for 10-jan, 10-feb-10-mar...etc). When I issue FOLLOWING COMMAND
select database_name,database_creation_date,backup_start_date,backup_size
from backupset WHERE BACKUP_START_DATE LIKE '10-...200?) order by
database_name,backup_size desc
I do not get the desired result. What is correct sql query?
ontario, canada
"db" wrote:
> I have information about all database backups for last two years (Including
> database size in table Backupset in MSDB.
> Does % increase in database backup size reflects the % increase in database
> size'
> --
> ontario, canada
> "db" wrote:
> > SQL server 2000
> > Hi
> > How can i find
> > 1. Information about all/current backup files, size of backup file on
> > server using sql query or a script.
> > 2. Initial size of all databases (when they were installed) and current size
> > of all databases.
> > Thanks
> > ontario, canada|||Select ...
Where BACKUP_START_DATE >= '20000101' And Day(BACKUP_START_DATE) = 10
Tom
"db" <db@.discussions.microsoft.com> wrote in message
news:BCC341DB-9254-43E5-B4FC-08F90F86A325@.microsoft.com...
>I am using
> select database_name,database_creation_date,backup_start_date,backup_size
> from backupset order by database_name,backup_size desc
> to get the bacup size information. I get the information.
> I want to select a subset of records which give me information for
> backupset
> for one day of every month (10-...200?). I.e monthwise backup set
> information for 10-jan, 10-feb-10-mar...etc). When I issue FOLLOWING
> COMMAND
> select database_name,database_creation_date,backup_start_date,backup_size
> from backupset WHERE BACKUP_START_DATE LIKE '10-...200?) order by
> database_name,backup_size desc
> I do not get the desired result. What is correct sql query?
>
> --
> ontario, canada
>
> "db" wrote:
>> I have information about all database backups for last two years
>> (Including
>> database size in table Backupset in MSDB.
>> Does % increase in database backup size reflects the % increase in
>> database
>> size'
>> --
>> ontario, canada
>> "db" wrote:
>> > SQL server 2000
>> > Hi
>> > How can i find
>> > 1. Information about all/current backup files, size of backup file on
>> > server using sql query or a script.
>> > 2. Initial size of all databases (when they were installed) and current
>> > size
>> > of all databases.
>> > Thanks
>> > ontario, canada

Thursday, March 22, 2012

BACKUP SERVICE MASTER KEY TO FILE

Hello,
I would to create a backup file dynamically (the filename will contain
the hostname of the DB).
any idea how can I use a variable for that?
(a variable is easy to use for a regular database backup, but not for
a master key).
I wrote on the comment the error I get.
thanks, Yaniv
use master;
declare @.tmpStr varchar(200)
set @.tmpStr = 'c:\temp\DB_service_2.key'
-- working
BACKUP SERVICE MASTER KEY TO FILE = 'c:\temp\DB_service.key'
ENCRYPTION BY
PASSWORD = 't1t1t1t1t1'
-- error: Incorrect syntax near '@.tmpStr'
BACKUP SERVICE MASTER KEY TO FILE = @.tmpStr ENCRYPTION BY PASSWORD =
't1t1t1t1t1'
goYou can use dynamic SQL. Built the BACKUP command in a TSQL varchar variable
, then execute it:
EXEC(@.sql)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<yaniv.harpaz@.gmail.com> wrote in message
news:1170238336.716966.152710@.p10g2000cwp.googlegroups.com...
> Hello,
> I would to create a backup file dynamically (the filename will contain
> the hostname of the DB).
> any idea how can I use a variable for that?
> (a variable is easy to use for a regular database backup, but not for
> a master key).
> I wrote on the comment the error I get.
> thanks, Yaniv
> use master;
> declare @.tmpStr varchar(200)
> set @.tmpStr = 'c:\temp\DB_service_2.key'
> -- working
> BACKUP SERVICE MASTER KEY TO FILE = 'c:\temp\DB_service.key'
> ENCRYPTION BY
> PASSWORD = 't1t1t1t1t1'
> -- error: Incorrect syntax near '@.tmpStr'
> BACKUP SERVICE MASTER KEY TO FILE = @.tmpStr ENCRYPTION BY PASSWORD =
> 't1t1t1t1t1'
> go
>

BACKUP SERVICE MASTER KEY TO FILE

Hello,
I would to create a backup file dynamically (the filename will contain
the hostname of the DB).
any idea how can I use a variable for that?
(a variable is easy to use for a regular database backup, but not for
a master key).
I wrote on the comment the error I get.
thanks, Yaniv
use master;
declare @.tmpStr varchar(200)
set @.tmpStr = 'c:\temp\DB_service_2.key'
-- working
BACKUP SERVICE MASTER KEY TO FILE = 'c:\temp\DB_service.key'
ENCRYPTION BY
PASSWORD = 't1t1t1t1t1'
-- error: Incorrect syntax near '@.tmpStr'
BACKUP SERVICE MASTER KEY TO FILE = @.tmpStr ENCRYPTION BY PASSWORD = 't1t1t1t1t1'
goYou can use dynamic SQL. Built the BACKUP command in a TSQL varchar variable, then execute it:
EXEC(@.sql)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<yaniv.harpaz@.gmail.com> wrote in message
news:1170238336.716966.152710@.p10g2000cwp.googlegroups.com...
> Hello,
> I would to create a backup file dynamically (the filename will contain
> the hostname of the DB).
> any idea how can I use a variable for that?
> (a variable is easy to use for a regular database backup, but not for
> a master key).
> I wrote on the comment the error I get.
> thanks, Yaniv
> use master;
> declare @.tmpStr varchar(200)
> set @.tmpStr = 'c:\temp\DB_service_2.key'
> -- working
> BACKUP SERVICE MASTER KEY TO FILE = 'c:\temp\DB_service.key'
> ENCRYPTION BY
> PASSWORD = 't1t1t1t1t1'
> -- error: Incorrect syntax near '@.tmpStr'
> BACKUP SERVICE MASTER KEY TO FILE = @.tmpStr ENCRYPTION BY PASSWORD => 't1t1t1t1t1'
> go
>

Tuesday, March 20, 2012

Backup Restore Fails

Hi,

I ran a backup procedure and BAK file created.
I moved the file to another server, tried to restore and failed with following error:
"Cannot open backup device 'C:\*****.bak'. Operating system error 5(Access is denied.)"

I checked the file on the original server and the file is accessible: I can see its content with RESTORE FILELISTONLY command.

Any clues?

Thanks,
Mark

Hello Mark

Are you using the Restore dialog to do this operation or are you using T-SQL?

Either case, ensure that the account under which SQL server service is running has access to the 'C' drive.

[Venkat]
This posting is provided "AS IS" with no warranties, and confers no rights.

backup restore database into different filegroup arrangement

Hi,
Is there a way to restore the backup files of a database
which has only one file and one filegroup (primay) into a
database which has multiple files and filegroups? and can
the reverse to be done (multiple files into one file)?
e.g.:
db1, db1_data on primary
restore db1 backup file into
db2 (db2_data1, primary
db2_data2, secondary)
Is there a direct way to use 'backup-restore' method?
We basically want the same data but we want to re-arrange
the file management of the database.
many thanks!
JJ
I'm afraid not. A backup is essentially an image of the database, which is why you need the same amount of
files with each at least the same size and logical filename as when the backup was performed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:1569601c415fc$b972b1e0$a401280a@.phx.gbl...
> Hi,
> Is there a way to restore the backup files of a database
> which has only one file and one filegroup (primay) into a
> database which has multiple files and filegroups? and can
> the reverse to be done (multiple files into one file)?
> e.g.:
> db1, db1_data on primary
> restore db1 backup file into
> db2 (db2_data1, primary
> db2_data2, secondary)
> Is there a direct way to use 'backup-restore' method?
> We basically want the same data but we want to re-arrange
> the file management of the database.
> many thanks!
> JJ
>
>
>
|||thanks for the feedback, Tibor.
I will try it out, but I'm pretty sure that it can't be
done like you said. :-)
JJ
>--Original Message--
>I'm afraid not. A backup is essentially an image of the
database, which is why you need the same amount of
>files with each at least the same size and logical
filename as when the backup was performed.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1569601c415fc$b972b1e0$a401280a@.phx.gbl...
a
can
arrange
>
>.
>

Backup report file (.rdl)

Can I backup report files (.rdl) from reporting services server? and how to
do it?
Because my disk is broken, the report project also lost.
-_-...
thankshttp://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.getreportdefinition(SQL.90).aspx|||You can use Reporting Services Scripter to extract all the reports from your
Report Server.
Download here http://www.sqldbatips.com/showarticle.asp?ID=62
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"HC" <mcmssupereditor@.yahoo.com.hk> wrote in message
news:%23a60VIWWGHA.3848@.TK2MSFTNGP05.phx.gbl...
> Can I backup report files (.rdl) from reporting services server? and how
> to do it?
> Because my disk is broken, the report project also lost.
> -_-...
> thanks
>sql

backup question

Using SS2000 SP4. Is there any way to back up to a .bak file just selected
tables from a database?
Thanks,
--
Dan D.No. If you have multiple filegroups and you segregate the tables you want
into a specific file group you can backup just that filegroup.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:954CD167-72FA-49EB-BCC8-6EAFFEF1EA0F@.microsoft.com...
> Using SS2000 SP4. Is there any way to back up to a .bak file just selected
> tables from a database?
> Thanks,
> --
> Dan D.|||I don't have multiple filegroups. Thanks.
--
Dan D.
"Andrew J. Kelly" wrote:
> No. If you have multiple filegroups and you segregate the tables you want
> into a specific file group you can backup just that filegroup.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:954CD167-72FA-49EB-BCC8-6EAFFEF1EA0F@.microsoft.com...
> > Using SS2000 SP4. Is there any way to back up to a .bak file just selected
> > tables from a database?
> >
> > Thanks,
> > --
> > Dan D.
>|||Sorry - there is not.
One strategy that you might wish to employ is to put these selected tables
in their own filegroup. You can then do a filegroup backup of just that one
group. Another option - you could still separate out your tables into their
own filegroup and stop SQL Server, copy the files associated with that
filegroup, and restart. This is not the best way to help availability. ;-)
Another option would be to replicate those tables to another DB and backup
that DB.
It depends on which approach will work best for you.
Rick Heiges
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:954CD167-72FA-49EB-BCC8-6EAFFEF1EA0F@.microsoft.com...
> Using SS2000 SP4. Is there any way to back up to a .bak file just selected
> tables from a database?
> Thanks,
> --
> Dan D.|||Thanks for the suggestions Rick.
--
Dan D.
"Rick Heiges" wrote:
> Sorry - there is not.
> One strategy that you might wish to employ is to put these selected tables
> in their own filegroup. You can then do a filegroup backup of just that one
> group. Another option - you could still separate out your tables into their
> own filegroup and stop SQL Server, copy the files associated with that
> filegroup, and restart. This is not the best way to help availability. ;-)
> Another option would be to replicate those tables to another DB and backup
> that DB.
> It depends on which approach will work best for you.
> Rick Heiges
> SQL Server MVP
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:954CD167-72FA-49EB-BCC8-6EAFFEF1EA0F@.microsoft.com...
> > Using SS2000 SP4. Is there any way to back up to a .bak file just selected
> > tables from a database?
> >
> > Thanks,
> > --
> > Dan D.
>
>

Monday, March 19, 2012

backup question

sql server 2000 sp3.
I would like to backup a database frequently, maybe every 1/2 hour. I would
like to backup to a file. Can a create a backup device (file) on a network
server so the backup goes directly to another server? Or do I have to backup
to the lcoal hard drive and schedule a script to copy it off the drive?
any info is appreciated. thanks.What you can do is map a network drive on your server, then you can
create a backup device on that mapped drive
--
Eric Li
SQL DBA
MCDBA
djc wrote:
> sql server 2000 sp3.
> I would like to backup a database frequently, maybe every 1/2 hour. I would
> like to backup to a file. Can a create a backup device (file) on a network
> server so the backup goes directly to another server? Or do I have to backup
> to the lcoal hard drive and schedule a script to copy it off the drive?
> any info is appreciated. thanks.
>|||If the service account for the SQL Server service has permissions on the drive, then yes, you can use a
network drive. Address it using UNC naming.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"djc" <noone@.nowhere.com> wrote in message news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...
> sql server 2000 sp3.
> I would like to backup a database frequently, maybe every 1/2 hour. I would
> like to backup to a file. Can a create a backup device (file) on a network
> server so the backup goes directly to another server? Or do I have to backup
> to the lcoal hard drive and schedule a script to copy it off the drive?
> any info is appreciated. thanks.
>|||Hi,
Do not perform the FULL database backup every 30 minutes. This will create
issue once your
database grow big. I recommend you to do a transaction log backup every 30
minutes to a remote server and
perform the FULL database backup once every night.
Prerequsites to do a backup remotely
---
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Transaction log backup
--
BACKUP log <dbname> to disk='\\computername\sharename\dbname_tran1.trn'
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
Thanks
Hari
MCDBA
"djc" <noone@.nowhere.com> wrote in message
news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...
> sql server 2000 sp3.
> I would like to backup a database frequently, maybe every 1/2 hour. I
would
> like to backup to a file. Can a create a backup device (file) on a network
> server so the backup goes directly to another server? Or do I have to
backup
> to the lcoal hard drive and schedule a script to copy it off the drive?
> any info is appreciated. thanks.
>|||thats what I was thinking. Will I need to log on as the SQLServer Service
account and map the drive? In other words, I was concerned that if I map the
drive under my account it would not exist under the context of the scheduled
task?
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:eB2F$XxUEHA.384@.TK2MSFTNGP10.phx.gbl...
> What you can do is map a network drive on your server, then you can
> create a backup device on that mapped drive
> --
> Eric Li
> SQL DBA
> MCDBA
> djc wrote:
> > sql server 2000 sp3.
> >
> > I would like to backup a database frequently, maybe every 1/2 hour. I
would
> > like to backup to a file. Can a create a backup device (file) on a
network
> > server so the backup goes directly to another server? Or do I have to
backup
> > to the lcoal hard drive and schedule a script to copy it off the drive?
> >
> > any info is appreciated. thanks.
> >
> >|||thanks for the reply.
can I do this through GUI Enterprise manager? (under database, management,
backup) I am not very familiar with TSQL at this point.
I think I tried to create a new backup device using a UNC name and it would
not let me? (which leads me to believe what your refering to has to be done
using TSQL)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6BUZaxUEHA.644@.tk2msftngp13.phx.gbl...
> If the service account for the SQL Server service has permissions on the
drive, then yes, you can use a
> network drive. Address it using UNC naming.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "djc" <noone@.nowhere.com> wrote in message
news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > sql server 2000 sp3.
> >
> > I would like to backup a database frequently, maybe every 1/2 hour. I
would
> > like to backup to a file. Can a create a backup device (file) on a
network
> > server so the backup goes directly to another server? Or do I have to
backup
> > to the lcoal hard drive and schedule a script to copy it off the drive?
> >
> > any info is appreciated. thanks.
> >
> >
>|||thanks for the reply. Is using TSQL the only way to accomplish this? or
could I use enterprise manager gui. I'm not that familiar with TSQL at this
point.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OfzbFf4UEHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Do not perform the FULL database backup every 30 minutes. This will create
> issue once your
> database grow big. I recommend you to do a transaction log backup every 30
> minutes to a remote server and
> perform the FULL database backup once every night.
> Prerequsites to do a backup remotely
> ---
> There are Few Pre requisites to do backup remotely;
> 1. You Should start SQL server using Domain user who got access to remote
> machine Share
> 2. Should have share in the remote machine
> 3. If you need to schedule this as a job then SQL Agent should use the
same
> Domain user in which SQL server was started
> 4. Restart the services
> Now you can execute the Backup script with UNC path
> BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak'
with
> init
> Transaction log backup
> --
> BACKUP log <dbname> to disk='\\computername\sharename\dbname_tran1.trn'
> Note:
> Backup to remote machine will not work if you start SQL server using Local
> system account
> Thanks
> Hari
> MCDBA
>
>
> "djc" <noone@.nowhere.com> wrote in message
> news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > sql server 2000 sp3.
> >
> > I would like to backup a database frequently, maybe every 1/2 hour. I
> would
> > like to backup to a file. Can a create a backup device (file) on a
network
> > server so the backup goes directly to another server? Or do I have to
> backup
> > to the lcoal hard drive and schedule a script to copy it off the drive?
> >
> > any info is appreciated. thanks.
> >
> >
>|||Yep, you can create the backup device using EM (I just tried it), you just have to type the full UNC name, and
ignore the warning that EM cannot confirm that you have access to the file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"djc" <noone@.nowhere.com> wrote in message news:u1np1n5UEHA.1356@.TK2MSFTNGP09.phx.gbl...
> thanks for the reply.
> can I do this through GUI Enterprise manager? (under database, management,
> backup) I am not very familiar with TSQL at this point.
> I think I tried to create a new backup device using a UNC name and it would
> not let me? (which leads me to believe what your refering to has to be done
> using TSQL)
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u6BUZaxUEHA.644@.tk2msftngp13.phx.gbl...
> > If the service account for the SQL Server service has permissions on the
> drive, then yes, you can use a
> > network drive. Address it using UNC naming.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "djc" <noone@.nowhere.com> wrote in message
> news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > > sql server 2000 sp3.
> > >
> > > I would like to backup a database frequently, maybe every 1/2 hour. I
> would
> > > like to backup to a file. Can a create a backup device (file) on a
> network
> > > server so the backup goes directly to another server? Or do I have to
> backup
> > > to the lcoal hard drive and schedule a script to copy it off the drive?
> > >
> > > any info is appreciated. thanks.
> > >
> > >
> >
> >
>|||I should have added that your problem can very well be permissions problem. Check out the service account for
SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:ef5Wi15UEHA.544@.TK2MSFTNGP11.phx.gbl...
> Yep, you can create the backup device using EM (I just tried it), you just have to type the full UNC name,
and
> ignore the warning that EM cannot confirm that you have access to the file.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "djc" <noone@.nowhere.com> wrote in message news:u1np1n5UEHA.1356@.TK2MSFTNGP09.phx.gbl...
> > thanks for the reply.
> >
> > can I do this through GUI Enterprise manager? (under database, management,
> > backup) I am not very familiar with TSQL at this point.
> >
> > I think I tried to create a new backup device using a UNC name and it would
> > not let me? (which leads me to believe what your refering to has to be done
> > using TSQL)
> >
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> > message news:u6BUZaxUEHA.644@.tk2msftngp13.phx.gbl...
> > > If the service account for the SQL Server service has permissions on the
> > drive, then yes, you can use a
> > > network drive. Address it using UNC naming.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "djc" <noone@.nowhere.com> wrote in message
> > news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > > > sql server 2000 sp3.
> > > >
> > > > I would like to backup a database frequently, maybe every 1/2 hour. I
> > would
> > > > like to backup to a file. Can a create a backup device (file) on a
> > network
> > > > server so the backup goes directly to another server? Or do I have to
> > backup
> > > > to the lcoal hard drive and schedule a script to copy it off the drive?
> > > >
> > > > any info is appreciated. thanks.
> > > >
> > > >
> > >
> > >
> >
> >
>|||ok. Great. I had only very quickly given it one attempt before... and I had
an administrative share in the path (\\server\d$ ...etc...) so maybe that
had something to do with it as well. I will also setup the appropriate
permissions for the sql service account... Thanks for the info!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ef5Wi15UEHA.544@.TK2MSFTNGP11.phx.gbl...
> Yep, you can create the backup device using EM (I just tried it), you just
have to type the full UNC name, and
> ignore the warning that EM cannot confirm that you have access to the
file.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "djc" <noone@.nowhere.com> wrote in message
news:u1np1n5UEHA.1356@.TK2MSFTNGP09.phx.gbl...
> > thanks for the reply.
> >
> > can I do this through GUI Enterprise manager? (under database,
management,
> > backup) I am not very familiar with TSQL at this point.
> >
> > I think I tried to create a new backup device using a UNC name and it
would
> > not let me? (which leads me to believe what your refering to has to be
done
> > using TSQL)
> >
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:u6BUZaxUEHA.644@.tk2msftngp13.phx.gbl...
> > > If the service account for the SQL Server service has permissions on
the
> > drive, then yes, you can use a
> > > network drive. Address it using UNC naming.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "djc" <noone@.nowhere.com> wrote in message
> > news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > > > sql server 2000 sp3.
> > > >
> > > > I would like to backup a database frequently, maybe every 1/2 hour.
I
> > would
> > > > like to backup to a file. Can a create a backup device (file) on a
> > network
> > > > server so the backup goes directly to another server? Or do I have
to
> > backup
> > > > to the lcoal hard drive and schedule a script to copy it off the
drive?
> > > >
> > > > any info is appreciated. thanks.
> > > >
> > > >
> > >
> > >
> >
> >
>

backup question

backup problems error 3202

Hello
We are currently having problems backing in a full backup into maintenance
plan all days, we have this error in log file:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC SQL
Server Driver][SQL Server] write failure in
'E:\backupsql\ECASH_db_200507120652.BAK', state = 121
When I try that in the morning, ( executed manually) the result is ok
Any help would be appreciated.Hi,
THis type of issue come when there is some hardware issues (RAID). Can you
ask your hardware vendor to run the diagonostic checks
to verify if there is any issue.
Thanks
Hari
SQL Server MVP
"santi" <santi@.discussions.microsoft.com> wrote in message
news:811568D0-F68E-4780-9E57-2661AEEC1FA4@.microsoft.com...
> Hello
> We are currently having problems backing in a full backup into maintenance
> plan all days, we have this error in log file:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server] write failure in
> 'E:\backupsql\ECASH_db_200507120652.BAK', state = 121
> When I try that in the morning, ( executed manually) the result is ok
> Any help would be appreciated.
>|||Hi
I think this answer is in the correct way, because we have in our server a
RAID system. I will try contact with hardware vendor for disk test, but I
don,t understand why in automatic mode we have error and in manual mode we
don,t have error. Do you understand?
Thank you
"Hari Prasad" escribiÃ³:
> Hi,
> THis type of issue come when there is some hardware issues (RAID). Can you
> ask your hardware vendor to run the diagonostic checks
> to verify if there is any issue.
> Thanks
> Hari
> SQL Server MVP
>
> "santi" <santi@.discussions.microsoft.com> wrote in message
> news:811568D0-F68E-4780-9E57-2661AEEC1FA4@.microsoft.com...
> > Hello
> >
> > We are currently having problems backing in a full backup into maintenance
> > plan all days, we have this error in log file:
> >
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC
> > SQL
> > Server Driver][SQL Server] write failure in
> > 'E:\backupsql\ECASH_db_200507120652.BAK', state = 121
> >
> > When I try that in the morning, ( executed manually) the result is ok
> >
> > Any help would be appreciated.
> >
>
>

Sunday, March 11, 2012

backup problems

Hi all,
I have a database on SQL Server 2000, and I back it up from time to time.
At one point, I moved the location of the backup file. Since then, every
time I want to do a backup, I get the old location;
I add the new one, remove the old one, do the backup (successfully), but
next time I still get the old one.
How can I fix this problem?
Thanks
AdrianUse T-SQL
BACKUP DATABASE foo TO DISK = 'c:\foo.bak' WITH INIT
You will need to replace "foo" with the name of the database that you want
to back up
You will also want to replace "c:\" with a physical path on your database
server. That will be the location of your backup file.
--
Keith
"Adrian Sandor" <aditsu@.yahoo.com> wrote in message
news:e%23gL201SFHA.2756@.tk2msftngp13.phx.gbl...
> Hi all,
> I have a database on SQL Server 2000, and I back it up from time to time.
> At one point, I moved the location of the backup file. Since then, every
> time I want to do a backup, I get the old location;
> I add the new one, remove the old one, do the backup (successfully), but
> next time I still get the old one.
> How can I fix this problem?
> Thanks
> Adrian
>|||"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OePMgJ2SFHA.2096@.TK2MSFTNGP14.phx.gbl...
> Use T-SQL
> BACKUP DATABASE foo TO DISK = 'c:\foo.bak' WITH INIT
Hmm, I'd rather use Enterprise Manager; besides, I'm thinking of scheduling
an automatic backup. Or do you still suggest using T-SQL for that?
Adrian|||You can schedule the appropriate sql statement to run within the jobs node
under the management folder within Enterprise Manager.
--
Keith
"Adrian Sandor" <aditsu@.yahoo.com> wrote in message
news:eQhVGM2SFHA.2432@.TK2MSFTNGP12.phx.gbl...
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:OePMgJ2SFHA.2096@.TK2MSFTNGP14.phx.gbl...
>> Use T-SQL
>> BACKUP DATABASE foo TO DISK = 'c:\foo.bak' WITH INIT
> Hmm, I'd rather use Enterprise Manager; besides, I'm thinking of
> scheduling an automatic backup. Or do you still suggest using T-SQL for
> that?
> Adrian
>|||"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eEubVV2SFHA.1044@.TK2MSFTNGP10.phx.gbl...
> You can schedule the appropriate sql statement to run within the jobs node
> under the management folder within Enterprise Manager.
Thanks, I will look into that.
But even if I am able to do everything I need, I'm still wondering if there
is a solution to that unpleasant behavior described in my first message.
Where does Sql Server store the information about backup destinations, and
how can I change it?
Adrian|||Hi
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
Key: BackupDirectory
Adjust the path if you are using a named instance.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Adrian Sandor" <aditsu@.yahoo.com> wrote in message
news:%23ZohBo2SFHA.264@.tk2msftngp13.phx.gbl...
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eEubVV2SFHA.1044@.TK2MSFTNGP10.phx.gbl...
>> You can schedule the appropriate sql statement to run within the jobs
>> node under the management folder within Enterprise Manager.
> Thanks, I will look into that.
> But even if I am able to do everything I need, I'm still wondering if
> there is a solution to that unpleasant behavior described in my first
> message. Where does Sql Server store the information about backup
> destinations, and how can I change it?
> Adrian
>|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uUBaG24SFHA.2424@.TK2MSFTNGP09.phx.gbl...
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
> Key: BackupDirectory
The path I found there is neither the old one nor the new one, but it's the
"Program Files\Microsoft SQL Server\MSSQL\BACKUP" path (which I never used
for making backups). I'm not sure what it is for, but it's quite clear it's
not what I need.
To be clearer, I used to back up that database to the file
"D:\db_backup\business" and then I moved it to "D:\backups\sql
server\business". I keep getting the "D:\db_backup\business" path when I try
to make a backup from Ent. Manager.
"business" is a file with no extension, not a folder.
Adrian|||Perhaps EM reads backup history tables? You could check out sp_delete_backuphistory.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian Sandor" <aditsu@.yahoo.com> wrote in message news:u3AbCW8SFHA.3636@.TK2MSFTNGP14.phx.gbl...
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:uUBaG24SFHA.2424@.TK2MSFTNGP09.phx.gbl...
>> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
>> Key: BackupDirectory
> The path I found there is neither the old one nor the new one, but it's the "Program
> Files\Microsoft SQL Server\MSSQL\BACKUP" path (which I never used for making backups). I'm not
> sure what it is for, but it's quite clear it's not what I need.
> To be clearer, I used to back up that database to the file "D:\db_backup\business" and then I
> moved it to "D:\backups\sql server\business". I keep getting the "D:\db_backup\business" path when
> I try to make a backup from Ent. Manager.
> "business" is a file with no extension, not a folder.
> Adrian
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e6DLyZ8SFHA.560@.TK2MSFTNGP10.phx.gbl...
> Perhaps EM reads backup history tables? You could check out
> sp_delete_backuphistory.
Starting from your suggestion and digging further, I found the old paths in
the backupmediafamily table in the msdb database.
The field name is physical_device_name.
I wonder if it is safe to change the paths there. Or is there a stored
procedure for that?
Thanks
Adrian|||I wouldn't hack this. The question is why EM connects a database with this backupmodeiafamily? I
don't know as I don't use EM for things like these. I thought that EM suggests a backup device which
was the *last one* used for that database...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian Sandor" <aditsu@.yahoo.com> wrote in message news:%23034Fy8SFHA.3464@.tk2msftngp13.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e6DLyZ8SFHA.560@.TK2MSFTNGP10.phx.gbl...
>> Perhaps EM reads backup history tables? You could check out sp_delete_backuphistory.
> Starting from your suggestion and digging further, I found the old paths in the backupmediafamily
> table in the msdb database.
> The field name is physical_device_name.
> I wonder if it is safe to change the paths there. Or is there a stored procedure for that?
> Thanks
> Adrian
>|||Could it be that you are backing up to a Backup Device that you have created
in EM? As Tibor, I'm not an expert on how EM does this since I'm not using
it either, but when you look at the Backup Device properties in EM, it has a
"physical location" and it might be this that are causing the issue. I think
that you'll have to drop this backup device and then re-create it to get the
right location back. You might also be able to change the path in the MSDB
database, but I don't know if it's as simple as that.
Regards
Steen
Adrian Sandor wrote:
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote in message news:e6DLyZ8SFHA.560@.TK2MSFTNGP10.phx.gbl...
>> Perhaps EM reads backup history tables? You could check out
>> sp_delete_backuphistory.
> Starting from your suggestion and digging further, I found the old
> paths in the backupmediafamily table in the msdb database.
> The field name is physical_device_name.
> I wonder if it is safe to change the paths there. Or is there a stored
> procedure for that?
> Thanks
> Adrian|||"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uRt6zPITFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Could it be that you are backing up to a Backup Device that you have
> created in EM?
Yes, most likely.
I don't think I've ever used any other way to create a backup.
> As Tibor, I'm not an expert on how EM does this since I'm not using it
> either,
Is it really so unusual/uncivilized to make backups in EM?
> but when you look at the Backup Device properties in EM, it has a
> "physical location" and it might be this that are causing the issue.
I think I didn't use a "Backup Device", but just a "File Name" (I get the
choice when I add a new backup destination)
> that you'll have to drop this backup device and then re-create it to get
> the
> right location back.
Hm, creating a new file might help, I will try that.
> You might also be able to change the path in the MSDB database, but I
> don't know if it's as simple as that.
Oh.. then maybe I should keep it as a last resort.
Thanks
Adrian|||Oh, I forgot to report my progress
>> that you'll have to drop this backup device and then re-create it to get
>> the right location back.
> Hm, creating a new file might help, I will try that.
Well, that helped. Apparently, when creating a backup in a new path, the
file must not already exist.
Adrian

backup problems

Hi all,
I have a database on SQL Server 2000, and I back it up from time to time.
At one point, I moved the location of the backup file. Since then, every
time I want to do a backup, I get the old location;
I add the new one, remove the old one, do the backup (successfully), but
next time I still get the old one.
How can I fix this problem?
Thanks
AdrianUse T-SQL
BACKUP DATABASE foo TO DISK = 'c:\foo.bak' WITH INIT
You will need to replace "foo" with the name of the database that you want
to back up
You will also want to replace "c:\" with a physical path on your database
server. That will be the location of your backup file.
Keith
"Adrian Sandor" <aditsu@.yahoo.com> wrote in message
news:e%23gL201SFHA.2756@.tk2msftngp13.phx.gbl...
> Hi all,
> I have a database on SQL Server 2000, and I back it up from time to time.
> At one point, I moved the location of the backup file. Since then, every
> time I want to do a backup, I get the old location;
> I add the new one, remove the old one, do the backup (successfully), but
> next time I still get the old one.
> How can I fix this problem?
> Thanks
> Adrian
>|||"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OePMgJ2SFHA.2096@.TK2MSFTNGP14.phx.gbl...
> Use T-SQL
> BACKUP DATABASE foo TO DISK = 'c:\foo.bak' WITH INIT
Hmm, I'd rather use Enterprise Manager; besides, I'm thinking of scheduling
an automatic backup. Or do you still suggest using T-SQL for that?
Adrian|||You can schedule the appropriate sql statement to run within the jobs node
under the management folder within Enterprise Manager.
Keith
"Adrian Sandor" <aditsu@.yahoo.com> wrote in message
news:eQhVGM2SFHA.2432@.TK2MSFTNGP12.phx.gbl...
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:OePMgJ2SFHA.2096@.TK2MSFTNGP14.phx.gbl...
> Hmm, I'd rather use Enterprise Manager; besides, I'm thinking of
> scheduling an automatic backup. Or do you still suggest using T-SQL for
> that?
> Adrian
>|||"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eEubVV2SFHA.1044@.TK2MSFTNGP10.phx.gbl...
> You can schedule the appropriate sql statement to run within the jobs node
> under the management folder within Enterprise Manager.
Thanks, I will look into that.
But even if I am able to do everything I need, I'm still wondering if there
is a solution to that unpleasant behavior described in my first message.
Where does Sql Server store the information about backup destinations, and
how can I change it?
Adrian|||Hi
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer
Key: BackupDirectory
Adjust the path if you are using a named instance.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Adrian Sandor" <aditsu@.yahoo.com> wrote in message
news:%23ZohBo2SFHA.264@.tk2msftngp13.phx.gbl...
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eEubVV2SFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Thanks, I will look into that.
> But even if I am able to do everything I need, I'm still wondering if
> there is a solution to that unpleasant behavior described in my first
> message. Where does Sql Server store the information about backup
> destinations, and how can I change it?
> Adrian
>|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uUBaG24SFHA.2424@.TK2MSFTNGP09.phx.gbl...
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer
> Key: BackupDirectory
The path I found there is neither the old one nor the new one, but it's the
"Program Files\Microsoft SQL Server\MSSQL\BACKUP" path (which I never used
for making backups). I'm not sure what it is for, but it's quite clear it's
not what I need.
To be clearer, I used to back up that database to the file
"D:\db_backup\business" and then I moved it to "D:\backups\sql
server\business". I keep getting the "D:\db_backup\business" path when I try
to make a backup from Ent. Manager.
"business" is a file with no extension, not a folder.
Adrian|||Perhaps EM reads backup history tables? You could check out sp_delete_backup
history.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian Sandor" <aditsu@.yahoo.com> wrote in message news:u3AbCW8SFHA.3636@.TK2MSFTNGP14.phx.g
bl...
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:uUBaG24SFHA.2424@.TK2MSFTNGP09.phx.gbl...
> The path I found there is neither the old one nor the new one, but it's th
e "Program
> Files\Microsoft SQL Server\MSSQL\BACKUP" path (which I never used for maki
ng backups). I'm not
> sure what it is for, but it's quite clear it's not what I need.
> To be clearer, I used to back up that database to the file "D:\db_backup\b
usiness" and then I
> moved it to "D:\backups\sql server\business". I keep getting the "D:\db_ba
ckup\business" path when
> I try to make a backup from Ent. Manager.
> "business" is a file with no extension, not a folder.
> Adrian
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e6DLyZ8SFHA.560@.TK2MSFTNGP10.phx.gbl...
> Perhaps EM reads backup history tables? You could check out
> sp_delete_backuphistory.
Starting from your suggestion and digging further, I found the old paths in
the backupmediafamily table in the msdb database.
The field name is physical_device_name.
I wonder if it is safe to change the paths there. Or is there a stored
procedure for that?
Thanks
Adrian|||I wouldn't hack this. The question is why EM connects a database with this b
ackupmodeiafamily? I
don't know as I don't use EM for things like these. I thought that EM sugges
ts a backup device which
was the *last one* used for that database...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian Sandor" <aditsu@.yahoo.com> wrote in message news:%23034Fy8SFHA.3464@.tk2msftngp13.phx
.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:e6DLyZ8SFHA.560@.TK2MSFTNGP10.phx.gbl...
> Starting from your suggestion and digging further, I found the old paths i
n the backupmediafamily
> table in the msdb database.
> The field name is physical_device_name.
> I wonder if it is safe to change the paths there. Or is there a stored pro
cedure for that?
> Thanks
> Adrian
>

Tuesday, March 27, 2012

Backup SQL Tables

backup SQL server databases

backup SQL server databases

backup SQL server databases

Backup SQL Database in asp.net

Sunday, March 25, 2012

backup size and initial database size

backup size and initial database size

Thursday, March 22, 2012

BACKUP SERVICE MASTER KEY TO FILE

BACKUP SERVICE MASTER KEY TO FILE

Tuesday, March 20, 2012

Backup Restore Fails

backup restore database into different filegroup arrangement

Backup report file (.rdl)

backup question

Monday, March 19, 2012

backup question

backup question

backup question

backup problems error 3202

Sunday, March 11, 2012

backup problems

backup problems

backup problems

sql backup files

Blog Archive

About Me