Resotring a replicated database

I have a database A on server_1 that is being merge replicated to database B on server_2 which is production a box

NOW

I want to restore database A on server_3 that is being merge replicated to database B on Server_4 which is test box.

How about do I do that with replication being involved?

ThanxI don't know that there is any way to restore a database that is involved in replication. It smells like a VERY bad thing to me because it would do horribly confusing things to the data and especially the log information that could send replication into hysterics!

If you really want to do this, I'd delete the server from the replication schema (probably all of the servers from the schema for Merge Replication), then do the restore. After that restore was complete, then I'd rebuild whatever replication I needed.

-PatP|||You can backup the replicated database but follow what Pat referred and go with this MSDN http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replbackup_8enn.asp link for more details.

Resotring a backup file to a DB on a differerent server

Hi,
I'm trying to restore a backup file to a data base on a different server by
overwriting it.
In order to do this I need to move the logical file I am restoring to the
location of the physical database file I am restoring to (as per the prompt I
am receiving)
When it say "MOVE", will this in fact move the file over & possibly damage
the database from which the restore is being taken? Does it move any files or
just copy?
Is there any danger here?
Below is what I'm doing:
RESTORE DATABASE oneDB
FROM DISK = 'C:\myPhysicalBackupFile'
WITH REPLACE,
MOVE 'myLogicalFile' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\PhysicalFile.MDF'
Many thanks for any help on this
AntIt's a little confusing the first time, but all the MOVE is doing is telling
the engine where to place the MDF file. The FROM is the .BAK file.
And if you're overwriting an exiting database, then yes, you're gonna whack
it. But that is expected behavior.
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:69B97720-9594-48A8-9181-53F874B8EB3E@.microsoft.com...
> Hi,
> I'm trying to restore a backup file to a data base on a different server
> by
> overwriting it.
> In order to do this I need to move the logical file I am restoring to the
> location of the physical database file I am restoring to (as per the
> prompt I
> am receiving)
> When it say "MOVE", will this in fact move the file over & possibly damage
> the database from which the restore is being taken? Does it move any files
> or
> just copy?
> Is there any danger here?
> Below is what I'm doing:
> RESTORE DATABASE oneDB
> FROM DISK = 'C:\myPhysicalBackupFile'
> WITH REPLACE,
> MOVE 'myLogicalFile' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\PhysicalFile.MDF'
>
> Many thanks for any help on this
> Ant
>|||Hi Jay, thanks very much for the reply.
It's not the Database I'm overwriting that I'm concerned about, so long as
it doesn't affect the database from where I'm using the Backup from. I was
concerned that it might damage that. Sounds safe.
Many thanks for your answer Jay.
"Jay" wrote:
> It's a little confusing the first time, but all the MOVE is doing is telling
> the engine where to place the MDF file. The FROM is the .BAK file.
> And if you're overwriting an exiting database, then yes, you're gonna whack
> it. But that is expected behavior.
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:69B97720-9594-48A8-9181-53F874B8EB3E@.microsoft.com...
> > Hi,
> >
> > I'm trying to restore a backup file to a data base on a different server
> > by
> > overwriting it.
> >
> > In order to do this I need to move the logical file I am restoring to the
> > location of the physical database file I am restoring to (as per the
> > prompt I
> > am receiving)
> >
> > When it say "MOVE", will this in fact move the file over & possibly damage
> > the database from which the restore is being taken? Does it move any files
> > or
> > just copy?
> >
> > Is there any danger here?
> >
> > Below is what I'm doing:
> >
> > RESTORE DATABASE oneDB
> >
> > FROM DISK = 'C:\myPhysicalBackupFile'
> > WITH REPLACE,
> > MOVE 'myLogicalFile' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL\data\PhysicalFile.MDF'
> >
> >
> > Many thanks for any help on this
> >
> > Ant
> >
>
>|||Ant
Please read also
http://dimantdatabasesolutions.blogspot.com/2007/04/sql-or-windows-authentication.html
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:A48539B7-996B-4A16-8F99-1100515146C7@.microsoft.com...
> Hi Jay, thanks very much for the reply.
> It's not the Database I'm overwriting that I'm concerned about, so long as
> it doesn't affect the database from where I'm using the Backup from. I was
> concerned that it might damage that. Sounds safe.
> Many thanks for your answer Jay.
>
> "Jay" wrote:
>> It's a little confusing the first time, but all the MOVE is doing is
>> telling
>> the engine where to place the MDF file. The FROM is the .BAK file.
>> And if you're overwriting an exiting database, then yes, you're gonna
>> whack
>> it. But that is expected behavior.
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:69B97720-9594-48A8-9181-53F874B8EB3E@.microsoft.com...
>> > Hi,
>> >
>> > I'm trying to restore a backup file to a data base on a different
>> > server
>> > by
>> > overwriting it.
>> >
>> > In order to do this I need to move the logical file I am restoring to
>> > the
>> > location of the physical database file I am restoring to (as per the
>> > prompt I
>> > am receiving)
>> >
>> > When it say "MOVE", will this in fact move the file over & possibly
>> > damage
>> > the database from which the restore is being taken? Does it move any
>> > files
>> > or
>> > just copy?
>> >
>> > Is there any danger here?
>> >
>> > Below is what I'm doing:
>> >
>> > RESTORE DATABASE oneDB
>> >
>> > FROM DISK = 'C:\myPhysicalBackupFile'
>> > WITH REPLACE,
>> > MOVE 'myLogicalFile' TO 'C:\Program Files\Microsoft SQL
>> > Server\MSSQL\data\PhysicalFile.MDF'
>> >
>> >
>> > Many thanks for any help on this
>> >
>> > Ant
>> >
>>sql

Resize Filegroups Allocation on restore

Hi, I have a database with about 200Gigs allocated to filegroups and 200Megs data in it presently, and am trying to create a copy of the database on the same server - except I don't have enough diskspace free for another 200Gigs. Is there a way using copy database wizard, or restore that the filegroup allocations can be resized?

Thanks,
Chris

Unfortunately, no.

Backup copies physical blocks and puts them back in the same place that they came from.

Even if your database is only using 200MB out of 200GB, there is no assurance that the 200MB is nicely located in the front of the data files.

Your options are:

If the source database doesn't need to be that big, you can run DBCC SHRINKDATABASE on it and then back it up, or you can temporarily restore the database on a 3rd machine, run SHRINKDATABASE on it, back it up, and take it to your smaller machine.

Reset IDENTITY seed

Hello,
Can I reset the IDENTITY seed of a Table column without delete/drop the table?
I want to delete all the table rows, restore de seed, and restore abackup made on a XML (using SET IDENTITY_INSERT Table ON)
I cant drop the table due to acount restricctions.
regards,
Edu
You could try
TRUNCATE TABLE MyTable|||Check outDBCC CHECKIDENT.|||Thanks,
DBCC CHECKIDENT. works fine!
Regards,
Edu

reposting: bkup and resore strategy. Please reply

Hi,
My vb.net app will use database which will be deployed using MSDE on client
machines. I will provide UI to client in my app to backup and restore the d
atabase. My app will work with one database, for e.g. MyAppDB. My question
is what are the best pract
ices for doing this:
a) Should I choose Full or Simple mode. There is no mission critical data.
But as my clients will be using MSDE, will setting up the recovery mode as
Full cause the database and log size limits to cross the 2GB limit soon. Is
it better if I choose SIMP
LE recovery mode. What do you suggest.
b) Should I back up the system databases also, if yes then what should be th
e order of backing these up (and then in what order should the databases be
restored)
c) Does the MSDE login information also needs to be backed up. At max I wil
l have just one custom login and sa. I am using SQL Authentication.
d) Will I need to close my application and make sure that my apps' MSDE inst
ance is not running before backing up the databases.
d) I want to provide user the option to choose the backup location. How wil
l I figure out if they chose something like tape drive or zip drive. How sh
ould I handle this scenario.
e) Also how can I find out if there is sufficient space on the hard drive to
backup the database. Are there any standard sql functions to check that.
f) Should I do database log file backup too while backing up the database.
sorry for so many questions.
thanks
newbie
Print | Copy URL of this post
Expand All Collapse AllSee answers inline:
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:3DFC5E47-73A3-4CFC-9161-0AF13E789AD5@.microsoft.com...
> Hi,
> My vb.net app will use database which will be deployed using MSDE on
client machines. I will provide UI to client in my app to backup and
restore the database. My app will work with one database, for e.g. MyAppDB.
My question is what are the best practices for doing this:
> a) Should I choose Full or Simple mode. There is no mission critical
data. But as my clients will be using MSDE, will setting up the recovery
mode as Full cause the database and log size limits to cross the 2GB limit
soon. Is it better if I choose SIMPLE recovery mode. What do you suggest.
I almost always use either full or bulk-logged, although there are a few
instances where simple is appropriate. Basically you need to determine in
the event of failure do you want to be able to recover to the point of
failure, or is it good engoug to recover from the last full backup? As for
size limits, you can control the size of the log by backing it up regularly.
Besides, I am pretty sure that the 2Gb size limit is on the data file, not
the log.

> b) Should I back up the system databases also, if yes then what should be
the order of backing these up (and then in what order should the databases
be restored)
Yes, at least master and msdb, but the order of backing them up is not
important. Master would be the first to be restored

> c) Does the MSDE login information also needs to be backed up. At max I
will have just one custom login and sa. I am using SQL Authentication.
The logins are stored in master.

> d) Will I need to close my application and make sure that my apps' MSDE
instance is not running before backing up the databases.
No, backups are an online operation.

> d) I want to provide user the option to choose the backup location. How
will I figure out if they chose something like tape drive or zip drive. How
should I handle this scenario.
That's an application issue, you stated that you were providing them a front
end UI to backup the databases. The destination of the backup files is just
one of the BACKUP command arguments. There are also several arguments that
apply only to tape backup operations that you might want to take into
consideration.

> e) Also how can I find out if there is sufficient space on the hard drive
to backup the database. Are there any standard sql functions to check that.
There isn't a function that will estimate the size of the backup, but you
can read the msdb..backupset table to get the size of the last backup (or an
average of the last several) and estimate the size based on that.

> f) Should I do database log file backup too while backing up the database.
Not at the same time, although it won't hurt anything, there's just no
reason to do them at the same time.

> sorry for so many questions.
> thanks
> newbie
>
> Print | Copy URL of this post
>
> Expand All Collapse All
>|||Thanks don. Here are few more concerns that I have:
1> Is there any way to zip the .bak files into just 1 file ising T-sql or wi
ll I have to resort to 3rd party solutions for compression (as .Net doesn't
have any compression lib)
2> What happens if one user chooses to backup while others are still working
on the same database (imagine a small office setup with few computers and m
y app on all of them and the db on one central machine). Will the backup wor
k fine in this situation or
will the users need to log off.
3> Also we are not aiming at schedule backups option right now. Our clients
will have to go to the UI screen and backup the database themselves when the
y want to. So in this situation should I restore system databases also when
the users choose to restore
my app's db from the backed up location. And do I need to backup the system
databases always when they choose to backup my apps db or just before any ma
jor important changes.
Thanks

reposting: bkup and resore strategy. Please reply

Hi,
My vb.net app will use database which will be deployed using MSDE on client machines. I will provide UI to client in my app to backup and restore the database. My app will work with one database, for e.g. MyAppDB. My question is what are the best pract
ices for doing this:
a) Should I choose Full or Simple mode. There is no mission critical data. But as my clients will be using MSDE, will setting up the recovery mode as Full cause the database and log size limits to cross the 2GB limit soon. Is it better if I choose SIMP
LE recovery mode. What do you suggest.
b) Should I back up the system databases also, if yes then what should be the order of backing these up (and then in what order should the databases be restored)
c) Does the MSDE login information also needs to be backed up. At max I will have just one custom login and sa. I am using SQL Authentication.
d) Will I need to close my application and make sure that my apps' MSDE instance is not running before backing up the databases.
d) I want to provide user the option to choose the backup location. How will I figure out if they chose something like tape drive or zip drive. How should I handle this scenario.
e) Also how can I find out if there is sufficient space on the hard drive to backup the database. Are there any standard sql functions to check that.
f) Should I do database log file backup too while backing up the database.
sorry for so many questions.
thanks
newbie
Print | Copy URL of this post
Expand All Collapse All
See answers inline:
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:3DFC5E47-73A3-4CFC-9161-0AF13E789AD5@.microsoft.com...
> Hi,
> My vb.net app will use database which will be deployed using MSDE on
client machines. I will provide UI to client in my app to backup and
restore the database. My app will work with one database, for e.g. MyAppDB.
My question is what are the best practices for doing this:
> a) Should I choose Full or Simple mode. There is no mission critical
data. But as my clients will be using MSDE, will setting up the recovery
mode as Full cause the database and log size limits to cross the 2GB limit
soon. Is it better if I choose SIMPLE recovery mode. What do you suggest.
I almost always use either full or bulk-logged, although there are a few
instances where simple is appropriate. Basically you need to determine in
the event of failure do you want to be able to recover to the point of
failure, or is it good engoug to recover from the last full backup? As for
size limits, you can control the size of the log by backing it up regularly.
Besides, I am pretty sure that the 2Gb size limit is on the data file, not
the log.

> b) Should I back up the system databases also, if yes then what should be
the order of backing these up (and then in what order should the databases
be restored)
Yes, at least master and msdb, but the order of backing them up is not
important. Master would be the first to be restored

> c) Does the MSDE login information also needs to be backed up. At max I
will have just one custom login and sa. I am using SQL Authentication.
The logins are stored in master.

> d) Will I need to close my application and make sure that my apps' MSDE
instance is not running before backing up the databases.
No, backups are an online operation.

> d) I want to provide user the option to choose the backup location. How
will I figure out if they chose something like tape drive or zip drive. How
should I handle this scenario.
That's an application issue, you stated that you were providing them a front
end UI to backup the databases. The destination of the backup files is just
one of the BACKUP command arguments. There are also several arguments that
apply only to tape backup operations that you might want to take into
consideration.

> e) Also how can I find out if there is sufficient space on the hard drive
to backup the database. Are there any standard sql functions to check that.
There isn't a function that will estimate the size of the backup, but you
can read the msdb..backupset table to get the size of the last backup (or an
average of the last several) and estimate the size based on that.

> f) Should I do database log file backup too while backing up the database.
Not at the same time, although it won't hurt anything, there's just no
reason to do them at the same time.

> sorry for so many questions.
> thanks
> newbie
>
> Print | Copy URL of this post
>
> Expand All Collapse All
>
|||Thanks don. Here are few more concerns that I have:
1> Is there any way to zip the .bak files into just 1 file ising T-sql or will I have to resort to 3rd party solutions for compression (as .Net doesn't have any compression lib)
2> What happens if one user chooses to backup while others are still working on the same database (imagine a small office setup with few computers and my app on all of them and the db on one central machine). Will the backup work fine in this situation or
will the users need to log off.
3> Also we are not aiming at schedule backups option right now. Our clients will have to go to the UI screen and backup the database themselves when they want to. So in this situation should I restore system databases also when the users choose to restore
my app's db from the backed up location. And do I need to backup the system databases always when they choose to backup my apps db or just before any major important changes.
Thanks