Backup file database was restored from

Hello!
I am trying to find out name of the backup file database was restored from
(I am restoring from DISK), something similar to physical_device_name in
backupmediafamily. restorehistory doesn't have this information.
Any advice is greatly appreciated.
Thanks,
Igor
You can use backup_set_id on restorehistory and media_set_id on backupset to
find the record on backupmediafamily.
Hope this helps,
Ben Nevarez
"Igor Marchenko" wrote:

> Hello!
> I am trying to find out name of the backup file database was restored from
> (I am restoring from DISK), something similar to physical_device_name in
> backupmediafamily. restorehistory doesn't have this information.
> Any advice is greatly appreciated.
> Thanks,
> Igor
|||Thanks a lot Ben! That exactly what I was looking for.
Igor
"Ben Nevarez" wrote:
[vbcol=seagreen]
> You can use backup_set_id on restorehistory and media_set_id on backupset to
> find the record on backupmediafamily.
> Hope this helps,
> Ben Nevarez
>
>
> "Igor Marchenko" wrote:
sql

Backup file database was restored from

Hello!
I am trying to find out name of the backup file database was restored from
(I am restoring from DISK), something similar to physical_device_name in
backupmediafamily. restorehistory doesn't have this information.
Any advice is greatly appreciated.
Thanks,
IgorYou can use backup_set_id on restorehistory and media_set_id on backupset to
find the record on backupmediafamily.
Hope this helps,
Ben Nevarez
"Igor Marchenko" wrote:
> Hello!
> I am trying to find out name of the backup file database was restored from
> (I am restoring from DISK), something similar to physical_device_name in
> backupmediafamily. restorehistory doesn't have this information.
> Any advice is greatly appreciated.
> Thanks,
> Igor|||Thanks a lot Ben! That exactly what I was looking for.
Igor
"Ben Nevarez" wrote:
> You can use backup_set_id on restorehistory and media_set_id on backupset to
> find the record on backupmediafamily.
> Hope this helps,
> Ben Nevarez
>
>
> "Igor Marchenko" wrote:
> > Hello!
> >
> > I am trying to find out name of the backup file database was restored from
> > (I am restoring from DISK), something similar to physical_device_name in
> > backupmediafamily. restorehistory doesn't have this information.
> > Any advice is greatly appreciated.
> >
> > Thanks,
> > Igor

backup and restoring

I used backup and restore to upgrade a database from sql 2000 to sql 2005. Is it necessary to create the mdf and ldf on the new server at the time of restore under options or should I copy them to the new server in the data folder? I am new and not quite sure what the log files hold.
Thanksif you have created a backup of a database, you don't need anything but the .bak file.

when you restore from a bak file, the data and log files will be created automatically by the server.|||Edit: You don't need to create them, the restore does.

How backup works:
When SQL Server backs up a database, it backs up the data file first. During the backup of the data file, no changes are written to the data file, only to the log. When the bacup of the data file is complete, the changes written to the log file is backed up. In other words, backup of a SQL Server restores to the point of time when the backup finished, not started. Furthermore, your log file will be used to keep track of transactions afer the backup have restored, so you will need the file.|||Ok. If I don't specify in options at the time of restore, I can't find where the the mdf and ldf files were created. And are these new mdf and ldf files or do they hold the same data as the database before they were created on the new server.
thanks|||this will tell you where they are:

exec sp_helpfile

backup and restoring

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

Backup and Restore Urgent!

Hi All,
Sql server 7.0

i have a db ABC and want to restore it as ABCbak, I am taking the backup of the db ABC and restoring it on ABCbak, i am also seeing that the physical names are different but logical names remains the same. Will this cause any kind of problem to db ABC.

Waiting for reply.

TIA
AdilThe logical names of the files should not present a problem.|||yeah... no problem... if you're super anal and it really bothers you... you can change them after the restore... can't think of the commands offhand though, there's threads on here that have them in them and I'm sure it would be in books online

Backup and Restore Urgent!

Hi All,
Sql server 7.0
i have a db ABC and want to restore it as ABCbak, I am
taking the backup of the db ABC and restoring it on
ABCbak, i am also seeing that the physical names are
different but logical names remains the same. Will this
cause any kind of problem to db ABC.
Waiting for reply.
TIA
AdilYou should be able to do this using the "with replace" phrasing to allow SQL
to overwrite the other database. You will probably also have to use "with
move" to ensure the physical files do not try to go to the same place as the
existing ones. The logical names staying the same is not a problem but can
be changed with ALTER DATABASE after the restore is complete if you so
desire. Check out BOL for the exact syntax.
Christian Smith
"haseeb" <anonymous@.discussions.microsoft.com> wrote in message
news:11cf901c3f647$cca656b0$a101280a@.phx
.gbl...
> Hi All,
> Sql server 7.0
> i have a db ABC and want to restore it as ABCbak, I am
> taking the backup of the db ABC and restoring it on
> ABCbak, i am also seeing that the physical names are
> different but logical names remains the same. Will this
> cause any kind of problem to db ABC.
>
> Waiting for reply.
> TIA
> Adil

Backup and Restore Urgent!

Hi All,
Sql server 7.0
i have a db ABC and want to restore it as ABCbak, I am
taking the backup of the db ABC and restoring it on
ABCbak, i am also seeing that the physical names are
different but logical names remains the same. Will this
cause any kind of problem to db ABC.
Waiting for reply.
TIA
AdilYou should be able to do this using the "with replace" phrasing to allow SQL
to overwrite the other database. You will probably also have to use "with
move" to ensure the physical files do not try to go to the same place as the
existing ones. The logical names staying the same is not a problem but can
be changed with ALTER DATABASE after the restore is complete if you so
desire. Check out BOL for the exact syntax.
Christian Smith
"haseeb" <anonymous@.discussions.microsoft.com> wrote in message
news:11cf901c3f647$cca656b0$a101280a@.phx.gbl...
> Hi All,
> Sql server 7.0
> i have a db ABC and want to restore it as ABCbak, I am
> taking the backup of the db ABC and restoring it on
> ABCbak, i am also seeing that the physical names are
> different but logical names remains the same. Will this
> cause any kind of problem to db ABC.
>
> Waiting for reply.
> TIA
> Adil

Backup and Restore Databases

Hello,
I'm restoring some databases and when i take a look in the
users that were created on these databases they do not
appear after the restore. I've done this a lot of times
and this problem never happen before.
Best RegardsYou most probably have orphaned users:
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Server
s
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message news:14f4d01c4159d$92c1a6f0$
a301280a@.phx.gbl...
> Hello,
> I'm restoring some databases and when i take a look in the
> users that were created on these databases they do not
> appear after the restore. I've done this a lot of times
> and this problem never happen before.
> Best Regards|||Have you refreshed the user list?
>--Original Message--
>Hello,
>I'm restoring some databases and when i take a look in
the
>users that were created on these databases they do not
>appear after the restore. I've done this a lot of times
>and this problem never happen before.
>Best Regards
>.
>

Backup & Restore Problem

Dear All,
I've problem when restoring differential & log backup.
How I can restore differential backup and log backup?
Note : I backup sql server data to files.
ThanksFirst restore last full backup of your database. Specifiy (in EM) 'able to
receive additional tran logs'
Then restore last differential backup of your database
then restore sequentially transaction logs from the last diff backup to the
point you need.
MC
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:eqh$nek%23FHA.272@.tk2msftngp13.phx.gbl...
> Dear All,
> I've problem when restoring differential & log backup.
> How I can restore differential backup and log backup?
> Note : I backup sql server data to files.
> Thanks|||First you need full backup.
Regards

Backup & Restore Problem

Dear All,
I've problem when restoring differential & log backup.
How I can restore differential backup and log backup?
Note : I backup sql server data to files.
Thanks
First restore last full backup of your database. Specifiy (in EM) 'able to
receive additional tran logs'
Then restore last differential backup of your database
then restore sequentially transaction logs from the last diff backup to the
point you need.
MC
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:eqh$nek%23FHA.272@.tk2msftngp13.phx.gbl...
> Dear All,
> I've problem when restoring differential & log backup.
> How I can restore differential backup and log backup?
> Note : I backup sql server data to files.
> Thanks
|||First you need full backup.
Regards

Backup & Restore Problem

Dear All,
I've problem when restoring differential & log backup.
How I can restore differential backup and log backup?
Note : I backup sql server data to files.
ThanksFirst restore last full backup of your database. Specifiy (in EM) 'able to
receive additional tran logs'
Then restore last differential backup of your database
then restore sequentially transaction logs from the last diff backup to the
point you need.
MC
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:eqh$nek%23FHA.272@.tk2msftngp13.phx.gbl...
> Dear All,
> I've problem when restoring differential & log backup.
> How I can restore differential backup and log backup?
> Note : I backup sql server data to files.
> Thanks|||First you need full backup.
Regards

Backing up/restoring using hard disk or tape

Here is my situation:
I have a 2 node SQL 2000 enterprise cluster for which i need to backup
3 databases. Here is my plan. I've changed the recovery mode to Full
on all 3 databases and have performed complete backups. I've
scheduled a complete backup at midnight and differentials every hour
and t-logs every 15 minutes. As an alternate backup, I am backing up
to tape using Arcserve's SQL agent. The schedule for this is a
complete backup nightly, and differential every 4 hours. What I'm
unclear on is the truncation of t-logs. Do they auto truncate every
time a differential or complete backup is run? I'm worried that the
tape backup (when it runs) may contain some transactions that the SQL
backup to disk does not have and vice versa...is this true? I know
it simple recovery mode, the databases will auto truncate the t-log
after a backup but not sure what will happen or where to find the
setting for full recovery mode. I would assume that SQL would allow me
to create multiple backups of the same databases but I just cant find
out how/where...any help is greatly appreciated as we are deploying a
production server on Aug 31. TIA...
TonyA full/differential backup doesn't truncate the tran log.
Linchi
>--Original Message--
>Here is my situation:
>I have a 2 node SQL 2000 enterprise cluster for which i
need to backup
>3 databases. Here is my plan. I've changed the recovery
mode to Full
>on all 3 databases and have performed complete backups.
I've
>scheduled a complete backup at midnight and differentials
every hour
>and t-logs every 15 minutes. As an alternate backup, I
am backing up
>to tape using Arcserve's SQL agent. The schedule for
this is a
>complete backup nightly, and differential every 4 hours.
What I'm
>unclear on is the truncation of t-logs. Do they auto
truncate every
>time a differential or complete backup is run? I'm
worried that the
>tape backup (when it runs) may contain some transactions
that the SQL
>backup to disk does not have and vice versa...is this
true? I know
>it simple recovery mode, the databases will auto truncate
the t-log
>after a backup but not sure what will happen or where to
find the
>setting for full recovery mode. I would assume that SQL
would allow me
>to create multiple backups of the same databases but I
just cant find
>out how/where...any help is greatly appreciated as we
are deploying a
>production server on Aug 31. TIA...
>Tony
>.
>

backing up/restoring a db

Hello,
I have a set of scripts, which create a database Test1,
create accounts/roles/users, and grant permissions for
those users to access the newly created database.
When I ran the scripts for the 1st time, everything worked
just fine. Test1 db was created and I could successfully
connect to it from my client application, add/remove
records, etc.
Then, I needed to move my db to a different server, so I
decided to backup the database, and then to restore on the
new server.
Here is the backup script I ran:
USE master
EXEC sp_adddumpdevice 'disk', 'Test1DB', 'Test1db.dat'
BACKUP DATABASE Test1 TO Test1DB
The database backup is created properly (when I restored,
I saw all the original records).
The problem I am experiencing, though, is the messed up
accounts/logins, once the database is restored.
If I just run a restore script, my database is restored,
but unders 'security' for the SQL registration (in
Enterprise Manager) the login associated with my DB is
missing.
If I first run my scripts to create a brand new database,
and then restore the database from a backup, the logins
are created, but after the restore script is done, the
association with DB seems to be broken: if I double-click
on my login under 'security' and look at the list of dbs
in 'database access', my Test1 db doesn't have a check
mark next to it (even though it was there before I ran
restore script), if I try to set the check, I get an error
21002, 'User already 'so-and-so' already exist'.
What I am doing wrong? Is there any way to backup a db in
such way so that logins could be also restored? Or is
there a way to restore a db, so that existing
accounts/permissions would not be affected, but only the
data be merged in?
Thanks a lot for any help/suggestions.
Sincerely,
VRHi
backing or restoring a user database does not create
logins ,the logins hv to pre-exist or u hv to create
logins before creating/restoring the user db. bcos logins
exists inside master db and not user db
but when u exec scripts which include the logins surely
they get created.
thanks
rahul
>--Original Message--
>Hello,
>I have a set of scripts, which create a database Test1,
>create accounts/roles/users, and grant permissions for
>those users to access the newly created database.
>When I ran the scripts for the 1st time, everything
worked
>just fine. Test1 db was created and I could successfully
>connect to it from my client application, add/remove
>records, etc.
>Then, I needed to move my db to a different server, so I
>decided to backup the database, and then to restore on
the
>new server.
>Here is the backup script I ran:
>USE master
>EXEC sp_adddumpdevice 'disk', 'Test1DB', 'Test1db.dat'
>BACKUP DATABASE Test1 TO Test1DB
>The database backup is created properly (when I
restored,
>I saw all the original records).
>The problem I am experiencing, though, is the messed up
>accounts/logins, once the database is restored.
>If I just run a restore script, my database is restored,
>but unders 'security' for the SQL registration (in
>Enterprise Manager) the login associated with my DB is
>missing.
>If I first run my scripts to create a brand new
database,
>and then restore the database from a backup, the logins
>are created, but after the restore script is done, the
>association with DB seems to be broken: if I double-
click
>on my login under 'security' and look at the list of dbs
>in 'database access', my Test1 db doesn't have a check
>mark next to it (even though it was there before I ran
>restore script), if I try to set the check, I get an
error
>21002, 'User already 'so-and-so' already exist'.
>What I am doing wrong? Is there any way to backup a db
in
>such way so that logins could be also restored? Or is
>there a way to restore a db, so that existing
>accounts/permissions would not be affected, but only the
>data be merged in?
>Thanks a lot for any help/suggestions.
>Sincerely,
>VR
>
>.
>|||Check out sp_change_users_login in the Books Online
<tsqlref.chm::/ts_sp_ca-cz_8qzy.htm>. This will report and/or correct
mismatches between users and logins due to a restore/attach.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"VR" <anonymous@.discussions.microsoft.com> wrote in message
news:033601c3cb79$a2806270$a101280a@.phx.gbl...
> Hello,
> I have a set of scripts, which create a database Test1,
> create accounts/roles/users, and grant permissions for
> those users to access the newly created database.
> When I ran the scripts for the 1st time, everything worked
> just fine. Test1 db was created and I could successfully
> connect to it from my client application, add/remove
> records, etc.
> Then, I needed to move my db to a different server, so I
> decided to backup the database, and then to restore on the
> new server.
> Here is the backup script I ran:
> USE master
> EXEC sp_adddumpdevice 'disk', 'Test1DB', 'Test1db.dat'
> BACKUP DATABASE Test1 TO Test1DB
> The database backup is created properly (when I restored,
> I saw all the original records).
> The problem I am experiencing, though, is the messed up
> accounts/logins, once the database is restored.
> If I just run a restore script, my database is restored,
> but unders 'security' for the SQL registration (in
> Enterprise Manager) the login associated with my DB is
> missing.
> If I first run my scripts to create a brand new database,
> and then restore the database from a backup, the logins
> are created, but after the restore script is done, the
> association with DB seems to be broken: if I double-click
> on my login under 'security' and look at the list of dbs
> in 'database access', my Test1 db doesn't have a check
> mark next to it (even though it was there before I ran
> restore script), if I try to set the check, I get an error
> 21002, 'User already 'so-and-so' already exist'.
> What I am doing wrong? Is there any way to backup a db in
> such way so that logins could be also restored? Or is
> there a way to restore a db, so that existing
> accounts/permissions would not be affected, but only the
> data be merged in?
> Thanks a lot for any help/suggestions.
> Sincerely,
> VR
>
>

Backing up the sql database(*.bak) and restoring the Database in VB.net

Hi There,

I want to know how to backup and restore database from VB.net. ie I am trying to put two buttons on my form, Backup and Restore. When I click on Backup a Backup of the SQL Database with extension of *.bak is created. Similarly when I click on restore and select a database with *.bak extension it should restore that database. I saw a software where it created a backup of Sql database with extension of .zip containing the bak file which is password protected. when u restore the database it automatically gets unzipped and the database is restored. Can anyone help me.

Thanks in advance.

Regards,

Amit

You can create stored procedure to perform backup/restore, then call the stored procedure from your code. For example:

use master
go

CREATE PROC sp_backup @.path sysname,@.dbname sysname,
@.dataDevice varchar(100),@.logDevice varchar(100)
as
DECLARE @.fullFileName sysname
SET @.fullFileName=@.path+@.dataDevice
--Create the data backup device.
EXEC sp_addumpdevice 'disk', @.dataDevice,@.fullFileName

--Create the log backup device.
SET @.fullFileName=@.path+@.logDevice
EXEC sp_addumpdevice 'disk', @.logDevice,@.fullFileName

-- Back up the full MyNwind database.
EXEC('BACKUP DATABASE'+@.dbname+' TO'+@.dataDevice)

-- Update activity has occurred since the full database backup.

-- Back up the log of the MyNwind database.
EXEC('BACKUP LOG'+@.dbname+' TO'+@.logDevice)
go

sp_backup 'c:\','Northwind','test_Data','test_Log'

Then call the sp:

string connectionString = @."Data Source=Confute\SQL2000;Initial Catalog=master;Integrated Security=SSPI;";

using (SqlConnection connection =
new SqlConnection(connectionString))
{

SqlCommand command = new SqlCommand("EXEC sp_backup @.path ,@.dbname,
@.dataDevice,@.logDevice", connection);
command.Connection.Open();

command.Parameters.Add("@.path", textBox1.Text)

//add all parameters here
command.ExecuteNonQuery();

}

Backing up the Services Master Key

What happens if you don't backup the Service Master Key, or don't know where
the backup is? Will this keep you from restoring the Master Database? How
does this backup play in the disaster recovery?Hi
"DBA GUY" wrote:
> What happens if you don't backup the Service Master Key, or don't know where
> the backup is? Will this keep you from restoring the Master Database? How
> does this backup play in the disaster recovery?
Check out
http://blogs.msdn.com/lcris/archive/2005/07/08/sql-server-2005-a-look-at-the-master-keys.aspx
and other posts on the blog. The absolute worse case will be that you won't
be able to access the encrypted data in any of your databases, but if you
know the passwords to the DMKs you can drop the SMK encryption and re-apply
it.
John

Backing up the Services Master Key

What happens if you don't backup the Service Master Key, or don't know where
the backup is? Will this keep you from restoring the Master Database? How
does this backup play in the disaster recovery?
Hi
"DBA GUY" wrote:

> What happens if you don't backup the Service Master Key, or don't know where
> the backup is? Will this keep you from restoring the Master Database? How
> does this backup play in the disaster recovery?
Check out
http://blogs.msdn.com/lcris/archive/2005/07/08/sql-server-2005-a-look-at-the-master-keys.aspx
and other posts on the blog. The absolute worse case will be that you won't
be able to access the encrypted data in any of your databases, but if you
know the passwords to the DMKs you can drop the SMK encryption and re-apply
it.
John

Backing up the Services Master Key

What happens if you don't backup the Service Master Key, or don't know where
the backup is? Will this keep you from restoring the Master Database? How
does this backup play in the disaster recovery?Hi
"DBA GUY" wrote:

> What happens if you don't backup the Service Master Key, or don't know whe
re
> the backup is? Will this keep you from restoring the Master Database? Ho
w
> does this backup play in the disaster recovery?
Check out
http://blogs.msdn.com/lcris/archive.../>
r-keys.aspx
and other posts on the blog. The absolute worse case will be that you won't
be able to access the encrypted data in any of your databases, but if you
know the passwords to the DMKs you can drop the SMK encryption and re-apply
it.
John