Copy Large Database

Does anyone know of a good way to copy a large database or backup of a database (~10 GB) across long distances (transatlantic) in a minimal amount of time?

I have tried regular file copies, BCP, DTC, my own handwritten extraction utility, PCAnywhere, and several file splitters, all to no avail. To complicate things, the source is SQL 7 with a different code page from the destination, which is SQL 2K. The file copies always seem to get interrupted after several hours.How about some form of replication. That way the live data is now checked and the copy come from a backup.|||Thanks for the response. The situation here is that these are 24x365 production databases with high levels of activity that support manufacturing production, so replicating on an ongoing basis will be costly to the site. I'd really prefer some method of capturing a snapshot.

Add to that the fact that since it is 24x365, getting time to try things is at a premium. Many of the solutions I spoke of earlier worked well in test environments where the geographical distances were a few hundred miles, but broke down when going cross-country or transatlantic.|||Dump and zip and ftp?|||Yeah, I think that's about the only thing left. Docs all say that DUMP and BACKUP are synonymous; is that true?|||yeah...dump is a 6.5 term...just habit...

Did you mention something about a different collation?

If it is, I don't think you'll be able to RESTORE it...|||if you use SQLLitespeed (or other utilities allowing compression while backup is being taken)- you can always compress it on the fly here, ace or rar the resulting backup , ftp it over and extract on the other side.

simas

Copy files after backup in maintenance plan

Hello,

We're new users of SQL Server 2005. I created two maintenance plans...one to backup the database at 2 AM daily and one to back up transaction logs every 30 minutes. These maintenance plans write to a local disk. What we want to do, within the maintenance plan, is copy the files as soon as they are written to a remote server. Is that possible?

Thanks in advance.

Create a shared folder in the remote server and specify the path in the maintenace plan so that the backup will directly fall in the remote server. You need to ensure that the sql startup account should have modify privileges to the shared folder in remote server for the maintenance jobs to succeed !..........

else you can also add a job step in maintenance jobs using xcopy command to copy the files to remote server.

Thanxx

Deepak

|||

Maintenance Plan is nothing but a SSIS (Integration Services) project. What u can do is open the MP in BIDS (business intellegence Development studio) ENV and add a *File System Task* and make the requried changes.

Madhu

|||

Thank you both for the fast replys....

From what I can tell, the maintenance plans are stored in the database. However, when I go into BIDS the file/open is only for OS files. What do I need to do to open the maintenance plan in BIDS?

Thanks again in advance!

|||

Hi Marty,

Go to BIDS--> create a New project -- > Open the project.

In solution explorer you can see "SSIS packages" folder --> Right click on the folder

Select "Add existing package"

Mention the servername & Authentication mode in the window then you can select the maintainence plan you have created.

|||Excellent...thank you so much

Copy files after backup in maintenance plan

Hello,

We're new users of SQL Server 2005. I created two maintenance plans...one to backup the database at 2 AM daily and one to back up transaction logs every 30 minutes. These maintenance plans write to a local disk. What we want to do, within the maintenance plan, is copy the files as soon as they are written to a remote server. Is that possible?

Thanks in advance.

Create a shared folder in the remote server and specify the path in the maintenace plan so that the backup will directly fall in the remote server. You need to ensure that the sql startup account should have modify privileges to the shared folder in remote server for the maintenance jobs to succeed !..........

else you can also add a job step in maintenance jobs using xcopy command to copy the files to remote server.

Thanxx

Deepak

|||

Maintenance Plan is nothing but a SSIS (Integration Services) project. What u can do is open the MP in BIDS (business intellegence Development studio) ENV and add a *File System Task* and make the requried changes.

Madhu

|||

Thank you both for the fast replys....

From what I can tell, the maintenance plans are stored in the database. However, when I go into BIDS the file/open is only for OS files. What do I need to do to open the maintenance plan in BIDS?

Thanks again in advance!

|||

Hi Marty,

Go to BIDS--> create a New project -- > Open the project.

In solution explorer you can see "SSIS packages" folder --> Right click on the folder

Select "Add existing package"

Mention the servername & Authentication mode in the window then you can select the maintainence plan you have created.

|||Excellent...thank you so muchsqlsql

copy file

hi guys,

anybody know the syntax in SQL to copy file to other server.

we want to copy our db backup files automaticaly to other server.

thanks

-wei

Hi

It wud be helpful if u can be more specific, like if u wanna back up ur entire DB or just copy data from one table to another table in another DB

|||

hi,

I want to copy file to other server using SQL.

I found out that we can use xp_cmdshell but I can make it work.

my syntax is " exec xp_cmdshell 'copy c:\folder\test.xxx

\\server1\folder1, no_ouput ;

on the result pane it said succesful, but when I look to the destination folder there is no file that I copied.

|||

hi dedy,

hi try mapping "\\server1\folder1" to qa drive letter

to map

1. open windows explorer.

2. click tools.

3. clcik on map network drive

4. enter necessary permission

you can assign a specific user (using different username option) to your mappings

to make sure you wont run on a permission problem with NTFS

run

exec xp_cmdshell 'copy c:\folder\test.xxx x:' <--where x: is the mapped network drive

regards,

joey

|||

hi joey,

I have tries your suggestion.

but there is an error that said "The system cannot find the drive specified"

I'm using SQL 2005.

I already map the folder using administrator account.

please advice.

copy db sql7 ->2000 newbie question

Hi,
I have a backup copy of a database in sql 7. I am copying it over to sql
2000 and then restoring it. There are 3 views that show up in both the table
and view section in sql 2000 but they are only in the view section in sql 7.
(sql 7 running on windows 2000 server/sql 2000 on windows 2003 server.) why
is that happening?
Thanks for any help with this. much appreciated!
kak37
On Jun 13, 10:12 am, kak37 <k...@.discussions.microsoft.com> wrote:
> Hi,
> I have a backup copy of a database in sql 7. I am copying it over to sql
> 2000 and then restoring it. There are 3 views that show up in both the table
> and view section in sql 2000 but they are only in the view section in sql 7.
> (sql 7 running on windows 2000 server/sql 2000 on windows 2003 server.) why
> is that happening?
> Thanks for any help with this. much appreciated!
> --
> kak37
Try refresh at databse level (EM) and check again
sqlsql

copy db sql7 ->2000 newbie question

Hi,
I have a backup copy of a database in sql 7. I am copying it over to sql
2000 and then restoring it. There are 3 views that show up in both the table
and view section in sql 2000 but they are only in the view section in sql 7.
(sql 7 running on Windows 2000 server/sql 2000 on windows 2003 server.) why
is that happening?
Thanks for any help with this. much appreciated!
--
kak37On Jun 13, 10:12 am, kak37 <k...@.discussions.microsoft.com> wrote:
> Hi,
> I have a backup copy of a database in sql 7. I am copying it over to sql
> 2000 and then restoring it. There are 3 views that show up in both the tab
le
> and view section in sql 2000 but they are only in the view section in sql
7.
> (sql 7 running on Windows 2000 server/sql 2000 on windows 2003 server.) wh
y
> is that happening?
> Thanks for any help with this. much appreciated!
> --
> kak37
Try refresh at databse level (EM) and check again

copy db sql7 ->2000 newbie question

Hi,
I have a backup copy of a database in sql 7. I am copying it over to sql
2000 and then restoring it. There are 3 views that show up in both the table
and view section in sql 2000 but they are only in the view section in sql 7.
(sql 7 running on windows 2000 server/sql 2000 on windows 2003 server.) why
is that happening?
Thanks for any help with this. much appreciated!
--
kak37On Jun 13, 10:12 am, kak37 <k...@.discussions.microsoft.com> wrote:
> Hi,
> I have a backup copy of a database in sql 7. I am copying it over to sql
> 2000 and then restoring it. There are 3 views that show up in both the table
> and view section in sql 2000 but they are only in the view section in sql 7.
> (sql 7 running on windows 2000 server/sql 2000 on windows 2003 server.) why
> is that happening?
> Thanks for any help with this. much appreciated!
> --
> kak37
Try refresh at databse level (EM) and check again

copy DB from 2005 to 2000?

Hi;
I need to copy a database fomr Sql Server 2005 to Sql Server 2000. I tried
backup/restore but the restore did not work.
What is the easiest way to do this? I need to copy everything and the
database has the same name on both systems.
--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htmHi
I think only way is DTS, but ... try and you'll see :)
Mex
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:81C3719B-CB27-4455-89DB-D907CF9F2668@.microsoft.com...
> Hi;
> I need to copy a database fomr Sql Server 2005 to Sql Server 2000. I tried
> backup/restore but the restore did not work.
> What is the easiest way to do this? I need to copy everything and the
> database has the same name on both systems.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>|||Hi, David,
I understand that you would like to how to migrate your SQL Server 2005
database to SQL Server 2000.
If I have misunderstood, please let me know.
Due to the incompatibility issue, there is no very convenient way to do
this process. However you can try the following process:
1. Fully back up your SQL Server 2005 database;
2. Create a SQL Server Integration Services project via SQL Server Business
Intelligence Development Studio;
3. On the Control Flow tab, drag a Transfer SQL Server Objects, and set the
source connection, destination connection, SourceDatabase and
DestinationDatabase; if the DestinationDatabase does not exist, please
manually create an empty database in Enterprise Manager.
4. Execute the task with the following options:
DropObjectsFirst: True
IncludeExtendedProperties: True
CopyData: True
ExistingData: Replace
CopySchema: True
UseCollation: True
IncludeDependentObjects: True
CopyAllObjects: Flase
CopyAllTables: True
CopyAllViews: True
CopyAllStoredProcedures: True
CopyAllDefinedFunctions: True
CopyAllDefaults: True
CopyAllUserDefinedDataTypes: True
<other options>: False
CopyDatabaseUsers: False
CopyDatabaseRoles: False
CopySqlServerLogins: False
CopyObjectLevelPermissions: False
CopyIndexes: True
CopyTriggers: True
CopyFullTextIndexes: True
CopyPrimaryKeys: True
CopyForeignKeys: True
GenerateScriptsInUnicode: True
5. For the jobs, logins and passwords, please refer to the following KB
articles:
How to move databases between computers that are running SQL Server
http://support.microsoft.com/?id=314546
How to transfer logins and passwords between instances of SQL Server
http://support.microsoft.com/?id=246133
After transfer the logins and passwords, you may need to re-assign
server roles to the logins on the SQL Server 2000;
6. Manually create the related users and assign the permissions.
If you just want to transfer the data, the 5th and 6th steps can be
ignored. It is recommended that you install SQL Server 2005 SP2 and SQL
Server 2000 SP4 before the process.
Microsoft SQL Server 2005 Service Pack 2
http://www.microsoft.com/downloads/details.aspx?FamilyID=d07219b2-1e23-49c8-
8f0c-63fa18f26d3a&DisplayLang=en
Microsoft SQL Server 2000 Service Pack 4
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=8E2D
FC8D-C20E-4446-99A9-B7F0213F8BC5
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||They don't make this easy do they...
--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
"Charles Wang[MSFT]" wrote:
> Hi, David,
> I understand that you would like to how to migrate your SQL Server 2005
> database to SQL Server 2000.
> If I have misunderstood, please let me know.
> Due to the incompatibility issue, there is no very convenient way to do
> this process. However you can try the following process:
> 1. Fully back up your SQL Server 2005 database;
> 2. Create a SQL Server Integration Services project via SQL Server Business
> Intelligence Development Studio;
> 3. On the Control Flow tab, drag a Transfer SQL Server Objects, and set the
> source connection, destination connection, SourceDatabase and
> DestinationDatabase; if the DestinationDatabase does not exist, please
> manually create an empty database in Enterprise Manager.
> 4. Execute the task with the following options:
> DropObjectsFirst: True
> IncludeExtendedProperties: True
> CopyData: True
> ExistingData: Replace
> CopySchema: True
> UseCollation: True
> IncludeDependentObjects: True
> CopyAllObjects: Flase
> CopyAllTables: True
> CopyAllViews: True
> CopyAllStoredProcedures: True
> CopyAllDefinedFunctions: True
> CopyAllDefaults: True
> CopyAllUserDefinedDataTypes: True
> <other options>: False
> CopyDatabaseUsers: False
> CopyDatabaseRoles: False
> CopySqlServerLogins: False
> CopyObjectLevelPermissions: False
> CopyIndexes: True
> CopyTriggers: True
> CopyFullTextIndexes: True
> CopyPrimaryKeys: True
> CopyForeignKeys: True
> GenerateScriptsInUnicode: True
> 5. For the jobs, logins and passwords, please refer to the following KB
> articles:
> How to move databases between computers that are running SQL Server
> http://support.microsoft.com/?id=314546
> How to transfer logins and passwords between instances of SQL Server
> http://support.microsoft.com/?id=246133
> After transfer the logins and passwords, you may need to re-assign
> server roles to the logins on the SQL Server 2000;
> 6. Manually create the related users and assign the permissions.
> If you just want to transfer the data, the 5th and 6th steps can be
> ignored. It is recommended that you install SQL Server 2005 SP2 and SQL
> Server 2000 SP4 before the process.
> Microsoft SQL Server 2005 Service Pack 2
> http://www.microsoft.com/downloads/details.aspx?FamilyID=d07219b2-1e23-49c8-
> 8f0c-63fa18f26d3a&DisplayLang=en
> Microsoft SQL Server 2000 Service Pack 4
> http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=8E2D
> FC8D-C20E-4446-99A9-B7F0213F8BC5
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>
>
>
>
>|||Hi, Dave,
No, the process is not as convenient as backup/restore database from SQL
2000 to SQL 2005. Actually, the process of moving all the database objects
from SQL 2000 to SQL 2005 is also not very simple. Please refer to the two
articles in the step 5 in my first reply for that process.
Please try the recommendations first. If you encounter any issues, please
feel free to post back.
Have a nice day!
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================sqlsql

copy db

in sql 2005 -- what's the best way for me to make a complete copy of my database - including indexes and keys and everything??
i tried backup but when restoring to the second i get an error with multiple media sets.What error are you getting on the restore?|||If you are restoring to the same server, then you need to rename all of the physical files.|||Msg 3132, Level 16, State 1, Line 1
The media set has 2 media families but only 1 are provided. All members must be provided.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

I am doing

RESTORE DATABASE trafficr
FROM DISK = 'c:\backups\traffic.bak'
WITH MOVE 'traffic' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\trafficr.mdf',
MOVE 'traffic_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\trafficr_log.ldf',

REPLACE|||What's with the commas at the end of each line?|||this is the syntax i'm using for sql server - -is it wrong?

i think it once worked but not working now - maybe with the way i did the backup? how should i do the backup so one media|||post the output of this:

RESTORE headeronly FROM DISK = 'c:\backups\traffic.bak'

you may have more than one backup in the file.sqlsql

Copy Database?

I am trying to create a backup of our production database, but a restore of
the backup doesn't seem to be grabbing everything (and I'm not sure what
it's missing, I just know things aren't working properly in the backup
database). Anyway, I was wondering if there is a way of copying EVERYTHING
from the live database without taking it offline. I tried the Copy Database
Wizard, but it chokes and dies if anybody is connected to the database. Any
suggestions would be greatly appreciated.
Willie Bodger
A backup does copy everything in the database and is the preferred way to do
what you are attempting. When you give details like "things aren't working
properly" it is pretty hard for someone to give you a correct response to
your problem. I will guess and say you have issues with the users not
matching up to the Logins. Logins are server level and do not come over
with the db but the users do. These should help:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Willie Bodger" <willie@.bodgerfam.com> wrote in message
news:Oe3aNZwNFHA.3716@.TK2MSFTNGP14.phx.gbl...
>I am trying to create a backup of our production database, but a restore of
>the backup doesn't seem to be grabbing everything (and I'm not sure what
>it's missing, I just know things aren't working properly in the backup
>database). Anyway, I was wondering if there is a way of copying EVERYTHING
>from the live database without taking it offline. I tried the Copy Database
>Wizard, but it chokes and dies if anybody is connected to the database. Any
>suggestions would be greatly appreciated.
> Willie Bodger
>
|||For instance, when I try to run a stored procedure, I get this error:
OLE Automation Error Information
HRESULT: 0x800401f3
Source: ODSOLE Extended Procedure
Description: Invalid class string
error! 1
Any thoughts, or is there still too little info?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e0LMjkwNFHA.3076@.TK2MSFTNGP14.phx.gbl...
>A backup does copy everything in the database and is the preferred way to
>do what you are attempting. When you give details like "things aren't
>working properly" it is pretty hard for someone to give you a correct
>response to your problem. I will guess and say you have issues with the
>users not matching up to the Logins. Logins are server level and do not
>come over with the db but the users do. These should help:
> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
> http://www.support.microsoft.com/?id=314546 Moving DB's between
> Servers
> http://www.support.microsoft.com/?id=224071 Moving SQL Server
> Databases to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> Restore
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
> a Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or
> Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scri...p?scriptid=599
> Restoring a .mdf
> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
> for SQL Server
>
> --
> Andrew J. Kelly SQL MVP
>
> "Willie Bodger" <willie@.bodgerfam.com> wrote in message
> news:Oe3aNZwNFHA.3716@.TK2MSFTNGP14.phx.gbl...
>
|||You are attempting to execute a non-native (ie: user created) sql server
extended stored procedure. They too are at the server level and not part of
the database. They are simply files on the hard drive and you are
responsible for copying them over as well. Since these are basically DLL's
it could be almost anything depending on how it was coded and what else it
is dependant on. I would find who created it and see what they have to say.
Andrew J. Kelly SQL MVP
"Willie Bodger" <willie@.bodgerfam.com> wrote in message
news:OYrqYexNFHA.3156@.TK2MSFTNGP15.phx.gbl...
> For instance, when I try to run a stored procedure, I get this error:
> OLE Automation Error Information
> HRESULT: 0x800401f3
> Source: ODSOLE Extended Procedure
> Description: Invalid class string
> error! 1
> Any thoughts, or is there still too little info?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e0LMjkwNFHA.3076@.TK2MSFTNGP14.phx.gbl...
>

Copy Database?

I am trying to create a backup of our production database, but a restore of
the backup doesn't seem to be grabbing everything (and I'm not sure what
it's missing, I just know things aren't working properly in the backup
database). Anyway, I was wondering if there is a way of copying EVERYTHING
from the live database without taking it offline. I tried the Copy Database
Wizard, but it chokes and dies if anybody is connected to the database. Any
suggestions would be greatly appreciated.
Willie BodgerA backup does copy everything in the database and is the preferred way to do
what you are attempting. When you give details like "things aren't working
properly" it is pretty hard for someone to give you a correct response to
your problem. I will guess and say you have issues with the users not
matching up to the Logins. Logins are server level and do not come over
with the db but the users do. These should help:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Willie Bodger" <willie@.bodgerfam.com> wrote in message
news:Oe3aNZwNFHA.3716@.TK2MSFTNGP14.phx.gbl...
>I am trying to create a backup of our production database, but a restore of
>the backup doesn't seem to be grabbing everything (and I'm not sure what
>it's missing, I just know things aren't working properly in the backup
>database). Anyway, I was wondering if there is a way of copying EVERYTHING
>from the live database without taking it offline. I tried the Copy Database
>Wizard, but it chokes and dies if anybody is connected to the database. Any
>suggestions would be greatly appreciated.
> Willie Bodger
>|||For instance, when I try to run a stored procedure, I get this error:
OLE Automation Error Information
HRESULT: 0x800401f3
Source: ODSOLE Extended Procedure
Description: Invalid class string
error! 1
Any thoughts, or is there still too little info?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e0LMjkwNFHA.3076@.TK2MSFTNGP14.phx.gbl...
>A backup does copy everything in the database and is the preferred way to
>do what you are attempting. When you give details like "things aren't
>working properly" it is pretty hard for someone to give you a correct
>response to your problem. I will guess and say you have issues with the
>users not matching up to the Logins. Logins are server level and do not
>come over with the db but the users do. These should help:
> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
> http://www.support.microsoft.com/?id=314546 Moving DB's between
> Servers
> http://www.support.microsoft.com/?id=224071 Moving SQL Server
> Databases to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> Restore
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
> a Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or
> Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> Restoring a .mdf
> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
> for SQL Server
>
> --
> Andrew J. Kelly SQL MVP
>
> "Willie Bodger" <willie@.bodgerfam.com> wrote in message
> news:Oe3aNZwNFHA.3716@.TK2MSFTNGP14.phx.gbl...
>>I am trying to create a backup of our production database, but a restore
>>of the backup doesn't seem to be grabbing everything (and I'm not sure
>>what it's missing, I just know things aren't working properly in the
>>backup database). Anyway, I was wondering if there is a way of copying
>>EVERYTHING from the live database without taking it offline. I tried the
>>Copy Database Wizard, but it chokes and dies if anybody is connected to
>>the database. Any suggestions would be greatly appreciated.
>> Willie Bodger
>|||You are attempting to execute a non-native (ie: user created) sql server
extended stored procedure. They too are at the server level and not part of
the database. They are simply files on the hard drive and you are
responsible for copying them over as well. Since these are basically DLL's
it could be almost anything depending on how it was coded and what else it
is dependant on. I would find who created it and see what they have to say.
--
Andrew J. Kelly SQL MVP
"Willie Bodger" <willie@.bodgerfam.com> wrote in message
news:OYrqYexNFHA.3156@.TK2MSFTNGP15.phx.gbl...
> For instance, when I try to run a stored procedure, I get this error:
> OLE Automation Error Information
> HRESULT: 0x800401f3
> Source: ODSOLE Extended Procedure
> Description: Invalid class string
> error! 1
> Any thoughts, or is there still too little info?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e0LMjkwNFHA.3076@.TK2MSFTNGP14.phx.gbl...
>>A backup does copy everything in the database and is the preferred way to
>>do what you are attempting. When you give details like "things aren't
>>working properly" it is pretty hard for someone to give you a correct
>>response to your problem. I will guess and say you have issues with the
>>users not matching up to the Logins. Logins are server level and do not
>>come over with the db but the users do. These should help:
>> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
>> http://www.support.microsoft.com/?id=314546 Moving DB's between
>> Servers
>> http://www.support.microsoft.com/?id=224071 Moving SQL Server
>> Databases to a New Location with Detach/Attach
>> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
>> Restore
>> http://www.support.microsoft.com/?id=246133 How To Transfer Logins
>> and Passwords Between SQL Servers
>> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
>> after a Restore
>> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
>> users
>> http://www.support.microsoft.com/?id=168001 User Logon and/or
>> Permission Errors After Restoring Dump
>> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
>> Issues When a Database Is Moved Between SQL Servers
>> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
>> Restoring a .mdf
>> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
>> for SQL Server
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Willie Bodger" <willie@.bodgerfam.com> wrote in message
>> news:Oe3aNZwNFHA.3716@.TK2MSFTNGP14.phx.gbl...
>>I am trying to create a backup of our production database, but a restore
>>of the backup doesn't seem to be grabbing everything (and I'm not sure
>>what it's missing, I just know things aren't working properly in the
>>backup database). Anyway, I was wondering if there is a way of copying
>>EVERYTHING from the live database without taking it offline. I tried the
>>Copy Database Wizard, but it chokes and dies if anybody is connected to
>>the database. Any suggestions would be greatly appreciated.
>> Willie Bodger
>>
>

Copy Database?

I am trying to create a backup of our production database, but a restore of
the backup doesn't seem to be grabbing everything (and I'm not sure what
it's missing, I just know things aren't working properly in the backup
database). Anyway, I was wondering if there is a way of copying EVERYTHING
from the live database without taking it offline. I tried the Copy Database
Wizard, but it chokes and dies if anybody is connected to the database. Any
suggestions would be greatly appreciated.
Willie BodgerA backup does copy everything in the database and is the preferred way to do
what you are attempting. When you give details like "things aren't working
properly" it is pretty hard for someone to give you a correct response to
your problem. I will guess and say you have issues with the users not
matching up to the Logins. Logins are server level and do not come over
with the db but the users do. These should help:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Willie Bodger" <willie@.bodgerfam.com> wrote in message
news:Oe3aNZwNFHA.3716@.TK2MSFTNGP14.phx.gbl...
>I am trying to create a backup of our production database, but a restore of
>the backup doesn't seem to be grabbing everything (and I'm not sure what
>it's missing, I just know things aren't working properly in the backup
>database). Anyway, I was wondering if there is a way of copying EVERYTHING
>from the live database without taking it offline. I tried the Copy Database
>Wizard, but it chokes and dies if anybody is connected to the database. Any
>suggestions would be greatly appreciated.
> Willie Bodger
>|||For instance, when I try to run a stored procedure, I get this error:
OLE Automation Error Information
HRESULT: 0x800401f3
Source: ODSOLE Extended Procedure
Description: Invalid class string
error! 1
Any thoughts, or is there still too little info?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e0LMjkwNFHA.3076@.TK2MSFTNGP14.phx.gbl...
>A backup does copy everything in the database and is the preferred way to
>do what you are attempting. When you give details like "things aren't
>working properly" it is pretty hard for someone to give you a correct
>response to your problem. I will guess and say you have issues with the
>users not matching up to the Logins. Logins are server level and do not
>come over with the db but the users do. These should help:
> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
> http://www.support.microsoft.com/?id=314546 Moving DB's between
> Servers
> http://www.support.microsoft.com/?id=224071 Moving SQL Server
> Databases to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> Restore
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
> a Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or
> Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scr...sp?scriptid=599
> Restoring a .mdf
> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
> for SQL Server
>
> --
> Andrew J. Kelly SQL MVP
>
> "Willie Bodger" <willie@.bodgerfam.com> wrote in message
> news:Oe3aNZwNFHA.3716@.TK2MSFTNGP14.phx.gbl...
>|||You are attempting to execute a non-native (ie: user created) sql server
extended stored procedure. They too are at the server level and not part of
the database. They are simply files on the hard drive and you are
responsible for copying them over as well. Since these are basically DLL's
it could be almost anything depending on how it was coded and what else it
is dependant on. I would find who created it and see what they have to say.
Andrew J. Kelly SQL MVP
"Willie Bodger" <willie@.bodgerfam.com> wrote in message
news:OYrqYexNFHA.3156@.TK2MSFTNGP15.phx.gbl...
> For instance, when I try to run a stored procedure, I get this error:
> OLE Automation Error Information
> HRESULT: 0x800401f3
> Source: ODSOLE Extended Procedure
> Description: Invalid class string
> error! 1
> Any thoughts, or is there still too little info?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e0LMjkwNFHA.3076@.TK2MSFTNGP14.phx.gbl...
>sqlsql

Copy database with encrypted column to new server and decrypt column there

To do this successfully do I need to backup the Service master, Database master, and database itself from the the Source server, then restore all three of them on the destination server?

(I'm concerned that restoring the source Service Master key to a new target server with an existing sql 2005 install will screw things up big time.)

TIA,

Barkingdog

It's actually a little easier than that. All you have to do is restore the database and then restore the encryption between the SMK and the DBMK. The database master key should already be in the database so after that you just need to associate the DBMK with the new server's service master key.

After restore, you will need to:

1) use <database_name>

2) open database master key

3) alter database master key add encryption by service master key

After that, everything should work as normal.

Sung

Copy database problem

I am running SQL Server 2005 and trying to make a copy of a database from
Management Studio. Using the Backup wizard I go through all the steps that
you would normally walk through to make a copy, and actually start the copy,
but when I do I get the following error message from the copy database wizard.
"Could not load type
'Microsoft.SqlServer.Management.Smo.Agent.JobBaseC ollection' from assembly
'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture = neutral, Public Token
= xxxx..."
Could somone tell me what the problem is and how to correct it?
My SS version numbers etc follow:
Microsoft SQL Server Management Studio9.00.1399.00
Microsoft Analysis Services Client Tools2005.090.1399.00
Microsoft Data Access Components (MDAC)2000.085.1117.00
(xpsp_sp2_rtm.040803-2158)
Microsoft MSXML2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer7.0.5730.11
Microsoft .NET Framework2.0.50727.832
Operating System5.1.2600
The same error has been fixed for a couple of people by
applying SP2 - to the server and the client. You would
probably want to start with that approach.
-Sue
On Fri, 27 Jul 2007 13:52:02 -0700, Woody
<Woody@.discussions.microsoft.com> wrote:

>I am running SQL Server 2005 and trying to make a copy of a database from
>Management Studio. Using the Backup wizard I go through all the steps that
>you would normally walk through to make a copy, and actually start the copy,
>but when I do I get the following error message from the copy database wizard.
>"Could not load type
>'Microsoft.SqlServer.Management.Smo.Agent.JobBase Collection' from assembly
>'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture = neutral, Public Token
>= xxxx..."
>Could somone tell me what the problem is and how to correct it?
>My SS version numbers etc follow:
>Microsoft SQL Server Management Studio9.00.1399.00
>Microsoft Analysis Services Client Tools2005.090.1399.00
>Microsoft Data Access Components (MDAC)2000.085.1117.00
>(xpsp_sp2_rtm.040803-2158)
>Microsoft MSXML2.6 3.0 4.0 5.0 6.0
>Microsoft Internet Explorer7.0.5730.11
>Microsoft .NET Framework2.0.50727.832
>Operating System5.1.2600
>

Copy database from SqlServer 2000 to 2005

Hi;
How can I copy a database from SqlServer 2000 to 2005? Copying between Sql
Server 2000 instances we do a backup/restore but 2005 won't do a restore of a
2000 backup.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
David Thielen wrote:
> Hi;
> How can I copy a database from SqlServer 2000 to 2005? Copying between Sql
> Server 2000 instances we do a backup/restore but 2005 won't do a restore of a
> 2000 backup.
>
Sure it will. You can restore a 2000 backup to a 2005 server, but you
can't go the other way.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||We migrated all of our databases from 2000 to 2005 last year - my
memory isn't very clear now but I believe we did detach/reattach of the
database files. Basically you just copy over the ldf, mdf and ndf files
to the new server, then attach them through 2005 management studio,
making sure to specify the new file locations on the new server...
David Thielen wrote:
> Hi;
> How can I copy a database from SqlServer 2000 to 2005? Copying between Sql
> Server 2000 instances we do a backup/restore but 2005 won't do a restore of a
> 2000 backup.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
|||yes, the other posters are correct - you can restore a 2000 db on 2005,
just not the other way around
also keep in mind, once you restore the db's you can change the
compatibility of a given database to match that of 2005, as the
compatibility will be 2000 by default (however be careful when doing
this as you may lose old functionality you may still need)
there is a database upgrade advisor tool available from microsoft,
avail as a download - probably best to run this first before changing
the db compatibility, esp on a production machine
just my 2 cents
MC wrote:[vbcol=seagreen]
> What do you mean wont do a restore of 2000 backup. It should, I did it more
> then a few times. You cant restore 2005 backup on 2000...
>
> MC
>
> "David Thielen" <thielen@.nospam.nospam> wrote in message
> news:7CBA4B6A-BAC8-44D9-9152-637A5D6153FD@.microsoft.com...

Copy database from SqlServer 2000 to 2005

Hi;
How can I copy a database from SqlServer 2000 to 2005? Copying between Sql
Server 2000 instances we do a backup/restore but 2005 won't do a restore of
a
2000 backup.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htmDavid Thielen wrote:
> Hi;
> How can I copy a database from SqlServer 2000 to 2005? Copying between Sql
> Server 2000 instances we do a backup/restore but 2005 won't do a restore o
f a
> 2000 backup.
>
Sure it will. You can restore a 2000 backup to a 2005 server, but you
can't go the other way.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||We migrated all of our databases from 2000 to 2005 last year - my
memory isn't very clear now but I believe we did detach/reattach of the
database files. Basically you just copy over the ldf, mdf and ndf files
to the new server, then attach them through 2005 management studio,
making sure to specify the new file locations on the new server...
David Thielen wrote:
> Hi;
> How can I copy a database from SqlServer 2000 to 2005? Copying between Sql
> Server 2000 instances we do a backup/restore but 2005 won't do a restore o
f a
> 2000 backup.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm|||What do you mean wont do a restore of 2000 backup. It should, I did it more
then a few times. You cant restore 2005 backup on 2000...
MC
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:7CBA4B6A-BAC8-44D9-9152-637A5D6153FD@.microsoft.com...
> Hi;
> How can I copy a database from SqlServer 2000 to 2005? Copying between Sql
> Server 2000 instances we do a backup/restore but 2005 won't do a restore
> of a
> 2000 backup.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>|||yes, the other posters are correct - you can restore a 2000 db on 2005,
just not the other way around
also keep in mind, once you restore the db's you can change the
compatibility of a given database to match that of 2005, as the
compatibility will be 2000 by default (however be careful when doing
this as you may lose old functionality you may still need)
there is a database upgrade advisor tool available from microsoft,
avail as a download - probably best to run this first before changing
the db compatibility, esp on a production machine
just my 2 cents
MC wrote:[vbcol=seagreen]
> What do you mean wont do a restore of 2000 backup. It should, I did it mor
e
> then a few times. You cant restore 2005 backup on 2000...
>
> MC
>
> "David Thielen" <thielen@.nospam.nospam> wrote in message
> news:7CBA4B6A-BAC8-44D9-9152-637A5D6153FD@.microsoft.com...

Copy database backup files over the network

Hello!
We are using SQLSafe software to backup/compress our databases. We would
like to copy backup files (could be as big as 400GB compressed) to the
different computer within the same network. I know there are tools like
robocopy,xcopy etc. that are suitable for this job. I was wondering if
anybody can share his/her own experience in copying big backup files.
Ideally, we wouldn't like to saturate our network/cpu resources while
copying these files.
Thanks,
Igor
Hi
I find it best to have the backup software stripe the backup over multiple
files. This enables you to re-start a failed copy without having to re-copy
all 399GB
Also, add a dedicated NIC (Fiber, 1GBit or faster) onto the 2 servers, so
that they do not copy this file over the production network, affecting normal
queries.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"imarchenko" wrote:

> Hello!
> We are using SQLSafe software to backup/compress our databases. We would
> like to copy backup files (could be as big as 400GB compressed) to the
> different computer within the same network. I know there are tools like
> robocopy,xcopy etc. that are suitable for this job. I was wondering if
> anybody can share his/her own experience in copying big backup files.
> Ideally, we wouldn't like to saturate our network/cpu resources while
> copying these files.
> Thanks,
> Igor
>
>
|||Thanks Mike! We are striping backup over multiple files. We just want to
copy backup to the different computer, once it si completed, in the most
efficient way. Thanks for your tip regarding dedicated NIC.
Igor
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:E6EB48F8-4D11-48DD-B2A2-FA1F46334344@.microsoft.com...[vbcol=seagreen]
> Hi
> I find it best to have the backup software stripe the backup over multiple
> files. This enables you to re-start a failed copy without having to
> re-copy
> all 399GB
> Also, add a dedicated NIC (Fiber, 1GBit or faster) onto the 2 servers, so
> that they do not copy this file over the production network, affecting
> normal
> queries.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "imarchenko" wrote:

Copy database backup files over the network

Hello!
We are using SQLSafe software to backup/compress our databases. We would
like to copy backup files (could be as big as 400GB compressed) to the
different computer within the same network. I know there are tools like
robocopy,xcopy etc. that are suitable for this job. I was wondering if
anybody can share his/her own experience in copying big backup files.
Ideally, we wouldn't like to saturate our network/cpu resources while
copying these files.
Thanks,
IgorHi
I find it best to have the backup software stripe the backup over multiple
files. This enables you to re-start a failed copy without having to re-copy
all 399GB
Also, add a dedicated NIC (Fiber, 1GBit or faster) onto the 2 servers, so
that they do not copy this file over the production network, affecting norma
l
queries.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"imarchenko" wrote:

> Hello!
> We are using SQLSafe software to backup/compress our databases. We woul
d
> like to copy backup files (could be as big as 400GB compressed) to the
> different computer within the same network. I know there are tools like
> robocopy,xcopy etc. that are suitable for this job. I was wondering if
> anybody can share his/her own experience in copying big backup files.
> Ideally, we wouldn't like to saturate our network/cpu resources while
> copying these files.
> Thanks,
> Igor
>
>|||Thanks Mike! We are striping backup over multiple files. We just want to
copy backup to the different computer, once it si completed, in the most
efficient way. Thanks for your tip regarding dedicated NIC.
Igor
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:E6EB48F8-4D11-48DD-B2A2-FA1F46334344@.microsoft.com...[vbcol=seagreen]
> Hi
> I find it best to have the backup software stripe the backup over multiple
> files. This enables you to re-start a failed copy without having to
> re-copy
> all 399GB
> Also, add a dedicated NIC (Fiber, 1GBit or faster) onto the 2 servers, so
> that they do not copy this file over the production network, affecting
> normal
> queries.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "imarchenko" wrote:
>

Copy database backup files over the network

Hello!
We are using SQLSafe software to backup/compress our databases. We would
like to copy backup files (could be as big as 400GB compressed) to the
different computer within the same network. I know there are tools like
robocopy,xcopy etc. that are suitable for this job. I was wondering if
anybody can share his/her own experience in copying big backup files.
Ideally, we wouldn't like to saturate our network/cpu resources while
copying these files.
Thanks,
IgorHi
I find it best to have the backup software stripe the backup over multiple
files. This enables you to re-start a failed copy without having to re-copy
all 399GB
Also, add a dedicated NIC (Fiber, 1GBit or faster) onto the 2 servers, so
that they do not copy this file over the production network, affecting normal
queries.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"imarchenko" wrote:
> Hello!
> We are using SQLSafe software to backup/compress our databases. We would
> like to copy backup files (could be as big as 400GB compressed) to the
> different computer within the same network. I know there are tools like
> robocopy,xcopy etc. that are suitable for this job. I was wondering if
> anybody can share his/her own experience in copying big backup files.
> Ideally, we wouldn't like to saturate our network/cpu resources while
> copying these files.
> Thanks,
> Igor
>
>|||Thanks Mike! We are striping backup over multiple files. We just want to
copy backup to the different computer, once it si completed, in the most
efficient way. Thanks for your tip regarding dedicated NIC.
Igor
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:E6EB48F8-4D11-48DD-B2A2-FA1F46334344@.microsoft.com...
> Hi
> I find it best to have the backup software stripe the backup over multiple
> files. This enables you to re-start a failed copy without having to
> re-copy
> all 399GB
> Also, add a dedicated NIC (Fiber, 1GBit or faster) onto the 2 servers, so
> that they do not copy this file over the production network, affecting
> normal
> queries.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "imarchenko" wrote:
>> Hello!
>> We are using SQLSafe software to backup/compress our databases. We
>> would
>> like to copy backup files (could be as big as 400GB compressed) to the
>> different computer within the same network. I know there are tools like
>> robocopy,xcopy etc. that are suitable for this job. I was wondering if
>> anybody can share his/her own experience in copying big backup files.
>> Ideally, we wouldn't like to saturate our network/cpu resources while
>> copying these files.
>> Thanks,
>> Igor
>>

Copy Backup file

hi,
I have a problem copying 12 Gb database backup files (2) for network backup
copy from the SAN. I used xp_cmdshell ' copy ...' to copy the backup files
every day night. Sometimes, the files are not copied over the network. I have
full backup every day night and log backup every hour. Can any one suggest a
good solution for copying the backup files out of SAN, (cost effective
solution, without buying software. I dont have a differential backups right
now because of storage space. )
thanks
kris
"kpxus" <kpxus@.discussions.microsoft.com> wrote in message
news:8EA2A0EA-61CB-446D-906D-8C9D7BCD96B9@.microsoft.com...
> hi,
> I have a problem copying 12 Gb database backup files (2) for network
backup
> copy from the SAN. I used xp_cmdshell ' copy ...' to copy the backup
files
> every day night. Sometimes, the files are not copied over the network. I
have
> full backup every day night and log backup every hour. Can any one suggest
a
> good solution for copying the backup files out of SAN, (cost effective
> solution, without buying software. I dont have a differential backups
right
> now because of storage space. )
>
> thanks
> kris
>
Kris,
Several things come to mind. You could make smaller backups by creating
filegroups and doing filegroup backups on a rotational schedule. Check them
out in the BOL. Faster backups, smaller backups, quicker recovery and
fairly straightforward to use.
You could use the Windows scheduler to copy the files around.
There are shareware/freeware .NET Windows Service apps that can copy files
from just about any location to any other location and include encryption
etc.
Rick Sawtell
MCT, MCSD, MCDBA
|||Hi,
You could write a small DOS script (batch file) to copy files from one
location to other and schedule the batch file via windows schedular. For
example:
On the destination server create a batch like:
copy \\server_name\Share_Name\directory_or_filenames
destination_path(E:\sqlbackups\
Test this out. It will work properly.
hth
DeeJay Puar
"kpxus" wrote:

> hi,
> I have a problem copying 12 Gb database backup files (2) for network backup
> copy from the SAN. I used xp_cmdshell ' copy ...' to copy the backup files
> every day night. Sometimes, the files are not copied over the network. I have
> full backup every day night and log backup every hour. Can any one suggest a
> good solution for copying the backup files out of SAN, (cost effective
> solution, without buying software. I dont have a differential backups right
> now because of storage space. )
>
> thanks
> kris
>
|||Thanks
"DeeJay Puar" <DeeJayPuar@.discussions.microsoft.com> wrote in message
news:30CD919E-39BD-408F-9A9E-239FA9F62BA7@.microsoft.com...[vbcol=seagreen]
> Hi,
> You could write a small DOS script (batch file) to copy files from one
> location to other and schedule the batch file via windows schedular. For
> example:
> On the destination server create a batch like:
> copy \\server_name\Share_Name\directory_or_filenames
> destination_path(E:\sqlbackups\
> Test this out. It will work properly.
> hth
> DeeJay Puar
>
> "kpxus" wrote:
backup[vbcol=seagreen]
files[vbcol=seagreen]
have[vbcol=seagreen]
suggest a[vbcol=seagreen]
right[vbcol=seagreen]