copying the database from development box to production box
-
I have a database db1 on the development box Serv1. I have copied that database to the production box by restoring the latest full back of the database on development box.
I can see all the objects and logins on the production box except the sql authentiacated logins.
I am also unable to see the logins at security node on the production box. So i have created them manually and trying to give the required previleges to the sql logins. But it says login already exists. But when i try to connect from query analyzer to the database db1 it gives me the error 'login failed'.
So I have dropped all the users by using the command sp_dropuser and created the logins at server level at gave the required previleges to the users on this db.
Can anyone let me know if I am doing the correct process when copying the database from one box to the other.
Thanks.
-
The reason is when u copied the Databases over the logins that have rights on the databases got copied over. But the "sid" for the logins that were created on the Dev box did not match the sid on the production box. So u can do one of the following:
1.User sp_change_users_login to fix the sid mismatch.
EXEC sp_change_users_login 'Update_One', 'UserId', 'UserID'
2.Revoke access and grant them access again.
sp_revokedbaccess @name_in_db = 'DBUserName'
go
sp_grantdbaccess @loginame =LoginName',@name_in_db =DBUserName'
go
Hope this helps.
Thanks
Sreejith
-
Instead of using backup and restore, you can script the database. In the options page, you can script the database, users and database roles, SQL Server Logins and object level permissioins.
-
I am going for backup and restore because I need to copy the data from development server to production box.
-
One more thing I need to do is that to transfer sql jobs and dts packages associated with this database to the production server from development server.
How can I do that?
Thanks.
-
For SQL jobs, you have to use script. You have to script out each job and put it in production. For DTS package, open the DTS package, click on save as, put the new server name in the 'Server' field, then you are done.
-
To transfer the jobs, there is another way is using DTS package, the is a task called "Transfer Jobs Task", you specify the source server and destination server and select which job to transfer.
-
Is there any system procedure to know the size of the database like we use sp_spaceused to know the size of the table.
Thanks.
-
To get the size of the database
sp_helpdb @dbname= 'databaseName'
-
There is no system procedure to get size of the table, it has system procedure to get size of the file or file group.
But you can calculate the size of the table by yourself. Have fun
Estimating the Size of a Table
The following steps can be used to estimate the amount of space required to store the data in a table:
- Specify the number of rows present in the table:
Number of rows in the table = Num_Rows
- If there are fixed-length and variable-length columns in the table definition, calculate the space that each of these groups of columns occupies within the data row. The size of a column depends on the data type and length specification. For more information, see Data Types.
Number of columns = Num_Cols
Sum of bytes in all fixed-length columns = Fixed_Data_Size
Number of variable-length columns = Num_Variable_Cols
Maximum size of all variable-length columns = Max_Var_Size
- If there are fixed-length columns in the table, a portion of the row, known as the null bitmap, is reserved to manage column nullability. Calculate its size:
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
Only the integer portion of the above expression should be used; discard any remainder.
- If there are variable-length columns in the table, determine how much space is used to store the columns within the row:
Total size of variable-length columns (Variable_Data_Size) = 2 + (Num_Variable_Cols x 2) + Max_Var_Size
If there are no variable-length columns, set Variable_Data_Size to 0.
This formula assumes that all variable-length columns are 100 percent full. If you anticipate that a lower percentage of the variable-length column storage space will be used, you can adjust the result by that percentage to yield a more accurate estimate of the overall table size.
- Calculate the row size:
Total row size (Row_Size) = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap +4
The final value of 4 represents the data row header.
- Calculate the number of rows per page (8096 free bytes per page):
Number of rows per page (Rows_Per_Page) = ( 8096 ) / (Row_Size + 2)
Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row.
- If a clustered index is to be created on the table, calculate the number of reserved free rows per page, based on the fill factor specified. For more information, see Fill Factor. If no clustered index is to be created, specify Fill_Factor as 100.
Number of free rows per page (Free_Rows_Per_Page) = 8096 x ((100 - Fill_Factor) / 100) / (Row_Size + 2)
The fill factor used in the calculation is an integer value rather than a percentage.
Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row. As the fill factor grows, more data will be stored on each page and there will be fewer pages.
- Calculate the number of pages required to store all the rows:
Number of pages (Num_Pages) = Num_Rows / (Rows_Per_Page - Free_Rows_Per_Page)
The number of pages estimated should be rounded up to the nearest whole page.
- Calculate the amount of space required to store the data in a table (8192 total bytes per page):
Table size (bytes) = 8192 x Num_Pages
- Specify the number of rows present in the table:
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply