MSSQL, Microsoft SQL Server:
I succeeded in transferring a backup file from the SQL Server machine
to the client using OPENROWSET.
I used this way because it is not (always easy) possible to restore a
file residing on the client machine on a SQL Server.
One has to share drives on the client machine for remote SQL Server
access or better granting access on the SQL Server machine for the
client.
In my line of bussiness it is not possible to help every client on this
issue (several thousand users)
What I need to achieve is transferring backup to the server by using
SQL Server to do it and restoring it with SQL Server.
A solution for SQL 2005 would be perfect (it is not necessary to solve
this in SQL Server 2000). Using Named pipes is not an option because it
is no longer supported in SQL2005
Hi
"Oz" wrote:
> MSSQL, Microsoft SQL Server:
> I succeeded in transferring a backup file from the SQL Server machine
> to the client using OPENROWSET.
I am not sure why you are doing this, if you are actually talking about a
database backup. I assume this is a CSV or XML file? If so why not use the
BULK INSERT command? This will take a UNC name for the file if necessary.
> I used this way because it is not (always easy) possible to restore a
> file residing on the client machine on a SQL Server.
> One has to share drives on the client machine for remote SQL Server
> access or better granting access on the SQL Server machine for the
> client.
> In my line of bussiness it is not possible to help every client on this
> issue (several thousand users)
> What I need to achieve is transferring backup to the server by using
> SQL Server to do it and restoring it with SQL Server.
Have you looked at SSIS and loading the file using FTP either through a
package or User Interface (e.g
http://classicasp.aspfaq.com/general/how-do-i-ftp-files-from-asp.html ) onto
the SQL Server or somewhere which can be seen by the SQL Server and then
loading it from a package?
> A solution for SQL 2005 would be perfect (it is not necessary to solve
> this in SQL Server 2000). Using Named pipes is not an option because it
> is no longer supported in SQL2005
>
John
Showing posts with label backup. Show all posts
Showing posts with label backup. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
How to trancate transaction log?
I use the backup wizard to run a transaction log backup with option to remov
e
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.Are you using the full or bulk-logged recovery mode? I guess you are if the
backup completes successfully. You probably have an uncommitted transaction
that's preventing the log being truncated. Look at the output from DBCC
OPENTRAN to determine the age of the oldest uncommitted transaction (on SQL
2000)
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
> I use the backup wizard to run a transaction log backup with option to
remove
> inactive transaction logs. After completing the backup, the size of
> transaction log file does not shrink. Why? Thanks.|||Hi
The physical size of the log file will not change when you do a backup -
that will only truncate the log. To reduce the filesize, you'll have to
shrink the file.
You can try to look up DBCC SHRINKFILE in BOL - and also check out
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Regards
Steen
joeau wrote:
> I use the backup wizard to run a transaction log backup with option
> to remove inactive transaction logs. After completing the backup, the
> size of transaction log file does not shrink. Why? Thanks.|||Removing unused transactions means incorporating those committed
transactions in the full backup as well.
Truncation does not imply file shrinkage, just the removal, actually just a
mark on the virtual logs, that those transactions have completed and backed
up.
Although the transaction log file may not have been shrunk like you'd like,
because all of the old transactions have committed and backed up, the
transaction log does not have any reason to grow either because it can now
use those freed up virtaul logs.
Sincerely,
Anthony Thomas
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
I use the backup wizard to run a transaction log backup with option to
remove
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.
e
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.Are you using the full or bulk-logged recovery mode? I guess you are if the
backup completes successfully. You probably have an uncommitted transaction
that's preventing the log being truncated. Look at the output from DBCC
OPENTRAN to determine the age of the oldest uncommitted transaction (on SQL
2000)
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
> I use the backup wizard to run a transaction log backup with option to
remove
> inactive transaction logs. After completing the backup, the size of
> transaction log file does not shrink. Why? Thanks.|||Hi
The physical size of the log file will not change when you do a backup -
that will only truncate the log. To reduce the filesize, you'll have to
shrink the file.
You can try to look up DBCC SHRINKFILE in BOL - and also check out
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Regards
Steen
joeau wrote:
> I use the backup wizard to run a transaction log backup with option
> to remove inactive transaction logs. After completing the backup, the
> size of transaction log file does not shrink. Why? Thanks.|||Removing unused transactions means incorporating those committed
transactions in the full backup as well.
Truncation does not imply file shrinkage, just the removal, actually just a
mark on the virtual logs, that those transactions have completed and backed
up.
Although the transaction log file may not have been shrunk like you'd like,
because all of the old transactions have committed and backed up, the
transaction log does not have any reason to grow either because it can now
use those freed up virtaul logs.
Sincerely,
Anthony Thomas
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
I use the backup wizard to run a transaction log backup with option to
remove
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.
How to trancate transaction log?
I use the backup wizard to run a transaction log backup with option to remove
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.
Are you using the full or bulk-logged recovery mode? I guess you are if the
backup completes successfully. You probably have an uncommitted transaction
that's preventing the log being truncated. Look at the output from DBCC
OPENTRAN to determine the age of the oldest uncommitted transaction (on SQL
2000)
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
> I use the backup wizard to run a transaction log backup with option to
remove
> inactive transaction logs. After completing the backup, the size of
> transaction log file does not shrink. Why? Thanks.
|||Hi
The physical size of the log file will not change when you do a backup -
that will only truncate the log. To reduce the filesize, you'll have to
shrink the file.
You can try to look up DBCC SHRINKFILE in BOL - and also check out
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Regards
Steen
joeau wrote:
> I use the backup wizard to run a transaction log backup with option
> to remove inactive transaction logs. After completing the backup, the
> size of transaction log file does not shrink. Why? Thanks.
|||Removing unused transactions means incorporating those committed
transactions in the full backup as well.
Truncation does not imply file shrinkage, just the removal, actually just a
mark on the virtual logs, that those transactions have completed and backed
up.
Although the transaction log file may not have been shrunk like you'd like,
because all of the old transactions have committed and backed up, the
transaction log does not have any reason to grow either because it can now
use those freed up virtaul logs.
Sincerely,
Anthony Thomas
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
I use the backup wizard to run a transaction log backup with option to
remove
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.
sql
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.
Are you using the full or bulk-logged recovery mode? I guess you are if the
backup completes successfully. You probably have an uncommitted transaction
that's preventing the log being truncated. Look at the output from DBCC
OPENTRAN to determine the age of the oldest uncommitted transaction (on SQL
2000)
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
> I use the backup wizard to run a transaction log backup with option to
remove
> inactive transaction logs. After completing the backup, the size of
> transaction log file does not shrink. Why? Thanks.
|||Hi
The physical size of the log file will not change when you do a backup -
that will only truncate the log. To reduce the filesize, you'll have to
shrink the file.
You can try to look up DBCC SHRINKFILE in BOL - and also check out
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Regards
Steen
joeau wrote:
> I use the backup wizard to run a transaction log backup with option
> to remove inactive transaction logs. After completing the backup, the
> size of transaction log file does not shrink. Why? Thanks.
|||Removing unused transactions means incorporating those committed
transactions in the full backup as well.
Truncation does not imply file shrinkage, just the removal, actually just a
mark on the virtual logs, that those transactions have completed and backed
up.
Although the transaction log file may not have been shrunk like you'd like,
because all of the old transactions have committed and backed up, the
transaction log does not have any reason to grow either because it can now
use those freed up virtaul logs.
Sincerely,
Anthony Thomas
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
I use the backup wizard to run a transaction log backup with option to
remove
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.
sql
How to trancate transaction log?
I use the backup wizard to run a transaction log backup with option to remove
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.Are you using the full or bulk-logged recovery mode? I guess you are if the
backup completes successfully. You probably have an uncommitted transaction
that's preventing the log being truncated. Look at the output from DBCC
OPENTRAN to determine the age of the oldest uncommitted transaction (on SQL
2000)
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
> I use the backup wizard to run a transaction log backup with option to
remove
> inactive transaction logs. After completing the backup, the size of
> transaction log file does not shrink. Why? Thanks.|||Hi
The physical size of the log file will not change when you do a backup -
that will only truncate the log. To reduce the filesize, you'll have to
shrink the file.
You can try to look up DBCC SHRINKFILE in BOL - and also check out
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Regards
Steen
joeau wrote:
> I use the backup wizard to run a transaction log backup with option
> to remove inactive transaction logs. After completing the backup, the
> size of transaction log file does not shrink. Why? Thanks.|||Removing unused transactions means incorporating those committed
transactions in the full backup as well.
Truncation does not imply file shrinkage, just the removal, actually just a
mark on the virtual logs, that those transactions have completed and backed
up.
Although the transaction log file may not have been shrunk like you'd like,
because all of the old transactions have committed and backed up, the
transaction log does not have any reason to grow either because it can now
use those freed up virtaul logs.
Sincerely,
Anthony Thomas
--
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
I use the backup wizard to run a transaction log backup with option to
remove
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.Are you using the full or bulk-logged recovery mode? I guess you are if the
backup completes successfully. You probably have an uncommitted transaction
that's preventing the log being truncated. Look at the output from DBCC
OPENTRAN to determine the age of the oldest uncommitted transaction (on SQL
2000)
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
> I use the backup wizard to run a transaction log backup with option to
remove
> inactive transaction logs. After completing the backup, the size of
> transaction log file does not shrink. Why? Thanks.|||Hi
The physical size of the log file will not change when you do a backup -
that will only truncate the log. To reduce the filesize, you'll have to
shrink the file.
You can try to look up DBCC SHRINKFILE in BOL - and also check out
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Regards
Steen
joeau wrote:
> I use the backup wizard to run a transaction log backup with option
> to remove inactive transaction logs. After completing the backup, the
> size of transaction log file does not shrink. Why? Thanks.|||Removing unused transactions means incorporating those committed
transactions in the full backup as well.
Truncation does not imply file shrinkage, just the removal, actually just a
mark on the virtual logs, that those transactions have completed and backed
up.
Although the transaction log file may not have been shrunk like you'd like,
because all of the old transactions have committed and backed up, the
transaction log does not have any reason to grow either because it can now
use those freed up virtaul logs.
Sincerely,
Anthony Thomas
--
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
I use the backup wizard to run a transaction log backup with option to
remove
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.
Friday, March 23, 2012
How to test if a full backup exists
I'm writing a automated backup routine for both SQL Server 2000 and 2005 that
will be a combination of full (once a week) and differential (all other days
of the week) backups.
My problem is that if someone creates a new database I need to automatically
take a full backup before the differential backup (or I'll get error 3035
"Cannot perform a differential backup"). So I would like to know if there is
a way to test if a full backup has been taken for a specific database?
Yes, I know about msdb.dbo.backupset but that's not a fullproof solution
since someone can create a new database with the same name as a previously
backed up database.
I have searched through DATABASEPROPERTY, DATABASEPROPERTYEX and all columns
in sysdatabases, sysfiles etc without any luck. Have I missed something or is
this property not available?
-AllanYou can use a combination of the name, create date, type and backup finish
date between the sysdatabases and backupset tables to determine whether a
full backup has occured for a given database. create date is found on both
backupset and sysdatabases and is the key to making sure the backup is
associated with the correct "version" of databases created over time with
the same name. NOTE: you will need to lop off the milliseconds to get the
join to work
sysdatabases.crdate
2007-05-01 09:35:14.553
backupset.database_creation_date
2007-05-01 09:35:14.000
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Allan" <allan@.newsgroups.nospam> wrote in message
news:C3484A5E-287C-4953-98DF-C34762A684CD@.microsoft.com...
> I'm writing a automated backup routine for both SQL Server 2000 and 2005
> that
> will be a combination of full (once a week) and differential (all other
> days
> of the week) backups.
> My problem is that if someone creates a new database I need to
> automatically
> take a full backup before the differential backup (or I'll get error 3035
> "Cannot perform a differential backup"). So I would like to know if there
> is
> a way to test if a full backup has been taken for a specific database?
> Yes, I know about msdb.dbo.backupset but that's not a fullproof solution
> since someone can create a new database with the same name as a previously
> backed up database.
> I have searched through DATABASEPROPERTY, DATABASEPROPERTYEX and all
> columns
> in sysdatabases, sysfiles etc without any luck. Have I missed something or
> is
> this property not available?
> -Allan
will be a combination of full (once a week) and differential (all other days
of the week) backups.
My problem is that if someone creates a new database I need to automatically
take a full backup before the differential backup (or I'll get error 3035
"Cannot perform a differential backup"). So I would like to know if there is
a way to test if a full backup has been taken for a specific database?
Yes, I know about msdb.dbo.backupset but that's not a fullproof solution
since someone can create a new database with the same name as a previously
backed up database.
I have searched through DATABASEPROPERTY, DATABASEPROPERTYEX and all columns
in sysdatabases, sysfiles etc without any luck. Have I missed something or is
this property not available?
-AllanYou can use a combination of the name, create date, type and backup finish
date between the sysdatabases and backupset tables to determine whether a
full backup has occured for a given database. create date is found on both
backupset and sysdatabases and is the key to making sure the backup is
associated with the correct "version" of databases created over time with
the same name. NOTE: you will need to lop off the milliseconds to get the
join to work
sysdatabases.crdate
2007-05-01 09:35:14.553
backupset.database_creation_date
2007-05-01 09:35:14.000
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Allan" <allan@.newsgroups.nospam> wrote in message
news:C3484A5E-287C-4953-98DF-C34762A684CD@.microsoft.com...
> I'm writing a automated backup routine for both SQL Server 2000 and 2005
> that
> will be a combination of full (once a week) and differential (all other
> days
> of the week) backups.
> My problem is that if someone creates a new database I need to
> automatically
> take a full backup before the differential backup (or I'll get error 3035
> "Cannot perform a differential backup"). So I would like to know if there
> is
> a way to test if a full backup has been taken for a specific database?
> Yes, I know about msdb.dbo.backupset but that's not a fullproof solution
> since someone can create a new database with the same name as a previously
> backed up database.
> I have searched through DATABASEPROPERTY, DATABASEPROPERTYEX and all
> columns
> in sysdatabases, sysfiles etc without any luck. Have I missed something or
> is
> this property not available?
> -Allan
how to tell when log file is not in use
sql2k sp3
Ive got a db with almost 4 gig of free space. I created a
proc that attempted to shrink it last night before doing
my backup so as to not backup this space. But I got the
message "Cannot shrink log file 2 (bla_log) because all
logical log files are in use". So I either need a way to
tell my backup to ignore the free space(this would be
preferable, but I dont this its possible) or tell my
shrink proc to see if the Log file is not in use.
TIA, ChrisBackup does not put your free space into the backup file. So don't worry
about that.
hth
Quentin
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:09ff01c3dc46$b4e9d2b0$a601280a@.phx.gbl...
space is about 9.7 gigs?
file. So don't worry
message
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:018e01c3dc54$5ee62310$a101280a@.phx.gbl...[QUOTE]
> Then why do I have a backup thats 12.7 gigs when the used
> space is about 9.7 gigs?
>
> file. So don't worry
> message
> a|||Yes.
"Quentin Ran" <ab@.who.com> wrote in message
news:#9DozhG3DHA.2296@.TK2MSFTNGP11.phx.gbl...
Ive got a db with almost 4 gig of free space. I created a
proc that attempted to shrink it last night before doing
my backup so as to not backup this space. But I got the
message "Cannot shrink log file 2 (bla_log) because all
logical log files are in use". So I either need a way to
tell my backup to ignore the free space(this would be
preferable, but I dont this its possible) or tell my
shrink proc to see if the Log file is not in use.
TIA, ChrisBackup does not put your free space into the backup file. So don't worry
about that.
hth
Quentin
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:09ff01c3dc46$b4e9d2b0$a601280a@.phx.gbl...
quote:|||Then why do I have a backup thats 12.7 gigs when the used
> sql2k sp3
> Ive got a db with almost 4 gig of free space. I created a
> proc that attempted to shrink it last night before doing
> my backup so as to not backup this space. But I got the
> message "Cannot shrink log file 2 (bla_log) because all
> logical log files are in use". So I either need a way to
> tell my backup to ignore the free space(this would be
> preferable, but I dont this its possible) or tell my
> shrink proc to see if the Log file is not in use.
> TIA, Chris
space is about 9.7 gigs?
quote:
>--Original Message--
>Backup does not put your free space into the backup
file. So don't worry
quote:
>about that.
>hth
>Quentin
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
quote:|||Have you updated the used space information?
>news:09ff01c3dc46$b4e9d2b0$a601280a@.phx.gbl...
a[QUOTE]
>
>.
>
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:018e01c3dc54$5ee62310$a101280a@.phx.gbl...[QUOTE]
> Then why do I have a backup thats 12.7 gigs when the used
> space is about 9.7 gigs?
>
> file. So don't worry
> message
> a|||Yes.
"Quentin Ran" <ab@.who.com> wrote in message
news:#9DozhG3DHA.2296@.TK2MSFTNGP11.phx.gbl...
quote:
> Have you updated the used space information?
> "chris" <anonymous@.discussions.microsoft.com> wrote in message
> news:018e01c3dc54$5ee62310$a101280a@.phx.gbl...
>
how to tell when log file is not in use
sql2k sp3
Ive got a db with almost 4 gig of free space. I created a
proc that attempted to shrink it last night before doing
my backup so as to not backup this space. But I got the
message "Cannot shrink log file 2 (bla_log) because all
logical log files are in use". So I either need a way to
tell my backup to ignore the free space(this would be
preferable, but I dont this its possible) or tell my
shrink proc to see if the Log file is not in use.
TIA, ChrisBackup does not put your free space into the backup file. So don't worry
about that.
hth
Quentin
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:09ff01c3dc46$b4e9d2b0$a601280a@.phx.gbl...
> sql2k sp3
> Ive got a db with almost 4 gig of free space. I created a
> proc that attempted to shrink it last night before doing
> my backup so as to not backup this space. But I got the
> message "Cannot shrink log file 2 (bla_log) because all
> logical log files are in use". So I either need a way to
> tell my backup to ignore the free space(this would be
> preferable, but I dont this its possible) or tell my
> shrink proc to see if the Log file is not in use.
> TIA, Chris|||Then why do I have a backup thats 12.7 gigs when the used
space is about 9.7 gigs?
>--Original Message--
>Backup does not put your free space into the backup
file. So don't worry
>about that.
>hth
>Quentin
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:09ff01c3dc46$b4e9d2b0$a601280a@.phx.gbl...
>> sql2k sp3
>> Ive got a db with almost 4 gig of free space. I created
a
>> proc that attempted to shrink it last night before doing
>> my backup so as to not backup this space. But I got the
>> message "Cannot shrink log file 2 (bla_log) because all
>> logical log files are in use". So I either need a way to
>> tell my backup to ignore the free space(this would be
>> preferable, but I dont this its possible) or tell my
>> shrink proc to see if the Log file is not in use.
>> TIA, Chris
>
>.
>|||Have you updated the used space information?
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:018e01c3dc54$5ee62310$a101280a@.phx.gbl...
> Then why do I have a backup thats 12.7 gigs when the used
> space is about 9.7 gigs?
>
> >--Original Message--
> >Backup does not put your free space into the backup
> file. So don't worry
> >about that.
> >
> >hth
> >
> >Quentin
> >
> >"chris" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:09ff01c3dc46$b4e9d2b0$a601280a@.phx.gbl...
> >> sql2k sp3
> >>
> >> Ive got a db with almost 4 gig of free space. I created
> a
> >> proc that attempted to shrink it last night before doing
> >> my backup so as to not backup this space. But I got the
> >> message "Cannot shrink log file 2 (bla_log) because all
> >> logical log files are in use". So I either need a way to
> >> tell my backup to ignore the free space(this would be
> >> preferable, but I dont this its possible) or tell my
> >> shrink proc to see if the Log file is not in use.
> >>
> >> TIA, Chris
> >
> >
> >.
> >|||Yes.
"Quentin Ran" <ab@.who.com> wrote in message
news:#9DozhG3DHA.2296@.TK2MSFTNGP11.phx.gbl...
> Have you updated the used space information?
> "chris" <anonymous@.discussions.microsoft.com> wrote in message
> news:018e01c3dc54$5ee62310$a101280a@.phx.gbl...
> > Then why do I have a backup thats 12.7 gigs when the used
> > space is about 9.7 gigs?
> >
> >
> > >--Original Message--
> > >Backup does not put your free space into the backup
> > file. So don't worry
> > >about that.
> > >
> > >hth
> > >
> > >Quentin
> > >
> > >"chris" <anonymous@.discussions.microsoft.com> wrote in
> > message
> > >news:09ff01c3dc46$b4e9d2b0$a601280a@.phx.gbl...
> > >> sql2k sp3
> > >>
> > >> Ive got a db with almost 4 gig of free space. I created
> > a
> > >> proc that attempted to shrink it last night before doing
> > >> my backup so as to not backup this space. But I got the
> > >> message "Cannot shrink log file 2 (bla_log) because all
> > >> logical log files are in use". So I either need a way to
> > >> tell my backup to ignore the free space(this would be
> > >> preferable, but I dont this its possible) or tell my
> > >> shrink proc to see if the Log file is not in use.
> > >>
> > >> TIA, Chris
> > >
> > >
> > >.
> > >
>
Ive got a db with almost 4 gig of free space. I created a
proc that attempted to shrink it last night before doing
my backup so as to not backup this space. But I got the
message "Cannot shrink log file 2 (bla_log) because all
logical log files are in use". So I either need a way to
tell my backup to ignore the free space(this would be
preferable, but I dont this its possible) or tell my
shrink proc to see if the Log file is not in use.
TIA, ChrisBackup does not put your free space into the backup file. So don't worry
about that.
hth
Quentin
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:09ff01c3dc46$b4e9d2b0$a601280a@.phx.gbl...
> sql2k sp3
> Ive got a db with almost 4 gig of free space. I created a
> proc that attempted to shrink it last night before doing
> my backup so as to not backup this space. But I got the
> message "Cannot shrink log file 2 (bla_log) because all
> logical log files are in use". So I either need a way to
> tell my backup to ignore the free space(this would be
> preferable, but I dont this its possible) or tell my
> shrink proc to see if the Log file is not in use.
> TIA, Chris|||Then why do I have a backup thats 12.7 gigs when the used
space is about 9.7 gigs?
>--Original Message--
>Backup does not put your free space into the backup
file. So don't worry
>about that.
>hth
>Quentin
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:09ff01c3dc46$b4e9d2b0$a601280a@.phx.gbl...
>> sql2k sp3
>> Ive got a db with almost 4 gig of free space. I created
a
>> proc that attempted to shrink it last night before doing
>> my backup so as to not backup this space. But I got the
>> message "Cannot shrink log file 2 (bla_log) because all
>> logical log files are in use". So I either need a way to
>> tell my backup to ignore the free space(this would be
>> preferable, but I dont this its possible) or tell my
>> shrink proc to see if the Log file is not in use.
>> TIA, Chris
>
>.
>|||Have you updated the used space information?
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:018e01c3dc54$5ee62310$a101280a@.phx.gbl...
> Then why do I have a backup thats 12.7 gigs when the used
> space is about 9.7 gigs?
>
> >--Original Message--
> >Backup does not put your free space into the backup
> file. So don't worry
> >about that.
> >
> >hth
> >
> >Quentin
> >
> >"chris" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:09ff01c3dc46$b4e9d2b0$a601280a@.phx.gbl...
> >> sql2k sp3
> >>
> >> Ive got a db with almost 4 gig of free space. I created
> a
> >> proc that attempted to shrink it last night before doing
> >> my backup so as to not backup this space. But I got the
> >> message "Cannot shrink log file 2 (bla_log) because all
> >> logical log files are in use". So I either need a way to
> >> tell my backup to ignore the free space(this would be
> >> preferable, but I dont this its possible) or tell my
> >> shrink proc to see if the Log file is not in use.
> >>
> >> TIA, Chris
> >
> >
> >.
> >|||Yes.
"Quentin Ran" <ab@.who.com> wrote in message
news:#9DozhG3DHA.2296@.TK2MSFTNGP11.phx.gbl...
> Have you updated the used space information?
> "chris" <anonymous@.discussions.microsoft.com> wrote in message
> news:018e01c3dc54$5ee62310$a101280a@.phx.gbl...
> > Then why do I have a backup thats 12.7 gigs when the used
> > space is about 9.7 gigs?
> >
> >
> > >--Original Message--
> > >Backup does not put your free space into the backup
> > file. So don't worry
> > >about that.
> > >
> > >hth
> > >
> > >Quentin
> > >
> > >"chris" <anonymous@.discussions.microsoft.com> wrote in
> > message
> > >news:09ff01c3dc46$b4e9d2b0$a601280a@.phx.gbl...
> > >> sql2k sp3
> > >>
> > >> Ive got a db with almost 4 gig of free space. I created
> > a
> > >> proc that attempted to shrink it last night before doing
> > >> my backup so as to not backup this space. But I got the
> > >> message "Cannot shrink log file 2 (bla_log) because all
> > >> logical log files are in use". So I either need a way to
> > >> tell my backup to ignore the free space(this would be
> > >> preferable, but I dont this its possible) or tell my
> > >> shrink proc to see if the Log file is not in use.
> > >>
> > >> TIA, Chris
> > >
> > >
> > >.
> > >
>
Monday, March 19, 2012
How to take SSAS database metadata backup using XMLA commands?
Hi,
I am looking for XMLA command to only take metadata backup of a SSAS database having MOLAP storage mode.
I know that by issuing normal backup command it can take backup of metadata, source data and aggregations but I want only metadata backup.
Can someone confirm if this is possible or not?
Quick reposnse of this query is highly appreciated.
Thanks,
Kuldeep
Backup command is not intended to backup metadata only.
You can write few lines using AMO object model to output database metadata into a file and backup the file itself.
Something like:
server.Connect("MyServerName");
XmlTextWriter xmlwrite = new XmlTextWriter("c:\backup\MyDatabase.xmla", System.Text.Encoding.UTF8);
xmlwrite.Formatting = Formatting.Indented;
xmlwrite.Indentation = 2;
Scripter.WriteAlter(xmlwrite, server.Databases["MyDatabaseName"], true, true);
xmlwrite.Close();
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
How to take backup of SQL Server 2000 and 2005
Hi,
Good morning to All,
I have SQL Server 2005 installed on my system. Recently I have formatted by computer. But I couldn't take backup of my data. Big loss.
For this, I've googled also. but I couldn't get clear information.
I want like the following:
I could able to backup entire the Database either into my pen-drive, or some other media.
And,latter I could able to put the Data back into my system.
Can anyone please tell how to take backup of my Data, and Restore also.
Thanks in advance,
Ashok kumar.
You can do the backup/restore using SQL Management Studio. If you are using 2005 Express Edition, you will need the Express Edition of the management studio. Do a quick search on download.microsoft.com for Microsoft SQL Server Management Studio Express (SSMSE).
Once you have it loaded, login and right-click on the database. Under 'tasks' is the option to create the backups and perform the restores.
How to Take Backup from Command prompt
Hi all,
Please tell me how to take MS SQL backup from command
prompt.
With regards,
Anna Patil
Generate the BACKUP command you want to run (see the BACKUP topic in Books
Online for syntax and usage). Then, you can save this script in a .sql
file, and run it using osql from the command line. osql has its own topic
in Books Online too, so you can learn how to use it there...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Anna Patil" <anna.patil@.ddsl.net> wrote in message
news:11ee001c4425e$b666b580$a301280a@.phx.gbl...
> Hi all,
> Please tell me how to take MS SQL backup from command
> prompt.
> With regards,
> Anna Patil
|||Hi,
From command prompt execute the below command:-
OSQL -Usa -Ppassword -Sservername -Q"backup database pubs to
disk='c:\pubs.bak' with init"
(replace the database pubs with actual database)
Thanks
Hari
MCDBA
"Anna Patil" <anna.patil@.ddsl.net> wrote in message
news:11ee001c4425e$b666b580$a301280a@.phx.gbl...
> Hi all,
> Please tell me how to take MS SQL backup from command
> prompt.
> With regards,
> Anna Patil
Please tell me how to take MS SQL backup from command
prompt.
With regards,
Anna Patil
Generate the BACKUP command you want to run (see the BACKUP topic in Books
Online for syntax and usage). Then, you can save this script in a .sql
file, and run it using osql from the command line. osql has its own topic
in Books Online too, so you can learn how to use it there...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Anna Patil" <anna.patil@.ddsl.net> wrote in message
news:11ee001c4425e$b666b580$a301280a@.phx.gbl...
> Hi all,
> Please tell me how to take MS SQL backup from command
> prompt.
> With regards,
> Anna Patil
|||Hi,
From command prompt execute the below command:-
OSQL -Usa -Ppassword -Sservername -Q"backup database pubs to
disk='c:\pubs.bak' with init"
(replace the database pubs with actual database)
Thanks
Hari
MCDBA
"Anna Patil" <anna.patil@.ddsl.net> wrote in message
news:11ee001c4425e$b666b580$a301280a@.phx.gbl...
> Hi all,
> Please tell me how to take MS SQL backup from command
> prompt.
> With regards,
> Anna Patil
How to Take Backup from Command prompt
Hi all,
Please tell me how to take MS SQL backup from command
prompt.
With regards,
Anna PatilGenerate the BACKUP command you want to run (see the BACKUP topic in Books
Online for syntax and usage). Then, you can save this script in a .sql
file, and run it using osql from the command line. osql has its own topic
in Books Online too, so you can learn how to use it there...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Anna Patil" <anna.patil@.ddsl.net> wrote in message
news:11ee001c4425e$b666b580$a301280a@.phx
.gbl...
> Hi all,
> Please tell me how to take MS SQL backup from command
> prompt.
> With regards,
> Anna Patil|||Hi,
From command prompt execute the below command:-
OSQL -Usa -Ppassword -Sservername -Q"backup database pubs to
disk='c:\pubs.bak' with init"
(replace the database pubs with actual database)
Thanks
Hari
MCDBA
"Anna Patil" <anna.patil@.ddsl.net> wrote in message
news:11ee001c4425e$b666b580$a301280a@.phx
.gbl...
> Hi all,
> Please tell me how to take MS SQL backup from command
> prompt.
> With regards,
> Anna Patil
Please tell me how to take MS SQL backup from command
prompt.
With regards,
Anna PatilGenerate the BACKUP command you want to run (see the BACKUP topic in Books
Online for syntax and usage). Then, you can save this script in a .sql
file, and run it using osql from the command line. osql has its own topic
in Books Online too, so you can learn how to use it there...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Anna Patil" <anna.patil@.ddsl.net> wrote in message
news:11ee001c4425e$b666b580$a301280a@.phx
.gbl...
> Hi all,
> Please tell me how to take MS SQL backup from command
> prompt.
> With regards,
> Anna Patil|||Hi,
From command prompt execute the below command:-
OSQL -Usa -Ppassword -Sservername -Q"backup database pubs to
disk='c:\pubs.bak' with init"
(replace the database pubs with actual database)
Thanks
Hari
MCDBA
"Anna Patil" <anna.patil@.ddsl.net> wrote in message
news:11ee001c4425e$b666b580$a301280a@.phx
.gbl...
> Hi all,
> Please tell me how to take MS SQL backup from command
> prompt.
> With regards,
> Anna Patil
How to Take Backup from Command prompt
Hi all,
Please tell me how to take MS SQL backup from command
prompt.
With regards,
Anna PatilGenerate the BACKUP command you want to run (see the BACKUP topic in Books
Online for syntax and usage). Then, you can save this script in a .sql
file, and run it using osql from the command line. osql has its own topic
in Books Online too, so you can learn how to use it there...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Anna Patil" <anna.patil@.ddsl.net> wrote in message
news:11ee001c4425e$b666b580$a301280a@.phx.gbl...
> Hi all,
> Please tell me how to take MS SQL backup from command
> prompt.
> With regards,
> Anna Patil|||Hi,
From command prompt execute the below command:-
OSQL -Usa -Ppassword -Sservername -Q"backup database pubs to
disk='c:\pubs.bak' with init"
(replace the database pubs with actual database)
Thanks
Hari
MCDBA
"Anna Patil" <anna.patil@.ddsl.net> wrote in message
news:11ee001c4425e$b666b580$a301280a@.phx.gbl...
> Hi all,
> Please tell me how to take MS SQL backup from command
> prompt.
> With regards,
> Anna Patil
Please tell me how to take MS SQL backup from command
prompt.
With regards,
Anna PatilGenerate the BACKUP command you want to run (see the BACKUP topic in Books
Online for syntax and usage). Then, you can save this script in a .sql
file, and run it using osql from the command line. osql has its own topic
in Books Online too, so you can learn how to use it there...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Anna Patil" <anna.patil@.ddsl.net> wrote in message
news:11ee001c4425e$b666b580$a301280a@.phx.gbl...
> Hi all,
> Please tell me how to take MS SQL backup from command
> prompt.
> With regards,
> Anna Patil|||Hi,
From command prompt execute the below command:-
OSQL -Usa -Ppassword -Sservername -Q"backup database pubs to
disk='c:\pubs.bak' with init"
(replace the database pubs with actual database)
Thanks
Hari
MCDBA
"Anna Patil" <anna.patil@.ddsl.net> wrote in message
news:11ee001c4425e$b666b580$a301280a@.phx.gbl...
> Hi all,
> Please tell me how to take MS SQL backup from command
> prompt.
> With regards,
> Anna Patil
How to take back up of DTS packages ?
Hi,
Is there any way to take backup of DTS packages ?
Thanks,I saved my DTS packages in SQL Server. So, if I go to DTS node I can see all
my packages.
If I restore msdb, will DTS packages reflect in my SQL server like it used
to be ?
Thanks
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1KadnQGCK_zo7u6iRVn-jw@.giganews.com...
> They are stored in MSDB. Backup MSDB.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
> "Vgs" <generalofmine@.yahoo.com> wrote in message
> news:%23hFSe76gDHA.2292@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > Is there any way to take backup of DTS packages ?
> >
> > Thanks,
> >
> >
>|||Your DTS Package saved in the msdb database, you need a
backup msdb database
>--Original Message--
>Hi,
>Is there any way to take backup of DTS packages ?
>Thanks,
>
>.
>|||Also checkout this utility
Backing Up Packages
(http://www.sqldts.com/default.aspx?202)
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:4p6dnWJ9t6iPkemiRVn-uA@.giganews.com...
> Yes. MSDB also contains your SQL Agent jobs, schedules, etc so these will
be
> restored as well.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
> "Vgs" <generalofmine@.yahoo.com> wrote in message
> news:OdUHJT%23gDHA.1668@.TK2MSFTNGP12.phx.gbl...
> > I saved my DTS packages in SQL Server. So, if I go to DTS node I can see
> all
> > my packages.
> > If I restore msdb, will DTS packages reflect in my SQL server like it
> used
> > to be ?
> >
> > Thanks
> >
> >
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> > news:1KadnQGCK_zo7u6iRVn-jw@.giganews.com...
> > > They are stored in MSDB. Backup MSDB.
> > >
> > > --
> > > David Portas
> > > --
> > > Please reply only to the newsgroup
> > > --
> > >
> > > "Vgs" <generalofmine@.yahoo.com> wrote in message
> > > news:%23hFSe76gDHA.2292@.TK2MSFTNGP10.phx.gbl...
> > > > Hi,
> > > >
> > > > Is there any way to take backup of DTS packages ?
> > > >
> > > > Thanks,
> > > >
> > > >
> > >
> > >
> >
> >
>
Is there any way to take backup of DTS packages ?
Thanks,I saved my DTS packages in SQL Server. So, if I go to DTS node I can see all
my packages.
If I restore msdb, will DTS packages reflect in my SQL server like it used
to be ?
Thanks
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1KadnQGCK_zo7u6iRVn-jw@.giganews.com...
> They are stored in MSDB. Backup MSDB.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
> "Vgs" <generalofmine@.yahoo.com> wrote in message
> news:%23hFSe76gDHA.2292@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > Is there any way to take backup of DTS packages ?
> >
> > Thanks,
> >
> >
>|||Your DTS Package saved in the msdb database, you need a
backup msdb database
>--Original Message--
>Hi,
>Is there any way to take backup of DTS packages ?
>Thanks,
>
>.
>|||Also checkout this utility
Backing Up Packages
(http://www.sqldts.com/default.aspx?202)
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:4p6dnWJ9t6iPkemiRVn-uA@.giganews.com...
> Yes. MSDB also contains your SQL Agent jobs, schedules, etc so these will
be
> restored as well.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
> "Vgs" <generalofmine@.yahoo.com> wrote in message
> news:OdUHJT%23gDHA.1668@.TK2MSFTNGP12.phx.gbl...
> > I saved my DTS packages in SQL Server. So, if I go to DTS node I can see
> all
> > my packages.
> > If I restore msdb, will DTS packages reflect in my SQL server like it
> used
> > to be ?
> >
> > Thanks
> >
> >
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> > news:1KadnQGCK_zo7u6iRVn-jw@.giganews.com...
> > > They are stored in MSDB. Backup MSDB.
> > >
> > > --
> > > David Portas
> > > --
> > > Please reply only to the newsgroup
> > > --
> > >
> > > "Vgs" <generalofmine@.yahoo.com> wrote in message
> > > news:%23hFSe76gDHA.2292@.TK2MSFTNGP10.phx.gbl...
> > > > Hi,
> > > >
> > > > Is there any way to take backup of DTS packages ?
> > > >
> > > > Thanks,
> > > >
> > > >
> > >
> > >
> >
> >
>
Monday, March 12, 2012
How to sync local database with Network database
Hi
Scenario: In general user will take database backup from database server to local system. User perform some changes to his local database based on his requirement. Again user connects to database server and click on update, local database should update to server database by informing each record status to the user. ex: record 1 updated / call closed do you want to update?
Here local database is going to be Pocket pc database and server database is on SQLDatabase. Can any one suggest best way to implement this feature in Windows Pocket pc, c#. I would appreciate your reply.
Thanks,
Replication could be used to keep the databases syncronized.
However, with your desire for user interaction with the process, I think that you are going to have to have a custom application.
As far as I am aware, there is no 'simple' process in SQL Server that would allow "informing each record status to the user. ex: record 1 updated / call closed do you want to update". I don't think that you would want users to have access to (and permissions for), the SQL Client Tools, particularly, SSMS.
|||
Subscribe to:
Posts (Atom)