Sql Backing up Tables: back

Showing posts with label back. Show all posts

Sunday, March 25, 2012

Backup everything except data

We have a SQL2000 database that is created via a dump from a third party
product. Being as this dump runs every day, we don't need to back up the
data. However we need the schema, views, triggers, etc. Everything except
the actual data in the tables. Is there a way to make a backup of this?
Thanks!
BrianYes, use some tool that generates DDL script for the database:
http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Brian" <me@.here.there> wrote in message news:O%2323gnK8HHA.484@.TK2MSFTNGP06.phx.gbl...
> We have a SQL2000 database that is created via a dump from a third party product. Being as this
> dump runs every day, we don't need to back up the data. However we need the schema, views,
> triggers, etc. Everything except the actual data in the tables. Is there a way to make a backup of
> this?
> Thanks!
> Brian
>

Backup Error

I got an error with a back up maintenance plan: BACKUP failed to
complete the command BACKUP LOG [EAGLE] TO DISK = N'\
\Eaglent1\backuponeaglent1\backup\EAGLE LOG BACKUP' WITH NOINIT ,
NOUNLOAD , NAME = N'EAGLE LOG BACKUP', NOSKIP , STATS = 10,
DESCRIPTION = N'EAGLE LOG BACKUP', NOFORMAT. I also see this error
along with it: The backup data in '\\Eaglent1\backuponeaglent1\backup
\EAGLE LOG BACKUP' is incorrectly formatted. Backups cannot be
appended, but existing backup sets may still be usable.
What do I need to change to get this backup completed?
Joanne Mahoney
SDN Consultants
Jacksonville, FLMy guess is that your file EAGLE LOG BACKUP already exists, and it is not a
sql backup file, and you are appending to it.
If so, taking out any one of those, you should be good.
Quentin
"Joanne M." <joanne.e.mahoney@.gmail.com> wrote in message
news:1190401872.060395.60460@.w3g2000hsg.googlegroups.com...
>I got an error with a back up maintenance plan: BACKUP failed to
> complete the command BACKUP LOG [EAGLE] TO DISK = N'\
> \Eaglent1\backuponeaglent1\backup\EAGLE LOG BACKUP' WITH NOINIT ,
> NOUNLOAD , NAME = N'EAGLE LOG BACKUP', NOSKIP , STATS = 10,
> DESCRIPTION = N'EAGLE LOG BACKUP', NOFORMAT. I also see this error
> along with it: The backup data in '\\Eaglent1\backuponeaglent1\backup
> \EAGLE LOG BACKUP' is incorrectly formatted. Backups cannot be
> appended, but existing backup sets may still be usable.
> What do I need to change to get this backup completed?
> Joanne Mahoney
> SDN Consultants
> Jacksonville, FL
>|||Sorry for my ignorance, but what do you mean by that? Where should I
look? BTW, this is sql 2000
Joanne
On Sep 21, 3:17 pm, "Quentin Ran" <remove_qr...@.yahoo.com> wrote:
> My guess is that your file EAGLE LOG BACKUP already exists, and it is not a
> sql backup file, and you are appending to it.
> If so, taking out any one of those, you should be good.
> Quentin
> "Joanne M." <joanne.e.maho...@.gmail.com> wrote in message
> news:1190401872.060395.60460@.w3g2000hsg.googlegroups.com...
>
> >I got an error with a back up maintenance plan: BACKUP failed to
> > complete the command BACKUP LOG [EAGLE] TO DISK = N'\
> > \Eaglent1\backuponeaglent1\backup\EAGLE LOG BACKUP' WITH NOINIT ,
> > NOUNLOAD , NAME = N'EAGLE LOG BACKUP', NOSKIP , STATS = 10,
> > DESCRIPTION = N'EAGLE LOG BACKUP', NOFORMAT. I also see this error
> > along with it: The backup data in '\\Eaglent1\backuponeaglent1\backup
> > \EAGLE LOG BACKUP' is incorrectly formatted. Backups cannot be
> > appended, but existing backup sets may still be usable.
> > What do I need to change to get this backup completed?
> > Joanne Mahoney
> > SDN Consultants
> > Jacksonville, FL- Hide quoted text -
> - Show quoted text -|||The most simple: if the file is not important (or copy it to somewhere if it
is), delete it and then try again.
"Joanne M." <joanne.e.mahoney@.gmail.com> wrote in message
news:1190402838.559703.52750@.y42g2000hsy.googlegroups.com...
> Sorry for my ignorance, but what do you mean by that? Where should I
> look? BTW, this is sql 2000
> Joanne
>
> On Sep 21, 3:17 pm, "Quentin Ran" <remove_qr...@.yahoo.com> wrote:
>> My guess is that your file EAGLE LOG BACKUP already exists, and it is not
>> a
>> sql backup file, and you are appending to it.
>> If so, taking out any one of those, you should be good.
>> Quentin
>> "Joanne M." <joanne.e.maho...@.gmail.com> wrote in message
>> news:1190401872.060395.60460@.w3g2000hsg.googlegroups.com...
>>
>> >I got an error with a back up maintenance plan: BACKUP failed to
>> > complete the command BACKUP LOG [EAGLE] TO DISK = N'\
>> > \Eaglent1\backuponeaglent1\backup\EAGLE LOG BACKUP' WITH NOINIT ,
>> > NOUNLOAD , NAME = N'EAGLE LOG BACKUP', NOSKIP , STATS = 10,
>> > DESCRIPTION = N'EAGLE LOG BACKUP', NOFORMAT. I also see this error
>> > along with it: The backup data in '\\Eaglent1\backuponeaglent1\backup
>> > \EAGLE LOG BACKUP' is incorrectly formatted. Backups cannot be
>> > appended, but existing backup sets may still be usable.
>> > What do I need to change to get this backup completed?
>> > Joanne Mahoney
>> > SDN Consultants
>> > Jacksonville, FL- Hide quoted text -
>> - Show quoted text -
>

Tuesday, March 20, 2012

Backup Databases SQL

Good morning,
I am with some questions concerning Backup of SQL Server 2000 and 2005.
I wonder what the databases should be held back, outside the base of the
system itself, the master, you should hold back up any further, for example
model or msdb?
Thank you,
Sincerely,
Ralph HaddadI'm sorry, I don't really understand the question. What do you mean by 'hel
d
back'? Are you asking if there are any DBs that you shouldn't backup?
"Ralph Haddad" wrote:

> Good morning,
> I am with some questions concerning Backup of SQL Server 2000 and 2005.
> I wonder what the databases should be held back, outside the base of the
> system itself, the master, you should hold back up any further, for exampl
e
> model or msdb?
>
> Thank you,
> Sincerely,
> Ralph Haddad
>|||Good Afternoon,
I would like to know what the database should be done Backup? For example,
need make Backup of Database Model? Or database tempdb? master?
Thank you,
Sincerely,
Ralph Haddad
--
Ralph Haddad
"Sean McCown" wrote:
[vbcol=seagreen]
> I'm sorry, I don't really understand the question. What do you mean by 'h
eld
> back'? Are you asking if there are any DBs that you shouldn't backup?
>
> "Ralph Haddad" wrote:
>|||I understand now. While you don't have to backup model unelss you've done
something special to it, I like to anyway. But you can get it from any othe
r
server.
The only DB you can't actually backup is tempdb. Any other DB should be
backed up... esp the system DBs.
"Ralph Haddad" wrote:
[vbcol=seagreen]
> Good Afternoon,
> I would like to know what the database should be done Backup? For example
,
> need make Backup of Database Model? Or database tempdb? master?
>
> Thank you,
> Sincerely,
> Ralph Haddad
> --
> Ralph Haddad
>
> "Sean McCown" wrote:
>sql

Monday, March 19, 2012

Backup databases

I'm trying to setup a back up plan for a number of databases, I initially set up one plan to include all user databases which worked fine or so I thought, when I check them a few days later I noticed that some of the databases were not appearing in the backup set, the only way I could get these to appear is to set the comp level to 90, now when we run certain applications we get an error, when I return the comp level back to 70 then the application works fine, is there a reason I can not back up any database on sql 2005 without it being a comp level 90?

Thanks inadvance

I'm guessing that the maintenance plan you've created uses some feature or syntax which didn't exist in SQL 7.0

Look over the TSQL backup commands in your maintenance plan and verify that the syntax there is compatible with SQL 7.0

|||

Hi thanks for the reply, but its not getting that far, stepping through the wizard,first it asks for the database(s) to back up, the choice being all system databases, or specific databases and I don't see any database that has not been set to comp level 90, so no tsql to check.

Thanks

|||

I see that on my system as well.

Is compatibility level 80 an option for you? Databases with that compatibility level do show up in the Wizard.

I'll check on why 70 databases are excluded, but I suspect that it has to do with what was supported at that version.

Ultimately your best option may be to write a backup script yourself.

|||

Yes thanks, not sure why I didn't think of that, but setting to comp level 80 does the trick,, thanks!

|||I have encountered the same problem when I setup the maintenance plan with the maintenance plan Wizard. Any help if I cannot set the comp. level to 80? Please advice, thanks!

Backup Database! this is really interesting!

Hi,
I run this in query analyzer
dump database MYDB to disk = 'c:\temp.dat' with init
sql server fails to back up and returns
Server: Msg 3202, Level 16, State 1, Line 1
Write on 'c:\temp.dat' failed, status = 64. See the SQL Server Error Log For
More details.
Server: Msg 3013, Level 16, State 1, Line 1
Backup Database is terminating abnormally
and these are the diagnostics and computer configurations i
performed(computerA):
1. There is 25.5 GB of free space available on C drive.
2. The computer has two RAIDSone SCSI drive. It generates same error on all
of them. This leads me to believe that this is not hardware problem.
3. It generates for all three databases on the server. so not dependent on
database either.
4. Tested on another computer with same hardware but got the databases from
computerA. It fails on that computer too.
5. tested on yet another computer with older versions of the same databases
,(not restored from computerA). It succeeds.
My thoughts are inclined towards virus or sth in the database. but here
comes the kicker. asked the services guy to send me the database backups.
Restored dbs on my machine to the backup copies. It all works fine.
So not the DBs, Not the SQL Server, not the Hardware. what is it?
Or am i totally on the wrong track?
Please someone put some suggestions. I am burning saturday to do this.
A couple things to note. One is that DUMP is the old 6.5 command and you
should be using BACKUP DATABASE instead. But I suspect the error is due to
incorrect permissions but without more details it's hard to say for sure.
Did you check the SQL Error log as it stated? The backup command is always
done under the account SQL Server is running under. So make sure that
account has write permissions on that directory and file. Make sure the file
is not locked by some other application such as a tape backup process.
Andrew J. Kelly SQL MVP
"venAdder" <venAdder@.discussions.microsoft.com> wrote in message
news:F7183062-C942-4B7C-8DD3-A0FAC0228CC8@.microsoft.com...
> Hi,
> I run this in query analyzer
> dump database MYDB to disk = 'c:\temp.dat' with init
> sql server fails to back up and returns
> Server: Msg 3202, Level 16, State 1, Line 1
> Write on 'c:\temp.dat' failed, status = 64. See the SQL Server Error Log
> For
> More details.
> Server: Msg 3013, Level 16, State 1, Line 1
> Backup Database is terminating abnormally
>
> and these are the diagnostics and computer configurations i
> performed(computerA):
> 1. There is 25.5 GB of free space available on C drive.
> 2. The computer has two RAIDSone SCSI drive. It generates same error on
> all
> of them. This leads me to believe that this is not hardware problem.
> 3. It generates for all three databases on the server. so not dependent on
> database either.
> 4. Tested on another computer with same hardware but got the databases
> from
> computerA. It fails on that computer too.
> 5. tested on yet another computer with older versions of the same
> databases
> ,(not restored from computerA). It succeeds.
> My thoughts are inclined towards virus or sth in the database. but here
> comes the kicker. asked the services guy to send me the database backups.
> Restored dbs on my machine to the backup copies. It all works fine.
> So not the DBs, Not the SQL Server, not the Hardware. what is it?
> Or am i totally on the wrong track?
> Please someone put some suggestions. I am burning saturday to do this.
|||I am sorry c:\temp.dat is wrong.
it is \\myserver\c$\temp.dat
Ok I think I know what the problem is. The path is actually
'\\myserver\c$\temp.dat'. The status = 64 means The specified network name
is no longer available ( ERROR_NETNAME_DELETED ). But the myserver is the
name of the computer on which sql server runs. I mean sql server is installed
on myserver. So it should be same as saying c:\temp.dat.
So the query becomes
dump database MYDB to disk = '\\myserver\c$\temp.dat' with init
But in case of c:\temp.dat it succeeds and in case of \\myserver\c$\temp.dat
it fails ( it used to work, but has been failing recently).
Now i tried following.
1. I am able to access c drive on myserver by typing \\myserver\c$ in
windows explorer from another computer on LAN.
2. I am able to access c drive by typing \\myserver\c$ in the windows
explorer on myserver.
3. ping 127.0.0.1 return normal results on myserver.
So why does windows generates ERROR_NETNAME_DELETED when i run
dump database MYDB to disk = '\\myserver\c$\temp.dat' with init
in query analyzer?
|||If this is duplicate sorry, for some reason i don't see my posts on the forum
Ok I think I know what the problem is. Sorry for the 'c:\temp.dat'. The path
is actually
'\\myserver\c$\temp.dat'. The status = 64 means The specified network name
is no longer available ( ERROR_NETNAME_DELETED ). But the myserver is the
name of the computer on which sql server runs. I mean sql server is installed
on myserver. So it should be same as saying c:\temp.dat.
So the query becomes
dump database MYDB to disk = '\\myserver\c$\temp.dat' with init
But in case of c:\temp.dat it succeeds and in case of \\myserver\c$\temp.dat
it fails ( it used to work, but ahs been failing recently).
Now i tried following.
1. I am able to access c drive on myserver by typing \\myserver\c$ in
windows explorer from another computer on LAN.
2. I am able to access c drive by typing \\myserver\c$ in the windows
explorer on myserver.
3. ping 127.0.0.1 return normal results on myserver.
So why does windows generates ERROR_NETNAME_DELETED when i run
dump database MYDB to disk = '\\myserver\c$\temp.dat' with init
in query analyzer?
|||I think Andrew is correct that it is a permissions issue. It works fine
locally on my machine.
Try this from Query Analyzer:
exec master..xp_cmdshell 'dir \\myserver\c$\temp.dat'
You should see the output of the dir command in the results pane.
Ron
Ron Talmage
SQL Server MVP
"venAdder" <venAdder@.discussions.microsoft.com> wrote in message
news:4AFC6143-1BC2-483C-AB63-DB4AFD398EE6@.microsoft.com...
> If this is duplicate sorry, for some reason i don't see my posts on the
forum
> Ok I think I know what the problem is. Sorry for the 'c:\temp.dat'. The
path
> is actually
> '\\myserver\c$\temp.dat'. The status = 64 means The specified network name
> is no longer available ( ERROR_NETNAME_DELETED ). But the myserver is the
> name of the computer on which sql server runs. I mean sql server is
installed
> on myserver. So it should be same as saying c:\temp.dat.
> So the query becomes
> dump database MYDB to disk = '\\myserver\c$\temp.dat' with init
> But in case of c:\temp.dat it succeeds and in case of
\\myserver\c$\temp.dat
> it fails ( it used to work, but ahs been failing recently).
> Now i tried following.
> 1. I am able to access c drive on myserver by typing \\myserver\c$ in
> windows explorer from another computer on LAN.
> 2. I am able to access c drive by typing \\myserver\c$ in the windows
> explorer on myserver.
> 3. ping 127.0.0.1 return normal results on myserver.
>
> So why does windows generates ERROR_NETNAME_DELETED when i run
> dump database MYDB to disk = '\\myserver\c$\temp.dat' with init
> in query analyzer?
>
|||I still think it is permissions. Log on to the server with the account that
SQL Server is running under and see if you can access that share.
Andrew J. Kelly SQL MVP
"venAdder" <venAdder@.discussions.microsoft.com> wrote in message
news:B3D7FA16-1D79-48BF-8D64-90319A6A9B7C@.microsoft.com...
>I am sorry c:\temp.dat is wrong.
> it is \\myserver\c$\temp.dat
> Ok I think I know what the problem is. The path is actually
> '\\myserver\c$\temp.dat'. The status = 64 means The specified network name
> is no longer available ( ERROR_NETNAME_DELETED ). But the myserver is the
> name of the computer on which sql server runs. I mean sql server is
> installed
> on myserver. So it should be same as saying c:\temp.dat.
> So the query becomes
> dump database MYDB to disk = '\\myserver\c$\temp.dat' with init
> But in case of c:\temp.dat it succeeds and in case of
> \\myserver\c$\temp.dat
> it fails ( it used to work, but has been failing recently).
> Now i tried following.
> 1. I am able to access c drive on myserver by typing \\myserver\c$ in
> windows explorer from another computer on LAN.
> 2. I am able to access c drive by typing \\myserver\c$ in the windows
> explorer on myserver.
> 3. ping 127.0.0.1 return normal results on myserver.
>
> So why does windows generates ERROR_NETNAME_DELETED when i run
> dump database MYDB to disk = '\\myserver\c$\temp.dat' with init
> in query analyzer?
>
>
|||No it's not a permissions issue. The reason is when that query executes i can
see the file in explorer for a few seconds. Then it gets deleted when query
fails. Here is what happened:
The issue turned out to be network redirector cahing related.
The precise article corressponding to this is
http://support.microsoft.com/default...EN-US;q285997.
The reason I am sure this is it because the articles describes that using
c:\ should
be fine, which is actually the case. It works fine if no unc is used. and
the windows reports this failed to write to
\device\lanmanredirector\........... it's exactly the error described in
that article.
the solution to this is here
http://support.microsoft.com/default...b;en-us;163401
Now this article explains how to turn off network redirector disabling in
winnt not in win 2000. I opened win registry. The keys are diff.
does anyone know how to do the same thing in win2000professional?
and also it's still a mystry what could ahve caused this problem on that
particular computer. The file size might be one of the causes, but I don't
think so because I have tried on computers configured in exactly the same
way(hardware and software) with much larger files and it all works fine.
|||Make sure your WORKSTATION has the Server Service and the RPC Service
running.
Sincerely,
Anthony Thomas

"venAdder" <venAdder@.discussions.microsoft.com> wrote in message
news:057130BB-486B-4092-AED3-5B24680AD800@.microsoft.com...
No it's not a permissions issue. The reason is when that query executes i
can
see the file in explorer for a few seconds. Then it gets deleted when query
fails. Here is what happened:
The issue turned out to be network redirector cahing related.
The precise article corressponding to this is
http://support.microsoft.com/default...EN-US;q285997.
The reason I am sure this is it because the articles describes that using
c:\ should
be fine, which is actually the case. It works fine if no unc is used. and
the windows reports this failed to write to
\device\lanmanredirector\........... it's exactly the error described in
that article.
the solution to this is here
http://support.microsoft.com/default...b;en-us;163401
Now this article explains how to turn off network redirector disabling in
winnt not in win 2000. I opened win registry. The keys are diff.
does anyone know how to do the same thing in win2000professional?
and also it's still a mystry what could ahve caused this problem on that
particular computer. The file size might be one of the causes, but I don't
think so because I have tried on computers configured in exactly the same
way(hardware and software) with much larger files and it all works fine.

Backup database with encrypted column data

Hi,
I have database with encrypted column data in my customer server. Sometime, I might need to backup their database back to office for troubleshooting.

How could I backup/restore master key, symmetric and asymmetric key created for my database?

Thank you

Hi,

This might help

http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/SQLEncryption.doc

Regards

|||Hi,
Thank you for reply.
I didn't see any BACKUP command mentioned in doc for asymmetric/symmeric keys. Do I need to backup sys.asymmetric_keys and sys.symmetric_keys table manually?

Thank you
|||

You don't need to backup or restor any key, to be able to use your database backup on another machine. You just need to know the password protecting the database master key. On the machine on which you restore the database, you will have to reencrypt this master key with the service master key of the database server. You can do this with the following statements:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Thanks
Laurentiu

|||Refer to books online that links up a section about BACKUP and RESTORING encryption keys http://msdn2.microsoft.com/en-US/library/ms157275.aspx link.|||

I think the keys in the new db you restored must rebuild,because the service master key maybe different.

you can't restore a db by the command simply.

Backup database to floppy

I am trying to back my SQL Server 2000 database (which contains one table) to
floppy disk.
I select SQL Server backup option and select destination backup to: A:
When I click OK, I see message that says 'backup in progrress', and then a
message box displays 'The backup operation has been completed successfully'.
At no time did my floppy drive light up and the data did not get backed up
to it.
I don't see where it got backed up to.
Does anyone know why it is not backing up to my floppy drive?
Thanks.Are you performing this operation where the Server installation was made?
SQL Server takes the folders from the machine where its installed and not
that of the client.
thanks and regards
Chandra
"Ron" wrote:
> I am trying to back my SQL Server 2000 database (which contains one table) to
> floppy disk.
> I select SQL Server backup option and select destination backup to: A:
> When I click OK, I see message that says 'backup in progrress', and then a
> message box displays 'The backup operation has been completed successfully'.
> At no time did my floppy drive light up and the data did not get backed up
> to it.
> I don't see where it got backed up to.
> Does anyone know why it is not backing up to my floppy drive?
> Thanks.|||Yes I am performing this operation where the Server installation was made.
So how do I backup to floppy?
"Chandra" wrote:
> Are you performing this operation where the Server installation was made?
> SQL Server takes the folders from the machine where its installed and not
> that of the client.
> thanks and regards
> Chandra
>
> "Ron" wrote:
> > I am trying to back my SQL Server 2000 database (which contains one table) to
> > floppy disk.
> > I select SQL Server backup option and select destination backup to: A:
> > When I click OK, I see message that says 'backup in progrress', and then a
> > message box displays 'The backup operation has been completed successfully'.
> > At no time did my floppy drive light up and the data did not get backed up
> > to it.
> > I don't see where it got backed up to.
> > Does anyone know why it is not backing up to my floppy drive?
> > Thanks.|||Just see this
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpt_1m0p.asp
thanks and regards
Chandra
"Ron" wrote:
> Yes I am performing this operation where the Server installation was made.
> So how do I backup to floppy?
>
> "Chandra" wrote:
> >
> > Are you performing this operation where the Server installation was made?
> > SQL Server takes the folders from the machine where its installed and not
> > that of the client.
> >
> > thanks and regards
> > Chandra
> >
> >
> >
> > "Ron" wrote:
> >
> > > I am trying to back my SQL Server 2000 database (which contains one table) to
> > > floppy disk.
> > > I select SQL Server backup option and select destination backup to: A:
> > > When I click OK, I see message that says 'backup in progrress', and then a
> > > message box displays 'The backup operation has been completed successfully'.
> > > At no time did my floppy drive light up and the data did not get backed up
> > > to it.
> > > I don't see where it got backed up to.
> > > Does anyone know why it is not backing up to my floppy drive?
> > > Thanks.|||Chandra
Those were the exact steps I already took and it didn't back up.
Just to try something different I just tried selecting schedule with todays
date & time and received the message
'SQL Server agent on target (local) is stopped. Make sure it is running
during scheduled execution of this job.'
My target is my floppy drive.
Any ideas whjat is wrong?
"Chandra" wrote:
> Just see this:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpt_1m0p.asp
> thanks and regards
> Chandra
> "Ron" wrote:
> > Yes I am performing this operation where the Server installation was made.
> > So how do I backup to floppy?
> >
> >
> > "Chandra" wrote:
> >
> > >
> > > Are you performing this operation where the Server installation was made?
> > > SQL Server takes the folders from the machine where its installed and not
> > > that of the client.
> > >
> > > thanks and regards
> > > Chandra
> > >
> > >
> > >
> > > "Ron" wrote:
> > >
> > > > I am trying to back my SQL Server 2000 database (which contains one table) to
> > > > floppy disk.
> > > > I select SQL Server backup option and select destination backup to: A:
> > > > When I click OK, I see message that says 'backup in progrress', and then a
> > > > message box displays 'The backup operation has been completed successfully'.
> > > > At no time did my floppy drive light up and the data did not get backed up
> > > > to it.
> > > > I don't see where it got backed up to.
> > > > Does anyone know why it is not backing up to my floppy drive?
> > > > Thanks.|||Ron,
Just back it up on a different drive and see whats the size of the backup
file. Is it lee that 1.44 MB
"Ron" wrote:
> Chandra
> Those were the exact steps I already took and it didn't back up.
> Just to try something different I just tried selecting schedule with todays
> date & time and received the message
> 'SQL Server agent on target (local) is stopped. Make sure it is running
> during scheduled execution of this job.'
> My target is my floppy drive.
> Any ideas whjat is wrong?
> "Chandra" wrote:
> >
> > Just see this:
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpt_1m0p.asp
> >
> > thanks and regards
> > Chandra
> >
> > "Ron" wrote:
> >
> > > Yes I am performing this operation where the Server installation was made.
> > > So how do I backup to floppy?
> > >
> > >
> > > "Chandra" wrote:
> > >
> > > >
> > > > Are you performing this operation where the Server installation was made?
> > > > SQL Server takes the folders from the machine where its installed and not
> > > > that of the client.
> > > >
> > > > thanks and regards
> > > > Chandra
> > > >
> > > >
> > > >
> > > > "Ron" wrote:
> > > >
> > > > > I am trying to back my SQL Server 2000 database (which contains one table) to
> > > > > floppy disk.
> > > > > I select SQL Server backup option and select destination backup to: A:
> > > > > When I click OK, I see message that says 'backup in progrress', and then a
> > > > > message box displays 'The backup operation has been completed successfully'.
> > > > > At no time did my floppy drive light up and the data did not get backed up
> > > > > to it.
> > > > > I don't see where it got backed up to.
> > > > > Does anyone know why it is not backing up to my floppy drive?
> > > > > Thanks.|||Add on to chandra, do the below steps:-
1. Backup the database to hard drive using below command (Execute from Query
Analyzer)
Backup Database <dbname> to disk = 'c:\dbname.bak' with init,stats=10
2. See the size of backup file, if the file size is less than 1.44 MB, copy
the file to floppy. If the file is greater than 1.44 MB then try zipping the
backup file.
If the file come down to < 1.44 MB copy the zip to floppy, else you cant
copy to floppy.
Thanks
Hari
SQL Server MVP
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:C14877A0-1792-4767-A995-AA5D467C734B@.microsoft.com...
> Ron,
> Just back it up on a different drive and see whats the size of the backup
> file. Is it lee that 1.44 MB
>
> "Ron" wrote:
> > Chandra
> > Those were the exact steps I already took and it didn't back up.
> > Just to try something different I just tried selecting schedule with
todays
> > date & time and received the message
> > 'SQL Server agent on target (local) is stopped. Make sure it is running
> > during scheduled execution of this job.'
> > My target is my floppy drive.
> > Any ideas whjat is wrong?
> >
> > "Chandra" wrote:
> >
> > >
> > > Just see this:
> > >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpt_1m0p.asp
> > >
> > > thanks and regards
> > > Chandra
> > >
> > > "Ron" wrote:
> > >
> > > > Yes I am performing this operation where the Server installation was
made.
> > > > So how do I backup to floppy?
> > > >
> > > >
> > > > "Chandra" wrote:
> > > >
> > > > >
> > > > > Are you performing this operation where the Server installation
was made?
> > > > > SQL Server takes the folders from the machine where its installed
and not
> > > > > that of the client.
> > > > >
> > > > > thanks and regards
> > > > > Chandra
> > > > >
> > > > >
> > > > >
> > > > > "Ron" wrote:
> > > > >
> > > > > > I am trying to back my SQL Server 2000 database (which contains
one table) to
> > > > > > floppy disk.
> > > > > > I select SQL Server backup option and select destination backup
to: A:
> > > > > > When I click OK, I see message that says 'backup in progrress',
and then a
> > > > > > message box displays 'The backup operation has been completed
successfully'.
> > > > > > At no time did my floppy drive light up and the data did not get
backed up
> > > > > > to it.
> > > > > > I don't see where it got backed up to.
> > > > > > Does anyone know why it is not backing up to my floppy drive?
> > > > > > Thanks.|||I backed up to a folder on my hard drive and it worked fine.
The file size is 915 KB. I then copied it to my floppy drive successfully.
I don't know why I can't backup directly to floppy but at least it works in
2 steps.
Thanks.
"Chandra" wrote:
> Ron,
> Just back it up on a different drive and see whats the size of the backup
> file. Is it lee that 1.44 MB
>
> "Ron" wrote:
> > Chandra
> > Those were the exact steps I already took and it didn't back up.
> > Just to try something different I just tried selecting schedule with todays
> > date & time and received the message
> > 'SQL Server agent on target (local) is stopped. Make sure it is running
> > during scheduled execution of this job.'
> > My target is my floppy drive.
> > Any ideas whjat is wrong?
> >
> > "Chandra" wrote:
> >
> > >
> > > Just see this:
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpt_1m0p.asp
> > >
> > > thanks and regards
> > > Chandra
> > >
> > > "Ron" wrote:
> > >
> > > > Yes I am performing this operation where the Server installation was made.
> > > > So how do I backup to floppy?
> > > >
> > > >
> > > > "Chandra" wrote:
> > > >
> > > > >
> > > > > Are you performing this operation where the Server installation was made?
> > > > > SQL Server takes the folders from the machine where its installed and not
> > > > > that of the client.
> > > > >
> > > > > thanks and regards
> > > > > Chandra
> > > > >
> > > > >
> > > > >
> > > > > "Ron" wrote:
> > > > >
> > > > > > I am trying to back my SQL Server 2000 database (which contains one table) to
> > > > > > floppy disk.
> > > > > > I select SQL Server backup option and select destination backup to: A:
> > > > > > When I click OK, I see message that says 'backup in progrress', and then a
> > > > > > message box displays 'The backup operation has been completed successfully'.
> > > > > > At no time did my floppy drive light up and the data did not get backed up
> > > > > > to it.
> > > > > > I don't see where it got backed up to.
> > > > > > Does anyone know why it is not backing up to my floppy drive?
> > > > > > Thanks.

Backup Database Task

Hi,

I am trying in the Back Up Database Task to set the Value of SelectedDatabases via Expression.
Is there any way?

Thanks,

Willfried

SQL Server 2005 SP 1 + Hotfix

You cannot set the SelectedDatabases property with an Expression. Try using an Execute SQL Task and an expression to set the SqlStatementSource

Backup Database Task

Hi,

I am trying in the Back Up Database Task to set the Value of SelectedDatabases via Expression.
Is there any way?

Thanks,

Willfried

SQL Server 2005 SP 1 + Hotfix

You cannot set the SelectedDatabases property with an Expression. Try using an Execute SQL Task and an expression to set the SqlStatementSource

Thursday, March 8, 2012

Backup continues to try to execute...

Greetings
I've inherited a SQL server recently at work, and in trying to clean up
the maintenance plan and back ups I've discovered that every night
there is a backup job for the transactions for two particular databases
that attempts to run, but is misconfigured and fails. Weird thing is
there isn't a backup job in Enterprise Manager. My maintenance plan,
the only maintenance plan, executes fine and backs up those particular
databases and their transaction logs fine. I'm just trying to find
this phantom backup job that is misconfigured and filling up my event
logs. Any help you can provide is appreciated.
moterSQL Server Profiler is your friend
<motert@.gmail.com> wrote in message
news:1152453296.717787.45300@.m73g2000cwd.googlegroups.com...
> Greetings
> I've inherited a SQL server recently at work, and in trying to clean up
> the maintenance plan and back ups I've discovered that every night
> there is a backup job for the transactions for two particular databases
> that attempts to run, but is misconfigured and fails. Weird thing is
> there isn't a backup job in Enterprise Manager. My maintenance plan,
> the only maintenance plan, executes fine and backs up those particular
> databases and their transaction logs fine. I'm just trying to find
> this phantom backup job that is misconfigured and filling up my event
> logs. Any help you can provide is appreciated.
> moter
>|||I looked through the Profiler, but could not find any events that
watched backups. I know the job is executing, I just can't find it in
Enterprise Manager to delete it. It doesn't display in the Backups
section and is not a part of my maintenance plan. It's a phantom
backup job from some where.
Any other ideas?
Uri Dimant wrote:
> SQL Server Profiler is your friend
>
> <motert@.gmail.com> wrote in message
> news:1152453296.717787.45300@.m73g2000cwd.googlegroups.com...
> > Greetings
> >
> > I've inherited a SQL server recently at work, and in trying to clean up
> > the maintenance plan and back ups I've discovered that every night
> > there is a backup job for the transactions for two particular databases
> > that attempts to run, but is misconfigured and fails. Weird thing is
> > there isn't a backup job in Enterprise Manager. My maintenance plan,
> > the only maintenance plan, executes fine and backs up those particular
> > databases and their transaction logs fine. I'm just trying to find
> > this phantom backup job that is misconfigured and filling up my event
> > logs. Any help you can provide is appreciated.
> >
> > moter
> >|||The backup command can be executed by anyone. Agent isn't the only scheduler on the market. Even
Windows comes with a built-in scheduler. Use Profiler to capture the BACKUP command and see what the
application name is. Might help you to narrow things down a bit.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<motert@.gmail.com> wrote in message news:1152456107.316846.79550@.75g2000cwc.googlegroups.com...
>I looked through the Profiler, but could not find any events that
> watched backups. I know the job is executing, I just can't find it in
> Enterprise Manager to delete it. It doesn't display in the Backups
> section and is not a part of my maintenance plan. It's a phantom
> backup job from some where.
> Any other ideas?
>
> Uri Dimant wrote:
>> SQL Server Profiler is your friend
>>
>> <motert@.gmail.com> wrote in message
>> news:1152453296.717787.45300@.m73g2000cwd.googlegroups.com...
>> > Greetings
>> >
>> > I've inherited a SQL server recently at work, and in trying to clean up
>> > the maintenance plan and back ups I've discovered that every night
>> > there is a backup job for the transactions for two particular databases
>> > that attempts to run, but is misconfigured and fails. Weird thing is
>> > there isn't a backup job in Enterprise Manager. My maintenance plan,
>> > the only maintenance plan, executes fine and backs up those particular
>> > databases and their transaction logs fine. I'm just trying to find
>> > this phantom backup job that is misconfigured and filling up my event
>> > logs. Any help you can provide is appreciated.
>> >
>> > moter
>> >
>|||motert@.gmail.com wrote:
> I looked through the Profiler, but could not find any events that
> watched backups. I know the job is executing, I just can't find it in
> Enterprise Manager to delete it. It doesn't display in the Backups
> section and is not a part of my maintenance plan. It's a phantom
> backup job from some where.
> Any other ideas?
>
The output of sp_who2 includes a column for the application name. If
this is running from an Agent job, it will indicate that in this field.
You could also KILL the process that is running the backup, and then see
which Agent job fails.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks to all who have responded so far. I went all the way through
the Profiler and couldn't find where to select BACKUP as an event to
trace. I'll admidt I've never used it before so any basics will help.
I was able to find this though:
"SQL Server Scheduled Job 'database backup1' - Status: Failed - Invoked
on: 2006-07-09 22:00:01 - Message: The job failed. The Job was invoked
by Schedule 13 (Schedule 1). The last step to run was step 1 (Step
1)."
Does that help narrow it down any?
Thanks again!
moter
Tracy McKibben wrote:
> motert@.gmail.com wrote:
> > I looked through the Profiler, but could not find any events that
> > watched backups. I know the job is executing, I just can't find it in
> > Enterprise Manager to delete it. It doesn't display in the Backups
> > section and is not a part of my maintenance plan. It's a phantom
> > backup job from some where.
> >
> > Any other ideas?
> >
> The output of sp_who2 includes a column for the application name. If
> this is running from an Agent job, it will indicate that in this field.
> You could also KILL the process that is running the backup, and then see
> which Agent job fails.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I found it. Typing it out led me there. Thanks everyone.
mot...@.gmail.com wrote:
> Thanks to all who have responded so far. I went all the way through
> the Profiler and couldn't find where to select BACKUP as an event to
> trace. I'll admidt I've never used it before so any basics will help.
> I was able to find this though:
> "SQL Server Scheduled Job 'database backup1' - Status: Failed - Invoked
> on: 2006-07-09 22:00:01 - Message: The job failed. The Job was invoked
> by Schedule 13 (Schedule 1). The last step to run was step 1 (Step
> 1)."
> Does that help narrow it down any?
> Thanks again!
> moter
>
> Tracy McKibben wrote:
> > motert@.gmail.com wrote:
> > > I looked through the Profiler, but could not find any events that
> > > watched backups. I know the job is executing, I just can't find it in
> > > Enterprise Manager to delete it. It doesn't display in the Backups
> > > section and is not a part of my maintenance plan. It's a phantom
> > > backup job from some where.
> > >
> > > Any other ideas?
> > >
> >
> > The output of sp_who2 includes a column for the application name. If
> > this is running from an Agent job, it will indicate that in this field.
> >
> > You could also KILL the process that is running the backup, and then see
> > which Agent job fails.
> >
> >
> > --
> > Tracy McKibben
> > MCDBA
> > http://www.realsqlguy.com|||motert@.gmail.com wrote:
> Thanks to all who have responded so far. I went all the way through
> the Profiler and couldn't find where to select BACKUP as an event to
> trace. I'll admidt I've never used it before so any basics will help.
> I was able to find this though:
> "SQL Server Scheduled Job 'database backup1' - Status: Failed - Invoked
> on: 2006-07-09 22:00:01 - Message: The job failed. The Job was invoked
> by Schedule 13 (Schedule 1). The last step to run was step 1 (Step
> 1)."
> Does that help narrow it down any?
> Thanks again!
> moter
>
That gives you the name of the job right there in the error message.
"database backup1" is the name of the Agent job...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Backup continues to try to execute...

backup and restoring

How to back up SQL 2000 and restore it ti SQL 7 vice versa?
thank youYou can take a backup from 7 and restore it to 2000 with no problems =(update the optimiser stats afterwards though). But a 2k backup cannot =be restored to 7. Easiset way for that is via DTS.
Mike John
"sitreks" <anonymous@.discussions.microsoft.com> wrote in message =news:9554F949-A533-41FC-8AC2-82449CC50F81@.microsoft.com...
> How to back up SQL 2000 and restore it ti SQL 7 vice versa?
> thank you

Backup and Restore using SQL Server with Merge Replication

We have a project where we are replicating filtered data sets to SQL Express
clients. We are thinking of a back and restore approach that uses SQL
Express. The a SQL Express Instance would receive all data thru replication
then that data set would be backed up. We are pursuing the approach because
of distance and location of the central replication server and logistics of
backing it up. Or managers have requested Microsoftâ's documented or
undocumented view on this approach. If there is any online documentation
that outlines a process like this or a document that illustrates that this
can be done and is a feasible back and restore approach, please post back.
Thanks in advance.
--
Alexander L. Wykel
MIT ProfessionalsHi Alexander,
I am not sure if I totally understand your meaning. Based on your
description, I understand that you have a project which can replicate
filtered data sets to your SQL Express clients and that you would like to
know of a backup and restore approach for your SQL Express clients so that
they can backup the data set after they receive all data through
replication function in your project. So your question here is that you
would like to know a method for backing up your data set in your SQL
Express client, right?
If I have misunderstood, please let me know.
Unfortunately there is no backup method at partial data set level in SQL
Server 2005, but only at file level or database level. Regarding this, you
can refer to BACKUP DATABASE statement in SQL Server 2005 Books Online.
At SQL Server side, I think that you may consider the following two ways:
1. Use incremental database backup for your SQL Express client.
2. Create those tables on a dedicated file and then only backup your
database on that file.
Of course if your client database is not large, you may directly perform a
full database backup. This depends on your database size and performance
requirement.
You may refer to the following articles:
Copying Databases with Backup and Restore
http://msdn2.microsoft.com/en-us/library/ms190436.aspx
Backing Up and Restoring Databases in SQL Server
http://technet.microsoft.com/en-us/library/ms187048.aspx
Strategies for Backing Up and Restoring Merge Replication
http://technet.microsoft.com/en-us/library/ms152497.aspx
Backing Up and Restoring Replicated Databases
http://technet.microsoft.com/en-us/library/ms151152.aspx
If you have any other questions or concerns, please feel free to let me
know. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
=========================================================This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================|||Sorry Charles,
My outline of the project details left al lot of unresolved issues for you
to have to piece together. The process is simple, for now forget about the
individual clients whom are receiving filtered data sets. What our primary
aim is to replicate the entire data set from the publisher to a local
Subscriber SQL Express instance. Our Corporate network is distributed and
our disaster recovery center is located off site (which we intend on
implementing via hot spare), so we would like to have local copy of the
entire data set (the data that is really important to our organization, to
use to rebuild the server while itâ's down and the hot spare is in use) not
the replication and publisher subscriber information, that we would have to
rebuild. If worst comes to worst we can rebuild that from the main
replicated data set which will be managed locally.
I guess the critical path question is can SQL Express server in this
capacity for our needs? Can you rebuild a SQL Publisher Server Instance from
an SQL Express Subscriber Instance? I have heard of Replication Database
Mirroring techniques:
â'The Publisher and Distributor must be Microsoft SQL Server 2005.
Subscribers can be SQL Server 2005 or a previous versionâ'
â'Replication supports mirroring the publication database for merge
replication and for transactional replication with read-only Subscribers or
queued updating Subscribers. Immediate updating Subscribers, Oracle
Publishers, Publishers in a peer-to-peer topology, and republishing are not
supported.â'
SQL Server 2005 Books On-line (September 2007)
Replication and Database Mirroring
- http://msdn2.microsoft.com/en-us/library/ms151799.aspx
Not sure if this means we can use this technology. From what Iâ'm reading it
looks like it does, could you clarify it for me please?
--
Alexander L. Wykel
MIT Professionals
"Charles Wang[MSFT]" wrote:
> Hi Alexander,
> I am not sure if I totally understand your meaning. Based on your
> description, I understand that you have a project which can replicate
> filtered data sets to your SQL Express clients and that you would like to
> know of a backup and restore approach for your SQL Express clients so that
> they can backup the data set after they receive all data through
> replication function in your project. So your question here is that you
> would like to know a method for backing up your data set in your SQL
> Express client, right?
> If I have misunderstood, please let me know.
> Unfortunately there is no backup method at partial data set level in SQL
> Server 2005, but only at file level or database level. Regarding this, you
> can refer to BACKUP DATABASE statement in SQL Server 2005 Books Online.
> At SQL Server side, I think that you may consider the following two ways:
> 1. Use incremental database backup for your SQL Express client.
> 2. Create those tables on a dedicated file and then only backup your
> database on that file.
> Of course if your client database is not large, you may directly perform a
> full database backup. This depends on your database size and performance
> requirement.
> You may refer to the following articles:
> Copying Databases with Backup and Restore
> http://msdn2.microsoft.com/en-us/library/ms190436.aspx
> Backing Up and Restoring Databases in SQL Server
> http://technet.microsoft.com/en-us/library/ms187048.aspx
> Strategies for Backing Up and Restoring Merge Replication
> http://technet.microsoft.com/en-us/library/ms152497.aspx
> Backing Up and Restoring Replicated Databases
> http://technet.microsoft.com/en-us/library/ms151152.aspx
> If you have any other questions or concerns, please feel free to let me
> know. Have a nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =========================================================> Delighting our customers is our #1 priority. We welcome your
> comments and suggestions about how we can improve the
> support we provide to you. Please feel free to let my manager
> know what you think of the level of service provided. You can
> send feedback directly to my manager at: msdnmg@.microsoft.com.
> =========================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================>
>
>
>
>
>
>|||I have one minor correction, the site we intend to replicate the full data
set is off site (SQL Express Subscriber). This allows us to incrementally
send data as it arrives as apposed to sending large backup distributions.
Then the main back up would then proceed from the off site location. We
intend to back up the MDF file as apposed to creating a backup, then if the
main location goes down we send the attach the MDF and rebuild the Publisher
Data Set from the full copy of the Subscriber MDF data set.
--
Alexander L. Wykel
MIT Professionals
"Alexander Wykel" wrote:
> Sorry Charles,
> My outline of the project details left al lot of unresolved issues for you
> to have to piece together. The process is simple, for now forget about the
> individual clients whom are receiving filtered data sets. What our primary
> aim is to replicate the entire data set from the publisher to a local
> Subscriber SQL Express instance. Our Corporate network is distributed and
> our disaster recovery center is located off site (which we intend on
> implementing via hot spare), so we would like to have local copy of the
> entire data set (the data that is really important to our organization, to
> use to rebuild the server while itâ's down and the hot spare is in use) not
> the replication and publisher subscriber information, that we would have to
> rebuild. If worst comes to worst we can rebuild that from the main
> replicated data set which will be managed locally.
> I guess the critical path question is can SQL Express server in this
> capacity for our needs? Can you rebuild a SQL Publisher Server Instance from
> an SQL Express Subscriber Instance? I have heard of Replication Database
> Mirroring techniques:
> â'The Publisher and Distributor must be Microsoft SQL Server 2005.
> Subscribers can be SQL Server 2005 or a previous versionâ'
> â'Replication supports mirroring the publication database for merge
> replication and for transactional replication with read-only Subscribers or
> queued updating Subscribers. Immediate updating Subscribers, Oracle
> Publishers, Publishers in a peer-to-peer topology, and republishing are not
> supported.â'
> SQL Server 2005 Books On-line (September 2007)
> Replication and Database Mirroring
> - http://msdn2.microsoft.com/en-us/library/ms151799.aspx
> Not sure if this means we can use this technology. From what Iâ'm reading it
> looks like it does, could you clarify it for me please?
> --
> Alexander L. Wykel
> MIT Professionals
>
> "Charles Wang[MSFT]" wrote:
> > Hi Alexander,
> > I am not sure if I totally understand your meaning. Based on your
> > description, I understand that you have a project which can replicate
> > filtered data sets to your SQL Express clients and that you would like to
> > know of a backup and restore approach for your SQL Express clients so that
> > they can backup the data set after they receive all data through
> > replication function in your project. So your question here is that you
> > would like to know a method for backing up your data set in your SQL
> > Express client, right?
> > If I have misunderstood, please let me know.
> >
> > Unfortunately there is no backup method at partial data set level in SQL
> > Server 2005, but only at file level or database level. Regarding this, you
> > can refer to BACKUP DATABASE statement in SQL Server 2005 Books Online.
> >
> > At SQL Server side, I think that you may consider the following two ways:
> > 1. Use incremental database backup for your SQL Express client.
> > 2. Create those tables on a dedicated file and then only backup your
> > database on that file.
> >
> > Of course if your client database is not large, you may directly perform a
> > full database backup. This depends on your database size and performance
> > requirement.
> >
> > You may refer to the following articles:
> > Copying Databases with Backup and Restore
> > http://msdn2.microsoft.com/en-us/library/ms190436.aspx
> >
> > Backing Up and Restoring Databases in SQL Server
> > http://technet.microsoft.com/en-us/library/ms187048.aspx
> >
> > Strategies for Backing Up and Restoring Merge Replication
> > http://technet.microsoft.com/en-us/library/ms152497.aspx
> >
> > Backing Up and Restoring Replicated Databases
> > http://technet.microsoft.com/en-us/library/ms151152.aspx
> >
> > If you have any other questions or concerns, please feel free to let me
> > know. Have a nice day!
> >
> > Best regards,
> > Charles Wang
> > Microsoft Online Community Support
> > =========================================================> > Delighting our customers is our #1 priority. We welcome your
> > comments and suggestions about how we can improve the
> > support we provide to you. Please feel free to let my manager
> > know what you think of the level of service provided. You can
> > send feedback directly to my manager at: msdnmg@.microsoft.com.
> > =========================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> > =========================================================> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >|||Hi Alexander,
Thanks for your response.
Indeed Database Mirroring can provide high availability (automatic
failover) at database level, however it does not support SQL Server Express
Edition. Only SQL Server 2005 Standard/Enterprise Edition can support this
feature.
For SQL Server Express Edition, you can use SQL Server replication to
replicate data from your publisher to your subscriber. However do you
really need merge replication? If the data change on your subscriber need
not be merged to your publisher, I recommend that you use Transactional
replication so that you can win the best performance among the existing
three types of SQL Server replication.
Though transactional replication has very high performance for replicating
data, actually it is also not a real time replication as well as other type
of replication, so if you want to use detach/attach methods to recover your
database on your publisher, there might be still at a risk of a little part
data loss. Since the data is very critical to your organization, I
recommend that you may consider the following solutions for your publisher
server: SQL Server failover cluster, or Database Mirroring, or Replication
together with Database Mirroring, or Replication together with failover
cluster.
With SQL Server failover cluster or Database Mirroring for your publisher
servers (two or more nodes), when the primary publisher is down, the
secondary publisher will automatically failover to be the primary
publisher. By using replication together with the failover function, it
brings you an additional level of security safeguard. You can distribute
your data to a different location. If some emergent situations happen, you
can recover your publisher by using this data, though there might be a
little data loss.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
=========================================================This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

Backup and Restore using SQL Server with Merge Replication

We have a project where we are replicating filtered data sets to SQL Express
clients. We are thinking of a back and restore approach that uses SQL
Express. The a SQL Express Instance would receive all data thru replication
then that data set would be backed up. We are pursuing the approach because
of distance and location of the central replication server and logistics of
backing it up. Or managers have requested Microsoft’s documented or
undocumented view on this approach. If there is any online documentation
that outlines a process like this or a document that illustrates that this
can be done and is a feasible back and restore approach, please post back.
Thanks in advance.
Alexander L. Wykel
MIT Professionals
Hi Alexander,
I am not sure if I totally understand your meaning. Based on your
description, I understand that you have a project which can replicate
filtered data sets to your SQL Express clients and that you would like to
know of a backup and restore approach for your SQL Express clients so that
they can backup the data set after they receive all data through
replication function in your project. So your question here is that you
would like to know a method for backing up your data set in your SQL
Express client, right?
If I have misunderstood, please let me know.
Unfortunately there is no backup method at partial data set level in SQL
Server 2005, but only at file level or database level. Regarding this, you
can refer to BACKUP DATABASE statement in SQL Server 2005 Books Online.
At SQL Server side, I think that you may consider the following two ways:
1. Use incremental database backup for your SQL Express client.
2. Create those tables on a dedicated file and then only backup your
database on that file.
Of course if your client database is not large, you may directly perform a
full database backup. This depends on your database size and performance
requirement.
You may refer to the following articles:
Copying Databases with Backup and Restore
http://msdn2.microsoft.com/en-us/library/ms190436.aspx
Backing Up and Restoring Databases in SQL Server
http://technet.microsoft.com/en-us/library/ms187048.aspx
Strategies for Backing Up and Restoring Merge Replication
http://technet.microsoft.com/en-us/library/ms152497.aspx
Backing Up and Restoring Replicated Databases
http://technet.microsoft.com/en-us/library/ms151152.aspx
If you have any other questions or concerns, please feel free to let me
know. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======
|||Sorry Charles,
My outline of the project details left al lot of unresolved issues for you
to have to piece together. The process is simple, for now forget about the
individual clients whom are receiving filtered data sets. What our primary
aim is to replicate the entire data set from the publisher to a local
Subscriber SQL Express instance. Our Corporate network is distributed and
our disaster recovery center is located off site (which we intend on
implementing via hot spare), so we would like to have local copy of the
entire data set (the data that is really important to our organization, to
use to rebuild the server while it’s down and the hot spare is in use) not
the replication and publisher subscriber information, that we would have to
rebuild. If worst comes to worst we can rebuild that from the main
replicated data set which will be managed locally.
I guess the critical path question is can SQL Express server in this
capacity for our needs? Can you rebuild a SQL Publisher Server Instance from
an SQL Express Subscriber Instance? I have heard of Replication Database
Mirroring techniques:
“The Publisher and Distributor must be Microsoft SQL Server 2005.
Subscribers can be SQL Server 2005 or a previous version”
“Replication supports mirroring the publication database for merge
replication and for transactional replication with read-only Subscribers or
queued updating Subscribers. Immediate updating Subscribers, Oracle
Publishers, Publishers in a peer-to-peer topology, and republishing are not
supported.”
SQL Server 2005 Books On-line (September 2007)
Replication and Database Mirroring
- http://msdn2.microsoft.com/en-us/library/ms151799.aspx
Not sure if this means we can use this technology. From what I’m reading it
looks like it does, could you clarify it for me please?
Alexander L. Wykel
MIT Professionals
"Charles Wang[MSFT]" wrote:

> Hi Alexander,
> I am not sure if I totally understand your meaning. Based on your
> description, I understand that you have a project which can replicate
> filtered data sets to your SQL Express clients and that you would like to
> know of a backup and restore approach for your SQL Express clients so that
> they can backup the data set after they receive all data through
> replication function in your project. So your question here is that you
> would like to know a method for backing up your data set in your SQL
> Express client, right?
> If I have misunderstood, please let me know.
> Unfortunately there is no backup method at partial data set level in SQL
> Server 2005, but only at file level or database level. Regarding this, you
> can refer to BACKUP DATABASE statement in SQL Server 2005 Books Online.
> At SQL Server side, I think that you may consider the following two ways:
> 1. Use incremental database backup for your SQL Express client.
> 2. Create those tables on a dedicated file and then only backup your
> database on that file.
> Of course if your client database is not large, you may directly perform a
> full database backup. This depends on your database size and performance
> requirement.
> You may refer to the following articles:
> Copying Databases with Backup and Restore
> http://msdn2.microsoft.com/en-us/library/ms190436.aspx
> Backing Up and Restoring Databases in SQL Server
> http://technet.microsoft.com/en-us/library/ms187048.aspx
> Strategies for Backing Up and Restoring Merge Replication
> http://technet.microsoft.com/en-us/library/ms152497.aspx
> Backing Up and Restoring Replicated Databases
> http://technet.microsoft.com/en-us/library/ms151152.aspx
> If you have any other questions or concerns, please feel free to let me
> know. Have a nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== =======
> Delighting our customers is our #1 priority. We welcome your
> comments and suggestions about how we can improve the
> support we provide to you. Please feel free to let my manager
> know what you think of the level of service provided. You can
> send feedback directly to my manager at: msdnmg@.microsoft.com.
> ================================================== =======
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== =======
>
>
>
>
>
>
>
|||I have one minor correction, the site we intend to replicate the full data
set is off site (SQL Express Subscriber). This allows us to incrementally
send data as it arrives as apposed to sending large backup distributions.
Then the main back up would then proceed from the off site location. We
intend to back up the MDF file as apposed to creating a backup, then if the
main location goes down we send the attach the MDF and rebuild the Publisher
Data Set from the full copy of the Subscriber MDF data set.
Alexander L. Wykel
MIT Professionals
"Alexander Wykel" wrote:
[vbcol=seagreen]
> Sorry Charles,
> My outline of the project details left al lot of unresolved issues for you
> to have to piece together. The process is simple, for now forget about the
> individual clients whom are receiving filtered data sets. What our primary
> aim is to replicate the entire data set from the publisher to a local
> Subscriber SQL Express instance. Our Corporate network is distributed and
> our disaster recovery center is located off site (which we intend on
> implementing via hot spare), so we would like to have local copy of the
> entire data set (the data that is really important to our organization, to
> use to rebuild the server while it’s down and the hot spare is in use) not
> the replication and publisher subscriber information, that we would have to
> rebuild. If worst comes to worst we can rebuild that from the main
> replicated data set which will be managed locally.
> I guess the critical path question is can SQL Express server in this
> capacity for our needs? Can you rebuild a SQL Publisher Server Instance from
> an SQL Express Subscriber Instance? I have heard of Replication Database
> Mirroring techniques:
> “The Publisher and Distributor must be Microsoft SQL Server 2005.
> Subscribers can be SQL Server 2005 or a previous version”
> “Replication supports mirroring the publication database for merge
> replication and for transactional replication with read-only Subscribers or
> queued updating Subscribers. Immediate updating Subscribers, Oracle
> Publishers, Publishers in a peer-to-peer topology, and republishing are not
> supported.”
> SQL Server 2005 Books On-line (September 2007)
> Replication and Database Mirroring
> - http://msdn2.microsoft.com/en-us/library/ms151799.aspx
> Not sure if this means we can use this technology. From what I’m reading it
> looks like it does, could you clarify it for me please?
> --
> Alexander L. Wykel
> MIT Professionals
>
> "Charles Wang[MSFT]" wrote:
|||Hi Alexander,
Thanks for your response.
Indeed Database Mirroring can provide high availability (automatic
failover) at database level, however it does not support SQL Server Express
Edition. Only SQL Server 2005 Standard/Enterprise Edition can support this
feature.
For SQL Server Express Edition, you can use SQL Server replication to
replicate data from your publisher to your subscriber. However do you
really need merge replication? If the data change on your subscriber need
not be merged to your publisher, I recommend that you use Transactional
replication so that you can win the best performance among the existing
three types of SQL Server replication.
Though transactional replication has very high performance for replicating
data, actually it is also not a real time replication as well as other type
of replication, so if you want to use detach/attach methods to recover your
database on your publisher, there might be still at a risk of a little part
data loss. Since the data is very critical to your organization, I
recommend that you may consider the following solutions for your publisher
server: SQL Server failover cluster, or Database Mirroring, or Replication
together with Database Mirroring, or Replication together with failover
cluster.
With SQL Server failover cluster or Database Mirroring for your publisher
servers (two or more nodes), when the primary publisher is down, the
secondary publisher will automatically failover to be the primary
publisher. By using replication together with the failover function, it
brings you an additional level of security safeguard. You can distribute
your data to a different location. If some emergent situations happen, you
can recover your publisher by using this data, though there might be a
little data loss.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Wednesday, March 7, 2012

Backup and restore to the same machine

We are using SQL Server 2005.
I did a full back up of myDatabase. I would like to restore it to a
different database name on the same machine (I still want the original
database to be there), can I do that ?
On the Restore database, under the "To database" I typed in "myNewDatabase",
and on the "From device" I selected the full backup that I made.
When I try to restore it, it gave me an error "The file c:\program
files\...\myDatabase.mdf" cannot be overwritten. It is being used by
database 'myDatabase'. (Microsoft.SQLServer.Smo).
So, does it mean that even though I restore it to a different database name,
I can not restore the database in the same machine ?
Thank you.
fniles,
You also need to use the BACKUP feature to MOVE Filename TO
NewPhysicalFileName.
If you are using the SQL Server Management Studio to do this, on the RESTORE
dialog, click on the OPTIONS tab. You can give new file names to the
restoring mdf and ldg there.
RLF
"fniles" <fniles@.pfmail.com> wrote in message
news:eq5y9tVTIHA.5980@.TK2MSFTNGP04.phx.gbl...
> We are using SQL Server 2005.
> I did a full back up of myDatabase. I would like to restore it to a
> different database name on the same machine (I still want the original
> database to be there), can I do that ?
> On the Restore database, under the "To database" I typed in
> "myNewDatabase", and on the "From device" I selected the full backup that
> I made.
> When I try to restore it, it gave me an error "The file c:\program
> files\...\myDatabase.mdf" cannot be overwritten. It is being used by
> database 'myDatabase'. (Microsoft.SQLServer.Smo).
> So, does it mean that even though I restore it to a different database
> name, I can not restore the database in the same machine ?
> Thank you.
>
|||It can be done .. do this:
1) Create a new database called 'myDB2'.
2) Right click on the myDB2, and go to Restore.
3) Select the From Device to the BAK file you created from myDatabase.
4) Under options, select overwrite.
5) Change the location of the data file and log file to that of myDB2. So
it can over write the files for this database to match what is in your backup.
That should work for yaa ;-).
Or you can run the following script:
RESTORE DATABASE [myDB2]
FROM DISK = 'D:\MSSQL\Backup\myDatabase.bak'
WITH FILE = 1,
MOVE N'myDatabase_dat' TO 'D:\MSSQL\DATA\myDB2_dat.mdf'
MOVE N'myDatabase_log' TO 'E:\MSSQL\LOG\myDB2.ldf',
NOUNLOAD, REPLACE, STATS = 10
After creating the database

Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/
"fniles" wrote:

> We are using SQL Server 2005.
> I did a full back up of myDatabase. I would like to restore it to a
> different database name on the same machine (I still want the original
> database to be there), can I do that ?
> On the Restore database, under the "To database" I typed in "myNewDatabase",
> and on the "From device" I selected the full backup that I made.
> When I try to restore it, it gave me an error "The file c:\program
> files\...\myDatabase.mdf" cannot be overwritten. It is being used by
> database 'myDatabase'. (Microsoft.SQLServer.Smo).
> So, does it mean that even though I restore it to a different database name,
> I can not restore the database in the same machine ?
> Thank you.
>
>
|||Hi
You will need to use the move option for the restore command see example E at
http://msdn2.microsoft.com/en-us/library/ms186858.aspx
John
"fniles" wrote:

> We are using SQL Server 2005.
> I did a full back up of myDatabase. I would like to restore it to a
> different database name on the same machine (I still want the original
> database to be there), can I do that ?
> On the Restore database, under the "To database" I typed in "myNewDatabase",
> and on the "From device" I selected the full backup that I made.
> When I try to restore it, it gave me an error "The file c:\program
> files\...\myDatabase.mdf" cannot be overwritten. It is being used by
> database 'myDatabase'. (Microsoft.SQLServer.Smo).
> So, does it mean that even though I restore it to a different database name,
> I can not restore the database in the same machine ?
> Thank you.
>
>
|||First line should have said: ... use the RESTORE feature to MOVE Filename TO
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OMqsA2VTIHA.1164@.TK2MSFTNGP02.phx.gbl...
> fniles,
> You also need to use the BACKUP feature to MOVE Filename TO
> NewPhysicalFileName.
> If you are using the SQL Server Management Studio to do this, on the
> RESTORE dialog, click on the OPTIONS tab. You can give new file names to
> the restoring mdf and ldg there.
> RLF
> "fniles" <fniles@.pfmail.com> wrote in message
> news:eq5y9tVTIHA.5980@.TK2MSFTNGP04.phx.gbl...
>
|||Hi
I should have added use RESTORE FILELISTONLY to get the logical file names
of you don't know what they are, or look at the existing database. Logical
file names do not have to be different between databases (and in this case
will not unless you change them later!)
John
"fniles" wrote:

> We are using SQL Server 2005.
> I did a full back up of myDatabase. I would like to restore it to a
> different database name on the same machine (I still want the original
> database to be there), can I do that ?
> On the Restore database, under the "To database" I typed in "myNewDatabase",
> and on the "From device" I selected the full backup that I made.
> When I try to restore it, it gave me an error "The file c:\program
> files\...\myDatabase.mdf" cannot be overwritten. It is being used by
> database 'myDatabase'. (Microsoft.SQLServer.Smo).
> So, does it mean that even though I restore it to a different database name,
> I can not restore the database in the same machine ?
> Thank you.
>
>
|||"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23$DDE5VTIHA.5524@.TK2MSFTNGP05.phx.gbl...
> First line should have said: ... use the RESTORE feature to MOVE Filename
> TO
>
And here I was thinking that was a nifty new feature I was going to try out.
(I actually could think of cases where it would be useful.)

> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OMqsA2VTIHA.1164@.TK2MSFTNGP02.phx.gbl...
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Sunday, March 25, 2012

Backup everything except data

Backup Error

Tuesday, March 20, 2012

Backup Databases SQL

Monday, March 19, 2012

Backup databases

Backup Database! this is really interesting!

Backup database with encrypted column data

Backup database to floppy

Backup database to floppy

Backup database to floppy

Backup Database Task

Backup Database Task

Thursday, March 8, 2012

Backup continues to try to execute...

Backup continues to try to execute...

backup and restoring

Backup and Restore using SQL Server with Merge Replication

Backup and Restore using SQL Server with Merge Replication

Wednesday, March 7, 2012

Backup and restore to the same machine

Sql Backing up Tables

Blog Archive

About Me