I'm working on an algorithm to backup data from pocket pc sql server 2.0
database involved in an merge replication with sql server 2000 sp3 with rda
in case that replication fails from whatever reason. My question is if I can
determine on a sql server ce 2.0 table involved in merge replication what
rows have been updated or inserted form last replication in order to
transfer only those rows on backup database. I've noticed that inserted rows
have s_Generation field 0 but I have no clue about updated rows.
Any changed (inserted or updated) row in ce database which is part of a
replicated table will have the s_generation column set to zero until the
next sync. So this is just an indicator of changed rows between two
successive syncs.
thanks
gopal
Showing posts with label backup. Show all posts
Showing posts with label backup. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Determine last backup?
If I back up a database without using a maintenance plan, is there a query
that can be made to determine when the last full backup of that database
occurred?
Thanks in advance!Try:
select top 1
backup_finish_date
from
msdb.dbo.backupset
where
database_name = 'Northwind'
and type = 'D'
order by
backup_finish_date desc
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Jim Norton" <jim.norton@.joe.com> wrote in message
news:%237SbYsn%23FHA.1312@.TK2MSFTNGP09.phx.gbl...
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
>|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '''
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '''
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !
that can be made to determine when the last full backup of that database
occurred?
Thanks in advance!Try:
select top 1
backup_finish_date
from
msdb.dbo.backupset
where
database_name = 'Northwind'
and type = 'D'
order by
backup_finish_date desc
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Jim Norton" <jim.norton@.joe.com> wrote in message
news:%237SbYsn%23FHA.1312@.TK2MSFTNGP09.phx.gbl...
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
>|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '''
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '''
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !
Determine last backup?
If I back up a database without using a maintenance plan, is there a query
that can be made to determine when the last full backup of that database
occurred?
Thanks in advance!Try:
select top 1
backup_finish_date
from
msdb.dbo.backupset
where
database_name = 'Northwind'
and type = 'D'
order by
backup_finish_date desc
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Jim Norton" <jim.norton@.joe.com> wrote in message
news:%237SbYsn%23FHA.1312@.TK2MSFTNGP09.phx.gbl...
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
>|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102
) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '''
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102
) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '''
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !
that can be made to determine when the last full backup of that database
occurred?
Thanks in advance!Try:
select top 1
backup_finish_date
from
msdb.dbo.backupset
where
database_name = 'Northwind'
and type = 'D'
order by
backup_finish_date desc
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Jim Norton" <jim.norton@.joe.com> wrote in message
news:%237SbYsn%23FHA.1312@.TK2MSFTNGP09.phx.gbl...
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
>|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102
) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '''
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102
) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '''
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !
Determine last backup?
If I back up a database without using a maintenance plan, is there a query
that can be made to determine when the last full backup of that database
occurred?
Thanks in advance!
Try:
select top 1
backup_finish_date
from
msdb.dbo.backupset
where
database_name = 'Northwind'
and type = 'D'
order by
backup_finish_date desc
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Jim Norton" <jim.norton@.joe.com> wrote in message
news:%237SbYsn%23FHA.1312@.TK2MSFTNGP09.phx.gbl...
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
>
|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '?'
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !
|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '?'
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !
that can be made to determine when the last full backup of that database
occurred?
Thanks in advance!
Try:
select top 1
backup_finish_date
from
msdb.dbo.backupset
where
database_name = 'Northwind'
and type = 'D'
order by
backup_finish_date desc
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Jim Norton" <jim.norton@.joe.com> wrote in message
news:%237SbYsn%23FHA.1312@.TK2MSFTNGP09.phx.gbl...
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
>
|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '?'
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !
|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '?'
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !
Sunday, March 25, 2012
Determine first log backup after db backup
Hello,
How can I check if backup log fiile is first log backup after database
backup ?
f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
and 10 log backup files ( BACKUP LOG x TO DISK='...)
I want to be sure that noone have removed the fist log file. using T-SQL
To create order in backkup log files i'm using FistLSN and LastLSN collumns
of RESTORE HEADERONLY procedure. But i dont know how to connect them to
database backup.
Any ideas ?
Best Regards
Wojciech Znaniecki
Try checking table backupset in database msdb. Something like:
select
backup_set_id,
type,
backup_start_date,
backup_finish_date
from
msdb..backupset
where
database_name = 'your_db'
and type = 'L'
and backup_start_date > (select top 1 backup_finish_date from
msdb..backupset where database_name = 'your_db' and type = 'D' order by
backup_start_date desc)
order by
backup_start_date;
AMB
"Wojtek Z" wrote:
> Hello,
> How can I check if backup log fiile is first log backup after database
> backup ?
> f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
> and 10 log backup files ( BACKUP LOG x TO DISK='...)
> I want to be sure that noone have removed the fist log file. using T-SQL
> To create order in backkup log files i'm using FistLSN and LastLSN collumns
> of RESTORE HEADERONLY procedure. But i dont know how to connect them to
> database backup.
> Any ideas ?
> --
> Best Regards
> Wojciech Znaniecki
>
>
|||Thanks - i havent known that. But how about restoring db to a different
server ?
Best Regards,
Wojciech Znaniecki
Uytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com>
napisa w wiadomoci
news:9DBEE13B-A899-4BF5-8E29-DA24F801F335@.microsoft.com...[vbcol=seagreen]
> Try checking table backupset in database msdb. Something like:
> select
> backup_set_id,
> type,
> backup_start_date,
> backup_finish_date
> from
> msdb..backupset
> where
> database_name = 'your_db'
> and type = 'L'
> and backup_start_date > (select top 1 backup_finish_date from
> msdb..backupset where database_name = 'your_db' and type = 'D' order by
> backup_start_date desc)
> order by
> backup_start_date;
>
> AMB
> "Wojtek Z" wrote:
[vbcol=seagreen]
collumns[vbcol=seagreen]
|||My guess is that the first log backup's FirstLsn need to be prior to the database backup's FirstLsn.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wojtek Z" <wojtas_z@.poczta.fm> wrote in message news:d5upv9$174$1@.nemesis.news.tpi.pl...
> Thanks - i havent known that. But how about restoring db to a different
> server ?
> --
> Best Regards,
> Wojciech Znaniecki
> Uytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com>
> napisa w wiadomoci
> news:9DBEE13B-A899-4BF5-8E29-DA24F801F335@.microsoft.com...
>
> collumns
>
|||Uytkownik "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> napisa w wiadomoci
news:OcqdnSvVFHA.3320@.TK2MSFTNGP12.phx.gbl...
> My guess is that the first log backup's FirstLsn need to be prior to the
database backup's FirstLsn.
Thanks ! thats it.
Best Regards,
Wojciech Znaniecki
How can I check if backup log fiile is first log backup after database
backup ?
f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
and 10 log backup files ( BACKUP LOG x TO DISK='...)
I want to be sure that noone have removed the fist log file. using T-SQL
To create order in backkup log files i'm using FistLSN and LastLSN collumns
of RESTORE HEADERONLY procedure. But i dont know how to connect them to
database backup.
Any ideas ?
Best Regards
Wojciech Znaniecki
Try checking table backupset in database msdb. Something like:
select
backup_set_id,
type,
backup_start_date,
backup_finish_date
from
msdb..backupset
where
database_name = 'your_db'
and type = 'L'
and backup_start_date > (select top 1 backup_finish_date from
msdb..backupset where database_name = 'your_db' and type = 'D' order by
backup_start_date desc)
order by
backup_start_date;
AMB
"Wojtek Z" wrote:
> Hello,
> How can I check if backup log fiile is first log backup after database
> backup ?
> f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
> and 10 log backup files ( BACKUP LOG x TO DISK='...)
> I want to be sure that noone have removed the fist log file. using T-SQL
> To create order in backkup log files i'm using FistLSN and LastLSN collumns
> of RESTORE HEADERONLY procedure. But i dont know how to connect them to
> database backup.
> Any ideas ?
> --
> Best Regards
> Wojciech Znaniecki
>
>
|||Thanks - i havent known that. But how about restoring db to a different
server ?
Best Regards,
Wojciech Znaniecki
Uytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com>
napisa w wiadomoci
news:9DBEE13B-A899-4BF5-8E29-DA24F801F335@.microsoft.com...[vbcol=seagreen]
> Try checking table backupset in database msdb. Something like:
> select
> backup_set_id,
> type,
> backup_start_date,
> backup_finish_date
> from
> msdb..backupset
> where
> database_name = 'your_db'
> and type = 'L'
> and backup_start_date > (select top 1 backup_finish_date from
> msdb..backupset where database_name = 'your_db' and type = 'D' order by
> backup_start_date desc)
> order by
> backup_start_date;
>
> AMB
> "Wojtek Z" wrote:
[vbcol=seagreen]collumns[vbcol=seagreen]
|||My guess is that the first log backup's FirstLsn need to be prior to the database backup's FirstLsn.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wojtek Z" <wojtas_z@.poczta.fm> wrote in message news:d5upv9$174$1@.nemesis.news.tpi.pl...
> Thanks - i havent known that. But how about restoring db to a different
> server ?
> --
> Best Regards,
> Wojciech Znaniecki
> Uytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com>
> napisa w wiadomoci
> news:9DBEE13B-A899-4BF5-8E29-DA24F801F335@.microsoft.com...
>
> collumns
>
|||Uytkownik "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> napisa w wiadomoci
news:OcqdnSvVFHA.3320@.TK2MSFTNGP12.phx.gbl...
> My guess is that the first log backup's FirstLsn need to be prior to the
database backup's FirstLsn.
Thanks ! thats it.
Best Regards,
Wojciech Znaniecki
Determine first log backup after db backup
Hello,
How can I check if backup log fiile is first log backup after database
backup ?
f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
and 10 log backup files ( BACKUP LOG x TO DISK='...)
I want to be sure that noone have removed the fist log file. using T-SQL :)
To create order in backkup log files i'm using FistLSN and LastLSN collumns
of RESTORE HEADERONLY procedure. But i dont know how to connect them to
database backup.
Any ideas ?
--
Best Regards
Wojciech ZnanieckiTry checking table backupset in database msdb. Something like:
select
backup_set_id,
type,
backup_start_date,
backup_finish_date
from
msdb..backupset
where
database_name = 'your_db'
and type = 'L'
and backup_start_date > (select top 1 backup_finish_date from
msdb..backupset where database_name = 'your_db' and type = 'D' order by
backup_start_date desc)
order by
backup_start_date;
AMB
"Wojtek Z" wrote:
> Hello,
> How can I check if backup log fiile is first log backup after database
> backup ?
> f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
> and 10 log backup files ( BACKUP LOG x TO DISK='...)
> I want to be sure that noone have removed the fist log file. using T-SQL :)
> To create order in backkup log files i'm using FistLSN and LastLSN collumns
> of RESTORE HEADERONLY procedure. But i dont know how to connect them to
> database backup.
> Any ideas ?
> --
> Best Regards
> Wojciech Znaniecki
>
>|||Thanks - i havent known that. But how about restoring db to a different
server ?
--
Best Regards,
Wojciech Znaniecki
U¿ytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com>
napisa³ w wiadomo¶ci
news:9DBEE13B-A899-4BF5-8E29-DA24F801F335@.microsoft.com...
> Try checking table backupset in database msdb. Something like:
> select
> backup_set_id,
> type,
> backup_start_date,
> backup_finish_date
> from
> msdb..backupset
> where
> database_name = 'your_db'
> and type = 'L'
> and backup_start_date > (select top 1 backup_finish_date from
> msdb..backupset where database_name = 'your_db' and type = 'D' order by
> backup_start_date desc)
> order by
> backup_start_date;
>
> AMB
> "Wojtek Z" wrote:
> > Hello,
> > How can I check if backup log fiile is first log backup after database
> > backup ?
> > f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
> > and 10 log backup files ( BACKUP LOG x TO DISK='...)
> > I want to be sure that noone have removed the fist log file. using T-SQL
:)
> >
> > To create order in backkup log files i'm using FistLSN and LastLSN
collumns
> > of RESTORE HEADERONLY procedure. But i dont know how to connect them to
> > database backup.
> > Any ideas ?
> >
> > --
> > Best Regards
> > Wojciech Znaniecki
> >
> >
> >|||My guess is that the first log backup's FirstLsn need to be prior to the database backup's FirstLsn.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wojtek Z" <wojtas_z@.poczta.fm> wrote in message news:d5upv9$174$1@.nemesis.news.tpi.pl...
> Thanks - i havent known that. But how about restoring db to a different
> server ?
> --
> Best Regards,
> Wojciech Znaniecki
> U¿ytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com>
> napisa³ w wiadomo¶ci
> news:9DBEE13B-A899-4BF5-8E29-DA24F801F335@.microsoft.com...
>> Try checking table backupset in database msdb. Something like:
>> select
>> backup_set_id,
>> type,
>> backup_start_date,
>> backup_finish_date
>> from
>> msdb..backupset
>> where
>> database_name = 'your_db'
>> and type = 'L'
>> and backup_start_date > (select top 1 backup_finish_date from
>> msdb..backupset where database_name = 'your_db' and type = 'D' order by
>> backup_start_date desc)
>> order by
>> backup_start_date;
>>
>> AMB
>> "Wojtek Z" wrote:
>> > Hello,
>> > How can I check if backup log fiile is first log backup after database
>> > backup ?
>> > f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
>> > and 10 log backup files ( BACKUP LOG x TO DISK='...)
>> > I want to be sure that noone have removed the fist log file. using T-SQL
> :)
>> >
>> > To create order in backkup log files i'm using FistLSN and LastLSN
> collumns
>> > of RESTORE HEADERONLY procedure. But i dont know how to connect them to
>> > database backup.
>> > Any ideas ?
>> >
>> > --
>> > Best Regards
>> > Wojciech Znaniecki
>> >
>> >
>> >
>|||U¿ytkownik "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> napisa³ w wiadomo¶ci
news:OcqdnSvVFHA.3320@.TK2MSFTNGP12.phx.gbl...
> My guess is that the first log backup's FirstLsn need to be prior to the
database backup's FirstLsn.
Thanks ! thats it.
--
Best Regards,
Wojciech Znaniecki
How can I check if backup log fiile is first log backup after database
backup ?
f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
and 10 log backup files ( BACKUP LOG x TO DISK='...)
I want to be sure that noone have removed the fist log file. using T-SQL :)
To create order in backkup log files i'm using FistLSN and LastLSN collumns
of RESTORE HEADERONLY procedure. But i dont know how to connect them to
database backup.
Any ideas ?
--
Best Regards
Wojciech ZnanieckiTry checking table backupset in database msdb. Something like:
select
backup_set_id,
type,
backup_start_date,
backup_finish_date
from
msdb..backupset
where
database_name = 'your_db'
and type = 'L'
and backup_start_date > (select top 1 backup_finish_date from
msdb..backupset where database_name = 'your_db' and type = 'D' order by
backup_start_date desc)
order by
backup_start_date;
AMB
"Wojtek Z" wrote:
> Hello,
> How can I check if backup log fiile is first log backup after database
> backup ?
> f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
> and 10 log backup files ( BACKUP LOG x TO DISK='...)
> I want to be sure that noone have removed the fist log file. using T-SQL :)
> To create order in backkup log files i'm using FistLSN and LastLSN collumns
> of RESTORE HEADERONLY procedure. But i dont know how to connect them to
> database backup.
> Any ideas ?
> --
> Best Regards
> Wojciech Znaniecki
>
>|||Thanks - i havent known that. But how about restoring db to a different
server ?
--
Best Regards,
Wojciech Znaniecki
U¿ytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com>
napisa³ w wiadomo¶ci
news:9DBEE13B-A899-4BF5-8E29-DA24F801F335@.microsoft.com...
> Try checking table backupset in database msdb. Something like:
> select
> backup_set_id,
> type,
> backup_start_date,
> backup_finish_date
> from
> msdb..backupset
> where
> database_name = 'your_db'
> and type = 'L'
> and backup_start_date > (select top 1 backup_finish_date from
> msdb..backupset where database_name = 'your_db' and type = 'D' order by
> backup_start_date desc)
> order by
> backup_start_date;
>
> AMB
> "Wojtek Z" wrote:
> > Hello,
> > How can I check if backup log fiile is first log backup after database
> > backup ?
> > f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
> > and 10 log backup files ( BACKUP LOG x TO DISK='...)
> > I want to be sure that noone have removed the fist log file. using T-SQL
:)
> >
> > To create order in backkup log files i'm using FistLSN and LastLSN
collumns
> > of RESTORE HEADERONLY procedure. But i dont know how to connect them to
> > database backup.
> > Any ideas ?
> >
> > --
> > Best Regards
> > Wojciech Znaniecki
> >
> >
> >|||My guess is that the first log backup's FirstLsn need to be prior to the database backup's FirstLsn.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wojtek Z" <wojtas_z@.poczta.fm> wrote in message news:d5upv9$174$1@.nemesis.news.tpi.pl...
> Thanks - i havent known that. But how about restoring db to a different
> server ?
> --
> Best Regards,
> Wojciech Znaniecki
> U¿ytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com>
> napisa³ w wiadomo¶ci
> news:9DBEE13B-A899-4BF5-8E29-DA24F801F335@.microsoft.com...
>> Try checking table backupset in database msdb. Something like:
>> select
>> backup_set_id,
>> type,
>> backup_start_date,
>> backup_finish_date
>> from
>> msdb..backupset
>> where
>> database_name = 'your_db'
>> and type = 'L'
>> and backup_start_date > (select top 1 backup_finish_date from
>> msdb..backupset where database_name = 'your_db' and type = 'D' order by
>> backup_start_date desc)
>> order by
>> backup_start_date;
>>
>> AMB
>> "Wojtek Z" wrote:
>> > Hello,
>> > How can I check if backup log fiile is first log backup after database
>> > backup ?
>> > f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
>> > and 10 log backup files ( BACKUP LOG x TO DISK='...)
>> > I want to be sure that noone have removed the fist log file. using T-SQL
> :)
>> >
>> > To create order in backkup log files i'm using FistLSN and LastLSN
> collumns
>> > of RESTORE HEADERONLY procedure. But i dont know how to connect them to
>> > database backup.
>> > Any ideas ?
>> >
>> > --
>> > Best Regards
>> > Wojciech Znaniecki
>> >
>> >
>> >
>|||U¿ytkownik "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> napisa³ w wiadomo¶ci
news:OcqdnSvVFHA.3320@.TK2MSFTNGP12.phx.gbl...
> My guess is that the first log backup's FirstLsn need to be prior to the
database backup's FirstLsn.
Thanks ! thats it.
--
Best Regards,
Wojciech Znaniecki
Determine first log backup after db backup
Hello,
How can I check if backup log fiile is first log backup after database
backup ?
f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
and 10 log backup files ( BACKUP LOG x TO DISK='...)
I want to be sure that noone have removed the fist log file. using T-SQL
To create order in backkup log files i'm using FistLSN and LastLSN collumns
of RESTORE HEADERONLY procedure. But i dont know how to connect them to
database backup.
Any ideas ?
Best Regards
Wojciech ZnanieckiTry checking table backupset in database msdb. Something like:
select
backup_set_id,
type,
backup_start_date,
backup_finish_date
from
msdb..backupset
where
database_name = 'your_db'
and type = 'L'
and backup_start_date > (select top 1 backup_finish_date from
msdb..backupset where database_name = 'your_db' and type = 'D' order by
backup_start_date desc)
order by
backup_start_date;
AMB
"Wojtek Z" wrote:
> Hello,
> How can I check if backup log fiile is first log backup after database
> backup ?
> f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
> and 10 log backup files ( BACKUP LOG x TO DISK='...)
> I want to be sure that noone have removed the fist log file. using T-SQL
> To create order in backkup log files i'm using FistLSN and LastLSN collumn
s
> of RESTORE HEADERONLY procedure. But i dont know how to connect them to
> database backup.
> Any ideas ?
> --
> Best Regards
> Wojciech Znaniecki
>
>|||Thanks - i havent known that. But how about restoring db to a different
server ?
Best Regards,
Wojciech Znaniecki
Uytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com>
napisa w wiadomoci
news:9DBEE13B-A899-4BF5-8E29-DA24F801F335@.microsoft.com...[vbcol=seagreen]
> Try checking table backupset in database msdb. Something like:
> select
> backup_set_id,
> type,
> backup_start_date,
> backup_finish_date
> from
> msdb..backupset
> where
> database_name = 'your_db'
> and type = 'L'
> and backup_start_date > (select top 1 backup_finish_date from
> msdb..backupset where database_name = 'your_db' and type = 'D' order by
> backup_start_date desc)
> order by
> backup_start_date;
>
> AMB
> "Wojtek Z" wrote:
>
[vbcol=seagreen]
collumns[vbcol=seagreen]|||My guess is that the first log backup's FirstLsn need to be prior to the dat
abase backup's FirstLsn.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wojtek Z" <wojtas_z@.poczta.fm> wrote in message news:d5upv9$174$1@.nemesis.news.tpi.pl...[vb
col=seagreen]
> Thanks - i havent known that. But how about restoring db to a different
> server ?
> --
> Best Regards,
> Wojciech Znaniecki
> Uytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com>
> napisa w wiadomoci
> news:9DBEE13B-A899-4BF5-8E29-DA24F801F335@.microsoft.com...
>
> collumns
>[/vbcol]|||Uytkownik "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> napisa w wiadomoci
news:OcqdnSvVFHA.3320@.TK2MSFTNGP12.phx.gbl...
> My guess is that the first log backup's FirstLsn need to be prior to the
database backup's FirstLsn.
Thanks ! thats it.
Best Regards,
Wojciech Znaniecki
How can I check if backup log fiile is first log backup after database
backup ?
f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
and 10 log backup files ( BACKUP LOG x TO DISK='...)
I want to be sure that noone have removed the fist log file. using T-SQL
To create order in backkup log files i'm using FistLSN and LastLSN collumns
of RESTORE HEADERONLY procedure. But i dont know how to connect them to
database backup.
Any ideas ?
Best Regards
Wojciech ZnanieckiTry checking table backupset in database msdb. Something like:
select
backup_set_id,
type,
backup_start_date,
backup_finish_date
from
msdb..backupset
where
database_name = 'your_db'
and type = 'L'
and backup_start_date > (select top 1 backup_finish_date from
msdb..backupset where database_name = 'your_db' and type = 'D' order by
backup_start_date desc)
order by
backup_start_date;
AMB
"Wojtek Z" wrote:
> Hello,
> How can I check if backup log fiile is first log backup after database
> backup ?
> f.e. I have 1 database backup file ( BACKUP DATABASE x TO DISK = ...)
> and 10 log backup files ( BACKUP LOG x TO DISK='...)
> I want to be sure that noone have removed the fist log file. using T-SQL
> To create order in backkup log files i'm using FistLSN and LastLSN collumn
s
> of RESTORE HEADERONLY procedure. But i dont know how to connect them to
> database backup.
> Any ideas ?
> --
> Best Regards
> Wojciech Znaniecki
>
>|||Thanks - i havent known that. But how about restoring db to a different
server ?
Best Regards,
Wojciech Znaniecki
Uytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com>
napisa w wiadomoci
news:9DBEE13B-A899-4BF5-8E29-DA24F801F335@.microsoft.com...[vbcol=seagreen]
> Try checking table backupset in database msdb. Something like:
> select
> backup_set_id,
> type,
> backup_start_date,
> backup_finish_date
> from
> msdb..backupset
> where
> database_name = 'your_db'
> and type = 'L'
> and backup_start_date > (select top 1 backup_finish_date from
> msdb..backupset where database_name = 'your_db' and type = 'D' order by
> backup_start_date desc)
> order by
> backup_start_date;
>
> AMB
> "Wojtek Z" wrote:
>
[vbcol=seagreen] collumns[vbcol=seagreen]|||My guess is that the first log backup's FirstLsn need to be prior to the dat
abase backup's FirstLsn.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wojtek Z" <wojtas_z@.poczta.fm> wrote in message news:d5upv9$174$1@.nemesis.news.tpi.pl...[vb
col=seagreen]
> Thanks - i havent known that. But how about restoring db to a different
> server ?
> --
> Best Regards,
> Wojciech Znaniecki
> Uytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com>
> napisa w wiadomoci
> news:9DBEE13B-A899-4BF5-8E29-DA24F801F335@.microsoft.com...
>
> collumns
>[/vbcol]|||Uytkownik "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> napisa w wiadomoci
news:OcqdnSvVFHA.3320@.TK2MSFTNGP12.phx.gbl...
> My guess is that the first log backup's FirstLsn need to be prior to the
database backup's FirstLsn.
Thanks ! thats it.
Best Regards,
Wojciech Znaniecki
Friday, March 9, 2012
detach and attach VS restore from backup
I need to migrate an application from one server to another.
I have always in the past detached the db and re-attached to the new
server.
Was are the pros and cons from doing it this way?
Should I be restoring the db on the new server from a backup?
Which is the preferred way in which to do a migration?
thanks,
kim
Kim,
you can do the detach and reattach but this can be messy if you are
using more than one file group. The method of using backup and restore
is faster the only thing you need to remember is you have to be in
single user mode to restore the db to the new server. Als you will need
to have a storage disk accessible from both servers to do the backup
Bill
*** Sent via Developersdex http://www.codecomments.com ***
|||I have everything on one file group - so no concern there.
thank you for the reply.
"Bill" <nospam@.devdex.com> wrote in message
news:eqCs1Zu8GHA.2364@.TK2MSFTNGP02.phx.gbl...
> Kim,
> you can do the detach and reattach but this can be messy if you are
> using more than one file group. The method of using backup and restore
> is faster the only thing you need to remember is you have to be in
> single user mode to restore the db to the new server. Als you will need
> to have a storage disk accessible from both servers to do the backup
>
> Bill
> *** Sent via Developersdex http://www.codecomments.com ***
|||Kim wrote:
> I have everything on one file group - so no concern there.
> thank you for the reply.
>
Detach and attach is fine if you don't mind taking the database
offline. The main advantage of a backup is that your database can still
be used.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Hi,
There is no disadvantage in using Detach and Attach; apart from the reason
it needs a down time. Where as if you use Backup and Restore
it do not require a downtime.
Thanks
Hari
"Kim" <kim@.xxx.com> wrote in message
news:qruZg.15868$nn6.1113@.bignews6.bellsouth.net.. .
>I need to migrate an application from one server to another.
> I have always in the past detached the db and re-attached to the new
> server.
> Was are the pros and cons from doing it this way?
> Should I be restoring the db on the new server from a backup?
> Which is the preferred way in which to do a migration?
> thanks,
> kim
>
|||I will used backup fullbackup and restore with norecovery,
And restore transaction with recovery
It will reduce downtime.
Cheers
"Kim" wrote:
> I need to migrate an application from one server to another.
> I have always in the past detached the db and re-attached to the new
> server.
> Was are the pros and cons from doing it this way?
> Should I be restoring the db on the new server from a backup?
> Which is the preferred way in which to do a migration?
> thanks,
> kim
>
>
|||> Detach and attach is fine if you don't mind taking the database
> offline. The main advantage of a backup is that your database can still
> be used.
And that you have **at least one** complete backup!
I have always in the past detached the db and re-attached to the new
server.
Was are the pros and cons from doing it this way?
Should I be restoring the db on the new server from a backup?
Which is the preferred way in which to do a migration?
thanks,
kim
Kim,
you can do the detach and reattach but this can be messy if you are
using more than one file group. The method of using backup and restore
is faster the only thing you need to remember is you have to be in
single user mode to restore the db to the new server. Als you will need
to have a storage disk accessible from both servers to do the backup
Bill
*** Sent via Developersdex http://www.codecomments.com ***
|||I have everything on one file group - so no concern there.
thank you for the reply.
"Bill" <nospam@.devdex.com> wrote in message
news:eqCs1Zu8GHA.2364@.TK2MSFTNGP02.phx.gbl...
> Kim,
> you can do the detach and reattach but this can be messy if you are
> using more than one file group. The method of using backup and restore
> is faster the only thing you need to remember is you have to be in
> single user mode to restore the db to the new server. Als you will need
> to have a storage disk accessible from both servers to do the backup
>
> Bill
> *** Sent via Developersdex http://www.codecomments.com ***
|||Kim wrote:
> I have everything on one file group - so no concern there.
> thank you for the reply.
>
Detach and attach is fine if you don't mind taking the database
offline. The main advantage of a backup is that your database can still
be used.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Hi,
There is no disadvantage in using Detach and Attach; apart from the reason
it needs a down time. Where as if you use Backup and Restore
it do not require a downtime.
Thanks
Hari
"Kim" <kim@.xxx.com> wrote in message
news:qruZg.15868$nn6.1113@.bignews6.bellsouth.net.. .
>I need to migrate an application from one server to another.
> I have always in the past detached the db and re-attached to the new
> server.
> Was are the pros and cons from doing it this way?
> Should I be restoring the db on the new server from a backup?
> Which is the preferred way in which to do a migration?
> thanks,
> kim
>
|||I will used backup fullbackup and restore with norecovery,
And restore transaction with recovery
It will reduce downtime.
Cheers
"Kim" wrote:
> I need to migrate an application from one server to another.
> I have always in the past detached the db and re-attached to the new
> server.
> Was are the pros and cons from doing it this way?
> Should I be restoring the db on the new server from a backup?
> Which is the preferred way in which to do a migration?
> thanks,
> kim
>
>
|||> Detach and attach is fine if you don't mind taking the database
> offline. The main advantage of a backup is that your database can still
> be used.
And that you have **at least one** complete backup!
detach and attach VS restore from backup
This summary is not available. Please
click here to view the post.
detach and attach VS restore from backup
This summary is not available. Please
click here to view the post.
Detach and Attach for Backup and Deployment?
Hello,
is it ok, to use Detach and Attach for Backup and
Deployment? To deploy a database by copy the .mdf and .ldf
file and simply run the sp_attach_db stored procedure on
the target server. Or are there reasons why a Attach might
fail? When does it fail? What are the requirements that it
does run successfull?
Thank you, Markus
hi Markus,
"Markus S" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:1e96401c456cb$6637cfa0$a601280a@.phx.gbl...
> Hello,
> is it ok, to use Detach and Attach for Backup and
> Deployment? To deploy a database by copy the .mdf and .ldf
> file and simply run the sp_attach_db stored procedure on
> the target server. Or are there reasons why a Attach might
> fail? When does it fail? What are the requirements that it
> does run successfull?
detach + attach is a viable solution...
database deplyment is usually done in 3 ways...
1) backup your distribution database and restore it on user's server..
2) detach your distribution database and re-attach it on user's server..
both these methods can be accomplished via oSql.exe, the command line user
inteface MSDE is shipped with and are relative easy to implement..
they share the same caveat:
- orphaned users troubles can be raised if you do not properly clean the
registered users for that database befor backing it up or detach it...
orphaned users are users that are non more in sync with the target server
becouse of the relationship between sysusers.sid and syslogins.sid produces
a NULL value... this can be fixed via sp_change_users_login system stored
procedure
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
224071 INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/?id=224071
221465 INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/?id=221465
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
246133 INF: How To Transfer Logins and Passwords Between SQL Servers
http://support.microsoft.com/?id=246133
168001 PRB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/?id=168001
Fixing broken logins
http://www.sqlservercentral.com/colu...okenlogins.asp
all these articles can help you understanding how perform this and
troubleshouting logins problems...
- another caveat is that the distribution database inherits all it's
settings from your development model database, including collation/sort
order, database settings, special user object present in model database such
as users, dbobjects and so on...
this can not be an issue anymore, becouse SQL Server 2000 allows multiple
collation/sort order settings per instance, but regarding SQL Server
7.0/MSDE1.0, this was a big one...
3) scripting out all DDL statements to sql files in order to re-create the
databases on target server..
this require more work, becouse you have to distribute all sql files to
regen the database using external tools such as oSql.exe or additional
applications of yours via ADO/ODBC/Ado.Net/SQL-DMO...
I do prefer this method even if it involves more work, becouse it's
granularity and flexibility...
a companion application of mine is deserved to database creation reading a
proprietary file which lists all files and relative actions to be performed,
such as executing actions stored in separeted files like T-SQL CREATE
[object] statements, loading base data via T-SQL INSERT INTO statements,
eventually performing BULK INSERT operations, T-SQL statements regarding
privileges and/or executing direct T-SQL statements included in the
definition file like UPDATE... SET...
this comes in handy when an update to the database schema have to be
shipped... only the DDL sql files to modify the database are shipped and
executed ... and the app is the same.. only different command line
switches/paramenters are passed
you pay the price of a little more complexity, but you have full control
over the database creation on the target server...
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hello Andrea,
thank you very much for your detailed answer!
I think Nr. 3 is more work if you want to provide data
with your database. I's easy to attach a filled database.
Markus
is it ok, to use Detach and Attach for Backup and
Deployment? To deploy a database by copy the .mdf and .ldf
file and simply run the sp_attach_db stored procedure on
the target server. Or are there reasons why a Attach might
fail? When does it fail? What are the requirements that it
does run successfull?
Thank you, Markus
hi Markus,
"Markus S" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:1e96401c456cb$6637cfa0$a601280a@.phx.gbl...
> Hello,
> is it ok, to use Detach and Attach for Backup and
> Deployment? To deploy a database by copy the .mdf and .ldf
> file and simply run the sp_attach_db stored procedure on
> the target server. Or are there reasons why a Attach might
> fail? When does it fail? What are the requirements that it
> does run successfull?
detach + attach is a viable solution...
database deplyment is usually done in 3 ways...
1) backup your distribution database and restore it on user's server..
2) detach your distribution database and re-attach it on user's server..
both these methods can be accomplished via oSql.exe, the command line user
inteface MSDE is shipped with and are relative easy to implement..
they share the same caveat:
- orphaned users troubles can be raised if you do not properly clean the
registered users for that database befor backing it up or detach it...
orphaned users are users that are non more in sync with the target server
becouse of the relationship between sysusers.sid and syslogins.sid produces
a NULL value... this can be fixed via sp_change_users_login system stored
procedure
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
224071 INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/?id=224071
221465 INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/?id=221465
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
246133 INF: How To Transfer Logins and Passwords Between SQL Servers
http://support.microsoft.com/?id=246133
168001 PRB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/?id=168001
Fixing broken logins
http://www.sqlservercentral.com/colu...okenlogins.asp
all these articles can help you understanding how perform this and
troubleshouting logins problems...
- another caveat is that the distribution database inherits all it's
settings from your development model database, including collation/sort
order, database settings, special user object present in model database such
as users, dbobjects and so on...
this can not be an issue anymore, becouse SQL Server 2000 allows multiple
collation/sort order settings per instance, but regarding SQL Server
7.0/MSDE1.0, this was a big one...
3) scripting out all DDL statements to sql files in order to re-create the
databases on target server..
this require more work, becouse you have to distribute all sql files to
regen the database using external tools such as oSql.exe or additional
applications of yours via ADO/ODBC/Ado.Net/SQL-DMO...
I do prefer this method even if it involves more work, becouse it's
granularity and flexibility...
a companion application of mine is deserved to database creation reading a
proprietary file which lists all files and relative actions to be performed,
such as executing actions stored in separeted files like T-SQL CREATE
[object] statements, loading base data via T-SQL INSERT INTO statements,
eventually performing BULK INSERT operations, T-SQL statements regarding
privileges and/or executing direct T-SQL statements included in the
definition file like UPDATE... SET...
this comes in handy when an update to the database schema have to be
shipped... only the DDL sql files to modify the database are shipped and
executed ... and the app is the same.. only different command line
switches/paramenters are passed
you pay the price of a little more complexity, but you have full control
over the database creation on the target server...
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hello Andrea,
thank you very much for your detailed answer!
I think Nr. 3 is more work if you want to provide data
with your database. I's easy to attach a filled database.
Markus
Wednesday, March 7, 2012
Destination resets when changing selected databases
I discovered that everytime I need to add a database to my backup Maintenance Plan, after I select the new database from the drop-down of databases, the Destination automatically resets back to a default location.
I'm assuming this is a bug that will be resolved at some point, but in the meantime, I need to see if there is a way I can deal with this permanently. I am not the only one adding databases to the backup routine so I can't verify that this setting is properly changed every time we have a new database (which is about once a week).
Thanks in advance.
This is my last effort to get an answer on this. At this point using the maintenance plans is causing more problems than it solves because we add new databases most weeks. Everytime a database is added we have to change the backup maintenance plan and remember to change the backup path after it resets to a default path.
If there is even a place that I can change the default backup location, I will take that as a good workaround. I just can't continue this way since I am not always adding the databases to the backup and therefore can't validate that the path is reset every time. If there isn't a solution, I'll have to build a front-end to manage the backup scripts which I really don't want to do.
I am asking that a Microsoft representative PLEASE give me an anwer on this so I can move on.
Thanks.
|||If you go into RegEdit (with ALL of the caveats and cautions that always accompany manually modifying the registry!),
Navigate to:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer
and modify the BackupDirectory value, you'll have this as the new default.
Note that this permanently modifies the default location for ALL TSQL backups!
|||But is Microsoft going to fix this bug? I submitted it via the feedback site and was told they couldn't reproduce it. We don't add databases as often but it is still a pain to have to remember to look at the path and make sure it is right. I can't use the registry fix as each database goes to it's own location so every maint. plan is different.|||
Thanks for the idea Kevin. Unfortunately this is a client-side solution and I need a server-side solution for the default. Otherwise I would have to go make this change to every PC, laptop, and home PC via VPN for all administrators who can add backups. That just can't happen.
Thanks again for the info though.
Heather, so they said they can't reproduce it? I can't find an installation where it doesn't work this way, and we are on SP1 too.
I guess we'll just have to wait and see if it is ever acknowledged.
|||
Actually, this is a server-side setting.
If you make the registry setting on the server, and then connect to it from another node using Management Studio, that instance of Management studio will see the new default location for the backups. While we can't customize it for each database (I'm not sure how we'd do that since there's nothing to remember), we can at least get the proper parent directory, and keep backups off the C: drive!
|||Thank you Kevin!!! That is great. I will go make the setting (carefully of course) now and we should be good to go.
For what it's worth, I would recommend that Microsoft add a backup path setup option to the server properties dialogue, maybe along with default mdb and ldb paths. Since custom backup paths to other media are common, even encouraged, I just think it makes sense to turn it into an accessible option. Obviously it is not necessary to accomplish even the most complex backup plans because the flexibility in SQL 2005 rocks, but it would make it easier to accuratly configure multiple maintenance plans using the Managment Studio tools without error.
Anyway, thanks again for the big tip! I'm sure it will help others as well.
P.S. I've made the change now and it works like a charm. I didn't even have to disconnect/reconnect my local Management Studio for it to take effect locally when configuring a maintenance plan.
|||It worked beautifully for me as well. But I also agree this setting should be something simple in the Management Studio, not just a registry hack.
Thank you for the great assistance!
|||The problem is not trying to set a different default destination for each database, the problem is that anytime you open the backup step properties, the destination folder is reset to the server default instead of remembering the value that you set it to manually.
It had to look at the saved properties to get the database list, it could just as easily load the saved value of the destination folder instead of going to the registry.
Destination resets when changing selected databases
I discovered that everytime I need to add a database to my backup Maintenance Plan, after I select the new database from the drop-down of databases, the Destination automatically resets back to a default location.
I'm assuming this is a bug that will be resolved at some point, but in the meantime, I need to see if there is a way I can deal with this permanently. I am not the only one adding databases to the backup routine so I can't verify that this setting is properly changed every time we have a new database (which is about once a week).
Thanks in advance.
This is my last effort to get an answer on this. At this point using the maintenance plans is causing more problems than it solves because we add new databases most weeks. Everytime a database is added we have to change the backup maintenance plan and remember to change the backup path after it resets to a default path.
If there is even a place that I can change the default backup location, I will take that as a good workaround. I just can't continue this way since I am not always adding the databases to the backup and therefore can't validate that the path is reset every time. If there isn't a solution, I'll have to build a front-end to manage the backup scripts which I really don't want to do.
I am asking that a Microsoft representative PLEASE give me an anwer on this so I can move on.
Thanks.
|||If you go into RegEdit (with ALL of the caveats and cautions that always accompany manually modifying the registry!),
Navigate to:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer
and modify the BackupDirectory value, you'll have this as the new default.
Note that this permanently modifies the default location for ALL TSQL backups!
|||But is Microsoft going to fix this bug? I submitted it via the feedback site and was told they couldn't reproduce it. We don't add databases as often but it is still a pain to have to remember to look at the path and make sure it is right. I can't use the registry fix as each database goes to it's own location so every maint. plan is different.|||
Thanks for the idea Kevin. Unfortunately this is a client-side solution and I need a server-side solution for the default. Otherwise I would have to go make this change to every PC, laptop, and home PC via VPN for all administrators who can add backups. That just can't happen.
Thanks again for the info though.
Heather, so they said they can't reproduce it? I can't find an installation where it doesn't work this way, and we are on SP1 too.
I guess we'll just have to wait and see if it is ever acknowledged.
|||
Actually, this is a server-side setting.
If you make the registry setting on the server, and then connect to it from another node using Management Studio, that instance of Management studio will see the new default location for the backups. While we can't customize it for each database (I'm not sure how we'd do that since there's nothing to remember), we can at least get the proper parent directory, and keep backups off the C: drive!
|||Thank you Kevin!!! That is great. I will go make the setting (carefully of course) now and we should be good to go.
For what it's worth, I would recommend that Microsoft add a backup path setup option to the server properties dialogue, maybe along with default mdb and ldb paths. Since custom backup paths to other media are common, even encouraged, I just think it makes sense to turn it into an accessible option. Obviously it is not necessary to accomplish even the most complex backup plans because the flexibility in SQL 2005 rocks, but it would make it easier to accuratly configure multiple maintenance plans using the Managment Studio tools without error.
Anyway, thanks again for the big tip! I'm sure it will help others as well.
P.S. I've made the change now and it works like a charm. I didn't even have to disconnect/reconnect my local Management Studio for it to take effect locally when configuring a maintenance plan.
|||It worked beautifully for me as well. But I also agree this setting should be something simple in the Management Studio, not just a registry hack.
Thank you for the great assistance!
|||The problem is not trying to set a different default destination for each database, the problem is that anytime you open the backup step properties, the destination folder is reset to the server default instead of remembering the value that you set it to manually.
It had to look at the saved properties to get the database list, it could just as easily load the saved value of the destination folder instead of going to the registry.
Destination resets when changing selected databases
I discovered that everytime I need to add a database to my backup Maintenance Plan, after I select the new database from the drop-down of databases, the Destination automatically resets back to a default location.
I'm assuming this is a bug that will be resolved at some point, but in the meantime, I need to see if there is a way I can deal with this permanently. I am not the only one adding databases to the backup routine so I can't verify that this setting is properly changed every time we have a new database (which is about once a week).
Thanks in advance.
This is my last effort to get an answer on this. At this point using the maintenance plans is causing more problems than it solves because we add new databases most weeks. Everytime a database is added we have to change the backup maintenance plan and remember to change the backup path after it resets to a default path.
If there is even a place that I can change the default backup location, I will take that as a good workaround. I just can't continue this way since I am not always adding the databases to the backup and therefore can't validate that the path is reset every time. If there isn't a solution, I'll have to build a front-end to manage the backup scripts which I really don't want to do.
I am asking that a Microsoft representative PLEASE give me an anwer on this so I can move on.
Thanks.
|||If you go into RegEdit (with ALL of the caveats and cautions that always accompany manually modifying the registry!),
Navigate to:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer
and modify the BackupDirectory value, you'll have this as the new default.
Note that this permanently modifies the default location for ALL TSQL backups!
|||But is Microsoft going to fix this bug? I submitted it via the feedback site and was told they couldn't reproduce it. We don't add databases as often but it is still a pain to have to remember to look at the path and make sure it is right. I can't use the registry fix as each database goes to it's own location so every maint. plan is different.|||
Thanks for the idea Kevin. Unfortunately this is a client-side solution and I need a server-side solution for the default. Otherwise I would have to go make this change to every PC, laptop, and home PC via VPN for all administrators who can add backups. That just can't happen.
Thanks again for the info though.
Heather, so they said they can't reproduce it? I can't find an installation where it doesn't work this way, and we are on SP1 too.
I guess we'll just have to wait and see if it is ever acknowledged.
|||
Actually, this is a server-side setting.
If you make the registry setting on the server, and then connect to it from another node using Management Studio, that instance of Management studio will see the new default location for the backups. While we can't customize it for each database (I'm not sure how we'd do that since there's nothing to remember), we can at least get the proper parent directory, and keep backups off the C: drive!
|||Thank you Kevin!!! That is great. I will go make the setting (carefully of course) now and we should be good to go.
For what it's worth, I would recommend that Microsoft add a backup path setup option to the server properties dialogue, maybe along with default mdb and ldb paths. Since custom backup paths to other media are common, even encouraged, I just think it makes sense to turn it into an accessible option. Obviously it is not necessary to accomplish even the most complex backup plans because the flexibility in SQL 2005 rocks, but it would make it easier to accuratly configure multiple maintenance plans using the Managment Studio tools without error.
Anyway, thanks again for the big tip! I'm sure it will help others as well.
P.S. I've made the change now and it works like a charm. I didn't even have to disconnect/reconnect my local Management Studio for it to take effect locally when configuring a maintenance plan.
|||It worked beautifully for me as well. But I also agree this setting should be something simple in the Management Studio, not just a registry hack.
Thank you for the great assistance!
|||The problem is not trying to set a different default destination for each database, the problem is that anytime you open the backup step properties, the destination folder is reset to the server default instead of remembering the value that you set it to manually.
It had to look at the saved properties to get the database list, it could just as easily load the saved value of the destination folder instead of going to the registry.
Subscribe to:
Posts (Atom)