Skip to main content
added 26 characters in body
Source Link
Stijn Wynants
  • 1.8k
  • 12
  • 18

Since you want to copy your database to another server I'd suggest doing this by the concept of backup restore. This is example code but you can easily also use the GUI (http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backups), make sure that the service account has access the place you are going to write your backup too

BACKUP DATABASE [YourDatabaseName] TO  DISK = N'\\someshare\YourDatabase.bak' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'YourDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

and then Restore the database on the destination server, also here make sure that the service account of SQL Server has access to the location where the backup is stored

USE [master]
RESTORE DATABASE [YourDatabase] 
FROM  DISK = N'\\someshare\YourDatabase.bak' WITH  FILE = 1,  
MOVE N'DatabaseFileName' TO N'SomeDisk\YourDatabase.mdf',  
MOVE N'DatabaseFileName2' TO N'SomeDisk\YourDatabase.ndf',  
MOVE N'N'DatabaseLogFileName' TO N'SomeDisk\YourDatabase.ldf',  NOUNLOAD,  STATS = 5

Or you can use DBA-Tools to do it for you using powershell

Check out https://dbatools.io/functions/backup-dbadatabase/ & https://dbatools.io/functions/restore-dbadatabase/

EDIT

Using dba-tools you can easily script this for a list of databases.

Because you want minimum of downtime, I suggest to restore the full db-backup to the server & leave it in recovery mode, and then start applying all logs to the database in recovery until you have all data in sync which you want in sync and restore the last log with recovery

For the destination server where you already have the databases on, I would suggest restoring the db's to a different name and then renaming the old db when you have restored the database to that side. This way the downtime will only be the rename of the db, which will require a DB level lock.

The way of Backup restore is more reliable, especially for the amount of data that has to be copied.

Since you want to copy your database to another server I'd suggest doing this by the concept of backup restore. This is example code but you can easily also use the GUI (http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backups), make sure that the service account has access the place you are going to write your backup too

BACKUP DATABASE [YourDatabaseName] TO  DISK = N'\\someshare\YourDatabase.bak' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'YourDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

and then Restore the database on the destination server, also here make sure that the service account of SQL Server has access to the location where the backup is stored

USE [master]
RESTORE DATABASE [YourDatabase] 
FROM  DISK = N'\\someshare\YourDatabase.bak' WITH  FILE = 1,  
MOVE N'DatabaseFileName' TO N'SomeDisk\YourDatabase.mdf',  
MOVE N'DatabaseFileName2' TO N'SomeDisk\YourDatabase.ndf',  
MOVE N'N'DatabaseLogFileName' TO N'SomeDisk\YourDatabase.ldf',  NOUNLOAD,  STATS = 5

Or you can use DBA-Tools to do it for you using powershell

Check out https://dbatools.io/functions/backup-dbadatabase/ & https://dbatools.io/functions/restore-dbadatabase/

EDIT

Using dba-tools you can easily script this for a list of databases.

Because you want minimum of downtime, I suggest to restore the full db-backup to the server & leave it in recovery mode, and then start applying all logs to the database in recovery until you have all data in sync which you want in sync and restore the last log with recovery

For the destination server you already have the databases on, I would suggest restoring the db's and then renaming the old db when you have restored the database to that side. This way the downtime will only be the rename of the db, which will require a DB level lock.

The way of Backup restore is more reliable, especially for the amount of data that has to be copied.

Since you want to copy your database to another server I'd suggest doing this by the concept of backup restore. This is example code but you can easily also use the GUI (http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backups), make sure that the service account has access the place you are going to write your backup too

BACKUP DATABASE [YourDatabaseName] TO  DISK = N'\\someshare\YourDatabase.bak' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'YourDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

and then Restore the database on the destination server, also here make sure that the service account of SQL Server has access to the location where the backup is stored

USE [master]
RESTORE DATABASE [YourDatabase] 
FROM  DISK = N'\\someshare\YourDatabase.bak' WITH  FILE = 1,  
MOVE N'DatabaseFileName' TO N'SomeDisk\YourDatabase.mdf',  
MOVE N'DatabaseFileName2' TO N'SomeDisk\YourDatabase.ndf',  
MOVE N'N'DatabaseLogFileName' TO N'SomeDisk\YourDatabase.ldf',  NOUNLOAD,  STATS = 5

Or you can use DBA-Tools to do it for you using powershell

Check out https://dbatools.io/functions/backup-dbadatabase/ & https://dbatools.io/functions/restore-dbadatabase/

EDIT

Using dba-tools you can easily script this for a list of databases.

Because you want minimum of downtime, I suggest to restore the full db-backup to the server & leave it in recovery mode, and then start applying all logs to the database in recovery until you have all data in sync which you want in sync and restore the last log with recovery

For the destination server where you already have the databases on, I would suggest restoring the db's to a different name and then renaming the old db when you have restored the database to that side. This way the downtime will only be the rename of the db, which will require a DB level lock.

The way of Backup restore is more reliable, especially for the amount of data that has to be copied.

added 637 characters in body
Source Link
Stijn Wynants
  • 1.8k
  • 12
  • 18

Since you want to copy your database to another server I'd suggest doing this by the concept of backup restore. This is example code but you can easily also use the GUI (http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backups), make sure that the service account has access the place you are going to write your backup too

BACKUP DATABASE [YourDatabaseName] TO  DISK = N'\\someshare\YourDatabase.bak' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'YourDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

and then Restore the database on the destination server, also here make sure that the service account of SQL Server has access to the location where the backup is stored

USE [master]
RESTORE DATABASE [YourDatabase] 
FROM  DISK = N'\\someshare\YourDatabase.bak' WITH  FILE = 1,  
MOVE N'DatabaseFileName' TO N'SomeDisk\YourDatabase.mdf',  
MOVE N'DatabaseFileName2' TO N'SomeDisk\YourDatabase.ndf',  
MOVE N'N'DatabaseLogFileName' TO N'SomeDisk\YourDatabase.ldf',  NOUNLOAD,  STATS = 5

Or you can use DBA-Tools to do it for you using powershell

Check out https://dbatools.io/functions/backup-dbadatabase/ & https://dbatools.io/functions/restore-dbadatabase/

EDIT

Using dba-tools you can easily script this for a list of databases.

Because you want minimum of downtime, I suggest to restore the full db-backup to the server & leave it in recovery mode, and then start applying all logs to the database in recovery until you have all data in sync which you want in sync and restore the last log with recovery

For the destination server you already have the databases on, I would suggest restoring the db's and then renaming the old db when you have restored the database to that side. This way the downtime will only be the rename of the db, which will require a DB level lock.

The way of Backup restore is more reliable, especially for the amount of data that has to be copied.

Since you want to copy your database to another server I'd suggest doing this by the concept of backup restore. This is example code but you can easily also use the GUI (http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backups), make sure that the service account has access the place you are going to write your backup too

BACKUP DATABASE [YourDatabaseName] TO  DISK = N'\\someshare\YourDatabase.bak' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'YourDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

and then Restore the database on the destination server, also here make sure that the service account of SQL Server has access to the location where the backup is stored

USE [master]
RESTORE DATABASE [YourDatabase] 
FROM  DISK = N'\\someshare\YourDatabase.bak' WITH  FILE = 1,  
MOVE N'DatabaseFileName' TO N'SomeDisk\YourDatabase.mdf',  
MOVE N'DatabaseFileName2' TO N'SomeDisk\YourDatabase.ndf',  
MOVE N'N'DatabaseLogFileName' TO N'SomeDisk\YourDatabase.ldf',  NOUNLOAD,  STATS = 5

Or you can use DBA-Tools to do it for you using powershell

Check out https://dbatools.io/functions/backup-dbadatabase/ & https://dbatools.io/functions/restore-dbadatabase/

Since you want to copy your database to another server I'd suggest doing this by the concept of backup restore. This is example code but you can easily also use the GUI (http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backups), make sure that the service account has access the place you are going to write your backup too

BACKUP DATABASE [YourDatabaseName] TO  DISK = N'\\someshare\YourDatabase.bak' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'YourDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

and then Restore the database on the destination server, also here make sure that the service account of SQL Server has access to the location where the backup is stored

USE [master]
RESTORE DATABASE [YourDatabase] 
FROM  DISK = N'\\someshare\YourDatabase.bak' WITH  FILE = 1,  
MOVE N'DatabaseFileName' TO N'SomeDisk\YourDatabase.mdf',  
MOVE N'DatabaseFileName2' TO N'SomeDisk\YourDatabase.ndf',  
MOVE N'N'DatabaseLogFileName' TO N'SomeDisk\YourDatabase.ldf',  NOUNLOAD,  STATS = 5

Or you can use DBA-Tools to do it for you using powershell

Check out https://dbatools.io/functions/backup-dbadatabase/ & https://dbatools.io/functions/restore-dbadatabase/

EDIT

Using dba-tools you can easily script this for a list of databases.

Because you want minimum of downtime, I suggest to restore the full db-backup to the server & leave it in recovery mode, and then start applying all logs to the database in recovery until you have all data in sync which you want in sync and restore the last log with recovery

For the destination server you already have the databases on, I would suggest restoring the db's and then renaming the old db when you have restored the database to that side. This way the downtime will only be the rename of the db, which will require a DB level lock.

The way of Backup restore is more reliable, especially for the amount of data that has to be copied.

Source Link
Stijn Wynants
  • 1.8k
  • 12
  • 18

Since you want to copy your database to another server I'd suggest doing this by the concept of backup restore. This is example code but you can easily also use the GUI (http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backups), make sure that the service account has access the place you are going to write your backup too

BACKUP DATABASE [YourDatabaseName] TO  DISK = N'\\someshare\YourDatabase.bak' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'YourDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

and then Restore the database on the destination server, also here make sure that the service account of SQL Server has access to the location where the backup is stored

USE [master]
RESTORE DATABASE [YourDatabase] 
FROM  DISK = N'\\someshare\YourDatabase.bak' WITH  FILE = 1,  
MOVE N'DatabaseFileName' TO N'SomeDisk\YourDatabase.mdf',  
MOVE N'DatabaseFileName2' TO N'SomeDisk\YourDatabase.ndf',  
MOVE N'N'DatabaseLogFileName' TO N'SomeDisk\YourDatabase.ldf',  NOUNLOAD,  STATS = 5

Or you can use DBA-Tools to do it for you using powershell

Check out https://dbatools.io/functions/backup-dbadatabase/ & https://dbatools.io/functions/restore-dbadatabase/