Copy a database, then test its restore...

Question

Copy a database, then test its restore...

jralston88

SSCommitted

Points: 1931
More actions
April 13, 2012 at 10:07 am

#259963

We have multiple instances of SQL Server 2000, 2005 and 2008.
We want to test our restore process for each database. Since we don't want to test on our production environments, our plan was to copy each database over to a test server. It has SQL Server 2008. So with a SQL Server 2000 database, we would copy it over to the server, change the compatibility setting to 2000, then test the restore process.
My questions:
How do you 'copy' a database, then import to another server? I'm assuming all users would have to be off the database for this process.
Is there a query you can run to confirm a database has been restored properly? I'm looking to test outside the production entirely.
Any URLs/thoughts on the matter would be appreciated. Thank you.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

CapnHector SSCoach Points: 16143 More actions · Answer 1

we do a similar process, we backup the database, zip it to condense it a little (we dont use backup compression) copy the zip file over the network, unzip the file, the restore it on our second server. from here google will be your best friend on how to actually run the backups, zip them, copy to the other server, and restore.

to check if our database was restored properly we have tables that track creation dates with a datetime stamp so i check that the MAX(datetimestamp) is where it should be after the restore.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

vinu512 SSCoach Points: 15729 More actions · Answer 2

+1

We do it the same way:

1. Take Backup.

2. Archive/Zip it.

3. Copy the compressed zip folder to the server.

4. Unzip it.

5. Restore. :hehe:

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

jitendra.padhiyar SSCertifiable Points: 5824 More actions · Answer 3

Hi Hector,

Is import/Export good solution?

I mean we will first export db to excel file and than copy that excel file to remote server where we can import that db.

Thanks,

SQLisAwe5oMe SSChampion Points: 14027 More actions · Answer 4

jralston88 (4/13/2012)
We have multiple instances of SQL Server 2000, 2005 and 2008.
We want to test our restore process for each database. Since we don't want to test on our production environments, our plan was to copy each database over to a test server. It has SQL Server 2008. So with a SQL Server 2000 database, we would copy it over to the server, change the compatibility setting to 2000, then test the restore process.
My questions:
How do you 'copy' a database, then import to another server? I'm assuming all users would have to be off the database for this process.
Is there a query you can run to confirm a database has been restored properly? I'm looking to test outside the production entirely.
Any URLs/thoughts on the matter would be appreciated. Thank you.

Hi,

Can someone confirm, if you are able to do a backup of a db from 2008 and restore in 2000 or 2005?.....I know you can do the other way around but.....please confirm

Regards,
SQLisAwe5oMe.

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 5

SQLCrazyCertified (4/16/2012)
jralston88 (4/13/2012)
We have multiple instances of SQL Server 2000, 2005 and 2008.
We want to test our restore process for each database. Since we don't want to test on our production environments, our plan was to copy each database over to a test server. It has SQL Server 2008. So with a SQL Server 2000 database, we would copy it over to the server, change the compatibility setting to 2000, then test the restore process.
My questions:
How do you 'copy' a database, then import to another server? I'm assuming all users would have to be off the database for this process.
Is there a query you can run to confirm a database has been restored properly? I'm looking to test outside the production entirely.
Any URLs/thoughts on the matter would be appreciated. Thank you.
Hi,
Can someone confirm, if you are able to do a backup of a db from 2008 and restore in 2000 or 2005?.....I know you can do the other way around but.....please confirm

No, you can not backup a database in SQL Server 2008 and restore it to SQL Server 2000 or SQL Server 2005.

SQLisAwe5oMe SSChampion Points: 14027 More actions · Answer 6

Thanks Lynn for your quick reply. 🙂

Regards,

TA:

Regards,
SQLisAwe5oMe.

CapnHector SSCoach Points: 16143 More actions · Answer 7

jitendra.padhiyar (4/16/2012)
Hi Hector,
Is import/Export good solution?
I mean we will first export db to excel file and than copy that excel file to remote server where we can import that db.
Thanks,

i would run BACKUP DATABASE and RESTORE DATABASE. that way you have an exact copy of your database and can test the restore from the .bak file. IMHO the only way to backup and restore a database. if you are looking at exporting only part of your data to import into another server i would look at SSIS for that.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]