Stored Procedures in a Database backup

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Stored Procedures in a Database backup

Author

Topic

gmetaj
Starting Member

33 Posts

Posted - 2004-05-11 : 12:40:31

Is it possible to backup a MSSQL 7 database to another server with a different database name. Do the names of the databases have to be identically the same. If so would it also be possible to do incremental/differential backups on database A on server 1, copy the file over to server 2 and apply the changes to DatabaseB. Note that the databases have identical structures but different filenames.

Thanks a lot!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-11 : 13:06:51

You can restore a database using a different name than what the source is. So let's say you backup DatabaseA, you can restore the backup as DatabaseB.

So,

BACKUP DATABASE DatabaseA
TO DISK = F:\MSSQL\BACKUP\DatabaseA.BAK'
WITH INIT

Copy the file to the other server, then

RESTORE DATABASE DatabaseB
FROM DISK = 'C:\DatabaseA.BAK'
WITH REPLACE

If the MDF and LDF file are going to be located in a different path than where DatabaseA is located, then you'll need to use MOVE option in RESTORE command.

Does that help?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-11 : 13:13:33

Watch out for the collation....

Make sure both server are set up the same...

Brett

8-)

gmetaj
Starting Member

33 Posts

Posted - 2004-05-11 : 14:04:07

Thank YOU!

quote:
Originally posted by tduggan

You can restore a database using a different name than what the source is. So let's say you backup DatabaseA, you can restore the backup as DatabaseB.

So,

BACKUP DATABASE DatabaseA
TO DISK = F:\MSSQL\BACKUP\DatabaseA.BAK'
WITH INIT

Copy the file to the other server, then

RESTORE DATABASE DatabaseB
FROM DISK = 'C:\DatabaseA.BAK'
WITH REPLACE

If the MDF and LDF file are going to be located in a different path than where DatabaseA is located, then you'll need to use MOVE option in RESTORE command.

Does that help?

Tara

gmetaj
Starting Member

33 Posts

Posted - 2004-05-11 : 14:07:45

Thank you for your help! So is there a way to implement differential backup act somehow like an incremental backup, ie. that backs up files since last differential backup. I heard someone say that you could use triggers, but was not sure it could be implemented. If that is the case could it also be possible to apply the incremental backup of Database A on server A to the database B on server 2.

Thank you in advance!

quote:
Originally posted by tduggan

You can restore a database using a different name than what the source is. So let's say you backup DatabaseA, you can restore the backup as DatabaseB.

So,

BACKUP DATABASE DatabaseA
TO DISK = F:\MSSQL\BACKUP\DatabaseA.BAK'
WITH INIT

Copy the file to the other server, then

RESTORE DATABASE DatabaseB
FROM DISK = 'C:\DatabaseA.BAK'
WITH REPLACE

If the MDF and LDF file are going to be located in a different path than where DatabaseA is located, then you'll need to use MOVE option in RESTORE command.

Does that help?

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-11 : 14:58:34

Yes you can use differentials as well.

BACKUP DATABASE DBName
TO DISK = 'C:\DBName.BAK'
WITH INIT, DIFFERENTIAL

I don't see any reason to use a trigger.

Are you looking to have a disaster recovery server? If so, you'll need to use log shipping whether it be SQL Server's built-in log shipping with Enterprise Edition or your own custom log shipping.

Tara

gmetaj
Starting Member

33 Posts

Posted - 2004-05-11 : 15:17:57

What I am trying to do is periodic updates to the database B on Server/Webserver 2. databaseB is on the internet being accessed by users. The content of databaseB on server 2 does not change, it is only being read. Changes/updates are done to databaseA on SQL 7 (server2) running on a windows box. changes occur here but there is not very many compared to the size of the database. I want to do apply changes occurring in database A (at night) to databaseB. both databaseA and databaseB are big in size and I cannot do a full back up and trasnfer the file over every night. I want to transfer only the changes to databaseB. I am considering different options such as FTP-ing the updates. I was also reading about Snapshot and Transactional replications.

Any help is greatly appreciated.

Thank you!

quote:
Originally posted by tduggan

Yes you can use differentials as well.

BACKUP DATABASE DBName
TO DISK = 'C:\DBName.BAK'
WITH INIT, DIFFERENTIAL

I don't see any reason to use a trigger.

Are you looking to have a disaster recovery server? If so, you'll need to use log shipping whether it be SQL Server's built-in log shipping with Enterprise Edition or your own custom log shipping.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-11 : 15:20:54

I would go with replication for this.

If you used the BACKUP/RESTORE approach, databaseB would be unavailable while the RESTORE was occurring.

Tara

gmetaj
Starting Member

33 Posts

Posted - 2004-05-11 : 15:31:01

Thank you!

quote:
Originally posted by tduggan

I would go with replication for this.

If you used the BACKUP/RESTORE approach, databaseB would be unavailable while the RESTORE was occurring.

Tara

gmetaj
Starting Member

33 Posts

Posted - 2004-05-11 : 15:44:46

in the command below does "WITH INIT, DIFFERENTIAL" do differential or full backup. How do i know which one it is?

Thanks!

BACKUP DATABASE DBName
TO DISK = 'C:\DBName.BAK'
WITH INIT, DIFFERENTIAL

quote:
Originally posted by tduggan

Yes you can use differentials as well.

BACKUP DATABASE DBName
TO DISK = 'C:\DBName.BAK'
WITH INIT, DIFFERENTIAL

I don't see any reason to use a trigger.

Are you looking to have a disaster recovery server? If so, you'll need to use log shipping whether it be SQL Server's built-in log shipping with Enterprise Edition or your own custom log shipping.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-11 : 16:17:32

WITH INIT says to overwrite the file if it exists. WITH DIFFERENTIAL says to take a differential backup. If you want a full backup, then remove the word DIFFERENTIAL.

I would read up on BACKUP DATABASE in SQL Server Books Online for more details.

Tara

gmetaj
Starting Member

33 Posts

Posted - 2004-05-11 : 16:59:03

Tara, thank you for all your help. I tried using the command
RESTORE DATABASE DatabaseB
FROM DISK = 'C:\DatabaseA.BAK'
WITH REPLACE
to restore with a different name but t says that

Server: Msg 3156, Level 16, State 2, Line 1
The file 'C:\MSSQL7\data\DatabaseA_Data.MDF' cannot be used by RESTORE. Consider using the WITH MOVE option to identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.

I also used the WITH MOVE option and it did not work either.

Thanks!

quote:
Originally posted by tduggan

You can restore a database using a different name than what the source is. So let's say you backup DatabaseA, you can restore the backup as DatabaseB.

So,

BACKUP DATABASE DatabaseA
TO DISK = F:\MSSQL\BACKUP\DatabaseA.BAK'
WITH INIT

Copy the file to the other server, then

RESTORE DATABASE DatabaseB
FROM DISK = 'C:\DatabaseA.BAK'
WITH REPLACE

If the MDF and LDF file are going to be located in a different path than where DatabaseA is located, then you'll need to use MOVE option in RESTORE command.

Does that help?

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-11 : 17:01:42

So what is the command that you ran with WITH MOVE? Are you restoring this onto a different sever or the same server but a different name? Is the .BAK a full backup or a differential backup? What does this show:

RESTORE FILELISTONLY
FROM DISK = 'C:\DatabaseA.BAK'

Tara

gmetaj
Starting Member

33 Posts

Posted - 2004-05-11 : 17:49:35

Tara, Thank you so much. I got it to work for a full backup. the command show below works fine:

BACKUP DATABASE Trust
TO DISK = 'c:\MSSQL7\Backup\TrustFullB.bak'
WITH INIT
RESTORE FILELISTONLY
FROM DISK = 'c:\MSSQL7\Backup\TrustFullB.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\MSSQL7\Backup\TrustFullB.bak'
WITH MOVE 'Trust_Data' TO 'c:\MSSQL7\Data\testdb.mdf',
MOVE 'Trust_log' TO 'c:\MSSQL7\data\testdb.ldf', REPLACE
GO

Trust = DatabaseA
TestDB = DatabaseB

HOwever I cannot get it to work for incremental backups. When I do BACKUP DATABASE Trust
TO DISK = 'c:\MSSQL7\Backup\TrustFullB.bak'
WITH INIT, DIFFERENTIAL
RESTORE FILELISTONLY
FROM DISK = 'c:\MSSQL7\Backup\TrustFullB.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\MSSQL7\Backup\TrustFullB.bak'
WITH MOVE 'Trust_Data' TO 'c:\MSSQL7\Data\testdb.mdf',
MOVE 'Trust_log' TO 'c:\MSSQL7\data\testdb.ldf', NORECOVERY
GO
i get the error message show below:
Server: Msg 4306, Level 16, State 1, Line 6
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 6
Backup or restore operation terminating abnormally.

I have tried NORECOVERY, RECOVERY or STANDBY and they give me the same message. I actually needit with recover or with STANDBY. Any suggestions?

Thank you so much!

quote:
Originally posted by tduggan

So what is the command that you ran with WITH MOVE? Are you restoring this onto a different sever or the same server but a different name? Is the .BAK a full backup or a differential backup? What does this show:

RESTORE FILELISTONLY
FROM DISK = 'C:\DatabaseA.BAK'

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-11 : 18:04:47

To apply a differential backup, you need to specify WITH NORECOVERY at the time you did the RESTORE of the full backup.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources