Export Empty Database

This is kind of a elementary question, but how do you export an empty copy of a SQL 2005 database to a backup file? Basically I need a backup without any of the data. Thanks!what you want is just the scripts for the objects then.

you can use GSW for this, or this console app: http://www.codeplex.com/scriptdb|||I think I already accomplished this using SQL Management Studio. Is that all I need?|||yea, that works. that's what I meant by GSW - "generate scripts wizard" in ssms.

Export database from SQL server 2005 to 2000

Hi

I need to transfer schema and data from SQL server 2005 to SQL server 2000. Backup had been perfect, but a backupfile made by SQL server 2005 can't be read in SQL server 2000. I seach for a solution but I did not found any.

Is there someone that have a solution?

Hi,

either make an import from the 2k server or an export from the 2k5 server via DTS Assistent. Another but more complicated way would be to create scripts of your database objects and data and execute them on the 2k system. But prior to that you have to make sure that they are compatible with the 2k system.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
sql

Expired Backup Files

Hello.
I can't get SQL to delete the old backup and transaction
files.
I was told that there is a bug with Windows 95, 98 or ME
but that is not my case. I have SQL 2000 running on
Windows 2000.
What can I do?
Thanx.
Hector,
There was a thread on this recently which can be viewed here:
Subject: Re: Maintenence Plan
http://tinyurl.com/5plnm
Does that help?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Hector wrote:
> Hello.
> I can't get SQL to delete the old backup and transaction
> files.
> I was told that there is a bug with Windows 95, 98 or ME
> but that is not my case. I have SQL 2000 running on
> Windows 2000.
> What can I do?
> Thanx.
>
|||I read it and it could be my situation.
Thanx Mark!

>--Original Message--
>Hector,
>There was a thread on this recently which can be viewed
here:[vbcol=seagreen]
>Subject: Re: Maintenence Plan
>http://tinyurl.com/5plnm
>Does that help?
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>Hector wrote:
transaction
>.
>

Expired Backup Files

Hello.
I can't get SQL to delete the old backup and transaction
files.
I was told that there is a bug with Windows 95, 98 or ME
but that is not my case. I have SQL 2000 running on
Windows 2000.
What can I do?
Thanx.Hector,
There was a thread on this recently which can be viewed here:
Subject: Re: Maintenence Plan
http://tinyurl.com/5plnm
Does that help?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Hector wrote:
> Hello.
> I can't get SQL to delete the old backup and transaction
> files.
> I was told that there is a bug with Windows 95, 98 or ME
> but that is not my case. I have SQL 2000 running on
> Windows 2000.
> What can I do?
> Thanx.
>|||I read it and it could be my situation.
Thanx Mark!
>--Original Message--
>Hector,
>There was a thread on this recently which can be viewed
here:
>Subject: Re: Maintenence Plan
>http://tinyurl.com/5plnm
>Does that help?
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>Hector wrote:
>> Hello.
>> I can't get SQL to delete the old backup and
transaction
>> files.
>> I was told that there is a bug with Windows 95, 98 or ME
>> but that is not my case. I have SQL 2000 running on
>> Windows 2000.
>> What can I do?
>> Thanx.
>.
>

Expired Backup Files

Hello.
I can't get SQL to delete the old backup and transaction
files.
I was told that there is a bug with Windows 95, 98 or ME
but that is not my case. I have SQL 2000 running on
Windows 2000.
What can I do?
Thanx.Hector,
There was a thread on this recently which can be viewed here:
Subject: Re: Maintenence Plan
http://tinyurl.com/5plnm
Does that help?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Hector wrote:
> Hello.
> I can't get SQL to delete the old backup and transaction
> files.
> I was told that there is a bug with Windows 95, 98 or ME
> but that is not my case. I have SQL 2000 running on
> Windows 2000.
> What can I do?
> Thanx.
>|||I read it and it could be my situation.
Thanx Mark!

>--Original Message--
>Hector,
>There was a thread on this recently which can be viewed
here:
>Subject: Re: Maintenence Plan
>http://tinyurl.com/5plnm
>Does that help?
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>Hector wrote:
transaction[vbcol=seagreen]
>.
>

Expire existing backup sets in a backup device

Hi,
Is it possible to remove or expire backup sets from a backup device
which don't currently have an expiry?
We have some backup devices which contain lots of transaction logs
which need removing. We now specify RETAIN_DAYS as part of the backup
command.
Need to do this in both SQL2K & SQL 2005 (Standard)
Thanks
Dave
Hi,
I read somewhere that media retention could be used to control this
but could not find out how to set it.
I guess you are saying take the backup device (file) top take then run
a single backup with INIT to clear out the entires.?
Dave
On Fri, 9 Feb 2007 10:43:13 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>RETAIN_DAYS and EXPIREDATE has nothing to do with automatic removing of old backups. In fact, you
>cannot even remove old backups from a backup device while keeping newer ones (INIT is all or
>nothing). Consider writing scripts or using maint plans so you have several backup files and the
>oldest ones are re-used with INIT or deleted.
|||>> I guess you are saying take the backup device (file) top take then run
>I'm sorry, but I didn't get that... Can you re-phrase?
>
Yes it was complete jibberish wasnt it :-)
I meant to say move the backup device (file) onto tape then run a
backup INIT to clear the old entires.

>Say you have a backup device with, say, 10 backups on it. You can not remove only 5 of those. You
>can overwrite the lot using INIT, or append using NOINIT (default). RETAINDAYS etc does not affect
>this.
Ok I understand.
Thanks for your help
Dave

Expire existing backup sets in a backup device

Hi,
Is it possible to remove or expire backup sets from a backup device
which don't currently have an expiry?
We have some backup devices which contain lots of transaction logs
which need removing. We now specify RETAIN_DAYS as part of the backup
command.
Need to do this in both SQL2K & SQL 2005 (Standard)
Thanks
DaveRETAIN_DAYS and EXPIREDATE has nothing to do with automatic removing of old
backups. In fact, you
cannot even remove old backups from a backup device while keeping newer ones
(INIT is all or
nothing). Consider writing scripts or using maint plans so you have several
backup files and the
oldest ones are re-used with INIT or deleted.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"David Gray" <grayd@.turpin-distribution.com> wrote in message
news:treos25nhk0gcgrm851bo3rejdvjl8542u@.
4ax.com...
> Hi,
> Is it possible to remove or expire backup sets from a backup device
> which don't currently have an expiry?
> We have some backup devices which contain lots of transaction logs
> which need removing. We now specify RETAIN_DAYS as part of the backup
> command.
> Need to do this in both SQL2K & SQL 2005 (Standard)
> Thanks
> Dave|||Hi,
I read somewhere that media retention could be used to control this
but could not find out how to set it.
I guess you are saying take the backup device (file) top take then run
a single backup with INIT to clear out the entires.?
Dave
On Fri, 9 Feb 2007 10:43:13 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>RETAIN_DAYS and EXPIREDATE has nothing to do with automatic removing of old
backups. In fact, you
>cannot even remove old backups from a backup device while keeping newer one
s (INIT is all or
>nothing). Consider writing scripts or using maint plans so you have several
backup files and the
>oldest ones are re-used with INIT or deleted.|||> I guess you are saying take the backup device (file) top take then run
> a single backup with INIT to clear out the entires.?
I'm sorry, but I didn't get that... Can you re-phrase?
Say you have a backup device with, say, 10 backups on it. You can not remove
only 5 of those. You
can overwrite the lot using INIT, or append using NOINIT (default). RETAINDA
YS etc does not affect
this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"David Gray" <grayd@.turpin-distribution.com> wrote in message
news:n3mos2t0ip5enqpr2h8tibk43ktq79d9rt@.
4ax.com...[vbcol=seagreen]
> Hi,
> I read somewhere that media retention could be used to control this
> but could not find out how to set it.
> I guess you are saying take the backup device (file) top take then run
> a single backup with INIT to clear out the entires.?
> Dave
>
> On Fri, 9 Feb 2007 10:43:13 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>|||>> I guess you are saying take the backup device (file) top take then run
>I'm sorry, but I didn't get that... Can you re-phrase?
>
Yes it was complete jibberish wasnt it :-)
I meant to say move the backup device (file) onto tape then run a
backup INIT to clear the old entires.

>Say you have a backup device with, say, 10 backups on it. You can not remov
e only 5 of those. You
>can overwrite the lot using INIT, or append using NOINIT (default). RETAIND
AYS etc does not affect
>this.
Ok I understand.
Thanks for your help
Dave

Expire existing backup sets in a backup device

Hi,
Is it possible to remove or expire backup sets from a backup device
which don't currently have an expiry?
We have some backup devices which contain lots of transaction logs
which need removing. We now specify RETAIN_DAYS as part of the backup
command.
Need to do this in both SQL2K & SQL 2005 (Standard)
Thanks
DaveRETAIN_DAYS and EXPIREDATE has nothing to do with automatic removing of old backups. In fact, you
cannot even remove old backups from a backup device while keeping newer ones (INIT is all or
nothing). Consider writing scripts or using maint plans so you have several backup files and the
oldest ones are re-used with INIT or deleted.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"David Gray" <grayd@.turpin-distribution.com> wrote in message
news:treos25nhk0gcgrm851bo3rejdvjl8542u@.4ax.com...
> Hi,
> Is it possible to remove or expire backup sets from a backup device
> which don't currently have an expiry?
> We have some backup devices which contain lots of transaction logs
> which need removing. We now specify RETAIN_DAYS as part of the backup
> command.
> Need to do this in both SQL2K & SQL 2005 (Standard)
> Thanks
> Dave|||Hi,
I read somewhere that media retention could be used to control this
but could not find out how to set it.
I guess you are saying take the backup device (file) top take then run
a single backup with INIT to clear out the entires.?
Dave
On Fri, 9 Feb 2007 10:43:13 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>RETAIN_DAYS and EXPIREDATE has nothing to do with automatic removing of old backups. In fact, you
>cannot even remove old backups from a backup device while keeping newer ones (INIT is all or
>nothing). Consider writing scripts or using maint plans so you have several backup files and the
>oldest ones are re-used with INIT or deleted.|||> I guess you are saying take the backup device (file) top take then run
> a single backup with INIT to clear out the entires.?
I'm sorry, but I didn't get that... Can you re-phrase?
Say you have a backup device with, say, 10 backups on it. You can not remove only 5 of those. You
can overwrite the lot using INIT, or append using NOINIT (default). RETAINDAYS etc does not affect
this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"David Gray" <grayd@.turpin-distribution.com> wrote in message
news:n3mos2t0ip5enqpr2h8tibk43ktq79d9rt@.4ax.com...
> Hi,
> I read somewhere that media retention could be used to control this
> but could not find out how to set it.
> I guess you are saying take the backup device (file) top take then run
> a single backup with INIT to clear out the entires.?
> Dave
>
> On Fri, 9 Feb 2007 10:43:13 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>>RETAIN_DAYS and EXPIREDATE has nothing to do with automatic removing of old backups. In fact, you
>>cannot even remove old backups from a backup device while keeping newer ones (INIT is all or
>>nothing). Consider writing scripts or using maint plans so you have several backup files and the
>>oldest ones are re-used with INIT or deleted.|||>> I guess you are saying take the backup device (file) top take then run
>> a single backup with INIT to clear out the entires.?
>I'm sorry, but I didn't get that... Can you re-phrase?
>
Yes it was complete jibberish wasnt it :-)
I meant to say move the backup device (file) onto tape then run a
backup INIT to clear the old entires.
>Say you have a backup device with, say, 10 backups on it. You can not remove only 5 of those. You
>can overwrite the lot using INIT, or append using NOINIT (default). RETAINDAYS etc does not affect
>this.
Ok I understand.
Thanks for your help
Dave

Expert help !

I just want to confirm from you that is this is the good practise if I can
take full backup which size is 4 GB and log backup into another system
instead of same system... right now what I am thinking is the size of the
backup is too larget so the network would be too jam if the full backup
would be take place ? need your idea.
Thanks
Hi,
If you have enough hard disk space, it is always recomended to perform full
database backup in to the local system.
This will complete the backup very fast and will not create network issues.
After the backup process you could
copy the .BAK file to remote machine.
Thanks
Hari
SQL Server MVP
"Joh" <joh@.mailcity.com> wrote in message
news:%23snt5iwWFHA.2700@.TK2MSFTNGP12.phx.gbl...
>I just want to confirm from you that is this is the good practise if I can
> take full backup which size is 4 GB and log backup into another system
> instead of same system... right now what I am thinking is the size of the
> backup is too larget so the network would be too jam if the full backup
> would be take place ? need your idea.
> Thanks
>
|||if you can compare then how much would be difference ... 50% delay or 20%
like if you campare to both backup into same machine and backup into another
machine....just for idea.
Thanks
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eM55cuwWFHA.2348@.TK2MSFTNGP14.phx.gbl...
> Hi,
> If you have enough hard disk space, it is always recomended to perform
full
> database backup in to the local system.
> This will complete the backup very fast and will not create network
issues.[vbcol=seagreen]
> After the backup process you could
> copy the .BAK file to remote machine.
> Thanks
> Hari
> SQL Server MVP
>
> "Joh" <joh@.mailcity.com> wrote in message
> news:%23snt5iwWFHA.2700@.TK2MSFTNGP12.phx.gbl...
can[vbcol=seagreen]
the
>
|||Hi,
That depends up on your Network speed and disk controller in the remote
server. As a trial you could try once by backup the database in your local
server once and do a backup across the network once. This will give you the
exact picture on timing.
Thanks
Hari
SQL Server MVP
"Joh" <joh@.mailcity.com> wrote in message
news:uHY2bzwWFHA.2080@.TK2MSFTNGP15.phx.gbl...
> if you can compare then how much would be difference ... 50% delay or 20%
> like if you campare to both backup into same machine and backup into
> another
> machine....just for idea.
> Thanks
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eM55cuwWFHA.2348@.TK2MSFTNGP14.phx.gbl...
> full
> issues.
> can
> the
>

Expected LSN of a standby DB

thanks john, but RESTORE HEADERONLY will give me the LSN range in the backup
file.
I'm after the LSN a standby database is expecting next...
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:AEFF783B-174E-493D-8CB1-83625E9D792F@.microsoft.com...
> Hi Peter
>
> RESTORE HEADERONLY will give you the LSN range.
>
> John
>
> "Peter Reid" wrote:
>
>> Is it possible to query the next expected LSN from a DB in
>> recovery/standby?
>>
>> I know that if you try to restore a log backup out of sequence, SQL
>> Server
>> throws error #4326 which will tell you the LSN you just tried to restore
>> and
>> the expected one, but is it possible to get this info without having to
>> attempt a log restore?
>>
>> ThanksHi
You may want DBCC DBINFO, this is undocumented
See
http://www.sql-server-performance.com/ac_sql_server_2000_undocumented_dbcc.asp
John
"Peter Reid" wrote:
> thanks john, but RESTORE HEADERONLY will give me the LSN range in the backup
> file.
> I'm after the LSN a standby database is expecting next...
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:AEFF783B-174E-493D-8CB1-83625E9D792F@.microsoft.com...
> > Hi Peter
> >
> > RESTORE HEADERONLY will give you the LSN range.
> >
> > John
> >
> > "Peter Reid" wrote:
> >
> >> Is it possible to query the next expected LSN from a DB in
> >> recovery/standby?
> >>
> >> I know that if you try to restore a log backup out of sequence, SQL
> >> Server
> >> throws error #4326 which will tell you the LSN you just tried to restore
> >> and
> >> the expected one, but is it possible to get this info without having to
> >> attempt a log restore?
> >>
> >> Thanks
>
>

Expected LSN

Is it possible to query the next expected LSN from a DB in recovery/standby?
I know that if you try to restore a log backup out of sequence, SQL Server
throws error #4326 which will tell you the LSN you just tried to restore and
the expected one, but is it possible to get this info without having to
attempt a log restore?
Thanks
Hi Peter
RESTORE HEADERONLY will give you the LSN range.
John
"Peter Reid" wrote:

> Is it possible to query the next expected LSN from a DB in recovery/standby?
> I know that if you try to restore a log backup out of sequence, SQL Server
> throws error #4326 which will tell you the LSN you just tried to restore and
> the expected one, but is it possible to get this info without having to
> attempt a log restore?
> Thanks
>
>

Expected LSN

Is it possible to query the next expected LSN from a DB in recovery/standby?
I know that if you try to restore a log backup out of sequence, SQL Server
throws error #4326 which will tell you the LSN you just tried to restore and
the expected one, but is it possible to get this info without having to
attempt a log restore?
ThanksHi Peter
RESTORE HEADERONLY will give you the LSN range.
John
"Peter Reid" wrote:
> Is it possible to query the next expected LSN from a DB in recovery/standby?
> I know that if you try to restore a log backup out of sequence, SQL Server
> throws error #4326 which will tell you the LSN you just tried to restore and
> the expected one, but is it possible to get this info without having to
> attempt a log restore?
> Thanks
>
>

Expected LSN

Is it possible to query the next expected LSN from a DB in recovery/standby?
I know that if you try to restore a log backup out of sequence, SQL Server
throws error #4326 which will tell you the LSN you just tried to restore and
the expected one, but is it possible to get this info without having to
attempt a log restore?
ThanksHi Peter
RESTORE HEADERONLY will give you the LSN range.
John
"Peter Reid" wrote:

> Is it possible to query the next expected LSN from a DB in recovery/standb
y?
> I know that if you try to restore a log backup out of sequence, SQL Server
> throws error #4326 which will tell you the LSN you just tried to restore a
nd
> the expected one, but is it possible to get this info without having to
> attempt a log restore?
> Thanks
>
>